High mysql write activity and high 'Created_tmp_files'

Question

After using iostat and iotop, I can see that mysql read and write activity is consistently in the several M/s.

I have been monitoring long queries with slow_query_log and all the slow queries longer than 2s are just 'reads' (i.e. select queries). There are no other queries running when I use show processlist;.

Here are the variables I get when I run show global status like '%tmp%';:

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 13      |
| Created_tmp_files       | 137781 |
| Created_tmp_tables      | 783045 |
+-------------------------+--------+

My guess is that these read queries are writing a lot to the disk.

My mysql conf is as follows (these are just what are updated by me, rest are default)

innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_size = 10737418240
innodb_buffer_pool_instances = 10
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0
max_connections = 800
innodb_purge_threads = 16
innodb_max_purge_lag = 2000000

NB: binlogs are disabled as not necessary

My machine is a 32GB RAM, 8 core CPU machine, mysql v8. MySQL usually takes around 40% of free memory and 1/8 of available CPU % at any time when I use top. The server supports a lot of users so thats why the buffer pool size and instances have been increased to a high level.

I was wondering if anyone had any idea of what I could investigate next. 2 questions:

1. Are the Created_tmp_files unusually high, and is there not enough memory available for mySQL, that it's writing temporary tables to disk.

2. Are there any adjustments I could make to max_heap_table_size or innodb_buffer_pool_chunk_size to improve this?

If any ideas of what I could look into next, please let me know. Open to all ideas! I'm a newbie when it comes to db

Answer 1

Are you using "TempTable storage engine"?? The following discusses tmp_tables, not tmp_files.

Indexing is the most likely cure for "lots" of tmp tables, (and many other high metrics) is to improve the indexing. To achieve that, first identify the "slow" queries. (After all, if it needs a tmp table, it is likely to be "slow"!)

Then present the query, plus SHOW CREATE TABLE here for advice on indexing it. We may or may not be able to avoid a tmp table. For example, GROUP BY abc ORDER BY xyz will [perhaps] always use a tmp table, sometimes two. It may be practical to modify one or the other of them to make them compatible to get rid of one of the tmp tables. For example GROUP BY a,b ORDER BY b,a --> GROUP BY b,a ORDER BY b,a. That is [I think] guaranteed to give the same results and usually be faster.

Look at EXPLAIN. But be aware that neither "using temporary", nor "using filesort" necessarily means what it says. It may or may not actually hit the disk. A TEXT column prevents use of in-memory sort; reformulating the query may prevent the need for shoveling the TEXT through the sort.

innodb_buffer_pool_chunk_size -- irrelevant.

max_heap_table_size and tmp_table_size -- Don't raise above 1% of available RAM, else you might incur swapping. Swapping is much worse for performance than tmp tables.

Created_tmp_tables / Uptime -- >80/sec is "high"
Created_tmp_disk_tables / Uptime -- >10/sec is "high"
Created_tmp_disk_tables / Created_tmp_tables -- >30% is "high"
Created_tmp_files / Uptime_since_flush_status -- 0.3/sec is "high"

innodb_buffer_pool_size should be set to about 70% of available RAM. You currently have 10G/32GB. Unless MySQL is sharing RAM with some bulky apps, that setting could be raised.

But you only have a high "files", not "tmp tables".

Please provide

SHOW VARIABLES LIKE "%mmap%";
SHOW STATUS LIKE "%mmap%";

Answer 2

Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

• Version: 8.0.18
• 32 GB of RAM
• Uptime = 15:51:54; some GLOBAL STATUS values may not be meaningful yet.
• 75.5 QPS

The More Important Issues:

Suggested changes to settings:

innodb_buffer_pool_size -- if RAM is available, something like 20G
innodb_log_file_size = 1600M
innodb_flush_method -- O_DIRECT may be better than fsync
innodb_flush_log_at_trx_commit -- 2
max_connections = 100  -- (max of only 39 used in this sample)

If SSD (not spinning HDD)

innodb_io_capacity = 1000

Looking at the number of transactions and write statements, I wonder if some transactions could be combined. This would save some of the overhead of transactions.

