

Ask HN: MySQL Tuning for Performance Suggestions/Help - brianjking

I have a LiquidWeb Storm Server running CentOS 64 bit version 6.5 with a single 8 core CPU (AMD FX-8350) and 31gb of memory with a SSD.<p>I&#x27;m currently running Apache 2.2.29&#x2F;PHP 5.5&#x2F;MySQL 5.5 and host around 30 sites, two of which are fairly high throughput WordPress sites built in WordPress.<p>MySQL Tuner is reporting large #&#x27;s of fragmented tables and rather high (15-25%) connections aborted. I&#x27;m looking for advice on my.cnf configuration tweaks that I can put in place to tune the system for maximum performance.<p>Thanks!<p>&gt;&gt;  MySQLTuner 1.3.0 - Major Hayden &lt;major@mhtx.net&gt;
 &gt;&gt;  Bug reports, feature requests, and downloads at http:&#x2F;&#x2F;mysqltuner.com&#x2F;
 &gt;&gt;  Run with &#x27;--help&#x27; for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.40-cll
[!!] Total fragmented tables: 574<p>-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned<p>-------- 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&#x2F;300)
[OK] Key buffer size &#x2F; total MyISAM indexes: 384.0M&#x2F;290.8M
[OK] Key buffer hit rate: 99.9% (110M cached &#x2F; 130K reads)
[OK] Query cache efficiency: 66.5% (7M cached &#x2F; 11M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (16 temp sorts &#x2F; 99K sorts)
[!!] Joins performed without indexes: 1637
[OK] Temporary tables created on disk: 21% (68K on disk &#x2F; 321K total)
[OK] Thread cache hit rate: 99% (26 created &#x2F; 222K connections)
[!!] Table cache hit rate: 3% (3K open &#x2F; 80K opened)
[OK] Open file limit used: 32% (5K&#x2F;16K)
[OK] Table locks acquired immediately: 99% (7M immediate &#x2F; 7M locks)
[!!] Connections aborted: 17%
======
houk
e-mail is in profile. shoot me a message.

