my.cnf recommendations please

[rct2·com]

We have a small dedicated server [Intel P4 2.4GHz, 512MB memory, single 80GB IDE hard disk, 10mbit uplink, RedHat 3, Apache 2.0, MySQL 4.1.x]

Board has 293,000 posts, total size about 640MB, topics table 18MB, posts table 490MB.

In a peak hour, there will have been about 900 different visitors to the board, about 80% of those will be Guests. Spiders are ALWAYS visiting it seems.

What we see is the server load suddenly spiking high, and staying high for 20-30 minutes in the peak hour. Server load goes above 20 and stays there for 10+ minutes, top shows that the problem is all iowait, the CPU is sitting waiting for data from the disks.

On investigation, I've found that the my.cnf contains only:

[mysqld]

safe-show-database

innodb_data_file_path=ibdata1:10M:autoextend

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

old_passwords=1


[mysql.server]

user=mysql

basedir=/var/lib


[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Can anybody please suggest a my.cnf configuration that will optimize server performance during the hourly peak?

[Wondering Soul]

Its hard to give a perfect, working group of settings without being able to look at their effect on the machine but I have a few tips for setting some values :)

The most important values within this file are query_cache_size, key_buffer_size, table_cache, sort_buffer, read_rnd_buffer_size and thread_cache. From my experience with MySQL, setting those values correctly in a way that best fits your hardware is a very good way to increase its overall performance!

The query_cache_size value controls the amount of memory that is to be allocated to the caching of queries that are performed regularly. For example, in IP.Board this could be an UPDATE query. Mine is set quite high but I have 8GB's of RAM :P For a server with 512MB's of RAM, I think a setting of 16MB would be fine (you could try 32!).

key_buffer_size is used with indexes. So the higher this is set, the quicker queries will be executed and a result returned. In normal operation, its fine to set this to at least a quarter of the available memory on your server but no more than half. In your case, anything between 128MB and 256MB (although I think 128 would be fine).

table_cache relates to MySQL accessing tables and placing them in their own cache. Therefore, the more tables MySQL has to access, the slower the process takes place, so by increasing this, you speed that process up! Something around 128MB should be ok for you! Although if you want to tune this even more, take a look at the value of open_tables when your at peak times. If its higher than the table_cache value, then you need to increase this!

sort_buffer is useful if there are many SORT queries being ran. It can speed that process up slightly. This value doesn't need to be too high though.

read_rnd_buffer_size is used after a SORT command is used. If there are many ORDER BY commands being used ( there are in IP.Board ) then increasing this can improve overall performance! Generally, its known to allocate 1KB to this value for every MB of memory on your server!

thread_cache can be used to take quite a lot of the load away from the CPU! Its hard to set a value for this without monitoring your system during peak times. However, 32MB has always worked well for me and that way its not too heavy on memory consumption! If you wish to tune this value yourself, you need to set it high enough so that Threads_created (found from SHOW STATS command) stops increasing!

So, that's a few tips for you :) Judging from what I have said, you could try using the following to see if you get any improvements:

[mysqld]

safe-show-database

innodb_data_file_path=ibdata1:10M:autoextend

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

old_passwords=1

query_cache_size=16M

table_cache=128M

key_buffer=128M

#sort_buffer=2M

read_rnd_buffer_size=512K

thread_cache=32M


[mysql.server]

user=mysql

basedir=/var/lib


[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Hope that helps :)

[rct2·com]

Thank you for such a detailed, considered reply. :thumbsup:

Could you also suggest a time to set for logging slow queries?

[Wondering Soul]

No problem :)

I have mine set to detect any queries that take longer than 3 seconds to execute. Then again I have more available RAM to perform them quicker but I would say that somewhere around 3-5 seconds should be ok. Anything taking longer than that would mean your SQL Server isn't performing very well.

Hope that helps :)

[Velvet Elvis]

I'm pretty sure table caches is the number of tables cached and not the amount of memory used.

http://dev.mysql.com/doc/refman/5.0/en/tab...ache.html"]http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

The cache of open tables is kept at a level of table_cache entries. The default value is 64; this can be changed with the --table_cache option to mysqld. Note that MySQL may temporarily open more tables than this to execute queries.

[Wondering Soul]

Yes your correct.

I put MB behind the value out of habit, sorry for any confusion...

[crafty55]

You'll want a low query cache size & query cache, set query cache to 1mb for ipb only servers and query cache size to 4mb or so if I recall. Even if you have 8GB of ram query cache makes mysqld sluggish.

Next, you'll want to upgrade to MySQL5, theres performance gains, also compile if yourself or hire a system administrator to do so. It's worth it by far.