Jump to content

Help needed to analyse the MySQL slow queries


Recommended Posts

Help needed in understanding what is causing these slow queries?

Over 3K entries in 3-4 hours. Below is a small sample of last couple of pages.

Also its logging queries without index.

# User@Host: dummyname @ localhost []  Id:  3311
# Query_time: 0.000907  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763412;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=10282;
# Time: 2020-04-01T17:50:13.927016Z
# User@Host: dummyname @ localhost []  Id:  3312
# Query_time: 0.021526  Lock_time: 0.000124 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763413;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:15.833196Z
# User@Host: dummyname @ localhost []  Id:  3313
# Query_time: 0.021612  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763415;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:16.345437Z
# User@Host: dummyname @ localhost []  Id:  3314
# Query_time: 0.021635  Lock_time: 0.000131 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763416;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:17.644784Z
# User@Host: dummyname @ localhost []  Id:  3316
# Query_time: 0.021781  Lock_time: 0.000117 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763417;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:19.440366Z
# Time: 2020-04-01T17:49:59.364806Z
# User@Host: dummyname @ localhost []  Id:  3296
# Query_time: 0.000894  Lock_time: 0.000017 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763399;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=27208;
# Time: 2020-04-01T17:50:00.221319Z
# User@Host: dummyname @ localhost []  Id:  3297
# Query_time: 0.021762  Lock_time: 0.000126 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763400;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:00.421537Z
# User@Host: dummyname @ localhost []  Id:  3298
# Query_time: 0.021629  Lock_time: 0.000126 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763400;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:02.321192Z
# User@Host: dummyname @ localhost []  Id:  3300
# Query_time: 0.021604  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763402;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:03.609614Z
# User@Host: dummyname @ localhost []  Id:  3301
# Query_time: 0.021593  Lock_time: 0.000127 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763403;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:46.147596Z
# User@Host: dummyname @ localhost []  Id:  3285
# Query_time: 0.023963  Lock_time: 0.000120 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763386;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:46.209695Z
# User@Host: dummyname @ localhost []  Id:  3286
# Query_time: 0.022603  Lock_time: 0.000127 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763386;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:48.935601Z
# User@Host: dummyname @ localhost []  Id:  3288
# Query_time: 0.021588  Lock_time: 0.000123 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763388;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:52.228132Z
# User@Host: dummyname @ localhost []  Id:  3289
# Query_time: 0.022334  Lock_time: 0.000123 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763392;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:52.480006Z
SET timestamp=1585763368;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:30.832373Z
# User@Host: dummyname @ localhost []  Id:  3269
# Query_time: 0.021494  Lock_time: 0.000130 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763370;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:33.366652Z
# User@Host: dummyname @ localhost []  Id:  3270
# Query_time: 0.021650  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763373;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:34.140295Z
# User@Host: dummyname @ localhost []  Id:  3271
# Query_time: 0.021545  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763374;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:35.683698Z
# User@Host: dummyname @ localhost []  Id:  3274
# Query_time: 0.021548  Lock_time: 0.000119 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763375;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:37.461513Z
# User@Host: dummyname @ localhost []  Id:  3276
# Query_time: 0.000084  Lock_time: 0.000022 Rows_sent: 0  Rows_examined: 26
SET timestamp=1585763377;
/*IPS\Http\Url\cms_hook_FriendlyUrl::createFriendlyUrlFromComponents:55*/ SELECT store_current_id FROM `cms_url_store` WHERE store_type='page' and store_path='fbclid=IwAR16ELYSJqeQ0TjcQoAaPOQFNEZDw6C2zfGHKAtHwPCToBE5SILpAsvUtWI';
# Time: 2020-04-01T17:49:37.753534Z
# User@Host: dummyname @ localhost []  Id:  3277
# Query_time: 0.022480  Lock_time: 0.000120 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763377;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:37.862450Z
# User@Host: dummyname @ localhost []  Id:  3276
# Query_time: 0.027804  Lock_time: 0.000059 Rows_sent: 6  Rows_examined: 18532
SET timestamp=1585763377;
/*IPS\Theme\Cache\class_membersshop_front_widgets::mostpoints:61*/ SELECT * FROM `core_members` ORDER BY core_members.shop_points DESC LIMIT 6;
# Time: 2020-04-01T17:49:39.895140Z
# User@Host: dummyname @ localhost []  Id:  3278
# Query_time: 0.021495  Lock_time: 0.000126 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763379;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:40.361018Z
# User@Host: dummyname @ localhost []  Id:  3279
# Query_time: 0.021502  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763380;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:42.192213Z
# User@Host: dummyname @ localhost []  Id:  3282
# Query_time: 0.023179  Lock_time: 0.000111 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763382;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:44.252843Z
# User@Host: dummyname @ localhost []  Id:  3283
# Query_time: 0.021532  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763384;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:46.147596Z

