SQLite Forum

Looking for Disk IO performance suggestions
Login

Looking for Disk IO performance suggestions

(1) By tang12y (tg2298) on 2021-06-18 19:28:05 [source]

Hi 

I am new to SQLite3. Our products are running on an embedded system that needs to write the database 7*24h. So the disk I/O throughput(write) is critical for our eMMC devices' lifetime. By monitoring with iotop, We found the system i/o write to the disk is much higher than the actual data file size(20-40 times higher depends on different journal mode). And the "WAL" mode has the smallest I/O write throughput among all kinds of journal modes. I was thought it should be "OFF" mode. Also, it is weird when using journal mode = "off" or "memory", seems the sqlite still trying to write the xxx.db-journal file. 
see following strace result:

......
15:40:41 open("/home/jie/Desktop/AXM-WEB2/sqlite3_test/testDB.db-journal", O_RDWR|O_CREAT|O_CLOEXEC, 0644) = 4
15:40:41 fstat(4, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
15:40:41 geteuid()                      = 1000
15:40:41 fstat(4, {st_mode=S_IFREG|0644, st_size=0, ...}) = 0
15:40:41 lseek(4, 0, SEEK_SET)          = 0
15:40:41 write(4, "\0\0\0\0\0\0\0\0\0\0\0\0\212\315\325\23\0\0\0\36\0\0\2\0\0\0\4\0\0\0\0\0"..., 512) = 512
15:40:41 lseek(4, 512, SEEK_SET)        = 512
15:40:41 write(4, "\0\0\0\10", 4)       = 4
15:40:41 lseek(4, 516, SEEK_SET)        = 516
15:40:41 write(4, "\n\0\0\0\t\3\224\0\3\364\3\350\3\334\3\320\3\304\3\270\3\254\3\240\3\224\0\0\0\0\0\0"..., 1024) = 1024
15:40:41 lseek(4, 1540, SEEK_SET)       = 1540
15:40:41 write(4, "\212\315\325\26", 4) = 4
15:40:41 lseek(4, 1544, SEEK_SET)       = 1544
15:40:41 write(4, "\0\0\0\36", 4)       = 4
15:40:41 lseek(4, 1548, SEEK_SET)       = 1548
15:40:41 write(4, "\r\0\0\0\1\3\37\0\3\37\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 1024) = 1024
15:40:41 lseek(4, 2572, SEEK_SET)       = 2572
15:40:41 write(4, "\212\315\325\32", 4) = 4
......

I wonder is there any other operations or any suggestions for saving some I/O write flow?

Thanks for your help.

(2) By anonymous on 2021-06-18 19:42:07 in reply to 1 [link] [source]

If your record size is quite smaller than the page size then may be check if you can group those inserts into transactions. Potentially saving on partial page waste.

Also note that what the WAL journaling mode saves during the insertion process is paid later during the checkpointing process.