Previous Thread
Next Thread
Print Thread
Rate Thread
#257937 08/31/2003 8:58 PM
Joined: Feb 2002
Posts: 1,759
M
msula Offline OP
Addict
OP Offline
Addict
M
Joined: Feb 2002
Posts: 1,759
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
Joined: Apr 2003
Posts: 10
I
Newbie
Offline
Newbie
I
Joined: Apr 2003
Posts: 10
Another possibility helps MySQL running faster is to increase the size of the join buffer.

So long...

Indy

Joined: Feb 2002
Posts: 950
P
Hacker
Offline
Hacker
P
Joined: Feb 2002
Posts: 950
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?

Joined: Feb 2002
Posts: 1,759
M
msula Offline OP
Addict
OP Offline
Addict
M
Joined: Feb 2002
Posts: 1,759
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.

Joined: Jul 2001
Posts: 442
C
Enthusiast
Offline
Enthusiast
C
Joined: Jul 2001
Posts: 442
[]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
Joined: Oct 2001
Posts: 213
L
Member
Offline
Member
L
Joined: Oct 2001
Posts: 213
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.


Link Copied to Clipboard
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
bluehost
InterServer
Visit us on Facebook
Member Spotlight
JAISP
JAISP
PA
Posts: 448
Joined: February 2008
Forum Statistics
Forums63
Topics37,551
Posts293,824
Members13,840
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online 305
Topics Created 0
Posts Made 0
Users Online 0
Birthdays 17
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,808
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 66
Gizmo 12
Brett 7
WebGuy 2
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2021 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Snapshot build 20201211)
Responsive Width:

PHP: 5.4.45 Page Time: 0.052s Queries: 27 (0.019s) Memory: 3.2999 MB (Peak: 3.6351 MB) Data Comp: Zlib Server Time: 2021-01-18 03:28:24 UTC
Valid HTML 5 and Valid CSS