1

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:

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
Improve this question
7
  • 2
    Query cache is off by default because it doesn't scale. mysqltuner also recommends off. Note the efficiency. memory tables that temporary tables use don't support TEXT/BLOB types. Its possible these are in the query result forcing a on disk usage. Take a look at the slow queries to see if they can be better indexed.
    – danblack
    Dec 28, 2020 at 9:11
  • Additional information request. # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; F) complete MySQLTuner report AND Optional information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Dec 29, 2020 at 16:11
  • Please post to pastebin.com ( and share the links ) the last 100 lines of your Slow Query Log for analysis.
    – Wilson Hauck
    Dec 29, 2020 at 16:17
  • Show us a query that is generating an on-disk temp table. Be sure to include SHOW CREATE TABLE. We will explain why. Meanwhile, it is not the end of the world.
    – Rick James
    Dec 31, 2020 at 8:42
  • I would start looking for slow SQL. You may have a number of inefficient queries that sort too much data. Do you see lots of "distinct" selects? In flemish "distinct" equals "that one smells".
    – Gerard H. Pille
    Dec 31, 2020 at 17:35

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.