ok,,, i have been suffering for the past 2 weeks,
my server has been really slow, my mysql is consuming most of my cpu , 22% to 40% at a time, and sometimes even more, which will usaully result in a slow down of the site or I will get a message saying that the database is offline or can not connect to the database.

I am a newbie,, if you direct me in a specific directed please be specific, i searched for mysql optimization but i don't know where to start,, also i wonder why this is happening now

i have mysql 3.23.58
running on PLESK 7.0.0

You'll first most likely want to track down what's causing the load problems TechOffice. What you describe can happen pretty easily if you happen to have a script installed that has a badly optimized MySQL query or two, even if there's only a single fairly high traffic site that is using it.

A couple of tools to help you find the culprit:

A Database Journal article that will give you some idea of what to look for when evaluating MySQL queries.

An application you can install calledmytop that was created by Jeremy Zawodny of Yahoo! fame that you can use to watch MySQL queries as they roll through your server. This should help a great deal in determining which script and which query is causing the overload.

Typically what you're going to see with a badly optimized query is something that has a NULL key or possible_keys value when you run EXPLAIN on the query, plus a large number of rows that my be cycled through. Pay attention to the Extras field too.

All of this info is in the article. I've seen some really bad shopping cart software that has to sort through 10's of thousands of lines in a database simply to produce a thumbnail image of products. And if a typical store there might be 20+ products on some pages, so the script quickly begins causing the server to work much harder than it should need to just to keep up.

As to how to fix it, this is going to depend a lot upon what you find. You'll want to fix any unoptimized queries you find of course. This can sometimes be difficult if someone is using off-the-shelf scripts. Many of them are badly optimized IMO, and the developers many times don't seem to want to fix them by creating an index. Sad statement there, but too often true.

Other things you can do...

Upgrade your system so that it has more memory (and hopefully a beefier CPU too) for MySQL to use.

You could also upgrade to MySQL ver 4.x, which allows you to cache queries. (I believe this is going to require a Plesk upgrade too since I've not seen MySQL 4.x available for anything below the more recent versions of Plesk. That's what I seem to remember from the ART repositories anyway.

There should be another thread or two around here somewhere discussing how one can better optimize MySQL and how to find runaway queries. I know I've posted in one or two of them before anyway, so they should be aruond here somewhere.