insert performance issue
I've got an node.js application that needs to log stuff to the mysql database.
I'm using: @mysql/xdevapi@8.0.23, community edition of mysql on a i5 ubuntu box (i5-8400cpu [@2.8Ghz x 6], 32GB of ram)
Currently my insert code looks like this:
// start of stuff for each insert //
let command = dbconn.sql('INSERT INTO cellvalues(field1,field2,field3,field4,field5,field6,field7,field8) VALUES(?,?,?,?,?,?,?,?);');
let args = [value1, value2, value3, value4, value5, value6, value7, value8];
let query = command.bind(args);
if( query && query.execute ) {
this.totalInsertsStarted++;
let self = this;
query.execute().then( () => {
self.totalInsertsFinished++;
}).catch( err=>{console.error("error in insert: ",err)});
// end of stuff done for each insert
What I'm seeing while I run this is that I'm starting about 1584 inserts a second, and I'm only seeing somewhere between 123 and 145 finish a second, so a big backlog builds up very quickly. I'd actually like to do more like 20000 or 30000 inserts a second.
If I was coding this in another language, I'd be using prepared statements, however, the docs for the connector say that prepared statements aren't supported explicitly, and the list of statements done implicitly doesn't include inserts. Factoring the dbconn.sql(sqltext) out of the repeated block of code result in failure.
Is there a better way to code the insert or should I look to writing this part of the code in language where the connector supports prepared statements?
My table definition looks like:
CREATE TABLE `cellvalues` (
`field1` bigint NOT NULL,
`field2` tinyint NOT NULL,
`field3` tinyint NOT NULL,
`field4` tinyint NOT NULL,
`field5` tinyint NOT NULL,
`field6` smallint NOT NULL,
`field7` int NOT NULL,
`field8` tinyint NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Thank you.