[sqlite] Insertion into the large DB drastically slows down at some point
> On modern PCs, SQLite's page cache does not have a large effect because > of the file cache of the OS, but you should do, e.g., > PRAGMA cache_size = -100; > for a 1 GB cache (default is only a few MB). Hitting the page cache is much cheaper as it does not involve a system call. Try opening a memory only db, versus placing sqlite on a ramdisk. A simple benchmark will show a large speed difference. Sergej
[sqlite] Insertion into the large DB drastically slows down at some point
Yuri wrote: > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; > ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA journal_mode = OFF; > * Synchronous mode is disabled: PRAGMA synchronous = OFF; On modern PCs, SQLite's page cache does not have a large effect because of the file cache of the OS, but you should do, e.g., PRAGMA cache_size = -100; for a 1 GB cache (default is only a few MB). Regards, Clemens
[sqlite] Insertion into the large DB drastically slows down at some point
On 2015-08-28 04:15 AM, Yuri wrote: > I build a fairly large DB, with major tables having 800k..5M rows, > with several relationships between tables. > > At ~30% into the insertion process it slows down rapidly. Records > #171k..172k are still fast, and records #172k...173k are already ~10 > times slower. (all records are more or less similar) > > During Insertion process, selects are also performed to obtain keys to > insert into other tables. On the point of inserting and selecting to get keys.. you don't /have/ to let the autoincrement or primary key assign keys, if I do bulk inserts, I check the once what the next key should be (highest key + 1) and then simply count up from there inserting every next item with it's next key which I then don't need to select to find. Much faster this way. Check extra Indices and Triggers are not slowing things down. Also - I am not sure that turning the Journal off saves much for that size transaction. The cache will spill to disk anyway. Alternatively, break it into several smaller transactions, maybe doing just 100k inserts at a time. Best of luck, Ryan > > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN > TRANSACTON; ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA journal_mode = OFF; > * Synchronous mode is disabled: PRAGMA synchronous = OFF; > > Plan for each "select" statement shows that it uses an index or > primary key. Every insert statement is a simple insert "insert into > xxx(x,x,x) values(?,?,?)" Selects are also all simple one-table > selects. All statements used in prepared form. > > How can I understand why the slowdown occurs? Especially, why the > slowdown in so "sharp"? > > Something drastic happens, like some strategy is recomputed, some > index is rebuilt, etc... > > Thank you, > Yuri > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insertion into the large DB drastically slows down at some point
On 28 Aug 2015, at 3:45am, Yuri wrote: > My computer has 24GB ob RAM of which 4GB is free. At the time of the problem > the size of the process is 325MB. And the size of complete DB is ~250MB. So > this isn't it. > > What's worth mentioning though is that at the time of the event in question > size of the process increases by 5MB. It seems we need someone who knows how SQLite creates databases in memory. Do you open this database as ':memory:' ? I don't know why the process suddenly gets bigger. I don't think anything in the SQLite programming is doing it. There are many mysterious things going on in modern operating systems and I only know Macintosh well enough to make guesses about why it does things. Simon.
[sqlite] Insertion into the large DB drastically slows down at some point
On 28 Aug 2015, at 3:15am, Yuri wrote: > * Database is written into memory > [...] > How can I understand why the slowdown occurs? Especially, why the slowdown in > so "sharp"? Your computer has a certain amount of free memory. Once your database is bigger than that size the computer has to keep moving parts of the database into storage so it has room for the new data. I will guess that if you got more RAM in your computer you would have faster operations for longer. Simon.
[sqlite] Insertion into the large DB drastically slows down at some point
Thanks to everybody who made suggestions. There was the legitimate constraint violation caused by a bug in an importing program. Program was erroneously inserting zero integer into the field that is both the leading part of the primary key (possibly causing its non-uniqueness), and a foreign key (definitely causing its violation). This triggered the slowdown behavior. I can't understand why exactly, because the primary key should have failed immediately, and the foreign key was deferred. But that's what happened. Yuri
[sqlite] Insertion into the large DB drastically slows down at some point
How many indexes on your data? Can you DROP your indexes for the insert, then CREATE INDEX them again after all the content is in place? -- D. Richard Hipp drh at sqlite.org
[sqlite] Insertion into the large DB drastically slows down at some point
I'm with Simon. If you're writing 5 million rows to memory, the OS has to move unused memory out to disk. I'd say write the data to disk (SSD if you can) and check if the data written out is at a consistent speed. On Thu, Aug 27, 2015 at 10:29 PM, Simon Slavin wrote: > > On 28 Aug 2015, at 3:15am, Yuri wrote: > > > * Database is written into memory > > [...] > > How can I understand why the slowdown occurs? Especially, why the > slowdown in so "sharp"? > > Your computer has a certain amount of free memory. Once your database is > bigger than that size the computer has to keep moving parts of the database > into storage so it has room for the new data. > > I will guess that if you got more RAM in your computer you would have > faster operations for longer. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Insertion into the large DB drastically slows down at some point
On 08/27/2015 19:45, Richard Hipp wrote: > How many indexes on your data? > > Can you DROP your indexes for the insert, then CREATE INDEX them again > after all the content is in place? Only indexes needed during the insert are in DB. Only 2 indexes exist, plus there are several "uniq" constraints, also added to be used by inserts. Without indexes/uniq constrainst it will slow down, and plans will show table scans. Yuri
[sqlite] Insertion into the large DB drastically slows down at some point
On 08/27/2015 19:29, Simon Slavin wrote: > Your computer has a certain amount of free memory. Once your database is > bigger than that size the computer has to keep moving parts of the database > into storage so it has room for the new data. > > I will guess that if you got more RAM in your computer you would have faster > operations for longer. Good point. My computer has 24GB ob RAM of which 4GB is free. At the time of the problem the size of the process is 325MB. And the size of complete DB is ~250MB. So this isn't it. What's worth mentioning though is that at the time of the event in question size of the process increases by 5MB. Yuri
[sqlite] Insertion into the large DB drastically slows down at some point
I build a fairly large DB, with major tables having 800k..5M rows, with several relationships between tables. At ~30% into the insertion process it slows down rapidly. Records #171k..172k are still fast, and records #172k...173k are already ~10 times slower. (all records are more or less similar) During Insertion process, selects are also performed to obtain keys to insert into other tables. I followed every advise I could find: * Database is written into memory * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; ... END TRANSACTION; block. * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; * Journal is disabled: PRAGMA journal_mode = OFF; * Synchronous mode is disabled: PRAGMA synchronous = OFF; Plan for each "select" statement shows that it uses an index or primary key. Every insert statement is a simple insert "insert into xxx(x,x,x) values(?,?,?)" Selects are also all simple one-table selects. All statements used in prepared form. How can I understand why the slowdown occurs? Especially, why the slowdown in so "sharp"? Something drastic happens, like some strategy is recomputed, some index is rebuilt, etc... Thank you, Yuri
