InnoDB: Stopping purge / InnoDB: Resuming purge --> does it degrade performance?

[Mercury Forever]

 

 

Hello community, for about a few weeks, I have detected that my IPS platform randomly stays stuck for more than 5 minutes, many times having to restart the HTTPD (Apache) service.

I have identified that when this happens, this event is recorded in the MySQL log.

023-02-07T12:29:57.352911Z 328404 [Note] InnoDB: Stopping purge
2023-02-07T12:29:57.535725Z 328404 [Note] InnoDB: Resuming purge
2023-02-07T12:30:47.027999Z 329078 [Note] InnoDB: Stopping purge
2023-02-07T12:30:47.173236Z 329078 [Note] InnoDB: Resuming purge
2023-02-07T12:39:56.455512Z 335986 [Note] InnoDB: Stopping purge
2023-02-07T12:39:56.654359Z 335986 [Note] InnoDB: Resuming purge
2023-02-07T12:48:17.608407Z 342715 [Note] InnoDB: Stopping purge
2023-02-07T12:48:17.768217Z 342715 [Note] InnoDB: Resuming purge
2023-02-07T12:56:44.433301Z 346192 [Note] InnoDB: Stopping purge
2023-02-07T12:56:44.598197Z 346192 [Note] InnoDB: Resuming purge

 

What type of operation does IPS perform that triggers these tasks??? How can you identify which module performs this task??? As background, my server is physical and exclusive for IPS and MySQL.

 

Regards.

 

 

[Marc Stridgen]

This is an issue with your mysql instance. You would need to contact your hosting company to assist you with this

[Mercury Forever]

Dear friends, as I have mentioned in other threads, for some time I have been experiencing unusual behavior in the loading of the IPS pages, although it is not widespread, when the portal is presented it is completely stuck, affecting the loading of the site.

The only piece of information that I have identified during this problem is that this type of connection appears in the database.

  

 

From the research I have done, this type of behavior could be related to the connection method that IPS executes on the database and in the handling of the connections and the way in which it closes and/or reuses them.

I would appreciate if anyone has any guidance on this. This is the other issue that is related to identifying the behavior of my platform.

 

As additional information, the server is exclusive for IPS MySQL, 32 GB RAM, two SSD disks in RAID 1, one for the Operating System and the IPS application; the other is exclusive to the database.

Regards,

[Jim M]

I have merged this with your other topic for assistance with your MySQL server.

[Mercury Forever]

I think I have seen in some post configuration, put a time limit between post and post for users, I have searched for this option and I do not identify it.

In my search to identify what is affecting the performance of my page, I think you can go this way, since I have an audiovisual content forum that feeds on internet bots.

Regards,

[Mercury Forever]

Hi @Jim M, how can you optimize the queries that IPS executes on MySQL??? parsing the output of the PERL script mysqltuner.pl gives me this alert.

[!!] Joins performed without indexes: 14276

 

Other recommendations

  Reduce your SELECT DISTINCT queries which have no LIMIT clause

 

Regards,

 

Edited February 12, 2023 by Mercury Forever

[Jim M]

Wouldn’t worry too much about these unless you’re seeing a specific slow issue on your site really. We, of course, regularly review queries and update performance when reported in certain areas but bound, on need, to have queries meet these circumstances where they are joined with no index and/or use DISTINCT. These performance tools often assume you live in a vacuum which is unreasonable. 

[Mercury Forever]

As I've been mentioning, I've been experiencing system sluggishness for a while now, which unfortunately I haven't managed to root cause. These tools allow me to have a thread from which to improve. Having said that, how can the product improve these issues that have been detected?

In what way can I give feedback to identify and have an analysis on your part to gradually remove the findings.

Regards,

[Jim M]

You would need to provide slow query logs for proof of what is happening here. This tool is only speaking in hypotheticals, not actuals like the slow query log does. Please set the slow query log to 10 seconds and disable all third party applications/plugins for testing to get readings of the core software. 

[Mercury Forever]

 

Hi @Jim M, 

After monitoring for a long time, I find that these queries are generating

 

