Essential MySQL server performance tuning

Most important MySQL server configuration options to improve performance on new server installation:

1. innodb_buffer_pool_size – Amount of memory MySQL will use for data cache.
Set to ~80% of server RAM. (If server is dedicated for DB). Example for server with 32GB RAM:

innodb_buffer_pool_size=24G

2. innodb_flush_log_at_trx_commit = 2 – log writes will happen once per second instead of every commit. Major I/O bottleneck with many writes.

innodb_flush_log_at_trx_commit = 2

3. innodb_log_file_size – The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.

innodb_log_file_size = 512M

After changing the value, gracefully shutdown MySQL and move /var/lib/mysql/ib_logfile* somewhere safe just in case. If MySQL starts successfully, delete moved files.

4. innodb_log_buffer_size – enables large transactions to run without writing the log to disk before the transactions commit, again – saving disk I/O. Default = 1MB.

innodb_log_buffer_size = 8M

5. innodb_file_per_table – Not a performance setting, however a very important parameter to set right after server installation as it will be hard to change it later.

In file-per-table mode, each newly created table will have its own data file, that will allow to reclaim used space after table deletion, and have other advantages

Summary:

innodb_buffer_pool_size=24G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_file_per_table

MySQL Replication configuration example

Database replication will synchronize databases between two different servers.
Should any record change on primary server, it will be automatically sent to secondary (slave) server.

  • Primary server
  • Edit MySQL config file /etc/my.cnf

    server-id=1
    log_bin=/var/log/mysql/mysql-bin.log
    

    log_bin – specifies the location of MySQL binary log, which is used for replication.

    You can limit the binary logging to only one database by specifying parameter binlog_do_db=database .
    However this is not recommended, see (dev.mysql.com )

    Save changes and restart MySQL server.

    service mysql restart

    Now we should grant privileges on slave user which will be used for replcation.

    mysql -u root -p
    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'pass';
    FLUSH PRIVILEGES;
    

    Now lock and dump DB to the file to transfer it to the second server.

    FLUSH TABLES READ LOCK;
    SHOW MASTER STATUS;
    

    Save “Position” field.

    If you do something in this window database will automatic unlock,
    you should open new window and do it there:

    mysqldump -u root -p -f db1 > db1.sql

    db1 – name of database, that you want to dump

    Unlock database.

    UNLOCK TABLES;

    Now it’s time to configure secondary server.

  • Secondary server
  • CREATE DATABASE db1;

    Import dump from first server:

    mysql -u root -p -f db1 < db1.sql

    Edit MySQL config file /etc/my.cnf

    server-id=2
    relay-log=/var/log/mysql/mysql-relay-bin.log
    log_bin=/var/log/mysql/mysql-bin.log
    

    Restart MySQL:

    service mysql restart

    Server configured and ready for replication.
    To activate replication on slave server execute following command in mysql console:

    CHANGE MASTER TO
    MASTER_HOST='IP-address or primary server',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=XXX;

    Remember “Position” field from “SHOW MASTER STATUS” command and write value in place of XXX.

    And start replacation:

    START SLAVE;

    Look at status::

    SHOW SLAVE STATUS\G

    Slave_IO_State: Waiting for master to send event

    To stop replication use:

    STOP SLAVE

    PS: MySQL uses TCP port 3306 for communication, so it should be open between them.