Use these instructions to install and configure a database for Sync Service.
Alfresco recommends that you use a separate PostgreSQL instance for the Sync Service.
Note: Only the Sync Service communicates with the database. It persists events taken from the JMS queue into the database. The repository doesn’t communicate with the database.
The Sync Service isn’t packaged with a PostgreSQL driver, so it’ll need to be downloaded separately and cited in the start-up. See step Starting the Sync Service.
-
Download the appropriate driver that’s compatible with JDBC42 from the PostgreSQL JDBC Driver download page.
-
Copy the JAR file into the same directory as the Sync Service JAR.
-
Increase the maximum connections setting in the PostgreSQL configuration file.
-
Locate the configuration file:
- Linux:
/var/lib/pgsql/9.4/data/postgresql.conf
- Windows:
C:\Program Files\PostgreSQL\9.4\data\postgresql.conf
- Linux:
-
Add or edit the
max_connections
property:max_connections = 450
If
max_connections
is left unchanged, bear in mind that in the PostgreSQL database, the defaultmax_connections
is100
. So, the value of thedb.pool.max
property in theconfig.yml
file must be less than or equal to100
.If there are multiple Sync Service instances forming a cluster hidden behind a load balancer,
max_connections
should be greater than or equal to the sum of alldb.pool.max
from allconfig.yml
files.For example, if
max_connections=450
, and there are 3 Sync Service instances, then the correct setting inconfig.yml
isdb.pool.max : 150
. -
Restart the database.
-
-
Review your memory requirements in
work_mem
in thepostgresql.conf
file in your PostgreSQL directory.Increasing the
work_mem
value increases performance and allows PostgreSQL to perform larger in-memory sorting. For more information about PostgreSQL performance, see Tuning PostgreSQL. -
Create a Postgres user with the username given by the sync property,
sql.db.username
, with password given by the sync propertysql.db.password
:CREATE USER alfresco WITH PASSWORD 'admin';
-
Create a Postgres database with the name given in the property,
sql.db.url
, owned by the useralfresco
that you will use to run PostgreSQL, and ensure that this user has write permissions on all tables and sequences.CREATE DATABASE alfresco OWNER alfresco ENCODING 'utf8'; GRANT ALL PRIVILEGES ON DATABASE alfresco TO alfresco;
-
Ensure
sql.db.driver
andsql.db.url
are correctly updated in theconfig.yml
file, e.g.sql: db: driver: org.postgresql.Driver url: jdbc:postgresql://localhost:5432/alfresco
Additional PostgreSQL configuration requirements
For the Sync Service installation, there are some additional PostgreSQL database configuration requirements.
The PostgreSQL settings to configure depends on:
- Level of repository activity: A higher activity increases the database insert/update and auto_vacuum analyze load.
- Number of syncs: A higher number of syncs results in a higher query load. The sizing of memory buffers need to reflect this.
- Event size: The average event size is 1300 bytes.
Sync activity level
Setting affected by the sync activity level include:
work_mem
: The sync query result set sizes may be large for subscriptions that haven’t been synced for a while. Also, the subscriptions need to be sorted. By default, the client will sync every 5 minutes so the number of sync changes is not likely to be very large, but clients that are offline will build up large outstanding sync result sets. Setwork_mem
higher if clients are expected to be offline for long periods of time.
Repository activity level
Settings affected by repository activity level include:
autovacuum_naptime
: The database is split into half between writes (event persistence) and reads (sync changes). For a more write heavy installation in which the repository updates outweigh the sync activity, this property needs to be set lower so that the new events are incorporated into the table statistics (and hence indexes are used optimally).autovacuum_analyze_threshold
: The default value is 50 tuples. For a more repository update heavy installation, set this property to a low value to help with queries.
Disk space
Disk space is required for the events and subscriptions. The events use most of the disk space. A typical operation, such as add folder/document, update document, delete folder/document, move folder/document will result in 1-10 events. More complex operations, such as create site will generate more.
A cleanup job runs periodically to clean up events that are older than a configurable number. The default value is 28 days, so the disk space is required to cover this time period. The disk space is set using the sync.cleanup
property in the config.yml
file.
A rough estimate of disk space requirements for PostgreSQL database can be calculated as follows:
(#update operations per hour * 24*28 * 5 * 1300) / (1024*1024) MB
So based on the above assumptions and 100 operations per hour, we have ~416MB.
The following query will give the disk space usage for each of the Sync Service tables and indexes:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND relname like '%sync%'
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 200;
Alfresco recommends that you use a separate Oracle instance for the Sync Service.
Note: Only the Sync Service communicates with the database. It persists events taken from the JMS queue into the database. The repository does not communicate with the database.
The Sync Service isn’t packaged with an Oracle driver, so it’ll need to be downloaded separately and cited in the start-up. See step Starting the Sync Service.
-
Download the Oracle database connector
ojdbc7.jar
from the Oracle JDBC Driver download page. -
Copy the JAR file into the same directory as the Sync Service JAR.
The JDBC driver for Oracle is in the JAR file:
ojdbc7.jar
.However, if you see the following error, then add the
Doracle.jdbc.thinLogonCapability=o3
parameter toJAVA_OPTS
:java.sql.SQLException: OAUTH marshaling failure
-
The Oracle database must be created with the AL32UTF8 character set. Check the current character set by executing:
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
Have a look at this quick tutorial to alter the character set: Change Oracle Database Character Set : NLS_CHARACTERSET.
-
Increase the maximum connections setting in the Oracle configuration file. The property
processes
specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. This effectively determines the maximum number of concurrent users in the system.alter system set processes=450 scope=spfile
The value of the
db.pool.max
property in theconfig.yml
file must be less thanprocesses
.If there are multiple Sync Service instances forming a cluster hidden behind a load balancer,
processes
should be greater than or equal to the sum of alldb.pool.max
from allconfig.yml
files.For example, if
processes=450
, and there are 3 Sync Service instances, then the correct setting inconfig.yml
isdb.pool.max : 150
. -
Create a user with the username given by the sync property,
sql.db.username
, with password given by the sync property,sql.db.password
:CREATE USER alfresco IDENTIFIED BY admin;
-
Grant the alfresco user Connect and Resource privileges in Oracle.
-
Grant the user write permissions on all tables and sequences:
GRANT CONNECT, RESOURCE TO alfresco;
-
Configure the privileges by using one of the following commands:
ALTER USER alfresco QUOTA <QUOTE_M> ON Users;
or
GRANT UNLIMITED TABLESPACE TO alfresco
Note: If the privileges on tablespace “USERS” aren’t set correctly, you may see the following error:
ORA-01950: no privileges on tablespace 'USERS'
-
-
Ensure
sql.db.driver
andsql.db.url
are correctly updated in theconfig.yml
file, for example:sql: db: driver: oracle.jdbc.OracleDriver url: jdbc:oracle:thin:@//localhost:1521/xe
Alfresco recommends that you use a separate MySQL instance for the Sync Service.
Note: Only the Sync Service communicates with the database. It persists events taken from the JMS queue into the database. The repository doesn’t communicate with the database.
The Sync Service isn’t packaged with a MySQL driver, so it’ll need to be downloaded separately and cited in the start-up. See step Starting the Sync Service.
-
Download the MySQL database connector from the MySQL JDBC Driver download page.
-
Copy the JAR file into the same directory as the Sync Service JAR.
-
Locate the configuration file:
- For Linux:
/etc/my.cnf
- For Windows:
C:\Users\All Users\MySQL\MySQL Server 5.6\my.ini
- For Linux:
-
Increase the maximum connections setting in the MySQL configuration file. In the
mysqld
section, add or edit themax_connections
property:max_connections = 450
If
max_connections
is left unchanged, bear in mind that in the MySQL database, the defaultmax_connections
is151
. So, the value of thedb.pool.max
property in the config.yml file must be less than or equal to151
.If there are multiple Sync Service instances forming a cluster hidden behind a load balancer,
max_connections
should be greater than or equal to the sum of alldb.pool.max
from allconfig.yml
files.For example, if
max_connections=450
, and there are 3 Sync Service instances, then the correct setting inconfig.yml
isdb.pool.max : 150
. -
Set the
max_allowed_packet
parameter to an appropriate size, for example1M
.max_allowed_packet=1M
This helps to avoid Packet too large exceptions, since the average event size is 1300 bytes.
-
To further improve InnoDB performance, you can increase the InnoDB buffer pool size.
innodb_buffer_pool_size=2G
The larger the InnoDB buffer pool, the more InnoDB acts like an in-memory database. The default value is
8M
. See the MySQL documentation on Configuring the InnoDB buffer pool size for more details. -
Divide your buffer pool into separate instances:
innodb_buffer_pool_instances=4
This can improve concurrency by reducing contention, as different threads read and write to cached pages. Default value is 1 when
innodb_buffer_pool_size<1G
. -
Restart the database.
-
Create a MySQL user with the username given by the sync property,
sql.db.username
, with password given by the sync property,sql.db.password
:CREATE USER 'alfresco' IDENTIFIED BY 'admin';
-
Create a MySQL database with the name given by the property,
sql.db.url
, owned by the user,alfresco
:sql: db: url: jdbc:mysql://localhost:3306/alfresco?useUnicode=yes&characterEncoding=UTF-8
If the repository generates events for nodes with names or paths that require non-US-ASCII characters, you need to set the encoding for internationalization. This allows you to store content with accents in the repository. The database must be created with the UTF-8 character set and the
utf8_bin
collation. Although MySQL is a Unicode database, and uses Unicode strings in Java, the JDBC driver might corrupt your non-English data. Ensure that you keep the?useUnicode=yes&characterEncoding=UTF-8
parameters at the end of the JDBC URL.Note: Ensure that the MySQL database is set to use UTF-8 and InnoDB.
-
Ensure that the MySQL database is set to use UTF-8 and InnoDB:
CREATE DATABASE alfresco DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; GRANT ALL ON alfresco.* to alfresco IDENTIFIED BY 'admin';
-
Ensure
sql.db.driver
andsql.db.url
are correctly updated in theconfig.yml
file, for example:sql: db: driver: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/alfresco?useUnicode=yes&characterEncoding=UTF-8