[SQLite] Use of db:exec cause 5 second delay

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[SQLite] Use of db:exec cause 5 second delay

Gilles Ganault
Hello

I noticed that the "db:exec" part of the following script waits for
about 5s when running on a Linux appliance:
=================
HTTP/1.0 200 OK
Content-Type: text/plain

<?
local db = sqlite3.open('test.db')

-- stalls for about 5s
db:exec[[
  CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:nrows("SELECT * FROM test") do
  mg.write(row.id, row.content,'\n')
end

mg.write("Done")

db:close()
?>
=================

Why is that, and is there something I could try to speed things up?

Thank you.


Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

D. Matt Placek


On Jan 29, 2014 11:41 AM, "Gilles Ganault" <[hidden email]> wrote:
>
> Hello
>
> I noticed that the "db:exec" part of the following script waits for
> about 5s when running on a Linux appliance:

It may be forcing a sync on your disk.  Does it take 5s every time, or is it faster if you run it a second time or run "sync" first before running the script?

Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

Rena
In reply to this post by Gilles Ganault
On Wed, Jan 29, 2014 at 11:41 AM, Gilles Ganault <[hidden email]> wrote:
Hello

I noticed that the "db:exec" part of the following script waits for
about 5s when running on a Linux appliance:
=================
HTTP/1.0 200 OK
Content-Type: text/plain

<?
local db = sqlite3.open('test.db')

-- stalls for about 5s
db:exec[[
  CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:nrows("SELECT * FROM test") do
  mg.write(row.id, row.content,'\n')
end

mg.write("Done")

db:close()
?>
=================

Why is that, and is there something I could try to speed things up?

Thank you.



How do you know it's that particular line that stalls? What if you break it into four separate queries? Is it exactly 5 seconds?

--
Sent from my Game Boy.
Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

David Favro
In reply to this post by Gilles Ganault
On 01/29/2014 11:41 AM, Gilles Ganault wrote:

> I noticed that the "db:exec" part of the following script waits for
> about 5s when running on a Linux appliance:

> -- stalls for about 5s
> db:exec[[
>    CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
>    INSERT INTO test VALUES (NULL, 'Hello World');
>    INSERT INTO test VALUES (NULL, 'Hello Lua');
>    INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
> ]]

Wrap all that into one transaction, like:

db:exec[[
    BEGIN TRANSACTION;
    CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);
    INSERT INTO test VALUES (NULL, 'Hello World');
    INSERT INTO test VALUES (NULL, 'Hello Lua');
    INSERT INTO test VALUES (NULL, 'Hello Sqlite3');
    COMMIT;
]]

SQLite3 by default makes each statement into a separate transaction, and
syncs to physical disk (not filesystem buffers) at each commit.  Still, 5
seconds for 4 statements is a bit extreme, so I'm guessing that your
appliance has slow-write storage, like maybe flash.


Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

D. Matt Placek
On Wed, Jan 29, 2014 at 1:06 PM, David Favro <[hidden email]> wrote:
SQLite3 by default makes each statement into a separate transaction, and syncs to physical disk (not filesystem buffers) at each commit.  Still, 5 seconds for 4 statements is a bit extreme, so I'm guessing that your appliance has slow-write storage, like maybe flash.

It also can depend on the file system you're using; at one point I had horrible delays with sqlite on ext4 where ext3 had been much much faster (on the order of 1s on ext4 vs 100ms on ext3).  There may be file system tuning parameters that can be tweaked to improve sync times.

Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

Gilles Ganault
On Wed, 29 Jan 2014 14:50:24 -0500, "D. Matt Placek"
<[hidden email]> wrote:
>It also can depend on the file system you're using; at one point I had
>horrible delays with sqlite on ext4 where ext3 had been much much faster
>(on the order of 1s on ext4 vs 100ms on ext3).  There may be file system
>tuning parameters that can be tweaked to improve sync times.

Thanks for the infos. When using a transaction, it's done to about 2s.

The whole system lives on an SD card, where the root partition is
indeed formated in ext4:
===================
Disk /dev/mmcblk0: 3957 MB, 3957325824 bytes
4 heads, 16 sectors/track, 120768 cylinders, total 7729152 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000332e9

        Device Boot      Start         End      Blocks   Id  System
/dev/mmcblk0p1   *        2048      440319      219136   83  Linux
/dev/mmcblk0p2          440320     7286783     3423232   83  Linux
/dev/mmcblk0p3         7288830     7727103      219137    5  Extended
/dev/mmcblk0p5         7288832     7727103      219136   82  Linux
swap / Solaris
===================
~# mount
sysfs on /sys type sysfs (rw,nosuid,nodev,noexec,relatime)
proc on /proc type proc (rw,nosuid,nodev,noexec,relatime)
udev on /dev type devtmpfs
(rw,relatime,size=10240k,nr_inodes=63463,mode=755)
devpts on /dev/pts type devpts
(rw,nosuid,noexec,relatime,gid=5,mode=620,ptmxmode=000)
tmpfs on /run type tmpfs
(rw,nosuid,noexec,relatime,size=51516k,mode=755)
/dev/disk/by-uuid/b1767040-9366-43c0-9684-3a8ff83f6547 on / type ext4
(rw,relatime,errors=remount-ro,user_xattr,barrier=1,data=ordered)
tmpfs on /run/lock type tmpfs
(rw,nosuid,nodev,noexec,relatime,size=5120k)
tmpfs on /run/shm type tmpfs
(rw,nosuid,nodev,noexec,relatime,size=146840k)
/dev/mmcblk0p1 on /boot type ext2 (rw,relatime,errors=continue)
===================

I'll see if performance can be further improved.

Thanks all.


Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

Nathan Angelacos
idea:

try using tmps (from your post, /run/shm would work) and see if it
improves.  If it does, then you know its the SD card:

local db = sqlite3.open('/run/shm/test.db')



Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

Gilles Ganault
On Wed, 29 Jan 2014 19:07:24 -0500, Nathan Angelacos
<[hidden email]> wrote:
>try using tmps (from your post, /run/shm would work) and see if it
>improves.  If it does, then you know its the SD card:
>
>local db = sqlite3.open('/run/shm/test.db')

Yes, it runs in a fraction of a second. I'll read up on SD and ext4.

Thanks for the tip.


Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

David Favro
On 01/29/2014 07:42 PM, Gilles Ganault wrote:
> On Wed, 29 Jan 2014 19:07:24 -0500, Nathan Angelacos
> <[hidden email]> wrote:
>> try using tmps (from your post, /run/shm would work) and see if it
>> improves.  If it does, then you know its the SD card:
>>
>> local db = sqlite3.open('/run/shm/test.db')
>
> Yes, it runs in a fraction of a second. I'll read up on SD and ext4.

Ext4 has little or nothing to do with it [1], but sqlite and flash storage
has a lot.

If you need your database to be preserved across power loss, don't put it on
a tmpfs; if you don't, you might want to consider a different database.

As I mentioned before, sqlite syncs to disk after every transaction.
Writing to cheap flash (especially cheap SD cards) is _very_ slow.

Typically, when using sqlite the best idea is to wrap operations that are
all executed at once into transactions (this is often desirable for other
reasons as well, such as atomicity and serializability); you did 4
operations and when you wrapped it you got a reduction by about a factor of
4, etc.  I only showed you the first exec() wrapped in the xaction because
that's what you complained was slow.  You can also put the next SELECT
inside the transaction and you can generate your mg.write()s without delay.
  If the DB is accessed by only one process you may be able to create some
long-running transactions but generally this is not a good idea.

In your case, you can disable the sync of the write-ahead log by executing
this commands after you open your database [2]:
PRAGMA synchronous = OFF;
This should eliminate the delay, at the cost of leaving you open to lost
data if e.g. your appliance loses power after a write.  But that's basically
what you are asking for anyhow: the only way to guarantee that the data is
physically written is to physically write it, and your storage is slow for
writes.

-- David

[1]: the filesystem's metadata journal (which may or may not be present on
either an ext3 or ext4 FS, but is not on ext2) might make the delay slightly
longer, but even if you removed it you would likely not notice the difference.

[2]: http://www.sqlite.org/pragma.html#pragma_synchronous


Reply | Threaded
Open this post in threaded view
|

Re: [SQLite] Use of db:exec cause 5 second delay

Gilles Ganault
On Wed, 29 Jan 2014 20:48:21 -0500, David Favro <[hidden email]>
wrote:

>Ext4 has little or nothing to do with it [1], but sqlite and flash storage
>has a lot.
>
>If you need your database to be preserved across power loss, don't put it on
>a tmpfs; if you don't, you might want to consider a different database.
>
>As I mentioned before, sqlite syncs to disk after every transaction.
>Writing to cheap flash (especially cheap SD cards) is _very_ slow.
>
>Typically, when using sqlite the best idea is to wrap operations that are
>all executed at once into transactions (this is often desirable for other
>reasons as well, such as atomicity and serializability); you did 4
>operations and when you wrapped it you got a reduction by about a factor of
>4, etc.  I only showed you the first exec() wrapped in the xaction because
>that's what you complained was slow.  You can also put the next SELECT
>inside the transaction and you can generate your mg.write()s without delay.
>  If the DB is accessed by only one process you may be able to create some
>long-running transactions but generally this is not a good idea.
>
>In your case, you can disable the sync of the write-ahead log by executing
>this commands after you open your database [2]:
>PRAGMA synchronous = OFF;
>This should eliminate the delay, at the cost of leaving you open to lost
>data if e.g. your appliance loses power after a write.  But that's basically
>what you are asking for anyhow: the only way to guarantee that the data is
>physically written is to physically write it, and your storage is slow for
>writes.
>
>-- David
>
>[1]: the filesystem's metadata journal (which may or may not be present on
>either an ext3 or ext4 FS, but is not on ext2) might make the delay slightly
>longer, but even if you removed it you would likely not notice the difference.
>
>[2]: http://www.sqlite.org/pragma.html#pragma_synchronous

Thanks much David.

More information about ext4:
http://stackoverflow.com/questions/13891920/