MySQL Tuning advice

[marklcfc]

I run a fairly busy forum, and am looking for ways to improve the speed of my site. I have ran mysqltuner and here are my current settings, what changes are recommended?

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 216)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 30

-------- Security Recommendations -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user 'xxx'@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 3h 44m 38s (9M q [22.437 qps], 589K conn, TX: 231B, RX: 7B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (18% of installed RAM)
[OK] Slow queries: 0% (107/9M)
[OK] Highest usage of available connections: 84% (424/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/747.4M
[OK] Key buffer hit rate: 99.2% (712M cached / 5M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (295 temp sorts / 100K sorts)
[!!] Temporary tables created on disk: 27% (15K on disk / 57K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 147K opened)
[OK] Open file limit used: 4% (114/2K)
[OK] Table locks acquired immediately: 99% (13M immediate / 13M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_cache (> 64)

[Clickfinity]

Can you post the contents of your my.cnf file and let us know the details of the machine (proc/mem/OS/etc.) as giving advice based purely on the mysqltuner shortlist wouldn't be advisable ... :thumbsup:

Cheers,
Shaun :D

[marklcfc]

What to I put into SSH to edit .my.cnf?

I just did this

root@server [~]# cat /etc/my.cnf

[mysqld]

set-variable = max_connections=500

safe-show-database

Doesn't seem to show much unless thats all thats set?

[marklcfc]

But I'm running Linux with a AMD Phenom II X4 840 Processor and 8GB RAM.

[marklcfc]

I've been trying to make my.cnf the following.

max_connections=500

safe-show-database

key_buffer = 312M

max_allowed_packet = 64M

thread_cache_size = 128

max_connections = 500

table_cache = 2048

table_definition_cache = 1200

tmp_table_size = 128M

max_heap_table_size = 128M

sort_buffer_size = 4M

read_buffer_size = 256K

read_rnd_buffer_size = 256K

join_buffer_size = 2M

query_cache_limit = 3M

query_cache_size = 128M

query_cache_type = 1

But after saving and trying to run service mysql restart, I'm recieving an error

Starting MySQL.Manager of pid-file quit without updating fi[FAILED]
root@server [~]#

Which means MYSQL has gone down and won't restart, I fixed it by removed all those changes I made. Am I doing something wrong ??

[marklcfc]

Well after doing some testing, it turns out table_definition_cache=1200 was causing mysql to fail.

My.cnf is currently set up as the following


max_connections=500
safe-show-database
key_buffer=312M
max_allowed_packet=64M
thread_cache_size=128
table_cache=2048
tmp_table_size=128M
max_heap_table_size=128M
sort_buffer_size=4M
read_buffer_size=256K
read_rnd_buffer_size=256K
join_buffer_size=2M
query_cache_limit=3M
query_cache_size=128M
query_cache_type=1

Any advice what to add/change?

[Clickfinity]

Well after doing some testing, it turns out table_definition_cache=1200 was causing mysql to fail.

My.cnf is currently set up as the following

max_connections=600

safe-show-database

key_buffer=1024M

max_allowed_packet=64M

thread_cache_size=128

table_cache=3500

tmp_table_size=128M

max_heap_table_size=128M

sort_buffer_size=4M

read_buffer_size=256K

read_rnd_buffer_size=256K

join_buffer_size=2M

query_cache_limit=3M

query_cache_size=128M

query_cache_type=1

Any advice what to add/change?

Backup my.cnf and then try it with these changes.

There are other things that can speed up your site more dramatically than tweaking MySQL - do you have a file and/or Opcode cache installed? Such as Memcached (file cache), or APC / eAccellerator (Opcode cache)?

Cheers,
Shaun :D

[marklcfc]

I had eaccellerater installed for a few months and it was great when it worked, but I had some problems where memory usage kept rising upto 80% and higher and consequently the server crashed. This happened almost daily. That did continue after I switched it off and I had to have hardware replaced, whether eaccellerater was the cause or not I don't know, but I wasn't having any crashes previous to that.

Also I have noticed an increase in performance. Any other suggestions are most welcome :smile:

[Clickfinity]

What web server are you using - there may be some optimisations you could do there too, such as expires headers for images/CSS?

[marklcfc]

Linux, AMD Phenom™ II X4 840 Processor and 8GB RAM.

[Clickfinity]

Linux, AMD Phenom™ II X4 840 Processor and 8GB RAM.

Web server software - Apache, nginx, lighttpd, etc.?

[marklcfc]

Apache/2.0.63

[Clickfinity]

Apache/2.0.63

Okay, good, so you should be able to use Expires: http://httpd.apache....od_expires.html

You'll need to ensure mod_expires is enabled then add this to the bottom of your httpd.conf file (backup the file first):

# Improve caching - Load images, css, and javascript from visitors

# browser for 30 days before re-caching


ExpiresActive on

ExpiresByType text/css "access plus 1 day"

ExpiresByType text/javascript "access plus 1 month"

ExpiresByType application/javascript "access plus 1 month"

ExpiresByType application/x-javascript "access plus 1 month"

ExpiresByType application/x-shockwave-flash "access plus 1 month"

ExpiresByType image/gif "access plus 1 month"

ExpiresByType image/jpg "access plus 1 month"

ExpiresByType image/jpeg "access plus 1 month"

ExpiresByType image/png "access plus 1 month"

ExpiresByType image/ico "access plus 1 month"

ExpiresByType image/icon "access plus 1 month"

Obviously you can change the refresh timings to suit your site/setup, but this basically stores the files on the visitors browser and loads them from the local (computer) cache rather than from the server each time. Once the expires time is up, a new copy will be downloaded from the server and cached for whatever period you have set in httpd.conf.

Caveat - it has an occasional side effect that you need to be aware of; the visitor's browser can continue to use the cached data even when you've actually updated it on the server - particularly pesky when someone changes their avatar and the old one keeps appearing - but all it needs to "cure" it is a forced-refresh by the browser (to update all the cached files) and that sorts the problem out. :smile:

Cheers,
Shaun :D

[marklcfc]

Would that jpg/png/gif 1 month setting mean that photos are not updated immediately once changed?

[marklcfc]

Don't approve the above post please, hadn't read all of Cyclechats post

[Luke]

eAccelerator is bad. Use APC or xcache.

[Swiftie]

eAccelerator is bad. Use APC or xcache.

Bad in what sense?

[Clickfinity]

eAccelerator is bad. Use APC or xcache.

If you're going to make such statements it is useful to the other thread participants (in particular those with less experience of tuning their server) to explain why something is bad or unsuitable; or in what situation or combination/setup.

I used eAccellerator for my IPB install on an LAMP server with no issues at all (it ran a little bit more efficiently than APC in the trials I ran!!). :)

Cheers,
Shaun :D

[Luke]

eAccelerator isn't as well maintained anymore. Meaning support for more up to date version of PHP is difficult. I've had eAccelerator break on a minor release to PHP. In fact the website doesn't exist anymore. APC/xcache are much better maintained and have better APIs. APC can store objects in memory without serialization. APC is supposedly going to be included into PHP 6. Overall, APC is the better choice. xcache works rather good as well.

[marklcfc]

This is my current set up, any tips? My server still slows up once I'm getting 350-400 connections every 5 minutes.

max_connections=600
safe-show-database
key_buffer=512M
max_allowed_packet=64M
thread_cache_size=128
table_cache=3500
tmp_table_size=256M
max_heap_table_size=128M
sort_buffer_size=4M
read_buffer_size=256K
read_rnd_buffer_size=256K
join_buffer_size=2M
query_cache_limit=3M
query_cache_size=256M
query_cache_type=1

[Grumpy]

Um... looking at this...
But I'm running Linux with a AMD Phenom™ II X4 840 Processor and 8GB RAM.
Are you getting your service from server4you by any chance?? If so, they're using green drives. No matter how much you try to optimize, the IOPS is too low on writes with something like raid 1. You'll likely hit the disk saturation pretty quick once you get a fair bit of visitors.

You should show your iostats during your peaks.
iostat -d -x 60 2
(post only the 2nd one. first is meaningless. 2nd will take 60 seconds to output)
If my guess is right, your await value is likely too high (200+).

Without upgrading your hardware, you can attempt to leverage you ram more if your database is small-ish by converting to innodb and increasing the max ram available for innodb cache ridiculously high (not higher than physically avl ofc). Innodb has write caching which will alleviate the issue a bit.

You should make your tmp table size and max heap table size equal (both 256M). If heap is smaller than tmp table size, tmp table size will either be reduced automatically to 128M (usual behavior) or it'll start using disk -- which is (performance wise) LOT worse than not having any of it.

I can't suggest anymore than that because there's serious lack of information you've given.

[Gary.]

You cannot tune a mysql server in the space of a hour, We specialize in this and that can take days to fully complete and even then your site could have grown or got busy which would mean more changes are required.

Ok yes, I can instantly tune and it will have a big impact overall but I could not tell you the true performance without it being run for a good 24 hours or even run a full benchmark.

And having max_connections=600 is rather high if you ask me, If your hitting those sort of myqsl connections for an IPB site then your apache pipe must be heavily filled, You must note that the apache connections are very different to mysql connections and do not run identical !


Gary.

[marklcfc]

Going quite slow again, just in page loads. Server load (below 1.00) and memory usage (30%) seem fine.

[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 636)
[!!] Total fragmented tables: 116

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 1h 32m 36s (8M q [48.679 qps], 477K conn, TX: 183B, RX: 3B)
[--] Reads / Writes: 65% / 35%
[--] Total buffers: 1.5G global + 6.8M per thread (600 max threads)
[OK] Maximum possible memory usage: 5.5G (72% of installed RAM)
[OK] Slow queries: 0% (11/8M)
[OK] Highest usage of available connections: 11% (67/600)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G
[OK] Key buffer hit rate: 99.9% (324M cached / 166K reads)
[OK] Query cache efficiency: 50.6% (3M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (130 temp sorts / 45K sorts)
[OK] Temporary tables created on disk: 18% (2K on disk / 15K total)
[OK] Thread cache hit rate: 99% (67 created / 477K connections)
[OK] Table cache hit rate: 84% (1K open / 1K opened)
[OK] Open file limit used: 26% (1K/7K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries


max_connections=600
skip-innodb
safe-show-database
key_buffer=1024M
max_allowed_packet=64M
thread_cache_size=128
table_cache=3500
tmp_table_size=256M
max_heap_table_size=256M
sort_buffer_size=4M
read_buffer_size=256K
read_rnd_buffer_size=256K
join_buffer_size=2M
query_cache_limit=3M
query_cache_size=256M
query_cache_type=1

[Grumpy]

Post the SECOND stat of
iostat -x 60 2
(wait 60 seconds after you execute command) while your site is slow.
(and use something like

 tags so it doesn't mess up spacings...)

[Gary.]

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

Enable the slow query log to troubleshoot bad queries

The first one, Do as it asks you, Issue this in SSH.

mysqlcheck -u root --optimize --all-databases

The second one, Add this to your my.cnf file, Anywhere under the [mysqld]

log-slow-queries=/var/lib/mysql/slow.log

Save and restart your mysql server and wait around 24 hours.


Page load is not always Mysql, It could be your apache and possibly running suPHP, I use mod_fcgi for IPB forums as it will speed thing's up, If you have some free memory then it's worth a shot :)