AlexJ Posted June 4 Posted June 4 Have few consistently slow performing queries but not sure from where it's generated. Any thoughts for making it faster would be helpful. Running MySQL 5.7.x with Redis. # Time: 2024-04-22T07:56:51.886625Z # User@Host: ips_user[ips_user] @ localhost [] Id: 32607188 # Query_time: 6.290145 Lock_time: 0.000050 Rows_sent: 1 Rows_examined: 1126912 SET timestamp=1713772611; /*test_table::ips_user::IPS\Content\Search\_Results::count:769*/ SELECT COUNT(*) FROM `ipb_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\cms\\Pages\\PageItem' OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR ( index_class IN('IPS\\cms\\Records2','IPS\\cms\\Records\\Comment2','IPS\\cms\\Records\\Review2') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\quizzes\\Quiz','IPS\\quizzes\\Quiz\\Comment','IPS\\quizzes\\Quiz\\Review') ) OR ( index_class IN('IPS\\communitymap\\Markers','IPS\\communitymap\\Markers\\Comment','IPS\\communitymap\\Markers\\Review') ) ) AND ( index_content LIKE '%{query}\'and(select+1)>0waitfor/**/delay\'0:0:0%' or index_title LIKE '%{query}\'and(select+1)>0waitfor/**/delay\'0:0:0%' ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0; # Time: 2024-04-22T07:56:59.448524Z # User@Host: ips_user[ips_user] @ localhost [] Id: 32608155 # Query_time: 6.962464 Lock_time: 0.000078 Rows_sent: 0 Rows_examined: 1126912 SET timestamp=1713772619; /*test_table::ips_user::IPS\Content\Search\Mysql\_Query::search:761*/ SELECT main.*, ((( index_title LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' ) AND MATCH(index_title) AGAINST ('' IN BOOLEAN MODE)*5)+(( index_content LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' or index_title LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' ) AND 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 `ipb_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\cms\\Pages\\PageItem' OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR ( index_class IN('IPS\\cms\\Records2','IPS\\cms\\Records\\Comment2','IPS\\cms\\Records\\Review2') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\quizzes\\Quiz','IPS\\quizzes\\Quiz\\Comment','IPS\\quizzes\\Quiz\\Review') ) OR ( index_class IN('IPS\\communitymap\\Markers','IPS\\communitymap\\Markers\\Comment','IPS\\communitymap\\Markers\\Review') ) ) AND ( index_content LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' or index_title LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0 ORDER BY calcscore DESC LIMIT 0,25; # Time: 2024-04-22T07:57:06.499082Z # User@Host: ips_user[ips_user] @ localhost [] Id: 32608155 # Query_time: 7.050252 Lock_time: 0.000051 Rows_sent: 1 Rows_examined: 1126912 SET timestamp=1713772626; /*test_table::ips_user::IPS\Content\Search\_Results::count:769*/ SELECT COUNT(*) FROM `ipb_core_search_index` AS `main` WHERE ( ( index_class IN('IPS\\core\\Statuses\\Status','IPS\\core\\Statuses\\Reply') ) OR index_class='IPS\\cms\\Pages\\PageItem' OR ( index_class IN('IPS\\cms\\Records1','IPS\\cms\\Records\\Comment1','IPS\\cms\\Records\\Review1') ) OR ( index_class IN('IPS\\cms\\Records2','IPS\\cms\\Records\\Comment2','IPS\\cms\\Records\\Review2') ) OR index_class='IPS\\forums\\Topic\\Post' OR ( index_class IN('IPS\\calendar\\Event','IPS\\calendar\\Event\\Comment','IPS\\calendar\\Event\\Review') ) OR ( index_class IN('IPS\\gallery\\Image','IPS\\gallery\\Image\\Comment','IPS\\gallery\\Image\\Review') ) OR ( index_class IN('IPS\\gallery\\Album\\Item','IPS\\gallery\\Album\\Comment','IPS\\gallery\\Album\\Review') ) OR ( index_class IN('IPS\\nexus\\Package\\Item','IPS\\nexus\\Package\\Review') ) OR ( index_class IN('IPS\\blog\\Entry','IPS\\blog\\Entry\\Comment') ) OR ( index_class IN('IPS\\downloads\\File','IPS\\downloads\\File\\Comment','IPS\\downloads\\File\\Review') ) OR ( index_class IN('IPS\\quizzes\\Quiz','IPS\\quizzes\\Quiz\\Comment','IPS\\quizzes\\Quiz\\Review') ) OR ( index_class IN('IPS\\communitymap\\Markers','IPS\\communitymap\\Markers\\Comment','IPS\\communitymap\\Markers\\Review') ) ) AND ( index_content LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' or index_title LIKE '%{query}\'/**/and/**/DBMS_PIPE.RECEIVE_MESSAGE(\'c\',0)=\'c%' ) AND ( index_permissions = '*' OR ( FIND_IN_SET(2,index_permissions) ) ) AND index_hidden=0; # Time: 2024-04-24T21:41:37.196599Z # User@Host: ips_user[ips_user] @ localhost [] Id: 34042420 # Query_time: 5.170450 Lock_time: 0.000017 Rows_sent: 500 Rows_examined: 1926224 SET timestamp=1713994897; /*test_table::ips_user::IPS\core\modules\front\discover\_popular::leaderboard:70*/ SELECT class_type_id_hash, total_rep FROM ( SELECT class_type_id_hash, SUM(rep_rating) as total_rep FROM `ipb_core_reputation_index` AS `x` WHERE rep_date BETWEEN 1253102970 AND 1713994892 AND rep_class='IPS\\forums\\Topic\\Post' GROUP BY `class_type_id_hash` ) AS `x` ORDER BY x.total_rep desc LIMIT 0,500; # Time: 2024-06-03T12:52:20.369823Z # User@Host: ips_user[ips_user] @ localhost [] Id: 57314593 # Query_time: 5.582759 Lock_time: 0.000205 Rows_sent: 25 Rows_examined: 56926 SET timestamp=1717419140; /*test_table::ips_user::IPS\Helpers\Table\_Content::getRows:504*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ipb_forums_topics` AS `forums_topics` LEFT JOIN `ipb_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id LEFT JOIN `ipb_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN `ipb_core_members` AS `author` ON author.member_id = forums_topics.starter_id LEFT JOIN `ipb_core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND forums_topics.is_future_entry=0 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(124,125,133,134,135,210) ORDER BY forums_topics.pinned DESC, forums_topics.last_post desc LIMIT 4125,25; # Time: 2024-06-03T13:24:03.947946Z # User@Host: ips_user[ips_user] @ localhost [] Id: 57385357 # Query_time: 5.179081 Lock_time: 0.000166 Rows_sent: 25 Rows_examined: 55576 SET timestamp=1717421043; /*test_table::ips_user::IPS\Helpers\Table\_Content::getRows:504*/ SELECT forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `ipb_forums_topics` AS `forums_topics` LEFT JOIN `ipb_forums_forums` AS `forums_forums` ON forums_topics.forum_id=forums_forums.id LEFT JOIN `ipb_core_tags_cache` AS `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN `ipb_core_members` AS `author` ON author.member_id = forums_topics.starter_id LEFT JOIN `ipb_core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_forums.can_view_others=1 AND forums_topics.approved=1 AND forums_topics.approved!=-2 AND forums_topics.approved!=-3 AND forums_topics.is_future_entry=0 AND ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND forums_topics.forum_id IN(124,125,133,134,135,210) ORDER BY forums_topics.pinned DESC, forums_topics.last_post desc LIMIT 2775,25;
Randy Calvert Posted June 4 Posted June 4 (edited) Misread post. There is a separate thread regarding mysql8…. It might be worth looking at it even though you’re not on 8. Edited June 4 by Randy Calvert
Jim M Posted June 4 Posted June 4 Would recommend checking out ACP -> Support to see if you have any database qualified issues there. If you're not running InnoDB, that is recommended. Additionally, upgrading to MySQL 8, if possible, will provide a little performance benefit. Finally, the issues there seem to be related to searching. MySQL search can take a little time. If your users are performing a lot of searches and expect fast results, ElasticSearch may be something you wish to evaluate.
AlexJ Posted June 4 Author Posted June 4 @Randy Calvert Using 5.7 but good note for MySQL 8.x. @Jim M -> No issues their. Using InnoDB since a while. MySQL 8.x or MariaDB - thinking which route to go.. but as per Google, i see don't see ground breaking benefits vs folks are reporting higher memory/CPU usage. So need to give that a try. My general question is: This queries are coming from which URL, app, etc. ? Those are like full table scan queries. Can we do something about it? I don't want to switch to elasticsearch because something is not working as expected OR we can enhance underlying app to provide better performing queries. For ElasticSearch I had read this post and kinda avoided it. Is this issue resolved OR requires additional configuration? https://invisioncommunity.com/forums/topic/473721-gracefully-handle-the-elasticsearch-server-being-unavailable/#comment-2941053
Jim M Posted June 4 Posted June 4 45 minutes ago, AlexJ said: My general question is: This queries are coming from which URL, app, etc. ? Those are like full table scan queries. Can we do something about it? I don't want to switch to elasticsearch because something is not working as expected OR we can enhance underlying app to provide better performing queries. As mentioned, these are coming from Searching. The queries are actually pretty simple COUNT queries. On a proper system, it should run quickly and efficiently in that manner. However, if you are doing heavy searches, there is nothing we can do to make MySQL Full Text Search more efficient. This is an internal functionality of MySQL. 45 minutes ago, AlexJ said: For ElasticSearch I had read this post and kinda avoided it. Is this issue resolved OR requires additional configuration? https://invisioncommunity.com/forums/topic/473721-gracefully-handle-the-elasticsearch-server-being-unavailable/#comment-2941053 Not quite sure I understand your concern there as respectively, if your MySQL server is down for these items, your community would be down as well 🙂 .
AlexJ Posted June 6 Author Posted June 6 On 6/4/2024 at 4:11 PM, Jim M said: As mentioned, these are coming from Searching. That's where I am trying understand further. Is the IPS regular new content search? OR User search OR someone doing the search through ACP? Reason for ask is if you notice it says front discover popular. So it searching for popular content across full data? # Time: 2024-04-24T21:41:37.196599Z # User@Host: ips_user[ips_user] @ localhost [] Id: 34042420 # Query_time: 5.170450 Lock_time: 0.000017 Rows_sent: 500 Rows_examined: 1926224 SET timestamp=1713994897; /*test_table::ips_user::IPS\core\modules\front\discover\_popular::leaderboard:70*/ SELECT class_type_id_hash, total_rep FROM ( SELECT class_type_id_hash, SUM(rep_rating) as total_rep FROM `ipb_core_reputation_index` AS `x` WHERE rep_date BETWEEN 1253102970 AND 1713994892 AND rep_class='IPS\\forums\\Topic\\Post' GROUP BY `class_type_id_hash` ) AS `x` ORDER BY x.total_rep desc LIMIT 0,500;
teraßyte Posted June 6 Posted June 6 It's the query that loads the popular content for the leaderboard page. Example here => https://invisioncommunity.com/leaderboard/ AlexJ 1
AlexJ Posted June 6 Author Posted June 6 Thanks @teraßyte - So it's not all user search. In cases like this i.e. no user search looking for suggestions to improve performance. Database is mostly tunned except write log efficiency.. So suggestions are welcome. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB Buffer Pool size ( 9.0G ) under limit for 64 bits architecture: (17179869184.0G ) [OK] InnoDB buffer pool / data size: 9.0G / 5.9G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/9.0G should be equal to 25% [OK] InnoDB buffer pool instances: 9 [--] Number of InnoDB Buffer Pool Chunk: 72 for 9 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 100.00% (304683506927 hits / 304683832411 total) [!!] InnoDB Write Log efficiency: 74.5% (80190978 hits / 107639276 total) [OK] InnoDB log waits: 0.00% (0 waits / 27448298 writes) ------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 154d 5h 1m 32s (2B q [201.446 qps], 59M conn, TX: 29081G, RX: 1065G) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 62.6G [--] Max MySQL memory : 14.7G [--] Other process memory: 0B [--] Total buffers: 13.4G global + 7.3M per thread (150 max threads) [--] Performance_schema Max memory usage: 234M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 14.7G (23.55% of installed RAM) [OK] Maximum possible memory usage: 14.7G (23.54% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (198/2B) [!!] Highest connection usage: 100% (151/150) [OK] Aborted connections: 0.03% (19352/59617988) [!!] Query cache efficiency: 0.1% (2M cached / 2B selects) [!!] Query cache may be disabled by default due to mutex contention. [OK] Sorts requiring temporary tables: 0% (2M temp sorts / 447M sorts) [!!] Joins performed without indexes: 35426337 [OK] Temporary tables created on disk: 22% (20M on disk / 93M total) [OK] Thread cache hit rate: 99% (5K created / 59M connections) [OK] Table cache hit rate: 99% (6B hits / 6B requests) [OK] table_definition_cache (10000) is greater than number of tables (1755) [OK] Open file limit used: 5% (1K/20K) [OK] Table locks acquired immediately: 99% (290M immediate / 290M locks)
Recommended Posts