# User@Host: dummyname @ localhost []  Id:  3289
# Query_time: 0.000355  Lock_time: 0.000021 Rows_sent: 10  Rows_examined: 211
SET timestamp=1585763392;
/*IPS\featuredcontent\_Slider::fromCustom:638*/ SELECT * FROM `featuredcontent_contents` WHERE fcc_slider=70 ORDER BY RAND() LIMIT 0,20;
# Time: 2020-04-01T17:49:52.492625Z
# User@Host: dummyname @ localhost []  Id:  3290
# Query_time: 0.022380  Lock_time: 0.000113 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763392;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:53.252109Z
# User@Host: dummyname @ localhost []  Id:  3291
# Query_time: 0.000351  Lock_time: 0.000024 Rows_sent: 9  Rows_examined: 209
SET timestamp=1585763393;
/*IPS\featuredcontent\_Slider::fromCustom:638*/ SELECT * FROM `featuredcontent_contents` WHERE fcc_slider=73 ORDER BY RAND() LIMIT 0,20;
# Time: 2020-04-01T17:49:53.270722Z
# User@Host: dummyname @ localhost []  Id:  3292
# Query_time: 0.023296  Lock_time: 0.000170 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763393;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:53.282073Z
# User@Host: dummyname @ localhost []  Id:  3291
# Query_time: 0.000354  Lock_time: 0.000017 Rows_sent: 10  Rows_examined: 211
SET timestamp=1585763393;
/*IPS\featuredcontent\_Slider::fromCustom:638*/ SELECT * FROM `featuredcontent_contents` WHERE fcc_slider=70 ORDER BY RAND() LIMIT 0,20;
# Time: 2020-04-01T17:49:53.582308Z
# User@Host: dummyname @ localhost []  Id:  3292
# Query_time: 0.000357  Lock_time: 0.000014 Rows_sent: 10  Rows_examined: 211
SET timestamp=1585763393;
/*IPS\featuredcontent\_Slider::fromCustom:638*/ SELECT * FROM `featuredcontent_contents` WHERE fcc_slider=71 ORDER BY RAND() LIMIT 0,20;
# Time: 2020-04-01T17:49:56.625556Z
# User@Host: dummyname @ localhost []  Id:  3293
# Query_time: 0.021547  Lock_time: 0.000124 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763396;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:49:59.364806Z
# User@Host: dummyname @ localhost []  Id:  3296

# Time: 2020-04-01T17:50:06.076380Z
# User@Host: dummyname @ localhost []  Id:  3303
# Query_time: 0.023793  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763406;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:06.451228Z
# User@Host: dummyname @ localhost []  Id:  3304
# Query_time: 0.025381  Lock_time: 0.000131 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763406;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:08.997641Z
# User@Host: dummyname @ localhost []  Id:  3305
# Query_time: 0.021670  Lock_time: 0.000126 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763408;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:10.637792Z
# User@Host: dummyname @ localhost []  Id:  3308
# Query_time: 0.021607  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763410;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:11.484728Z
# User@Host: dummyname @ localhost []  Id:  3309
# Query_time: 0.023702  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763411;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:11.732868Z
# User@Host: dummyname @ localhost []  Id:  3310
# Query_time: 0.022362  Lock_time: 0.000122 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763411;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:12.331545Z
# User@Host: dummyname @ localhost []  Id:  3311
# Query_time: 0.000907  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 17231

# User@Host: dummyname @ localhost []  Id:  3318
# Query_time: 0.022499  Lock_time: 0.000119 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763419;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:21.397308Z
# User@Host: dummyname @ localhost []  Id:  3319
# Query_time: 0.022529  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763421;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:22.104804Z
# User@Host: dummyname @ localhost []  Id:  3321
# Query_time: 0.021491  Lock_time: 0.000124 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763422;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:23.431014Z
# User@Host: dummyname @ localhost []  Id:  3322
# Query_time: 0.021680  Lock_time: 0.000127 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763423;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:25.297973Z
# User@Host: dummyname @ localhost []  Id:  3323
# Query_time: 0.021648  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763425;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:25.596070Z
# User@Host: dummyname @ localhost []  Id:  3324
# Query_time: 0.000823  Lock_time: 0.000027 Rows_sent: 1  Rows_examined: 9
SET timestamp=1585763425;
/*IPS\Content\Search\_Results::init:246*/ SELECT `app`, `type`, `type_id`, `member_id`, `rep_rating`, `reaction` FROM `core_reputation_index`  LEFT JOIN `core_reactions` ON reaction=reaction_id WHERE ( reaction_enabled=1 AND app='forums$
# Time: 2020-04-01T17:50:25.709388Z
# User@Host: dummyname @ localhost []  Id:  3324
# Query_time: 0.000912  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763425;

# Query_time: 0.000912  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763425;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=10659;
# Time: 2020-04-01T17:50:27.078886Z
# User@Host: dummyname @ localhost []  Id:  3327
# Query_time: 0.021628  Lock_time: 0.000123 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763427;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:27.130422Z
# User@Host: dummyname @ localhost []  Id:  3326
# Query_time: 0.001008  Lock_time: 0.000024 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763427;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=10265;
# Time: 2020-04-01T17:50:27.209523Z
# User@Host: dummyname @ localhost []  Id:  3328
# Query_time: 0.029687  Lock_time: 0.000123 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763427;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:27.609941Z
# User@Host: dummyname @ localhost []  Id:  3330
# Query_time: 0.000903  Lock_time: 0.000019 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763427;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=10170;
# Time: 2020-04-01T17:50:28.936390Z
# User@Host: dummyname @ localhost []  Id:  3332
# Query_time: 0.022450  Lock_time: 0.000120 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763428;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:31.601200Z
# User@Host: dummyname @ localhost []  Id:  3334
# Query_time: 0.000915  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763431;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=12023;
# Time: 2020-04-01T17:50:32.098079Z
# User@Host: dummyname @ localhost []  Id:  3335
# Query_time: 0.021491  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763432;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:32.803349Z
# User@Host: dummyname @ localhost []  Id:  3336
# Query_time: 0.022528  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763432;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:34.813465Z
# User@Host: dummyname @ localhost []  Id:  3337
# Query_time: 0.023262  Lock_time: 0.000120 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763434;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:36.890484Z
# User@Host: dummyname @ localhost []  Id:  3339
# Query_time: 0.000928  Lock_time: 0.000015 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763436;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=26004;
# Time: 2020-04-01T17:50:37.446807Z
# User@Host: dummyname @ localhost []  Id:  3340
# Query_time: 0.021553  Lock_time: 0.000118 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763437;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
SET timestamp=1585763437;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:37.643541Z
# User@Host: dummyname @ localhost []  Id:  3341
# Query_time: 0.021540  Lock_time: 0.000114 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763437;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:39.045187Z
# User@Host: dummyname @ localhost []  Id:  3342
# Query_time: 0.000928  Lock_time: 0.000019 Rows_sent: 1  Rows_examined: 17231
SET timestamp=1585763439;
/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=18121;
# Time: 2020-04-01T17:50:39.968212Z
# User@Host: dummyname @ localhost []  Id:  3344
# Query_time: 0.021478  Lock_time: 0.000124 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763439;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:42.087237Z
# User@Host: dummyname @ localhost []  Id:  3346
# Query_time: 0.021599  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763442;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$
# Time: 2020-04-01T17:50:43.147057Z
# User@Host: dummyname @ localhost []  Id:  3348
# Query_time: 0.021645  Lock_time: 0.000125 Rows_sent: 394  Rows_examined: 1579
SET timestamp=1585763443;
/*IPS\Node\_Model::loadIntoMemory:749*/ SELECT * FROM `forums_forums`  LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_for$

 

Link to comment
Share on other sites

Edit: 2K in 1 hour

-------- Performance Metrics -------------------------------------------------
[--] Up for: 59m 4s (406K q [114.568 qps], 4K conn, TX: 3B, RX: 337M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 7.3G global + 4.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 9.5G (30% of installed RAM)
[OK] Slow queries: 0% (2K/406K)
[OK] Highest usage of available connections: 6% (34/500)
[OK] Key buffer size / total MyISAM indexes: 4.0G/204.0M
[OK] Key buffer hit rate: 99.9% (11M cached / 13K reads)
[OK] Query cache efficiency: 87.2% (334K cached / 383K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (56 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 34
[OK] Temporary tables created on disk: 3% (356 on disk / 11K total)
[OK] Thread cache hit rate: 99% (34 created / 4K connections)
[OK] Table cache hit rate: 72% (11K open / 15K opened)
[OK] Open file limit used: 19% (6K/32K)
[OK] Table locks acquired immediately: 99% (65K immediate / 65K locks)
[OK] InnoDB buffer pool / data size: 1.0G/34.9M
[OK] InnoDB log waits: 0
 

Link to comment
Share on other sites

44K slow queries in 12 hours

-------- Performance Metrics -------------------------------------------------
[--] Up for: 12h 34m 35s (6M q [134.405 qps], 49K conn, TX: 51B, RX: 5B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 7.3G global + 4.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 9.5G (30% of installed RAM)
[OK] Slow queries: 0% (44K/6M)
[OK] Highest usage of available connections: 9% (45/500)
[OK] Key buffer size / total MyISAM indexes: 4.0G/204.4M
[OK] Key buffer hit rate: 100.0% (143M cached / 29K reads)
[OK] Query cache efficiency: 90.7% (5M cached / 5M selects)
[!!] Query cache prunes per day: 406082
[OK] Sorts requiring temporary tables: 0% (435 temp sorts / 44K sorts)
[!!] Joins performed without indexes: 391
[OK] Temporary tables created on disk: 4% (6K on disk / 133K total)
[OK] Thread cache hit rate: 99% (45 created / 49K connections)
[OK] Table cache hit rate: 61% (9K open / 14K opened)
[OK] Open file limit used: 18% (5K/32K)
[OK] Table locks acquired immediately: 99% (733K immediate / 734K locks)
[OK] InnoDB buffer pool / data size: 1.0G/34.9M
[OK] InnoDB log waits: 0
 

Link to comment
Share on other sites

So the first thing I want to say is that it is normal to have some queries not using indexes. For instance, we load all forums up into memory, and when you select all rows from a table no particular index is used (since there is no filtering occurring). You can't solve all of those types of queries.

/*IPS\Patterns\hook543::load:17*/ SELECT * FROM `membersshop_points` WHERE `member_id`=10282;

You have a member shop application or plugin installed which needs an index on membershop_points.member_id column.

/*IPS\Http\Url\cms_hook_FriendlyUrl::createFriendlyUrlFromComponents:55*/ SELECT store_current_id FROM `cms_url_store` WHERE store_type='page' and store_path='fbclid=IwAR16ELYSJqeQ0TjcQoAaPOQFNEZDw6C2zfGHKAtHwPCToBE5SILpAsvUtWI';

This has already been addressed for 4.5, though it's unlikely to be slow for most communities regardless.

/*IPS\Theme\Cache\class_membersshop_front_widgets::mostpoints:61*/ SELECT * FROM `core_members` ORDER BY core_members.shop_points DESC LIMIT 6;

Again, your member shop application. You may get some performance by adding an index on core_members.shop_points and/or the query may need to be adjusted.

/*IPS\featuredcontent\_Slider::fromCustom:638*/ SELECT * FROM `featuredcontent_contents` WHERE fcc_slider=70 ORDER BY RAND() LIMIT 0,20;

Order by RAND() is always going to be horrible. This should be changed by the plugin author. We have a pseudo-random approach when we order "randomly".

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...