Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 16:37, Simon Slavin wrote: >On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: >> Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. >> When in one of the multithreaded modes, that query utilizes an average of >> 60% CPU, compared to 12% when running singlethreaded. >So if I understand this right, SQLite in multithread mode can itself use >multiple threads at once, which means it can use many cores at once, >which means it might be faster, most likely for complicated queries which >involve lots of different things to be done. >I'm slightly stunned. That had never occurred to me. Thank you. Only sort operations are candidates for internal multi-threading in SQLite at the moment and then only if all the following conditions are met: - the amount of data to be sorted exceeds page_size * min(cache_size, pmasz) bytes - the library is compiled with SQLITE_DEFAULT_WORKER_THREADS greater than 0 (the default is 0) OR pragma threads=X; is used to set the default number of threads to a value greater than 0 at runtime for a connection OR sqlite3_limit(db, SQLITE_LIMIT_WORKER_THREADS ...) C interface is used to set a >0 number of threads for a connection - SQLITE_MAX_WORKER_THREADS is greater than 0 (the default is 8) - the threading mode is not SQLITE_SINGLETHREAD -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
Another point being, fully normalized data can be a bear to extract. I see penalities in my humble database reports, so I leave 3rd and 4th normalization for managers wish lists. ;) On Fri, Dec 27, 2019, 6:37 PM Simon Slavin wrote: > On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: > > > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. > When in one of the multithreaded modes, that query utilizes an average of > 60% CPU, compared to 12% when running singlethreaded. > > So if I understand this right, SQLite in multithread mode can itself use > multiple threads at once, which means it can use many cores at once, which > means it might be faster, most likely for complicated queries which involve > lots of different things to be done. > > I'm slightly stunned. That had never occurred to me. Thank you. > ___ > 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
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When > in one of the multithreaded modes, that query utilizes an average of 60% CPU, > compared to 12% when running singlethreaded. So if I understand this right, SQLite in multithread mode can itself use multiple threads at once, which means it can use many cores at once, which means it might be faster, most likely for complicated queries which involve lots of different things to be done. I'm slightly stunned. That had never occurred to me. Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 14:19, Simon Slavin wrote: >On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: >> Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 >minutes. (Perhaps it disables some of the internal multithreaded sorters >-- I don't know). >Can anyone explain this ? The increase was in the summarization query which basically does a group by/order by of the ~11 million row run results into an ~25,000 row summary. This is a significant amount of data to group and sort, and since the database model is fully relational (that is it is normalized to 4th normal) that is a LOT of data to sort since indexes are non-helpful. Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When in one of the multithreaded modes, that query utilizes an average of 60% CPU, compared to 12% when running singlethreaded. (Looking at that query again I found an error in the group by/order by that was causing SQLite to have to sort twice -- once for the group by then a partial sort for a non-matching order by -- making them the same chopped the time in half again). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: > Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes. > (Perhaps it disables some of the internal multithreaded sorters -- I don't > know). Can anyone explain this ? (To save you reading all the stuff I snipped, it's one extremely complicated query which takes 2 minutes in multi-threaded mode.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 10:29, Cecil Westerhof wrote: >Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : >> On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: >>> My applications only use one thread (for the db stuff). Would it be a >>> good idea to switch to single-thread mode, or does that not give a real >>> performance improvement? >>> On a desktop computer, or a mobile phone, the increase in speed is not >>> large. Maybe a few percent. If your application is already fast >>> enough. >> to please your users, I would not do the switching. >OK, thanks. I will not bother about that then. ;-) There are, of course, other considerations. For example, I wrote a program which runs initial connection packets against some firewall rules. The current database size is ~11 million initial packets and ~2000 rules. Basically, running the entire thing is one (admittedly large and complex) SQL statement. The initial total runtime was about 25 minutes (includes a couple of other sumarization steps -- about 22 minutes spent just running the ruleset). After redesigning the query the time to run it dropped to 23 seconds for a total elapsed time of 2 minutes. Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes. (Perhaps it disables some of the internal multithreaded sorters -- I don't know). Setting "MUTITHREAD" shaved an additional 50 nanoseconds off the runtime. The moral of the story is that it is (in this case) not worth changing the default mode of SERIALIZED and that it may not have the result you intend. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : > On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > > > My applications only use one thread (for the db stuff). Would it be a > good idea to switch to single-thread mode, or does that not give a real > performance improvement? > > On a desktop computer, or a mobile phone, the increase in speed is not > large. Maybe a few percent. If your application is already fast enough. > to please your users, I would not do the switching. > OK, thanks. I will not bother about that then. ;-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > My applications only use one thread (for the db stuff). Would it be a good > idea to switch to single-thread mode, or does that not give a real > performance improvement? On a desktop computer, or a mobile phone, the increase in speed is not large. Maybe a few percent. If your application is already fast enough. to please your users, I would not do the switching. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
