Performance degradation in long-running sessions with Memory engine, possibly related to metadata_dropped directory

[yidigo]

In a long-running chdb session where I repeatedly create, query, and drop ENGINE=Memory tables, I've observed significant performance degradation. A simple query that is very fast at the beginning of the session takes progressively longer to execute as more operations are performed.

Concurrently, I've noticed that the /.chdb_data/metadata_dropped subdirectory is continuously populated with new files, and these files are not automatically cleaned up during the session. I suspect that the increasing number of files in this directory is causing chdb to slow down during certain internal operations, leading to the performance hit.

I have tried executing DROP TABLE to clean up the tables, and while the command succeeds, it does not resolve the performance issue or clean up the files in metadata_dropped.

[yidigo]

the memory is going up

[wudidapaopao]

Hi @yidigo, when a table is dropped, it is not deleted synchronously. Instead, a marker is first created in the metadata_dropped directory, and then background threads periodically clean up the data tables. However, due to chdb's limitation on the number of background threads (unlike ClickHouse, which by default allocates a relatively larger number of background threads), the cleanup process of data tables may slow down under frequent table creation and deletion, leading to the accumulation of numerous files in the metadata_dropped directory.
For reference: https://clickhouse.com/docs/engines/database-engines/atomic#drop-detach-table.

One possible solution is to adopt synchronous table deletion to prevent excessive backlog of deletion tasks in the background. Nevertheless, synchronous deletion will directly block the session until the deletion completes. Therefore, unless support for configuring a greater number of background threads is added in the future, frequent table deletion operations are not highly recommended.

If you experience performance degradation even without frequent table deletion, please feel free to discuss it with us at any time.

[wudidapaopao]

Hi @yidigo, thank you very much for raising the question. I have found some other issues when deleting tables. See #364.

[yidigo]

I didn't delete the tables frequently. I juse use sql to insert date, calculate data or use table engine=memory. Is it related to this?

[wudidapaopao]

This #364 will fix the issue where table deletion does not take effect physically.