• Support will be limited until May 1st, as I will be out of the office travelling. Thank you for your patience and understanding.
Status
Not open for further replies.

Home Alone

Customer
I'm still having problems with the Thanks mod and slow queries. I tried changing the dbtech_thanks_entry table to Innodb and the query time went from 1 - 2 seconds to 9 - 10 seconds. Any idea why this happened? What tables can be changed to innodb for this mod that will help with the slow queries?
 
We have ran no tests on InnoDB conversion of tables, as InnoDB storage engine introduces pagination issues on our particular system. Performance of InnoDB varies greatly based on your server configuration and my.cnf variables.

There are already tickets open for further optimisation of APTL queries, and without a slow query log I cannot tell you whether I can do anything today or whether I need to gain more MySQL knowledge first.
 
Snippets from the slow query log:

# User@Host: root[root] @ [10.0.0.1]
# Thread_id: 606229 Schema: lipstick_forum QC_hit: No
# Query_time: 2.098514 Lock_time: 0.000127 Rows_sent: 8 Rows_examined: 4197514
SET timestamp=1356977957;
SELECT
entry.*,
user.userid,
user.username,
user.usergroupid,
user.membergroupids,
user.infractiongroupid,
user.displaygroupid
, user.dbtech_vbshop_purchase
FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,5492853,5492930,5493188,5493193,5493212,5493222,5493358,5493474,5493527,5493572,5493601,7474190,7474252,7474665,7474757) AND contenttype = 'post') AS entry
LEFT JOIN user AS user USING(userid)
ORDER BY entry.entryid DESC;



# User@Host: root[root] @ [10.0.0.1]
# Thread_id: 606203 Schema: lipstick_forum QC_hit: No
# Query_time: 1.985057 Lock_time: 0.000080 Rows_sent: 44 Rows_examined: 4197585
SET timestamp=1356977956;
SELECT
entry.*,
user.userid,
user.username,
user.usergroupid,
user.membergroupids,
user.infractiongroupid,
user.displaygroupid
, user.dbtech_vbshop_purchase
FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,11696007,11696032,11696047,11696049,11696055,11696058,11696096,11696097,11696098,11696119,11696133,11696155,11696205,11696260,11696300) AND contenttype = 'post') AS entry
LEFT JOIN user AS user USING(userid)
ORDER BY entry.entryid DESC;


# User@Host: root[root] @ [10.0.0.1]
# Thread_id: 606169 Schema: lipstick_forum QC_hit: No
# Query_time: 2.006247 Lock_time: 0.000091 Rows_sent: 48 Rows_examined: 4197593
SET timestamp=1356977955;
SELECT
entry.*,
user.userid,
user.username,
user.usergroupid,
user.membergroupids,
user.infractiongroupid,
user.displaygroupid
, user.dbtech_vbshop_purchase
FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,11695905,11695927,11695935,11695941,11695945,11695952,11695953,11695955,11695961,11695967,11695970,11695975,11695981,11695984,11695985) AND contenttype = 'post') AS entry
LEFT JOIN user AS user USING(userid)
ORDER BY entry.entryid DESC;
 
To my knowledge today these queries cannot be optimised any further. However, I'm always learning new things and I will go back and change these queries when I've figured out a way to reduce the time needed to run those queries :)
 
Not all systems have this issue with InnoDB or indeed performance issues with InnoDB, as I said it depends wildly on a number of factors. In fact, it's a near infinite number of factors.

That being said, I believe I specify tables to be created as MyISAM in the installer.
 
I added low_priority_updates=1 to my my.cnf file then watched the MySQL processes. Queries that deleted anything from dbtech_thanks_entry took a very long time to run, sometimes as long as 10 minutes. I disabled the ability for all user groups to un-click a thanks or like and the load on the server has been stable since.

If I disable the mod completely the load on the mysql server averages from .6 to 3 over a 24 hour period.
If I enable the mod and enable the option to un-click for all user groups the load on the mysql server ranges from 20 to as high as 400.
If I enable the mod and disable the option to un-click for all user groups the load ranges from 6 to 20.

The database server configuration:
Dual Hexa-Core 2.0Ghz Intel Xeon Romley E5-2620
64GB RAM
32GB Swap
4 x 240GB SSD Intel 520 series drives in RAID 10

