Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase
I'll explain our needs a little more and then perhaps you can tell me if the current approach is the optimal one or not. In our application, if we were to have a single file, due to the billions of records we need to store, the file would easily exceed the 14TB maximum limit of a database file. In the application, the nature of the data is such that we really need many millions of tables, but the contents of each table is completely independent of another; we don't ever need to run a join or query multiple tables when making a query. We also know the exact table name + database to use for a query based on the table and database names. This is the reason we wanted to try and fit as many tables into a database file as practical. Each table is not expected to have more than 5000 rows. I saw some posts in the past where Dr. Richard Hipp said it's fine to have 100's or even 1000's of tables per database file as long as you keep the application that uses this database long running where you don't constantly open and close it. We would fit into this model. Performance is important in this app, so this is the reason we started using WAL mode to improve write speeds. 70% of the operations we have are inserts + updates. Any thoughts on this approach? Thanks From: Simon Slavin <slav...@bigfraud.org> >To: Terry Cumaranatunge <cumar...@yahoo.com>; General Discussion of SQLite >Database <sqlite-users@sqlite.org> >Sent: Friday, September 2, 2011 12:41 PM >Subject: Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens >increase > > >On 2 Sep 2011, at 6:34pm, Terry Cumaranatunge wrote: > >> This does suggest that we should re-evaluate our design and not open as many >> databases from a single process. The original motivation was to limit the >> size the database for performance reasons, which resulted in a large number >> of database files. I think we are going to try creating multiple tables per >> database and evaluate its performance. >> >> Are there any general guidelines on a tested limit on the number of tables >> in a database or a general rule of thumb on the maximum recommended size for >> a database file? > >I'm concerned that you might be involved in premature optimization. The >performance of SQLite does not greatly degrade with larger tables or larger >databases. In fact you might be slowing your system down more by trying to >arrange multiple databases. Have you tried just using SQLite in the naive way >-- with one big table in one database -- and seeing whether that provides a >solution too slow for you ? > >Simon. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_open_v2 performance degrades as number of opens increase
on't see the times you're seeing using this program...though my databases
>> are empty which probably makes a difference.
>> I do see the gradual increase in time...I think this is almost all due to
>> the OS when you try and open thousands of files in one process.
>>
>> I ran this once to create 25,000 databases
>> ./db 25000
>> ls -l file* | wc -l
>> 25000
>> The tested opening them
>> time ./db 5000
>> 1.602s
>>
>> time ./db 1
>> 5.357s
>>
>> time ./db 15000
>> 11.280s
>>
>> time ./db 2
>> 19.570s
>>
>> time ./db 25000
>> 28.166s
>>
>> #include
>> #include
>> #include
>> #include "sqlite3.h"
>>
>> int main(int argc,char *argv[])
>> {
>> int i,rc;
>> sqlite3 *db;
>> for(i=0;i<atoi(argv[1]);++i) {
>> char name[4096];
>> sprintf(name,"file%d",i);
>>
>> rc=sqlite3_open_v2(name,,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|SQLITE_OPEN_NOMUTEX,NULL);
>> if (rc != SQLITE_OK) {
>> printf("%s",sqlite3_errmsg(db));
>> exit(1);
>> }
>> //sqlite3_close(db); // if you close the database it runs a LOT faster.
>> So I don't think it's sqlite3 causing it.
>> }
>> return 0;
>> }
>>
>> If you run this you'll see similar behavior
>> #include
>> #include
>> #include
>> #include
>> #include
>> #include
>> #include
>>
>> int main(int argc,char *argv[])
>> {
>> int i;
>> int fd;
>> for(i=0;i<atoi(argv[1]);++i) {
>> char name[4096];
>> sprintf(name,"%dfile",i);
>> fd=open(name,O_RDWR|O_CREAT);
>> if (fd==-1) perror("open error:");
>> write(fd,name,strlen(name));
>> //close(fd); // again it runs a lot faster if you close the file
>> descriptor each time -- so it's the OS slowing you down.
>> fsync(fd);
>> }
>> return 0;
>> }
>>
>>
>>
>>
>> Michael D. Black
>> Senior Scientist
>> NG Information Systems
>> Advanced Analytics Directorate
>>
>>
>>
>>
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
>> behalf of Terry Cumaranatunge [cumar...@gmail.com]
>> Sent: Friday, September 02, 2011 6:30 AM
>> To: sqlite-users@sqlite.org
>> Subject: EXT :[sqlite] sqlite3_open_v2 performance degrades as number of
>> opens increase
>>
>>
>> Hello,
>>
>> We have an application that creates many small databases (over 100K) to be
>> able to control the size of the database and provide more deterministic
>> performance. At process startup, it opens many of these databases in a loop
>> to keep them open for faster transaction response times. The behavior we are
>> noticing is that the it takes progressively a longer time for each
>> sqlite3_open_v2 to complete as the number of databases kept
>> opened increases. These are some of the measurements:
>>
>> 5000 DBs = 3 secs
>> 1 DBs = 11 secs
>> 15000 DBs = 35 secs
>> 2 DBs = 75 secs
>>
>> Many processes can concurrently open 5000 db's at the same time and it takes
>> about the same time as a single process doing the work. So, it doesn't
>> appear to be related to the OS related issue with the number of opens.
>>
>> The open is done as:
>> sqlite3_open_v2(dbName, db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX,
>> NULL)
>>
>> We are using RHEL 6 with SQLite 3.7.7.1. Each database was created with
>> journaling mode set to WAL.
>> Does anyone know why the performance degrades this way and what can be done
>> to resolve the problem?
>> ___
>> 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
>> ___
>> 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
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_open_v2 performance degrades as number of opens increase
Hello, We have an application that creates many small databases (over 100K) to be able to control the size of the database and provide more deterministic performance. At process startup, it opens many of these databases in a loop to keep them open for faster transaction response times. The behavior we are noticing is that the it takes progressively a longer time for each sqlite3_open_v2 to complete as the number of databases kept opened increases. These are some of the measurements: 5000 DBs = 3 secs 1 DBs = 11 secs 15000 DBs = 35 secs 2 DBs = 75 secs Many processes can concurrently open 5000 db's at the same time and it takes about the same time as a single process doing the work. So, it doesn't appear to be related to the OS related issue with the number of opens. The open is done as: sqlite3_open_v2(dbName, db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX, NULL) We are using RHEL 6 with SQLite 3.7.7.1. Each database was created with journaling mode set to WAL. Does anyone know why the performance degrades this way and what can be done to resolve the problem? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
