Improve MySql performance

[physikerwelt]

MySql seems to be a performance bottleneck for the data ingest process.

mysqltuner suggests:
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
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:
query_cache_size (> 64M)
sort_buffer_size (> 4M)
read_rnd_buffer_size (> 1M)
performance_schema=ON
key_buffer_size (~ 24M)
innodb_buffer_pool_size (>= 44.9G) if possible.
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
r

[physikerwelt]

even after #397 it seems there is a lot of disk writes

[physikerwelt]

MySQL tuner now recommends
General recommendations:
Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance
ALTER TABLE my_wiki.page FORCE; -- can free 117 MiB
ALTER TABLE my_wiki.objectcache FORCE; -- can free 492 MiB
Total freed space after defragmentation: 609 MiB

MySQL was started within the last 24 hours: recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
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:
query_cache_limit (> 128K, or use smaller result sets)
query_cache_size (=0)
query_cache_type (=0)
sort_buffer_size (> 4M)
read_rnd_buffer_size (> 1M)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
key_buffer_size (~ 4M)
innodb_buffer_pool_size (>= 45.4G) if possible.
innodb_log_file_size should be (=512M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
innodb_buffer_pool_instances(=4)

[physikerwelt]

Investigate why the db is 40G:

MariaDB [(none)]> SELECT 
    ->      table_schema as `Database`, 
    ->      table_name AS `Table`, 
    ->      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
    -> FROM information_schema.TABLES 
    -> ORDER BY (data_length + index_length) DESC;
+--------------------+----------------------------------------------------+------------+
| Database           | Table                                              | Size in MB |
+--------------------+----------------------------------------------------+------------+
| my_wiki            | text                                               |   18684.00 |
| my_wiki            | job                                                |   11795.81 |
| my_wiki            | pagelinks                                          |    3607.91 |
| my_wiki            | page_props                                         |    1970.72 |
| my_wiki            | logging                                            |    1609.86 |
| my_wiki            | recentchanges                                      |    1502.28 |
| my_wiki            | revision                                           |    1197.41 |
| my_wiki            | watchlist                                          |     866.31 |
| my_wiki            | page                                               |     675.38 |
| my_wiki            | log_search                                         |     658.83 |
| my_wiki            | wbt_text                                           |     552.72 |
| my_wiki            | wbt_item_terms                                     |     456.22 |
| my_wiki            | mathlatexml                                        |     428.61 |
| my_wiki            | externallinks                                      |     360.73 |
| my_wiki            | content                                            |     357.92 |
| my_wiki            | slots                                              |     351.48 |
| my_wiki            | wbt_term_in_lang                                   |     195.80 |
| my_wiki            | wbt_text_in_lang                                   |     182.77 |
| my_wiki            | l10n_cache                                         |     179.97 |
| my_wiki            | wbc_entity_usage                                   |     145.50 |
| my_wiki            | objectcache                                        |     143.48 |
| my_wiki            | wb_changes                                         |     137.98 |
| my_wiki            | templatelinks                                      |      80.08 |
| wiki_swmath        | l10n_cache                                         |      70.52 |
| my_wiki            | archive                                            |      65.20 |
| my_wiki            | change_tag                                         |      17.58 |
| my_wiki            | categorylinks                                      |      13.91 |
| my_wiki            | wb_items_per_site                                  |      10.34 |
| my_wiki            | comment                                            |       7.03 |
| my_wiki            | wb_changes_subscription                            |       6.09 |
| mysql              | time_zone_transition                               |       4.24 |

[physikerwelt]

The job-table is no longer part of the db and a replica has been set up. Current size

+--------------------+------------------------------------------------------+------------+
| Database           | Table                                                | Size in MB |
+--------------------+------------------------------------------------------+------------+
| my_wiki            | text                                                 |   22840.98 |
| my_wiki            | pagelinks                                            |    8436.88 |
| my_wiki            | recentchanges                                        |    3211.94 |
| my_wiki            | page_props                                           |    2725.89 |
| my_wiki            | logging                                              |    2496.58 |
| my_wiki            | wbc_entity_usage                                     |    2255.00 |
| my_wiki            | revision                                             |    1934.78 |
| my_wiki            | watchlist                                            |    1267.72 |
| my_wiki            | page                                                 |    1250.11 |
| my_wiki            | wbt_text                                             |     773.77 |
| my_wiki            | wb_changes                                           |     658.45 |
| my_wiki            | content                                              |     657.98 |
| my_wiki            | log_search                                           |     645.98 |
| my_wiki            | slots                                                |     583.77 |
| my_wiki            | wbt_item_terms                                       |     566.25 |
| my_wiki            | templatelinks                                        |     492.63 |
| my_wiki            | mathlatexml                                          |     430.61 |
| my_wiki            | externallinks                                        |     310.16 |
| my_wiki            | wbt_term_in_lang                                     |     255.86 |
| my_wiki            | wbt_text_in_lang                                     |     243.80 |
| my_wiki            | categorylinks                                        |     220.61 |
| my_wiki            | l10n_cache                                           |     164.94 |
| my_wiki            | wb_items_per_site                                    |     145.69 |
| my_wiki            | wb_changes_subscription                              |      94.72 |
| my_wiki            | objectcache                                          |      74.23 |
| my_wiki            | archive                                              |      63.16 |
| wiki_swmath        | l10n_cache                                           |      52.67 |
| my_wiki            | comment                                              |      34.08 |
| my_wiki            | change_tag                                           |      20.58 |
| mysql              | time_zone_transition                                 |       7.20 |

Query

SELECT 
          table_schema as `Database`, 
          table_name AS `Table`, 
          round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
     FROM information_schema.TABLES 
     ORDER BY (data_length + index_length) DESC;

[physikerwelt]

In addition the number of disk writes is much smaller than the reads now.

[physikerwelt]

We see a lot of disk reads again. 5.9TB in just two days.

[physikerwelt]

Testing #576

[physikerwelt]

restarting the db server now...

Some data:
Started 2024-10-22 14:34:15
Current 2024-10-24 22:16:25
Disk read:7770GB
Disk write:249GB

[physikerwelt]

This could look better now, before we investigate in detail let's also update the version as there were some CVEs happened. https://mariadb.com/kb/en/upgrading-from-mariadb-10-6-to-mariadb-10-11/

[physikerwelt]

[physikerwelt]

The update worked well on staging:

root@f7982df8f840:/# mariadb-upgrade
Reading datadir from the MariaDB server failed. Got the following error when executing the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed
root@f7982df8f840:/# mariadb-upgrade --password
Enter password: 
Major version upgrade detected from 10.6.16-MariaDB to 10.11.9-MariaDB. Check required!
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
Phase 4/8: Fixing views
mysql.user                                         OK
sys.host_summary                                   OK
sys.host_summary_by_file_io                        OK
sys.host_summary_by_file_io_type                   OK
sys.host_summary_by_stages                         OK
sys.host_summary_by_statement_latency              OK
sys.host_summary_by_statement_type                 OK
sys.innodb_buffer_stats_by_schema                  OK
sys.innodb_buffer_stats_by_table                   OK
sys.innodb_lock_waits                              OK
sys.io_by_thread_by_latency                        OK
sys.io_global_by_file_by_bytes                     OK
sys.io_global_by_file_by_latency                   OK
sys.io_global_by_wait_by_bytes                     OK
sys.io_global_by_wait_by_latency                   OK
sys.latest_file_io                                 OK
sys.memory_by_host_by_current_bytes                OK
sys.memory_by_thread_by_current_bytes              OK
sys.memory_by_user_by_current_bytes                OK
sys.memory_global_by_current_bytes                 OK
sys.memory_global_total                            OK
sys.metrics                                        OK
sys.processlist                                    OK
sys.ps_check_lost_instrumentation                  OK
sys.schema_auto_increment_columns                  OK
sys.schema_index_statistics                        OK
sys.schema_object_overview                         OK
sys.schema_redundant_indexes                       OK
sys.schema_table_lock_waits                        OK
sys.schema_table_statistics                        OK
sys.schema_table_statistics_with_buffer            OK
sys.schema_tables_with_full_table_scans            OK
sys.schema_unused_indexes                          OK
sys.session                                        OK
sys.session_ssl_status                             OK
sys.statement_analysis                             OK
sys.statements_with_errors_or_warnings             OK
sys.statements_with_full_table_scans               OK
sys.statements_with_runtimes_in_95th_percentile    OK
sys.statements_with_sorting                        OK
sys.statements_with_temp_tables                    OK
sys.user_summary                                   OK
sys.user_summary_by_file_io                        OK
sys.user_summary_by_file_io_type                   OK
sys.user_summary_by_stages                         OK
sys.user_summary_by_statement_latency              OK
sys.user_summary_by_statement_type                 OK
sys.version                                        OK
sys.wait_classes_global_by_avg_latency             OK
sys.wait_classes_global_by_latency                 OK
sys.waits_by_host_by_latency                       OK
sys.waits_by_user_by_latency                       OK
sys.waits_global_by_latency                        OK
sys.x$host_summary                                 OK
sys.x$host_summary_by_file_io                      OK
sys.x$host_summary_by_file_io_type                 OK
sys.x$host_summary_by_stages                       OK
sys.x$host_summary_by_statement_latency            OK
sys.x$host_summary_by_statement_type               OK
sys.x$innodb_buffer_stats_by_schema                OK
sys.x$innodb_buffer_stats_by_table                 OK
sys.x$innodb_lock_waits                            OK
sys.x$io_by_thread_by_latency                      OK
sys.x$io_global_by_file_by_bytes                   OK
sys.x$io_global_by_file_by_latency                 OK
sys.x$io_global_by_wait_by_bytes                   OK
sys.x$io_global_by_wait_by_latency                 OK
sys.x$latest_file_io                               OK
sys.x$memory_by_host_by_current_bytes              OK
sys.x$memory_by_thread_by_current_bytes            OK
sys.x$memory_by_user_by_current_bytes              OK
sys.x$memory_global_by_current_bytes               OK
sys.x$memory_global_total                          OK
sys.x$processlist                                  OK
sys.x$ps_digest_95th_percentile_by_avg_us          OK
sys.x$ps_digest_avg_latency_distribution           OK
sys.x$ps_schema_table_statistics_io                OK
sys.x$schema_flattened_keys                        OK
sys.x$schema_index_statistics                      OK
sys.x$schema_table_lock_waits                      OK
sys.x$schema_table_statistics                      OK
sys.x$schema_table_statistics_with_buffer          OK
sys.x$schema_tables_with_full_table_scans          OK
sys.x$session                                      OK
sys.x$statement_analysis                           OK
sys.x$statements_with_errors_or_warnings           OK
sys.x$statements_with_full_table_scans             OK
sys.x$statements_with_runtimes_in_95th_percentile  OK
sys.x$statements_with_sorting                      OK
sys.x$statements_with_temp_tables                  OK
sys.x$user_summary                                 OK
sys.x$user_summary_by_file_io                      OK
sys.x$user_summary_by_file_io_type                 OK
sys.x$user_summary_by_stages                       OK
sys.x$user_summary_by_statement_latency            OK
sys.x$user_summary_by_statement_type               OK
sys.x$wait_classes_global_by_avg_latency           OK
sys.x$wait_classes_global_by_latency               OK
sys.x$waits_by_host_by_latency                     OK
sys.x$waits_by_user_by_latency                     OK
sys.x$waits_global_by_latency                      OK
Phase 5/8: Fixing table and database names
Phase 6/8: Checking and upgrading tables
Processing databases
information_schema
msc_classification
msc_classification.msc_id_mapping                  OK
my_wiki
my_wiki.actor                                      OK
my_wiki.archive                                    OK
my_wiki.babel                                      OK
my_wiki.block                                      OK
my_wiki.block_target                               OK
my_wiki.bot_passwords                              OK
my_wiki.category                                   OK
my_wiki.categorylinks                              OK
my_wiki.change_tag                                 OK
my_wiki.change_tag_def                             OK
my_wiki.comment                                    OK
my_wiki.content                                    OK
my_wiki.content_models                             OK
my_wiki.echo_email_batch                           OK
my_wiki.echo_event                                 OK
my_wiki.echo_notification                          OK
my_wiki.echo_push_provider                         OK
my_wiki.echo_push_subscription                     OK
my_wiki.echo_push_topic                            OK
my_wiki.echo_target_page                           OK
my_wiki.echo_unread_wikis                          OK
my_wiki.ed_url_cache                               OK
my_wiki.entityschema_id_counter                    OK
my_wiki.externallinks                              OK
my_wiki.filearchive                                OK
my_wiki.image                                      OK
my_wiki.imagelinks                                 OK
my_wiki.interwiki                                  OK
my_wiki.ip_changes                                 OK
my_wiki.ipblocks_restrictions                      OK
my_wiki.iwlinks                                    OK
my_wiki.job                                        OK
my_wiki.l10n_cache                                 OK
my_wiki.langlinks                                  OK
my_wiki.linktarget                                 OK
my_wiki.log_search                                 OK
my_wiki.logging                                    OK
my_wiki.math_mlp                                   OK
my_wiki.math_review_list                           OK
my_wiki.math_wbs_entity_map                        OK
my_wiki.math_wbs_text_store                        OK
my_wiki.mathidentifier                             OK
my_wiki.mathindex                                  OK
my_wiki.mathlog                                    OK
my_wiki.mathobservation                            OK
my_wiki.mathpagesimilarity                         OK
my_wiki.mathperformance                            OK
my_wiki.mathrevisionstat                           OK
my_wiki.mathsemantics                              OK
my_wiki.mathvarstat                                OK
my_wiki.matomo                                     OK
my_wiki.module_deps                                OK
my_wiki.oauth2_access_tokens                       OK
my_wiki.oauth_accepted_consumer                    OK
my_wiki.oauth_registered_consumer                  OK
my_wiki.objectcache                                OK
my_wiki.oldimage                                   OK
my_wiki.openid_connect                             OK
my_wiki.page                                       OK
my_wiki.page_props                                 OK
my_wiki.page_restrictions                          OK
my_wiki.pagelinks                                  OK
my_wiki.protected_titles                           OK
my_wiki.querycache                                 OK
my_wiki.querycache_info                            OK
my_wiki.querycachetwo                              OK
my_wiki.recentchanges                              OK
my_wiki.redirect                                   OK
my_wiki.revision                                   OK
my_wiki.searchindex                                OK
my_wiki.site_identifiers                           OK
my_wiki.site_stats                                 OK
my_wiki.sites                                      OK
my_wiki.slot_roles                                 OK
my_wiki.slots                                      OK
my_wiki.templatelinks                              OK
my_wiki.text                                       OK
my_wiki.updatelog                                  OK
my_wiki.uploadstash                                OK
my_wiki.user                                       OK
my_wiki.user_autocreate_serial                     OK
my_wiki.user_former_groups                         OK
my_wiki.user_groups                                OK
my_wiki.user_newtalk                               OK
my_wiki.user_properties                            OK
my_wiki.watchlist                                  OK
my_wiki.watchlist_expiry                           OK
my_wiki.wb_changes                                 OK
my_wiki.wb_changes_subscription                    OK
my_wiki.wb_id_counters                             OK
my_wiki.wb_id_mapping                              OK
my_wiki.wb_items_per_site                          OK
my_wiki.wb_property_info                           OK
my_wiki.wbc_entity_usage                           OK
my_wiki.wbqc_constraints                           OK
my_wiki.wbt_item_terms                             OK
my_wiki.wbt_property_terms                         OK
my_wiki.wbt_term_in_lang                           OK
my_wiki.wbt_text                                   OK
my_wiki.wbt_text_in_lang                           OK
my_wiki.wbt_type                                   OK
performance_schema
sys
sys.sys_config                                     OK
user__quickstatements_auth
user__quickstatements_auth.batch_oauth             OK
user__quickstatements_auth.user                    OK
user__quickstatements_p
user__quickstatements_p.batch                      OK
user__quickstatements_p.batch_oauth                OK
user__quickstatements_p.command                    OK
user__quickstatements_p.user                       OK
wiki_swmath
Phase 7/8: uninstalling plugins
Phase 8/8: Running 'FLUSH PRIVILEGES'
OK
root@f7982df8f840:/# 

[physikerwelt]

root@1f379e8b6369:/# perl mysqltuner.pl --user root --pass 
 >>  MySQLTuner 2.6.0
         * Jean-Marie Renouard <[email protected]>
         * Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 2.0K (Tables: 2)
[--] Data in InnoDB tables: 272.7G (Tables: 243)
[--] Data in Aria tables: 32.0K (Tables: 1)
[!!] Total fragmented tables: 1
 
[OK] Currently running supported MySQL version 10.11.9-MariaDB-ubu2204
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned

 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 13h 35m 2s (165M q [537.851 qps], 5M conn, TX: 858G, RX: 42G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 125.8G
[--] Max MySQL memory    : 25.9G
[--] Other process memory: 0B
[--] Total buffers: 20.2G global + 5.7M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 149M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 20.9G (16.60% of installed RAM)
[OK] Maximum possible memory usage: 25.9G (20.58% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (169K/165M)
[OK] Highest usage of available connections: 10% (100/1000)
[OK] Aborted connections: 0.00% (6/5109563)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (48 temp sorts / 2M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 3% (51K on disk / 1M total)
[OK] Thread cache hit rate: 99% (100 created / 5M connections)
[OK] Table cache hit rate: 99% (268M hits / 268M requests)
[!!] table_definition_cache (400) is less than number of tables (537) 
[OK] Open file limit used: 0% (63/33K)
[OK] Table locks acquired immediately: 100% (148K immediate / 148K locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 150.0M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Consider migrating 2 following tables to InnoDB:
[--] * InnoDB migration request for my_wiki.searchindex Table: ALTER TABLE my_wiki.searchindex ENGINE=InnoDB;
[--] * InnoDB migration request for wiki_swmath.searchindex Table: ALTER TABLE wiki_swmath.searchindex ENGINE=InnoDB;
[--] General MyIsam metrics:
[--]  +-- Total MyISAM Tables  : 2
[--]  +-- Total MyISAM indexes : 2.0K
[--]  +-- KB Size :24.0M
[--]  +-- KB Used Size :4.5M
[--]  +-- KB used :18.7%
[--]  +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--]  +-- Write KB hit rate: 0% (0 cached / 0 writes)
[!!] Key buffer used: 18.7% (4.5M used / 24.0M cache)
[OK] Key buffer size / total MyISAM indexes: 24.0M/2.0K
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 20.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 20.0G / 272.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.15625%): 32.0M * 1 / 20.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 64 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% (2203911027564 hits / 2203999549502 total)
[OK] InnoDB Write Log efficiency: 93.83% (14424327 hits / 15372419 total)
[OK] InnoDB log waits: 0.00% (0 waits / 948092 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/2.6M
[OK] Aria pagecache hit rate: 96.5% (17B cached / 620M reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 1 server(s).
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[!!] This replication slave is not running but seems to be configured.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance
      ALTER TABLE `my_wiki`.`l10n_cache` FORCE; -- can free 27928 MiB
    Total freed space after defragmentation: 27928 MiB
    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:
    table_definition_cache (400) > 537 or -1 (autosizing if supported)
    key_buffer_size (~ 4M)
    innodb_buffer_pool_size (>= 272.7G) if possible.
    innodb_log_file_size should be (=5G) if possible, so InnoDB total log file size equals 25% of buffer pool size.

[physikerwelt]

Even if no imports are running queries like

SELECT /* WikiPage::isCountable  */  1  FROM `pagelinks`    WHERE pl_from = 4705191  LIMIT 1

generate a lot of load as the pagelink table is very large

[physikerwelt]

I renamed the link tables, which solved it for now.

[physikerwelt]

Current table sizes

Database	Table	Size in MB
my_wiki	text	135724.00
my_wiki	l10n_cache	27205.98
my_wiki	externallinks	21704.83
my_wiki	recentchanges	21153.98
my_wiki	wbc_entity_usage	13710.91
my_wiki	pagelinks_bkr	12624.95
my_wiki	recentchanges_backup	8376.98
my_wiki	revision	8017.92
my_wiki	logging	4715.88
my_wiki	templatelinks	4494.95
my_wiki	page_props	4014.94
my_wiki	watchlist	2931.92
my_wiki	content	2653.98
my_wiki	page	2481.80
my_wiki	slots	2335.97
my_wiki	log_search	1108.97
my_wiki	mathlog	983.53
my_wiki	wbt_text	945.97
my_wiki	wbt_item_terms	737.52
my_wiki	wb_items_per_site	654.55
my_wiki	wb_changes_subscription	541.34
my_wiki	wbt_term_in_lang	433.02
my_wiki	wbt_text_in_lang	411.97
my_wiki	linktarget_bkf	317.69
my_wiki	wb_changes	243.78
my_wiki	comment	183.25
my_wiki	archive	143.28
my_wiki	change_tag	140.75
wiki_swmath	l10n_cache	54.64
my_wiki	categorylinks	37.69
my_wiki	redirect	22.03
my_wiki	objectcache	10.03
mysql	time_zone_transition	7.20
my_wiki	imagelinks	4.55
my_wiki	pagelinks	3.52
matomo	matomo_archive_blob_2024_10	3.42
my_wiki	wb_id_mapping	3.41
my_wiki	linktarget	3.03