On 08/15/2016 12:02 AM, Венцислав Русев wrote:
My computer has 4 cores. I have compile sqlite like this "gcc
-DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4
shell.c sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and
found that "pragma threads = 4" doesn't decrease runtime of the query
that sorts 1 milion records.
SQLite version 3.8.8
sqlite> pragma threads;
4
sqlite> CREATE TABLE event (
ID INTEGER PRIMARY KEY NOT NULL,
date INTEGER NOT NULL,
value INTEGER NOT NULL );
sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1;
0|0|0|SCAN TABLE event
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.493 user 2.426000 sys 0.049000
sqlite> pragma threads = 0;
0
sqlite> SELECT ID FROM event ORDER BY date LIMIT 1;
4101021
Run Time: real 2.484 user 2.421000 sys 0.044000
To sum it up I have:
- multi core cpu;
- sqlite that is compiled to use "auxiliary threads";
- a table with 1 million records;
- a query that scans through the table, sorts all records and
outputs the first ID.
sqlite doesn't use these "auxiliary threads" that sqlite docs talks
about and the runtime of that query is the same with or without
"pragma threads = 4".
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Some clarifications:
- OS: Ubuntu 14.04 LTS;
- "PRAGMA compile_options" gives "SYSTEM_MALLOC THREADSAFE=1";
- when sqlite shell is running and no query is running at the moment
the OS reports that it has only one thread;
On 09/16/2016 11:29 AM, Richard Hipp wrote:
The algorithm used for "ORDER BY ... LIMIT N" uses much less memory
than a full-up "ORDER BY" because is only keeps track of the top N
entries seen so far, discarding the rest. But it also only uses a
single thread. If you want multiple threads to be used, you'll need to
drop the LIMIT, though I imagine that would defeat your purpose, no?
Richard Hipp has a point. I searched the sources and found that the
sorting algorithm uses threads only if there isn't a LIMIT clause.
To be exact the sources say "This file contains code for the VdbeSorter
object, used in concert with a VdbeCursor to sort large numbers of keys
for CREATE INDEX statements or by SELECT statements with ORDER BY
clauses that cannot be satisfied using indexes and without LIMIT clauses."
But I'm wondering what if your query is like so "SELECT ID FROM event
ORDER BY date LIMIT 1 OFFSET 1000000;"? Isn't it better to sort the
results using available threads?
On 09/16/2016 12:13 PM, Adam Devita wrote:
Have you proven that the cpu is the bottleneck? Sorting a million rows
seems like a lot, but even older single core cpus may be capable of 2
billion ops per second. [I apologize if this has been sorted out
already.... I've got about 2 days of history on the thread] regards,
Adam DeVita
This was just a test to see the performance of "PRAGMA threads".
Actually I expect to sort 10-30 million rows or more. But after the
tests I may choose the index.
Just to let you know what other tests I've conducted.
sqlite> .output stdout
sqlite> pragma threads = 4;
sqlite> select ID from event order by date;
Run Time: real 2.880 user 4.469000 sys 0.147000
sqlite> pragma threads = 0;
sqlite> select ID from event order by date;
Run Time: real 3.870 user 3.771000 sys 0.075000
Besides the runtime difference I inspected the process information while
the query is running. When "pragma threads = 4;" sqlite shell starts new
threads for sorting and stops them after the query has finished.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
