I do have a similar issue. I am finding it hard which is the best configuration for most of the large scale application. Our database size grows from 0 – 45 GB . As the database size grows, performance seems to be degrading. Major operations include insertion/read/delete Current settings: PRAGMA journal_mode = TRUNCATE PRAGMA page_size;", lPageSize) UINT64 ui64MaxPageCount = 53687091200 / lPageSize; //50 GB
PRAGMA max_page_count = %I64u;", ui64MaxPageCount)); I am planning to change it to below settings to see if that improves the performance. PRAGMA journal_mode = WAL pragma page_size=4096 pragma cache_size=16384 PRAGMA wal_autocheckpoint=100000 Setting wal_autocheckpoint to 100000 mean that the data gets committed to the disk after it reaches 100 MB , Am I right ? If the machine crashes or power goes off during this time, 100 MB data is lost? Any API calls which would commit the data to disk forcibly? Let me know if the above settings would improve the application performance or something needs to be configured. Thanks -Veeresh -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Sunday, January 19, 2014 9:37 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Dramatic performance difference between different "PRAGMA synchronous" settings for bulk inserts In WAL mode with synchronous=NORMAL, SQLite only syncs (FlushFileBuffers() on windows) when it does a checkpoint operation. Checkpoints should be happening automatically whenever the WAL file exceeds about 1MB in size. For an 8GB database, probably there are about 8000 sync operations, therefore. If each takes about 2 seconds, that would pretty much account for the extra 4.5 hours. If you are creating a new database from scratch, it is safe to set synchronous=OFF. If you lose power in the middle, your database file will probably be corrupt, but since you were creating it from scratch you can easily recover just be starting the database creation process over again from the beginning. If you want to try running with synchronous=NORMAL, you might try setting PRAGMA wal_autocheckpoint=100000; (from the default of 1000) which will make for dramatically larger WAL files, but also dramatically fewer syncs. Then the syncs will use just 5 or 6 minutes instead of 4.5 hours. Hopefully. On Sun, Jan 19, 2014 at 9:00 AM, Mario M. Westphal <m...@mwlabs.de> wrote: > I have a performance effect which I don't quite understand. > Maybe I'm using the wrong settings or something. Sorry for the long > post, but I wanted to include all the info that may be important. > > My software is written in C++, runs on Windows 7/8, the SQLite > database file is either on a local SATA RAID disk or a SSD. > Typical database sizes are between 2 GB and 8 GB. > The largest tables hold several million entries. Also FTS4 is used, > which also creates large tables. > Fast internal RAID disks, SDD. Four Xeon cores. 8 GB RAM. > > I'm using SQLite 3.8.0.2 > WAL mode, shared cache enabled. > locking_mode=NORMAL > checkpoint_fullfsync=0 > pragma page_size=4096 > pragma cache_size=16384 > > General (retrieval) performance is excellent! > > > During an ingest phase, my application pumps in hundreds of thousands > of records into multiple tables. > There are massive amounts of writes during that phase, different > record sizes, tables with one to four indices etc. > > My application is multi-threaded and inserts data into the database > concurrently from multiple threads. > The threads process data in batches, and use SQLite transactions to > process all records of a batch into the database. Transactions gain a > lot of speed, which outweighs the side effects of potential blocking. > The threads monitor the execution times of the database operations and > adjust the batch size to balance speed and transaction lock duration. > Slower > operations cause smaller batches, which results in shorter database > locks and better concurrency. The system adapts fairly well to system > performance and data structure. > > The performance was not that bad, but far from good. > > For a given set of input data (100,000 "elements"), the execution > estimate was about 5 hours. > Database on a high-speed SSD. > The largest table holds about 5 million entries afterwards. > > ***With one single change*** I improved the execution time from 5 > hours down to about 30 minutes! > > I changed > > PRAGMA synchronous=NORMAL > > to > > PRAGMA synchronous=OFF > > Also all other database write operations just 'fly' now. > I'm even more impressed with SQLite than before, but I wonder why is > the change so _dramatic_ ? > > From the docs my impression was that using WAL mode is ideal for bulk > inserts. That wrapping large bulks of data into smaller batches, > wrapped in BEGIN/COMMT is best for performance etc. That using > synchronous=NORMAL limits the file system flush/wait operations > certain really important operations. > > I logged the execution times of various operations in this phase to a > text file. Everything was fast, the processing, the INSERTs etc. > But COMMIT operations sometimes took 20s, then 0.2s, then again 10s. > That's the time SQLite spends in the execute call with "COMMIT". > > Of course the amount of data written in each transaction block varied, > but in general, 85% of the total execution time of my code was spent > in the COMMIT call. > My application was the only application with measurable disk I/O at > the time. No virus checker etc. > > Changing to synchronous=OFF made the commits 10 times faster. > > Is this the expected behavior or am I missing something obvious? > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
