Fwd: How can I make this faster?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: How can I make this faster?

J Decker
(Sent to wrong address to start; forwarded here(?))
---------- Forwarded message ----------
From: J Decker <[hidden email]>
Date: Sun, Aug 13, 2017 at 10:57 PM
Subject: How can I make this faster?
To: General Discussion of SQLite Database <[hidden email]>


So I have this sql script that inserts into a single table, and it is VERY
slow.
it is even slower without journal_mode=WAL

Also,  is a unique constraint also an index?

if I use this in my memory mapped VFS it is MUCH faster (but it doesn't
supply mmap, so it's only using -journal that it creates and deletes a lot).

The following is a snippet of this much longer script
https://drive.google.com/open?id=0B812EYiKwtkkSVBUVWtpYUR4WW8

it's only 1687 lines and about 50% are select statements, I would expect
this to complete in under 1 second.not 22 seconds.

(the command should also be replace into, not insert into; but given the
constraint, I wouldn't think that will matter)
-------

#PRAGMA journal_mode=WAL;;
select tbl_name,sql from sqlite_master where type='table' and name='record';
create table `record` (`soul` char,`field` char,`value` char,`relation`
char,`state` char,CONSTRAINT `record_unique` UNIQUE (`soul`,`field`) ON
CONFLICT REPLACE);
create index if not exists soul_index on record(soul);
create index if not exists soul_field_index on record(soul,field);
select * from Record where soul='db';
select state from Record where soul='db' and field='hello';
insert into Record (soul,field,value,relation,state)
values('db','hello','"world"',NULL,1502689508373);
select state from Record where soul='db' and field='other';
insert into Record (soul,field,value,relation,state)
values('db','other','"test"',NULL,1502689508408);
select state from Record where soul='j6bqj9ec1JUbWzaheTEO' and
field='field';
insert into Record (soul,field,value,relation,state)
values('j6bqj9ec1JUbWzaheTEO','field','"randomkey"',NULL,1502689508436.001);
select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTEO',1502689508466);
select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
select state from Record where soul='j6bqj9fz01aP8aM1rLy7R3U' and
field='field';
insert into Record (soul,field,value,relation,state) values('
j6bqj9fz01aP8aM1rLy7R3U','field','"randomkey"',NULL,1502689508495.002);
select state from Record where soul='db' and field='
j6bqj9fz01aP8aM1rLy7R3U';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9fz01aP8aM1rLy7R3U',NULL,'j6bqj9fz01aP8aM1rLy7R3U',1502689508528);
select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
select state from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi' and
field='field';
insert into Record (soul,field,value,relation,state) values('
j6bqj9ho02jCZpwwI9kz4Vi','field','"randomkey"',NULL,1502689508556.003);
select state from Record where soul='db' and field='
j6bqj9ho02jCZpwwI9kz4Vi';
insert into Record (soul,field,value,relation,state) values('db','
j6bqj9ho02jCZpwwI9kz4Vi',NULL,'j6bqj9ho02jCZpwwI9kz4Vi',1502689508611);
select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
select state from Record where soul='j6bqj9kgu96XPzRp4HfD' and
field='field';
insert into Record (soul,field,value,relation,state)
values('j6bqj9kgu96XPzRp4HfD','field','"randomkey"',NULL,1502689508656.001);
select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
select * from Record where soul='j6bqj9kgu96XPzRp4HfD';

----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How can I make this faster?

J Decker
A note; I was testing this script with the lastest sqlite3.exe

sqlite3 test.db < sql.log

the selects are really fast; the inserts are REALLY slow.

My own code I use get_autocommit but never set_autocommit(if there is such
a thing)... otherwise I'm using sqlite3_open_v2; sqlite3_prepare_v2, step,
and finalize.... so pretty vanilla; would expect sqlite3 to not be much
smarter; and it performs just as badly as my own code.

On Sun, Aug 13, 2017 at 11:00 PM, J Decker <[hidden email]> wrote:

> (Sent to wrong address to start; forwarded here(?))
>
> ---------- Forwarded message ----------
> From: J Decker <[hidden email]>
> Date: Sun, Aug 13, 2017 at 10:57 PM
> Subject: How can I make this faster?
> To: General Discussion of SQLite Database <[hidden email]>
>
>
> So I have this sql script that inserts into a single table, and it is VERY
> slow.
> it is even slower without journal_mode=WAL
>
> Also,  is a unique constraint also an index?
>
> if I use this in my memory mapped VFS it is MUCH faster (but it doesn't
> supply mmap, so it's only using -journal that it creates and deletes a lot).
>
> The following is a snippet of this much longer script
> https://drive.google.com/open?id=0B812EYiKwtkkSVBUVWtpYUR4WW8
>
> it's only 1687 lines and about 50% are select statements, I would expect
> this to complete in under 1 second.not 22 seconds.
>
> (the command should also be replace into, not insert into; but given the
> constraint, I wouldn't think that will matter)
> -------
>
> #PRAGMA journal_mode=WAL;;
> select tbl_name,sql from sqlite_master where type='table' and
> name='record';
> create table `record` (`soul` char,`field` char,`value` char,`relation`
> char,`state` char,CONSTRAINT `record_unique` UNIQUE (`soul`,`field`) ON
> CONFLICT REPLACE);
> create index if not exists soul_index on record(soul);
> create index if not exists soul_field_index on record(soul,field);
> select * from Record where soul='db';
> select state from Record where soul='db' and field='hello';
> insert into Record (soul,field,value,relation,state)
> values('db','hello','"world"',NULL,1502689508373);
> select state from Record where soul='db' and field='other';
> insert into Record (soul,field,value,relation,state)
> values('db','other','"test"',NULL,1502689508408);
> select state from Record where soul='j6bqj9ec1JUbWzaheTEO' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9ec1JUbWzaheTEO','field','"randomkey"',NULL,150
> 2689508436.001);
> select state from Record where soul='db' and field='j6bqj9ec1JUbWzaheTEO';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9ec1JUbWzaheTEO',NULL,'j6bqj9ec1JUbWzaheTE
> O',1502689508466);
> select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
> select state from Record where soul='j6bqj9fz01aP8aM1rLy7R3U' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9fz01aP8aM1rLy7R3U','field','"randomkey"',NULL,
> 1502689508495.002);
> select state from Record where soul='db' and field='j6bqj9fz01aP8aM1rLy7R3U
> ';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9fz01aP8aM1rLy7R3U',NULL,'j6bqj9fz01aP8aM1rLy7R3U',15026
> 89508528);
> select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
> select state from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9ho02jCZpwwI9kz4Vi','field','"randomkey"',NULL,
> 1502689508556.003);
> select state from Record where soul='db' and field='j6bqj9ho02jCZpwwI9kz4Vi
> ';
> insert into Record (soul,field,value,relation,state)
> values('db','j6bqj9ho02jCZpwwI9kz4Vi',NULL,'j6bqj9ho02jCZpwwI9kz4Vi',15026
> 89508611);
> select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
> select state from Record where soul='j6bqj9kgu96XPzRp4HfD' and
> field='field';
> insert into Record (soul,field,value,relation,state)
> values('j6bqj9kgu96XPzRp4HfD','field','"randomkey"',NULL,150
> 2689508656.001);
> select * from Record where soul='j6bqj9ec1JUbWzaheTEO';
> select * from Record where soul='j6bqj9fz01aP8aM1rLy7R3U';
> select * from Record where soul='j6bqj9ho02jCZpwwI9kz4Vi';
> select * from Record where soul='j6bqj9kgu96XPzRp4HfD';
>
> ----
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Clemens Ladisch
In reply to this post by J Decker
J Decker wrote:
> So I have this sql script that inserts into a single table, and it is VERY
> slow.

Wrap everything into a single transaction.
<http://www.sqlite.org/faq.html#q19>


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Simon Slavin-3


On 14 Aug 2017, at 7:52am, Clemens Ladisch <[hidden email]> wrote:

> Wrap everything into a single transaction.
> <http://www.sqlite.org/faq.html#q19>

and CREATE the INDEXes after doing the INSERTs:

BEGIN
    CREATE TABLE …
    INSERT …
    CREATE INDEX …
COMMIT

BEGIN
    SELECT …
COMMIT

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Simon Slavin-3
In reply to this post by J Decker


On 14 Aug 2017, at 7:00am, J Decker <[hidden email]> wrote:

> Also,  is a unique constraint also an index?

Your thinking is correct.  SQLite creates an index for internal use when you declare that something must be UNIQUE.  SQLite is usually clever enough to use this index for other purposes when it would speed things up.  So there’s no point in declaring your own identical index.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

J Decker
In reply to this post by Clemens Ladisch
On Sun, Aug 13, 2017 at 11:52 PM, Clemens Ladisch <[hidden email]>
wrote:

> J Decker wrote:
> > So I have this sql script that inserts into a single table, and it is
> VERY
> > slow.
>
> Wrap everything into a single transaction.
> <http://www.sqlite.org/faq.html#q19>
>

I'd have to wrap each one in a transaction.
Each is really an atomic operation from a higher level program... and each
must complete before returning to the parent.


>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Simon Slavin-3


On 14 Aug 2017, at 8:33am, J Decker <[hidden email]> wrote:

> I'd have to wrap each one in a transaction.
> Each is really an atomic operation from a higher level program... and each
> must complete before returning to the parent.

There may be no point in doing that.  If you do not declare a transaction yourself using "BEGIN" then SQLite automatically wraps each command in its own transaction.  The point Clemens was making is that it is not the INSERT which is taking the time, it is finishing the transaction.

Are other processes trying to access the database while the parent thinks up the next INSERT ?  If you have just one process accessing the database then it is safe to leave a transaction open for any amount of time while you supply more commands.  You can then COMMIT an hour or a day later.

On the other hand if, in real life, you have one process supplying new INSERT commands and another doing SELECT at the same time, you should not do this.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

R Smith
In reply to this post by Simon Slavin-3

On 2017/08/14 9:29 AM, Simon Slavin wrote:

>
> On 14 Aug 2017, at 7:52am, Clemens Ladisch <[hidden email]> wrote:
>
>> Wrap everything into a single transaction.
>> <http://www.sqlite.org/faq.html#q19>
> and CREATE the INDEXes after doing the INSERTs:
>
> BEGIN
>      CREATE TABLE …
>      INSERT …
>      CREATE INDEX …
> COMMIT
>
> BEGIN
>      SELECT …
> COMMIT

This may not be the best solution for the OP who does many SELECTs in
between the INSERTs, so those may become slow if not not supported by a
good Index. It might well be not all the Indices are necessary from the
start though, and, given the time he mentions, the selects might not
affect the total time by much.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

J Decker
In reply to this post by Simon Slavin-3
On Mon, Aug 14, 2017 at 12:40 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 14 Aug 2017, at 8:33am, J Decker <[hidden email]> wrote:
>
> > I'd have to wrap each one in a transaction.
> > Each is really an atomic operation from a higher level program... and
> each
> > must complete before returning to the parent.
>
> There may be no point in doing that.  If you do not declare a transaction
> yourself using "BEGIN" then SQLite automatically wraps each command in its
> own transaction.  The point Clemens was making is that it is not the INSERT
> which is taking the time, it is finishing the transaction.
>
> Are other processes trying to access the database while the parent thinks
> up the next INSERT ?  If you have just one process accessing the database
> then it is safe to leave a transaction open for any amount of time while
> you supply more commands.  You can then COMMIT an hour or a day later.
>
> On the other hand if, in real life, you have one process supplying new
> INSERT commands and another doing SELECT at the same time, you should not
> do this.
>
>
The process is linear, single threaded, and the log of commands is in
sequence. (auto commit/auto wal_checkpoint would be a separate thread more
later)

(to catch several messages...)
1) the create table and indexes are created before any select/insert... as
is shown from the attached clip of the script. (Oh I see, there is a select
on sqlite_master to see if the table already exists, and what the
definition of that is in case I need to update the definition)

2) since the beginning is auto transacted, not much point in the create
table/indexes being in a transaction... besides in reality there is at
least half second (and sometimes 9 or 10 seconds) or so between init/create
and the first command.