my.cnf has been optimized with suggestions from George @vbulletin.com.

Any idea why un-clicking causes such a problem?
 
That could be because the table is very heavily indexed. I'd suggest taking a backup of the table structure then dropping all the indexes - to be honest, I'm not entirely sure which of these indexes are still in use.
 
Here are the current indexes:

Code:
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dbtech_thanks_entry |          0 | PRIMARY        |            1 | entryid        | A         |     4253101 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            1 | userid         | A         |        5069 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            2 | entryid        | A         |     4253101 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            1 | receiveduserid | A         |        6729 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            2 | entryid        | A         |     4253101 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid         |            1 | userid         | A         |        5069 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | receiveduserid |            1 | receiveduserid | A         |        6729 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            1 | userid         | A         |        5069 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            2 | contenttype    | A         |        5559 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            3 | dateline       | A         |     4253101 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            1 | varname        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            2 | contenttype    | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            3 | entryid        | A         |     4253101 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            1 | varname        | NULL      |           1 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Are you saying it's safe to drop all of them?
 
Yeah, they can always be re-added if you find this significantly slows down general behaviour of the mod.

I'll also go through every query this mod makes and check what indexes they hit, and let you know which to re-add. This may take a few days (up to about a week) however, as I'm currently preparing to go home after my Xmas visit to my mother :)

If I take longer than a week then feel free to poke me to remind me :)
 
Yeah, they can always be re-added if you find this significantly slows down general behaviour of the mod.

I'll also go through every query this mod makes and check what indexes they hit, and let you know which to re-add. This may take a few days (up to about a week) however, as I'm currently preparing to go home after my Xmas visit to my mother :)

If I take longer than a week then feel free to poke me to remind me :)

Did you have a chance to check the indexes? The slow queries are killing my big-board:

Code:
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940240  Schema: lipstick_forum  QC_hit: No
# Query_time: 33.533731  Lock_time: 0.000146  Rows_sent: 0  Rows_examined: 5100585
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,5308411,5308426,5308472,5308529,5309155,5309351,5309369,5309408,5309676,5481204,5481298,5485421,5485444,5486016,5488512) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940481  Schema: lipstick_forum  QC_hit: No
# Query_time: 28.300901  Lock_time: 0.000099  Rows_sent: 15  Rows_examined: 5100615
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,12056760,12056831,12056842,12056843,12056897,12056974,12056989,12057005,12057011,12057088,12057090,12057091,12057237,12057286,12057287) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 938899  Schema: lipstick_forum  QC_hit: No
# Query_time: 23.397212  Lock_time: 0.000136  Rows_sent: 4  Rows_examined: 5100595
SET timestamp=1360610973;
SELECT
                                entry.*,
                                user.userid,
                                user.username,
                                user.usergroupid,
                                user.membergroupids,
                                user.infractiongroupid,
                                user.displaygroupid
                                , user.dbtech_vbshop_purchase
                        FROM (SELECT * FROM dbtech_thanks_entry AS entry WHERE contentid = '12025664' AND contenttype = 'post') AS entry
                        LEFT JOIN user AS user USING(userid)
                        ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940188  Schema: lipstick_forum  QC_hit: No
