Ok, if you have followed the other thread about tuning MySQL, you are off to a great start! But I wanted to inform you of a few settings that after extensive testing has increased MySQL and overall server performance by over 50%

I have tested nearly every combination of my.cnf settings you can think of, and after reading more and more MySQL documentation I have found a few changes to make for those busy .threads sites with servers with 1 GB of ram or more run even faster.

If you have less RAM than 1GB, you may want to stick to the other settings I gave, but if you have 1GB+, this should help tremendously.

These are the settings you should have in your my.cnf file



These are the most important settings, and by giving them even more memory to work with, you should see your page load times increase dramaticaly as well as lower server loads.

One thing you may want to experiment with is the table_cache, it may work slightly better to run that at 512, but from my tests 1024 works great on a P4 server with 1 GB of ram and running a threads site with 50-90 users online and over 1200 posts per day.

So if you are still experiencing some slow loads and your server has some extra memory, this could easily help boost performance even a bit more

Another possibility helps MySQL running faster is to increase the size of the join buffer.

So long...

Indy

I have two section that refer to the key_buffer: mysqld and myisamchk.

In mysqld I have key_buffer set to 384M, is this excessive for a machine with 2GB of RAM (dual Xeon processors?)? In the myisamchk its set to 256M, again, excessive?

Well, myisamchk I believe is only used when repairing/optimizing tables, so the larger that value, the faster that process probably goes, so I don't think that is of much a concern. I haven't tested any different myisamchk values to be able to say for sure.

As for key_buffer, that may be a little high, but it's hard to say depending on where the rest of your memory is being used throughout the system. I personally use 64MB and 128MB for some of my 1GB ram systems, and find that works fine. I have not experimented with much higher values.

You may want to run a few comparisons, starting with 128M, checking server load, memory usage, and the mysql footprint, run a few intense queries and average the time. Then try 256M... do the same thing.. and again your current value, just to get a better idea as to what your system likes best.

[]Jeremy said:
Ok, if you have followed the other thread about tuning MySQL, you are off to a great start! But I wanted to inform you of a few settings that after extensive testing has increased MySQL and overall server performance by over 50%

I have tested nearly every combination of my.cnf settings you can think of, and after reading more and more MySQL documentation I have found a few changes to make for those busy .threads sites with servers with 1 GB of ram or more run even faster.

If you have less RAM than 1GB, you may want to stick to the other settings I gave, but if you have 1GB+, this should help tremendously.

These are the settings you should have in your my.cnf file



These are the most important settings, and by giving them even more memory to work with, you should see your page load times increase dramaticaly as well as lower server loads.

One thing you may want to experiment with is the table_cache, it may work slightly better to run that at 512, but from my tests 1024 works great on a P4 server with 1 GB of ram and running a threads site with 50-90 users online and over 1200 posts per day.

So if you are still experiencing some slow loads and your server has some extra memory, this could easily help boost performance even a bit more <img src="/forum/images/graemlins/smile.gif" alt="" /> [/]

Jeremy
What did you use for max_connections?

FWIW with peak users of 250, regularly at 150 users online, plus hundreds more hitting our non-forum pages and another application (phpAdsNew - serving ads on eveyr page we deliver) hitting MySQL, our max connections to MySQL from extended-status is 79. I set our max connections in my.cnf to 150, and we're nowhere near that limit. The default is 100.