JohnWH Posted April 3 Posted April 3 Hi, Let me preface by saying I realize this is outside the scope of normal support (hence posting in this forum) and any pointers/suggestions would be in "at your own risk" territory. Am also aware that any file and/or DB suggested changes can cause issues and a more careful/time consuming approach in terms of future upgrades, but that is more than acceptable if it means a faster/better experience for our users. To make a long story short (as I'm sure overall this post will be anything but) we're having performance related issues that mostly stem from having 15k+ forums and was hoping someone would have any pointers, specifically when it comes to improving query execution time and PHP processing time. The vast majority of the forums are subforums under multiple sections of three categories to help organize and also reduce DOM size/prevent them being listed on the forum index, like so: Quote Category - Category - A - *All forums starting with A - Not visible on forum index* - B - *All forums starting with B - Not visible on forum index* ... Other Category - Other Category - A - *All forums starting with A - Not visible on forum index* - B - *All forums starting with B - Not visible on forum index* ... The forums run across three dedicated servers: - Webserver (nginx + php8.1-fpm) - Elasticsearch + Redis (enabled on all forum settings supporting them) - Database server (MariaDB 10.6) As for the forum software, other than a custom theme having a handful of template changes in terms of HTML/JS/CSS, the only modification is one custom plugin having an insignificant server load cost. What seems to be struggling the most is the DB. When looking at the process list there is one query that is slow enough to consistently show up. Am planning to enabling the slow query log with all filters and full verbosity to get a better overview of the rest. MariaDB [(none)]> SHOW FULL PROCESSLIST; +-----------+------------+-----------------+------------+---------+------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-----------+------------+-----------------+------------+---------+------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ | 932525665 | root | localhost | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST | 0.000 | | 0.000 | | 932527117 | site_forum | WEBSERVER:44212 | site_forum | Execute | 0 | Creating sort index | /*site_forum::site_forum::IPS\Node\_Model::loadIntoMemory:755*/ SELECT * FROM `forums_forums` LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=?) AND (forums_forums.id IN(SELECT perm_type_id FROM `core_permission_index` WHERE core_permission_index.app=? AND core_permission_index.perm_type=? AND ( ( ( FIND_IN_SET(2,perm_view) ) ) OR perm_view=? )) ) ORDER BY position | 0.000 | | 932527118 | site_forum | WEBSERVER:44216 | site_forum | Execute | 0 | Writing to net | /*site_forum::site_forum::IPS\Node\_Model::loadIntoMemory:755*/ SELECT * FROM `forums_forums` LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=?) AND (forums_forums.id IN(SELECT perm_type_id FROM `core_permission_index` WHERE core_permission_index.app=? AND core_permission_index.perm_type=? AND ( ( ( FIND_IN_SET(2,perm_view) ) ) OR perm_view=? )) ) ORDER BY position | 0.000 | | 932527126 | site_forum | WEBSERVER:44244 | site_forum | Execute | 0 | Sending data | /*site_forum::site_forum::IPS\Node\_Model::loadIntoMemory:755*/ SELECT * FROM `forums_forums` LEFT JOIN `core_members` ON core_members.member_id=forums_forums.last_poster_id WHERE (sub_can_post=0 OR min_posts_view<=?) AND (forums_forums.id IN(SELECT perm_type_id FROM `core_permission_index` WHERE core_permission_index.app=? AND core_permission_index.perm_type=? AND ( ( ( FIND_IN_SET(2,perm_view) ) ) OR perm_view=? )) ) ORDER BY position | 0.000 | | 0.000 | ... ... +-----------+------------+-----------------+------------+---------+------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+ Current MySQLTuner Info (once initial changes have been done the server will be restarted to give some more sane 24-48 hour values to go by) -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 18.7G (Tables: 502) [--] Data in MyISAM tables: 12.3G (Tables: 175) [--] Data in Aria tables: 32.0K (Tables: 1) [--] Data in MEMORY tables: 0B (Tables: 41) [!!] Total fragmented tables: 1 -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 994d 15h 38m 2s (13B q [161.924 qps], 932M conn, TX: 2467026G, RX: 5699G) [--] Reads / Writes: 94% / 6% [--] Binary logging is disabled [--] Physical Memory : 188.5G [--] Max MySQL memory : 106.2G [--] Other process memory: 0B [--] Total buffers: 102.3G global + 3.7M per thread (1000 max threads) [--] Performance_schema Max memory usage: 317M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 105.5G (55.94% of installed RAM) [OK] Maximum possible memory usage: 106.2G (56.32% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (252K/13B) [OK] Highest usage of available connections: 80% (802/1000) [OK] Aborted connections: 0.00% (2/932450231) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 1% (26M temp sorts / 1B sorts) [!!] Joins performed without indexes: 17201886 [!!] Temporary tables created on disk: 56% (97M on disk / 172M total) [OK] Thread cache hit rate: 99% (23K created / 932M connections) [OK] Table cache hit rate: 99% (30B hits / 30B requests) [!!] table_definition_cache (400) is less than number of tables (1010) [OK] Open file limit used: 1% (417/32K) [OK] Table locks acquired immediately: 100% (26K immediate / 26K locks) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Consider migrating 175 following tables to InnoDB: * Every table from old forum software database listed* [--] General MyIsam metrics: [--] +-- Total MyISAM Tables : 175 [--] +-- Total MyISAM indexes : 2.0G [--] +-- KB Size :2.0G [--] +-- KB Used Size :373.5M [--] +-- KB used :18.2% [--] +-- Read KB hit rate: 100.0% (21M cached / 103 reads) [--] +-- Write KB hit rate: 0% (0 cached / 0 writes) [!!] Key buffer used: 18.2% (373.5M used / 2.0G cache) [OK] Key buffer size / total MyISAM indexes: 2.0G/2.0G [OK] Read Key buffer hit rate: 100.0% (21M cached / 103 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB File per table is activated [OK] InnoDB Buffer Pool size ( 100.0G ) under limit for 64 bits architecture: (17179869184.0G ) [OK] InnoDB buffer pool / data size: 100.0G / 18.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (5%): 5.0G * 1 / 100.0G should be equal to 25% [--] Number of InnoDB Buffer Pool Chunk: 800 for 1 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% (22898371613830 hits / 22898372618659 total) [!!] InnoDB Write Log efficiency: 1063.06% (584048175 hits / 54940140 total) [OK] InnoDB log waits: 0.00% (0 waits / 638988315 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K [OK] Aria pagecache hit rate: 98.1% (2364B cached / 44B reads) -------- Recommendations --------------------------------------------------------------------------- General recommendations: Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance ALTER TABLE `site_forum`.`core_item_member_map` FORCE; -- can free 85 MiB Total freed space after defragmentation: 85 MiB We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause MyISAM engine is deprecated, consider migrating to InnoDB Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time Variables to adjust: join_buffer_size (> 1.0M, or always use indexes with JOINs) tmp_table_size (> 128M) max_heap_table_size (> 128M) table_definition_cache (400) > 1010 or -1 (autosizing if supported) key_buffer_size (~ 390M) innodb_log_file_size should be (=25G) if possible, so InnoDB total log file size equals 25% of buffer pool size. innodb_log_buffer_size (> 16M) join_buffer_size and tmp_table_size/max_heap_table_size related MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'handler_read%'; +--------------------------+---------------+ | Variable_name | Value | +--------------------------+---------------+ | handler_read_first | 16490691 | | handler_read_key | 2627454660802 | | handler_read_last | 98091160 | | handler_read_next | 3564353232230 | | handler_read_prev | 768992686183 | | handler_read_retry | 0 | | handler_read_rnd | 1204642252658 | | handler_read_rnd_deleted | 12711263 | | handler_read_rnd_next | 5959472221577 | +--------------------------+---------------+ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'created_tmp%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | created_tmp_disk_tables | 97928307 | | created_tmp_tables | 173180753 | +-------------------------+-----------+ Current MariaDB config [mysqld] bind-address=LAN-IP skip-name-resolve=1 max_connections=1000 performance_schema=ON innodb_buffer_pool_size=100G innodb_log_file_size=5G query_cache_size=0 query_cache_type=0 tmp_table_size=128M max_heap_table_size=128M key_buffer_size=2G join_buffer_size=1M Questions: All MyISAM tables are from an old forum database that we ran through the converter when moving to Invision Community. Is it safe to assume that all the necessary converter/redirect data is now stored in the convert_* tables and the old database can be dropped entirely? For the SQL config, thoughts on the following: Increase table_definition_cache to 1000 (if the answer from the first question is yes, there should be more than enough leeway even though the current recommendation is 1010) Remove key_buffer_size from config as MyISAM is not used anymore Increase tmp_table_size/max_heap_table_size equally in i.e 32MB increments and monitor created_tmp_disk_tables to try to create more tables in memory and write less tmp to disk Increasing join_buffer_size. If the query mentioned in processlist is one of the main culprits, looking at core_permission_index being around 4 MB and forums_forums around 6 MB, could one not set this to over 10MB, i.e 16MB whilst trying to create more indexes where necessary (see next question). As an general note, all queries using joins on this server are generated by the forum software. In an attempt to optimize the query shown in the processlist: Altering core_permission_index.perm_view attribute ... As the amount of usergroups will not change and the longest string is 22 characters, would there be any obvious pitfalls changing the data type from text to varchar(255) so that a new index can be added? Meaning: ALTER TABLE `core_permission_index` CHANGE `perm_view` `perm_view` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL; ALTER TABLE `core_permission_index` ADD INDEX(`app`, `perm_type`, `perm_type_id`, `perm_view`); To reduce the row size, transferred data to the webserver and also less memory allocation and array work for PHP, would there be any benefit in looking into what attributes are actually needed for the loadIntoMemory function and load only those compared to selecting all attributes from forums_forums and core_members Any potential caching strategies one could implement here? Even if just for the guests as permissions would be the same for all. Any comments or suggestions would be greatly appreciated!
Marc Posted April 4 Posted April 4 I havent anything to really add to the optimisation side of things here, but I would very much advise on thinking about the structure of your community if you have 15000 forums. That in of itself is going to cause you issues, as its very much out of the ordinary use of the software.
Recommended Posts