I was raising/discussing similar question. Look through SQLite archive for :
disable file locking mechanism over the network
Client/server manager for SQLite is not enough. Internally, Sqlite will still
request lock from the file system and the overhead will still be there. Once
the client/server manager is working you have to disable SQLite's locking
mechanism. Then, the overhead will be reduced but not gone. If do that, make
sure you NEVER access the same database file bypassing your manager. This is a
file, you might be tempted to access it using sqlite directly.
The remaining overhead I can describe as this: If you create a file on one
node, it takes time for this file to show up on another node. This delay
depends on many things: number of nodes, network, load, configuration. etc.
Critical here is that file system, in my case GPFS, will delay a command trying
to access newly crated file on a different node until it shows up. You can
think of it as locking on the folder level performed internally by the file
system. So, even with client/server manager to regulate access to database,
SQLIte will still be slower than you might expect. This now depends on the
project requirements and throughput.
The true solution is client / server where server is the only process
accessing database. Jobs from all the nodes ask the server to do the work.
mySQL and other exiting full featured DMS have one serious limitation: system
administrator must install and maintain them. Sqlite is user level and simple
thing. What is needed is what I called "mySQLite" a server based on SQLite.
However, as soon as "server" and "network" enter the project, it becomes
tremendously more complicated: threads, buffering, authentication, dropped
connections...
The problem is thus in distilling the basic feature set such mySQLite should
have. This is what SQLite developers did for SQLite.
Roman
________________________________________
From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of
Graham Holden [sql...@aldurslair.com]
Sent: Wednesday, October 16, 2019 10:45 AM
To: SQLite mailing list
Subject: Re: [sqlite] Network file system that support sqlite3 well
Wednesday, October 16, 2019, 1:22:58 AM, Gary R. Schmidt <grschm...@acm.org>
wrote:
> On 16/10/2019 10:38, Jens Alfke wrote:
>>
>>> On Oct 15, 2019, at 3:47 PM, Peng Yu <pengyu...@gmail.com> wrote:
>>>
>>> I'd like to use sqlite3 db files on many compute nodes. But they
>>> should access the same storage device for the sqlite3 db files.
>>
>> Why not use an actual client-server database system like MySQL? It's
>> optimized for this use case, so it incurs a lot less disk (network) I/O.
>>
> To second what Jens has written - use the right tool for the job.
> SQLite is *not* the right tool for this sort of job.
> MySQL/MariaDB/PostGRESQL/Oracle/SQL Server/DB2/... are what you should
> be looking at.
Or, depending on the type and variety of the operations your "compute
nodes" need to do, write a pair of what could be relatively simple
client-server programs that police access to the SQLite DB (which the
server will be accessing as a local file).
For this to work (well, easily) you'd be looking to create "higher-
level" functions ("store this data set", "retieve this data set" etc.)
that the clients (on the compute nodes) can ask the server (next to
the SQLite file) to perform on their behalf.
Of course, if the need is for the compute nodes to have full access to
the SQLite API, this approach wouldn't be suitable -- you shouldn't
(IMHO) be trying to make the SQLite API itself work across the
network**.
Graham
** I believe someone has tried/succeeded in doing something like
this, but I don't know the details off the top of my head.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
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%7C59fa1226b16042b1738108d752478d8c%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637068339589985989&sdata=VltANrFLWIxmiNPNju3eYBqHDxy%2F%2BQIbfOgjARlnbkM%3D&reserved=0
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
