- Fsync performance: https://www.percona.com/blog/2018/02/08/fsync-performance-storage-devices/
- 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/
- How mysql allocates memory: https://dev.mysql.com/doc/refman/5.7/en/memory-use.html
- 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/
- MMyISAM meltdown: https://mariadb.org/myisam-table-scan-performance-kpti/
- 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
ProxySQL query cache: https://www.percona.com/blog/2018/02/07/proxysql-query-cache/
- 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
-
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;
- OPTIMIZE TABLE: https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html