disable file locking mechanism over the network

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

disable file locking mechanism over the network

Roman Fleysher
( somewhat related to Re: [sqlite] Safe to use SQLite over a sketchy network?)

Dear SQLiters,

I am using SQLite over GPFS distributed file system. I was told it honestly implements file locking. I never experienced corruption. But it is slow in the sense that when many jobs from many compute nodes try to access the same database things slow down considerably.

I suspect, from the point of view of file system, there is lots of pressure to develop fast grabbing of a lock and slow release. I think this is because the key to fast network file system in general is making it as independent as possible, thus distributed. Avoid bottlenecks. But locking is by definition a bottleneck. On purpose. I think code requiring file locking is a sign of the code not intended for concurrent access from multiple compute nodes.

SQLite uses file locking to ensure data integrity. This is fine for imbedded systems.

We use SQLite over 100 compute nodes, not as intended.

To try to speed up locking I combined SQLite with FLoM, distributed file lock manager. It is client/server application.

From experience, it seems that because SQLite still requests file locks, the performance increase is not that big. I wonder if there is a way to disable SQLite's internal file locking mechanism. I know this seems strange to ask. But FLoM should be able to do it faster over many compute nodes.

Or, perhaps the right way is for me to combine SQLIte with simple client/server code to create light mySQL, mySQLite?

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Simon Slavin [[hidden email]]
Sent: Wednesday, September 25, 2019 12:58 AM
To: SQLite mailing list
Subject: Re: [sqlite] Safe to use SQLite over a sketchy network?

When I first learned the SQLite had problems with Network File Systems I read a ton of stuff to learn why there doesn't seem to be a Network File Systems that implements locking properly. I ended up with …

A) It slows access a lot. Even with clever hashing to check for collisions it takes time to figure out whether your range is already locked.

B) Different use-cases have different preferences for retry-and-timeout times. It's one more thing for admins to configure and many admins don't get it right.

C) It's hard to debug. There are numerous different orders in which different clients can lock and unlock ranges. You have to run a random simulator to try them all. The logic to deal with them properly is not as simple as you'd think. Consider, for example, ranges which are not identical but do overlap.

D) It's mostly a waste of time. Most client software doesn't care how to deal with a BUSY status and either crashes – which annoys the admin and user – or retries immediately – which makes the management CPU hot. After all, most client software just wants to read a whole file or write a whole file. And if two people save the same word processing document at nearly the same time, who's to say who was first ?

Still, I wonder why someone working on a Linux network file system, or APFS, or ZFS, hasn't done it.
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f95ef2f5d454df697b808d74174fdc3%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637049843046630883&sdata=1jUmQCBsPi6VOToz%2Fx75E%2Fi9VLR%2Flj3Wbx6um5aAXnk%3D&reserved=0
_______________________________________________
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: disable file locking mechanism over the network

Simon Slavin-3
On 27 Sep 2019, at 11:59pm, Roman Fleysher <[hidden email]> wrote:

> From experience, it seems that because SQLite still requests file locks, the performance increase is not that big. I wonder if there is a way to disable SQLite's internal file locking mechanism. I know this seems strange to ask. But FLoM should be able to do it faster over many compute nodes.

What busy_timeout did you set ?

Do you process multiple operations inside a transaction, or do you let SQLite create its own transactions automatically ?

SQLite always locks the entire database.  It does not implement table or row locking.

SQLite has two (main) journalling modes.  The journalling made influences which operations lock out other operations.  The biggest change in lockouts occurs whether you do or do not have writes from multiple connections at once.  You might want to try executing this command just once:

    PRAGMA journal_mode = WAL

This makes a change to the database so that all connections which open it know it's in WAL mode.  You don't have to change your software.  It might speed things up.  (You can do

    PRAGMA journal_mode = DELETE

to set it back.)

There are other other things you can do to disable various parts of the locking system, but you should try the above first.  You might want to play around with

<https://sqlite.org/pragma.html#pragma_synchronous>

Refs:

<https://sqlite.org/pragma.html#pragma_journal_mode>
<https://sqlite.org/wal.html>
_______________________________________________
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: disable file locking mechanism over the network

Keith Medcalf
In reply to this post by Roman Fleysher

