My MySQL database kills host's cpu

[fightlinker]

Hi Guys

The mySQL database for my website was shut down last night and when i got in touch with my host (godaddy) they gave me this reason:

Please note that your MySQL username "fightlinker" was found over utilizing the database server and causing a >%90 CPU usage itself. Unfortunately we have had to disable the database username to prevent further issues for all customers on the server. Please note that you will need to correct the load issues with your coding/scripting prior to us re-enabling the username. Please let us know if you have any further issues or concerns that you need to have addressed.

Has anyone had this kind of issue using pligg? I have nearly 5000 links in my database, but i'm not getting any kind of really serious traffic, only around 500 pageloads a day. Anyone have any ideas or advice as to what could be causing this?

Thanks in advance
Ryan

[fightlinker]

Here's what they just emailed me:

Below is an example of the query that was causing a load issue on our database server which was affecting other customers:

BAD QUERIES:
SELECT DISTINCT pv_user_id, pv_type, pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user_id, pv_datetime HAVING (((pv_type)='profile') AND ((pv_page_id)=1) AND pv_user_id > 0) ORDER BY pv_datetime DESC LIMIT 5;

Suggestion:
add a WHERE clause to the query

[doctorgirlfriend]

The "pageviews" might refer to the Page Statistics module. That module has crashed my entire server every time I've used it (twice), so I disabled it. I'm with LiquidWeb.

[fightlinker]

How do you disable it?

Thanks
Ryan

[dollars5]

Page view table is used to record all page loadings - and page statistics module uses it only if the link to call this module is used - there should be no reason that it would break your site.

If you feel page view table is growing and is causing problems, then you can manually find and comment the select and updates queries for pageview tables that should fix it - but am not sure and have never tried before.

[Simon]

Wouldn't it be easier to include an admin option in future versions of pligg to turn on / off all pageview reading and writing? I found that the pageview data isn't really necessary, so I removed all references to it. Having it set to off by default will make sure that fewer people will run into trouble with it, since it seems to be causing problems for some.

[kbeeveer46]

The query above is for the "last 5 people to view this profile" feature.

[Simon]

I meant anything stored in the pageviews table (profile, story, out records) - it builds up a very large table very quickly, but I wouldn't say the benefit it brings is that great unless you have a specific reason for tracking those kind of events. It is a useful feature to have, but maybe better as optional rather than always on?

On a related note (keeping db table size down) - I was thinking of putting in an extra query on the first stage submit that deletes all "discard" links that are more than, say, 1 hour old.

[fightlinker]

So that query is only generated when the page to view a user's profile is shown? If i go into the files used by that page i can find the code and kill the query?

[dollars5]

What we do is manually back up them every month and clean up the DB - so no page view accumulation problem