MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?

I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner.pl and server config output). Is this behavior because of Innodb and just a display bug, or does the server really create files on my disk (which would explain the huge utilization of the disk)? Also, enabling query cache reduces the CPU load, despite its low utilization according to mysqltuner. Do you see a similar behavior using Mariadb? Output of mysqltuner.pl: Currently running supported MySQL version 10.3.27-MariaDB-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +OQGRAPH +PERFORMANCE_SCHEMA +SEQUENCE +TokuDB [--] Data in InnoDB tables: 8.6G (Tables: 3968) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1h 1m 53s (4M q [1K qps], 65K conn, TX: 42G, RX: 1G) [--] Reads / Writes: 98% / 2% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 251.9G [--] Max MySQL memory : 82.3G [--] Other process memory: 0B [--] Total buffers: 23.1G global + 120.3M per thread (500 max threads) [--] P_S Max memory usage: 500M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 35.4G (14.04% of installed RAM) [OK] Maximum possible memory usage: 82.3G (32.70% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (786/4M) [OK] Highest usage of available connections: 20% (100/500) [OK] Aborted connections: 0.01% (4/65835) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [!!] Query cache efficiency: 0.0% (0 cached / 3M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 791K sorts) [!!] Joins performed without indexes: 212 [!!] Temporary tables created on disk: 100% (327K on disk / 327K total) [OK] Thread cache hit rate: 99% (209 created / 65K connections) [OK] Table cache hit rate: 99% (5K open / 5K opened) [OK] table_definition_cache(5000) is upper than number of tables(4134) [OK] Open file limit used: 0% (72/1M) [OK] Table locks acquired immediately: 100% (29 immediate / 29 locks) [OK] Binlog cache memory access: 99.93% (52259 Memory / 52298 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 500.2M [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 40 thread(s). [--] Using default value is good enough for your version (10.3.27-MariaDB-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (97M used / 536M cache) [OK] Key buffer size / total MyISAM indexes: 512.0M/148.0K [OK] Read Key buffer hit rate: 95.6% (180 cached / 8 reads) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 20.0G/8.6G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3.0G * 2/20.0G should be equal to 25% [OK] InnoDB buffer pool instances: 20 [--] Number of InnoDB Buffer Pool Chunk : 160 for 20 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: 99.99% (3615446911 hits/ 3615706982 total) [!!] InnoDB Write Log efficiency: 66.63% (64938 hits/ 97466 total) [OK] InnoDB log waits: 0.00% (0 waits / 32528 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 100.0% (1B cached / 272K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is enabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. My config: tmpdir = /tmp default_storage_engine = InnoDB binlog_ignore_db = phpmyadmin binlog_cache_size = 49152 #(32768) relay_log_space_limit = 10737418240 expire_logs_days = 2 sync_master_info = 10000 performance_schema = ON big_tables = ON slave_parallel_threads = 10 slave_net_timeout = 3600 slave_skip_errors = ddl_exist_errors slave_transaction_retries = 13 innodb_stats_on_metadata = 0 #innodb_write_io_threads = 12 #(24) #innodb_read_io_threads = 8 #(24) innodb_buffer_pool_size = 20G #10737418240 innodb_buffer_pool_instances = 20 innodb_autoinc_lock_mode = 2 innodb_log_buffer_size = 512M innodb_sort_buffer_size = 512M innodb_log_file_size = 3G max_heap_table_size = 2048M max_connections = 500 #8192 #24576 max_connect_errors = 4294967295 max_binlog_size = 4147483647 max_relay_log_size = 1073741824 max_statement_time = 60 tmp_table_size = 2048M thread_cache_size = 64 query_cache_type = 1 #1 query_cache_limit = 4096 query_cache_size = 4096 open_files_limit = 1024000 table_open_cache = 65000 table_definition_cache = 5000 key_buffer_size = 512M # Per-thread Buffers sort-buffer-size = 32M read-buffer-size = 8M read-rnd-buffer-size = 32M join-buffer-size = 32M slow_query_log = ON long_query_time = 3 #skip-name-resolve = 1 [mysqld] #init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci [galera] [embedded] [mariadb] [mariadb-10.1] Thank you very much in advance and I wish you a happy new year :-) Edit: Thank you for the feedback! I already added my own table to cache related posts queries (which were the slow ones) for my wordpress sites. Here the requested additional information: - [MySQLTuner Report](https://pastebin.com/nJf4gTuX) - [SHOW GLOBAL STATUS](https://pastebin.com/nMXUMCCy) Hardware: CPU(s): 40 On-line CPU(s) list: 0-39 Thread(s) per core: 2 Core(s) per socket: 10 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz Main Memory (256 GB): Mem: 257894 177885 1083 2399 78925 75804 Disk: Disk /dev/nvme0n1: 1200.2 GB Disk /dev/nvme1n1: 1200.2 GG Ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 1031022 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 4096 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited iostat -xm (will update in the evening, when traffic is high) Linux 4.19.62-mod-std-ipv6-64-rescue 01/02/2021 _x86_64_ (40 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 56.23 0.01 1.50 0.02 0.00 42.24 Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util loop0 0.00 0.00 0.00 0.00 0.00 0.00 39.95 0.00 0.03 0.03 0.00 0.01 0.00 nvme0n1 0.01 76.34 11.48 135.84 0.28 2.45 37.88 0.01 0.13 0.33 0.11 0.52 7.63 nvme1n1 0.03 77.41 11.41 132.46 0.27 2.18 34.88 0.02 0.13 0.33 0.11 0.55 7.87 md4 0.00 0.00 22.83 432.94 0.55 4.62 23.23 0.00 0.00 0.00 0.00 0.00 0.00 md2 0.00 0.00 0.00 0.00 0.00 0.00 148.20 0.00 0.00 0.00 0.00 0.00 0.00 Htop has more than 800 threads (mostly filled from php-fpm which has up to 400 allowed children). Mysql requires actually less CPU(mostly around 130%, spikes to 1300%), even though I thought WordPress is DB heavy and not PHP heavy SHOW FULL PROCESSLIST almost always only contains just sleeping activities from WordPress sites (I'm not sure to post it here as some friends reveal too much personal information in their table names) Php-FPM Config listen.allowed_clients = 127.0.0.1 listen.owner = nginx listen.group = nginx listen.mode = 0660 user = nginx group = nginx pm = ondemand pm.max_children = 400 pm.process_idle_timeout = 6s pm.max_requests = 20002 request_slowlog_timeout = 15s clear_env = 1 rlimit_files = 162144 catch_workers_output = on php_admin_value[memory_limit] = 256M ;php_admin_value[memory_limit] = 512M php_value[session.save_handler] = files php_value[soap.wsdl_cache_dir] = /var/lib/php/wsdlcache php_value[opcache.file_cache] = /var/lib/php/opcache php_value[session.save_path] = /var/lib/php/session php_value[upload_max_filesize] = 1024M php_value[post_max_size] = 1024M php_value[max_execution_time] = 30s request_terminate_timeout = 45s php_value[opcache.enable] = 0