I have a site that runs a pretty extensive database using MySQL. It is a search engine of sorts and is coded in perl. The backend makes, on average, 5 to 10 database calls per second. The majority of these calls being UPDATE's. The rest would be either, SELECT, DELETE or INSERT, etc..

The problem I am having is that, at times, the mysql database chews up 99% of my cpu for an extended period of time. I am looking for ways to possibly illiviate this. I have already added LOW_PRIORITY to my UPDATE calls so they only happen when no other calls are being made. I currently do not use indexes because I am under the impression that indexes speed up SELECT calls but slow down UPDATE calls. Is this true?

I also have a front end website written in perl that provides a search interface to the database. So at any one time, I probably have UPDATE's, SELECT's, DELETE's and INSERT's all happening at the same time. I regularly OPTIMIZE my tables to remove overhead.

The system that is running all this is an AMD 3500+ 64 bit processor with 1 gig of PC2700 DDR RAM. The database has 11 tables with 218,000 records currently. The size is only 22.2 MB as of now.

Any ideas on how to get better performance?

Wow thanks for all the valuable info!! LOL..

I managed to get it anyway. Using InnoDB for the engine, having indexes and using varchar over text makes HUGE differences.

Just an observation, but 7 hours won't usually get you a good response... Sometimes 72 is closer!

Yes, indicies are good things nearly all of the time. You can go index crazy, but generally "more is better" as long as you can actually use them.

-PatP