Sync server high volume use

# 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.

## Database setup

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.

### MySQL server configuration

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](https://dev.mysql.com/doc/refman/5.5/en/innodb-file-format.html) 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.

Example configuration:
````
[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
````

### Database maintenance

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):
```` bash
mysql --user=cronuser --password=cronpassword -e "analyze table wbo;" pmsyncdb
````
Where `cronuser` and `cronpassword` are the credentials of a MySQL user created for this task with insert and update privileges.

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.