Bug performance issues on big-boards with lots of thanks/likes

Status
Not open for further replies.
J

janklerks

Customer
We're having very similar problems to http://www.dragonbyte-tech.com/f37/absolutely-abysmal-performance-14410/ but I didn't want to hijack his thread.

We've added quite a few indexes to the dbtech_thanks_entry table to get it perform acceptably, however now that we're well over 7.5 million entries in the dbtech_thanks_entry table, It's starting to become a serious issue.

We are using this in conjunction with vBOptimise.


here's the current table indexes (there may actually be a few extras in here that aren't necessary) The table is InnoDB.

Code: 
mysql> show indexes from dbtech_thanks_entry;
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            1 | userid         | A         |       96026 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            2 | entryid        | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            1 | receiveduserid | A         |      105362 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            2 | entryid        | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | receiveduserid |            1 | receiveduserid | A         |      126435 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid         |            1 | userid         | A         |      101148 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            1 | userid         | A         |      109943 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            2 | contenttype    | A         |      114941 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            3 | dateline       | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            1 | contenttype    | A         |          28 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            2 | contentid      | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            1 | varname        | A         |          34 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            2 | contenttype    | A         |          34 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            3 | contentid      | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            1 | contenttype    | A         |          24 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            2 | dateline       | A         |     7586115 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
17 rows in set (0.18 sec)

and, from the other thread, I gathered some threads with lots of thanks/likes -

