mySQL performance tuning

Hello all,

We’ve had iworx running for a couple of years now with no real issues, but recently we have a developer that is hosting a site that has started to get lots of hits. He’s been complaining about 7-9 sec page load times that he find unacceptable.

The machine itself has 12 GB of RAM and is a quad core intel proc. When I look at the resources it’s not being taxed in the least. My question is could someone here who is why smarter than me in mySQL tell me what good values would be in the options panel. I’m totally stuck at this point.

Attached you will find my current values.

Would the 12 sec load times come from the server or from the shoddy code? Of course the dev says that it’s not his code, but I have no way to prove that.

Optimizing MySQL

I’d recommend downloading MySQL Tuner, it will help you. I am guessing with that many connections set, you do not have enough RAM available. The script will tell you what the max ram is for your settings.

You can download MySQL Tuner script here:
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

Another good resource is MySQL WorkBench (free from MySQL’s website) which you can download directly from MySQL and you can watch connection statistics in real time. I have many busy servers and it’s rare for connections to get over 50-100.

Another good tip is to turn on the MySQL Slow Query Log. You can set it for something like 2 seconds and it will show you any query that’s taking too long. Then you can look at those queries in MySQL using the EXPLAIN statement to see what might be happening. Another MySQL logging thing to look at is Queries Not Using Indexes.

Here’s my.cnf file settings for a relatively busy server with about the same amount of ram:

max_connections=200
max_allowed_packet=16M
key_buffer=100M
join_buffer=16M
sort_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=32M
thread_cache_size=48
query_cache_limit=2M
query_cache_size=300M
query_cache_type=1
table_cache=32637
thread_concurrency=8
interactive_timeout=14400
wait_timeout=30
connect_timeout=30
max_connect_errors=99999999999
skip_name_resolve
tmp_table_size=768M
max_heap_table_size=256M
default-storage-engine=MyISAM

Here’s a sample output of MySQL Tuner from my Server:

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

-------- Storage Engine Statistics -------------------------------------------
[–] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 4G (Tables: 216)
[–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 2

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[–] Up for: 19h 23m 10s (801K q [11.487 qps], 143K conn, TX: 1B, RX: 90M)
[–] Reads / Writes: 90% / 10%
[–] Total buffers: 656.0M global + 58.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 12.0G (75% of installed RAM)
[OK] Slow queries: 3% (25K/801K)
[OK] Highest usage of available connections: 19% (38/200)
[OK] Key buffer size / total MyISAM indexes: 100.0M/2.7G
[OK] Key buffer hit rate: 100.0% (5M cached / 2K reads)
[OK] Query cache efficiency: 67.8% (235K cached / 347K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 59K sorts)
[!!] Temporary tables created on disk: 45% (34K on disk / 76K total)
[OK] Thread cache hit rate: 99% (38 created / 143K connections)
[OK] Table cache hit rate: 88% (271 open / 306 opened)
[OK] Open file limit used: 0% (496/65K)
[OK] Table locks acquired immediately: 99% (113K immediate / 113K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses