Hi After the latest changes and new databases (hello, forums! ;-), I decided that it's time to check the current situation of our database cluster again. You might know mysqltuner.pl and tuning-primer.sh from the mysql-tuning-scripts package: the two tools analyze a running mysql/mariadb and galera cluster and print out statistics and recommendations (and even more, if you call them with the right options ;-). Both tools recommended to increase the "InnoDB buffer pool size". I cross checked this with an SQL statement stolen from stackexchange[1]: => result of all tools: Recommended_InnoDB_Buffer_Pool_Size = 32G As the nodes inside the cluster just had 16G RAM and already showed some lags, I decided to follow the recommendation - but also increase the amount of available RAM on the machines. The maximum amount of RAM configured/available for each node was 20G. Good enough to increase the 16G to 20G without downtime, but not enough for the planned 32G innodb_buffer_pool if it really gets used. So I decided to set the current amount of RAM for each node to 48G and added a buffer in the VM configuration up to 64G. This needed a cold restart of all nodes (and the backup host) - but everything went fine. Just remember that we already set the systemd timeout value "TimeoutSec=7200" for the mariadb service, as in worst case (more than initially 20, now 40min downtime of the mariadb service results in a SST restore via mariabackup for ~40G of database files), the mariadb service needs up to 1:30h to start. So we are now at 48G RAM for each node together with the following InnoDB settings in my.cnf: innodb_buffer_pool_size = 32G innodb_buffer_pool_instances = 12 innodb_doublewrite = 1 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 8M innodb_log_file_size = 4G innodb_lock_wait_timeout = 240 innodb_print_all_deadlocks = 1 innodb_io_capacity = 2000 innodb_read_io_threads = 64 innodb_thread_concurrency = 0 innodb_write_io_threads = 64 Thanks for reading so far :-) From my side, there are currently some todo's and questions left: * there is a database called "database" on each node, which does not contain any tables but instead a file called db.opt with: default-character-set=utf8mb4 default-collation=utf8mb4_general_ci as content. Does anyone know why this is there? * Some wiki databases have 3 different collations and 2 different engines defined now. Note: interestingly not all of them. Example: [!!] 3 different collations for database wiki_it [!!] 2 different engines for database wiki_it [!!] wiki_it table column(s) has several charsets defined for all text like column(s). [!!] wiki_it table column(s) has several collations defined for all text like column(s). [!!] There are 2 storage engines. Be careful. Affected wiki databases: wiki_es, wiki_it, wiki_nl, wiki_pt, wiki_old_de, wiki_zh, wiki_en and: webforums When I migrated the wiki databases, this was not the case. * Some (äh: 8944!) tables also have wrong types set for some fields. * Also very important: a lot of tables don't have primary keys. This slows down the synchronization between the nodes. I will see if there are some guidelines for the mediawiki databases to add indexes to these tables - but some webforums tables also don't have primary keys. @Per: maybe you can check this for vB? * On the opposite side, there are ~20 unused indexes. I will remove them next week. I will start with some "ALTER TABLE" operations during the next days, to fix some of the settings above. Depending on the size of the tables, this might lead to some (hopefully short) locks of the databases. With kind regards, Lars -- [1]: https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-inno... -- To unsubscribe, e-mail: heroes+unsubscribe@opensuse.org To contact the owner, e-mail: heroes+owner@opensuse.org