Need some advice for a busy mysql server

Question

I have about 150 cron jobs running that process between 6 and 10 inserts every 3 seconds. There are LOTS of select statements happening every 3 seconds as well.

My problem is, things work very well for a while and then start messing up and just don't seem to respond. I have 16BG of memory and only 20% is used and my CPU usage is averaging about 2.0. This makes me think I have some options I can tweak somewhere.

I was trying to mess about with file descriptors on my other dedi box but ended up losing root access for some reason so only want to do this again if someone can point me to a VERY good tutorial.

MySQL's Opened_tables is at 15.6 million (that a a lot?)

I would just like some general advice on settings I should be tweaking and things I should be doing to make the server run well. I obviously have the resources available but I presume MySQL / Apache are limited in some way.

Server Spec: Linux CentOS 5 with Plesk 9.5.4 and MySQL 5.0.

I really appreciate any help in advance.

Answer 1

What I can recommend you is to use MySQL tuner. This perl script gives you many recommendations on what configuration variables you could set or change. Without knowing your exact statistics I can probably not give you a better answer.

I recommend you as well too look on the MySQL website for each variable MySQLtuner recommends you what exactly it is doing. Then you will learn about many new variables you can set. Furthermore you should run this tool about 48 hours after you changed the variables the first time. Often you get then some more (better) recommendations.

Do you use more innodb tables or myisam? Is upgrading to MySQL 5.5 an option?

UPDATE: Regarding your question about good tutorials. What I can highly recommend you is reading the MySQL Performance Blog. There are really a lot of good articles (One I already linked to).

Answer 2

Keep in mind that myisam tables are locked during insert or update so the select queries will wait.

You can speed up inserts with concurrent inserts option but do not expect miracle. http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html

I would say that switching to 5.5.x and InnoDB would be a smart move...

Answer 3

I find the MySQL Tuning Primer script (first item on page) to be more useful than the previously mentioned MySQL Tuner script.

Assuming that everything checks out okay with either script, then have you looked into possibly optimizing your queries using the EXPLAIN feature?

As others have mentioned, InnoDB offers row-level locking over MyISAM's table locking. While the speed of InnoDB is much improved in MySQL 5.5, truly any 5.x version should provide an increase.

The LOAD DATA INFILE feature is much faster than an insert. If it is possible to go that route then do so. If your tables have a lot of indexes, then ensure your key_buffer_size is large enough. If you're only using 20% of your RAM and this is a dedicated MySQL server then you can certainly improve your general configuration for better performance.