George: mysql params

[Dave#]

George just moved to a new server - was hoping you could give your view on mysql parameters:

The server is dedicated dual 2ghz xeon, 1 gig of ram scsi disk running php 4.3.1, mysql 4.0.12, latest apache, Turk cache and mod_gzip, more ram is going in as soon as it becomes available but the server is struggling a little now (running low on ram and swapping)

Mysql info
http://cpfc.org/php/mysql.php

Thanks

Dave

[eva2000]

:eek: with 2GB ram and 2GB swapfile and you're swapping to disk because your buffers are set too high and innodb support is enabled ;)

try


[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
should work fine :)

[Dave#]

!:eek: with 2GB ram and 2GB swapfile and you're swapping to disk because your buffers are set too high and innodb support is enabled ;)

try

should work fine :)

George - I only have 1 gig of Ram at the moment not two!

[eva2000]

whoops... same applies though!

[Dave#]

whoops... same applies though!

thanks :)

[Dave#]

George: I'm noticing that the load averages on my box are a little high and mysql seems to swap out to disk a little.

http://www.cpfc.org/mrtg/swap.html

I now have 2gig of ram in the box do you have any suggested tweaks? Someone else suggested:

Well.. your sort,read and join buffers are too big.. if you had alot of mysql processes going you would suck up all your ram so I'd recomend you change those to..

join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

and increase the key buffer..

key_buffer = 150M

any thoughts?

[tamarian]

Dave,

Try turning KeepAlive off in httpd.conf, and see how it goes. If you have Gzip and Turk installed, the difference should be barely noticable to users.

[Dave#]

Dave,

Try turning KeepAlive off in httpd.conf, and see how it goes. If you have Gzip and Turk installed, the difference should be barely noticable to users.

I have played with KeeepAlive a little on this server, heres my current settings:

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15

I thought about turning KeepAlive off alltogether but I'm not short of free real memory so I thought it best to keep it on.

[Erwin]

Dave,

Try turning KeepAlive off in httpd.conf, and see how it goes. If you have Gzip and Turk installed, the difference should be barely noticable to users.
I have exactly those settings and it works well for me.

[Dave#]

I have exactly those settings and it works well for me.

we are seeing quite large spikes in traffic Erwin

[eva2000]

George: I'm noticing that the load averages on my box are a little high and mysql seems to swap out to disk a little.

http://www.cpfc.org/mrtg/swap.html

I now have 2gig of ram in the box do you have any suggested tweaks? Someone else suggested:



any thoughts?
what does your current my.cnf look like ?

[Dave#]

what does your current my.cnf look like ?

It's as you suggested earlier in the thread some months ago

[eva2000]

It's as you suggested earlier in the thread some months ago
from your mysqlinfo url at http://cpfc.org/php/mysql.php and the 2nd post above where my my.cnf suggested is i can't see how mysql was the cause of the disk swapping used unless you have maxclients set to something very high > 256

what else you got running on the server ? java, chat apps, ad serving ??? what's the traffic figures handled on the server like ?

[Dave#]

from your mysqlinfo url at http://cpfc.org/php/mysql.php and the 2nd post above where my my.cnf suggested is i can't see how mysql was the cause of the disk swapping used unless you have maxclients set to something very high > 256

what else you got running on the server ? java, chat apps, ad serving ??? what's the traffic figures handled on the server like ?

No other real apps running on the server, although traffic can be very high - over 2meg a sec for large chunks of the day.

Apache config is pretty standard too - maxclients is set a 256.

the swapping isn't really a huge issue - it's only ever using 128 meg of swap - and load averages are generally lower than 1 - thanks for taking the time to look though

Cheers

Dave

[eva2000]

but i see from mysqlinfo url swap used = 429684K used and that's alot but i can't see how

[Dave#]

but i see from mysqlinfo url swap used = 429684K used and that's alot but i can't see how

yeah - I know what you mean - but a quick bounce of apache free up that swap

total used free shared buffers cached
Mem: 2064832 1152092 912740 0 56476 925284
-/+ buffers/cache: 170332 1894500
Swap: 2048276 58908 1989368


Starting to feel like mmcache might be responsible for this behaviour

[Erwin]

yeah - I know what you mean - but a quick bounce of apache free up that swap

total used free shared buffers cached
Mem: 2064832 1152092 912740 0 56476 925284
-/+ buffers/cache: 170332 1894500
Swap: 2048276 58908 1989368


Starting to feel like mmcache might be responsible for this behaviour
Mmm... I have 400 users right now, and my loads are less than 1.0 with vB3 - I use mmcache too - I do maxmimise all of vB3's optimization features. I made a post in the vB3 forum about it. Maybe a mod can move it over here since it probably belongs here.

[Dave#]

We don't use VB3