mySQL Optimization Suggestions
- by Brian Schroeter
I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are taking a long time (20,000+ ms) in some categories.
I ran MySQLTuner 1.3.0 and got the following results... (Disclaimer: I restarted mySQL earlier after tweaking some settings, and so the results here may not be 100% accurate):
>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.37-35.0
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 7G (Tables: 332)
[--] Data in InnoDB tables: 213G (Tables: 8714)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 353)
[!!] Total fragmented tables: 5492
-------- Security Recommendations -------------------------------------------
[!!] User '@host5.server1.autopartsnetwork.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'root@%' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 3m 4s (5M q [317.443 qps], 42K conn, TX: 18B, RX: 2B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 35.5G global + 184.5M per thread (1024 max threads)
[!!] Maximum possible memory usage: 220.0G (174% of installed RAM)
[OK] Slow queries: 0% (6K/5M)
[OK] Highest usage of available connections: 5% (61/1024)
[OK] Key buffer size / total MyISAM indexes: 512.0M/3.1G
[OK] Key buffer hit rate: 100.0% (102M cached / 45K reads)
[OK] Query cache efficiency: 66.9% (3M cached / 5M selects)
[!!] Query cache prunes per day: 3486361
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 812K sorts)
[!!] Joins performed without indexes: 1328
[OK] Temporary tables created on disk: 11% (126K on disk / 1M total)
[OK] Thread cache hit rate: 99% (61 created / 42K connections)
[!!] Table cache hit rate: 19% (9K open / 49K opened)
[OK] Open file limit used: 2% (712/25K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
[!!] InnoDB buffer pool / data size: 32.0G/213.4G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 128.0M, or always use indexes with joins)
table_cache (> 12288)
innodb_buffer_pool_size (>= 213G)
My my.cnf configuration is as follows...
[client]
port = 3306
[mysqld_safe]
nice = 0
[mysqld]
tmpdir = /var/lib/mysql/tmp
user = mysql
port = 3306
skip-external-locking
character-set-server = utf8
collation-server = utf8_general_ci
event_scheduler = 0
key_buffer = 512M
max_allowed_packet = 64M
thread_stack = 512K
thread_cache_size = 512
sort_buffer_size = 24M
read_buffer_size = 8M
read_rnd_buffer_size = 24M
join_buffer_size = 128M
# for some nightly processes client sessions set the join buffer to 8 GB
auto-increment-increment = 1
auto-increment-offset = 1
myisam-recover = BACKUP
max_connections = 1024
# max connect errors artificially high to support behaviors of NetScaler monitors
max_connect_errors = 999999
concurrent_insert = 2
connect_timeout = 5
wait_timeout = 180
net_read_timeout = 120
net_write_timeout = 120
back_log = 128
# this table_open_cache might be too low because of MySQL bugs #16244691 and #65384)
table_open_cache = 12288
tmp_table_size = 512M
max_heap_table_size = 512M
bulk_insert_buffer_size = 512M
open-files-limit = 8192
open-files = 1024
query_cache_type = 1
# large query limit supports SOAP and REST API integrations
query_cache_limit = 4M
# larger than 512 MB query cache size is problematic; this is typically ~60% full
query_cache_size = 512M
# set to true on read slaves
read_only = false
slow_query_log_file = /var/log/mysql/slow.log
slow_query_log = 0
long_query_time = 0.2
expire_logs_days = 10
max_binlog_size = 1024M
binlog_cache_size = 32K
sync_binlog = 0
# SSD RAID10 technically has a write capacity of 10000 IOPS
innodb_io_capacity = 400
innodb_file_per_table
innodb_table_locks = true
innodb_lock_wait_timeout = 30
# These servers have 80 CPU threads; match 1:1
innodb_thread_concurrency = 48
innodb_commit_concurrency = 2
innodb_support_xa = true
innodb_buffer_pool_size = 32G
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2G
skip-federated
[mysqldump]
quick
quote-names
single-transaction
max_allowed_packet = 64M
I have a monster of a server here to power our site because our catalog is very large (300,000 simple SKUs), and I'm just wondering if I'm missing anything that I can configure further. :-)
Thanks!