Code: 
mysql> SELECT COUNT(*) AS numthanks, threadid FROM dbtech_thanks_entry AS entry LEFT JOIN post AS post ON(post.postid = entry.contentid) WHERE contenttype = 'post' GROUP BY threadid ORDER BY numthanks DESC;
+-----------+----------+
| numthanks | threadid |
+-----------+----------+
|     91215 |   535388 |
|     63308 |   248862 |
|     36098 |  1205335 |
|     35014 |   951402 |
|     34423 |   639909 |
|     29463 |  1279413 |
|     28919 |  1386302 |
|     28236 |  1478071 |
|     26933 |   988547 |
|     25422 |   521993 |
|     23317 |   715972 |
|     22465 |  1282031 |
|     22042 |  1496968 |
|     21981 |  1279405 |
|     21565 |  1227453 |
|     20662 |   419362 |
|     19863 |  1610310 |
|     19119 |  1454785 |
|     18597 |   448668 |
|     18321 |  1702771 |
|     18035 |   391698 |
|     17555 |   258102 |
|     17290 |   436292 |
|     16845 |   907864 |
|     16481 |  1637108 |
|     15574 |  1185569 |
|     15330 |   533576 |
|     14475 |  1338277 |
|     14380 |  1232367 |
|     14127 |   866944 |
|     14037 |   552946 |
|     13877 |  1678165 |
|     13415 |  1592040 |
|     13135 |   496596 |
|     13065 |  1071971 |
|     12997 |  1597176 |
|     12718 |  1461770 |
|     12699 |  1154243 |
|     12649 |   619224 |
|     12623 |   584411 |
|     12114 |  1235673 |
|     11795 |  1195867 |
|     11701 |   526298 |
|     11651 |  1590461 |
|     11587 |  1694486 |
|     11343 |   902846 |
|     10956 |   180814 |
|     10866 |   893572 |
|     10777 |  1694463 |
|     10746 |  1546481 |
|     10535 |  1564587 |
|     10169 |   303650 |
|     10158 |   751306 |
|      9973 |   462238 |
|      9919 |   252329 |
|      9825 |  1543078 |
|      9697 |   210294 |
|      9672 |   987415 |
|      9616 |   312397 |
|      9363 |   450976 |
|      9326 |   211579 |
|      9323 |   902882 |
|      9318 |   623368 |
|      9073 |  1504557 |
|      8980 |   621473 |
|      8833 |  1072121 |
|      8789 |  1467252 |
|      8731 |   712180 |
|      8452 |  1589024 |
|      8437 |  1002079 |
|      8407 |  1633721 |
|      8355 |   591102 |
|      8166 |  1184839 |
|      8158 |   732972 |
|      8060 |   389562 |
|      7984 |   501192 |
|      7953 |  1105113 |
|      7877 |  1476943 |
|      7852 |   902866 |
|      7814 |  1193413 |
|      7792 |   586989 |
|      7704 |   410118 |
|      7403 |   196127 |
|      7357 |  1076301 |
|      7311 |  1195971 |
|      7306 |   328111 |
|      7298 |  1666368 |
|      7271 |   589712 |
|      7265 |  1557788 |
|      7209 |   233568 |
|      7202 |   902852 |
|      7163 |   289754 |
|      7061 |   649151 |
|      7055 |  1016985 |
|      6956 |   916096 |
|      6906 |  1669369 |
|      6867 |  1209153 |
|      6854 |  1622376 |
|      6835 |   409197 |
|      6802 |   142435 |
|      6736 |  1137657 |
|      6711 |  1650455 |
|      6684 |   296817 |
|      6651 |  1616158 |
|      6529 |  1675298 |
|      6502 |   620923 |
|      6501 |   572606 |
|      6451 |   824012 |
|      6352 |   324781 |
|      6329 |   248572 |
|      6279 |   330065 |
|      6218 |  1539298 |
|      6206 |   627065 |
|      6129 |  1691790 |
|      6122 |  1602228 |
|      6014 |  1437182 |
|      5989 |   505075 |
|      5973 |   312395 |
|      5905 |   841352 |
|      5892 |   125880 |
|      5876 |  1054781 |
|      5850 |  1016989 |
|      5846 |   565415 |
|      5825 |   429764 |
|      5821 |  1288357 |
|      5796 |   574298 |
|      5760 |   326552 |
|      5732 |   406050 |
|      5694 |   463228 |
|      5592 |   979994 |
|      5589 |   584932 |
|      5578 |  1096651 |
|      5525 |  1145371 |
|      5520 |   616596 |
|      5505 |   459134 |
|      5480 |   935364 |
|      5472 |   421289 |
|      5432 |   383003 |
|      5431 |  1585332 |
|      5368 |  1629952 |
|      5355 |   316909 |
|      5346 |   474259 |
|      5334 |   664188 |
|      5329 |    97155 |
|      5295 |   584077 |
|      5233 |   781324 |
|      5227 |   636797 |
|      5180 |   676730 |
|      5141 |   902836 |
|      5128 |  1485330 |
|      5110 |  1703213 |
|      5073 |   872588 |
|      5071 |  1107291 |
|      5040 |  1477662 |
|      5027 |   778352 |
|      5000 |  1358115 |
|      4953 |   902920 |
|      4926 |   559182 |
|      4923 |   525819 |
|      4907 |   268676 |
|      4863 |   517647 |
|      4860 |  1624852 |
|      4823 |   404830 |
|      4801 |  1518464 |
|      4797 |  1626406 |
|      4772 |  1552908 |
|      4771 |   373642 |
|      4720 |  1577981 |
|      4715 |   832114 |
|      4691 |   902874 |
|      4676 |   828816 |
|      4660 |   617380 |
|      4653 |   377403 |
|      4647 |   902894 |
|      4605 |   417666 |
|      4595 |   257492 |
|      4545 |   545083 |
|      4538 |   769564 |
|      4517 |  1399598 |
|      4490 |   955994 |
|      4476 |  1553988 |
|      4465 |  1597177 |
|      4459 |  1590092 |
|      4449 |  1690280 |
|      4440 |   347144 |
|      4416 |   465668 |
|      4384 |   988821 |
|      4383 |   244549 |
|      4358 |   572565 |
|      4320 |  1394666 |
|      4294 |   429804 |
|      4291 |  1077283 |
|      4289 |  1657774 |
|      4273 |   520031 |
|      4266 |   101602 |
|      4261 |  1422790 |
|      4259 |  1549099 |
|      4253 |   738932 |
|      4211 |  1707701 |
|      4200 |   105785 |
|      4184 |  1149643 |
|      4180 |   450211 |
|      4155 |   248878 |
|      4134 |   466766 |
|      4115 |   633339 |
|      4108 |   150050 |
|      4103 |   811646 |
|      4087 |   609716 |
|      4077 |   315079 |
|      4073 |   804178 |
|      4071 |    93057 |
|      4029 |  1337549 |
|      4022 |   997661 |
|      4018 |   217913 |
|      3998 |  1458518 |
|      3981 |   664296 |
|      3969 |   424489 |
|      3948 |   440790 |
|      3939 |  1629852 |
|      3896 |  1511420 |
|      3888 |  1454158 |
|      3853 |   902878 |
|      3850 |   665478 |
|      3850 |  1617497 |
|      3845 |  1452652 |
|      3824 |  1433647 |
|      3809 |  1641494 |
|      3790 |  1237079 |
|      3789 |   348428 |
|      3782 |  1663425 |
|      3779 |  1653423 |
|      3755 |   162411 |
|      3751 |  1235511 |
|      3742 |   359838 |
|      3737 |  1279265 |
|      3718 |   504738 |
|      3714 |  1514752 |
|      3710 |  1545969 |
|      3702 |   776476 |
|      3681 |  1640244 |
|      3656 |  1481300 |
|      3655 |  1078785 |
|      3642 |   771428 |
|      3641 |   431156 |
|      3639 |  1525535 |
|      3631 |   477094 |
|      3631 |  1527274 |
|      3627 |   303931 |
|      3625 |   264191 |
|      3624 |   434946 |
|      3619 |   435396 |
|      3618 |   712760 |
|      3595 |  1659733 |
|      3592 |   666588 |
|      3565 |  1668603 |
|      3551 |   431566 |
|      3528 |  1528931 |
|      3527 |  1544799 |
|      3522 |     NULL |
|      3513 |   763722 |
|      3500 |   219620 |
|      3494 |   530327 |
|      3478 |   211076 |
|      3478 |  1642029 |
|      3475 |   760274 |
|      3430 |  1029381 |
|      3417 |   428549 |
|      3408 |  1010057 |
|      3406 |  1637616 |
|      3406 |   392728 |
|      3385 |  1624502 |
|      3379 |   393451 |
|      3372 |  1583486 |
|      3370 |   498441 |
|      3360 |  1623655 |
|      3347 |  1548718 |
|      3344 |   900260 |
|      3335 |  1507997 |
|      3328 |   886358 |
|      3321 |   528872 |
|      3321 |  1662047 |
|      3317 |   415718 |
|      3314 |  1646095 |
|      3313 |   451636 |
|      3302 |  1156033 |
|      3301 |   833492 |
|      3296 |  1577681 |
|      3275 |   130185 |
|      3266 |   190673 |
|      3261 |  1640620 |
|      3249 |   297180 |
|      3229 |  1473086 |
|      3219 |   843262 |
|      3184 |  1494651 |
|      3172 |  1624222 |
|      3167 |   837458 |
|      3159 |  1185527 |
|      3137 |   752558 |
|      3131 |  1648139 |
|      3104 |   565193 |
|      3103 |  1645363 |
|      3095 |  1578775 |
|      3077 |   784362 |
|      3073 |  1616585 |
|      3070 |  1296429 |
|      3068 |   770578 |
|      3061 |   861082 |
|      3057 |  1646804 |
|      3049 |  1022349 |
|      3014 |   841110 |
|      3012 |   386617 |
|      3012 |  1587947 |
|      3009 |   769592 |
|      3007 |  1608237 |
|      2983 |  1161979 |
|      2980 |   569067 |
|      2976 |  1309545 |
|      2975 |  1199421 |
|      2954 |   564616 |
|      2948 |   658890 |
|      2945 |   594509 |
|      2940 |   971284 |
|      2937 |  1529119 |
|      2937 |   716634 |
|      2933 |  1029945 |
|      2928 |  1552467 |
|      2917 |  1662130 |
|      2914 |  1440626 |
|      2903 |  1077611 |
|      2896 |   456041 |
|      2895 |  1153917 |
|      2874 |  1120385 |
|      2874 |   409750 |
|      2855 |  1126337 |
|      2851 |  1626303 |
|      2850 |   545041 |
|      2848 |   902792 |
|      2846 |   599168 |
|      2842 |  1660101 |
|      2839 |  1682287 |
|      2820 |    88688 |
|      2819 |   902842 |
|      2818 |  1312307 |
|      2814 |    94760 |
|      2811 |   445441 |
|      2809 |   838486 |
|      2807 |  1174643 |
|      2799 |  1475806 |
|      2799 |   282708 |
|      2794 |   617348 |
|      2793 |   610068 |
|      2776 |  1576597 |
|      2768 |   455536 |
|      2759 |   746080 |
|      2755 |   821842 |
|      2753 |   899304 |
|      2749 |  1585326 |
|      2745 |  1554932 |
|      2741 |   440890 |
|      2738 |   604799 |
|      2735 |  1607574 |
|      2735 |   697888 |
|      2732 |  1608822 |
|      2719 |  1593079 |
|      2710 |   378475 |
|      2708 |  1068579 |
|      2703 |  1466962 |
|      2698 |   811412 |
|      2694 |  1619662 |
|      2685 |  1405436 |
|      2683 |   617420 |
|      2682 |   840100 |
|      2681 |   661008 |
|      2679 |   387445 |
|      2671 |  1623072 |
|      2667 |   842904 |
|      2659 |  1639056 |
|      2658 |   986662 |
|      2638 |  1349423 |
|      2634 |  1590345 |
|      2631 |  1590537 |
|      2624 |   536070 |
|      2603 |   266362 |
|      2601 |   993237 |
|      2601 |   279366 |
|      2597 |  1689649 |
|      2597 |  1249033 |
|      2576 |  1293335 |
|      2566 |   292999 |
|      2557 |  1565374 |
|      2551 |  1578747 |
|      2540 |   250017 |
|      2537 |  1465420 |
|      2531 |   623852 |
|      2531 |  1154741 |
|      2527 |  1436545 |
|      2504 |   781858 |
|      2502 |  1706591 |
|      2493 |   492377 |
|      2491 |  1323197 |
|      2490 |  1648113 |
|      2486 |  1575117 |
|      2478 |   477774 |
|      2474 |   851352 |
|      2473 |   390499 |
|      2472 |   616740 |
|      2471 |   507347 |
|      2471 |  1047133 |
|      2470 |   872592 |
|      2467 |   305147 |
|      2465 |  1538106 |
|      2438 |  1552366 |
|      2433 |  1161981 |
|      2425 |  1240383 |
|      2425 |   623353 |
|      2424 |   977176 |
|      2421 |   626417 |
|      2416 |   538215 |
|      2415 |   986078 |
|      2405 |  1112017 |
|      2403 |   468132 |
|      2402 |  1612513 |
|      2402 |  1204653 |
|      2401 |   725620 |
|      2383 |  1649881 |
|      2381 |   446150 |
|      2378 |  1669722 |
|      2377 |  1685313 |
|      2376 |   976116 |
|      2368 |   802680 |
|      2368 |   354758 |
|      2362 |  1364829 |
|      2361 |   122011 |
|      2357 |  1567595 |
|      2345 |   905946 |
|      2345 |   457285 |
|      2339 |  1588794 |
|      2339 |  1085299 |
|      2324 |   812594 |
|      2322 |  1085819 |
|      2321 |   645450 |
|      2315 |  1227913 |
|      2304 |  1135003 |
|      2302 |  1492936 |
|      2300 |   788896 |
|      2291 |  1016977 |
|      2290 |  1650272 |
|      2288 |  1432145 |
|      2286 |   746082 |
|      2276 |   836008 |
|      2274 |  1459342 |
|      2269 |  1593704 |
|      2256 |  1322753 |
|      2251 |   175804 |
|      2243 |   259852 |
|      2238 |  1586535 |
|      2236 |   793560 |
|      2223 |  1609288 |
|      2222 |   521763 |
|      2221 |  1395904 |
|      2219 |   194112 |
|      2212 |  1122049 |
|      2211 |  1253415 |
|      2208 |  1488967 |
|      2204 |  1094429 |
|      2199 |   542716 |
|      2196 |   588764 |
|      2195 |  1264555 |
|      2194 |   507805 |
|      2194 |   623849 |
|      2189 |   556189 |
|      2185 |  1649126 |
|      2181 |  1030701 |
|      2171 |   661638 |
|      2167 |   951332 |
|      2164 |   768148 |
|      2157 |   332767 |
|      2156 |  1566512 |
|      2156 |  1655143 |
|      2156 |  1541580 |
|      2151 |   603112 |
|      2147 |  1445457 |
|      2143 |   598789 |
|      2142 |   716118 |
|      2140 |  1370513 |
|      2138 |  1341641 |
|      2125 |   944672 |
|      2121 |  1524830 |
|      2115 |   519638 |
|      2115 |    96151 |
|      2114 |  1661452 |
|      2113 |  1606405 |
|      2112 |   325343 |
|      2112 |  1468455 |
|      2109 |  1006847 |
|      2108 |   545553 |
|      2106 |  1629225 |
|      2104 |   853168 |
|      2101 |  1589870 |
|      2100 |   783256 |
|      2097 |  1578140 |
|      2090 |   323812 |
|      2088 |  1145037 |
|      2088 |   623791 |
|      2086 |  1592064 |
|      2086 |  1533050 |
|      2085 |  1435737 |
|      2076 |  1693107 |
|      2074 |  1621941 |
|      2074 |  1434897 |
|      2073 |  1678672 |
|      2067 |  1105495 |
|      2066 |  1688438 |
|      2066 |  1651645 |
|      2065 |   441655 |
|      2061 |  1597001 |
|      2056 |  1656801 |
|      2047 |  1506472 |
|      2042 |  1416110 |
|      2042 |   774022 |
|      2037 |   408765 |
|      2018 |   434176 |
|      2018 |  1326421 |
|      2016 |  1632295 |
|      2015 |  1092819 |
|      2013 |  1663996 |
|      2012 |  1501014 |
|      2007 |  1592018 |
|      2004 |   520029 |
|      2003 |  1443533 |
|      2003 |   684546 |
|      2003 |  1563849 |
|      2003 |   867430 |
|      1994 |  1232643 |
|      1990 |   112047 |
|      1990 |  1693611 |
|      1989 |   973646 |
|      1988 |  1633274 |
|      1987 |  1660160 |
|      1986 |  1294785 |
|      1984 |  1549961 |
|      1983 |  1668636 |
|      1982 |   481191 |
|      1981 |  1640639 |
|      1978 |   875110 |
|      1977 |  1626777 |
|      1972 |  1069941 |
|      1965 |   164920 |
|      1964 |   579205 |
|      1962 |   935378 |
|      1960 |  1285653 |
|      1958 |   729418 |
|      1957 |  1638290 |
|      1956 |  1193403 |
|      1946 |  1584222 |
|      1937 |   210619 |
|      1935 |  1666994 |
|      1935 |   702584 |
|      1935 |   463513 |
|      1934 |   542943 |
|      1932 |  1439584 |
|      1930 |  1640962 |
|      1930 |  1570324 |
|      1929 |  1210337 |
|      1915 |   305854 |
|      1913 |   644756 |
|      1913 |   414682 |
|      1903 |   202698 |
|      1902 |   218732 |
|      1896 |   567666 |
|      1894 |   502392 |
|      1893 |   828080 |
|      1889 |  1687594 |
|      1888 |   193251 |
|      1881 |  1570484 |
|      1879 |   783922 |
|      1878 |  1639358 |
|      1876 |  1663187 |
|      1876 |  1658065 |
|      1874 |  1481747 |
|      1866 |   135440 |
|      1866 |  1553571 |
|      1864 |   537935 |
|      1864 |  1592053 |
|      1860 |   161887 |
|      1860 |   325431 |
|      1857 |   901170 |
|      1856 |  1636110 |
|      1853 |   428861 |
|      1853 |   338182 |
|      1853 |  1350075 |
|      1851 |  1383948 |
|      1850 |   808428 |
|      1846 |   623058 |
|      1844 |   653687 |
|      1843 |   834056 |
|      1843 |  1694933 |
|      1840 |   798680 |
|      1836 |  1557725 |
|      1835 |   321409 |
|      1834 |  1666301 |
|      1834 |   585691 |
|      1831 |  1186489 |
|      1830 |   300909 |
|      1827 |  1461109 |
|      1823 |  1632208 |
|      1823 |  1253359 |
|      1823 |   867620 |
|      1822 |  1552905 |
|      1821 |   928060 |
|      1819 |  1479665 |
|      1817 |  1119329 |
|      1816 |   641447 |
|      1815 |   617688 |
|      1812 |  1532797 |
|      1812 |   909396 |
|      1810 |  1130003 |
|      1809 |  1217321 |
|      1809 |   136232 |
|      1807 |  1633685 |
|      1807 |   351003 |
|      1804 |   364547 |
|      1802 |  1545381 |
|      1800 |   267947 |
|      1796 |  1552685 |
|      1795 |  1475843 |
|      1786 |  1142787 |
|      1781 |   441654 |
|      1780 |   678750 |
|      1773 |   170758 |
|      1773 |  1436764 |
|      1773 |  1076921 |
|      1769 |  1646678 |
|      1767 |  1163565 |
|      1766 |   566372 |
|      1765 |   529482 |
|      1764 |   195379 |
|      1753 |  1460563 |
|      1751 |  1590747 |
|      1751 |   856204 |
|      1750 |  1537401 |
|      1749 |  1383784 |
|      1744 |   941086 |
|      1743 |  1564149 |
|      1743 |  1421000 |
|      1742 |  1007107 |
|      1739 |  1331107 |
|      1734 |   672644 |
|      1721 |   357004 |
|      1719 |   784668 |
|      1718 |   820546 |
|      1716 |  1653670 |
|      1709 |  1067181 |
|      1708 |   804248 |
|      1708 |  1077477 |
|      1708 |  1598428 |
|      1705 |   441761 |
|      1704 |  1195049 |
|      1703 |  1114019 |
|      1702 |   387428 |
|      1701 |   581742 |
|      1699 |   578607 |
|      1698 |   567027 |
|      1697 |  1672504 |
|      1690 |   884322 |
|      1687 |   470776 |
|      1686 |   480755 |
|      1684 |  1605158 |
|      1680 |  1465969 |
|      1673 |  1203491 |
|      1671 |  1157079 |
|      1671 |  1357893 |
|      1665 |  1596783 |
|      1660 |  1675067 |
|      1651 |  1383090 |
|      1650 |   623885 |
|      1648 |   430368 |
|      1648 |   368115 |
|      1646 |   451090 |
|      1644 |  1283947 |
|      1641 |  1489761 |
|      1637 |  1632721 |
|      1633 |  1393930 |
|      1629 |   665286 |
|      1628 |  1683984 |
|      1623 |  1331783 |
|      1622 |   730054 |
|      1617 |  1590096 |
|      1614 |  1155519 |
|      1613 |  1571107 |
|      1611 |   420623 |
|      1609 |  1671431 |
|      1607 |  1546810 |
|      1606 |   431979 |
|      1605 |  1683812 |
|      1604 |  1506282 |
|      1603 |   808380 |
|      1602 |  1418100 |
|      1601 |   702858 |
|      1600 |  1378507 |
|      1599 |   788884 |
|      1596 |  1438996 |
|      1596 |   461803 |
|      1595 |   174610 |
|      1595 |   651281 |
|      1591 |  1605261 |
|      1589 |   385265 |
|      1587 |   441854 |
|      1585 |  1596822 |
|      1581 |   262382 |
|      1581 |  1528664 |
|      1581 |   990089 |
|      1581 |   612550 |
|      1580 |   933594 |
|      1577 |  1577656 |
|      1576 |   546137 |

