0

I need to remotely insert 3.5 million rows to my amazon rds. Currently my data is in an access database, for which I will use bullzip, access to mysql to create dump files. I have created my schema on the RDS and defined the data types pre-insert (because when you auto upload through the app it defines most cols as varchar(255)).

So far I have used the following methods, when uploading to a test server, whilst building my application.

  1. Direct transfer using bullzip's app. Left it to run overnight once, and 1.7 million rows had inserted after about 10 hours.

  2. Split the file into numerous sql dumps (300k rows each at the most, split by date). Which is also incredibly slow ( over an hour so far to 200k rows)

  3. Export to CSV, and upload through mysql workbench. ( also taking a hell of a long time)

I believe that my issue is that bullzip's application, produces an sql file which performs an insert statement for every row, rather than multi-row insert queries.

Is there a way to produce an sql file, in a multi-row insert format?

Or, do I have to just put up with it, because I'm exporting from access?

And I have read elsewhere that you can only add 50k rows to a multi-value insert. So is what I'm asking even suitable for me?

This is the SHOW CREATE TABLE:

'CREATE TABLE `1_txns` (
  `txn_id` varchar(32) DEFAULT NULL,
  `nationality_id` int(3) DEFAULT NULL,
  `transaction date` int(8) DEFAULT NULL,
  `Yr` int(4) DEFAULT NULL,
  `mth` int(2) DEFAULT NULL,
  `dy` int(2) DEFAULT NULL,
  `hr` int(2) DEFAULT NULL,
  `Min` int(2) DEFAULT NULL,
  `Flight No` varchar(12) DEFAULT NULL,
  `sku` varchar(20) DEFAULT NULL,
  `sales` decimal(8,2) DEFAULT NULL,
  `units` int(8) DEFAULT NULL,
  KEY `txnid` (`txn_id`),
  KEY `skutxnid` (`sku`,`txn_id`),
  KEY `skunatid` (`sku`,`nationality_id`,`txn_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8'
8
  • You are likely to get 10x speedup by multi-row inserts. (1000 rows at a time is enough.) If bullzip can't do it, find another way to do it. – Rick James Dec 23 '15 at 6:48
  • I have not heard of a specific 50K-row limit. There are other limits, such as packet size. That might equate to 50K rows for some particular schema. I recommend chunking 1000 at a time. It will be nearly the max speed possible. – Rick James Dec 23 '15 at 6:50
  • 1
    If bullzip can write a CSV file, do that. Then LOAD DATA will be very fast. – Rick James Dec 23 '15 at 6:51
  • Please provide SHOW CREATE TABLE; there are a lot of inefficiencies that might be fixable at that level. (Extra indexes, bad datatypes, etc) – Rick James Dec 23 '15 at 6:51
  • 1
    net_buffer_length controls the size (in bytes) of the INSERTs generated; you can't control it by row count. Anything between 1M to 16M is probably fine. Read the scanty details in dev.mysql.com/doc/refman/5.6/en/mysqldump.html about that setting. – Rick James Dec 23 '15 at 16:25
0

It would be convenient to diagnose where is the performance bottleneck in this case. Can you monitor the network throughput / CPU usage / disk usage to see which one is higher while uploading?

  • If it's a network bandwidth problem, make sure you're using compression in server/client protocol, any client should have this feature, Mysql Workbench for sure does
  • Most probably it's not related to the fact that the data comes from Access
  • Having your own test Mysql server inside your network would be of use for two things: importing the data there to see if the network is the problem; and importing it there and exporting it from there, forcing a multi-row insert export format
3
  • Compression looks like an interesting subject on the matter. This wasn't given to me as an option at import stage. Is this something I set elsewhere? – Adam Copley Dec 22 '15 at 18:23
  • In any client you set it on/off when connecting (in the host/username/password/schema screen), then all communication will be compressed including imports – golimar Dec 23 '15 at 9:48
  • Sweet found that, and it's definitely quicker. I imagine that with multi-row insert it will be super fast. My only concern is, that if I do a mysqldump, how can I make it so that it only does around 1k rows per insert, rather than all 3.5m for one insert? Surely it's not possible to put 3.5m in one insert statement. ESP over WAN – Adam Copley Dec 23 '15 at 9:55

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.