# Query_time: 34.671687  Lock_time: 0.000158  Rows_sent: 69  Rows_examined: 5100723
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,11583336,11583354,11583356,11583407,11583410,11583421,11583430,11583583,11583840,11583854,11584119,11585733,11586454) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940242  Schema: lipstick_forum  QC_hit: No
# Query_time: 33.543791  Lock_time: 0.000155  Rows_sent: 0  Rows_examined: 5100585
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,5308411,5308426,5308472,5308529,5309155,5309351,5309369,5309408,5309676,5481204,5481298,5485421,5485444,5486016,5488512) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940164  Schema: lipstick_forum  QC_hit: No
# Query_time: 35.196080  Lock_time: 0.000121  Rows_sent: 6  Rows_examined: 5100597
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,12048142,12048151,12048152,12048157,12048159,12048161,12048164,12048167,12048171,12048176,12048179,12048181,12048184,12048187,12048188) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940500  Schema: lipstick_forum  QC_hit: No
# Query_time: 27.601320  Lock_time: 0.000102  Rows_sent: 9  Rows_examined: 5100603
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,12038877,12039021,12039112,12039269,12039344,12039403,12040279,12040512,12040708,12044457,12044638,12044700,12044986,12045031,12047612) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940312  Schema: lipstick_forum  QC_hit: No
# Query_time: 32.132398  Lock_time: 0.000172  Rows_sent: 12  Rows_examined: 5100609
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,12054653,12054661,12054671,12054684,12054714,12055480,12056067,12056811,12056975,12057047,12057228) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940243  Schema: lipstick_forum  QC_hit: No
# Query_time: 33.621635  Lock_time: 0.000147  Rows_sent: 0  Rows_examined: 5100585
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,938346,938391,938434,938437,938458,938472,938483,938505,938549,938590,938602,938679,938776,938973,938994) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940412  Schema: lipstick_forum  QC_hit: No
# Query_time: 29.992562  Lock_time: 0.000207  Rows_sent: 0  Rows_examined: 5100585
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,5308411,5308426,5308472,5308529,5309155,5309351,5309369,5309408,5309676,5481204,5481298,5485421,5485444,5486016,5488512) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 940398  Schema: lipstick_forum  QC_hit: No
# Query_time: 30.301898  Lock_time: 0.000206  Rows_sent: 0  Rows_examined: 5100585
SET timestamp=1360610973;
SELECT
                        entry.*,
                        user.userid,
                        user.username,
                        user.usergroupid,
                        user.membergroupids,
                        user.infractiongroupid,
                        user.displaygroupid
                        , user.dbtech_vbshop_purchase
                FROM (SELECT * FROM dbtech_thanks_entry WHERE contentid IN (0,5308411,5308426,5308472,5308529,5309155,5309351,5309369,5309408,5309676,5481204,5481298,5485421,5485444,5486016,5488512) AND contenttype = 'post') AS entry
                LEFT JOIN user AS user USING(userid)
                ORDER BY entry.entryid DESC;
 
I'm not sure why it's examining 5.1 million rows, it seems like an arbitrary number... On DBTech, the same number reflects the number of members we have, yet your forum doesn't have 5.1 million members (that'd be a feat :P)

In either case, all those queries come from the /dbtech/thanks/hooks/showthread_query.php hook file, which I have just updated.

It should no longer scan the user table and no longer attempts to sort the rows in the query itself. As a result, it should be much, much faster all around.

Give it a try, and let me know how it works for you :)

If you didn't know, you can enable in-browser query inspection by turning on Debug Mode in config.php (I'd recommend limiting it to your IP address, though) and then add &explain=1 to the end of your URL while on a thread.
 
It's examining the dbtech_thanks_entry table, which has 5,100,818 thanks/groans in it. There's no improvement after uploading the new /dbtech/thanks/hooks/showthread_query.php file.

Code:
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 1918908  Schema: lipstick_forum  QC_hit: No
# Query_time: 12.631689  Lock_time: 0.000051  Rows_sent: 1  Rows_examined: 5100813
SET timestamp=1360628789;
SELECT *
                FROM dbtech_thanks_entry AS entry
                WHERE contentid IN (0,12058825,12058858,12058860,12058874,12058882,12058912,12058991,12059048,12059053,12059066,12059077,12059108,12059125,12059417,12059433)
                        AND contenttype = 'post';
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 1918819  Schema: lipstick_forum  QC_hit: No
# Query_time: 14.344287  Lock_time: 0.000047  Rows_sent: 4  Rows_examined: 5100813
SET timestamp=1360628789;
SELECT *
                FROM dbtech_thanks_entry AS entry
                WHERE contentid IN (0,12034612,12036008,12036872,12041168,12044275,12044366,12056581)
                        AND contenttype = 'post';
# Time: 130211 19:26:30
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 1918784  Schema: lipstick_forum  QC_hit: No
# Query_time: 15.008290  Lock_time: 0.000053  Rows_sent: 0  Rows_examined: 5100813
SET timestamp=1360628790;
SELECT *
                FROM dbtech_thanks_entry AS entry
                WHERE contentid IN (0,3509261,3509265,3509266,3509267,3509268,3509269,3509270,3509271,3509272,3509274,3509275,3509276,3509277,3509278,3509279)
                        AND contenttype = 'post';
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 1918905  Schema: lipstick_forum  QC_hit: No
# Query_time: 13.210533  Lock_time: 0.000072  Rows_sent: 34  Rows_examined: 5100813
SET timestamp=1360628790;
SELECT *
                FROM dbtech_thanks_entry AS entry
                WHERE contentid IN (0,12047158,12047187,12047252,12047277,12047299,12047411,12047454,12047802,12047878,12047925,12048126,12048224,12048405,12048425,12048512,12048543,12048693,12048923,12048972,12049251,12049785,12052023,12054138,12054711,12055248,12055281,12055407,12055410,12055702,12055792,12055816,12055862,12055914,12055946,12056069,12056294,12056375,12056676,12058412,12059432,12059500,12059662)
                        AND contenttype = 'post';
 
Is there currently an index on contentid? If not, try adding a single index on contentid and run an OPTIMIZE TABLE afterwards.

The query time has been cut in half, I'd consider that an improvement :)
 
Open the table, click Structure, then click "Index" next to the contentid column. That will add a regular index (not unique, that would be very bad).

Then click Operations, then Optimize :)
 
Blank pages with the new file. I see this in the error log:

2013-02-11 20:01:37.518 [NOTICE] [70.189.49.38:3912-0#APVH_lipstickalley.com] [STDERR] PHP Fatal error: Class 'VBECOMMERCE' not found in /home/lipstick/public_html/dbtech/thanks/hooks/showthread_query.php on line 72
 
Adding the index to contentid helped tremendously. This is the only thing in the slow query log now:

Code:
# Time: 130211 20:05:48
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 2063521  Schema: lipstick_forum  QC_hit: No
# Query_time: 1.581044  Lock_time: 0.000048  Rows_sent: 6389  Rows_examined: 5100896
SET timestamp=1360631148;
SELECT entryid, varname, userid
                        FROM dbtech_thanks_entry AS entry
                        WHERE
                                entry.contenttype = 'post'
                                AND dateline >= '1360544746';
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 2063543  Schema: lipstick_forum  QC_hit: No
# Query_time: 1.556433  Lock_time: 0.000036  Rows_sent: 6389  Rows_examined: 5100896
SET timestamp=1360631148;
SELECT entryid, varname, userid
                        FROM dbtech_thanks_entry AS entry
                        WHERE
                                entry.contenttype = 'post'
                                AND dateline >= '1360544746';
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 2063551  Schema: lipstick_forum  QC_hit: No
# Query_time: 1.550173  Lock_time: 0.000040  Rows_sent: 6389  Rows_examined: 5100896
SET timestamp=1360631148;
SELECT entryid, varname, userid
                        FROM dbtech_thanks_entry AS entry
                        WHERE
                                entry.contenttype = 'post'
                                AND dateline >= '1360544746';
# User@Host: root[root] @  [10.0.0.1]
# Thread_id: 2063558  Schema: lipstick_forum  QC_hit: No
# Query_time: 1.508375  Lock_time: 0.000048  Rows_sent: 6389  Rows_examined: 5100896
SET timestamp=1360631148;
SELECT entryid, varname, userid
                        FROM dbtech_thanks_entry AS entry
                        WHERE
                                entry.contenttype = 'post'
                                AND dateline >= '1360544746';

This is bearable considering there are 1300 members logged into the site and 2,700 visitors.
 
Updated the file again, sorry about that. Copypasta ftl :(

For that one, you could try adding an index on contenttype and dateline (tick those 2 boxes in the structure then hit Index) and run Optimize again.
 
The fixed file and indexes work great. I would normally have to turn this mod off from 8:00 p.m.. to midnight. With the mod enabled the load on the mysql server would go as high as 600. With the fixes the load is now 1.10. And there are no more dbtech slow queries.
 
Status
Not open for further replies.

Similar threads

Legacy Advanced Post Thanks / Like

vBulletin 3.8.x vBulletin 4.x.x
Seller
DragonByte Technologies
Release date
Last update
Total downloads
4,046
Customer rating
5.00 star(s) 1 ratings
Back
Top