[sqlite] MMAP performance with databases over 2GB
Roger Binns wrote: > It would also be really nice if there wasn't a 2GB mmap limit on 64 > bit machines. The database would fit in my RAM around 4 times, and in > the address space more times than there are grains of sand! Yea I > know this isn't very Lite ... SQLightning has no such limit... https://github.com/LMDB/sqlightning there's nothing "heavy" about making maximum use of mmap. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/
[sqlite] MMAP performance with databases over 2GB
On 08/07/2015 12:35 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 08/06/2015 09:27 AM, Dan Kennedy wrote: >>> Is it using more CPU cycles in mmap mode or just taking longer? >>> If the former, does [time] attribute them to "user" or "system"? > It is taking longer. I have 3 XML dumps which I turn into JSON > (incrementally) and then denormalise and insert into SQLite across > several tables. While all this work is going on, the code prints out > statistics about how long it is running and about how many records per > second are being processed. The final database size after commit and > vacuum is ~8GB. There are a lot of foreign keys too, although all > simple (referring to an INTEGER PRIMARY KEY column). > > I gave mmap a try - ie the *only* change was to add an extra pragma > before the beginning of the transaction: > > "pragma mmap_size="+str(2*1024*1024*1024) > > In hard numbers, without that line I was doing ~1,118 records per > second and with it it does ~300. A normal run takes about 1h20m but > the mmap one was still running 3 hours later when I aborted it. > > (BTW this is all on a tmpfs filesystem on 64 bit Linux with swap > spread across two ssds, and 32GB of ram. ie the actual storage > hardware isn't a factor. Also single threaded because XML.) When the b-tree layer requests a page reference in mmap mode, SQLite first needs to figure out whether it should use regular in-memory page (data cached in heap memory) or a mmap page (data is actually a pointer into mmap'd address space). If a write-transaction is open, it cannot use a mmap page if: 1) there is an entry for the requested page in the wal file, or 2) there is an entry (possibly a dirty one) for the requested page in the cache. If the wal file is really large, as in this case, then test (1) can be quite slow. One interesting thing is that the wal-file lookup is done before the cache lookup. Which doesn't seem quite right. Source code archeology and testing have failed to reveal why it is that way. The branch here swaps the two tests around: http://www.sqlite.org/src/info/3a82c8e6cb7227fe Does that improve performance any in your case? Thanks, Dan. > >>> How large are you letting the wal file grow between checkpoints? > Pretty much the entire database size. For the tests I was starting > with a deleted database directory (ie no pre-existing files), and then > doing these pragmas: > >"pragma page_size=4096", > # "pragma mmap_size="+str(2*1024*1024*1024), >"pragma journal_mode=wal", >"pragma wal_autocheckpoint=1", >"pragma foreign_keys=on", > > Then I start a transaction, and do the importing within that > transaction. The database file is 4kb during that process, the wal > file gets to be about 10GB. > > If I use gdb to periodically break into the running process in the > mmap case, then it was always in sqlite3WalFindFrame. > > I don't need any help fixing my importing process (eg don't need a > journal on an empty database anyway). But it is frustrating that mmap > only goes up to a few kb shy of 2GB even for 64 bit, and I have one > example (ie anecdote not data) showing that mmap hurts for inserts on >> 2GB databases. Perhaps it is worth others testing to see if this >> is > a systemic problem, or just bad luck for me :-) > > It may also be relevant that tables add columns over time. I > dynamically add them after encountering previously unseen fields in > the JSON. However I'd expect the schema to be final a few thousand > records in. Most tables have 3 to 6 million records. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlXDmukACgkQmOOfHg372QSVtgCbBihGgIuZqS3Yy2JARXZ1+Q59 > GmwAoMG53XxuLNhcMIw1PV46fD/Z/5tT > =luXx > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MMAP performance with databases over 2GB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/07/2015 02:55 AM, Dan Kennedy wrote: >> http://www.sqlite.org/src/info/3a82c8e6cb7227fe > >> Does that improve performance any in your case? I tested 3a82c8e6 (fix above) against e596a6b6 (previous changeset), in both cases starting with an empty database, on tmpfs and with WAL. Three kinds of data are imported into the database, but it also means the first kind fits mostly within 2GB. I stopped the third kind import at 48 minutes in both cases. 3a82c8e6 (with mmap change) - --- 2m52s 22,821 per second 2m56s 4,823 per second 47m56s 1,157 per second 3.3 million records imported of this kind e596a6b6 (without mmap change) - -- 2m51s 22,855 per second 3m43s 3,800 per second 47m54s462 per second 1.3 million records imported of this kind Your change definitely helped once the database got above 2GB (I don't track exactly where that change happens in the import process - looks like in the second kind.) It would also be really nice if there wasn't a 2GB mmap limit on 64 bit machines. The database would fit in my RAM around 4 times, and in the address space more times than there are grains of sand! Yea I know this isn't very Lite ... Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlXFLlkACgkQmOOfHg372QQStwCfXXQhxJsyfJWUq/hOUm2KYdbs aPoAoLOHYbBn7CItwbmASG5igPeeeXpl =f1gz -END PGP SIGNATURE-
[sqlite] MMAP performance with databases over 2GB
On 08/06/2015 09:53 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > In my testing I am finding that using SQLite's mmap support with > larger databases (final db size is ~10GB) to create the database (WAL > mode) is considerably (about three times) slower than no mmap. > > The default max mmap limit is 2GB (sadly). Has anyone else tested > mmap with >2GB databases and have results? I don't know if this is a > peculiarity of my data set, or because of how SQLite is implemented. Is it using more CPU cycles in mmap mode or just taking longer? If the former, does [time] attribute them to "user" or "system"? How large are you letting the wal file grow between checkpoints? Dan. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlXDdQMACgkQmOOfHg372QRt3wCeKQpP9g2OhWS2yJg+iU7Gxvxo > TPUAn3ikDarecOaKLPIsnS3Xv+IltreU > =yUxV > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MMAP performance with databases over 2GB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/06/2015 09:27 AM, Dan Kennedy wrote: >> Is it using more CPU cycles in mmap mode or just taking longer? >> If the former, does [time] attribute them to "user" or "system"? It is taking longer. I have 3 XML dumps which I turn into JSON (incrementally) and then denormalise and insert into SQLite across several tables. While all this work is going on, the code prints out statistics about how long it is running and about how many records per second are being processed. The final database size after commit and vacuum is ~8GB. There are a lot of foreign keys too, although all simple (referring to an INTEGER PRIMARY KEY column). I gave mmap a try - ie the *only* change was to add an extra pragma before the beginning of the transaction: "pragma mmap_size="+str(2*1024*1024*1024) In hard numbers, without that line I was doing ~1,118 records per second and with it it does ~300. A normal run takes about 1h20m but the mmap one was still running 3 hours later when I aborted it. (BTW this is all on a tmpfs filesystem on 64 bit Linux with swap spread across two ssds, and 32GB of ram. ie the actual storage hardware isn't a factor. Also single threaded because XML.) >> How large are you letting the wal file grow between checkpoints? Pretty much the entire database size. For the tests I was starting with a deleted database directory (ie no pre-existing files), and then doing these pragmas: "pragma page_size=4096", # "pragma mmap_size="+str(2*1024*1024*1024), "pragma journal_mode=wal", "pragma wal_autocheckpoint=1", "pragma foreign_keys=on", Then I start a transaction, and do the importing within that transaction. The database file is 4kb during that process, the wal file gets to be about 10GB. If I use gdb to periodically break into the running process in the mmap case, then it was always in sqlite3WalFindFrame. I don't need any help fixing my importing process (eg don't need a journal on an empty database anyway). But it is frustrating that mmap only goes up to a few kb shy of 2GB even for 64 bit, and I have one example (ie anecdote not data) showing that mmap hurts for inserts on > 2GB databases. Perhaps it is worth others testing to see if this > is a systemic problem, or just bad luck for me :-) It may also be relevant that tables add columns over time. I dynamically add them after encountering previously unseen fields in the JSON. However I'd expect the schema to be final a few thousand records in. Most tables have 3 to 6 million records. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlXDmukACgkQmOOfHg372QSVtgCbBihGgIuZqS3Yy2JARXZ1+Q59 GmwAoMG53XxuLNhcMIw1PV46fD/Z/5tT =luXx -END PGP SIGNATURE-
[sqlite] MMAP performance with databases over 2GB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 In my testing I am finding that using SQLite's mmap support with larger databases (final db size is ~10GB) to create the database (WAL mode) is considerably (about three times) slower than no mmap. The default max mmap limit is 2GB (sadly). Has anyone else tested mmap with >2GB databases and have results? I don't know if this is a peculiarity of my data set, or because of how SQLite is implemented. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlXDdQMACgkQmOOfHg372QRt3wCeKQpP9g2OhWS2yJg+iU7Gxvxo TPUAn3ikDarecOaKLPIsnS3Xv+IltreU =yUxV -END PGP SIGNATURE-
