physikerwelt
commented
1 year ago even after #397 it seems there is a lot of disk writes
Closed physikerwelt closed 11 months ago
physikerwelt
commented
1 year ago even after #397 it seems there is a lot of disk writes
physikerwelt
commented
1 year ago 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 timeVariables 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
commented
1 year ago 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 |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;In addition the number of disk writes is much smaller than the reads now.
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