Extremly poor insert performance with SQLite3

Question

I use SQLlite3 (exactly 3.9.2 2015-11-02) in my application. For test purposes I have a few tables.

One of them has schema as follows:

CREATE TABLE "Recordings" (
        `PartId`        INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        `CameraId`      INTEGER NOT NULL,
        `StartTime`     INTEGER NOT NULL,
        `EndTime`       INTEGER NOT NULL,
        `FilePath`      TEXT NOT NULL UNIQUE,
        `FileSize`      INTEGER NOT NULL,
        `DeleteLockManual`      INTEGER NOT NULL DEFAULT 0,
        `Event1`    INTEGER NOT NULL,
        `Event2`        INTEGER NOT NULL,
        `Event3`       INTEGER NOT NULL,
        `Event4`  INTEGER NOT NULL,
        `Event5`    INTEGER NOT NULL,
        `Event6`   INTEGER NOT NULL,
        FOREIGN KEY(`CameraId`) REFERENCES Devices ( CameraId )
);
CREATE INDEX `Table_Event2` ON `Table` (`Event2`);
CREATE INDEX `Table_Event3` ON `Table` (`Event3`);
CREATE INDEX `Table_Event4` ON `Table` (`Event4`);
CREATE INDEX `Table_Event5` ON `Table` (`Event5`);
CREATE INDEX `Table_Event6` ON `Table` (`Event6`);
CREATE INDEX `Table_Event1` ON `Table` (`Event1`);
CREATE INDEX `Table_DeleteLockManual` ON `Table` (`DeleteLockManual`);
CREATE INDEX `Table_EndTime` ON `Table` (`EndTime`);

The hardware I'm using is pretty old: Pentium 4 2.4GHZ, 512MB RAM, old 40GB Hard drive.

The Recordings table contains ~60k rows.

When I'm doing INSERT on this table, from time to time (one per 30) query takes extremely long to finish. Last time it took 23 sec (sic!) for a single 1-row INSERT. For the rest times it takes ~120ms.

I dumped stack during this operation:

[<e0a245a8>] jbd2_log_wait_commit+0x88/0xbc [jbd2]
[<e0a258ce>] jbd2_complete_transaction+0x69/0x6d [jbd2]
[<e0ac1e8e>] ext4_sync_file+0x208/0x279 [ext4]
[<c020acb1>] vfs_fsync_range+0x64/0x76
[<c020acd7>] vfs_fsync+0x14/0x16
[<c020acfb>] do_fsync+0x22/0x3f
[<c020aed5>] SyS_fdatasync+0x10/0x12
[<c0499292>] syscall_after_call+0x0/0x4
[<ffffffff>] 0xffffffff

Or:

[<c02b3560>] submit_bio_wait+0x46/0x51
[<c02bb54b>] blkdev_issue_flush+0x41/0x67
[<e0ac1ea8>] ext4_sync_file+0x222/0x279 [ext4]
[<c020acb1>] vfs_fsync_range+0x64/0x76
[<c020acd7>] vfs_fsync+0x14/0x16
[<c020acfb>] do_fsync+0x22/0x3f
[<c020aed5>] SyS_fdatasync+0x10/0x12
[<c0499292>] syscall_after_call+0x0/0x4
[<ffffffff>] 0xffffffff

The application using this database is single-threaded.

What can cause such behavior? Will switching to recent hardware (with ssd) solve this issue?

You can try better hardware. Even with all the indexes, the insert of a single row should be very fast, unless you have triggers or another process is locking the table. — Gordon Linoff, May 17 '16 at 01:49
Read [Improve INSERT-per-second performance of SQLite?](http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite) — nobody, May 17 '16 at 01:59
That is the file system, not the database. Anyway, it's obvious you forgot to use a single transaction for multiple inserts. — CL., May 17 '16 at 08:06
As i said, I'm doing single insert from time to time. Its one per minute or even less — peku33, May 17 '16 at 11:13

Extremly poor insert performance with SQLite3

0 Answers0