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.