On Friday, 27 September, 2019 17:00, Roman Fleysher wrote:

>I am using SQLite over GPFS distributed file system. I was told it
>honestly implements file locking. I never experienced corruption. But it
>is slow in the sense that when many jobs from many compute nodes try to
>access the same database things slow down considerably.

Yes.  This is how "correct" locking would be implemented.  If would be so slow as to be virtually unusable.

>I suspect, from the point of view of file system, there is lots of
>pressure to develop fast grabbing of a lock and slow release. I think
>this is because the key to fast network file system in general is making
>it as independent as possible, thus distributed.

No, it is because the filesystem server must be queried on every lock operation, and before that can return to the caller, each and every client must be contacted and "made consistent".  This means that as more clients "open" the same file, the overhead of each operation becomes orders of magnitude greater.  In order to "get around" this issue most network filesystems use so-called "optimistic" locking protocols.  This basically means that the software prays and goes ahead anyway without ensuring consistency amongst clients and simply corrupts the file in cases where the prayer does not work.  For the normal case this usually works adequately.  For in-place record updates, however, it usually does not.

>To try to speed up locking I combined SQLite with FLoM, distributed file
>lock manager. It is client/server application.

I don't think FLoM does what you think it does.

>From experience, it seems that because SQLite still requests file locks,
>the performance increase is not that big. I wonder if there is a way to
>disable SQLite's internal file locking mechanism. I know this seems
>strange to ask. But FLoM should be able to do it faster over many compute
>nodes.

>Or, perhaps the right way is for me to combine SQLIte with simple
>client/server code to create light mySQL, mySQLite?

Yes, this is the correct way to do it.  If multi-access to a single shared file worked properly then there would never have been any need to invent client/server database architecture.  The fact that is was invented at the same time as shared filesystems indicates that the KISS solution (a shared filesystem) was not robust.



_______________________________________________
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: disable file locking mechanism over the network

Roman Fleysher
In reply to this post by Simon Slavin-3
The timeout is set to 50min !

The database is used to synchronize / manage tasks. A kind of job manager. Thus each access is a quick search with small update or delete.

I looked at the WALL mode. I do not exactly understand how it works, but SQLite developers warn not to use WALL over network in item 2 of the link you mention.

I was not aware of the various synch option. I will study them.

Thank you,

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Simon Slavin [[hidden email]]
Sent: Friday, September 27, 2019 7:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On 27 Sep 2019, at 11:59pm, Roman Fleysher <[hidden email]> wrote:

> From experience, it seems that because SQLite still requests file locks, the performance increase is not that big. I wonder if there is a way to disable SQLite's internal file locking mechanism. I know this seems strange to ask. But FLoM should be able to do it faster over many compute nodes.

What busy_timeout did you set ?

Do you process multiple operations inside a transaction, or do you let SQLite create its own transactions automatically ?

SQLite always locks the entire database.  It does not implement table or row locking.

SQLite has two (main) journalling modes.  The journalling made influences which operations lock out other operations.  The biggest change in lockouts occurs whether you do or do not have writes from multiple connections at once.  You might want to try executing this command just once:

    PRAGMA journal_mode = WAL

This makes a change to the database so that all connections which open it know it's in WAL mode.  You don't have to change your software.  It might speed things up.  (You can do

    PRAGMA journal_mode = DELETE

to set it back.)

There are other other things you can do to disable various parts of the locking system, but you should try the above first.  You might want to play around with

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fpragma.html%23pragma_synchronous&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=a%2FTb%2Fr7kdca1Q4%2F1Le1azbaXR0jdmkDKm042RRMgNYM%3D&amp;reserved=0>

Refs:

<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fpragma.html%23pragma_journal_mode&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=MuvGgmedEKY7QBfx303DUcH4iWkpnRL1D3ADV51LeEg%3D&amp;reserved=0>
<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fwal.html&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=svGLhgL5mQkQfQ%2B7UwsKzaJ4krn8%2F%2FgnHTm3LG3UEPA%3D&amp;reserved=0>
_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=MV%2FomDHw0jFta70BSSxaKIzUP3mSqhdx%2BH3WY64UCZo%3D&amp;reserved=0
_______________________________________________
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: disable file locking mechanism over the network

