Question Performance question

[janslu]

Hi Fillip H.,

sorry to bother you in another place but in my quest for vbseo removal I need to take care of thanks/vbseo likes. I've successfuly installed the lite version of the plugin and imported the vbseo likes. Recreated statistics and turned the plugin on. But the load on mysql server went significantly up, comparing to vbseo likes or db likes/thanks turned off. What's interesting is that it's not the number of queries but rather CPU load.
I'm on a large forum - 10 million posts, almost 2 million thanks imported from vbseo.

Anything that can be done to speed things up?

 

[Fillip H.]

Can you highlight any particular queries via the Slow Query Log?

 

[janslu]

Can you highlight any particular queries via the Slow Query Log?

There's nothing really exceptional about the execution time, it's just that after enabling the product server load went from 1.0 - 1.2 to 3.2-4.2, cpu usage from below 80% to 250% (4 cores).
But I have parsed slow log and found the query I also see quite often in mytop (usually only for a second):

SELECT * FROM vb_dbtech_thanks_entry AS entry WHERE contentid IN (010423037,10423376,10423509,10423542,10423558,10423574,10423637,10423671,10423815,10423905) AND contenttype = 'post'

mysql> explain SELECT * FROM vb_dbtech_thanks_entry AS entry WHERE contentid IN (0158504,745284,874609,897057,939944,983917,1610362,2087532,2088110,2088431) AND contenttype = 'post';
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | entry | ALL  | NULL          | NULL | NULL    | NULL | 1888238 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

There's also a query I cannot run at the moment - on the statistics page, when I go to topic/posts. This one works for extended periods of time - above one minute. But this feature is not necessary for me...

 

[Fillip H.]

Please try running this query and let me know whether it helps:

ALTER TABLE vb_dbtech_thanks_entry ADD INDEX `__tmp_content` (`contentid`, `contenttype`)

Then run the queries you pasted above again

 

[janslu]

Please try running this query and let me know whether it helps:

ALTER TABLE vb_dbtech_thanks_entry ADD INDEX `__tmp_content` (`contentid`, `contenttype`)

Then run the queries you pasted above again

Oh yes! the index changed the situation dramatically!

Server load went back to 1.55

ysql> explain SELECT * FROM vb_dbtech_thanks_entry AS entry WHERE contentid IN (0158504,745284,874609,897057,939944,983917,1610362,2087532,2088110,2088431) AND contenttype = 'post';
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | entry | range | __tmp_content | __tmp_content | 81      | NULL |   10 | Using where |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)

I've tried the thanks.php?do=hottest stats to get the query. It's:

mysql> explain SELECT COUNT(*) AS entrycount, post.title AS posttitle, thread.title AS threadtitle, thread.title AS title, post.postid, thread.forumid FROM vb_dbtech_thanks_entry AS entry LEFT JOIN vb_post AS post ON(post.postid = entry.contentid) LEFT JOIN vb_thread AS thread ON(thread.threadid = post.threadid) WHERE contenttype = 'post' AND varname = 'thanks' AND thread.title IS NOT NULL GROUP BY entry.contentid ORDER BY entrycount DESC LIMIT 5;
+----+-------------+--------+--------+------------------------------------------------------+-----------+---------+--------------------------+---------+----------------------------------------------+
| id | select_type | table  | type   | possible_keys                                        | key       | key_len | ref                      | rows    | Extra                                        |
+----+-------------+--------+--------+------------------------------------------------------+-----------+---------+--------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | entry  | ref    | entryid_3,__tmp_content                              | entryid_3 | 229     | const,const              | 1886011 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post   | eq_ref | PRIMARY,threadid,th_search,threadid_visible_dateline | PRIMARY   | 4       | babyboom.entry.contentid |       1 | Using where                                  |
|  1 | SIMPLE      | thread | eq_ref | PRIMARY                                              | PRIMARY   | 4       | babyboom.post.threadid   |       1 | Using where                                  |
+----+-------------+--------+--------+------------------------------------------------------+-----------+---------+--------------------------+---------+----------------------------------------------+

Unfortunately it got stuck and I had to kill it after 30 seconds manually.

If you don't mind discussing dbseo here - after your changes in 1.0.5 I've installed yesterday it is much, much better. But it's still 30% more load on the database than on vbseo. I'm still seeing a few queries in mytop, but they never stay during the refresh:

SELECT COUNT(*) AS earlierPosts FROM vb_post AS post WHERE post.threadid = 30234 AND post.visible = 1 AND post.dateline <= 1393669552
SELECT COUNT(*) AS earlierPosts FROM vb_post AS post WHERE post.threadid = 1147 AND post.visible = 1 AND post.dateline <= 1393614863

mysql> explain SELECT COUNT(*) AS earlierPosts FROM vb_post AS post WHERE post.threadid = 1147 AND post.visible = 1 AND post.dateline <= 1393614863;
+----+-------------+-------+------+-------------------------------------------------------+----------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys                                         | key      | key_len | ref   | rows  | Extra       |
+----+-------------+-------+------+-------------------------------------------------------+----------+---------+-------+-------+-------------+
|  1 | SIMPLE      | post  | ref  | threadid,th_search,threadid_visible_dateline,dateline | threadid | 4       | const | 45055 | Using where |
+----+-------------+-------+------+-------------------------------------------------------+----------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

 

[janslu]

One more query popped up - taking very long time:

mysql> explain SELECT entry.*, user.userid, user.username, user.usergroupid, user.membergroupids, user.infractiongroupid, user.displaygroupid FROM (SELECT * FROM vb_dbtech_thanks_entry WHERE contentid IN(SELECT gmid FROM vb_groupmessage WHERE discussionid = 2196) AND contenttype = 'socialgroup') AS entry LEFT JOIN vb_user AS user USING(userid) ORDER BY entry.entryid DESC;

+----+--------------------+------------------------+-----------------+----------------------+---------+---------+--------------+---------+----------------+
| id | select_type        | table                  | type            | possible_keys        | key     | key_len | ref          | rows    | Extra          |
+----+--------------------+------------------------+-----------------+----------------------+---------+---------+--------------+---------+----------------+
|  1 | PRIMARY            | <derived2>             | ALL             | NULL                 | NULL    | NULL    | NULL         |       5 | Using filesort |
|  1 | PRIMARY            | user                   | eq_ref          | PRIMARY              | PRIMARY | 4       | entry.userid |       1 |                |
|  2 | DERIVED            | vb_dbtech_thanks_entry | ALL             | NULL                 | NULL    | NULL    | NULL         | 1888341 | Using where    |
|  3 | DEPENDENT SUBQUERY | vb_groupmessage        | unique_subquery | PRIMARY,discussionid | PRIMARY | 4       | func         |       1 | Using where    |
+----+--------------------+------------------------+-----------------+----------------------+---------+---------+--------------+---------+----------------+
4 rows in set (4.44 sec)

It seems to be coming from social groups discussions. I have disabled like/thanks in this section so far.

 

[Kevin C]

We jumped to 89% CPU usage on our database server (24 cores) after installing this addon. Our normal load is around .8 on this server. Needless to say, not a good start.

We currently have the addon disabled until we finish importing our 8 million thanks from Abe's addon. After that we'll add the index mentioned above and re-enable and see if it helps.