MySQL Forums
Forum List  »  Connector/Node.js

insert performance issue
Posted by: Eric Davies
Date: March 30, 2021 01:48PM

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 ) {
let self = this;
query.execute().then( () => {
}).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.

Options: ReplyQuote

Written By
insert performance issue
March 30, 2021 01:48PM
March 31, 2021 05:17AM
March 31, 2021 08:43AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.