I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this.

Now there is a requirement which involves insertion of very large amount of data, (may be 60-70x of current), that may even involve concurrent insertion of even approx 2000 record. It will obviously slow down our system, and cant be used in production environment. Moreover database size will become an issue.

I need some ideas that will serve my purpose.

  • 1
    Is there any real reason to store such data in a relational database if you do not use any relational "features"? Some "document storage" or even a plain log file might work better if it is only about storing the data.
    – jkavalik
    Sep 18 '15 at 7:41
  • Not forced to use relational storage. Our current system is in relational. We may plan to move it to another, but the problem here with me would be both "time constraint and knowledge". I've fresher level knowledge in database, and my manager will surely give low time to implement it (as with every software developer). So migrating to other storage will be critical. I've already mentioned that its in production environment (Billions of users). Initially I've also thought of similar solution, but implementation is tough and time taking. @jkavalik Sep 18 '15 at 8:25
  • 1
    Then you might want to specify what you want to do with the data - just store them (better in textfile imho), somehow aggregate/analyze, show per user...
    – jkavalik
    Sep 18 '15 at 10:02
  • My log is delimeter seperated string which contains various things. I'll read that string from log, split them from delimiter and index those fields in Solr. @jkavalik Sep 18 '15 at 10:23
  • 1
    Then you will need some way to determine whats "new" (inserted or updated). Would it help you if you split the string before inserting it and creating two tables? Something like log(id, written, processed) and log_item(log_id, string)
    – jkavalik
    Sep 18 '15 at 10:58

Use a "staging table". Insert the unprocessed data into a separate table, then remove it when processed.

How big will the table eventually become? A million row -- no problem. A billion rows -- let's discuss more details.

For really high speed ingestion, see my blog.


I have a few datasets I'm pulling into mysql, the small one has about 1.5 billion records in it (the large one has about 8 billion). The server is some 64 bit virtual machine in our corporate data center. Since it was specked out to run databases and thats all the VM is used for i presume that it has a reasonable ammount of memory and is at least a dual core - more likely a quad core instance. My table basically has 8 columns that are integer columns. Each of the columns has a b-tree index. The average size of the records is 37 bytes. my insert scheme is to use SQL prepaired statements with placeholders. Using this scheme i insert 5,000 records at a time. Experimentation has shown that a somewhat smaller chunk of records in my case is a little bit faster but in the big scheme of things i really dont care that much :)

As a practical matter I am inserting the records in batches of 876,000 records at a time, taking 5,000 of them per sql statment that gets sent to the server. Last night when I started, I took 41,503 mSec - 45 seconds to commit 876K records to the server. 12 hours and half a billion records later its taking about 650,000 Msec - nearly 11 minutes to commit 876K records to the server.

since its just been chugging along since last night, my conclusion is that due to the nature of the data- many fields are in near sorted order, I have hit along a pathological case for a b-tree - turning it into a poorly implemented linear search!

Moral to this story? when injesting LARGE ammounts of data, its probably a good idea - in addition to tuning your server - to turn or drop as many indices as you can and then reindex it later. Having said that, one needs to have a good handle on just how much overhead the indexing has at insert time compared to re-indexing the whole database. In my case where there will be essentially NO updates - just pure reads/queries, doing it on the tail end, makes good sense.

to give you the (d)illusion of useful data: one java program running at once (single threadded access to mysql) 876000 records are read from the data source and written 5,000 records at a time. time is tracked for each 'batch' of 87600.

Insert Performance Y axies is how long it took to insert X axies is the 'batch' number

I'm going to drop the indices and see how that works Stay tuned for another graph

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.