Roman Fleysher
In reply to this post by Keith Medcalf
I agree that overhead might grow exponentially with number of nodes trying to access the database. But then I do not understand why FLoM can not provide solution. It can enforce sequential access thus reducing the overhead.

And yes, I was trying the KISS approach: rely on GPFS to do the synchronization instead of me programming client/server.

Thank you,

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Keith Medcalf [[hidden email]]
Sent: Friday, September 27, 2019 11:12 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On Friday, 27 September, 2019 17:00, Roman Fleysher wrote:

>I am using SQLite over GPFS distributed file system. I was told it
>honestly implements file locking. I never experienced corruption. But it
>is slow in the sense that when many jobs from many compute nodes try to
>access the same database things slow down considerably.

Yes.  This is how "correct" locking would be implemented.  If would be so slow as to be virtually unusable.

>I suspect, from the point of view of file system, there is lots of
>pressure to develop fast grabbing of a lock and slow release. I think
>this is because the key to fast network file system in general is making
>it as independent as possible, thus distributed.

No, it is because the filesystem server must be queried on every lock operation, and before that can return to the caller, each and every client must be contacted and "made consistent".  This means that as more clients "open" the same file, the overhead of each operation becomes orders of magnitude greater.  In order to "get around" this issue most network filesystems use so-called "optimistic" locking protocols.  This basically means that the software prays and goes ahead anyway without ensuring consistency amongst clients and simply corrupts the file in cases where the prayer does not work.  For the normal case this usually workthe link you sent in item 2,s adequately.  For in-place record updates, however, it usually does not.

>To try to speed up locking I combined SQLite with FLoM, distributed file
>lock manager. It is client/server application.the link you sent in item 2,

I don't think FLoM does what you think it does.

>From experience, it seems that because SQLite still requests file locks,
>the performance increase is not that big. I wonder if there is a way to
>disable SQLite's internal file locking mechanism. I know this seems
>strange to ask. But FLoM should be able to do it faster over many compute
>nodes.

>Or, perhaps the right way is for me to combine SQLIte with simple
>client/server code to create light mySQL, mySQLite?

Yes, this is the correct way to do it.  If multi-access to a single shared file worked properly then there would never have been any need to invent client/server database architecture.  The fact that is was invented at the same time as shared filesystems indicates that the KISS solution (a shared filesystem) was not robust.



_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca315faff6fdc45dfc70408d743c1a8b0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052371361975178&amp;sdata=dhrR%2FbKGqPEmk6sLv3JGouW%2BdO%2BaAK2WyP97LLYQlCU%3D&amp;reserved=0
_______________________________________________
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: disable file locking mechanism over the network

Keith Medcalf
In reply to this post by Roman Fleysher

Yeah, WAL mode cannot work over a remote connection as the WAL index is a shared memory file -- which can only be accessed by processes on a single computer.

If you are using FLoM to co-ordinate your "application" use of SQLite3, then maybe you want to look at using the dotfile VFS which uses dotfiles to co-ordinate multi-access rather than file locks.

