High-volume use of the sync server
If you have a lot of users for your sync server, there are a few tweaks/setup considerations you need to have a look at. This is because the way the sync server is designed, most data is in a single, large table that gets constantly updated.
To deal with high-volume use on the database side, you need to:
- Use a real database server (MySQL). SQLite does not suffice for more than small organizations.
- Use InnoDB for your sync tables. This is required because sync has lots of writes/updates, MyISAM locks the entire table when updating, and you'll end up with overflowing queues/service refusal otherwise.
- Configure the MySQL server correctly (see below).
- Perform regular maintenance on the tables/indices.
The database server should be set up to handle the specific requirements of the sync server, which is a large number of insert/update/replace operations per user (typical usage sees more writes than reads which is not your average database use that is more reads than writes).
It is recommended to use the Barracuda file format and its DYNAMIC row format for the `wbo` table.
You should use separate files per table to deal with the data churn effectively and be able to reclaim disk space properly when performing maintenance.
Code: Select all
[mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock tmpdir = /tmp user=mysql symbolic-links = 0 sync_binlog = 0 # Allow the wbo table to grow as-needed (it will get very large) big-tables # If you require ACID compliance, you need to leave the following setting to default. innodb_flush_log_at_trx_commit = 2 # Adjust the following according to your processing capabilities, keep it on the low end. innodb_thread_concurrency = 2 thread_cache_size = 4 # Speed up writes, See MySQL documentation innodb_doublewrite = 0 # Very important: this should be 2/3-3/4 of your available RAM in the server innodb_buffer_pool_size = 4096M # Adjust as needed to buffer concurrent updates innodb_log_file_size = 256M innodb_log_buffer_size = 64M # io: min 100 def 200 -- adjust this to match your write I/O capacity of your hardware innodb_io_capacity = 600 ;performance_schema innodb_file_per_table = 1 innodb_file_format = barracuda key_buffer_size = 24M query_cache_limit = 4M query_cache_size = 256M query_cache_type = 1
Indices should be key-balanced on a nightly schedule if you have more than 10,000 users to prevent fragmentation and runaway I/O on your database.
This is best done using a cron job with the following command (e.g. in a cron shell script):
Where `cronuser` and `cronpassword` are the credentials of a MySQL user created for this task with insert and update privileges.
Code: Select all
mysql --user=cronuser --password=cronpassword -e "analyze table wbo;" pmsyncdb
This doesn't take a very long time and can be performed live and on-line, so you don't have to take down the server for this maintenance.
On less regular intervals, you should take the server down for an `optimize table` session to reclaim disk space. Keep in mind that InnoDB tables don't support in-place optimization so you need enough free disk space to store a copy of the wbo data table temporarily. How often this is needed depends on the amount of users and the amount of data churn.
Operating environment considerations
For larger installations:
- Use an enterprise Linux OS to handle the concurrent network/IO needed, e.g. CentOS or Debian.
- Running in a VPS is possible even for high-volume use, but be careful with the required amount of resources.
- Preferably use a fast front-end. Pale Moon Sync uses the NginX/PHP-FPM combination for this.
- Recommended choice of software: CentOS, NginX, PHP-FPM 5.6, MariaDB SQL server.