MySQL Forums
Forum List  »  Backup

Re: Painfully slow import
Posted by: George Develekos
Date: July 19, 2020 01:00PM

Many thanks again for your time.

It's multi-inserts, not sure how many, looks like in the thousands.

I've restarted the import in order to increase a few buffers at the top of the my.cnf file that I'm attaching before the command you asked for. I must note that the performance drops dramatically when the row-compressed tables start processing. Up until then it's moving very fast.

At the top of the sql file I have this:

set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;

I have also enabled these just *after* the import started, I trust they are in effect and helping speed things up.

SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL query_cache_type = 0;
SET GLOBAL query_cache_size = 0;


my.cnf:

[mysqld]
innodb_log_buffer_size=67108864
net_buffer_length=131072
bulk_insert_buffer_size=67108864
port=3306
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# tmpdir=/home/MYSQLOLDDATA/MYSQL-5.5.23/MYSQL_TMP_FOLDER/
basedir=/usr
# log=/var/log/MYSQL/mysql.log

slow-query-log = 0
# slow-query-log-file = /var/log/MYSQL/slow.log
# long-query-time = 5
# log-queries-not-using-indexes = 1

innodb_file_per_table
innodb_file_format=Barracuda
max_connections=150
max_heap_table_size=264M
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 128M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
key_buffer_size = 256M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
innodb_buffer_pool_size = 2200M

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

[mysqld_safe]
log-error=/var/log/MYSQL/mysql_safe.log
# pid-file=/home/MYSQLOLDDATA/MYSQL-5.5.23/data/mysqld.pid
# socket=/home/MYSQLOLDDATA/MYSQL-5.5.23/data/mysql.sock



--------------------------------------------------------------------
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
200719 19:52:05 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1842 1_second, 1841 sleeps, 184 10_second, 1 background, 1 flush
srv_master_thread log flush and writes: 1852
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3119, signal count 3105
Mutex spin waits 16377, rounds 67163, OS waits 1763
RW-shared spins 210, rounds 6212, OS waits 204
RW-excl spins 1, rounds 34617, OS waits 1149
Spin rounds per wait: 4.10 mutex, 29.58 RW-shared, 34617.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 1532D
Purge done for trx's n:o < 1506C undo n:o < 0
History list length 580
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f7d185eb700, query id 8016 localhost root
show engine innodb status
---TRANSACTION 1532C, ACTIVE 442 sec inserting
mysql tables in use 121, locked 121
3 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1401328
MySQL thread id 2, OS thread handle 0x7f7d1862c700, query id 8014 localhost root update
INSERT INTO `ChargedOffEvent` VALUES (387144697,'',NULL,NULL,1,'\0','939642930',1,642930,'0006434',1562371022222,'Asia/Tashkent',1684.00,0,1,2),(387144769,'',NULL,NULL,1,'\0','935863038',1,863038,'0006434',1562371024671,'Asia/Tashkent',1684.00,0,1,2),(387144770,'',NULL,NULL,1,'\0','942658606',1,658606,'0006434',1562371024714,'Asia/Tashkent',1684.00,0,1,2),(387144788,'',NULL,NULL,1,'\0','943068813',1,68813,'0006434',1562371026312,'Asia/Tashkent',1684.00,0,1,2),(387144789,'',NULL,NULL,1,'\0','935306411',1,306411,'0006434',1562371026893,'Asia/Tashkent',1684.00,0,1,2),(387144790,'',NULL,NULL,1,'\0
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
952 OS file reads, 834583 OS file writes, 40979 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 268.68 writes/s, 10.86 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 17, seg size 19, 1 merges
merged operations:
insert 1, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4565471, node heap has 168 buffer(s)
1823.03 hash searches/s, 1885.59 non-hash searches/s
---
LOG
---
Log sequence number 815368651494
Log flushed up to 815368371177
Last checkpoint at 815364511991
0 pending log writes, 0 pending chkp writes
15418 log i/o's done, 3.71 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2360934400; in additional pool allocated 0
Dictionary memory allocated 1520180
Buffer pool size 140799
Free buffers 0
Database pages 139309
Old database pages 51404
Modified db pages 496
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1551, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 941, created 554395, written 792563
0.00 reads/s, 22.00 creates/s, 259.53 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 139309, unzip_LRU len: 13930
I/O sum[10539]:cur[212], unzip sum[5002]:cur[67]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2648, id 140175290603264, state: sleeping
Number of rows inserted 58618421, updated 0, deleted 0, read 0
1832.45 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
1326
July 17, 2020 03:37AM
516
July 17, 2020 11:36AM
540
July 17, 2020 11:53AM
457
July 17, 2020 12:18PM
471
July 17, 2020 12:26PM
421
July 17, 2020 03:35PM
439
July 19, 2020 07:10AM
419
July 19, 2020 11:57AM
Re: Painfully slow import
448
July 19, 2020 01:00PM
482
July 19, 2020 01:48PM
432
July 19, 2020 02:48PM


Sorry, you can't reply to this topic. It has been closed.

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.