https://sqlite.org/search?s=d&q=dotfile

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Roman Fleysher
>Sent: Friday, 27 September, 2019 21:43
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>The timeout is set to 50min !
>
>The database is used to synchronize / manage tasks. A kind of job
>manager. Thus each access is a quick search with small update or delete.
>
>I looked at the WALL mode. I do not exactly understand how it works, but
>SQLite developers warn not to use WALL over network in item 2 of the link
>you mention.
>
>I was not aware of the various synch option. I will study them.
>
>Thank you,
>
>Roman
>
>________________________________________
>From: sqlite-users [[hidden email]] on
>behalf of Simon Slavin [[hidden email]]
>Sent: Friday, September 27, 2019 7:53 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>On 27 Sep 2019, at 11:59pm, Roman Fleysher
><[hidden email]> wrote:
>
>> From experience, it seems that because SQLite still requests file
>locks, the performance increase is not that big. I wonder if there is a
>way to disable SQLite's internal file locking mechanism. I know this
>seems strange to ask. But FLoM should be able to do it faster over many
>compute nodes.
>
>What busy_timeout did you set ?
>
>Do you process multiple operations inside a transaction, or do you let
>SQLite create its own transactions automatically ?
>
>SQLite always locks the entire database.  It does not implement table or
>row locking.
>
>SQLite has two (main) journalling modes.  The journalling made influences
>which operations lock out other operations.  The biggest change in
>lockouts occurs whether you do or do not have writes from multiple
>connections at once.  You might want to try executing this command just
>once:
>
>    PRAGMA journal_mode = WAL
>
>This makes a change to the database so that all connections which open it
>know it's in WAL mode.  You don't have to change your software.  It might
>speed things up.  (You can do
>
>    PRAGMA journal_mode = DELETE
>
>to set it back.)
>
>There are other other things you can do to disable various parts of the
>locking system, but you should try the above first.  You might want to
>play around with
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_synchronous&amp;data=02%7C01%7Croman.fleysher
>%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079
>934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=a%2FTb%2Fr7kdca1Q4
>%2F1Le1azbaXR0jdmkDKm042RRMgNYM%3D&amp;reserved=0>
>
>Refs:
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_journal_mode&amp;data=02%7C01%7Croman.fleyshe
>r%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f264807
>9934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=MuvGgmedEKY7QBfx3
>03DUcH4iWkpnRL1D3ADV51LeEg%3D&amp;reserved=0>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fwal.html&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f
>5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%
>7C637052252378056899&amp;sdata=svGLhgL5mQkQfQ%2B7UwsKzaJ4krn8%2F%2FgnHTm3
>LG3UEPA%3D&amp;reserved=0>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailingl
>ists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
>users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4
>f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C63705225
>2378056899&amp;sdata=MV%2FomDHw0jFta70BSSxaKIzUP3mSqhdx%2BH3WY64UCZo%3D&a
>mp;reserved=0
>_______________________________________________
>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: disable file locking mechanism over the network

Keith Medcalf
In reply to this post by Roman Fleysher

Check out the alternate VFS implementations (linked earlier), and also the available parameters that you can pass to the sqlite3_open_v2 interface when using URI mode, such as perhaps using the standard unix vfs but passing the nolock option to disable all file locking operations (but not the change detection).  Since you are serializing access at the application level, perhaps that might be useful for you to try.

https://sqlite.org/c3ref/open.html
https://sqlite.org/uri.html#coreqp


>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Roman Fleysher
>Sent: Friday, 27 September, 2019 21:49
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>I agree that overhead might grow exponentially with number of nodes
>trying to access the database. But then I do not understand why FLoM can
>not provide solution. It can enforce sequential access thus reducing the
>overhead.
>
>And yes, I was trying the KISS approach: rely on GPFS to do the
>synchronization instead of me programming client/server.
>
>Thank you,
>
>Roman
>
>________________________________________
>From: sqlite-users [[hidden email]] on
>behalf of Keith Medcalf [[hidden email]]
>Sent: Friday, September 27, 2019 11:12 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>On Friday, 27 September, 2019 17:00, Roman Fleysher wrote:
>
>>I am using SQLite over GPFS distributed file system. I was told it
>>honestly implements file locking. I never experienced corruption. But it
>>is slow in the sense that when many jobs from many compute nodes try to
>>access the same database things slow down considerably.
>
>Yes.  This is how "correct" locking would be implemented.  If would be so
>slow as to be virtually unusable.
>
>>I suspect, from the point of view of file system, there is lots of
>>pressure to develop fast grabbing of a lock and slow release. I think
>>this is because the key to fast network file system in general is making
>>it as independent as possible, thus distributed.
>
>No, it is because the filesystem server must be queried on every lock
>operation, and before that can return to the caller, each and every
>client must be contacted and "made consistent".  This means that as more
>clients "open" the same file, the overhead of each operation becomes
>orders of magnitude greater.  In order to "get around" this issue most
>network filesystems use so-called "optimistic" locking protocols.  This
>basically means that the software prays and goes ahead anyway without
>ensuring consistency amongst clients and simply corrupts the file in
>cases where the prayer does not work.  For the normal case this usually
>workthe link you sent in item 2,s adequately.  For in-place record
>updates, however, it usually does not.
>
>>To try to speed up locking I combined SQLite with FLoM, distributed file
>>lock manager. It is client/server application.the link you sent in item
>2,
>
>I don't think FLoM does what you think it does.
>
>>From experience, it seems that because SQLite still requests file locks,
>>the performance increase is not that big. I wonder if there is a way to
>>disable SQLite's internal file locking mechanism. I know this seems
>>strange to ask. But FLoM should be able to do it faster over many
>compute
>>nodes.
>
>>Or, perhaps the right way is for me to combine SQLIte with simple
>>client/server code to create light mySQL, mySQLite?
>
>Yes, this is the correct way to do it.  If multi-access to a single
>shared file worked properly then there would never have been any need to
>invent client/server database architecture.  The fact that is was
>invented at the same time as shared filesystems indicates that the KISS
>solution (a shared filesystem) was not robust.
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailingl
>ists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
>users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Ca315faff6fdc4
>5dfc70408d743c1a8b0%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C63705237
>1361975178&amp;sdata=dhrR%2FbKGqPEmk6sLv3JGouW%2BdO%2BaAK2WyP97LLYQlCU%3D
>&amp;reserved=0
>_______________________________________________
>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: disable file locking mechanism over the network

