Ask HN: MySQL Tuning for Performance Suggestions/Help
I'm currently running Apache 2.2.29/PHP 5.5/MySQL 5.5 and host around 30 sites, two of which are fairly high throughput WordPress sites built in WordPress.
MySQL Tuner is reporting large #'s of fragmented tables and rather high (15-25%) connections aborted. I'm looking for advice on my.cnf configuration tweaks that I can put in place to tune the system for maximum performance.
Thanks!
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> 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.40-cll [!!] Total fragmented tables: 574
-------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned
-------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 22h 31m 46s (15M q [93.205 qps], 222K conn, TX: 431B, RX: 11B) [--] Total buffers: 1.1G global + 16.4M per thread (300 max threads) [OK] Maximum possible memory usage: 5.9G (19% of installed RAM) [OK] Highest usage of available connections: 8% (26/300) [OK] Key buffer size / total MyISAM indexes: 384.0M/290.8M [OK] Key buffer hit rate: 99.9% (110M cached / 130K reads) [OK] Query cache efficiency: 66.5% (7M cached / 11M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (16 temp sorts / 99K sorts) [!!] Joins performed without indexes: 1637 [OK] Temporary tables created on disk: 21% (68K on disk / 321K total) [OK] Thread cache hit rate: 99% (26 created / 222K connections) [!!] Table cache hit rate: 3% (3K open / 80K opened) [OK] Open file limit used: 32% (5K/16K) [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) [!!] Connections aborted: 17%