Write speed drops by 10x after a few million row inserts unless database is restarted or flushed #25848
Labels
performance
Performance-related questions
Comments
moaazassali
changed the title
|
May I have your "show create database xxxx\G". And the "describe tablename". I need to get your database configuration and table structure. |
|
in the first time the speed went down , could you provide the capture of "top -Hp |
|
It seems that the difference is the vnode-commit threads in the third run. Fourth run had a write speed of 7.3M/s with only one vnode-commit thread with CPU usage of 11.7%. Note that the default frequency of top is 3 seconds, so the numbers aren't very accurate especially since the first two runs are done in like 0.25s. Nonetheless, they show the relevant differences. |
|
@yu285 any updates on this performance issue? |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Performance Issue
Write speed drops down from around 12M/s to 1.2M/s on my server after a few million rows are inserted.
Problem Description
I made a simple benchmark that uses the c# tdengine client library. I send 1000 batches across 1000 devices (1 batch per device) with 3000 rows per batch.
1- First run with 1000 batches (3M rows): write speed = ~12M/s
2- Second run with 1000 batches again: write speed = ~12M/s
3- Third run with 1000 batches: write speed = ~1-1.5M/s
4- Fourth run: ~1-1.5M/s
5- Fifth run: ~1-1.5M/s
6- Sixth run (random increase): ~5M/s
7- Seventh run (back to slow speed): ~1-1.5M/s
8- Eighth run: ~1-1.5M/s
9- Ninth run: ~1-1.5M/s
and so on...
None of the rows were deleted and I just kept adding more rows with new unique timestamps, so just purely new insertions are happening.
I tried up to 20 runs or so, and I also found that very few of them (just like the 6th run) randomly jumped to 3M/s or 5M/s sometimes.
But if I try restarting TDengine or running
flush <db>;after the 3rd run, the write speed goes back up to 12M/s:[3 runs have already happened before restart, so 9M rows already exist]
1- First run after restart: ~12M/s
2- Second run after restart: ~12M/s
3- Third run after restart: ~1-1.5M/s
I have then tried restarting multiple times after the 3rd benchmark run and the results were always the same: first two runs are fast, and third run is slower by 10 times.
Environment (please complete the following information):
Additional Context
This inconsistency with the write speed after inserting a couple million rows doesn't happen with other databases like ClickHouse.
Therefore, we can rule out that the issue is within my benchmark code.
We can also safely rule out that the issue is within the
taos-dotnet-connectorlibrary since the write speed issue is seemingly fixed by restarting/flushing the database after around 2 batches (6M rows) have been insertedI am using parameter binding for insertions (not SQL strings) using
BindRow()function in the c# library to ensure high write speed. Using SQL strings will make it even slower anyways.I am also not running out of RAM. My server's memory usage never exceeded 3GB while running the benchmark.
Some things I already tried
Changing the
DURATIONparameter to 1 day and making sure every batch is only inserting into 1 unique day per device, but that didn't change anything.The text was updated successfully, but these errors were encountered: