-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.16-log x86_64
Uptime = 2 days 6 hrs 5 min 55 sec
Avg. qps = 67
Total Questions = 13113961
Threads Connected = 3
INNODB STATUS
Current InnoDB index space = 1.59 G
Current InnoDB data space = 15.95 G
Current InnoDB buffer pool free = 1 %
Current innodb_buffer_pool_size = 16.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 17.59 G
Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 16.11 G
Configured Max Memory Limit : 22.98 G
Physical Memory : 23.52 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
No key reads?!
Seriously look into using some indexes
Configured Max Memory Limit : 22.98 GThat is going to starve the system of physical memory, I suggest decreasing that to at least 90%.
Physical Memory : 23.52 G
Max memory limit exceeds 90% of physical memory
the high load is because of mysql.68 is most definitely not a high load.
grep processor /proc/cpuinfo |tail -1
that is weird i dont see any setting in my.cnf as 22GBConfigured Max Per-thread Buffers : 6.87 G
[client]
port = 3306
socket = /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
[mysqld]
port = 3306
socket = /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
log_warnings
slow_query_log
long_query_time = 4
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
basedir=/opt/mysql5516
datadir=/opt/mysql-data
event-scheduler=on
general-log
slow-query-log
log-queries-not-using-indexes
expire_logs_days=7
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=0
innodb_use_sys_malloc=1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 16G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
log-error=/opt/mysql5516/mysql-error.log
pid-file=/opt/mysql5516/mysqld.pid
Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 16.11 G
Configured Max Memory Limit : 22.98 G
Perhaps Johann can expand on that :)
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 868B (Tables: 6)
[--] Data in InnoDB tables: 15G (Tables: 533)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 35
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 7h 4m 26s (13M q [67.701 qps], 807K conn, TX: 487B, RX: 16B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 16.2G global + 34.2M per thread (200 max threads)
[!!] Maximum possible memory usage: 22.9G (97% of installed RAM)
[OK] Slow queries: 4% (569K/13M)
[OK] Highest usage of available connections: 21% (43/200)
[OK] Key buffer size / total MyISAM indexes: 32.0M/125.0K
[OK] Key buffer hit rate: 100.0% (2K cached / 0 reads)
[OK] Query cache efficiency: 50.5% (4M cached / 8M selects)
[!!] Query cache prunes per day: 149729
[!!] Sorts requiring temporary tables: 11% (14K temp sorts / 118K sorts)
[!!] Joins performed without indexes: 213670
[OK] Temporary tables created on disk: 19% (15K on disk / 76K total)
[OK] Thread cache hit rate: 98% (13K created / 807K connections)
[!!] Table cache hit rate: 19% (1K open / 6K opened)
[OK] Open file limit used: 0% (70/8K)
[OK] Table locks acquired immediately: 100% (11M immediate / 11M locks)
[OK] InnoDB data size / buffer pool: 16.0G/16.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 64M)
sort_buffer_size (> 8M)
read_rnd_buffer_size (> 16M)
join_buffer_size (> 8.0M, or always use indexes with joins)
table_cache (> 2048)
per_thread_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections"
select *
from table1
where col1=5
and col2='foo'
and col3='bar'
I would first do a:
select count(distinct col1), count(distinct col2), count(distinct col3) from table1;
Then create an index with the column that has the highest distinct values as first e.g.
alter table table1 create index (col2, col1, col3);
Note that if a column has very few distinct values, it may not be worth indexing. If you're serious about performance suggest you check out the Oreilly High Performance MySQL book.