1) Increased innodb_write_io_thread from 4 to 16
2) Increased innodb_read_io_thread from 4 to 16
3) Altered innodb_flush_method=O_DIRECT
Sources:
http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-multiple_io_threads.htmlhttp://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
Question 1: Is my innodb_log_buffer_size too small?
Answer:
Since MySQL 5.0 there is a status called Innodb_log_waits. This status shows the number of times that the log buffer was too small. A wait is required for it to be flushed before continuing.
|
1 2 3 4 5 6 7 8 |
SHOW GLOBAL STATUS LIKE 'innodb_log_waits'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | Innodb_log_waits | 0 | +------------------+-------+ |
If this value is 0 or near innodb_log_buffer_size is defined well. If it is high and continuously growing, increase it or reduce the size of your transactions.
Question 2: What would be a better value?
Answer: https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
Use this to find out how much storage each engine uses:
|
1 2 3 4 5 6 7 8 9 10 |
mysql -e "select engine, SUM(DATA_LENGTH)/1024 as data_size, SUM(INDEX_LENGTH)/1024 as index_size, ((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024) as total_size from information_schema.tables group by engine;" +--------------------+-----------+------------+------------+ | engine | data_size | index_size | total_size | +--------------------+-----------+------------+------------+ | CSV | 0.0000 | 0.0000 | 0.0000 | | InnoDB | 480.0000 | 0.0000 | 480.0000 | | MEMORY | 0.0000 | 0.0000 | 0.0000 | | MyISAM | 0.0000 | 1.0000 | 1220.5859 | | PERFORMANCE_SCHEMA | 0.0000 | 0.0000 | 0.0000 | +--------------------+-----------+------------+------------+ |
From the data above, it may seem like a good idea to keep innodb_buffer_pool low, because we have so much MyISAM data.
BUT if I get one query a minute against that MyISAM data, and 500 queries per second against the considerably smaller InnoDB data-set, that doesn’t make sense anymore.
Then we’d want all of the InnoDB data in memory, hence a buffer pool of 512M+
It’s down to data usage and where the hotspots are!
|
1 2 3 4 5 |
wget http://www.percona.com/downloads/percona-toolkit/2.2.12/tarball/percona-toolkit-2.2.12.tar.gz tar -zxvf percona-toolkit-2.2.12.tar.gz -C /opt/ cd /opt/percona-toolkit-2.2.12/ perl Makefile.PL make |
Get the hotspots:
you can set NUMPACKETS or keep the tcpdump running for 20 minutes. JUST keep an eye on the file size!
|
1 2 3 4 |
NUMPACKETS=1000 tcpdump -s 65535 -x -nn -q -tttt -i any -c $NUMPACKETS port 3306 > /tmp/mysql.tcp.txt bin/pt-query-digest --type tcpdump /tmp/mysql.tcp.txt |
FYI: https://bugs.launchpad.net/percona-toolkit/+bug/1402776 official bug for that database being wrong thing
InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default
How to check the engine of tables for a database
|
1 |
mysql> select engine from tables.information_schema where table_schema='<DB_NAME>'; |
Summary of Engines’ space utilised
|
1 |
select engine,sum(index_length+data_length)/1024/1024,count(engine) from information_schema.tables group by engine; |
Check MyISAM tables
NOTE:
When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%
|
1 |
SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql") and engine = 'MyISAM'; |
Change all to InnoDB
https://support.rackspace.com/how-to/mysql-engines-myisam-vs-innodb/
When?
https://docs.dev4press.com/tutorial/wordpress/choosing-mysql-database-engine-myisam-or-innodb/
Quick answer: Always EXCEPT If you want to use FULLTEXT or SPATIAL indexes for some tables: in this case there is no choice and you must use MyISAM.
NOTE: FULLTEXT index support for InnoDB tables requires MySQL 5.6.4 or higherMariaDB 10.0.5 or higher
Highly recommend upgrading to MariaDB 10.0 as this is the 5.6 MySQL equivalent. It does not alter the data structures as 5.6 does and can be downgraded much more easily. It contains all the features of 5.6 plus more as well as being a drop-in replacement for MySQL in RHEL7.
I would point you to these KB articles:
https://mariadb.com/kb/en/mariadb-vs-mysql-features/https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/Repositories: https://downloads.mariadb.org/mariadb/repositories/#mirror=somerset
Check if there are FULLTEXT or SPATIAL indexes:
|
1 2 |
mysql> use information_schema; mysql> SELECT DISTINCT CONCAT(STATISTICS.TABLE_SCHEMA, '.', STATISTICS.TABLE_NAME) AS fulltext_table, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH)/1024/1024 AS size_M FROM STATISTICS INNER JOIN TABLES ON TABLES.TABLE_SCHEMA = STATISTICS.TABLE_SCHEMA AND TABLES.TABLE_NAME = STATISTICS.TABLE_NAME WHERE STATISTICS.INDEX_TYPE IN ("SPATIAL", "FULLTEXT") GROUP BY STATISTICS.TABLE_SCHEMA, STATISTICS.TABLE_NAME; |
Convert ALL MyISAM tables to InnoDB (all databases):
2 steps + backup
|
1 2 3 |
# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='MyISAM';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > rollback_all2Innodb.sql # mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > all2Innodb.sql # mysql < all2Innodb.sql |
(one liner – CAREFUL!)
|
1 |
# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' | mysql |
Convert MyISAM tables of a SPECIFIC database to InnoDB:
Backup and convert-file
|
1 2 3 4 |
# DB="<dbname>" # mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'MyISAM\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > rollback_$DB_all2Innodb.sql # mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'InnoDB\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > $DB_all2Innodb.sql |
NOTE: When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%