Roman Fleysher
In reply to this post by Keith Medcalf
Based on the link you provide, it looks like I need unix-none VFS and specify it as:

ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

FLoM will be ensuring sequential access. The journaling, as I understand, is independent of locking and will still work. That is robustness to crashes will remain.  unix-none just disables file lock requests, which are the cause of the overhead. Is this correct?

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Keith Medcalf [[hidden email]]
Sent: Friday, September 27, 2019 11:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

Yeah, WAL mode cannot work over a remote connection as the WAL index is a shared memory file -- which can only be accessed by processes on a single computer.

If you are using FLoM to co-ordinate your "application" use of SQLite3, then maybe you want to look at using the dotfile VFS which uses dotfiles to co-ordinate multi-access rather than file locks.

https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite.org%2Fsearch%3Fs%3Dd%26q%3Ddotfile&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C07b8076c03944d262b0808d743c6f9a6%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052394192099402&amp;sdata=Jkew%2B%2FZJkF2GZbtfYYtHfbFsXJiv%2FMUHfwveVXYdvxI%3D&amp;reserved=0

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Roman Fleysher
>Sent: Friday, 27 September, 2019 21:43
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>The timeout is set to 50min !
>
>The database is used to synchronize / manage tasks. A kind of job
>manager. Thus each access is a quick search with small update or delete.
>
>I looked at the WALL mode. I do not exactly understand how it works, but
>SQLite developers warn not to use WALL over network in item 2 of the link
>you mention.
>
>I was not aware of the various synch option. I will study them.
>
>Thank you,
>
>Roman
>
>________________________________________
>From: sqlite-users [[hidden email]] on
>behalf of Simon Slavin [[hidden email]]
>Sent: Friday, September 27, 2019 7:53 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] disable file locking mechanism over the network
>
>On 27 Sep 2019, at 11:59pm, Roman Fleysher
><[hidden email]> wrote:
>
>> From experience, it seems that because SQLite still requests file
>locks, the performance increase is not that big. I wonder if there is a
>way to disable SQLite's internal file locking mechanism. I know this
>seems strange to ask. But FLoM should be able to do it faster over many
>compute nodes.
>
>What busy_timeout did you set ?
>
>Do you process multiple operations inside a transaction, or do you let
>SQLite create its own transactions automatically ?
>
>SQLite always locks the entire database.  It does not implement table or
>row locking.
>
>SQLite has two (main) journalling modes.  The journalling made influences
>which operations lock out other operations.  The biggest change in
>lockouts occurs whether you do or do not have writes from multiple
>connections at once.  You might want to try executing this command just
>once:
>
>    PRAGMA journal_mode = WAL
>
>This makes a change to the database so that all connections which open it
>know it's in WAL mode.  You don't have to change your software.  It might
>speed things up.  (You can do
>
>    PRAGMA journal_mode = DELETE
>
>to set it back.)
>
>There are other other things you can do to disable various parts of the
>locking system, but you should try the above first.  You might want to
>play around with
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_synchronous&amp;data=02%7C01%7Croman.fleysher
>%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079
>934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=a%2FTb%2Fr7kdca1Q4
>%2F1Le1azbaXR0jdmkDKm042RRMgNYM%3D&amp;reserved=0>
>
>Refs:
>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fpragma.html%23pragma_journal_mode&amp;data=02%7C01%7Croman.fleyshe
>r%40einstein.yu.edu%7C26f5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f264807
>9934e02e89266ad0%7C1%7C0%7C637052252378056899&amp;sdata=MuvGgmedEKY7QBfx3
>03DUcH4iWkpnRL1D3ADV51LeEg%3D&amp;reserved=0>
><https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsqlite
>.org%2Fwal.html&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f
>5036ca2bc4f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%
>7C637052252378056899&amp;sdata=svGLhgL5mQkQfQ%2B7UwsKzaJ4krn8%2F%2FgnHTm3
>LG3UEPA%3D&amp;reserved=0>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailingl
>ists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-
>users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C26f5036ca2bc4
>f75ac5008d743a5f596%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C63705225
>2378056899&amp;sdata=MV%2FomDHw0jFta70BSSxaKIzUP3mSqhdx%2BH3WY64UCZo%3D&a
>mp;reserved=0
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C07b8076c03944d262b0808d743c6f9a6%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052394192099402&amp;sdata=OhldMpwWIrIVdPdGgltmiR0ucyw3TUCSlbVOiG7kwGM%3D&amp;reserved=0



