CODEWITHSUNDEEP
linuxinsertinnodbperformance-testingmysql-5.7
Rk Singh
Rk Singh

Reputation: 17

MySQL Insert operation are slow on Linux RDS server

please check the workbench output after 20 mints its writing only 100-200 records persecond before that its writing 1211-2000 writes per second I am trying to insert 2 million records on the MySQL 5.7.10 RDS server, Its taking almost 40 mints to insert the data on Linux environment where as the same data are inserted in 28 mints on Windows platform.

On Linux I am using SSD disk still its taking long time to insert.

My hardware configuration is:-

SSD Disk

RAM:- 122 GB

CPU:- 16 Cores

My MySQL configuration is:-

innodb_buffer_pool_size=80 G

innodb_log_file_size= 1G

innodb_log_buffer_size= 64MB

innodb_buffer_pool_instances = 28

tmp_table_size =4G

max_heap_size= 4G

table_open_cache=32262

innodb_file_per_table=ON

innodb_flush_log_at_trx_commit=2

innodb_flush_method=O-DIRECT

Team please check and help on this.

Thanks in advance.

Upvotes: 0

Views: 178

Answers (2)

Rick James
Rick James

Reputation: 142298

tmp_table_size =4G  -- lower to 1G
max_heap_size= 4G   -- lower to 1G
table_open_cache=32262  -- lower to, say, 1000

What filesystem (xfs, ext4, etc)? RAID?

Please show us the insert command(s). Where is the source data coming from (same drive, different machine, etc)?

More

Batch the INSERTs -- but BEGIN and COMMIT around 100-1000 rows at a time.

Upvotes: 1

Saurabh Srivastava
Saurabh Srivastava

Reputation: 1

There is a single line setting that can resolve the issue:

innodb_flush_log_at_trx_commit = 2

Upvotes: 0

Related Questions