Originally, we were operating on a relational database, with our data grouped by value type**.** All data points from all sources of matching value types were mixed together inside these type‑based tables. All our data is timestamped, and we mainly access it by timestamp, so for performance, we thought we’d switch to questdb.
New situation
Our default ttl is 93 days, so if a data source starts writing a lot to a grouped table, even if we switch to a separate table with smaller ttl, that other data will be retained for 93 days. So we split the tables into a table per data source, to make use of TTL properly.
Because of that split, we’re now inserting a few records in lots of tables. We use the QuestDB sender with flush threshold: 50,000 rows and flush interval: 1 second.
Despite low per‑table ingestion rates, the WAL apply process cannot keep up. wal_pending_row_count keeps climbing on many tables, while the CPU stays around 30%.
So, the issue seems to lie in having lots of tables with small writes rather than large batches. But squishing tables back together again wouldn’t work either in case of conflicting TTL.
What is the best solution here? Is it possible to speed up the writing of WAL to table? Or should we simply attempt to reduce our table count as much as possible?
Hi @YBrink , how many tables, and what hardware are you using?
With this kind of setup, you probably need to reduce cairo.writer.data.append.page.size and increase wal.apply.worker.count.
It’s also important to use a fast disk and filesystem, and avoid uising sync mode. By default, the database uses nosync mode, which allows the OS to fsync data at will. This trades durability for performance, so ensure you take frequent snapshots.
Ideally, you would have fewer tables, so perhaps you can explain a bit more about your data and schema, and we can find a good middle ground.
Hi @nwoolmer
About 600 tables (this is our worst case scenario). Our cpu has 4 cores and hyperthreading (Intel I5), the disk questdb is on is an ssd.
Outside of cairo.writer.data.append.page.size and wal.apply.worker.count we haven’t touched the settings, so nosync is on.
Data-wise, we write about 10-30 records every 2 milliseconds, each of which comes from a different datasource. (on more robust systems we’d want that number higher). The current schema is very simple: a timestamp and a value.
We’d prefer having more separate tables, but if needed we can squash things down to 7 or 8 tables per value type (for differing ttls) resulting in about 35-40ish tables.
A 500 Hz txn rate is quite high, are you able to reduce this to perhaps 10 Hz, which should give you a better batch size?
Since you only have single values, you might benefit from using a narrow schema for this kind of ingestion i.e. timestamp, sensor, value, which would allow you to write larger batches to fewer tables. You can re-arrange this data after the fact, copy it to different tables, build materialised views etc.
Sorry, to be clear, the write to questDB occurs on every flush at either 50000 records or the 1 second default. We put new records into the sender every 2 milliseconds, but we don’t flush at that time.
We’re going to apply the schema as you proposed and see if that has effect. Thanks for the help so far!
Text table will slowly tick up, float table rapidly, and the other tables at an intermediate pace.
Assuming this, with the schema of timestamp, value, symbol, symbol for each of these, what settings should I fiddle with now?
I take it the page append size can return back to the default setting?
Please can you share the built-in ingestion metrics charts for the tables? They are located next to the refresh button at the top of the tables window.
Additionally, if you are using newer versions of QuestDB, tables() has additional summary metrics for ingestion. It’d be great if you could run that and then hit the ‘copy as markdown’ button:
So, part of the problem was that, despite having dropped the old 600 or so tables (and them no longer showing in questdb), the log showed the server was still busy handling that deletion.
Specifically: i.q.c.w.WalPurgeJob table is not fully dropped, pinging WAL Apply job to delete table files
After deleting those files manually (multiple gigabytes worth of data), everything started working smoothly! Thanks for al the help
(The other part of the problem was having that many tables in the first place, we pared it down to 20)
Ah yes indeed, if you have very backed up tables, it can take a while to clean them up on drop. In future, you should be able to short circuit this by truncating them first or converting them to non-WAL.
