Tag Archives: tuning

MySQL – How much memory for InnoDB buffer?

Use this to find out how much storage each engine uses:

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!

Percona Toolkit to ‘get the hotspots’

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!

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