SQL Tuner help?

[xenTheory]

The largest database on the server my forum is on is the xenForo database at 600MB. I've run MySQLTuner and these are the results, I think it's set up well enough but not really too sure. Any pointers would be appreciated.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 451M (Tables: 1129)
[--] Data in InnoDB tables: 819M (Tables: 1840)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 745K (Tables: 29)
[!!] Total fragmented tables: 183

-------- Performance Metrics -------------------------------------------------
[--] Up for: 33d 21h 28m 26s (74M q [25.533 qps], 3M conn, TX: 3873B, RX: 30B)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 1.3G global + 3.6M per thread (5000 max threads)
[OK] Maximum possible memory usage: 19.0G (81% of installed RAM)
[OK] Slow queries: 1% (1M/74M)
[OK] Highest usage of available connections: 1% (95/5000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/91.7M
[OK] Key buffer hit rate: 99.9% (356M cached / 417K reads)
[OK] Query cache efficiency: 83.2% (47M cached / 56M selects)
[!!] Query cache prunes per day: 4033
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 952K sorts)
[!!] Joins performed without indexes: 423115
[!!] Temporary tables created on disk: 39% (736K on disk / 1M total)
[OK] Thread cache hit rate: 99% (95 created / 3M connections)
[!!] Table cache hit rate: 18% (3K open / 17K opened)
[OK] Open file limit used: 16% (2K/14K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)
[OK] InnoDB data size / buffer pool: 819.5M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above]
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 192M)
max_heap_table_size (> 24M)
table_cache (> 524288)

 

[Walter]

The most important issue with your config is your RAM usage.

Is this a dedicated database server? If not, 81% of your ram is dedicated to Mysql which is too much. You only have 19% for OS, webserver, PHP, cache, applications...

Your highest usage of connections is only 1% so you are probably wasting a lot of memory. Do you really need 5000 concurrent connections? Remember, 5000 concurrent connections is not equal 5000 concurrent users as not every users is loading a page at the same time. If you adjust your connections to 5000 instead of 5000 Mysql will use much less memory.

 

[xenTheory]

Hi Walter and thank you for replying.

Yes, it's a dedicated server - one I use only for my own sites. I have customers' sites on other server but those are correctly configured for a shared environment and run well. It's just this one that I'm confused with

I thought it might be useful to paste the my.cnf I've made for this server @Walter

[mysqld]
local-infile=0
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=14382
max_user_connections=3000
max_connections=5000
thread_cache_size=1000
innodb_buffer_pool_size=1024M
query_cache_size=256M
table-cache=512M
tmp_table_size=192M
join_buffer_size=1M
max_heap_table_size=24M
log_slow_queries=/var/log/mysql/mysql-slow.log
long_query_time=3
log-queries-not-using-indexes

If you have the time, and would mind taking a look at the above, I would greatly appreciate it. Again thank you.

 

[Walter]

You haven't really answered my two questions
(no pun intended)

Is this a dedicated database server (=only used as a database server = has no web server or other apps on it)?
Same about my question regarding connections.

 

[xenTheory]

Sorry @Walter

It's not a dedicated database server and I don't think 5000 is really needed but I'm not too sure - for my other servers I use a very simple set up and that works for purpose.

 

[MattW]

5,000 is a rather high number! You could probably set it down 1,000 (even lower than that more than likely)

 

[Walter]

Exactly

 

[dbembibre]

You can down max_connections to 500 without problems.