(I killed it at that point)


The vast majority of slow queries in our slow query log grabs data from the dbtech_thanks tables. If you really need that data let me know and I can send it to you.


The master database server that this is running on is quite the monster. (PCIe-SSD drive for the MySQL data, 128GB RAM, 2x 10-core Xeons... ) So any help you can give would be appreciated. If you need more data, please let me know.
 
I will need a copy of each unique query (there's no need to show me the same query that pulls from different IDs, though) from the slow query log, so that I can isolate specific parts that will need improvement.
 
dbtech_thanks_statistics:
Code: 
# User@Host: USER[USER] @  [192.168.128.88]
# Thread_id: 87737136  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 7.465718  Lock_time: 0.000338  Rows_sent: 20  Rows_examined: 158  Rows_affected: 0  Rows_read: 158
# Bytes_sent: 59445
SET timestamp=1395444520;
SELECT
                        post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
                        user.*, userfield.*, usertextfield.*,

                        avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,


                        editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
                        editlog.reason AS edit_reason, editlog.hashistory,
                        postparsed.pagetext_html, postparsed.hasimages,
                        sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
                        sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
                        IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid

                        , dbtech_thanks_statistics.*, post.userid
                FROM post AS post
                LEFT JOIN user AS user ON(user.userid = post.userid)
                LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
                LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)

                LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)


                LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
                LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 2 AND postparsed.languageid = 6)
                LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 2 AND sigparsed.languageid = 6)
                LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
                         LEFT JOIN dbtech_thanks_statistics AS dbtech_thanks_statistics ON(dbtech_thanks_statistics.userid = post.userid)
                WHERE post.postid IN (0,34928112,34928448,34928568,34928610,34928682,34928708,34928806,34929112,34929346,34930002,34930476,34930542,34934044,34957818,34957870,34960366,34962742,34964784,34964836,34965462)
                ORDER BY post.dateline;

