aiphee/Mysql Performance.md

## Mysql Performance.md

      
    Raw
  

              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


MMyISAM 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

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