Do you see the recommendations in the report? Namely:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Then
*** MySQL’s maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables
*** max_connections (> 750) query_cache_size (> 128M) join_buffer_size (> 128.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 33M)
The most obvious things to look at first is your using 162% of your 100% or RAM. Anytime you pass your maximum threshold of RAM then the server needs to SWAP. Swapping causes it to use the hard drive as a VERY SLOW type of RAM. This is further compounded if the drive is not defragmented. You are advised to bump up the RAM more than double what you have now; or optimize how your forum is utilizing RAM; however this is far more difficult then just adding more RAM.
Then the next culprit could be that you have a large number of non-indexed JOINS. JOINS slow down queries and they really slow them down if you don’t put indexes on columns that are being searched or lookup. You could add indexes to your JOINED database tables.
You may want to enable the slow query log to see if any of the actual queries could be optimized.
Personally I would go with the RAM first, especially if optimizing MySQL and Servers are not your forte, next I would index my joined tables.
Look in your slow queries log. For any queries that show up repeatedly provide us the query as well as table(s) structure including indexes.
We’d know more info in order to help you.
No problem. If you do post in the wrong forum, the best solution is to click the red flag next to your post and ask a moderator to move it for you. Saves confusion. [/ot]
It’s been tuned. Tuning included disabling persistent connections, greatly reducing connection timeout limits, etc. The site is faster and it now looks like this (24 hours has not passed yet):
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 3m 44s (1M q [76.885 qps], 62K conn, TX: 33B, RX: 676M)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 5.2G global + 9.2M per thread (400 max threads)
[!!] Maximum possible memory usage: 8.8G (112% of installed RAM)
[OK] Slow queries: 0% (30/1M)
[OK] Highest usage of available connections: 14% (59/400)
[OK] Key buffer size / total MyISAM indexes: 4.9G/4.4G
[OK] Key buffer hit rate: 99.8% (154M cached / 289K reads)
[OK] Query cache efficiency: 60.6% (580K cached / 958K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (1K temp sorts / 99K sorts)
[!!] Joins performed without indexes: 1050
[OK] Temporary tables created on disk: 3% (1K on disk / 39K total)
[OK] Thread cache hit rate: 99% (59 created / 62K connections)
[OK] Table cache hit rate: 97% (3K open / 3K opened)
[OK] Open file limit used: 38% (3K/8K)
[OK] Table locks acquired immediately: 98% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 33.1M/40.0M
-------- 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
Adjust your join queries to always utilize indexes
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 2.0M, or always use indexes with joins)
Does RAM need to be added or can I just leave it like this? There’s 4GB or swap available.
