MySQL serverand hight CPU usage

[Fred]

Hello,

I have a website hosted on Windows using asp pages to display ads.
The database server is a MySQL server (4.1.15) on an other server
(bi-xeon 3GHz, 1Gb RAM) .

The requests are quit big :
One of the most used request looks like this one :
SELECT SQL_CALC_FOUND_ROWS tblProduits.departementCode, paysFR, paysFR,
tblproduits.produitID, produitNbImgs > 0 AS imgExiste,
tblproprietesvaleurs1.propvaleurFR, tblproduits.prop9,
(tblproduits.prop4 + 0.00) AS prop4, (tblproduits.prop5 + 0.00) AS
prop5, (tblproduits.prop2 + 0.00) AS prop2,
tblproprietesvaleurs6.propvaleurFR, (tblproduits.prop15 + 0.00) AS
prop15 FROM tblproduits, tblpays, tblproprietesvaleurs AS
tblproprietesvaleurs1, tblproprietesvaleurs AS tblproprietesvaleurs6
WHERE tblproduits.paysID = tblpays.paysID AND tblproduits.prop1 =
tblproprietesvaleurs1.propvaleurID And tblproduits.prop20 =
tblproprietesvaleurs6.propvaleurID And tblproduits.rubriqueID = 510 AND
tblProduits.produitType <> 'S' AND tblProduits.produitType <> 'F' AND
tblProduits.produitDateFin > CURRENT_DATE() AND tblProduits.produitActif
= 'TRUE' ORDER BY produitID DESC LIMIT 0, 30

I have a high CPU usage on the server (up to 75%). MySQL Administrator
gives me the following indications :
Connexions : 60
Number of SQL Queries : 180
Hitrate : 63%

I'm using my-large.ini file and I already checked indexes on my database
(35.000 records for tblproduits)

Is there a way to improve the CPU usage or will I have to add a second
server for clustering ? Will I have to change my database engine (ie
migrate to MySQL 5 or SQL Server, for instance) ?

Thanks for your help

Fred

[Bill Karwin]

Fred wrote:

> I have a high CPU usage on the server (up to 75%). MySQL Administrator
> gives me the following indications :
> Connexions : 60
> Number of SQL Queries : 180
> Hitrate : 63%

Do you mean the key buffer hitrate from the Health->Memory tab? Why
such a low value? If you're using my-large.ini, your key buffer should
be at least 256MB. Isn't that large enough to store all your indexes fully?

Have you considered preloading the indexes?
See [url]http://dev.mysql.com/doc/refman/5.0/en/load-index.html[/url]

> I'm using my-large.ini file and I already checked indexes on my database
> (35.000 records for tblproduits)

What does EXPLAIN tell you about index usage in your query?

Which columns in your tables are indexed? I would recommend at least
the following:

- tblProduits.produitID
- tblProduits.rubriqueID
- tblProduits.produitDateFin
- tblpays.paysID
- tblproprietesvaleurs.propvaleurID

But even with additional columns indexed in tblProduits, MySQL will
probably use the primary key index only. Remember that MySQL can use
only one index per table per query.

Are these tables updated less frequently as they are queried? If a
given query is likely to be repeated, and is read more frequently than
the tables are updated, you could consider using the query cache:
[url]http://dev.mysql.com/doc/refman/5.0/en/query-cache.html[/url]

Is the query less expensive if you leave out the SQL_CALC_FOUND_ROWS
modifier? I understand why you need that if you're using LIMIT, but it
would be useful to know if that is the source of the great resource
usage of this query.

Regards,
Bill K.

[remixindir]

Very high CPU load by 20% do you think? I want you to help.