| 9072024 | xxxxxxxxxxx | localhost | yyyyyyyyyyyy | Execute |   10 | Sending data        | /*yyyyyyyyyyyy::xxxxxxxxxxx::IPS\Content\Search\_Results::count:754*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class=? OR index_class=? OR ( index_class IN('IPS\\cms\\Records3','IPS\\cms\\Records\\Comment3','IPS\\cms\\Records\\Review3') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( MATCH(index_content,index_title) AGAINST (? IN BOOLEAN MODE) ) OR ( index_item_index_id=index_id AND ( index_item_index_id IN(12040,16317,85841,112930,112940,188609,215596,231577,240615,257571,264639,268957,271071,271291,297993,344946,402803,402834,424707,492923,516170,528576,528600,529172,536725,551209,551239,565624,578000,586126,599343,599373,612473,621729,626867,630083,630122,640154,672629,676090,711487,712740,726640,726675,745167,813329,828588,905451,938259,954782,969825,981382,988424,990618,1001434,1001444,1016405,1027792,1027844,1088933,1102388,1102411,1106420,1108374,1108398,1115992,1124393,1126130,1132821,1132832,1134444,1134761,1136387,1143242,1148908,1149246,1157298,1161509,1161536,1161550,1171071,1174059,1175810,1189086,1203004,1203020,1211226,1212766,1248171,1254125,1254140,1265350,1265415,1265472,1269251,1269266,1269284,1273407,1274123,1274780,1274795,1274907,1274943,1274957,1281876,1290848,1295025,1296015,1296029,1301875,1302050,1305982,1311163,1318100,1318276,1319077,1319816,1333211,1333221,1333427,1335065,1337817,1338253,1346785,1348856,1348872,1348888,1352560,1353587,1358128,1361078,1361093,1361130,1361141,1361247,1362590,1362845,1362858,1362863,1366562,1366573,1367125,1374366,1375294,1375298,1375344,1377933,1377949,1379277,1379662,1379862,1382548,1382563,1382575,1384883,1385233,1385444,1386510,1389430,1389926,1390163,1390238,1392493,1392538,1392551,1392919,1392924,1394759,1395389,1395394,1395396,1397307,1398978,1400441,1402002,1403509,1403684,1404010,1404017,1404555,1404990,1406410,1407715,1407727,1408352,1410091,1410098,1410107,1410115,1411186,1411435,1411436,1412816,1413567,1413592,1414669,1415343,1415971,1416999,1417922,1418548,1419416,1419839,1419845,1420074,1420082,1420373,1420375,1420582,1420894,1421780,1421784,1422391,1423506,1423508,1423514,1424040,1425983,1426013,1426076,1426082,1427114,1428045,1428050,1428868,1428872,1428874,1428881,1428886,1431111,1431668,1435802,1435805,1438539,1441881,1442149,1442156,1443885,1443899,1444222,1445087,1448042,1456688,1469457,1470321,1472682,1486311,1486637,1493994,1501164,1504929,1507257,1524257,1581505,1619003,1717106,3160241,3167658,3179410,3183508) ) ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=? |

| 9072028 | xxxxxxxxxxx | localhost | yyyyyyyyyyyy | Execute |   10 | Sending data        | /*yyyyyyyyyyyy::xxxxxxxxxxx::IPS\Content\Search\_Results::count:754*/ SELECT COUNT(*) FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class=? OR index_class=? OR ( index_class IN('IPS\\cms\\Records3','IPS\\cms\\Records\\Comment3','IPS\\cms\\Records\\Review3') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( MATCH(index_content,index_title) AGAINST (? IN BOOLEAN MODE) ) OR ( index_item_index_id=index_id AND ( index_item_index_id IN(12040,16317,85841,112930,112940,188609,215596,231577,240615,257571,264639,268957,271071,271291,297993,344946,402803,402834,424707,492923,516170,528576,528600,529172,536725,551209,551239,565624,578000,586126,599343,599373,612473,621729,626867,630083,630122,640154,672629,676090,711487,712740,726640,726675,745167,813329,828588,905451,938259,954782,969825,981382,988424,990618,1001434,1001444,1016405,1027792,1027844,1088933,1102388,1102411,1106420,1108374,1108398,1115992,1124393,1126130,1132821,1132832,1134444,1134761,1136387,1143242,1148908,1149246,1157298,1161509,1161536,1161550,1171071,1174059,1175810,1189086,1203004,1203020,1211226,1212766,1248171,1254125,1254140,1265350,1265415,1265472,1269251,1269266,1269284,1273407,1274123,1274780,1274795,1274907,1274943,1274957,1281876,1290848,1295025,1296015,1296029,1301875,1302050,1305982,1311163,1318100,1318276,1319077,1319816,1333211,1333221,1333427,1335065,1337817,1338253,1346785,1348856,1348872,1348888,1352560,1353587,1358128,1361078,1361093,1361130,1361141,1361247,1362590,1362845,1362858,1362863,1366562,1366573,1367125,1374366,1375294,1375298,1375344,1377933,1377949,1379277,1379662,1379862,1382548,1382563,1382575,1384883,1385233,1385444,1386510,1389430,1389926,1390163,1390238,1392493,1392538,1392551,1392919,1392924,1394759,1395389,1395394,1395396,1397307,1398978,1400441,1402002,1403509,1403684,1404010,1404017,1404555,1404990,1406410,1407715,1407727,1408352,1410091,1410098,1410107,1410115,1411186,1411435,1411436,1412816,1413567,1413592,1414669,1415343,1415971,1416999,1417922,1418548,1419416,1419839,1419845,1420074,1420082,1420373,1420375,1420582,1420894,1421780,1421784,1422391,1423506,1423508,1423514,1424040,1425983,1426013,1426076,1426082,1427114,1428045,1428050,1428868,1428872,1428874,1428881,1428886,1431111,1431668,1435802,1435805,1438539,1441881,1442149,1442156,1443885,1443899,1444222,1445087,1448042,1456688,1469457,1470321,1472682,1486311,1486637,1493994,1501164,1504929,1507257,1524257,1581505,1619003,1717106,3160241,3167658,3179410,3183508) ) ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=? |

| 9072255 | xxxxxxxxxxx | localhost | yyyyyyyyyyyy | Execute |    8 | Creating sort index | /*yyyyyyyyyyyy::xxxxxxxxxxx::IPS\Content\Search\Mysql\_Query::search:746*/ SELECT main.*, ((MATCH(index_title) AGAINST ('' IN BOOLEAN MODE)*5)+(MATCH(index_content,index_title) AGAINST ('' IN BOOLEAN MODE)))/POWER(( ( UNIX_TIMESTAMP( NOW() ) - ( CASE WHEN index_date_updated <= UNIX_TIMESTAMP( NOW() ) THEN index_date_updated ELSE 0 END )) / 3600 ) + 2,1.5) AS calcscore FROM `ibf_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class=? OR index_class=? OR ( index_class IN('IPS\\cms\\Records3','IPS\\cms\\Records\\Comment3','IPS\\cms\\Records\\Review3') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) ) AND ( ( MATCH(index_content,index_title) AGAINST (? IN BOOLEAN MODE) ) OR ( index_item_index_id=index_id AND ( index_item_index_id IN(12040,16317,85841,112930,112940,188609,215596,231577,240615,257571,264639,268957,271071,271291,297993,344946,402803,402834,424707,492923,516170,528576,528600,529172,536725,551209,551239,565624,578000,586126,599343,599373,612473,621729,626867,630083,630122,640154,672629,676090,711487,712740,726640,726675,745167,813329,828588,905451,938259,954782,969825,981382,988424,990618,1001434,1001444,1016405,1027792,1027844,1088933,1102388,1102411,1106420,1108374,1108398,1115992,1124393,1126130,1132821,1132832,1134444,1134761,1136387,1143242,1148908,1149246,1157298,1161509,1161536,1161550,1171071,1174059,1175810,1189086,1203004,1203020,1211226,1212766,1248171,1254125,1254140,1265350,1265415,1265472,1269251,1269266,1269284,1273407,1274123,1274780,1274795,1274907,1274943,1274957,1281876,1290848,1295025,1296015,1296029,1301875,1302050,1305982,1311163,1318100,1318276,1319077,1319816,1333211,1333221,1333427,1335065,1337817,1338253,1346785,1348856,1348872,1348888,1352560,1353587,1358128,1361078,1361093,1361130,1361141,1361247,1362590,1362845,1362858,1362863,1366562,1366573,1367125,1374366,1375294,1375298,1375344,1377933,1377949,1379277,1379662,1379862,1382548,1382563,1382575,1384883,1385233,1385444,1386510,1389430,1389926,1390163,1390238,1392493,1392538,1392551,1392919,1392924,1394759,1395389,1395394,1395396,1397307,1398978,1400441,1402002,1403509,1403684,1404010,1404017,1404555,1404990,1406410,1407715,1407727,1408352,1410091,1410098,1410107,1410115,1411186,1411435,1411436,1412816,1413567,1413592,1414669,1415343,1415971,1416999,1417922,1418548,1419416,1419839,1419845,1420074,1420082,1420373,1420375,1420582,1420894,1421780,1421784,1422391,1423506,1423508,1423514,1424040,1425983,1426013,1426076,1426082,1427114,1428045,1428050,1428868,1428872,1428874,1428881,1428886,1431111,1431668,1435802,1435805,1438539,1441881,1442149,1442156,1443885,1443899,1444222,1445087,1448042,1456688,1469457,1470321,1472682,1486311,1486637,1493994,1501164,1504929,1507257,1524257,1581505,1619003,1717106,3160241,3167658,3179410,3183508) ) ) ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=? ORDER BY calcscore DESC LIMIT 0,25 |

| 9072390 | xxxxxxxxxxx | localhost | yyyyyyyyyyyy | Sleep   |    0 |                     | NULL   

 

[Makoto]

MySQL makes for a really bad search engine. It is not good at search performance and there is no way to make it such. You can make it less bad, but it's still bad.

Outside of ramping up your hardware, there's not much that can be done software side in regards to MySQL search performance.

IPS does, however, support using ElasticSearch as an alternative, which is substantially faster and more accurate in regards to searching. You'll need a good amount of RAM/server memory to run it on larger forums, but it's the next best step to take to address the performance issues you're seeing with the search table.

It's also worth noting even outside of search, these kind of queries can tie up available MySQL connections and other system resources, which can make your site slow outside of searching.