Mysql slow queries issue

Hi, my hosting provider site5 have been in contact saying:

While investigating load issues on the server, I came across entries in the slow-queries.log for mysql. The slow-query.log keeps track of mysql queries that take an inordinate amount of time to complete and the number of rows that were examined in relation to the amount of time taken. Entries in the slow-query.log almost always indicate an issue with the database that is detrimental to resource usage. Please find the details of queries below:





The had to take down my site as it is affecting other peoples sites. They also said this:

it looks like the memory usage for your site is still a bit high and I do see that your index file is at times spiking over 100% CPU usage.

Does anyone know what this means and how to fix it? Server details are below:

MODx Revolution 2.0.7-pl (traditional)
PHP 5.2.17 - CGI
Mysql - 5.0.92 - community log

Thanks!

Did you develop this site?

How many records are in that table and how are they displayed on your site? How much traffic do you get on average?

Hey, yeah I developed the site. It gets about 200 visits a day.

How do I found out how many records are in that table? im looking in phpmyadmin but not to sure how to find it.

Thanks

Click on the table name in the list on the left hand side, then click the ’Browse’ tab.

It should show you in green how many rows there are in total.

Are you able to replicate that ’slow’ query in phpMyAdmin and see how long it takes to execute?

sorry i’m not sure which table I need to browse to get that number?

The table name is cut off in the text you pasted above, but going by the column names it looks like the modx_access_policies table. That should be a relatively small table. Sorry, I should’ve noticed that sooner

Are you running some complex access policies, form customisation or permissions snippets on your site?

Ah thanks, site5 didn’t include the text in their ticket. I clicked that table:

Showing rows 0 - 6 (7 total, Query took 0.0002 sec)

I have form customizations but I wouldn’t say they are complex.

Do they affect the front end at all (ie. visitors logging in to see restricted areas), or is it manager-only stuff?

If not, what you could do is add the following code to your templates (in html comments if you want), then browse through your site noting any slowish-pages. That may give you an indication of which page -and which snippets - might be causing problems. Clear the cache first.


If you don’t turn anything up there, I’d get on the phone to site5 and see if they can give you any more info and about the issue and suggestions how to fix it. MODx shouldn’t really be causing this kind of problem, especially if you’re caching your pages and aren’t running any complex snippets.

No they are only manager stuff. The site is actually pretty fast, take a look:

www.louchemusic.com

The photography gallery is the only thing that takes a while to load, for obvious reasons.

I’ll ask site5 for some more info and post it up.

Thanks for your help Lucas!

I had a look and it seemed pretty snappy to me too - but site5 might have temporarily moved it to a different server. Unless those ~200 hits are all coming at the same time (which I doubt ), I don’t see how it could be maxing out your server. Just check that all your snippets are cached where possible and let us know what the host says.

Nice looking site by the way!