Re: Need to insert 10k records into table per second
Posted by:
Rick James
Date: February 07, 2014 01:19PM
> Brand new install of MySQL 5.6 (And here I thought newer is better)
Yes. MySQL has always had a good record, at least among the "GA" releases. (General Availability)
> InnoDB, only because I guess this is the default. (I'm preeeety new at this haha)
Fine. (Sometimes advice needs to be different for MyISAM.)
> 'innodb_log_buffer_size', '7340032'
That should changed to 70% of _available_ RAM. (Eg, 11G for a dedicated 16GB server.) This change will make a big difference in performance.
> The MAC is the source
But is it a "MAC address", like "ff:ee:dd:cc:bb:aa" ? If so this would be optimal as a string:
CHAR(17) CHARACTER SET ascii -- 17 bytes
but with a little work, this would be much smaller:
BINARY(6) -- 6 bytes
> ports are from 0-65535 so smallint should just be able to cover it
SMALLINT UNSIGNED
> My biggest concern is that I don't know how to tell mysql to use the ramdisk.
I'm trying to convince you that a ramdisk is the wrong approach.
> MySqlCommand(...)
I'm not familiar with the language (VB.NET?) and Class you are using, so, I can't address some of the details.
> cmd.Parameters.Add("@port", MySqlDbType.Int32).Value = Convert.ToInt32(port)
Will that work for UNSIGNED, 16-bit numbers?
Your code snippet shows only one row being inserted at a time. This will limit how fast you can insert data.
> The issue with this logging is that it happens in realtime, at the moment I'm working with text file that was created from a short period but ultimately I'd like to bypass this step to reduce overhead and waste less time by getting the data immediately into the database.
Good. That is _probably_ the best approach.
> this is a huge issue because the data coming in is enormous.
Consider summarizing the data, rather than storing all the details. Will you even need the details? Or will you only want counts, sums, averages, etc, over minutes/hours/etc ?