Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 3:40 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > >> >> WORKAROUNDS: >> >> Or, brute force: Copy the file locally, do your stuff, copy it back. >> > Looks like the brute force solution is the only answer here. (Translated automatically from the Spanish original) ... There is a special type that we could consider "implementation", called "Opportunistic cache". It is linked to the problems of locking files in multi-user environments in which different applications can access the same data. In these cases, operating systems have mechanisms so that a user (application program) obtain the blocking of an entire file or part thereof. The theory is that while maintaining the lock, no one else can modify the file (perhaps read), and that once completed modifications, the user unlocks the file so that others can use it. However, under certain network applications, and in order to improve performance, using a mixed system called "Opportunistic locking", in which the user communicates to the system that will use this modality. To do this, you get a copy of the entire file, which stores a local opportunistic cache. Thus, the transactions are faster than if you have to be conducted through the network requests for different pieces, along with the relevant requests of lock/unlock. Finally, when the user has completed transactions with the file, the server returns an updated copy. The problem arises when, in the interim, another user requests to use the same file. The incidence is particularly prevalent when the file is too big to handle. Why then, even for a minor change, the first user may be delayed enough to return the amended version to the server. The solution adopted to avoid excessive delays, is that, upon receipt of the request of the second user, the system sends an order to the first stopping oplock and return the file as it stands at this time so that the second user can use it. Although not without its problems, especially in unreliable networks unreliable, the system allows yield increases of around 30%. Not so much by the locking system used, for the fact that data have been previously cached by the user. HTH Adolfo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 3:40 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > > > WORKAROUNDS: > > Set SQLites page size to be much larger (PRAGMA page_size). >Makes SQLite deal with bigger chunks of data, reducing the overhead >percentage. You'll very likely need to turn this up pretty high >to see significant changes. > > Set SQLites page cache to be much larger (PRAGMA cache_size) >Reduces the number of I/O operations. Great for lookups and sorts. >Not that useful for writes. Depends a lot on how you use the DB. > > Live dangerously and turn down/off disk syncing (PRAGMA synchronous). >Reduces the delay for writes. Dangerous. > > Or, brute force: Copy the file locally, do your stuff, copy it back. > Thank you all for your suggestions and explanations. I now understand better the complexity underlying networked volumes. I tried: PRAGMA page_size = SQLITE_MAX_PAGE_SIZE PRAGMA cache_size = 100 PRAGMA synchronous = OFF (all executed before creating any tables) There was no improvement in first read/write performance at all. Looks like the brute force solution is the only answer here. Cheers, 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
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 02:40:28PM -0600, Peter K. Stys scratched on the wall: > On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > Performance on a remote volume is about 20x slower because the raw I/O > > capability of a network disk drive is about about 1/20th of a local > > disk drive. There isn't anything much SQLite can do about that. > I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (?2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). Bandwidth isn't the issue... it is mostly latency. Copy operations can buffer large amounts of data and spit it across in one big linear stream. The file system driver can easily pipeline file requests, keeping the pipe good and full (or nearly so). SQLite, on the other hand, is going to pick and choose different blocks for reading and writing from all over the data file. That's why you see the 40% drop (vs copies) even on local disks. The killer is that the overhead for a file request is MUCH larger for network based systems since it requires a network round-trip and that's aways going to be a dozen milliseconds or so, no matter what the bandwidth of the network. Copies can hide a lot of this because they're moving larger chunks of data, so the overhead percentage is reduced. If you know what you need next (such as a copy), you can also stagger requests. SQLite (or any application that accesses a file in a non-linear way) can't do that. SQLite tends to digest fairly small chunks of data (related to the page size), keeping the overhead high for network systems. Additionally, SQLite is normally extremely paranoid about I/O operations and blocks fully on all file operations, including writes. Operating systems also tend to be a lot more cautious about caching file pages in RAM from network volumes vs. local disks, which will further cut into your SQLite performance (but make little difference to a copy). I can go on and on, but it basically boils down to A) Copies are about the worst comparison you can make because they're a near-ideal situation. B) Network transaction overhead is significant and SQLite's I/O behavior tends to make the worst of that. This is pretty inherent in what SQLite does. It isn't poor coding, it's just the way things work for any system that needs quasi-random access to a file in small chunks. > So I don't understand why the huge performance hit (compared to other > network file access like Finder copies, not comparing remote vs. local) when > accessing remote SQLite files? I could understand some slowdown because of > extra overhead with network operations, but 20-fold? Yes. Easily. I'm sure if you do a bit of testing you'll also find the bandwidth of the network doesn't matter much. If you see 20-fold for GigE, I'd expect 25-fold (or less) for 100Mb. The issue is the huge number of round-trips, not the speed in which the smallish pages are passed back. WORKAROUNDS: Set SQLites page size to be much larger (PRAGMA page_size). Makes SQLite deal with bigger chunks of data, reducing the overhead percentage. You'll very likely need to turn this up pretty high to see significant changes. Set SQLites page cache to be much larger (PRAGMA cache_size) Reduces the number of I/O operations. Great for lookups and sorts. Not that useful for writes. Depends a lot on how you use the DB. Live dangerously and turn down/off disk syncing (PRAGMA synchronous). Reduces the delay for writes. Dangerous. Or, brute force: Copy the file locally, do your stuff, copy it back. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> You may see some performance increase by setting pragma page_size to a > larger value so that SQLite transfers fewer, but larger, blocks across > the network. I would try benchmark tests with page sizes of 8K and 32K > to see if there is a substantial difference. Good point Dennis, though you should probably mention that he would need to be using at least 3.5.8 and Vacuum after setting the page size pragma for it to actually take effect on an existing database. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
> I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (—2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). Peter, there is a lot more latency over a network than just hitting a local disk as well, so you've got potentially hundreds of requests from disk to perform a single select on the database (traversing the Btree, etc). Your OS may perform some read-aheads and caching which would reduce the latency to nearly nothing for the disk access (on a local machine), but you're having to deal with network latency and protocol overhead on _each_ of those hundreds of requests when you're working over a network. Raw sequential throughput you mentioned really has no relevance here at all. Like Richard said, use the right tool for the job. You need a database that resides on the server and communicates using its own network protocol. If you'd like to continue using SQLite you might check out some of the server/client wrappers out there: http://www.sqlite.org/cvstrac/wiki?p=SqliteNetwork You've got to realize that no other (non-server based) database would be able to perform better in this situation. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
Peter K. Stys wrote: > > I would disagree with this, unless I misunderstand. File copies (from the > Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% > of theoretical max on our Gbit LAN, whereas reading the records from the > same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible > performance). So there is plenty of raw I/O bandwidth across the LAN and > network drive, but for some reason SQLite access to its remote files is > extremely slow (to be clear: these are single users accessing single files). > > In contrast SQLite R/W to local files runs at about 60% of raw binary file > access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec). > > So I don't understand why the huge performance hit (compared to other > network file access like Finder copies, not comparing remote vs. local) when > accessing remote SQLite files? I could understand some slowdown because of > extra overhead with network operations, but 20-fold? > Random access I/O to a file using 1K pages is very different than sequential reading or writing used to do a file copy. There is *much* more overhead involved. What SQLite is doing is more like copying a directory with thousands of small files, rather than copying a single large file. You may see some performance increase by setting pragma page_size to a larger value so that SQLite transfers fewer, but larger, blocks across the network. I would try benchmark tests with page sizes of 8K and 32K to see if there is a substantial difference. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On Wed, May 14, 2008 at 2:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On May 14, 2008, at 3:58 PM, Peter K. Stys wrote: > > > I'm curious, when you say performance enhancements, does that include > > improved R/W performance to/from remote volumes on OS X, which > > presently > > suffer serious (20-fold) speed issues. I had a recent post on this. > > If not, will 3.6.0 address this? > > > > Performance on a remote volume is about 20x slower because the raw I/O > capability of a network disk drive is about about 1/20th of a local > disk drive. There isn't anything much SQLite can do about that. > Dear Richard, I would disagree with this, unless I misunderstand. File copies (from the Finder under OS X) to/from our Xserve run at about 50 MBytes/s or about 50% of theoretical max on our Gbit LAN, whereas reading the records from the same file via SQLite is 20-25x slower (≈2MB/sec at best, terrible performance). So there is plenty of raw I/O bandwidth across the LAN and network drive, but for some reason SQLite access to its remote files is extremely slow (to be clear: these are single users accessing single files). In contrast SQLite R/W to local files runs at about 60% of raw binary file access on a local volume, very acceptable (≈35MB/s vs. 60-70MB/sec). So I don't understand why the huge performance hit (compared to other network file access like Finder copies, not comparing remote vs. local) when accessing remote SQLite files? I could understand some slowdown because of extra overhead with network operations, but 20-fold? 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
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 3:58 PM, Peter K. Stys wrote: > I'm curious, when you say performance enhancements, does that include > improved R/W performance to/from remote volumes on OS X, which > presently > suffer serious (20-fold) speed issues. I had a recent post on this. > If not, will 3.6.0 address this? > Performance on a remote volume is about 20x slower because the raw I/O capability of a network disk drive is about about 1/20th of a local disk drive. There isn't anything much SQLite can do about that. If you need to access a database that physically resides on a remote machine, you should probably use a client/server database engine with the server located on the same machine where the data lives. 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] SQLite version 3.5.9
On May 14, 2008, at 2:14 PM, Petite Abeille wrote: > > On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > >> Works for me. Did you compile the shell yourself or use the prebuilt >> binary? > > I did compile it myself. Any additional configuration(s) one should > take care of to enable this pragma? > No. It should just work. As I said, I can't get it to fail. What is the result of the pragma statement - what does it return. It should return the new journal mode: $ sqlite3 test.db sqlite3> PRAGMA journal_mode=OFF; off If you did not see the "off" return, then perhaps you mistyped the pragma name. Unrecognized pragmas are silently ignored (a feature, not a bug). 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] SQLite version 3.5.9
On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > > Works for me. Did you compile the shell yourself or use the prebuilt > binary? Ooops... never mind... the shell works fine... I was using sqlite3_prepare and my application was linked against a different version of the lib... Everything works as advertise :) Thanks for the release! Cheers, PA. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 8:10 PM, D. Richard Hipp wrote: > Works for me. Did you compile the shell yourself or use the prebuilt > binary? I did compile it myself. Any additional configuration(s) one should take care of to enable this pragma? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.5.9
On May 14, 2008, at 1:59 PM, Petite Abeille wrote: > Hello, > > On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote: > >> There is also a new *experimental* PRAGMA called "journal_mode" >> which can provide performance improvements under some circumstances. > > I'm trying the new journal_mode pragma: > > % uname -v > Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386 > > % sqlite3 -version > 3.5.9 > > pragma journal_mode = off > > But this doesn't seem to have the expected effect as a '-journal' file > is still created. > Works for me. Did you compile the shell yourself or use the prebuilt binary? 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] SQLite version 3.5.9
Hello, On May 14, 2008, at 7:17 PM, D. Richard Hipp wrote: > There is also a new *experimental* PRAGMA called "journal_mode" > which can provide performance improvements under some circumstances. I'm trying the new journal_mode pragma: % uname -v Darwin Kernel Version 9.2.2; root:xnu-1228.4.31~1/RELEASE_I386 % sqlite3 -version 3.5.9 pragma journal_mode = off But this doesn't seem to have the expected effect as a '-journal' file is still created. Did I misunderstood what 'journal_mode = off' is meant to do, e.g. not creating a journal file in the first place? Thanks in advance. Kind regards, -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.5.9
SQLite version 3.5.9 is now available on the SQLite website http://www.sqlite.org/ http://www.sqlite.org/download.html This release features some minor bug fixes and performance enhancements. There is also a new *experimental* PRAGMA called "journal_mode" which can provide performance improvements under some circumstances. Additional information about these and other changes is available on the website. We anticipate that version 3.5.9 will be the last version in the 3.5 series. Our plan is for the next release to be version 3.6.0 which incorporates changes in the VFS layer used to tie SQLite into the underlying operating system. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
