"SQL error: out of memory" when insert data to table.

[pornpol]

System return "SQL error: out of memory" when inserts data into a table that has more than (~ 250 rows)

Insert into pqmeter_data (meter_id, exid, w, wh, pf, varh, i0, i1, i2, v0, v1, v2,thdvr, thdvs, thdvt, thdir, thdis, thdit, vunbr, vunbs, vunbt, chr3, chs3, cht3, chr5, chs5, cht5, chr7, chs7, cht7, chr9, chs9, cht9, chr11, chs11, cht11, chr13, chs13, cht13, chr15, chs15, cht15, freq, mdt) values ('1', '125', '0.0000', '0.0000', '0.0000', '0.0000', '0.00', '0.00', '0.00', '224.71', '112.61', '112.38', '1.41', '1.71', '1.68', '0.00', '0.00', '0.00', '49.91', '24.88', '25.03', '0.50', '0.84', '0.85', '1.02', '1.25', '1.23', '0.37', '0.23', '0.24', '0.60', '0.48', '0.46', '0.27', '0.27', '0.26', '0.23', '0.32', '0.31', '0.00', '0.00', '0.00', '49.96', '1552917357') SQL error: out of memory

System return "SQL error: out of memory" when inserts data into a table that has more than (~ 5000 rows) Insert into sensor_data (sensor_id, exid, a0, a1, a2, a3, a4, a5, a6, a7, sdt) values ('1', '1021', '-12.492', '0', '0', '0', '0', '0', '0', '0', '1552918218') SQL error: out of memory

"SELECT" statement still works fine in those tables. And "INSERT" into other tables still work too.

Please suggest what I can do.

Thank you.

[siara-cc]

You could do a few things:

• Your sketch may be using up available RAM. Reduce RAM consumption
• Reduce page size of the database: PRAGMA page_size=512; VACUUM; from sqlite3 command line tool
• See if your stack size is 8k instead of 2k. Refer https://github.com/espressif/arduino-esp32/pull/583

[pornpol]

You could do a few things:

• Your sketch may be using up available RAM. Reduce RAM consumption
• Reduce page size of the database: PRAGMA page_size=512; VACUUM; from sqlite3 command line tool
• See if your stack size is 8k instead of 2k. Refer espressif/arduino-esp32#583

1. My Task is run smoothly until size of table increase to the value as i mention above.
2. I have check Page size was 512.
3. I have check Stack size was 8k.

[siara-cc]

The page cache size is set to 64k. So at any time, please ensure more than 64k free RAM is available.

[siara-cc]

I used a program to simulate this problem. If all memory is available for Sqlite3 library, I could insert 5000 records. If I block memory, then it is giving "out of memory" error.

Which means it goes "out of memory" when trying to allocate cache. How much memory can you keep free? Please see what values you get for following code:

   Serial.printf("\nHeap size: %d\n", ESP.getHeapSize());
   Serial.printf("Free Heap: %d\n", esp_get_free_heap_size());
   Serial.printf("Min Free Heap: %d\n", esp_get_minimum_free_heap_size());
   Serial.printf("Max Alloc Heap: %d\n", ESP.getMaxAllocHeap());

[pornpol]

I used a program to simulate this problem. If all memory is available for Sqlite3 library, I could insert 5000 records. If I block memory, then it is giving "out of memory" error.

Which means it goes "out of memory" when trying to allocate cache. How much memory can you keep free? Please see what values you get for following code:

   Serial.printf("\nHeap size: %d\n", ESP.getHeapSize());
   Serial.printf("Free Heap: %d\n", esp_get_free_heap_size());
   Serial.printf("Min Free Heap: %d\n", esp_get_minimum_free_heap_size());
   Serial.printf("Max Alloc Heap: %d\n", ESP.getMaxAllocHeap());

When I try to delete some task to free more memory then I can insert more row. This means if I want to insert more row, I need more free memory right?