There's a lot like the above, but the query doesn't seem to happen often, and it doesn't really affect much on the forum.

When there's a lot of traffic, these tend to pop up in the slow_query log fairly often:

Code: 
# User@Host: USER[USER] @  [192.168.128.84]
# Thread_id: 361361  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 8.427393  Lock_time: 0.000338  Rows_sent: 1  Rows_examined: 7584375  Rows_affected: 0  Rows_read: 7584375
# Bytes_sent: 69
SET timestamp=1395450543;
SELECT COUNT(*)
        FROM dbtech_thanks_entry AS entry

        WHERE 1=1

 LIMIT 1;

Code: 
# Time: 140321 18:41:54
# User@Host: USER[USER] @  [192.168.128.88]
# Thread_id: 548461  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 9.493326  Lock_time: 0.000044  Rows_sent: 20  Rows_examined: 7583780  Rows_affected: 0  Rows_read: 7583780
# Bytes_sent: 281
SET timestamp=1395452514;
SELECT entryid
        FROM dbtech_thanks_entry AS entry
        WHERE 1=1

        ORDER BY entryid DESC
        LIMIT 7583760, 20;

Code: 
# User@Host: USER[USER] @  [192.168.128.114]
# Thread_id: 1713448  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 11.097185  Lock_time: 0.000070  Rows_sent: 1  Rows_examined: 7587670  Rows_affected: 0  Rows_read: 7587670
# Bytes_sent: 64
SET timestamp=1395465028;
SELECT COUNT(*)
        FROM dbtech_thanks_entry AS entry

        WHERE 1=1
        AND entry.varname = 'likes'AND entry.contentid = 102136979
 LIMIT 1;

