I have a set of several million database files sitting on my filesystem. Each thread will open a previously unprocessed database file, do some queries, close the database, and move on to the next unprocessed database file.
Jason Lee ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Monday, April 22, 2019 3:13:57 PM To: SQLite mailing list Subject: Re: [sqlite] Multiple Independent Database Instances This is somewhat unclear. You make two conflicting statements: "I have been testing with 16, 32, and 48 threads/databases at once ..." and "time it takes for all of the threads to just open all (millions) of the databases" So, are you: (a) opening one independently and uniquely named database per thread as would be apparent from the first conflicting statement above; or, (b) opening the same "millions" of databases per thread as indicated by the second conflicting statement above ? Per my testing the time taken to spin up a thread and open a database with a unique database name is constant and linear up to the thread limit of a process (about 800 threads). This is even true if you execute SQL against the connection within that thread and also count that execution time in the "Time Taken". --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Lee, Jason >Sent: Monday, 22 April, 2019 14:08 >To: SQLite mailing list >Subject: Re: [sqlite] Multiple Independent Database Instances > >Thanks for the quick responses! > > >I am on a machine with many many cores, 500GB RAM, and lots of NVMe >drives raided together, so the system should not be the issue. I have >been testing with 16, 32, and 48 threads/databases at once, and the >cumulative time it takes for all of the threads to just open all >(millions) of the databases goes from 1200 seconds to 2200 seconds to >3300 seconds. > > >As mentioned, this is likely to be something else, but I was hoping >that I was somehow using SQLite wrong. > > >Jason Lee > >________________________________ >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on >behalf of Jens Alfke <j...@mooseyard.com> >Sent: Monday, April 22, 2019 12:52:28 PM >To: SQLite mailing list >Subject: Re: [sqlite] Multiple Independent Database Instances > > > >> On Apr 22, 2019, at 11:39 AM, Lee, Jason <jason...@lanl.gov> wrote: >> >> Hi. Are there any gotchas when opening multiple independent >databases from within one process using the C API? > >Do you mean different database files, or multiple connections to the >same file? > >> I am opening one database per thread in my code, and noticed that >sqlite3_open_v2 and sqlite3_close slow down as the number of threads >increase, indicating there might be some resource contention >somewhere, even though the databases should be independent of each >other. > >How many databases/threads? With huge numbers I’d expect slowdowns, >since you’ll be bottlenecking on I/O. > >But otherwise there shouldn’t be any gotchas. I would troubleshoot >this by profiling the running code to see where the time is being >spent. Even without knowledge of the SQLite internals, looking at the >call stacks of the hot-spots can help identify what the problem is. > >—Jens >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
