MySQL tuning - HW or SW

[MongoHosting]

top shows a boatload (25) of this:

9599 mysql 0 0.0 2.8 /usr/local/mysqld --basedir=/ --datadir=var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/hostingco.com.pid --skip-locking

First off, is this normal? Cpu usage has been pushing the server load up and down all night, some major spikes. Mem is the real nag, been high b/c of these entries like above.

Any clues? There is one very active vB forum on this server.

Should I just up the mem and re assess or is there something I'm missing?

I'm posting this here because this didn't start occurring until I went to 9.2 Stable release to fix the exim problem.

 

[kris1351]

Do some searches out there on tuning PHP, MySQL and HTTP. No one solution will work for each server. Here is a good place to start on MySQL though.

http://forum.ev1servers.net/showthread.php?s=&threadid=20983

 

[GOT]

From shell, type

mysqladmin status

and post the results.

 

[MongoHosting]

We haven't had any problems tonight....of course we've been trying to track this down:

Uptime: 82350 Threads: 2 Questions: 948149 Slow queries: 69 Opens: 9056 Flush tables: 1 Open tables: 64 Queries per second avg: 11.514

 

[mickalo]

Originally posted by MongoHosting
We haven't had any problems tonight....of course we've been trying to track this down:

Uptime: 82350 Threads: 2 Questions: 948149 Slow queries: 69 Opens: 9056 Flush tables: 1 Open tables: 64 Queries per second avg: 11.514

Queries per second avg: 11.514... this is WAY too high... looks like you might have some queries that need optimizing or fine tune your global my.cnf file.

Mickalo

 

[GOT]

I would not necessarily agree with that assessment.

I have a very lightly loaded server and it runs at 10.

I have a moderately busy server and it runs at 30.

I have a server that I manage for someone which (IMO) is overloaded and it runs 50-60.

Now, that is not to say that your my.cnf doesn't need optimizing. It almost certainly does if you've not done it.

Whole books have been written on how to optimize mysql. Its more of an art than a science. There are lots of threads and guides that will give you a decent my.cnf. It won't be perfect, but it'll be better than what you have right now.

 

[tanq]

Uptime: 565008 Threads: 4 Questions: 74766200 Slow queries: 45 Opens: 82214
Flush tables: 1 Open tables: 512 Queries per second avg: 132.328


Thats bad, right?

 

[mickalo]

Originally posted by GotHosting
I would not necessarily agree with that assessment.

I have a very lightly loaded server and it runs at 10.

I have a moderately busy server and it runs at 30.

I have a server that I manage for someone which (IMO) is overloaded and it runs 50-60.

Now, that is not to say that your my.cnf doesn't need optimizing. It almost certainly does if you've not done it.

Whole books have been written on how to optimize mysql. Its more of an art than a science. There are lots of threads and guides that will give you a decent my.cnf. It won't be perfect, but it'll be better than what you have right now.

right you are, I negeleted to take in consideration the Questions factor.

One of our boxes has about 200 DB's... I've just never seen the QPS average quiet that high.

Mickalo

 

[GOT]

over 100 is not necessarily bad, depending on how taxing they are, and what type of io subsystem you are running.

Bottom line is what can your server handle. If all of them are being hit by the query cache, then you won't be in too bad of shape, but if only 90% are, you are probably stressing your hard drives, even if you have scsi.

 

[mickalo]

Originally posted by GotHosting
over 100 is not necessarily bad, depending on how taxing they are, and what type of io subsystem you are running.

Bottom line is what can your server handle. If all of them are being hit by the query cache, then you won't be in too bad of shape, but if only 90% are, you are probably stressing your hard drives, even if you have scsi.

one of the big improvements in ver 4.0+ was the query_cache which we use alot in our perl appilications. the recent release of 4.0.20 has fixed many bugs found in 4.0.18 regarding memory leakage. taking some time to learn how to tune MySQL is really worth the cost of a few good books... believe me