Question posted in 
I need help with MariaDB server optimization. I have a lot I/O operations (created tmp disk tables) and I want to reduce it.
Hardware: CPU 20 x 2197mHz, RAM 50 Gb, SSD disks RAID 10
Software: 10.1.26-MariaDB-0+deb9u1 – Debian 9.1
Server handles WordPress databases (~1500).
Config:
key_buffer_size = 384M
max_allowed_packet = 5096M
thread_stack = 192K
thread_cache_size = 16
myisam_recover_options = BACKUP
max_connections = 200
table_cache = 12000
max_connect_errors = 20
open_files_limit = 30000
wait_timeout = 3600
interactive_timeout = 3600
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0
join_buffer_size = 2M
tmp_table_size = 1G
max_heap_table_size = 1G
table_open_cache = 15000
innodb_buffer_pool_size = 35G
innodb_buffer_pool_instances = 40
2
Answers
Are you using MyISAM? If you are you shouldn’t be. Convert any MyISAM tables outside the mysql schema to InnoDB and set key_buffer_size to 1M.
max_allowed_packet = 5G is absurdly high.
thread_cache_size = 0 is the recommended default. Unless you really know what you are doing and have measurements to back it up, you should leave it alone.
join_buffer_size is another setting you should almost certainly not be touching.
max_heap_table_size = 1G is almost certainly too large – if you are getting temporary tables that big created in memory with any regularity, your server will run out of memory, grind to a halt and OOM anyway.
Yes, follow Gordan’s advice.
Improve WP’s indexing by following the advice here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
Turn on the slowlog to identify what queries are the worst: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog