0

I have a large dataset of approximately 420 million records and I was able to load them into a temporary table in a timely manner of about 15 minutes using a LOAD DATA INFILE statement. Super awesome. I needed this temporary table to stage the data because I do some cleaning on it before loading it into it's final destination table.

The temporary table is defined as:

CREATE TABLE `temporary_data` (
  `t_id` smallint(10) unsigned NOT NULL,
  `s_name` varchar(512) NOT NULL,
  `record_type` varchar(512) NOT NULL,
  `record_value` varchar(512) NOT NULL
) ENGINE=MyISAM;

The target table that needs this data loaded is called my_data and it is defined as:

CREATE TABLE `my_data` (
  `s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_id` smallint(10) unsigned NOT NULL,
  `s_name` varchar(63) NOT NULL,
  PRIMARY KEY (`s_id`),
  UNIQUE KEY `IDX_MY_DATA_S_NAME_T_ID` (`t_id`,`s_name`) USING BTREE,
  KEY `IDX_MY_DATA_S_NAME` (`s_name`) USING BTREE,
  CONSTRAINT `FK_MY_DATA_MY_PARENT` FOREIGN KEY (`t_id`) REFERENCES `my_parent` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The problem is, the query to load the data from the temporary table into my_data is very slow as I suspected it would be because my_data contains two indexes and a primary key.

So far I have been trying to run variations of this query (thanks to Rick James for his help in optimizing for inserting in PK order and eliminating jumping auto-increment values with the left join check):

set foreign_key_checks = 0;
set sql_log_bin=0;
INSERT INTO my_data (t_id, s_name)
SELECT t_id, s_name
FROM temporary_data
LEFT JOIN my_data AS d
USING (t_id, s_name)
WHERE d.s_id IS NULL
ORDER BY t_id, s_name;
set sql_log_bin=1;
set foreign_key_checks = 1;

I need to determine a way to speed up this query so it completes in a timely manner (under 30 minutes would be ideal) since it will be importing and appending any new records from the 400 million on a daily basis.

I switched from my cloud VPS to just attempting the import on my local box which is more powerful 32 GB of RAM and i7-4770K_CPU_@_3.50GHz CPU but get the following error consistently after about 45 minutes of running. Both the datadir and tmpdir are mapped to a drive that has 1.5 TB of space remaining and the actual data in the temporary_data table is only about 18 GB so the table full error doesn't make sense:

[ERROR] [MY-013132] [Server] The table 'D:\Data\Temp#sql147c_a_2' is full!

Interestingly, while the query is running for 45 minutes before failing, this is the output of the InnoDB status and doesn't look like there is any I/O activity even though it says "sending data" in the process list. The temp data directory also never expands like it did on the VPS server to support the ORDER BY operation.

InnoDB Status:

=====================================
2018-11-22 00:31:53 0x1110 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 28 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 3412 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 119
OS WAIT ARRAY INFO: signal count 108
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 12395
Purge done for trx's n:o < 12298 undo n:o < 0 state: running but idle
History list length 36
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283704833474960, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283704833474080, not started
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283704833473200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283704833472320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1270 OS file reads, 133653 OS file writes, 166 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 6906283, node heap has 0 buffer(s)
Hash table size 6906283, node heap has 1 buffer(s)
Hash table size 6906283, node heap has 4 buffer(s)
Hash table size 6906283, node heap has 0 buffer(s)
Hash table size 6906283, node heap has 2 buffer(s)
Hash table size 6906283, node heap has 1 buffer(s)
Hash table size 6906283, node heap has 0 buffer(s)
Hash table size 6906283, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          38124251
Log buffer assigned up to    38124251
Log buffer completed up to   38124251
Log written up to            38124251
Log flushed up to            38124251
Added dirty pages up to      38124251
Pages flushed up to          38124251
Last checkpoint at           38124251
229 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 28571598848
Dictionary memory allocated 517303
Buffer pool size   1703936
Free buffers       1702590
Database pages     1338
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1196, created 142, written 256
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: 1338, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   212992
Free buffers       212785
Database pages     206
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 205, created 1, written 17
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: 206, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   212992
Free buffers       212806
Database pages     184
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 182, created 2, written 18
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: 184, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   212992
Free buffers       212803
Database pages     189
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 187, created 2, written 17
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: 189, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   212992
Free buffers       212867
Database pages     124
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 123, created 1, written 23
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: 124, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   212992
Free buffers       212966
Database pages     25
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 24, created 1, written 1
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: 25, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   212992
Free buffers       212866
Database pages     125
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 61, created 64, written 64
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: 125, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   212992
Free buffers       212752
Database pages     239
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 173, created 66, written 87
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: 239, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   212992
Free buffers       212745
Database pages     246
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 241, created 5, written 29
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: 246, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5244, Main thread ID=0000000000003824 , state=sleeping
Number of rows inserted 32, updated 346, deleted 0, read 7972
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

my.ini:

[client]

port=3308

[mysql]
no-beep=

[mysqld]
port=3308
datadir=D:/Data/MySQL80
tmpdir=D:/Data/Temp
default_authentication_plugin=caching_sha2_password
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="MYLOG.log"
slow-query-log=1
slow_query_log_file="MYLOG-slow.log"
long_query_time=10
log-error="MYLOG.err"
server-id=1
lower_case_table_names=2
secure-file-priv=""
max_connections=151
table_open_cache=2000
tmp_table_size=57M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=104M
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=1G
innodb_log_file_size=48M
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=4Mo
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
loose_mysqlx_port=33060

Any recommendations on tweaking this to run as fast as possible would be greatly appreciated.

The production box will be a Digital Ocean box. I was planning to start out with just a $5 instance for normal querying operations but I bumped the resources up temporarily to a 16 GB / 6 vCPU machine and tweaked the innodb_buffer_pool_size to 14G to help it along but the VPS never came back from the "sending data" state after 16 hours. The tmpdir would grow up to about ~50 GB at its peak and then fluctuate downward to around a few GB and just hang there indefinitely in the "sending data" state sometimes going back up a few GB.

  • With 420M rows/day, how many rows do you anticipate eventually?? – Rick James Nov 23 '18 at 2:54
  • Most of them will be duplicates. There are around 170K new rows per day. I'm thinking about using a bloom filter to mitigate the majority of duplicate records that would be attempted to be inserted since each record has low business value on its own. – Adam Nov 24 '18 at 3:40
  • Duplicates of each other? In which case, shrinking the staging file is a big benefit. Or Duplicates only of the main table? And, are they complete duplicates? Or do you need to update something in the process? (See IODKU.) – Rick James Nov 24 '18 at 4:13
  • Both. There are duplicates within the file and also within my_data. The challenge is trying to capture just the new records but comparing to the main table seems to be too expensive given how long everything is taking which is why I was considering a persistent bloom filter to help get rid of the vast majority of duplicates. I am currently restructuring my ETL process to only download and load one file at a time (there are over 1,500 of these that add up to make the millions in the the temp table). – Adam Nov 24 '18 at 6:35
  • I thought about restructuring the input file directly so DATA INFILE could ingest it directly into the target table without staging. This sounded attractive at first but there is also another table I populate based on these incoming records and one table in particular is a many-many relationship. I was using the temp table to create the links between the two tables. – Adam Nov 24 '18 at 6:35
1

It is possible to sort a MyISAM table (but not InnoDB). After loading and cleansing temporary_data, do

ALTER TABLE temporary_data ORDER BY t_id, s_name;

Either this will fail the same way, or it may help prevent the other step from failing. Then, remove ORDER BY t_id, s_name from the INSERT..SELECT..

The ALTER sorts the table, obviating the need for the SELECT from doing the sort.

Also, now that you are using a 32GB machine, make some tuning changes:

key_buffer_size=8M    --> 2G
innodb_log_buffer_size=1M  --> 16M
innodb_buffer_pool_size=1G  --> 8G
innodb_log_file_size=48M  --> 256M  **
innodb_buffer_pool_instances=8  -- still 8

** If log_file_size prevents restarting, revert to what you have now.

Will the 400M rows be added once a day?

My next guess will be to break the process into chunks based on ranges of t_id after the Alter. That is, do the Insert..Select with a WHERE t_id BETWEEN .... There is a problem that I don't have a good answer for yet... That means adding an index, but MyISAM indexes are not clustered. Partitioning might be viable. Hmmmm...

  • Hey Rick thanks for those setting tweaks. I was able to make some small traction copying to a second temporary table just to massage the data a little more (delete the duplicates first and then I'll do that in-place sort) and once that completes, I'll try to load it into my_data. This effort, like everything else is incredibly slow. I have about 6 more queries to run against this staging table after I get the data loaded into my_data that I anticipate will be just as expensive. – Adam Nov 23 '18 at 1:22
  • I am cautiously starting to ponder whether a document-oriented approach would be more conducive to my needs. There is low business value per record, most queries I intend to perform within the app will require one main query against my_data containing about 9 JOINS & 7 LEFT JOINS to other tables (which are sparse, 3NF, and only grow as each record in my_data is accessed real-time), I was able to import all my records in just under a half hour when I tested MongoDB. Reporting is also not important. I really like the compactness and less duplication SQL provides though. Food for thought. – Adam Nov 23 '18 at 1:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.