On 23 Apr 2013, at 4:58pm, Alan Frankel <alan.fran...@mathworks.com> 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 <http://www.sqlite.org/howtocorrupt.html> 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