xTaskCreatePinnedToCore( TaskBlink , "TaskBlink" // A name just for humans , 1024 // This stack size can be checked & adjusted by reading the Stack Highwater , NULL , 1 // Priority, with 3 (configMAX_PRIORITIES - 1) being the highest, and 0 being the lowest. , NULL , ARDUINO_RUNNING_CORE);

xTaskCreatePinnedToCore(TaskRTC, "TaskRTC", 1024, NULL, 3, NULL, ARDUINO_RUNNING_CORE);

// xTaskCreatePinnedToCore(TaskWebServer, "TaskWebServer", 6000, NULL, 1, NULL, ARDUINO_RUNNING_CORE);

if(upload != 0) xTaskCreatePinnedToCore(TaskUpload, "TaskUpload", 4096, NULL, 1, NULL , ARDUINO_RUNNING_CORE);

if(logger_type == 1 || logger_type == 2) xTaskCreatePinnedToCore(TaskMeter, "TaskMeter", 6000, NULL, 2, NULL, ARDUINO_RUNNING_CORE);

if(logger_type == 3) xTaskCreatePinnedToCore(TaskADC, "TaskADC", 4096, NULL, 2, NULL, ARDUINO_RUNNING_CORE);

// if(auto_delete == 1) // xTaskCreatePinnedToCore(TaskAutoDelete, "TaskAutoDelete", 6144, NULL, 2, NULL, ARDUINO_RUNNING_CORE);

Now I add code as yours mention above to check free memory. I will feedback ASAP.

Heap size: 265324 Free Heap: 109292 Min Free Heap: 20168 Max Alloc Heap: 104572

[pornpol]

Read Meter Sucessfully Insert into pqmeter_data (meter_id, exid, w, wh, pf, varh, i0, i1, i2, v0, v1, v2,thdvr, thdvs, thdvt, thdir, thdis, thdit, vunbr, vunbs, vunbt, chr3, chs3, cht3, chr5, chs5, cht5, chr7, chs7, cht7, chr9, chs9, cht9, chr11, chs11, cht11, chr13, chs13, cht13, chr15, chs15, cht15, freq, mdt) values ('1', '125', '0.0000', '0.0000', '0.0000', '0.0000', '0.00', '0.00', '0.00', '227.91', '114.12', '113.90', '1.32', '1.54', '1.52', '0.00', '0.00', '0.00', '49.96', '24.91', '25.04', '0.47', '0.77', '0.78', '0.65', '0.82', '0.80', '0.45', '0.34', '0.34', '0.59', '0.49', '0.48', '0.57', '0.60', '0.59', '0.27', '0.39', '0.37', '0.00', '0.00', '0.00', '50.03', '1553087645')

Heap size: 261260 Free Heap: 90292 Min Free Heap: 3092 Max Alloc Heap: 46132

Read Meter Sucessfully Insert into pqmeter_data (meter_id, exid, w, wh, pf, varh, i0, i1, i2, v0, v1, v2,thdvr, thdvs, thdvt, thdir, thdis, thdit, vunbr, vunbs, vunbt, chr3, chs3, cht3, chr5, chs5, cht5, chr7, chs7, cht7, chr9, chs9, cht9, chr11, chs11, cht11, chr13, chs13, cht13, chr15, chs15, cht15, freq, mdt) values ('1', '125', '0.0000', '0.0000', '0.0000', '0.0000', '0.00', '0.00', '0.00', '227.73', '114.02', '113.83', '1.27', '1.48', '1.45', '0.00', '0.00', '0.00', '49.96', '24.92', '25.05', '0.44', '0.72', '0.74', '0.55', '0.72', '0.70', '0.47', '0.36', '0.35', '0.56', '0.45', '0.42', '0.59', '0.61', '0.60', '0.26', '0.38', '0.37', '0.00', '0.00', '0.00', '50.02', '1553087650') SQL error: out of memory SQL error

Heap size: 261260 Free Heap: 90292 Min Free Heap: 1900 Max Alloc Heap: 46132

Look like Insert statement "out of memory" occur when Min free Heap < ~2048 ??

    openDb("/sd/enres.db", &db1);
    rc = db_exec(db1, sql.c_str());
    if (rc != SQLITE_OK) Serial.println("SQL error");
    sqlite3_close(db1);

[tobozo]

what if you allocate a higher stack value to your db task ?

xTaskCreatePinnedToCore(BlahTask, "BlahTask", 16384, NULL, 2, NULL, ARDUINO_RUNNING_CORE);

[unangwarsodi]

how much Min Free Heap can insert data, I also experience the same problem (out of memory)?

Heap size: 237904 Free Heap: 26748 Min Free Heap: 732 Max Alloc Heap: 28320

[tobozo]

Free heap is the key, not min free heap.

I used to have this error when free heap was under 64k but after doing some tests I suspect it's more 64K + the index or cache size of the opened tables.

Heap size: 379992 Free Heap: 84584 Min Free Heap: 82284 Max Alloc Heap: 70472 Can't open database: out of memory

[edit] if your ESP32 has psram, there's a way to push this limit away

[unangwarsodi]

how to fix them?

[pornpol]

what if you allocate a higher stack value to your db task ?

xTaskCreatePinnedToCore(BlahTask, "BlahTask", 16384, NULL, 2, NULL, ARDUINO_RUNNING_CORE);

I declare "db" in global scope. I can insert data when reduce stack size T_T

[pornpol]

Free heap is the key, not min free heap.

I used to have this error when free heap was under 64k but after doing some tests I suspect it's more 64K + the index or cache size of the opened tables.

Heap size: 379992 Free Heap: 84584 Min Free Heap: 82284 Max Alloc Heap: 70472 Can't open database: out of memory

[edit] if your ESP32 has psram, there's a way to push this limit away

My Free Heap alway more than 64k But "SQL out of memory" when min free heap than ~2k

SQL error: out of memory

Heap size: 261716
Free Heap: 92544
Min Free Heap: 936
Max Alloc Heap: 91656

[siara-cc]

From all these, it looks like 64k page cache for Sqlite is too much on ESP32. I will reduce it to 32k and inform you shortly. I don't expect too much of performance degradation.

It can be further reduced if page_size can be reduced from 4k.

[unangwarsodi]

please inform me if it has been fixed, I will wait for the next update. thank you for the help so far I'm really waiting for the next update

[pornpol]

From all these, it looks like 64k page cache for Sqlite is too much on ESP32. I will reduce it to 32k and inform you shortly. I don't expect too much of performance degradation.

It can be further reduced if page_size can be reduced from 4k.

this is my DB pragmas

[siara-cc]

I have just pushed after reducing page cache to 32k. But this still does not seem to solve the problem. I tried on 4k page size. It eats up 7k for open and 9k for create statement. It looks like it will take more time to solve this problem.

I have pushed it so it might make a difference for 512 page size.

[siara-cc]

The problem turns out to be because of heap fragmentation when memory is low. We can only improve it - by using prepared statement with ? for insert instead of using sqlite3_exec().

I have added sqlite_bulk_insert example to simulate the problem.

For using it, first it asks for how much heap to block. You can enter say 20 (20*4096 bytes) and see the effect on free memory..

Then it asks how many records to insert.. enter 100 or 200 or 1000 to see how it affects free memory..

if it is successful, it shows how many records in the table and shows free memory again..

Based on this you can fine tune your application - make sure an amount of memory is free so SQLite does not go out of memory for insert.

[siara-cc]

For understanding what is heap fragmentation, please refer to HeapMetric example under ESP8266 Arduino core

[siara-cc]

So far I have been able to insert 20000 records successfully with blocking 20*4096 bytes

[tobozo]

To make the test perform closer to the conditions I have in my own app, I've started at 50, I could go up to 56, but the more entries I added, the lower the available heap when I needed to insert a new entry.

I am not bothered by this behaviour at all, however if you need someone to test further changes, I'm your man :-)

Callback function called: count(*) = 13364

(...)

Heap size: 380936
Free Heap: 327192
Min Free Heap: 320780
Max Alloc Heap: 113792
Enter No. of 4k heap to block:
48

Heap size: 380168
Free Heap: 129816
Min Free Heap: 127516
Max Alloc Heap: 70472
Opened database successfully
Enter No. of records to insert:
100
.......SQL error: out of memory

[siara-cc]

@tobozo I think thats probably because you are using psram.. I think your app should work even without psram, if change to parameterized prepared statements..

[tobozo]

nope, when using psram all those problems go away, this is why I'm not bothered by this :-)

[siara-cc]

@tobozo that means people like me who don't have board with psram cannot use your app :-)

[tobozo]

my app works fine with or without psram, I'm only using the test code you provided to help narrow the situation to a more controllable state.

[unangwarsodi]

how create mountpoint for micro sd?, I use a different micro sd library

[siara-cc]

@unangwarsodi ESP32 supports a native VFS for micro sd and this library reuses it. If you are using a different library, then I am afraid you will have to modify the library code to achieve it.

[siara-cc]

@pornpol @tobozo I have made some major changes to the SQLite-ESP32 interface.. Earlier it was based on NodeMCU's implementation. Now I have taken sample given by SQLite as basis.

There is some slight improvement in memory requirement. It still needs around 80k of RAM to work. But the major reason for changing it is to have to journal on disk so that recovery is possible. So far I have checked it works fine. Please let me know if you face any issues.

[tobozo]

@siara-cc thanks for that, I've updated my local copy of the library and flashed my device with the ble-collector, leaving 132576 bytes free ( ~ 700 bytes less than the previous version).

I'll post a stability update at your request (monday or tuesday ?).

[siara-cc]

Thank you @tobozo. I agree 700 bytes improvement is very less :-). When I tested with the bulk insert example, after the db is closed, the Max heap jumps back to original, unlike the previous one. So worst case one can close and re-open the db in low mem situations.

