2014 August

Essential MySQL server performance tuning

Aug/14

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

Aug/14

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.

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