Code: 
# Time: 140321 16:28:46
# User@Host: USER[USER] @  [192.168.128.86]
# Thread_id: 87737221  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 5.590998  Lock_time: 0.000031  Rows_sent: 20  Rows_examined: 250  Rows_affected: 0  Rows_read: 250
# Bytes_sent: 555
SET timestamp=1395444526;
SELECT entryid, contenttype
                        FROM dbtech_thanks_entry
                        WHERE userid = '81116'
                        ORDER BY entryid DESC
                        LIMIT 0, 20;


The vast majority of my slow query log are is filled up with variations of the above.


The other stuff is a persistent datastore table issue that I'm ready to murder someone or something over but that's not your problem :)
 
janklerks said:
dbtech_thanks_statistics:
Code: 
# User@Host: USER[USER] @  [192.168.128.88]
# Thread_id: 87737136  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 7.465718  Lock_time: 0.000338  Rows_sent: 20  Rows_examined: 158  Rows_affected: 0  Rows_read: 158
# Bytes_sent: 59445
SET timestamp=1395444520;
SELECT
                        post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS isdeleted,
                        user.*, userfield.*, usertextfield.*,

                        avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline,customavatar.width AS avwidth,customavatar.height AS avheight,


                        editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
                        editlog.reason AS edit_reason, editlog.hashistory,
                        postparsed.pagetext_html, postparsed.hasimages,
                        sigparsed.signatureparsed, sigparsed.hasimages AS sighasimages,
                        sigpic.userid AS sigpic, sigpic.dateline AS sigpicdateline, sigpic.width AS sigpicwidth, sigpic.height AS sigpicheight,
                        IF(user.displaygroupid=0, user.usergroupid, user.displaygroupid) AS displaygroupid, infractiongroupid

                        , dbtech_thanks_statistics.*, post.userid
                FROM post AS post
                LEFT JOIN user AS user ON(user.userid = post.userid)
                LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
                LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)

                LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid)


                LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
                LEFT JOIN postparsed AS postparsed ON(postparsed.postid = post.postid AND postparsed.styleid = 2 AND postparsed.languageid = 6)
                LEFT JOIN sigparsed AS sigparsed ON(sigparsed.userid = user.userid AND sigparsed.styleid = 2 AND sigparsed.languageid = 6)
                LEFT JOIN sigpic AS sigpic ON(sigpic.userid = post.userid)
                         LEFT JOIN dbtech_thanks_statistics AS dbtech_thanks_statistics ON(dbtech_thanks_statistics.userid = post.userid)
                WHERE post.postid IN (0,34928112,34928448,34928568,34928610,34928682,34928708,34928806,34929112,34929346,34930002,34930476,34930542,34934044,34957818,34957870,34960366,34962742,34964784,34964836,34965462)
                ORDER BY post.dateline;
