request for server optimisation (thx in advance eva!)

[buro9]

eva2000, I have followed all instructions in the other thread for gathering of information, and that is attached in a text file (which i figured must be easier for you than tons of posts and poorly formatted output).

in essence though... i'm not sure some of it will be as revealing as it should.

i've just moved from a VPS5000 to a nice fresh VPS5000 with Ventures Online. and the only thing i've done this far is to add query cache instructions as per your other thread to the my.cnf file.

i generally have between 60 and 80 users online every afternoon through to midnight (UK afternoon through to US afternoon!) with about 30 users concurrent the rest of the time.

i'm looking for preventive optimisations... the obvious stuff (well... obvious to you!) that can be done with the default build of the VPS to help it perform well from the outset.

thanks tons in advance... if there's any way i can return the favour, just ask :) i am pre-indebted to you.

cheers

david k

[buro9]

bump :)

[eva2000]

sorry for the delay having ADSL internet access/ISP outages and dropouts right now :(

I'd do the following in this order

1. upgrade to latest stable 2.4.x smp linux kernel if you can
2. change my.cnf to below and restart mysql

[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
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

[buro9]

I've done all that was suggested, and it has helped.

I was unable to perform a kernel upgrade as this is a shared host and I'm not convinced I have that level of access even though I do have root.

However... all that has happened is that "usage expands to fill resources available".

I'm now averaging 50-60 people 24 hours a day, with surges to just under 100 users for most of the afternoon & evening (GMT).

The load is relentless and consistent.

Every now and then the database will freeze (generally after a database dump performed by the back up tool... well, around 30 minutes after)... and it will all come crashing down temporarily leaving me with 500 or 600 hundred error e-mails before it all gets going again.

It does this, every two to three days at least.

Based on all the above info... would you say that I've outgrown this box? Or is there likely to be something else at work?

I've looked at the 'server load' and those numbers mean nothing to me really... but it's seldom above 2.0, so I'm running on the belief that something else is causing the problem.

I've tried to install turck-mmcache, but no joy as my PHP is running in CGI mode... so I've now removed the calls to it from the php.ini file.

Any ideas or suggestions would be much appreciated.

[eva2000]

500 or 600 hundred error e-mails before ?

what error messages you get ?

[buro9]

500 or 600 hundred error e-mails before ?

what error messages you get ?
Tons... all slightly different... but the common thing is that they all sulk that the MySql server has "gone away" or cannot connect through a sock.

Whenever I start receiving them I rush to the server... usually to find that the server is already back up... and that the e-mails were all produced in the 30 minute period before I started receiving them.

The only thing I could think is maybe the database locks in some way... hangs for a while (causing all of the error e-mails) and then restarts itself... which is when I generally notice things (I don't sit on my site all day clicking F5).

Apache is always fine... because the plain pages, the non-db php stuff... that always works.

[eva2000]

can you post your my.cnf contents and mysqlinfo.php url (link not text please) again

[buro9]

can you post your my.cnf contents and mysqlinfo.php url (link not text please) again
Sure:

http://www.bowlie.com/forum/admin/mysqlinfo.php

And here is the my.cnf again:

[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
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

[buro9]

Oh, and I have the deferred thread view counts hack installed too.

[eva2000]

you've only had 2hr 46min uptime according to mysqlinfo.php for mysql server, not enough time to properly gauge your current mysql server loads

try in my.cnf under [mysqld] group change

wait_timeout = 1800

to

wait_timeout = 28800

and restart mysql and run for a few days see how that goes

[buro9]

you've only had 2hr 46min uptime according to mysqlinfo.php for mysql server, not enough time to properly gauge your current mysql server loads
Yes... it crashed earlier hence my posts on here.

I'll be surprised if I can get a couple of days out of it... but I'll make the changes and bump this in two days to remind you to take a peek ;)

Thanks for all your help eva :)

[buro9]

Yes... it crashed earlier hence my posts on here.

I'll be surprised if I can get a couple of days out of it... but I'll make the changes and bump this in two days to remind you to take a peek ;)

Well it got to a day and half and crashed again. Currently having the host do a reboot.

So I'll try and get to two days and bump again.

Ah well.

[buro9]

Well it got to a day and half and crashed again. Currently having the host do a reboot.

So I'll try and get to two days and bump again.

Ah well.
Wooo!

Got to just over two days uptime! :)

Quick Eva... get it while the stats are there!

Hehe... This would be funny if it wasn't such a rare thing!