Site Inaccessible, Host Says "Queries inefficient"

[sonaro]

Hello,

My site was shut down/locked up, whatever. This is what my host says. What should I do?

"Yes, it looks like your queries are terribly inefficient and causing a
21000 second backlog. I have killed all of the processes at this time."

"This means you had some queries, mentioned in the first reply, working
on processing data for over 5 minutes. New connections piled up waiting
for those connections to finish and as a result your site was
inaccessible. A programmer needs to refactor the
code to fix it. The site wasn't pulled at all. Your code locked its
users from accessing it. 10 out of 10 connection slots were in use by
the site because you had 10 SELECT DISTINCT() queries stuck on
processing data."

[sonaro]

He must have 'unlocked' it. Is the problem caused from 9.0? Should I just upgrade to 9.1?

"Yes you're fine, but I'd dig a bit deeper into the issue to determine
why you had such a massive query. If you have a lot of data in the
database and improper indexes setup on the data then finding distinct
matches can be extremely costly in terms of processing time. Yes, your
database is fine. You would need to follow the developer's instructions
on updating the software if they have a fix or purge some old records."

Thanks,

-Chris

[kbeeveer46]

Is there any way they can show you which queries were causing the issues? It sounds like they shouldn't be hard to find since they pointed out that the queries in question were trying to find distinct values. I can do a search of the Pligg files and look for those queries.

[sonaro]

I replaced the actual db name and username with generic ones.

Thank you.

| 1569115 | DB_username | localhost | DB_name | Query
| 21127 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1570459 | DB_username | localhost | DB_name | Query
| 20827 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1571338 | DB_username | localhost | DB_name | Query
| 20632 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1571648 | DB_username | localhost | DB_name | Query
| 20538 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1572126 | DB_username | localhost | DB_name | Query
| 20414 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1573066 | DB_username | localhost | DB_name | Query
| 20200 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1573911 | DB_username | localhost | DB_name | Query
| 20013 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1574828 | DB_username | localhost | DB_name | Query
| 19815 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1575687 | DB_username | localhost | DB_name | Query
| 19614 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |
| 1576562 | DB_username | localhost | DB_name | Query
| 19418 | Removing duplicates | SELECT DISTINCT pv_user_id, pv_type,
pv_page_id FROM pageviews GROUP BY pv_type, pv_page_id, pv_user |

[sonaro]

If it survived the digg effect, does this mean there was some sort of 'build up?' Can something be emptied for the time being to get it back up?

[kbeeveer46]

Check this thread out MySQL Statement choking performance.

What that query does is insert a row into the table every time someone views a story on your website. Now, since you were on digg and you had TONS of people viewing that page it was creating a row in your pageviews table for every view. This may have created too much load for your database.

[sonaro]

Thank you kbeeveer46,

Is the solution to delete the lines of code in user.php and do what Ash says or is it just one or the other?

---------------
Ash:
The recommended fix is to open phpmyadmin and run these 2 queries.

Be sure to replace the word table_pageviews with the name of your table.

ALTER TABLE `table_pageviews` ADD KEY `select` (`pv_type`,`pv_page_id`,`pv_user_id`);";
ALTER TABLE `table_pageviews` ADD KEY `groupby` (`pv_type`,`pv_page_id`,`pv_datetime`,`pv_user_id` );";
---------------

I deleted the lines of code already and it is still not working because mysql is locked down. I will email the host to get in but I am a little weary of try the DB edit myself.

[sonaro]

Ok, the site is back up with the lines of code deleted from user.php. This should prevent the DB problem but not actually fix the pageviews problem?

I'm totally cool with pageviews being disabled if this is the case.

Thanks,

-Chris