Click to expand...
This query is a vB query, APTL simply hooks into it with a 1:1 table lookup that's already indexed.

janklerks said:
Code: 
# User@Host: USER[USER] @  [192.168.128.84]
# Thread_id: 361361  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 8.427393  Lock_time: 0.000338  Rows_sent: 1  Rows_examined: 7584375  Rows_affected: 0  Rows_read: 7584375
# Bytes_sent: 69
SET timestamp=1395450543;
SELECT COUNT(*)
        FROM dbtech_thanks_entry AS entry

        WHERE 1=1

 LIMIT 1;

Code: 
# Time: 140321 18:41:54
# User@Host: USER[USER] @  [192.168.128.88]
# Thread_id: 548461  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 9.493326  Lock_time: 0.000044  Rows_sent: 20  Rows_examined: 7583780  Rows_affected: 0  Rows_read: 7583780
# Bytes_sent: 281
SET timestamp=1395452514;
SELECT entryid
        FROM dbtech_thanks_entry AS entry
        WHERE 1=1

        ORDER BY entryid DESC
        LIMIT 7583760, 20;

Code: 
# User@Host: USER[USER] @  [192.168.128.114]
# Thread_id: 1713448  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 11.097185  Lock_time: 0.000070  Rows_sent: 1  Rows_examined: 7587670  Rows_affected: 0  Rows_read: 7587670
# Bytes_sent: 64
SET timestamp=1395465028;
SELECT COUNT(*)
        FROM dbtech_thanks_entry AS entry

        WHERE 1=1
        AND entry.varname = 'likes'AND entry.contentid = 102136979
 LIMIT 1;
