Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql store performance enhancement #1132

Closed
carr123 opened this issue Dec 14, 2020 · 3 comments · Fixed by #1140
Closed

sql store performance enhancement #1132

carr123 opened this issue Dec 14, 2020 · 3 comments · Fixed by #1140

Comments

@carr123
Copy link

carr123 commented Dec 14, 2020

hi, i read the nats-streaming-server sql store souce code. it seems that, a sql transaction is used (begin, preparedStmts, commit) to bulk insert messages.
but , i think this kind of bulk insertion [insert into tb values(1, 'a'),(2,'b'),(3,'c')] is more performant than the above.
@kozlovic
Copy link
Member

I will dig deeper into it. A quick test shows about a 4x gain, which is good!

@carr123
Copy link
Author

carr123 commented Dec 15, 2020

bulk insertion NOT within another transaction seems fastest.
hope it helps.

@carr123 carr123 closed this as completed Dec 15, 2020
@kozlovic
Copy link
Member

@carr123 Understood.

kozlovic added a commit that referenced this issue Jan 8, 2021
@kozlovic
[IMPROVED] Bulk insert of messages
adebcc0 
A new configuration `bulk_insert_limit` switches the server from
the current insertion of messages within a SQL transaction, to
an "INSERT INTO MESSAGES () VALUES (),(),..." which can speed up
performance by several folds.

The server still may perform regular insert within transaction
if the limit is deemed too low.

This new configuration parameter is not enabled by default. It
needs to be explicitly set, either in configuration file or
from command line `--sql_bulk_insert_limit <number here>`.

Resolves #1132

Signed-off-by: Ivan Kozlovic <ivan@synadia.com>
kozlovic added a commit that referenced this issue Jan 8, 2021
@kozlovic
[IMPROVED] Bulk insert of messages
20f9b79 
A new configuration `bulk_insert_limit` switches the server from
the current insertion of messages within a SQL transaction, to
an "INSERT INTO MESSAGES () VALUES (),(),..." which can speed up
performance by several folds.

The server still may perform regular insert within transaction
if the limit is deemed too low.

This new configuration parameter is not enabled by default. It
needs to be explicitly set, either in configuration file or
from command line `--sql_bulk_insert_limit <number here>`.

Resolves #1132

Signed-off-by: Ivan Kozlovic <ivan@synadia.com>
@kozlovic kozlovic mentioned this issue Jan 8, 2021
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[IMPROVED] Bulk insert of messages nats-io/nats-streaming-server
2 participants
@carr123 @kozlovic