head.daveops.net

Snippets for yer computer needs

performance

.. TODO Avoid NULL when possible (forget why)

indexing

Check the slow query log

Enable this for debugging, don’t leave it running if you don’t need it.

set global slow_query_log = ON|OFF
set global slow_query_log_file = file_name

# Plain-jane MySQL mysqldumpslow /var/log/mysql/mysql-slow.log # Percona toolkit (https://www.percona.com/software/database-tools/percona-toolkit) pt-query-digest /var/log/mysql/mysql-slow.log

Dealing with fragmentation

-- size in MB
select ENGINE, TABLE_NAME, Round(DATA_LENGTH/1024/1024) as data_length, round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/1024/1024) as data_free, (data_free/(index_length+data_length)) as frag_ratio from information_schema.tables where DATA_FREE > 0 order by frag_ratio desc;

Optimize table

optimize table <tbl>;

https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

Good ways to benchmark

.. TODO - cleanup

mysqlslap

Emulates client load

Run profiling

set profiling = 1;
* run query *
show profile;

Resources