I do have the ability to auto-transact; so if the connection is idle; on
the first command, generate a begin, and after X time of idle (each new
command bumps the tick to its time), generate a commit.  This generally
works but 1 in 10000 the process ends up doing a new command at the same
time I'm trying to do a commit; and they are one separate threads.... and
yes criticalsections/semaphores separte the threads, but still there's a
collision I'd love to avoid.

I monitored the process with ProcMon (I'm on windows 7 BTW)

12:55:34.7416316 AM node.exe 9012 WriteFile
\Device\HarddiskVolume17\javascript\gun.db\gun.db-wal SUCCESS Offset:
188,416, Length: 20,480, I/O Flags: Non-cached, Paging I/O, Synchronous
Paging I/O, Priority: Normal

this is the last in a block of writes that goes to -wal (which I thought
was memory mapped anyway and shouldn't even see writes to the file).  It's
got the extra flags of non-caches, paging, synchrous etc... and takes the
significant portion of the time.

I see now I actually have to do something like
PRAGMA mmap_size=16777216
(doesn't help)

and the other developer had issued this... PRAGMA synchronous = 0 , seeing
this now in conjunction with the ProcMon output I see why. (this helps a
LOT)



This is curious... locking at 1G of filespace?  the db after 375k records
is only 24M.

1:21:26.5793095 AM node.exe 1204 LockFile
\Device\HarddiskVolume17\javascript\gun.db\gun.db SUCCESS Exclusive: True,
Offset: 1,073,741,824, Length: 1, Fail Immediately: True

but now I also see why it's so much faster in my vfs even just using DELETE
journal method (PERSIST is better though)

Guess I should not do a journal_mode=WAL by default anymore... curious
though, now that I never say it should be WAL (so the databsae isn't marked
as WAL) it tries to delete -WAL

1:45:09.9147387 AM node.exe 13796 QueryOpen
C:\general\work\javascript\gun.db\gun.db-wal FAST IO DISALLOWED
1:45:09.9147710 AM node.exe 13796 CreateFile
C:\general\work\javascript\gun.db\gun.db-wal REPARSE Desired Access: Read
Attributes, Disposition: Open, Options: Open Reparse Point, Attributes:
n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult:
<unknown>
1:45:09.9148148 AM node.exe 13796 QueryOpen
\Device\HarddiskVolume17\javascript\gun.db\GUN.DB-WAL NAME NOT FOUND

---------------
There doesn't appear to be a way to disable locking?  In this usage, the
database is used by Javascript from Node.exe which is single threaded.
There are no other processes that will access the database at any time
while the process is running, it's(sqlite) just a persistance layer that
provides indexing of values for partial updates of simple fields.  There's
a simpler driver option which is just a dump of JSON into a file; but any
change requires dumping the whole graph.

https://www.sqlite.org/lockingv3.html


opt.client.do( "PRAGMA mmap_size=16777216" );
opt.client.do( "PRAGMA journal_mode=OFF" );
opt.client.do("PRAGMA synchronous = 0");

On the filesystem that gets me 0.4k inserts/sec (and this is file accesses,
not memory mapped, still not sure how to actually get memmory mapping to
work)
I guess WAL and non synchronous (and no transaction begin) gets me 1.0k
inserts/sec.

On my VFS the best case is about 1.5k inserts/sec. (kinda the limit of the
application  err no, I guess that's 2.7k )

(another note, the database never in WAL mode, with journal_mode=off,
continually attempts to delete -journal and -wal... I know... cleanup; but
the documentation clearly states that a WAL database will continue to be
WAL without specifying a mode, and implies that setting any other mode will
not stop it from being WAL... so if it's NOT WAL, there should be no reason
to delete -WAL)


(I didn't want to have to push the extra vfs option)




> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

J Decker
In reply to this post by R Smith
On Mon, Aug 14, 2017 at 2:11 AM, R Smith <[hidden email]> wrote:

>
> On 2017/08/14 9:29 AM, Simon Slavin wrote:
>
>>
>> On 14 Aug 2017, at 7:52am, Clemens Ladisch <[hidden email]> wrote:
>>
>> Wrap everything into a single transaction.
>>> <http://www.sqlite.org/faq.html#q19>
>>>
>> and CREATE the INDEXes after doing the INSERTs:
>>
>> BEGIN
>>      CREATE TABLE …
>>      INSERT …
>>      CREATE INDEX …
>> COMMIT
>>
>> BEGIN
>>      SELECT …
>> COMMIT
>>
>
> This may not be the best solution for the OP who does many SELECTs in
> between the INSERTs, so those may become slow if not not supported by a
> good Index. It might well be not all the Indices are necessary from the
> start though, and, given the time he mentions, the selects might not affect
> the total time by much.
>
> The selects are like in nano-time might as well be 0.  It's uhh 'wicked
fast!' .


>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Rowan Worth-2
In reply to this post by J Decker
On 14 August 2017 at 17:11, J Decker <[hidden email]> wrote:

> I monitored the process with ProcMon (I'm on windows 7 BTW)
>
> 12:55:34.7416316 AM node.exe 9012 WriteFile
> \Device\HarddiskVolume17\javascript\gun.db\gun.db-wal SUCCESS Offset:
> 188,416, Length: 20,480, I/O Flags: Non-cached, Paging I/O, Synchronous
> Paging I/O, Priority: Normal
>
> this is the last in a block of writes that goes to -wal (which I thought
> was memory mapped anyway and shouldn't even see writes to the file).  It's
> got the extra flags of non-caches, paging, synchrous etc... and takes the
> significant portion of the time.
>

AFAIK sqlite only ever uses mmap for read paths, never for writing.
Anyway, durability is not free. The synchronous write you are seeing is
part of sqlite's protocol to ensure robustness even across a machine crash
or power loss event. You can play with pragmas etc. but turning off
synchronous is a surefire way to end up with a corrupted DB if one of those
occurs.

The balanced strategy is to minimise the number of synchronous writes, by
minimising the number of transactions as you've been advised. Where you
draw the line depends on how much data you're willing to lose.


> This is curious... locking at 1G of filespace?  the db after 375k records
> is only 24M.
>

That's allowed, and completely normal for sqlite.


> There doesn't appear to be a way to disable locking?  In this usage, the
> database is used by Javascript from Node.exe which is single threaded.
> There are no other processes that will access the database at any time
> while the process is running, it's(sqlite) just a persistance layer that
> provides indexing of values for partial updates of simple fields.


"PRAGMA locking_mode = EXCLUSIVE" will eliminate most locks (there'll just
be a few at the start and end).

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

J Decker
On Mon, Aug 14, 2017 at 2:34 AM, Rowan Worth <[hidden email]> wrote:

> On 14 August 2017 at 17:11, J Decker <[hidden email]> wrote:
>
> > I monitored the process with ProcMon (I'm on windows 7 BTW)
> >
> > 12:55:34.7416316 AM node.exe 9012 WriteFile
> > \Device\HarddiskVolume17\javascript\gun.db\gun.db-wal SUCCESS Offset:
> > 188,416, Length: 20,480, I/O Flags: Non-cached, Paging I/O, Synchronous
> > Paging I/O, Priority: Normal
> >
> > this is the last in a block of writes that goes to -wal (which I thought
> > was memory mapped anyway and shouldn't even see writes to the file).
> It's
> > got the extra flags of non-caches, paging, synchrous etc... and takes the
> > significant portion of the time.
> >
>
> AFAIK sqlite only ever uses mmap for read paths, never for writing.
> Anyway, durability is not free. The synchronous write you are seeing is
> part of sqlite's protocol to ensure robustness even across a machine crash
> or power loss event. You can play with pragmas etc. but turning off
> synchronous is a surefire way to end up with a corrupted DB if one of those
> occurs.
>
> The balanced strategy is to minimise the number of synchronous writes, by
> minimising the number of transactions as you've been advised. Where you
> draw the line depends on how much data you're willing to lose.
>
>
> > This is curious... locking at 1G of filespace?  the db after 375k records
> > is only 24M.
> >
>
> That's allowed, and completely normal for sqlite.
>
>
> > There doesn't appear to be a way to disable locking?  In this usage, the
> > database is used by Javascript from Node.exe which is single threaded.
> > There are no other processes that will access the database at any time
> > while the process is running, it's(sqlite) just a persistance layer that
> > provides indexing of values for partial updates of simple fields.
>
>
> "PRAGMA locking_mode = EXCLUSIVE" will eliminate most locks (there'll just
> be a few at the start and end).
>

https://sqlite.org/pragma.html#pragma_locking_mode

Well that does make it go nice and fast, but it's not exactly the same as
never locking.  A second database connection to the same file is not
allowed.

Even though the process will always be single threaded access even if there
are multiple connections, it would be more helpful to have a
locking_mode=NONE.

Performance wise I go from 1.4-1.5k per second transactions to 0.9-1.05k
transactions per second.
approximately  ( .714ms to 1.11ms  per transaction which is a difference of
 .397ms per transaction... which is -50% of the speed)

And the lock is a logical lock within sqlite.  Against my VFS, which
supports no locking, it still locks a second connection.

I did do some more reading on https://sqlite.org/compile.html which has
SQLITE_ENABLE_LOCKING_STYLE; which after looking at the source to see how
it's used, I guess I should be able to set SQLITE_ENABLE_LOCKING_STYLE=0
 at compile time.    But that doesn't help.  EXCLUSIVE still locks other
connections; and non exclusive is 33% slower (takes 50% longer)

And it still generates locks.... (very strange... went looking to see if it
was actually built with the flag... it's set in the project, enabled
preprocess to a file, and    "ENABLE_LOCKING_STYLE="
CTIMEOPT_VAL(SQLITE_ENABLE_LOCKING_STYLE),
  is not in the output so it must be off)

these are on though...
#line 17519 "..\\src\\sqlite3.c"

  "DEFAULT_SYNCHRONOUS=" "2",
#line 17522 "..\\src\\sqlite3.c"

  "DEFAULT_WAL_SYNCHRONOUS=" "2",

the windows vfs doesn't have options to disable locking, so it always calls
WinLock. here.

static int pagerLockDb(Pager *pPager, int eLock){
    rc = pPager->noLock ? SQLITE_OK : sqlite3OsLock(pPager->fd, eLock);


(And then I looked at that in more depth) Ahh... there's a noLock option
there, maybe....
Okay: opening with

file:gun.db?nolock=1&mode=rwc   ( rwc might not be required, I didn't have
that option on the open initially it was just open_readwrite but no
open_create, not having open_create prevented rwc from working though, and
now that I have that as part of the open I don't have to specify it...) )

but there's still locks not on the journal file...


There's opens of -wal (which is not and was not enabled; again that status
is recorded in the database according to docs)

 QueryStandardInformationFile
\Device\HarddiskVolume17\javascript\gun-db\gun.db
 QueryOpen C:\general\work\javascript\gun-db\gun.db-wal FAST IO DISALLOWED
 CreateFile C:\general\work\javascript\gun-db\gun.db-wal REPARSE
 QueryOpen \Device\HarddiskVolume17\javascript\gun-db\GUN.DB-WAL NAME NOT
FOUND


there's the occasional lock on gun.db... followed immediately by an unlock
(looks pretty no-op to me )

 LockFile \Device\HarddiskVolume17\javascript\gun-db\gun.db SUCCESS
 UnlockFileSingle \Device\HarddiskVolume17\javascript\gun-db\gun.db SUCCESS

With exclusive enbled, neither of the prior operations happen.....

--------------------
So, to summarize.

SQLITE_ENABLE_LOCKING_STYLE=0   does nothing. (no improvement)
file:gun.db?nolock=1  does nothing(no improvement)

PRAGMA locking_mode = EXCLUSIVE  gives me good speed, at the cost of only 1
connection ever.  (not usable)

*sigh* so much work making URI work; testing that locking styule actually
compiled in... resulting in a whole lot of nothing.


> -Rowan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: How can I make this faster?

Rowan Worth-2
On 24 August 2017 at 13:58, J Decker <[hidden email]> wrote:

> Even though the process will always be single threaded access even if there
> are multiple connections, it would be more helpful to have a
> locking_mode=NONE.
>
> <snip>
>
> PRAGMA locking_mode = EXCLUSIVE  gives me good speed, at the cost of only 1
> connection ever.  (not usable)
>

You cannot provide consistency for more than one active connection without
locking, because file system writes are not atomic. ie. during COMMIT there
is a window where if another process was allowed to read the DB it would
see garbage and report "corrupt database" rather than consistent results.
I'm not very familiar with WAL mode but I think the critical locking stage
there is checkpointing rather than COMMIT.

If you're not happy with the INSERT speed, batch your writes.
-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users