Possible Database Issue - MAY NEED A DATABASE ADMIN

[Dragon41673]

Hello,

I have received 2 warnings from Siteground based on CPU Abuse caused by my Database. I'm unable to resolve this myself, and am in need of help from a person that knows Joomla Database issues. I cannot make out what they are showing me...so I've included it below. The only things I can make out are something with K2, an ID for an item...and that's it. They are now saying I have 24 hours to resolve...or they will suspend my site.

Can someone PLEASE help me ASAP?

I've starred out some info as it was not needed

Thank you

Code: Select all

=== Databases Info ============================================================
Database Tables Views InnoDB MyISAM Slow Queries Slowest Query DB Size
-------------- ------ ----- ------ ------ ------------ ------------- --------
********** 167 0 80 85 173 3.207 151.6 MB
1. Executed 4h 32m 58s ago for 3.207368 sec on Database --> **********
Date: 2016-07-31 11:57:25 Query_time: 3.207368 Rows_examined: 43471: Rows_sent 10 Lock_time: 0.000065
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 19770, 10;
-----------------------------------------------------------------------------------------------------
2. Executed 8h 5m 53s ago for 2.858708 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-07-31 08:24:30 Query_time: 2.858708 Rows_examined: 37887: Rows_sent 10 Lock_time: 0.000054
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 14270, 10;
-----------------------------------------------------------------------------------------------------
3. Executed 9h 20m 13s ago for 2.763078 sec on Database --> **********
Date: 2016-07-31 07:10:10 Query_time: 2.763078 Rows_examined: 33819: Rows_sent 10 Lock_time: 0.000084
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 10230, 10;
-----------------------------------------------------------------------------------------------------
4. Executed 8h 20m 47s ago for 2.700681 sec on Database --> **********
Date: 2016-07-31 08:09:36 Query_time: 2.700681 Rows_examined: 41468: Rows_sent 10 Lock_time: 0.000803
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 17860, 10;
-----------------------------------------------------------------------------------------------------
5. Executed 7h 53m 59s ago for 2.638383 sec on Database --> **********
Date: 2016-07-31 08:36:24 Query_time: 2.638383 Rows_examined: 44959: Rows_sent 10 Lock_time: 0.000050
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 21340, 10;
-----------------------------------------------------------------------------------------------------
6. Executed 9h 34m 8s ago for 2.588818 sec on Database --> **********
Date: 2016-07-31 06:56:15 Query_time: 2.588818 Rows_examined: 530736: Rows_sent 12 Lock_time: 0.000386
# Schema: ********** Last_errno: 0 Killed: 0 SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.published, a.access, a.created, a.modified, a.extra_fields, a.extra_fields_search, a.created_by, a.created_by_alias, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.ordering, a.language, a.metakey, a.metadesc, a.metadata,c.name AS category_title, c.image AS category_image, c.alias as category_alias,CONCAT_WS(",", f.id) AS field_ids, CONCAT_WS(",", f.name) AS field_names,CONCAT_WS(",", t.id) AS tag_ids, CONCAT_WS(",", t.name) AS tag_names,COUNT(co.id) AS comment_count,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM jos_k2_items as a LEFT JOIN jos_k2_categories AS c ON c.id = a.catid LEFT JOIN jos_k2_extra_fields_groups AS fg ON fg.id = c.extraFieldsGroup LEFT JOIN jos_k2_extra_fields AS f ON f.group = fg.id LEFT JOIN jos_k2_tags_xref AS tx ON tx.itemID = a.id LEFT JOIN jos_k2_tags AS t ON t.id = tx.tagID LEFT JOIN jos_k2_comments AS co ON co.itemID = a.id LEFT JOIN jos_users AS ua ON ua.id = a.created_by LEFT JOIN jos_users AS um ON um.id = a.modified_by LEFT JOIN jos_k2_rating AS v ON a.id = v.itemID LEFT JOIN jos_viewlevels AS vl ON a.access = vl.id LEFT JOIN jos_languages AS l ON a.language = l.lang_code WHERE ((a.access IN(1,1) AND a.trash = 0 AND a.published = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-07-31 11:56:12') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-07-31 11:56:12')) AND (a.catid IN (93))) GROUP BY a.id;
------------------------------------------------------------------------------------------------------
7. Executed 10h 5m 47s ago for 2.557902 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-07-31 06:24:36 Query_time: 2.557902 Rows_examined: 530268: Rows_sent 12 Lock_time: 0.000351
# Schema: ********** Last_errno: 0 Killed: 0 SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.published, a.access, a.created, a.modified, a.extra_fields, a.extra_fields_search, a.created_by, a.created_by_alias, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.ordering, a.language, a.metakey, a.metadesc, a.metadata,c.name AS category_title, c.image AS category_image, c.alias as category_alias,CONCAT_WS(",", f.id) AS field_ids, CONCAT_WS(",", f.name) AS field_names,CONCAT_WS(",", t.id) AS tag_ids, CONCAT_WS(",", t.name) AS tag_names,COUNT(co.id) AS comment_count,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM jos_k2_items as a LEFT JOIN jos_k2_categories AS c ON c.id = a.catid LEFT JOIN jos_k2_extra_fields_groups AS fg ON fg.id = c.extraFieldsGroup LEFT JOIN jos_k2_extra_fields AS f ON f.group = fg.id LEFT JOIN jos_k2_tags_xref AS tx ON tx.itemID = a.id LEFT JOIN jos_k2_tags AS t ON t.id = tx.tagID LEFT JOIN jos_k2_comments AS co ON co.itemID = a.id LEFT JOIN jos_users AS ua ON ua.id = a.created_by LEFT JOIN jos_users AS um ON um.id = a.modified_by LEFT JOIN jos_k2_rating AS v ON a.id = v.itemID LEFT JOIN jos_viewlevels AS vl ON a.access = vl.id LEFT JOIN jos_languages AS l ON a.language = l.lang_code WHERE ((a.access IN(1,1) AND a.trash = 0 AND a.published = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-07-31 11:24:34') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-07-31 11:24:34')) AND (a.catid IN (93))) GROUP BY a.id;
------------------------------------------------------------------------------------------------------
8. Executed 11h 8m 45s ago for 2.555371 sec on Database --> **********
Date: 2016-07-31 05:21:38 Query_time: 2.555371 Rows_examined: 95936: Rows_sent 8 Lock_time: 0.000102
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=137 AND published=1 ORDER BY commentDate DESC LIMIT 47960, 10;
----------------------------------------------------------------------------------------------------
9. Executed 13h 6m 19s ago for 2.542343 sec on Database --> **********
Date: 2016-07-31 03:24:04 Query_time: 2.542343 Rows_examined: 0: Rows_sent 0 Lock_time: 0.050258
# Schema: ********** Last_errno: 0 Killed: 0 SELECT `data` FROM `jos_session` WHERE `session_id` = 'fd5e83f5f848e21ad3e27d9b2d32355e';
------------------------------------------------------------------------------------------------
10. Executed 12h 52m 0s ago for 2.4621 sec on Database --> **********
Date: 2016-07-31 03:38:23 Query_time: 2.462100 Rows_examined: 527562: Rows_sent 12 Lock_time: 0.005152
# Schema: ********** Last_errno: 0 Killed: 0 SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.published, a.access, a.created, a.modified, a.extra_fields, a.extra_fields_search, a.created_by, a.created_by_alias, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.ordering, a.language, a.metakey, a.metadesc, a.metadata,c.name AS category_title, c.image AS category_image, c.alias as category_alias,CONCAT_WS(",", f.id) AS field_ids, CONCAT_WS(",", f.name) AS field_names,CONCAT_WS(",", t.id) AS tag_ids, CONCAT_WS(",", t.name) AS tag_names,COUNT(co.id) AS comment_count,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM jos_k2_items as a LEFT JOIN jos_k2_categories AS c ON c.id = a.catid LEFT JOIN jos_k2_extra_fields_groups AS fg ON fg.id = c.extraFieldsGroup LEFT JOIN jos_k2_extra_fields AS f ON f.group = fg.id LEFT JOIN jos_k2_tags_xref AS tx ON tx.itemID = a.id LEFT JOIN jos_k2_tags AS t ON t.id = tx.tagID LEFT JOIN jos_k2_comments AS co ON co.itemID = a.id LEFT JOIN jos_users AS ua ON ua.id = a.created_by LEFT JOIN jos_users AS um ON um.id = a.modified_by LEFT JOIN jos_k2_rating AS v ON a.id = v.itemID LEFT JOIN jos_viewlevels AS vl ON a.access = vl.id LEFT JOIN jos_languages AS l ON a.language = l.lang_code WHERE ((a.access IN(1,1) AND a.trash = 0 AND a.published = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-07-31 08:38:21') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-07-31 08:38:21')) AND (a.catid IN (93))) GROUP BY a.id;
------------------------------------------------------------------------------------------------------