_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C07b8076c03944d262b0808d743c6f9a6%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052394192099402&amp;sdata=OhldMpwWIrIVdPdGgltmiR0ucyw3TUCSlbVOiG7kwGM%3D&amp;reserved=0
_______________________________________________
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: disable file locking mechanism over the network

Keith Medcalf

On Friday, 27 September, 2019 22:11, Roman Fleysher <[hidden email]> wrote:

>Based on the link you provide, it looks like I need unix-none VFS and
>specify it as:

>ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

Yup.  I think you can also do that when opening the file on the sqlite3_open_v2 call by using the same URI format.  I think using nolock=1 without specifying a VFS does the same thing ... ie, using a filename of 'file:demo2.db?nolock=1'

>FLoM will be ensuring sequential access. The journaling, as I understand,
>is independent of locking and will still work. That is robustness to
>crashes will remain.  unix-none just disables file lock requests, which
>are the cause of the overhead. Is this correct?

I haven't looked at the actual code for the VFS but that is my impression.  The description for using the nolock=1 URI parameter says that you must have the application "serialize writes" in order to maintain consistency and if you are using FLoM to synchronize application access to the sqlite3 api, I would think that would cover it.  The nolock=1 specifically says that it disables "file locking operations" only but maintains the change detection logic which should presumably maintain cache consistency (I think the purpose of the nolock=1 is simply to allow "generic" vfs selection, by appending -none to the default vfs name -- ie, so that if you are running on Windows it uses win32-none instead of win32, and on unix uses unix-none instead of unix).

As an aside, it might be interesting to derive another VFS from the standard "unix" VFS which just replaces the locking operations with synchronization using FLoM, so that nothing special at all is required at the application level other than selecting a different VFS layer.



_______________________________________________
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: disable file locking mechanism over the network

Rowan Worth-2
In reply to this post by Roman Fleysher
On Sat, 28 Sep 2019 at 06:59, Roman Fleysher <[hidden email]>
wrote:

> ( somewhat related to Re: [sqlite] Safe to use SQLite over a sketchy
> network?)
>
> Dear SQLiters,
>
> I am using SQLite over GPFS distributed file system. I was told it
> honestly implements file locking. I never experienced corruption. But it is
> slow in the sense that when many jobs from many compute nodes try to access
> the same database things slow down considerably.
>
> I suspect, from the point of view of file system, there is lots of
> pressure to develop fast grabbing of a lock and slow release. I think this
> is because the key to fast network file system in general is making it as
> independent as possible, thus distributed. Avoid bottlenecks. But locking
> is by definition a bottleneck.
>


> From experience, it seems that because SQLite still requests file locks,
> the performance increase is not that big. I wonder if there is a way to
> disable SQLite's internal file locking mechanism.


