MySQL optimizations

[StingRay]

I've searched through the forums here looking for my.cnf optimizations but haven't found much. I'm a little disappointed, I was under the impression that there was a wealth of information here about setting up and optimizing my VPS.

I've found the information elsewhere, but of course it can be very specific to a certain setup. That's why I wish there was more in this forum that deals with the specific VPS's sold here.

Perhaps it is here somewhere and I just haven't seen it, but if not I hope some of you will chime in and give some good advice.

I will start by listing my current my.cnf which I built after reading for hours on end :P

[mysqld]
skip-locking
skip-innodb
max_connections=150
connect_timeout=15
wait_timeout=15
interactive_timeout=15
key_buffer=16M
join_buffer=1M
record_buffer=1M
sort_buffer_size=768K
read_buffer_size=512K
old-passwords
read_rnd_buffer_size=512K
table_cache=384
thread_cache_size=128
max_allowed_packet=5M
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
thread_concurrency=2

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet=16M

[Hvu]

This is my.cnf for my VPS. My VPS hosts only a couple sites but they are high traffic.


[mysqld]
skip-locking
max_connections=50
connect_timeout=5
key_buffer=64M
join_buffer=1M
record_buffer=1M
sort_buffer=1M
table_cache=500
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=5
query_cache_limit=32M
query_cache_size=1M
query_cache_type=1
thread_concurrency=2


We currently change to this my.cnf and stress testing it now

[mysqld]
skip-locking
max_connections=50
connect_timeout=5
key_buffer=64M
join_buffer=5M
record_buffer=5M
sort_buffer=5M
table_cache=500
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=5
query_cache_limit=32M
query_cache_size=1M
query_cache_type=1
thread_concurrency=2

[StingRay]

The amount of memory you have is important to the configuration. So knowing your VPS plan would be helpful. ie 256,512, 1G Ram?

Also which version of mysql. Mine is 4.1.13

[Hvu]

512mb / 4.1.13 / Running Directadmin and Webmin. VZPP usually says 30% memory usage, It runs at 50 queries per sec since the code uses ADoDB and caches most if not all requests.

[mikelbeck]

?

My my.cnf looks like this:


[mysqld]
set-variable = max_connections=500
safe-show-database


So I guess it's using the defaults for all of the other parameters... Should I tweak it like you guys have? Will it make that much of a difference?

[Hvu]

Well if your mysql / php pages load fine I dont see a reason to "if its not broken dont fix it" It never hurts to try. Edit your my.cnf then "service mysqld restart" and watch your load/memory usage for a couple hours.

[elix]

Wow my wait_timeout is at 1800..should I try reducing that, lol?

[elix]

Okay, i changed a few things.

[mysqld]
max_connections = 800
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
interactive_timeout = 10
wait_timeout = 10
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
skip-innodb
old-passwords

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M

And yes, MySQL is fairly active on my VPS as there are a few forums with 5-10 users on at once. The interactive_timeout appears to be helping tons.

[StingRay]

elix, how much memory do you have?

[Hvu]

I believe eLix has Power-3. I'm working on his VPS right now. 768mb, 2.5gb burst

[mikelbeck]

I made some changes and will keep an eye on mine... I found that 4.0.24 doesn't like the "old-passwords" command, though. ;-)

[elix]

I made some changes and will keep an eye on mine... I found that 4.0.24 doesn't like the "old-passwords" command, though. ;-)
Oh, sorry that's for 4.1.x

[mikelbeck]

Things got real sluggish and then eximstats failed. I put my.cnf back to the way it was, restarted mysql and restarted eximstats but my cPanel says it's still failed.

How do I fix this now?

[mikelbeck]

Things got real sluggish and then eximstats failed. I put my.cnf back to the way it was, restarted mysql and restarted eximstats but my cPanel says it's still failed.

How do I fix this now?

Ok, ignore that, eximstats is up now.

I'm gonna leave my.cnf the way it is. ;-)

[Hvu]

Yeah 4.0.x and 4.1.x have entirely different configurations. (: Upgrade to 4.1.x and i'll be able to help you. I'm currently tuning elix's server right now. Well not really just relaying just to change because I dont want to mess up his live sites.

[mikelbeck]

Yeah 4.0.x and 4.1.x have entirely different configurations. (: Upgrade to 4.1.x and i'll be able to help you. I'm currently tuning elix's server right now. Well not really just relaying just to change because I dont want to mess up his live sites.

I'm happy with 4.0.x for now, I don't see any benefit to upgrading to 4.1.x at this time. But thanks for the offer!

[jpetrov]

Well 4.1.x is generally available release and it is recommended by mysql. Very important for me, 4.1.x has native UTF-8 table size handling and per table encoding... As well as some more advanced database tools. I've upgraded recently without a problem. I have few mambo sites and nothing (bad) happened... Well... I forgot to recompile apache+php - but then - how a man can learn if he does not make mistakes ;)

[elix]

If you don't recompile Apache then just use old-passwords...that's if you're lazy like me ;D