Re: Need to insert 10k records into table per second

[Rick James]

10K/sec, even to disk, should be easy to do for a log. But not without a few minor changes.

Please provide the output of SHOW CREATE TABLE; what you provided leaves out some important details.

Are you INSERTing one row at a time?

Are you using MyISAM or InnoDB?

SHOW VARIABLES LIKE 'sync%';
SHOW VARIABLES LIKE 'bin%';
SHOW VARIABLES LIKE 'innodb%';
SHOW VARIABLES LIKE '%buffer%';

Other issues...
* VARCHAR(16) won't work for IPv6. You need (39), or better yet, VARBINARY(16) and a function to pack IPv4/IPv6 into 16 bytes.

* What character set do you need? Just Ascii? utf8?

* What version of MySQL? (Newer versions have fixed some sluggishness on Windows.)

* How much RAM? If your ramdrive does not leave any room for MySQL's caches, then your I/O is swapping. Do _not_ allow swapping.

* Windows likes to play with its hard disk even when it shouldn't; some day they will figure out how to improve that.

* Your log would be much smaller, hence load faster:
MAC varchar(20) -- MAC address? Redundant with IP?
action varchar(40) -- Only a few values? Use ENUM
date datetime -- 8 bytes; switch to TIMESTAMP (4 bytes)
protocol int -- ENUM?
dstIP varchar(16) -- see above
port int -- what is the range of "port"? SMALLINT UNSIGNED (2 bytes instead of 4) may be sufficient.

* It may be more efficient to normalize the MAC+IP pair.