In my experience with SQLite over network file systems, the biggest
bottleneck has nothing to do with locking and everything to do with
synchronous I/O, journalling, and the single-writer model.

Disabling locking in your scenario is _guaranteed_ to break your jobs.
SQLite on the compute nodes will at some point read a half-committed change
to the database and return SQLITE_CORRUPT (best case), or silently compute
a garbage result (worst case). Unless, that is, the database in question is
read-only and never updated. But if that was the case there would be no
scaling issue with the number of compute nodes as read-locks do not
conflict with each other.

The best thing you can do to improve concurrency for SQLite over a
networked file system is to carefully manage your transaction lifetimes.
There are several patterns to avoid:

1. Lots of small write transaction
2. Transactions which are open for a long time
3. Write transactions which do a lot of work before taking the RESERVED lock

All of which apply to SQLite on a local filesystem, but the network latency
magnifies the effects. To elaborate quickly, synchronous I/O and data being
written twice¹ impose a significant constant-time cost per transaction,
which is why small writes are not efficient. Avoiding long-running
transactions applies to both read and write transactions, because during a
DB update there is a period where the writer needs exclusive access to the
DB. If there is a long-running read transaction active at this point, the
writer must wait for it to finish and the effect is _every_ node wanting to
access the DB has to wait for this one read transaction.

¹once to the journal, once to the main DB

Somewhat related is a transaction which reads a bunch of data before doing
any DB updates - the problem here is that another node may take the
RESERVED lock during the read phase. SQLite only supports a single writer
at a time, so when the transaction tries to proceed to its write phase it
will not be able to proceed; you end up having to abort it and redo the
read phase. This one is avoided by phrasing the transaction using "BEGIN
IMMEDIATE", which will cause SQLite to take the RESERVED lock at the start
of the transaction.


I think WAL journal mode can improve concurrency but of course it doesn't
work in a network context. Anyway, trying to shortcut SQLite's mechanisms
is almost certainly the wrong question to be asking. If you don't need
locking then you don't need consistency and you should consider whether a
DB is the right tool or whether regular files would suffice.

-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: disable file locking mechanism over the network

Roman Fleysher
In reply to this post by Keith Medcalf
I like the idea of FLoM-based VFS !!

I am not sure I am up to actually doing it, but this should be as good a locking mechanism as any other.

Thank you, Keith.

Roman

________________________________________
From: sqlite-users [[hidden email]] on behalf of Keith Medcalf [[hidden email]]
Sent: Saturday, September 28, 2019 12:48 AM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On Friday, 27 September, 2019 22:11, Roman Fleysher <[hidden email]> wrote:

>Based on the link you provide, it looks like I need unix-none VFS and
>specify it as:

>ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

Yup.  I think you can also do that when opening the file on the sqlite3_open_v2 call by using the same URI format.  I think using nolock=1 without specifying a VFS does the same thing ... ie, using a filename of 'file:demo2.db?nolock=1'

>FLoM will be ensuring sequential access. The journaling, as I understand,
>is independent of locking and will still work. That is robustness to
>crashes will remain.  unix-none just disables file lock requests, which
>are the cause of the overhead. Is this correct?

I haven't looked at the actual code for the VFS but that is my impression.  The description for using the nolock=1 URI parameter says that you must have the application "serialize writes" in order to maintain consistency and if you are using FLoM to synchronize application access to the sqlite3 api, I would think that would cover it.  The nolock=1 specifically says that it disables "file locking operations" only but maintains the change detection logic which should presumably maintain cache consistency (I think the purpose of the nolock=1 is simply to allow "generic" vfs selection, by appending -none to the default vfs name -- ie, so that if you are running on Windows it uses win32-none instead of win32, and on unix uses unix-none instead of unix).

As an aside, it might be interesting to derive another VFS from the standard "unix" VFS which just replaces the locking operations with synchronization using FLoM, so that nothing special at all is required at the application level other than selecting a different VFS layer.



_______________________________________________
sqlite-users mailing list
[hidden email]
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C3e48038bb3254b2b636608d743cf2ce2%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052429406868157&amp;sdata=iJc7whQWoukCo1QUL8yAL73j5wcG5e%2Bw8TFU2RkHY1U%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users