MySQL Forums
Forum List  »  Performance

Poor insert performance. Is there a problem with my config?
Posted by: Lyle Hopkins
Date: September 23, 2021 04:05PM

Hi,

I'm experiencing very poor insert performance, the bottleneck appears to be MySQL itself.

Server Spec
AMD Threadripper Pro 12 core 24 thread
128GB RAM DDR4 3200
2x FireCude 530 2TB NVMe RAID 0

CPU usage is 0.5%
Memory usage is around 70GB
I've tested copying files to the disk and that's very fast evening during the slow inserts (hundreds or thousands of MB/s).

However, I can see from Task Manager than mysqld's disk activity is only around 30MB/s doing these inserts.

I'm splitting up a big table into smaller ones. I've read that INSERT INTO ... SELECT is slow for lots of data. So I've tested OUTFILE/INFILE with and without indexes on the new table and the performance is still around 30MB/s (that's alongside the INSERT INTO ... SELECT still running for the last 3 days). (BTW, it seems crazy to me to read in other threads so many people saying that OUTFILE/INFILE is so much faster than moving data from one table to another in the database??).

I'm hoping something is up with my config. I tried to run the MySQLTuner-perl script for tips but it just doesn't work on Windows.

Maybe someone here will spot something? My config:

default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="DB.log"
slow-query-log=1
slow_query_log_file="DB-slow.log"
long_query_time=10
log-error="DB.err"
log-bin="DB-bin"
server-id=1
lower_case_table_names=1
max_connections=151

table_open_cache=2000
tmp_table_size=256M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=268M
key_buffer_size=64M
read_buffer_size=64K
read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=256M
innodb_buffer_pool_size=95G
innodb_log_file_size=256M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0

back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
log_slow_admin_statements=1

Options: ReplyQuote


Subject
Views
Written By
Posted
Poor insert performance. Is there a problem with my config?
251
September 23, 2021 04:05PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.