The system seems to be somewhat I/O-bound, but not necessarily CPU-bound. The above settings should help some with I/O.

Why did you pick innodb_autoinc_lock_mode = 2?

Consider setting up the slowlog.

See if IODKU would be better than REPLACE.

Compared to other servers, Created_tmp_files / Uptimes is about 90th percentile. High, but I don't know what to recommend. This is distinct from Created_tmp_tables.

Details and other observations:

( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (0 + 0 + 7230717 + 5716392 + 740520 + 5716382) / 57114 = 339 /sec -- IOPs? -- If the hardware can handle it, set innodb_io_capacity (now 200) to about this value.

( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 0 + 0 + 7230717 + 5716392 + 740520 + 5716382 ) / 200 / 57114 = 169.9% -- This may be a metric indicating what innodb_io_capacity is set reasonably. -- Increase innodb_io_capacity (now 200) if the hardware can handle it.

( table_open_cache ) = 4,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( innodb_buffer_pool_size ) = 10,240 / 32768M = 31.2% -- % of RAM used for InnoDB buffer_pool -- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky. Use 2000 if using SSD drive.

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((6905000 + 5716382) ) / 57114 = 220 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size (now 10737418240)?

( Innodb_buffer_pool_pages_flushed ) = ((6905000 + 5716382) ) / 57114 = 100 /sec -- Writes (flushes) -- Increase innodb_buffer_pool_size (now 10737418240)?

( Innodb_os_log_written ) = (Innodb_ibuf_merged_inserts + Innodb_ibuf_merged_deletes + Innodb_ibuf_merges) / 57114 = 931960 /sec -- This is an indicator of how busy InnoDB is. -- Very idle or very busy InnoDB.

( Innodb_log_writes ) = (Innodb_ibuf_merged_inserts + Innodb_ibuf_merged_deletes + Innodb_ibuf_merges) / 57114 = 226 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 53,228,001,792 / (57114 / 3600) / 2 / 48M = 33.3 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 57,114 / 60 * 48M / 53228001792 = 0.9 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( Innodb_dblwr_writes ) = (Innodb_ibuf_merged_inserts + Innodb_ibuf_merged_deletes + Innodb_ibuf_merges) / 57114 = 13 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 7230717 + 5716392 ) / 57114 / 200 = 113.3% -- If > 100%, need more io_capacity. -- Increase innodb_io_capacity (now 200) if the drives can handle it.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy

( innodb_flush_log_at_trx_commit ) = 0 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 0)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( innodb_purge_threads ) = 16 -- Number of threads to clean up history list. -- If you have a lot of writes, recommend 4 in versions 5.6 and 10.0 or later.

( tmp_table_size ) = 128M -- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 134217728) to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (235392 + 341228 + 42936 + 2) / 236808 = 2.62 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 133 - 0 ) / ( 133 + 0 ) = 100.0% -- Are you closing your prepared statements? -- Add Closes.

( Com_stmt_close / Com_stmt_prepare ) = 0 / 133 = 0 -- Prepared statements should be Closed. -- Check whether all Prepared statements are "Closed".

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( Max_used_connections / max_connections ) = 39 / 400 = 9.8% -- Peak % of connections -- Since several memory factors can expand based on max_connections (now 400), it is good not to have that setting too high.

Abnormally large:

Com_replace_select = 23 /HR
Com_show_keys = 78 /HR
Com_show_slave_status = 0.033 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.33
Innodb_data_pending_fsyncs = 2
Innodb_log_writes / Innodb_log_write_requests = 190.0%
innodb_max_purge_lag = 2.0e+6
max_error_count = 1,024
max_length_for_sort_data = 4,096
performance_schema_max_cond_classes = 100
performance_schema_max_mutex_classes = 300
performance_schema_max_rwlock_classes = 60
performance_schema_max_stage_classes = 175
performance_schema_max_thread_classes = 100

Abnormal strings:

event_scheduler = ON
have_query_cache = NO
innodb_fast_shutdown = 1
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on,range_optimizer=on,dynamic_range=...
protocol_compression_algorithms = zlib,zstd,uncompressed
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN