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!
Percona Toolkit to ‘get the hotspots’
|
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