[itoctopus]

The problem that you are having is because of the LIMIT in your query (LIMIT tend to slow down the whole MySQL database server on very high limit numbers). Do you really have a K2 article that has tens of thousands of comments? (article id: 134)

In any case, if most of these comments are not spam, then you should disable pagination after a certain number. If you are on VPS, you can also tweak the InnoDB settings to better suit your website.

I think that most (if not all) of these comments are spam - I can only think of a few websites that can have tens of thousands of comments on just one post.

[Dragon41673]

No one is supposed to be allowed to comment. My site was suspended right after I posted this...so I cannot even check.

Oddly enough, I found this...

The last time I backed up my Database was July 2nd (I was going to do it again this weekend). At that time, it totaled 59.0MB

Right now, per SiteGround...one month later, it totals 302.2MB

I know I added a few things to the sales site over the last month...but no WAY was it that much. It it possible that the increase is from those comments?

[itoctopus]

Yes - it's possible, among other spam. If you don't really need the comments, then I suggest you truncate that table (truncating means completely deleting the content of that table). The thing is, you will need to implement a mechanism to block this from happening in the future, because most likely it is still happening as we speak.

[Dragon41673]

Here is what they pulled right after the site was suspended...

Code: Select all

=== TOP 10 of 227 (total) Slow Queries for the past 24 hours ==========
1. Executed 19h 46m 56s ago for 5.395194 sec on Database --> ********** 
Date: 2016-08-04 20:34:24 Query_time: 5.395194 Rows_examined: 658650: Rows_sent 12 Lock_time: 0.007695
# Schema: ********** Last_errno: 0 Killed: 0 SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.published, a.access, a.created, a.modified, a.extra_fields, a.extra_fields_search, a.created_by, a.created_by_alias, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.ordering, a.language, a.metakey, a.metadesc, a.metadata,c.name AS category_title, c.image AS category_image, c.alias as category_alias,CONCAT_WS(",", f.id) AS field_ids, CONCAT_WS(",", f.name) AS field_names,CONCAT_WS(",", t.id) AS tag_ids, CONCAT_WS(",", t.name) AS tag_names,COUNT(co.id) AS comment_count,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM jos_k2_items as a LEFT JOIN jos_k2_categories AS c ON c.id = a.catid LEFT JOIN jos_k2_extra_fields_groups AS fg ON fg.id = c.extraFieldsGroup LEFT JOIN jos_k2_extra_fields AS f ON f.group = fg.id LEFT JOIN jos_k2_tags_xref AS tx ON tx.itemID = a.id LEFT JOIN jos_k2_tags AS t ON t.id= tx.tagID LEFT JOIN jos_k2_comments AS co ON co.itemID = a.id LEFT JOIN jos_users AS ua ON ua.id = a.created_by LEFT JOIN jos_users AS um ON um.id = a.modified_by LEFT JOIN jos_k2_rating AS v ON a.id = v.itemID LEFT JOIN jos_viewlevels AS vl ON a.access = vl.id LEFT JOIN jos_languages AS l ON a.language = l.lang_code WHERE ((a.access IN(1,1) AND a.trash = 0 AND a.published = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-08-05 01:34:18') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-08-05 01:34:18')) AND (a.catid IN (93))) GROUP BY a.id; 
------------------------------------------------------------------------------------------------------
2. Executed 8h 44m 25s ago for 5.022415 sec on Database --> ********** 
Date: 2016-08-05 07:36:55 Query_time: 5.022415 Rows_examined: 679332: Rows_sent 12 Lock_time: 0.005497
# Schema: ********** Last_errno: 0 Killed: 0 SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.published, a.access, a.created, a.modified, a.extra_fields, a.extra_fields_search, a.created_by, a.created_by_alias, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.ordering, a.language, a.metakey, a.metadesc, a.metadata,c.name AS category_title, c.image AS category_image, c.alias as category_alias,CONCAT_WS(",", f.id) AS field_ids, CONCAT_WS(",", f.name) AS field_names,CONCAT_WS(",", t.id) AS tag_ids, CONCAT_WS(",", t.name) AS tag_names,COUNT(co.id) AS comment_count,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title FROM jos_k2_items as a LEFT JOIN jos_k2_categories AS c ON c.id = a.catid LEFT JOIN jos_k2_extra_fields_groups AS fg ON fg.id = c.extraFieldsGroup LEFT JOIN jos_k2_extra_fields AS f ON f.group = fg.id LEFT JOIN jos_k2_tags_xref AS tx ON tx.itemID = a.id LEFT JOIN jos_k2_tags AS t ON t.id= tx.tagID LEFT JOIN jos_k2_comments AS co ON co.itemID = a.id LEFT JOIN jos_users AS ua ON ua.id = a.created_by LEFT JOIN jos_users AS um ON um.id = a.modified_by LEFT JOIN jos_k2_rating AS v ON a.id = v.itemID LEFT JOIN jos_viewlevels AS vl ON a.access = vl.id LEFT JOIN jos_languages AS l ON a.language = l.lang_code WHERE ((a.access IN(1,1) AND a.trash = 0 AND a.published = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2016-08-05 12:36:50') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2016-08-05 12:36:50')) AND (a.catid IN (93))) GROUP BY a.id; 
------------------------------------------------------------------------------------------------------
3. Executed 20h 38m 55s ago for 4.753918 sec on Database --> ********** 
Date: 2016-08-04 19:42:25 Query_time: 4.753918 Rows_examined: 110263: Rows_sent 10 Lock_time: 0.000055
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=137 AND published=1 ORDER BY commentDate DESC LIMIT 55120, 10; 
------------------------------------------------------------------------------------------------------
4. Executed 9h 19m 8s ago for 4.044773 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-08-05 07:02:12 Query_time: 4.044773 Rows_examined: 0: Rows_sent 0 Lock_time: 0.000046
# Schema: ********** Last_errno: 0 Killed: 0 INSERT INTO `jos_session` (`session_id`, `client_id`, `time`) VALUES ('667e25f0625d148d99b5656a2bb605b2', 0, '1470398528'); 
------------------------------------------------------------------------------------------------
5. Executed 21h 2m 43s ago for 3.93611 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-08-04 19:18:37 Query_time: 3.936110 Rows_examined: 0: Rows_sent 0 Lock_time: 0.000038
# Schema: ********** Last_errno: 0 Killed: 0 DELETE FROM `jos_session` WHERE `time` < '1470347313'; 
------------------------------------------------------------------------------------------------
6. Executed 21h 2m 43s ago for 3.932251 sec on Database --> ********** 
Date: 2016-08-04 19:18:37 Query_time: 3.932251 Rows_examined: 1: Rows_sent 1 Lock_time: 0.000067
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_items WHERE id=133 LIMIT 0, 1; 
------------------------------------------------------------------------------------------------
7. Executed 13h 47m 3s ago for 3.93148 sec on Database --> ********** 
Date: 2016-08-05 02:34:17 Query_time: 3.931480 Rows_examined: 81179: Rows_sent 10 Lock_time: 0.000073
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=137 AND published=1 ORDER BY commentDate DESC LIMIT 25570, 10; 
-----------------------------------------------------------------------------------------------------
8. Executed 3h 25m 20s ago for 3.911465 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-08-05 12:56:00 Query_time: 3.911465 Rows_examined: 31076: Rows_sent 10 Lock_time: 0.000070
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=134 AND published=1 ORDER BY commentDate DESC LIMIT 4640, 10;
-----------------------------------------------------------------------------------------------------
9. Executed 11h 6m 5s ago for 3.870835 sec on Database --> ********** 
Date: 2016-08-05 05:15:15 Query_time: 3.870835 Rows_examined: 109123: Rows_sent 10 Lock_time: 0.000061
# Schema: ********** Last_errno: 0 Killed: 0 SELECT * FROM jos_k2_comments WHERE itemID=137 AND published=1 ORDER BY commentDate DESC LIMIT 53330, 10; 
------------------------------------------------------------------------------------------------------
10. Executed 21h 2m 43s ago for 3.845185 sec on Database --> Unknown (NOT FOUND / DELETED)
Date: 2016-08-04 19:18:37 Query_time: 3.845185 Rows_examined: 1: Rows_sent 0 Lock_time: 0.000238
# Schema: ********** Last_errno: 0 Killed: 0 UPDATE `jos_session` SET `data` = 'joomla|s:976:\"TzoyNDoiSm9vbWxhXFJlZ2lzdHJ5XFJlZ2lzdHJ5IjoyOntzOjc6IgAqAGRhdGEiO086ODoic3RkQ2xhc3MiOjE6e3M6OToiX19kZWZhdWx0IjtPOjg6InN0ZENsYXNzIjo0OntzOjc6InNlc3Npb24iO086ODoic3RkQ2xhc3MiOjM6e3M6NzoiY291bnRlciI7aToxO3M6NToidGltZXIiO086ODoic3RkQ2xhc3MiOjM6e3M6NToic3RhcnQiO2k6MTQ3MDM1NjMxMztzOjQ6Imxhc3QiO2k6MTQ3MDM1NjMxMztzOjM6Im5vdyI7aToxNDcwMzU2MzEzO31zOjU6InRva2VuIjtzOjMyOiJXR2dyNnBVcVR3aU13ZnRoT25tU2ZvY1JzSmExOUdrMyI7fXM6ODoicmVnaXN0cnkiO086MjQ6Ikpvb21sYVxSZWdpc3RyeVxSZWdpc3RyeSI6Mjp7czo3OiIAKgBkYXRhIjtPOjg6InN0ZENsYXNzIjowOnt9czo5OiJzZXBhcmF0b3IiO3M6MToiLiI7fXM6NDoidXNlciI7Tzo1OiJKVXNlciI6MTp7czoyOiJpZCI7aTowO31zOjEwOiJjb21fbWFpbHRvIjtPOjg6InN0ZENsYXNzIjoxOntzOjU6ImxpbmtzIjthOjE6e3M6NDA6ImY4NWRkZjAyMjZlM2E2OWZiMWIwYWNiMzRhYmJiOTQ5Y2Q0YzI2NmYiO086ODoic3RkQ2xhc3MiOjI6e3M6NDoibGluayI7czo4MToiaHR0cDovL3d3dy5hcmllc2dhbWVzYW5kbWluaXMuY29tL2luZGV4LnBocC9jb21wb25lbnQvazIvaXRlbS8xMzMtcm9kb2xmbzc3Ny5odG1sIjtzOjY6ImV4cGlyeSI7aToxNDcwMzU2MzEzO319fX19czo5OiJzZXBhcmF0b3IiO3M6MToiLiI7fQ==\";' , `time` = '1470356313' WHERE `session_id` = 'cc455795205d26f2596efb76b4624993'; 
------------------------------------------------------------------------------------------------

[Dragon41673]

Yes - it's possible, among other spam. If you don't really need the comments, then I suggest you truncate that table (truncating means completely deleting the content of that table). The thing is, you will need to implement a mechanism to block this from happening in the future, because most likely it is still happening as we speak.

I'll be honest...I don't even know what table to truncate. Is there more than one?

Thank you

[itoctopus]

The table to truncate is the `jos_k2_comments` table - just to make sure - this will delete all the comments on your website (including legitimate comments, if any). Again, you will need to close the loophole or disable the system altogether in order to prevent this from happening again.

[Webdongle]

Please run the fpa http://forum.joomla.org/viewtopic.php?f=621&t=582860 and post the results here. There is a possibility that your site has been compromised.

[Dragon41673]

The table to truncate is the `jos_k2_comments` table - just to make sure - this will delete all the comments on your website (including legitimate comments, if any). Again, you will need to close the loophole or disable the system altogether in order to prevent this from happening again.

Ok, entry has been emptied (truncated)...I've asked my host to see if this continues. I will keep an eye on it as well. If this continues...I'll run the FPA thing and post here.

I've also confirmed that ALL selections for comments in K2 are turned off.