2014 August

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:
[code]
innodb_buffer_pool_size=24G
[/code]

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.
[code]
innodb_flush_log_at_trx_commit = 2
[/code]

3. innodb_log_file_size – The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.
[code]
innodb_log_file_size = 512M
[/code]
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.
[code]
innodb_log_buffer_size = 8M
[/code]

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:

[code]
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
[/code]

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

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

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.
[code]service mysql restart[/code]

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

[code]mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘pass’;
FLUSH PRIVILEGES;
[/code]

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

[code]
FLUSH TABLES READ LOCK;
SHOW MASTER STATUS;
[/code]
Save “Position” field.

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

[code]mysqldump -u root -p -f db1 > db1.sql[/code]
db1 – name of database, that you want to dump

Unlock database.
[code]UNLOCK TABLES;[/code]

Now it’s time to configure secondary server.

Secondary server

[code]CREATE DATABASE db1;[/code]

Import dump from first server:

[code]mysql -u root -p -f db1 < db1.sql[/code]

Edit MySQL config file /etc/my.cnf

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

Restart MySQL:
[code]service mysql restart[/code]

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

[code]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;[/code]

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

And start replacation:
[code]START SLAVE;[/code]

Look at status::
[code]SHOW SLAVE STATUS\G[/code]

Slave_IO_State: Waiting for master to send event

To stop replication use:
[code]STOP SLAVE[/code]

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

Tags: mysqlPosted by kenshi in Linux on August 4, 2014