0

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

3
  • When you run show status like '%tmp%';, that is only coming from your current session. What you really want to use is show global status like '%tmp%'; so we can see the overall temp table usage. Please run show global status like '%tmp%';. Nov 21, 2022 at 17:31
  • you are spot on. I just ran what you told me and have an insane amount of tmp tables: Created_tmp_disk_tables | 13 , Created_tmp_files | 137722 |, Created_tmp_tables | 782150 |
    – Rohan
    Nov 21, 2022 at 18:49
  • What version of MySQL? Is it hosted in the cloud?
    – Rick James
    Nov 21, 2022 at 23:18

2 Answers 2

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%";
10
  • Thanks for getting back to me, Rick. After adding 'global' to the show status query, it seems like there were 700k temp tables too: Created_tmp_disk_tables | 13 , Created_tmp_files | 137722 |, Created_tmp_tables | 782150 |
    – Rohan
    Nov 21, 2022 at 18:51
  • I can update innodb_buffer_pool_size to have higher RAM. Are there any other settings that I could adjust to make the amount of tmp tables to be lower?
    – Rohan
    Nov 21, 2022 at 18:52
  • Output for SHOW VARIABLES LIKE "%mmap%"; is myisam_mmap_size | 18446744073709551615, myisam_use_mmap | OFF , temptable_use_mmap | ON Empty set for the second query
    – Rohan
    Nov 21, 2022 at 18:58
  • @Rohan - But how many per second?
    – Rick James
    Nov 21, 2022 at 19:05
  • The mmap question related to "tmp_files" -- about which I know nothing. It is relatively new in InnoDB, and I found no useful info.
    – Rick James
    Nov 21, 2022 at 19:07
1
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
2
  • Hi Rick, sorry for the delay, and I really appreciate the detailed response — it's really kind of you. I moved the monster joins off-site and all third-party tools that were using mysql off-site, and now there are 0 slow queries in my slow query logs. I was hoping this would solve the mysql high writes, but it's the same. Good news is there are no tmp disk tables now, and no 'performance' degradation that I notice; queries from my app are being run fast and quickly. I'm going to play around with some of those variables. Any other things you'd recommend I check (maybe turn on general log)?
    – Rohan
    Nov 28, 2022 at 9:04
  • @Rohan - High writes benefit from batching; sometimes 10x improvement. The General log is rarely useful -- mostly for discovering queries (or combinations of such) that can't be located by looking at the code. The slowlog is best for performance issues.
    – Rick James
    Nov 28, 2022 at 17:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.