Click to expand...
These come from a Pro-only feature, thanks.php?do=list. I will add an option to disable this page in the next version.

janklerks said:
Code: 
# Time: 140321 16:28:46
# User@Host: USER[USER] @  [192.168.128.86]
# Thread_id: 87737221  Schema: DBNAME  Last_errno: 0  Killed: 0
# Query_time: 5.590998  Lock_time: 0.000031  Rows_sent: 20  Rows_examined: 250  Rows_affected: 0  Rows_read: 250
# Bytes_sent: 555
SET timestamp=1395444526;
SELECT entryid, contenttype
                        FROM dbtech_thanks_entry
                        WHERE userid = '81116'
                        ORDER BY entryid DESC
                        LIMIT 0, 20;
Click to expand...
This comes from the Profile Block. I've added an option to disable the profile block integration in the next version.

I'm actually a bit surprised you haven't seen any others, there are a lot of queries that deal with fetching the users who clicked each button that I would have thought would show up here.

In either event, I have also replaced most of the queries to the "dbtech_thanks_entry" table with a hopefully more efficient cached version. In this new table, each piece of content (such as post ID 50, blog ID 20, etc) will have only one entry, and the clicks are stored in a serialised array.

I will need feedback from you after release, but hopefully it will help.


In summation, v3.2.5 will bring the following improvements:
* Ability to turn off profile block
* Ability to turn off Hottest Threads / Posts
* Ability to turn off Entry List
* Ability to disable APTL buttons in threads, leaving them enabled for other pieces of content
* Cached entries to alleviate SQL queries for listing "users who clicked"

The new version will be released around Midnight GMT this upcoming Monday.
 
Update: I've come up with an alternate solution that does not require turning off the profile block. Turning the profile block off would also prevent notifications from working, which is sub-optimal.

I will introduce a "recent entries" cache that stores entries from the last X days (admin configured, default to 30) and the profile block will only show these entries.

While this may cause disparities between the number of notifications and the actual profile view for users that left the forum for a long time, I feel that this is a worthwhile trade-off in order to preserve the entire notifications feature.

I would wager that the odds of someone wanting to browse through 7581 pages of button clicks via someone's profile is relatively close to nil.

If you would like to pre-emptively test what cut-off you should choose, you can run this query to determine how many entries would be kept in this cache:
Code: 
SELECT COUNT(*) FROM dbtech_thanks_entry WHERE dateline >= (UNIX_TIMESTAMP() - (86400 * X))
Replace X with the number of days you're testing. I would probably aim for 250k or lower results, depending on how many different members you have clicking the buttons.
 
Hi Fillip H.,

Great work, could I just ask will the new updated version feature all of the indexes that have been helping bigger forums?

Cheers
Mick
 
10 days is about 275k for us. :)

What are the chances something like that can be cached in memcached with vboptimise?



Thanks for all the hard work you're doing to help out the bigger forums!

Hey, one more question:

What are the chances of changing the mysql query to using mysql_slave? The more data I can pull from our slave servers, the happier I am.

ok, just a heads up, the "finalizing install", where I assume the cache is populated has been running for over an hour at this point. I didn't really see any warning in the upgrade guide.


Also, innotop is filled with these queries:
Code: 
SELECT contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 34979916, 34981890, 34990442, 34991240, 35023932, 35024810, 35025244, 35025498, 35033938, 35047084, 35068232, 35071370, 35072236, 35073486, 35129780, 35176726, 35338010, 35339414, 35346694, 35356156) AND contenttype = 'post'

which apparently doesn't have an index (yet?)

Code: 
____________ Sub-Part 1 _____________
Select Type: SIMPLE
      Table: dbtech_thanks_entrycache
 Partitions:
       Type: ALL
 Poss. Keys: content
      Index:
 Key Length:
  Index Ref:
  Row Count: 369756
    Special: Using where


It's at "Processing 715000" or so now.

I had to take the forum offline. those queries were filling up the allowed connections, and were taking an average of about 3-4 seconds to run each.


The install is still running, there's 7.7 million entries in dbtech_thanks_entry, and I assume this is going to take another couple hours.


Serves me right for doing this without testing first, I guess.

ugh, this is not good. after it all completed, queries on the dbtech_thanksentrycache table are taking over a minute each, it quickly fills up my allowed connections.

help.

running a single query with the forums shut off takes about 4 seconds

Code: 
 mysql>  contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330) AND contenttype = 'post';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 31375124, 3' at line 1
mysql> select contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330) AND contenttype = 'post';
Empty set (3.74 sec)

I had to disable the plugin, Even though I disabled every option, the database server started on fire within 90 seconds of opening the forum back up.

