Importing data into Mysql slowly



  • This file is 23GB in size, it was created with Dump from the workbench

    It starts well, but when it reaches 1/3 of the recorded data, the import starts to be extremely slow.

    What do you suggest to improve the performance of these queries?

    results: very slow , can someone help me?

    import using:

    mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS = 0; \
        SET UNIQUE_CHECKS = 0" -v  \
        2>./logdeimport.txt \
        -u usrimport -p \
        --default-character-set=utf8 Products_in \
        < Prd_001.sql  
    

    variables:

    key_buffer_size                    8 MB |
    query_cache_size                   0 MB |
    innodb_buffer_pool_size            49152  MB |
    innodb_additional_mem_pool_size    0  MB |
    innodb_log_buffer_size             6144  MB |
    BASE MEMORY                        55304  MB |
    sort_buffer_size                   0.250 MB |
    read_buffer_size                   0.125 MB |
    read_rnd_buffer_size               0.250 MB |
    join_buffer_size                   0.250 MB |
    thread_stack                       0.273 MB |
    binlog_cache_size                  0.031 MB |
    tmp_table_size                     16  MB |
    MEMORY PER CONNECTION              17.180 MB |
    Max_used_connections               5 |
    max_connections                    151 |
    TOTAL (MIN)                        55389.898 MB |
    TOTAL (MAX)                        57898.133 MB |
    
    SHOW ENGINE INNODB status \G;
    *************************** 1. row ***************************
      Type: InnoDB
      Name:
    Status:
    =====================================
    2022-02-23 11:59:21 0x7f5571487700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 11 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 13799 srv_active, 0 srv_shutdown, 243 srv_idle
    srv_master_thread log flush and writes: 0
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 115564
    OS WAIT ARRAY INFO: signal count 113111
    RW-shared spins 21, rounds 22, OS waits 1
    RW-excl spins 17036, rounds 510461, OS waits 17030
    RW-sx spins 537, rounds 16063, OS waits 534
    Spin rounds per wait: 1.05 RW-shared, 29.96 RW-excl, 29.91 RW-sx
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 675545
    Purge done for trx's n:o < 674572 undo n:o < 0 state: running but idle
    History list length 29
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 421501552888496, not started
    0 lock struct(s), heap size 1136, 0 row lock(s)
    ---TRANSACTION 675544, ACTIVE 44 sec inserting
    mysql tables in use 1, locked 1
    1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 6835
    MySQL thread id 76, OS thread handle 140004949817088, query id 7577 localhost root update
    INSERT INTO `saldoclientecca` VALUES ('2021-08-06 00:00:00',369,23,12701133,2,1,6,'93222',840.4300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,3,7760333,2,1,13,'93470',1445.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,3,7760333,2,1,6,'93470',1445.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,51,3719333,2,1,13,'102634',61.3400,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,46,12700588,2,1,13,'93182',833.3300,'2021-08-07 08:37:44','2021-08-07 00:00:00'),('2021-08-06 00:00:00',369,5
    --------
    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] , aio writes: [35, 0, 0, 0] ,
     ibuf aio reads:, log i/o's:, sync i/o's:
    Pending flushes (fsync) log: 0; buffer pool: 3
    13922 OS file reads, 33040400 OS file writes, 786283 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 254.89 writes/s, 8.91 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 3093, seg size 3095, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 12750011, node heap has 13 buffer(s)
    Hash table size 12750011, node heap has 11 buffer(s)
    Hash table size 12750011, node heap has 17 buffer(s)
    Hash table size 12750011, node heap has 7 buffer(s)
    Hash table size 12750011, node heap has 37779 buffer(s)
    Hash table size 12750011, node heap has 1 buffer(s)
    Hash table size 12750011, node heap has 13 buffer(s)
    Hash table size 12750011, node heap has 163 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number          372567137685
    Log buffer assigned up to    372567137685
    Log buffer completed up to   372567137685
    Log written up to            372567137685
    Log flushed up to            372567137685
    Added dirty pages up to      372567137685
    Pages flushed up to          372486561542
    Last checkpoint at           372486561542
    12690136 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 52747567104
    Dictionary memory allocated 60770569
    Buffer pool size   3145728
    Free buffers       2426262
    Database pages     681462
    Old database pages 250574
    Modified db pages  117734
    Pending reads      0
    Pending writes: LRU 0, flush list 37, single page 0
    Pages made young 56588, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 13897, created 667568, written 19834618
    0.00 reads/s, 0.00 creates/s, 254.98 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 681462, unzip_LRU len: 0
    I/O sum[577104]:cur[5472], unzip sum[0]:cur[0]
    ----------------------
    INDIVIDUAL BUFFER POOL INFO
    ----------------------
    ---BUFFER POOL 0
    Buffer pool size   65536
    Free buffers       50583
    Database pages     14161
    Old database pages 5207
    Modified db pages  1822
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 1848, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 279, created 13882, written 439188
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 14161, unzip_LRU len: 0
    I/O sum[12023]:cur[114], unzip sum[0]:cur[0]
    ---BUFFER POOL 1
    Buffer pool size   65536
    Free buffers       50694
    Database pages     14049
    Old database pages 5166
    Modified db pages  2463
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 544, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 339, created 13710, written 387091
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 14049, unzip_LRU len: 0
    I/O sum[12023]:cur[114], unzip sum[0]:cur[0]
    ---BUFFER POOL 2 a´te o POLL 47 é a mesma resposta (ocultei por limitação de carater) 
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Process ID=4782, Main thread ID=139972405339904 , state=checking free log space
    Number of rows inserted 64106963, updated 1578, deleted 52, read 197358
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    
    CREATE TABLE `Prd_001` (
      `DTSLD` datetime NOT NULL,
      `INSNAME` int(10) NOT NULL,
      `IDUNIDADEINST` int(10) NOT NULL,
      `IDCLI` int(10) NOT NULL,
      `IDPRO` smallint(5) NOT NULL,
      `IDMODAl` smallint(5) NOT NULL,
      `CODTIPOSALDO` smallint(5) NOT NULL,
      `IDENTNEG` varchar(50) NOT NULL,
      `TOTALSLO` decimal(19,4) NOT NULL,
      `DTHRR` datetime DEFAULT NULL,
      `DTCRA` datetime DEFAULT NULL,
      KEY `idx_IDCLIENTE` (`IDCLI`),
      KEY `idx_DTSLD` (`DTSMNY`),
      KEY `idx_CODTIPOSALDO` (`CODTPSLDO`),
      KEY `idx_TEMP` (`IDCLI`,`DTSMNY`,`CODTPSLDO`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    


    1. CREATE TABLE without those indexes.
    2. LOAD DATA
    3. ALTER TABLE .. ADD INDEX ...



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2