Re: [sqlite] index creation 30x slower on Mac than on Linux

[Stephan Beal]

On Tue, Apr 23, 2013 at 5:58 PM, Alan Frankel wrote:

> ...Why is the Mac 30 times slower than Linux? They're both on the same
> network, accessing the same network drive location, so neither has the
> advantage of local disk access.
>

i'm speculating, but i have seen (on Linux) similarly drastic differences
when trying to use file locking on files on an NFS-mounted home directory.
Perhaps (again, speculating) such locking is being done on your Mac
installation but not your Linux installation?

That said: using a _shared_ sqlite db instance on a network share (you
don't mention if your DB is shared, only that you are using a networked
drive) is one of the cardinal no-no's, known to be problematic for reasons
far outside of sqlite's ability to deal with:
http://www.sqlite.org/faq.html#q5

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] index creation 30x slower on Mac than on Linux

[Simon Slavin]

On 23 Apr 2013, at 4:58pm, Alan Frankel  wrote:

> We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. 
> In order to make our lookups fast, we're creating an index table in the 
> database. On Linux, creating the table takes about 200 seconds. On Linux, 
> creating the table takes about 200 seconds. On the Mac the same operation 
> takes 6,400 seconds. Here's the "CREATE INDEX" statement we're using:
> 
> CREATE INDEX Full_Closure_Index ON Full_Closure(Client,Dependency)
> 
> Why is the Mac 30 times slower than Linux? They're both on the same network, 
> accessing the same network drive location, so neither has the advantage of 
> local disk access.

Thanks for the excellent description of your setup and data sizes which saves a 
great deal of picky questions.  My two remaining questions are what network 
protocol you're using to share the folder where your database is, and what OS 
the server is running.  I will assume you're using SMB and that the database is 
hosted on a Windows computer.

> Any suggestions on how to improve the performance here would be very welcome.
> 
> FYI, we're using different version of SQLite on each architecture: v 3.6.23.1 
> on Linux and v 3.7.7 on the Mac.

SQLite over network protocols usually doesn't do locking properly because most 
network stacks don't implement locking properly.  Section 2.1 of



is worth a read.  But also note that in section 6.1 of the same document you 
see that a Linux-related bug is fixed in 3.7.  I wonder if updating to 3.7 for 
your Linux client may change what you see.

My guess is that either or both of these two is happening:

A) Linux is not doing locking properly or at all.  Should any other process 
attempt to access the database across the network while the index is being 
created, the database may be corrupted.  The Mac is locking and unlocking which 
would not allow the corruption.

B) Linux is correctly locking the file once at the beginning and unlocking it 
at the end.  The Mac client is, for some reason, repeatedly locking and 
unlocking the file.

A very unbiassed set of guesses: one assumes that the Linux implementation is 
buggy, the other assumes that the Mac implementation is buggy.  Unfortunately, 
any attempt to find out what locking is being done needs to be done on the 
server, but you might know what tools can be used on your server to monitor 
that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] index creation 30x slower on Mac than on Linux

[Alan Frankel]

We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. In 
order to make our lookups fast, we're creating an index table in the database. 
On Linux, creating the table takes about 200 seconds. On the Mac the same 
operation takes 6,400 seconds. Here's the "CREATE INDEX" statement we're using:

CREATE INDEX Full_Closure_Index ON Full_Closure(Client,Dependency)

Why is the Mac 30 times slower than Linux? They're both on the same network, 
accessing the same network drive location, so neither has the advantage of 
local disk access.

Any suggestions on how to improve the performance here would be very welcome.

FYI, we're using different version of SQLite on each architecture: v 3.6.23.1 
on Linux and v 3.7.7 on the Mac.

Thanks very much.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users