A1: Almost always CPU pressure implies lack of INDEX or poorly formulated SELECT.
A2: Same pid -- so that connection is doing one LOAD after another without stopping? That's OK. One thing to check when you do the SHOW is the Time column -- it is the elapsed time for the current query if running, or the amount of time it has been idle (if it says Sleep).
A3: "...DATA_080" -- You have differently named tables, each with multiple partitions? Or are you saying that "...DATA_080" is what you mean by "partitioning"? There is a big difference.
A3: "InnoDB tables created with hundreds of partitions" -- inefficient. How many such tables? How many partitions? What style of partitioning? Let's see SHOW CREATE TABLE (with only a sampling of the partitions listed).
A4: Are the LOADs in the slowlog? Crank long_query_time down to 0.3 to see if you catch something else.
A6: 40 GB vs 128GB of RAM. You could increase the buffer_pool, but it probably won't change things much. How much does top say that mysqld is using? (I suspect the 90% is bogus.)
A7: "to improve the overall performance?" -- Wait! I have not heard what the performance problem is. So far, it sounds like the only heavy task is lots of LOADs?
Thanks for the attachments!
Observations:
- Version: 5.7.20-log
- 120 GB of RAM
- Uptime = 17d 00:34:34
- You are not running on Windows.
- Running 64-bit version
- You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
You are writing (and flushing) 990 blocks per second. That is rather high, and implies that you do have SSD drive(s).
You asked about performance; this seems to be the limiting factor, in spite of iostat not pointing it out.
How many rows in a typical LOAD DATA?
The large number of partitions leads to the need to open lots of partitions (which are effectively 'sub-tables'). Let's work on that by discussing the partitioning.\
Meanwhile change some settings:
open_file_limit = 10000
table_open_cache = 5000
table_definition_cache = 3333
Are many of the 4001 tables partitioned?!
Increase both innodb_page_cleaners and innodb_buffer_pool_instances to 16.
If it turns out you are using SSDs, change innodb_flush_neighbors to 0.
innodb_log_file_size is 1G; change to 5G. However, it is not necessarily easy to do so. Skip this recommmendation if necessary.
Perhaps increasing bulk_insert_buffer_size to 32M would be beneficial, especially since you are doing a lot of LOAD DATAs.
You mentioned not many SELECTs in the slowlog; what about UPDATEs? --
Com_update = 197 /sec
Com_update_multi = 0.48 /sec
I guess this would explain why SHOW PROCESSLIST does keeps seeing a different table:
Com_load = 21 /sec
Innodb_rows_inserted = 4829 /sec
And, does that mean that the average file being loaded has 230 rows?
And, with only 20 Connections/second, there must be hundreds of LOADs in a single connection.
What's in the Stored Routines.
Several ALTER TABLEs per minute (on average) -- What's up?
Along with SHOW CREATE TABLE, please provideSHOW TABLE STATUS` for at least one of the many tables. I want more details before judging these:
OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0028`; -- can free 82 MB
OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0029`; -- can free 82 MB
OPTIMIZE TABLE `ca_uim`.`rn_qos_data_0035`; -- can free 112 MB
(etc)
If you raise innodb_buffer_pool_size, don't increase it to more than 90G.
Details and other observations:
( Innodb_buffer_pool_pages_flushed ) = 1,456,312,527 / 1470874 = 990 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size
( innodb_buffer_pool_size / _ram ) = 40960M / 122880M = 33.3% -- % of RAM used for InnoDB buffer_pool
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (8M / 0.20 + 40960M / 0.70) / 122880M = 47.7% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( Opened_tables ) = 44,353,045 / 1470874 = 30 /sec -- Frequency of opening Tables
-- increase table_open_cache
( Opened_table_definitions ) = 26,430,658 / 1470874 = 18 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.
( Table_open_cache_overflows ) = 43,864,852 / 1470874 = 30 /sec
-- May need to increase table_open_cache
( Table_open_cache_misses ) = 44,353,021 / 1470874 = 30 /sec
-- May need to increase table_open_cache
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 40960M / 8 = 5120MB -- Size of each buffer_pool instance.
-- An instance should be at least 1GB. In very large RAM, have 16 instances.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5 -- page_cleaners
-- Recommend setting innodb_page_cleaners to innodb_buffer_pool_instances
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((8736963 + 1456312527) ) / 1470874 = 996 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?
( Innodb_os_log_written ) = 2,138,679,386,624 / 1470874 = 1454019 /sec -- This is an indicator of how busy InnoDB is.
-- Very idle or very busy InnoDB.
( Innodb_log_writes ) = 151,318,300 / 1470874 = 102 /sec
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,470,874 / 60 * 1024M / 2138679386624 = 12.3 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)
( Innodb_dblwr_writes ) = 27,805,868 / 1470874 = 19 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( 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.
( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue
( bulk_insert_buffer_size / _ram ) = 8M / 122880M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.
( (Queries-Questions)/Queries ) = (1084892343-158114534)/1084892343 = 85.4% -- Fraction of queries that are inside Stored Routines.
-- (Not bad if high; but it impacts the validity of some other conclusions.)
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (39274680 + 290107639 + 1191197 + 0) / 25 = 1.32e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.
( Select_scan ) = 2,955,230 / 1470874 = 2 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)
( sort_buffer_size ) = 24M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (39274680 + 1191197 + 0 + 0 + 290107639 + 710124) / 1470874 = 225 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives
( Com_alter_table ) = 121,112 / 1470874 = 0.082 /sec
-- Why so many ALTERs?
( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.
( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( back_log / max_connections ) = 80 / 151 = 53.0%
You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.
Abnormally small:
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.32
slave_net_timeout = 60
Abnormally large:
Binlog_cache_use = 105 /sec
Binlog_stmt_cache_use = 0.32 /sec
Com_create_trigger = 0.0073 /HR
Com_create_view = 0.015 /HR
Com_dealloc_sql = 0.082 /sec
Com_drop_trigger = 0.0073 /HR
Com_drop_view = 0.015 /HR
Com_execute_sql = 0.082 /sec
Com_insert_select = 0.51 /sec
Com_insert_select + Com_replace_select = 0.51 /sec
Com_load = 21 /sec
Com_prepare_sql = 0.082 /sec
Com_show_engine_mutex = 0.0049 /HR
Com_truncate = 0.24 /sec
Com_update = 197 /sec
Com_update_multi = 0.48 /sec
Handler_delete = 63 /sec
Handler_update = 2396 /sec
Innodb_buffer_pool_bytes_dirty = 1,866.6MB
Innodb_buffer_pool_pages_data = 2.59e+6
Innodb_buffer_pool_pages_dirty = 119,461
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.34
Innodb_buffer_pool_pages_total = 2.62e+6
Innodb_data_fsyncs = 211 /sec
Innodb_data_writes = 1127 /sec
Innodb_data_written = 33895601 /sec
Innodb_dblwr_pages_written = 989 /sec
Innodb_log_write_requests = 2631 /sec
Innodb_os_log_fsyncs = 103 /sec
Innodb_os_log_pending_fsyncs = 1
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 2,496.0MB
Innodb_pages_created = 40 /sec
Innodb_pages_read + Innodb_pages_written = 1.48e+9
Innodb_pages_written = 990 /sec
Innodb_rows_deleted = 63 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 4891 /sec
Innodb_rows_inserted = 4829 /sec
Innodb_rows_updated = 2396 /sec
Performance_schema_rwlock_instances_lost = 2.04e+10
Qcache_free_memory = 256.0MB
innodb_io_capacity_max = 6,000
join_buffer_size = 64MB
max_tmp_tables = 64
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210
Abnormal strings:
bind_address = 0.0.0.0
have_ssl = YES
have_symlink = DISABLED
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_fast_shutdown = 1
innodb_large_prefix = ON
innodb_undo_directory = ./
log_bin_trust_function_creators = ON
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
Schema Critique
decimal(28,2) occupies 13 bytes. Consider whether some other datatype will be sufficiently precise for your application, yet save a lot of space: BIGINT and DOUBLE each take only 8 bytes hold 16-17 significant digits; no decimal places for the former, floating decimal for the latter.- Does
tz_offset need a 4-byte integer? Shrink it, too.
- What is the purpose of the
PARTITIONing? If performance, then show us the query that depends on partitioning. (I may shoot down any performance benefit.) If it is purging "old" data, then I strongly recommend switching to weekly partitions; this will save disk space and not hurt anything else, plus avoid some of the criticisms I had when looking at the STATUS. You would not have to change the existing partitions, rather make weekly partitions going forward, thereby gradually improving things without having to take a big downtime to make the change.
- Do not pre-build the partitions; do it "just-in-time". But also have a "future" partition to collect anything that accidentally lands in the table before building tomorrow's partition. More discussion in my blog .
- When you don't explicitly specify a
PRIMARY KEY, InnoDB creates a hidden 6-byte PK. Please show me the main queries, so I can help devise a better set of keys, possibly avoiding that PK. If necessary, I may recommend a MEDIUMINT UNSIGNED AUTO_INCREMENT (3 bytes, 16M max); this would easily allow 669,536 rows (as per `SHOW TABLE STATUS).
Crude estimates of space (MB per table) after implementing suggestions:
current proposed
Data 76 50
Index 95 60?
Free 70 10
--- ---
TOTAL 241 120
Shrinking the tables in half may cut the 990 writes/second in half. (There are several unknowns, especially with indexing.)
Since Data_free for the ~100 partitions is about equal to the predicted savings due to OPTIMIZE TABLE, I expect that OPTIMIZE won't really save anything. Each partition is always left with 4-7MB of "free" space.
