Gauravk Posted April 1, 2020 Share Posted April 1, 2020 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 More sharing options...
Gauravk Posted April 1, 2020 Author Share Posted April 1, 2020 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 More sharing options...
Gauravk Posted April 2, 2020 Author Share Posted April 2, 2020 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 More sharing options...
bfarber Posted April 2, 2020 Share Posted April 2, 2020 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". ASTRAPI 1 Link to comment Share on other sites More sharing options...
Recommended Posts