Training materials

Mysql Performance.md

HW + OS tuning

Disks

• Fsync performance: https://www.percona.com/blog/2018/02/08/fsync-performance-storage-devices/

STORAGE

• ZFS performance tuning: http://open-zfs.org/wiki/Performance_tuning#MySQL
• HW RAID configuration: https://www.percona.com/blog/2011/06/09/aligning-io-on-a-hard-disk-raid-the-theory/

Memory, connections, threads

Memory per connection

• How mysql allocates memory: https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

Global caches

Query cache

• Mariadb query cache info plugin: https://mariadb.com/kb/en/mariadb/query_cache_info-plugin/
• Is it really disabled?: https://www.percona.com/blog/2016/11/11/is-your-query-cache-really-disabled/

Key buffer size

• MyISAM meltdown: https://mariadb.org/myisam-table-scan-performance-kpti/

Innodb storage Engine

• innodb_thread_concurrency: https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-thread_concurrency.html
• innodb_numa_interleave: https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/
• innodb_log_file_size: https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/
• innodb_flush_method: https://www.percona.com/doc/percona-server/5.7/scalability/innodb_io.html#innodb_flush_method
• innodb_file_per_table: https://www.percona.com/community-blog/2019/07/23/impact-of-innodb_file_per_table-option-on-crash-recovery-time/

Mysql Scaling

Caching

ProxySQL query cache: https://www.percona.com/blog/2018/02/07/proxysql-query-cache/

Benchmarks

What to benchmark

Benchmarking tools

• mysqlslap invocation

mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200

# use already existing database
mysqlslap --create-schema="employees" --query="SELECT * FROM employees" --concurrency=100 --iterations=10

• sysbench installation

apt install sysbench

• sysbench basic tests

# CPU
sysbench cpu --cpu-max-prime=20000 run
# memory
sysbench memory --memory-block-size=1M --memory-total-size=10G run
# IO, firt prepare files, then test on them
sysbench fileio --file-total-size=5G --file-test-mode=rndrw prepare
sysbench fileio --file-total-size=5G --file-test-mode=rndrw --time=300 --max-requests=0 --report-interval=1 run

# OLTP
sysbench /usr/share/sysbench/oltp_insert.lua help
sysbench /usr/share/sysbench/oltp_insert.lua --table-size=1000000 --mysql-db=employees --mysql-user=root --mysql-password=ROOTPASSWORD prepare
sysbench /usr/share/sysbench/oltp_insert.lua --table-size=1000000 --mysql-db=employees --mysql-user=root --mysql-password=ROOTPASSWORD run
sysbench /usr/share/sysbench/oltp_insert.lua --table-size=1000000 --mysql-db=employees --mysql-user=root --mysql-password=ROOTPASSWORD cleanup

Performance schema

• Index of available tables: https://dev.mysql.com/doc/refman/5.7/en/performance-schema-table-index.html

• TASK: Try to look on unused indexes:

SELECT object_schema, object_name, index_name
 FROM performance_schema.table_io_waits_summary_by_index_usage
 WHERE index_name IS NOT NULL
 AND count_star = 0
 ORDER BY object_schema, object_name;

Table Maintenance

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