SQL Tuner help?

xenTheory

Active member
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

Well-known member
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

Active member
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 :p

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

Well-known member
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

Active member
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

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