This gives more control, so I will try some more tweaks to memory once the stability is established. For instance, changing SQLITE_ESP32VFS_BUFFERSZ to 4096 in esp32.cpp will free 4k instantly.

[tobozo]

so after more than 3 days of uptime, the 2 bytes per hour 'leak' stopped; the heap is now at 132412 and stable, no crash watsoever \o/

[siara-cc]

Thanks @tobozo .. I will release it and see how to further reduce memory consumption.

[rtek1000]

Hello, great work!

I wonder if it would be possible to adjust the parameters that use a lot of RAM, to try to run this project on other uC such as STM32F103C8T6 with only 20 Kbytes of RAM.

I am accessing 8MB Flash memory (W25Q64) with FAT file system, and wish I could change data on specific lines of a log file, and the possibility of using a database instead of a plain text file it's perfect!

Thank you!

[siara-cc]

Hi, Thank you for the feedback. I am working on the RAM issue and hope to soon come up with something that covers the STM Blue pill and more. I understand the need for database, but wonder what use case requires you to change the lines of a log file?

[rtek1000]

Hello, In my case, due to the tag change, to work around this, I think of using fixed length. but when searching the internet I found cases where logging is treated as a to-do list, which needs to be removed from the list when it is done. Thank you!

[siara-cc]

See new library https://github.com/siara-cc/sqlite_micro_logger_arduino. It overcomes all memory problems and corruption issues. However there are limitations. For documentation see: https://github.com/siara-cc/sqlite_micro_logger_c.

[Assppex]

See new library https://github.com/siara-cc/sqlite_micro_logger_arduino.

It overcomes all memory problems and corruption issues.

However there are limitations.

For documentation see: https://github.com/siara-cc/sqlite_micro_logger_c.

Hello, I am sorry for asking so late, but I will really appreciate if you answer this, so I am trying to use your library in esp-IDF and facing the same problem, even if my free heap is more 64k, I observe decrease of the minimum free heap size, this happens after several insert statements, also I tried to make test project to ensure that the problem is particularly in insert statement.So the question is: how to prevent this memory fragmentation or do I need PSRAM?