0

I recently got a task to tune MySQL for predominantly write workload on multiple innodb tables which are huge and partitioned.

About the application: It's Nimsoft suite, so the SQL queries are not written by developers and comes with the suite.

  • OS : Centos 7
  • MySQL Version : 5.7.20 Community server
  • RAM : 120 GB
  • cores: 24

Looking at the ouput of cat /sys/block/sda/queue/rotational and lsblk I suspect the hard disks are not SSD but I'm not sure because all I can see is VMWare virtual disk from my OS. So I will cross verify with storage team.

Observations so far:

1) No CPU pressure at all. ~10% of the CPU is used.

2)Looking at the MySQL workload with show processlist, I can see one thread is connected and executing continuously (processID is same and never goes to sleep).

3) Connected thread is only executing SQL

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_098
    FILEDS TERMINATED BY ',' LINES

But I can see the Loading in table happens fast because immediately when I execute show processlist again I can see Loading happens in different table

LOAD DATA CONCURRENT LOCAL INFILE 'data'
    INTO TABLE RN_QOS_DATA_080
    FILEDS TERMINATED BY ',' LINES

Each these tables are InnoDB tables created with hundreds of partitions.

4) I enabled Slow query log to capture any long running queries and I couldn't see any select query appearing in slow log for a long_query_time value of 1s.

5)Looking at the iostat output , I don't see abnormal qsize or waitime for write operations.

6) innodb_buffer_pool is set to 40 GB but the top command shows mysqld is constantly consuming more than 90% memory.

Since it is a suite and not much control we have over the SQL that is executing and also there are no slow running selects to identify and optimize.

What are the settings at the server level/ mysql level can be done in this case to improve the overall performance?

All other info about this MySQL server , I have uploaded in pastebin

  • So, what exactly to you need to tune? Unless you have a concrete, measurable goal you cannot start tuning. – mustaccio Dec 31 '18 at 16:06
  • When will you have time to Skype TALK with me? Have your determined if you have SSD or NVME on your server? To many details to cover with one question/one answer in this format. Will need 15 minutes, minimum for 1st session. View my profile, Network profile for contact information and invite me to connect via Skype, please. – Wilson Hauck Dec 31 '18 at 16:35
  • I have invited you to connect with me on Skype, when you have time. Thanks, Wilson Hauck – Wilson Hauck Dec 31 '18 at 19:40
  • Hi Mustaccio, we cant do much about the queries running and also there is no selects running in the system.we want to tune the mysql variables and any server/hardware wide options to make inserts faster.The same nimsoft suite , inserts are faster in SQL Server but comparitively slower in mysql and hence we see transactions getting queued up in front end. – udhayan dharmalingam Jan 1 at 11:38
  • @udhayandharmalingam Please post text results of SHOW CREATE TABLE RN_QOS_DATA_080; and SHOW INDEX FROM RN_QOS_DATA_080; and from Centos command line, text results of filefrag RN_QOS_DATA_080 so we have an extent count for this single table. – Wilson Hauck Jan 2 at 14:20
0

Rate Per Second=RPS Suggestions to consider for your my.cnf [mysqld] section

innodb_io_capacity=5000  # from 2000 to encourage more IOPS
innodb_flushing_avg_loops=5  # from 30 to reduce innodb_buffer_pool_pages_dirty of 119,461
innodb_lru_scan_depth=100  # from 1024 to reduce CPU effort by 90% for this function
read_rnd_buffer_size=196608  # from 256K to reduce Handler_read_rnd_next RPS of 25,974

These are DYNAMIC GLOBAL VARIABLES and you could use SET GLOBAL variable_name=value to avoid stop/start of services. Set ONE each HOUR and monitor error log before moving to the next one, please. If reaction is harmful, go back to current setting for this one variable.

You may use SHOW GLOBAL STATUS LIKE '%dirty%' to monitor your innodb_buffer_pool_pages_dirty and when they are under 100 your performance will be improved and innodb_buffer_pool_reads will have lower RPS. It will take a while to work out 119,461 dirty pages of data.

For additional suggestions, please view my profile, Network profile for contact information and reach me by Skype TALK, please.

  • @udhayandharmalingam Have any of these suggestions been applied? Please indicate outcome of your effort. Response time either BETTER or WORSE. Thanks – Wilson Hauck Jan 23 at 17:05
0

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.

  • Thanks Rick for the detailed post .. Differently named innodb tables with each with multiple partitions ..i have attached a sample in pastebin..i will try to analyze the suggested variables and make some changes and see if it improves the load performance show create - pastebin.com/49XKPB9D show index -pastebin.com/Vknf8Y0k show table status - pastebin.com/Sp7PRSBk – udhayan dharmalingam Jan 2 at 18:55
  • @udhayandharmalingam - I added 'Schema Critique', which has further recommendations. – Rick James Jan 2 at 19:55
  • I applied the changes suggested in this post, but 1 mysterious thing that i observed before and after the change is high mem usage by mysql. So the total memory allocated to innodb buffer pool is 80 GB out of 120 GB RAM,but top command shows continuously 95% usage by mysqld. – udhayan dharmalingam Jan 8 at 11:30
  • Tasks: 622 total,1 running,620 sleeping,1 stopped,0 zombie %Cpu(s): 7.1 us, 0.8 sy,0.0 ni,91.0 id,1.1 wa,0.0 hi,0.0 si,0.0 st KiB Mem :13131188+total,587716 free,12781116+used,2913000 buff/cache KiB Swap:15728636 total,2939604 free,12789032 used.2983408 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 41063 mysql 20 0 0.130t 0.117t 6284 S 316.7 95.6 10434:23 mysqld – udhayan dharmalingam Jan 8 at 11:31
  • top lies. Or at least doesn't give the numbers you expect to see. Have you set O_DIRECT? (SHOW VARIABLES LIKE 'innodb_flush_method';) SHOW PROCESSLIST; is more relevant to mysql than top. Also, see innotop. – Rick James Jan 8 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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