Definitely not faster than the last version :(
 
Last edited by a moderator:
How many rows are currently in your entrycache table at this time?

There is an index there, and on my MyISAM table the index is hit when running a query similar to the one you posted.
 
3,621,997 rows in entrycache.

The table was created as innodb. I converted it to MyISAM on a whim, but that didn't help. I tried throwing a few example queries (like the one above) at it, and they still aren't using the index, even if I tried to force it to use the index.

We're running Percona Server 5.5.35
 
Can you try running
Code: 
SELECT contentid, contenttype, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330);
and see if that's faster?

I can use PHP to limit the contenttype if adding that limitation to the query is what kills it. The index is being used for me on both MySQL 5.5.35 (dev) and MariaDB 5.5.36 (DBTech).
 
Nope, didn't seem to help at all.

HOWEVER!

All the numbers are integers!

I put quotes around all the integers like so:

Code: 
 explain select contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', '31375124', '31375490', '31384856', '31386816', '31429366', '31430728', '31442868', '31444234', '31787740', '32130814', '32134788', '32156708', '32157978', '32186044', '32188414', '32192572', '32192630', '34107490', '35665826', '35713330') AND contenttype = 'post';
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | dbtech_thanks_entrycache | range | content       | content | 31      | NULL |   21 | Using where |
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)


Now I just have to figure out why quoting uses the index over not quoting.


To google!



By the way, here's the comparison:

Code: 
mysql> select SQL_NO_CACHE contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', 31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330) AND contenttype = 'post';
Empty set (3.87 sec)

mysql> select SQL_NO_CACHE contentid, data FROM dbtech_thanks_entrycache WHERE contentid IN('', '31375124', '31375490', '31384856', '31386816', '31429366', '31430728', '31442868', '31444234', '31787740', '32130814', '32134788', '32156708', '32157978', '32186044', '32188414', '32192572', '32192630', '34107490', '35713330') AND contenttype = 'post';
Empty set (0.00 sec)
 
Last edited:
One last thing to try:
Code: 
SELECT contentid, contenttype, data FROM dbtech_thanks_entrycache WHERE contentid IN(31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330);

The first '' is error prevention, in case the array is empty. Maybe Percona doesn't like the mix of strings and integers, and that's causing the index to fail? If so, I will be able to work around it.
 
Yep, that ran just fine. 0.00 sec with SQL_NO_CACHE

Code: 
mysql> explain SELECT SQL_NO_CACHE contentid, contenttype, data FROM dbtech_thanks_entrycache WHERE contentid IN(31375124, 31375490, 31384856, 31386816, 31429366, 31430728, 31442868, 31444234, 31787740, 32130814, 32134788, 32156708, 32157978, 32186044, 32188414, 32192572, 32192630, 34107490, 35665826, 35713330);
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table                    | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | dbtech_thanks_entrycache | range | content       | content | 4       | NULL |   20 | Using where |
+----+-------------+--------------------------+-------+---------------+---------+---------+------+------+-------------+
 
This appears to be a vB3-only issue, I have applied a hotfix to /dbtech/thanks/hooks/showthread_query.php

Could you please re-download v3.2.5 and re-upload that file, then let me know if this works for you?
 
So how is performance for you now? I've been following this thread with interest as we have several boards your size or bigger that we'd like to convert.
 
Ok, apparently something went wrong with the initial build on entrycache. I thought it was a little weird that every row had the same BLOB size of 6 bytes, and all it contained was: a:0:{}

I didn't quite notice it at first because I was so focused on sorting out the queries. A few users alerted me and said "all the old posts are missing their likes!"


I'm in the process of rebuilding the cache again, and now the blob column appears to be populating correctly this time.




rudy: I'll let you know once this is sorted out. :)

In the profiles, the post thanks / like tab is now completely empty for everyone.
 
Last edited by a moderator:
By default, it will only show entries from the last 30 days. It will also not display any entries until the recententry table has been populated, which happens on the maintenance action.

Update: I have been alerted to an issue where if someone clicks a button, it erases the "recenentry" table contents. If you re-download the mod and re-upload the files, then re-run the button click cache maintenance action, it will work correctly onwards :)
 
Last edited:
ok, after a day of running, everything is looking good.


I haven't seen any queries related to to the addon in the slow query log in the last day, which is good news! :)
 
Status
Not open for further replies.

Similar threads

B
  • Locked
  • Support ticket
Question Migrate vBulletin Advanced Post Thanks / Like to Xenforo ?
Replies
4
Views
1K
DragonByte Technologies
DragonByte Technologies
G
  • Locked
  • Support ticket
Question Can I install thank button On Vb4 Php 7.3.30
Tags
thanks/likes
Replies
4
Views
2K
DragonByte Technologies
DragonByte Technologies
R
  • Locked
  • Support ticket
Question Migrating Likes to XF
Replies
5
Views
1K
DragonByte Technologies
DragonByte Technologies
neogeo
  • Locked
  • Support ticket
Bug [DBTech–Post Thanks] module missing on admincp
Replies
9
Views
2K
DragonByte Technologies
DragonByte Technologies
neogeo
  • Locked
  • Support ticket
Question Buton Likes with numbers
Replies
5
Views
985
DragonByte Technologies
DragonByte Technologies

Legacy Advanced Post Thanks / Like

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