Tuning mysql 5.7.1 for huge partitioned innodb tables and predominantly write workload

[udhayan D]

Hi Guys,

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

About the application:
1)Its 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


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 couldnt see any select query appearing in slow log for a long_query_time value of 1s.

5)Looking at the IOSTAT ouput , i dont see abnormal qsize or waitime for write operations.

6) innodb_buffer_pool is set to 40 GB but 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 overal performance.

Thanks