Re: [sqlite] baffling performance decrease across network (specific case)
Thank you, that was immensely helpful - as well as Roger Binns many informative posts about OpLocks. Looks like there is nothing to be done about the slowdown (in fact I should be grateful there is even such a thing as opportunistic locking to make the single client situation faster), but it is a relief to have an explanation about what is happening! Serena. On Thu, May 22, 2008 at 5:59 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > This slowdown is almost certainly due to the use of opportunistic > locking in the SMB protocols. With a single client the it uses exclusive > oplocks and can cache remote file data locally. When a second client > connects it can no longer do this and subsequently slows down to the > real speed of remote file access. > > See http://en.wikipedia.org/wiki/Opportunistic_Locking for additional > info. > > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
Serena Lien wrote: > Thanks for the response - no it hadn't occurred to me to try a different > network filesystem, as I don't really have access to non windows machines. > I'm just using windows xp machines set up on the same domain, where the > databases reside on shared folders. > > I actually thought there might be extra work sqlite is doing (no not > sleeping!) when more than one client is accessing the same database, like > having to move between extra locking states or something like that, and that > it might be easily explainable, but you're perfectly right that it could > just be down to the OS. > This slowdown is almost certainly due to the use of opportunistic locking in the SMB protocols. With a single client the it uses exclusive oplocks and can cache remote file data locally. When a second client connects it can no longer do this and subsequently slows down to the real speed of remote file access. See http://en.wikipedia.org/wiki/Opportunistic_Locking for additional info. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
Thanks for the response - no it hadn't occurred to me to try a different
network filesystem, as I don't really have access to non windows machines.
I'm just using windows xp machines set up on the same domain, where the
databases reside on shared folders.
I actually thought there might be extra work sqlite is doing (no not
sleeping!) when more than one client is accessing the same database, like
having to move between extra locking states or something like that, and that
it might be easily explainable, but you're perfectly right that it could
just be down to the OS.
thanks anyway,
Serena
On Tue, May 20, 2008 at 9:34 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> That is probably a question that cannot be answered without knowing
> specifics of your network filesystem. Certainly we have NOT put code
> in SQLite that says:
>
>
> if( serena_is_accessing_multiple_times_on_network_filesystem()==TRUE ){
>sqlite3_usleep(600);
> }
>
> I really do not know why it goes slowly on a network filesystem when
> two or more clients are connected. I would guess it has something to
> do with the locking and caching protocols of your network filesystem.
> But that is only a guess. You talk as if this behavior is true of
> every network filesystem. Is that true? Have you tried on a
> different network filesystem? If you really need to know why it is
> slow, open the code, attach a protocol analyzer to your network, and
> figure it out. Let us know if you find anything interesting.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
On May 20, 2008, at 4:15 PM, Serena Lien wrote:
> Thanks for your response. I do realize the performance of sqlite
> over the
> network will be slower than accessing local databases - I will
> restate if I
> wasn't clear - the question I asked is why sqlite accessing a
> networked
> database is slower WHEN that particular database is attached in a
> different
> database connection/process.
That is probably a question that cannot be answered without knowing
specifics of your network filesystem. Certainly we have NOT put code
in SQLite that says:
if( serena_is_accessing_multiple_times_on_network_filesystem()==TRUE ){
sqlite3_usleep(600);
}
I really do not know why it goes slowly on a network filesystem when
two or more clients are connected. I would guess it has something to
do with the locking and caching protocols of your network filesystem.
But that is only a guess. You talk as if this behavior is true of
every network filesystem. Is that true? Have you tried on a
different network filesystem? If you really need to know why it is
slow, open the code, attach a protocol analyzer to your network, and
figure it out. Let us know if you find anything interesting.
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
Thanks for your response. I do realize the performance of sqlite over the network will be slower than accessing local databases - I will restate if I wasn't clear - the question I asked is why sqlite accessing a networked database is slower WHEN that particular database is attached in a different database connection/process. Please see my original message. The performance I'm getting when accessing a database over the network is perfectly acceptable to me. As soon as another client is attached to the same database, I am clearly seeing a different behaviour from sqlite, and the performance in that case is much slower, and this is what I'm trying to understand. thanks, Serena. > > I posted a query on this list very recently about a similar issue, except I > noticed a 20-fold decrease in R/W performance across the network with a > single connection (BLOB access), compared to what you get with a binary > file > R/W. Cogent explanations were given, and I conclude that SQLite (and > perhaps all non-backend-server based solutions will suffer greatly across a > network vs. local volume). > > I had to rejig my app so that remote SQLite files are first copied to a > local temp file, operated on, then copied back to remote volume. I can't > see any other way. > > Peter. > > > -- > - > Peter K. Stys, MD > Dept. of Clinical Neurosciences > Hotchkiss Brain Institute > University of Calgary > tel (403) 210-8646 > - > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
On Tue, May 20, 2008 at 9:43 AM, Serena Lien <[EMAIL PROTECTED]> wrote: > Hello, > > I wonder if anyone could shed some light on this. I am using sqlite in my > client programs to access databases over the network. I know sqlite isn't > recommended for this usage model, but I am happy that I have implemented it > such that only 1 client ever has write access, and am also happy with the > performance (speed) over the network in normal situations. > I posted a query on this list very recently about a similar issue, except I noticed a 20-fold decrease in R/W performance across the network with a single connection (BLOB access), compared to what you get with a binary file R/W. Cogent explanations were given, and I conclude that SQLite (and perhaps all non-backend-server based solutions will suffer greatly across a network vs. local volume). I had to rejig my app so that remote SQLite files are first copied to a local temp file, operated on, then copied back to remote volume. I can't see any other way. Peter. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] baffling performance decrease across network (specific case)
Hello, I wonder if anyone could shed some light on this. I am using sqlite in my client programs to access databases over the network. I know sqlite isn't recommended for this usage model, but I am happy that I have implemented it such that only 1 client ever has write access, and am also happy with the performance (speed) over the network in normal situations. However, I've noticed that when 2 clients have attached to the same database (where the database is on a remote machine, *not local*) then I get a significant slowdown when querying that database - the sqlite3_step function takes longer to complete. If only 1 client is attached to the database, the query performs perfectly fast, so it's definitely not just network latency. Can anyone explain what sqlite is doing and if there is anything I can do to avoid this issue? Note I am querying a table which contains blobs of data. I think there is a slowdown on querying tables without blobs in it, but it is certainly very obviously slower on the table with blobs. You can reproduce this using the sqlite3 cmd line program (v3.5.6) and creating 2 databases where the 2nd database has a table something like: create table ImageData(ID integer primary key, nRows integer, nCols integer, imageMtx blob, palette blob, nGridRows integer, nGridCols integer, gridMtx blob); and add some data to the table First db connection open test1.db with sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis select nGridRows, nGridCols, gridMtx from currentAnalysis.ImageData -- data is returned instantaneously Second db connection now open test1.db with another instance of sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis now go back to the first db connection, repeat the query and notice the results returned after 6 or so seconds if you detach the database from the second db connection, and run the query again on the first db connection, it's still slow if you now detach and reattach the test2.db database in the first db connection and run the query again, it's fast again. Apologies for the very long description - I hope I've explained the situation clearly enough. I may legitimately have 2 clients reading the same database at the same time, and it's very frustrating for my client program to slow down in this situation. Could anyone explain if there is a reason sqlite is doing this and if I can avoid it? many thanks, Serena. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
