Mysql – Tuning thesql 5.7.1 for predominantly write workload on huge partitioned innodb tables

MySQLperformance

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

Best Answer

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.

Related Question