Previous Thread
Next Thread
Print Thread
Rate Thread
Let's tune MySQL part II #257937
08/31/2003 9:58 PM
08/31/2003 9:58 PM
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
M
msula Offline OP
Addict
msula  Offline OP
Addict
M
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
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

Code
 <br />key_buffer=64M <br />table_cache=1024 <br />sort_buffer=4M <br />read_buffer_size=2M <br />


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

Sponsored Links
Re: Let's tune MySQL part II [Re: palmen] #257938
09/09/2003 12:29 PM
09/09/2003 12:29 PM
Joined: Apr 2003
Posts: 10
Plauen, Germany
I
indy Offline
Newbie
indy  Offline
Newbie
I
Joined: Apr 2003
Posts: 10
Plauen, Germany
Another possibility helps MySQL running faster is to increase the size of the join buffer.

So long...

Indy

Re: Let's tune MySQL part II [Re: crablouse] #257939
10/14/2003 8:06 AM
10/14/2003 8:06 AM
Joined: Feb 2002
Posts: 989
P
PhotoPost Offline
Hacker
PhotoPost  Offline
Hacker
P
Joined: Feb 2002
Posts: 989
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?

Re: Let's tune MySQL part II [Re: gailg] #257940
10/14/2003 11:09 AM
10/14/2003 11:09 AM
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
M
msula Offline OP
Addict
msula  Offline OP
Addict
M
Joined: Feb 2002
Posts: 1,969
Lansing, Michigan
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.

Re: Let's tune MySQL part II [Re: palmen] #257941
03/23/2004 6:52 PM
03/23/2004 6:52 PM
Joined: Jul 2001
Posts: 462
Colorado
C
coloradok5 Offline
Enthusiast
coloradok5  Offline
Enthusiast
C
Joined: Jul 2001
Posts: 462
Colorado
[]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

Code
<br />key_buffer=64M<br />table_cache=1024<br />sort_buffer=4M<br />read_buffer_size=2M<br />


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?

Sponsored Links
Re: Let's tune MySQL part II [Re: bisbell] #257942
03/24/2004 11:42 AM
03/24/2004 11:42 AM
Joined: Oct 2001
Posts: 209
Cleveland, Ohio
L
little_joe Offline
Member
little_joe  Offline
Member
L
Joined: Oct 2001
Posts: 209
Cleveland, Ohio
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.


Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with, and recommend, the following Web Hosts:
· Stable Host
· Blue Host
· Interserver.net
Visit Us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,170
Joined: July 2001
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,448
Posts293,484
Members13,793
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,938
Greg Hard 5,533
Top Posters(30 Days)
isaac 4
Today's Statistics
Currently Online 710
Topics Created 0
Posts Made 0
Users Online 1
Birthdays 20
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.6.2
(Preview build 20180611.dev)
Page Time: 0.039s Queries: 15 (0.011s) Memory: 3.2605 MB (Peak: 3.4160 MB) Zlib enabled. Server Time: 2018-06-21 09:58:38 UTC