mysql performance issue

[matchlock]

I couldn't find a database specific section to post this in. Hopefully it is OK here.

Ocassionally (maybe once a day) my website goes down and zen states that it cannot connect to the database. What I have noticed is that the mysql instance is spiking the CPU over 100% constantly and doesn't allow zen to serve the pages. I have gone in to mysql and identified the query that is running the cpu up. I am however lost as to where this query is being spawned. Please find below the output from show SHOW PROCESSLIST; I identifed the listed query as the culprit and as soon as I kill it the DB performs normally. Please help...

-----

Id | User | Host | db | Command | Time | State | Info |
+-------+--------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 30366 | user | localhost | zendb | Query | 1485 | Sending data | select count(distinct p.products_id) as total FROM products p, products_description pd, products_t |

[DrByte]

Which site?
How many products do you have?
Have you any idea which link causes it?

That query comes from the pagination logic which checks to see how many products fall in the specified category or filter, so that it can determine what "next page" links to show, etc.

It would be helpful to know which filter link or sort link or pagination link you click on to trigger the problem.

It would also be helpful to know the entire query, not just the first 100 characters, if possible.

You could try running a REPAIR TABLE and OPTIMIZE TABLE on your products, products_description, and products_to_categories tables just in case something's damaged in them.

[matchlock]

Thanks for the quick reply!

Which site?

www.matchlockarms.com

How many products do you have?

6800

Have you any idea which link causes it?

I'm not sure if it is on the admin side or customer side. Or what link for that matter. I'm going to poke around a bit to see if i can make it happen.

That query comes from the pagination logic which checks to see how many products fall in the specified category or filter, so that it can determine what "next page" links to show, etc.

It would be helpful to know which filter link or sort link or pagination link you click on to trigger the problem.

Hopefully i can identify and post back shortly.

It would also be helpful to know the entire query, not just the first 100 characters, if possible.

Do you know of a way to show the whole query show show processes or something of the like?

You could try running a REPAIR TABLE and OPTIMIZE TABLE on your products, products_description, and products_to_categories tables just in case something's damaged in them.

I'll try this. Anything to be concerned of when I do so?

[DrByte]

6800 products isn't a lot.
If they're heavily laden with attributes, that does certainly create additional burden, but shouldn't seriously impact displaying a product listing.
A REPAIR or OPTIMIZE will cause a temporary lock on the tables while it does the job, and customers will just experience a brief hang if they click while that lock is in place until the lock is released. If your store had 10x that many products then I'd suggest waiting until a slow time to do it.

The slow-query-log might be needed for you to see the details of the query.
Or a GUI tool like Navicat or SQLYog which can extract more information from the database than phpMyAdmin does in things like SHOW PROCESSES and other things.