Setting MySQL for high availability (WebMail Pro 6 PHP)

From AfterLogic Wiki

Jump to: navigation, search

When deploying our WebMail Pro and MailSuite Pro applications on clustered environments, we have initially used straightforward MySQL servers replication in terms of shared database storage. It turned out, however, that this solution is not suitable for systems which run under heavy load. For high availability configuration, a different approach is required. While the approach was designed for our products, it can be used within any high availability solution where MySQL is involved.

We assume that you are already familiar with MySQL. Our main goal here is tuning and optimizing MySQL itself in order to provide maximum productivity for cluster node.

In case if you encounter performance issues, the first thing to do is to debug them.

Enable the slow query log as follows:

set global slow_query_log=1; set global long_query_time=3; //3 seconds set global log_queries_not_using_indexes=1;

Slow queries will be written in the slow query log in plain text format:

mysql> show global variables like 'slow_query_log_file'; +---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | slow_query_log_file | /var/lib/mysql/mysqld-slow.log | +---------------------+------------------------------+ 1 row in set (0.00 sec)

Alternately, you can use MySQL Enterprise Monitor which, however, does not allow for monitoring data nodes.

Troubleshooting of performance issues related to specific MySQL queries can be represented by the following scheme:

1. Start by analyzing the slow query log. Change long_query_time if needed.
2. Use EXPLAIN to figure out if the query is:
• using the correct indexes;
• JOINing the tables in the wrong order;
• so bad that it needs to be rewritten.
3. Re-run the query under configuration sets which match typical use cases, that can be done using mysqlslap.
4. Go back to step 1.

You can also use other tools like mysqlsla. It's important to realize that performance tuning is a never-ending task, so prior to doing any optimizations, make sure there are issues which require that.

The second important goal is tuning MySQL configuration parameters for better productivity.

The first parameter is ndb_cluster_connection_pool, tuning it can provide at least 70% performance boost.

Ndb_cluster_connection_pool = 2 x <CPU cores> is a good starting point. Configurator for MySQL Cluster/Replication can assist you with picking proper values.

The second parameter to tune is auto_increments. MySQL server caches ranges of auto_increments; for instance, with simplest case of 2 nodes ServerA gets 1..1024 and serverB gets 1025..2048. Once no values left in those ranges, data nodes must be locked, next range is fetched, and unlocking nodes is followed by serialization.

Default value for ndb_autoincrement_prefetch_sz is 1 which is too small. This value direclty affects a number of roundtrips for fetching new ranges from data nodes.

For example, if we have 16 batches, 8 threads and single application, a number of transactions per seconds (TPS) matching ndb_autoincrement_prefetch_sz would be something like:

  • Default=1: 1211.91 TPS
  • 256: 3471.71 TPS
  • 1024 : 3659.52 TPS

Thus, ndb_auto_increment_prefetch_sz should be adjusted depending on INSERT load.

Additional parameters worth checking are:

  • thread_cache_size = <max_connections>
  • table_open_cache = 512 (the value should be suitable for most cases)
  • Upon checking status with SHOW GLOBAL STATUS, increase thread_cache_size if threads_created increases, and increase table_open_cache if opened_tables increases.

Once again, Configurator for MySQL Cluster/Replication provides great default values, we believe it's the best service currently available for this purpose.

To conclude, there are several things which should be kept in mind:

  • Make sure you never run in swap! The data nodes performance will degrade and you will get an unstable system. It is strongly advised to set vm.swappiness to 0.
  • Lock data nodes to make sure ethernet access is not interruptedthreads to CPUs not handling interrupts for ETH.
  • Mount data storage partitions with noatime option supplied.

Below you'll find a sample configuration file for MySQL, values supplied there are picked based on our experience with setting multiple production systems.

###---- my.conf example for two mysql master-master replicated node cluster: [client] port = 3306 password = MYPASSWD socket = /usr/mailsuite/tmp/mysql.sock [mysqld] datadir=/var/lib/mysql port = 3307 socket = /tmp/mysql.sock user = mysql group = mysql key_buffer = 16K max_allowed_packet = 16M table_cache = 4 table_open_cache=512 thread_cache_size = 1024 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K ### NOTE: do not use MyISAM on slow storages! MyISAM is for SSD only! default-storage-engine = InnoDB wait_timeout = 300 max_connections = 1024 innodb_buffer_pool_size=2G innodb_log_file_size=128M innodb_flush_log_at_trx_commit=0 innodb_locks_unsafe_for_binlog=1 wsrep_slave_threads=16 #old_passwords=1 ############### change this for own case, node#1 #server-id=1 #master-host = IP.ADDR.ESS.02 #master-user = MASTER_USER #master-password = MASTER_PASS #master-port = 3306 ### these master-parameters are obsolete in modern mysqls, mysql itself recommends us to use internal requests to set up master-slave attitude instead of config-file parameters: # # CHANGE MASTER TO MASTER_HOST='IP.ADDR.ESS.02', MASTER_PORT=3306, MASTER_USER='MASTER_USER', MASTER_PASSWORD='MASTER_PASS'; # # NOTE: MASTER_USER@IP.ADDR.ESS.01 must be added with enough permissions on the second node IP.ADDR.ESS.02 to make replication possible. # # #log-bin #binlog-do-db=DBNAME1 #binlog-do-db=DBNAME2 #replicate-ignore-db = mysql ################ ############### change this for own case, node#2 #server-id=2 #master-host = IP.ADDR.ESS.01 #master-user = MASTER_USER #master-password = MASTER_PASS #master-port = 3306 ### these master-parameters are obsolete in modern mysqls, mysql itself recommends us to use internal requests to set up master-slave attitude instead of config-file parameters: # # CHANGE MASTER TO MASTER_HOST='IP.ADDR.ESS.01', MASTER_PORT=3306, MASTER_USER='MASTER_USER', MASTER_PASSWORD='MASTER_PASS'; # # NOTE: MASTER_USER@IP.ADDR.ESS.02 must be added with enough permissions on the first node IP.ADDR.ESS.01 to make replication possible. # # # #log-bin #binlog-do-db=DBNAME1 #binlog-do-db=DBNAME2 #replicate-ignore-db = mysql ################ ################ It is also necessary to purge old logs (or any storage size would not be enough): # expire_logs_days = 7 ### Another way to reduce binlogs size and amount with cron usage. Crontab line may looks like this one: # 1 0 * * * mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW(), INTERVAL 7 DAY);" ################ [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout ###---- my.conf example END


Last edit: 2012/3/07

WebMail Pro PHP documentation

Installation


Configuring WebMail

Customization

Integration

Synchronization

Clustering

See Also