Some context:
I have a simple in-memory database with a single table and an index. Here's the schema for the table.
`CREATE TABLE 'table_name' ('col1' INTEGER, 'col2' INTEGER, 'col3' INTEGER, 'col4' INTEGER, 'col5' STRING, PRIMARY KEY('col1','col2','col3')) WITHOUT ROWID;";`
The index is on one of the integers and the string.
This community helped me improve the insert performance quite a bit already. But I am still not satisfied with the performance of the memory allocator.
My table has 77 million rows and by my calculation has 2 GB of data. But around 80% of the time is spent on malloc/free. So I provided my own allocator functions and found out that SQLite asked my allocator to allocate 650GB of data in total (and of course also to free most of it) which surprised me. [Here's the data] (https://pastebin.com/t9AZQRKh) of allocation sizes and counts for my table.
I also run `sqlite3_db_config(_database, SQLITE_DBCONFIG_LOOKASIDE, 2048, 2048);` before I create the table with the hope that it reduces the necessity for smaller allocations. But it doesn't seem to help much.
Using the default allocator on Windows, it takes more than 20 minutes to populate the data where as using mimalloc, the performance is much better.
No queries or any other SQLite operations are happening during the test.
Here are my questions.
1) Why does SQLite allocate 650 GB for populating a much smaller DB?
2) Is there any way to reduce it? (Except doing all the memory management myself?)
3) `SQLITE_STATUS_MEMORY_USED` indicates that SQLite is using ~6.5 GB of data but my calculations, DB is only 2GB in size. What could explain this discrepancy?
4) I also see that the current value of `SQLITE_STATUS_MEMORY_USED` almost doubles after the last row has been written to the DB. Why could that be?
Regards,
Bhargava Srinarasi