2

Using a test script, the average time to complete an insert is a few ms. But about 3% of the time, the insert takes between 0.5 and 3 seconds to complete. If I run the same query 1000 times about 970 finish in under 10ms, while 30 take over 500ms.

I'm running a fairly recent build of Raspbian from a few months ago and SQLite 3.8.4.

The process doing the inserts jumps from about 5% CPU usage to 10% when the slow inserts happen, but otherwise the CPU usage is normal.

How can I find out what's going on here, how would I know if SQLite is waiting on the OS to write, or if it's waiting to acquire a lock, or something else?

Edit: Here is the table schema 

create table n (id INTEGER PRIMARY KEY,f TEXT,l TEXT);

And Here is the query I'm running

insert into n (f,l) values ('john','smith');
Seth Archer Brown
  • 1,141
  • 4
  • 12
  • 27
  • Good reading: http://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite?rq=1 – nobody Aug 21 '14 at 02:36
  • I read that earlier today. PRAGMA synchronous = OFF, speeds up the average time by about 10 times, but there's still a few percent that take over a second. This leads me to believe the problem isn't waiting for the OS to write, but maybe something like the db waiting to acquire a lock? – Seth Archer Brown Aug 21 '14 at 02:39
  • Do you have multiple processes accessing the same database? If you don't, there definitely shouldn't be any locking. – nobody Aug 21 '14 at 02:42
  • No, there aren't multiple processes accessing it. – Seth Archer Brown Aug 21 '14 at 02:46
  • How large is the table? – CL. Aug 21 '14 at 07:12
  • The table is empty when I start the test script, and has 1000 rows at the end. The number of slow downs don't seem to increase with size--they occur with the same frequency at the beginning of the test and the end. – Seth Archer Brown Aug 21 '14 at 08:35
  • Possible duplicate of [valgrind detects memory leak but application works](http://stackoverflow.com/questions/21822832/valgrind-detects-memory-leak-but-application-works) – Paul Sweatte May 15 '17 at 21:50
  • Did you ever find out why this happened? I'm having a very similar issue. – user5806139 Jul 15 '21 at 12:34

0 Answers0