Skip to content

Instantly share code, notes, and snippets.

@aiphee
Forked from davidkarban/Mysql Performance.md
Created June 11, 2019 08:11
Show Gist options
  • Save aiphee/edffb4409998b7bdd4981da5dd5ffbec to your computer and use it in GitHub Desktop.
Save aiphee/edffb4409998b7bdd4981da5dd5ffbec to your computer and use it in GitHub Desktop.
Training materials

HW + OS tuning

Disks

STORAGE

Memory, connections, threads

Memory per connection

Global caches

Query cache

Key buffer size

Innodb storage Engine

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment