Try to add to your SQLite test these steps: 3.5) Execute statement BEGIN (you can do it via prepared statement). 5.5) Say every 10 000 records execute COMMIT and then BEGIN again. Or if you indeed want to measure maximum write speed then skip this step at all.
And your words that pragma synchronous didn't have any effect at all show that either you did something wrong or you are working with some weird OS settings preventing respect of data flushing request... Pavel On Fri, Jul 24, 2009 at 5:29 PM, Zachary Turner<divisorthe...@gmail.com> wrote: > What kind of performance can I expect to get from sqlite inserting > many many rows of blob data into a database? I know this depends on > many factors, but I'm a little puzzled because I have written some > tests that allow me to test various ways of inserting data and as far > as I can tell I've tested almost every combination of pragmas and > sqlite3_config options that might have an effect on performance, and I > cannot find a way to insert any faster than about 20MB / second. On > this same disk, I wrote a similar program to test the speed of writing > sequentially to a file and in this case I was able to get around 75 MB > / second. Here was what I've done in the two scenarios: > > Raw Disk Write Test (75 MB / second) > ----------------------- > 1) Open a very large input file (a few GB) from physical disk 1 > 2) Open an output file on physical disk 2 > 2) Read from the input file sequentially in 4KB chunks > 3) For each 4KB chunk, write the chunk to the output file > > > Sqlite Test (20 MB / second) > ---------------------- > 1) (Same as above) > 2) Create a sqlite database on physical disk 2 with one table that has > 1 column of type BLOB > 3) Created a prepared statement "INSERT INTO MyTable (ChunkData) values (?1)" > 4) Read from the input file sequentially in 4KB chunks > 5) For each 4KB chunk,use sqlite3_bind_blob with the SQLITE_TRANSIENT > flag, execute the prepared statement, then reset the prepared > statement. > > > In addition, I've tried changing pretty much every sqlite option I can > find, including replacing the sqlite_mem_methods with an extremely > fast allocator using sqlite3_config(SQLITE_CONFIG_MALLOC, ...), > disabling memory stat collection with > sqlite3_config(SQLITE_CONFIG_MEMSTATUS, ...), pragma > default_cache_size, pragma page_size, and pragma synchronous. > > Even pragma synchronous, which I thought would surely increase > performance if I set it to 0, has no effect at all. I also tried > creating the database with SQLITE_OPEN_NOMUTEX and this also had no > effect on performance. 20MB / second seems awfully slow given that I > write almost 4x that fast using regular disk i/o. > > Am I doing something wrong? I was hoping to be able to achieve at > least 40MB / second of throughput. > > Regards, > Zach > _______________________________________________ > 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
