Home / server / Questions / 497672
Accepted
Vojtěch
Asked: 2013-04-10 07:31:08 +0800 CST

Tuning MySQL of 1.3 G DB, 8GB Ram, High IO Wait

  • 772

I am having MySQL database of size 1.3 GB on 8 core virtual machine, 8 GB of memory and I am having troubles optimizing the configuration. There is a lot of small rows and lot of joins going on.

I've found about this tool: https://tools.percona.com/wizard, but the differences it has suggested are only worsening the result. Primarily they suggest increasing InnoDB buffer pool to 6 GB. Currently I am having 1 GB buffer pool, but when I try to increase it, the performance is actually worse. Why is it happening?

Is there a way to force MySQL to cache the whole DB in memory, so it would not be stuck on such high levels of IO Wait?

The rest of the settings does not seem to make such difference.

Current state of my.cnf: https://gist.github.com/knyttl/ac6efe5c0730dd34a5cc

mysql

1 Answers

  1. Best Answer

    Looking at your my.cnf, you have defaults for many things. What you desperately need is a full Cleanup of the InnoDB Infrastructure. Click here to see the Pictorial Representation of the InnoDB Infrastructure.

    STEP 01) Run this query

    SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

    This will tell you how big to set innodb_buffer_pool_size (in MB).

    Let's say that answer came up 1024.

    STEP 02) Run this query

    SELECT CEILING(Total_MYI_Bytes/POWER(1024,2)) RKBS FROM
(SELECT SUM(index_length) Total_MYI_Bytes FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN
('information_schema','performance_schema','mysql')) A;

    This will tell you how big to set key_buffer_size (in MB).

    Let's say that answer came up 64.

    STEP 03 : Run this query

    SET GLOBAL innodb_fast_shutdown = 0;

    This will perform a full transactional commit of any Transactions leftover in the Transaction Logs during the time of shutdown. The Transaction Logs are

    • /var/lib/mysql/ib_logfile0
    • /var/lib/mysql/ib_logfile1

    STEP 04 : Dump all data out to a text file

    cd /root
mkdir datadump
cd datadump
MYSQLDUMP_CONNECT="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --flush-privileges --all-databases"
mysqldump ${MYSQLDUMP_CONNECT} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

    STEP 05 : Shutdown MySQL

    service mysql stop

    STEP 06 : Add the following to my.cnf

    If you are using MySQL 5.5 (or MySQL 5.1 with InnoDB Plugin Enabled), add these

    [mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_read_io_threads=16
innodb_write_io_threads=16
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

    If you are using MySQL 5.0, add these

    [mysqld]
key_buffer_size=64M
innodb_file_per_table
innodb_open_files=1000
innodb_buffer_pool_size=1024M
innodb_log_file_size=256M
innodb_fast_shutdown=0
innodb_thread_concurrency=0
join_buffer_size=4M
read_buffer_size=4M
read_rndbuffer_size=4M

    Please note I chose innodb_log_file_size to 25% of innodb_buffer_pool_size

    STEP 07 : Make room for new InnoDB Files

    cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile`.bak
mv ibdata1 ib_data1.bak

    STEP 08 : Start MySQL

    service mysql start

    STEP 09 : Load the Data back

    mysql -uroot -p < /root/datadump/MySQLData.sql

    STEP 10 : If everything is running better, then run this:

    rm -f ib_logfile0.bak
rm -f ib_logfile1.bak
rm -f ibdat1.bak

    All done with InnoDB Cleanup.

    I have suggested InnoDB improvements like this many times before

    Give it a Try !!!

    • 4