JohnWH

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: 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!

Was planning on moving from ElasticSearch to OpenSearch in the weekend and wanted to check if anyone had any experience with going with the newest version (2.6.0). Going by the OpenSearch documentation it states they are only introducing breaking changes between major versions, but wanted to see if anyone had any experience with it as it is not always easy to do full testing in a dev environment. Can see the following at line 98 applications\core\modules\admin\discovery\search.php, and would assume one could just take >= 2.2 and bump it to >= 2.7 (Any other constants/variables/strings/etc that needs to be changed?) if ( version_compare( $response['version']['number'], '2.2', '>=' ) ) { throw new \DomainException( \IPS\Member::loggedIn()->language()->addToStack('search_opensearch_server_unsupported_version', FALSE, array( 'sprintf' => array( $response['version']['number'] ) ) ) ); } Cheers

Hi Marc, thank you for getting so quickly back to us. Much appreciated Unfortunately it would be a challenge sorting off-premises sftp. Would it be possible to either here or via DM get some details regarding what needs to be done in plain text? Have access to the file system myself and would most likely be capable to solve it by just getting an indication on where the problem lies. Just as some quick notes: No settings have been altered, nor any plugins been installed or similar. The only change was manually uploading and extracting the 4.6.12.1 delta zip file the upgrade tool generated before running the upgrade Cheers,

Hi Marc, Have updated the credentials on file. Due to technical limitations I could not include ftp Cheers

Hi, We upgraded to 4.6.12.1 a couple of days ago and have been receiving reports from members that the "Unread Content" activity stream has changed behavior from only showing every updated thread once regardless of how many new comments there were, to showing one entry in the feed for every new comment, making the stream harder to keep track on due to the amount of entries on some busier threads. Is this related to what's being described in here and now considered intended, or some thing else? Have tried to reindex everything (using elasticsearch if that matters) and also reset the feeds in the admin panel, but that did not change it Any tips/comments to change it back would be greatly appreciated 🙂