2

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.

Improve this question
2
  • "MySQL's Opened_tables is at 15.6 million (that a a lot?)" Depends how long your MySQL server has been running. If that number is going up steadily then you can increase the table_cache variable. Check if Opened_tables is going up steadily by running: mysqladmin -u root -p ext -ri 30 | grep Opened_tables The first number is from last restart and then how many every 30 seconds – HTTP500 Jun 16 '11 at 21:05
  • What does "messing up and just don't seem to respond" mean? Can you be more specific? – HTTP500 Jun 16 '11 at 21:12
4

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).

Improve this answer
3
  • I use MyISAM table and I could upgrade, what would that offer? – webnoob Jun 16 '11 at 20:33
  • @webnoob: InnoDB is a lot faster in 5.5. In your case I don't know if there is lot that would help you. – Raffael Luthiger Jun 16 '11 at 20:35
  • 1
    I wouldn't upgrade until you know what your problem is. Only then will you know whether an upgrade provides a solution. – HTTP500 Jun 16 '11 at 21:09
1

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...

Improve this answer
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.