sqlite3VdbeExec profiling shows new syscalls in recent 3.32.3 version
(1) By Adam Piatyszek (ediap_) on 2021-01-05 08:46:19 [link] [source]
In a commercial application, we use SQLite virtual table interface to expose a lot of data in the form of database tables to Tcl command-line interface. Some time ago we bumped SQLite from an old 3.16.2 version to 3.32.3 one. While profiling some performance issue in our vtable implementation, we noticed that there is a significant change in the profile of sqlite3VdbeExec function, which we cannot explain. With the previous sqlite3 version we used (3.16.2), all the time was spent on virtual table interface calls (vt_filter, vt_eof, vt_column, vt_data), which is what we expect. However, with the newer sqlite3 version (3.32.3), the same virtual table client implementation results in a lot of "syscall" samples caught as part of sqlite3VdbeExec profile. See the two example PDF graphs from gperf for the same exact experiment:
The profiled virtual table was accessing an in memory sample of around 600.000 records with two columns delivering strings (names of queried objects).
Could anyone comment if this is expected behavior and what are these mystic "syscall" calls that takes so much CPU time of the sqlite3VdbeExec execution.
(2) By Gunter Hick (gunter_hick) on 2021-01-05 14:22:48 in reply to 1 [link] [source]
Are you sure that the query plan and SQLite bytecode have not changed? These are available by prepending EXPLAIN or EXPLAIN QUERY PLAN to the SELECT statement. What SYSCALLs are actually made? This should be available by running the application under control of strace (if running on a *nix system). Do they also occur when the query is run in the SQLite shell?
(3) By Adam Piatyszek (ediap_) on 2021-01-07 13:03:15 in reply to 2 [link] [source]
I need to do more experiments to try to investigate what kind of SYSCALLs these are, but because this is in a bigger proprietary application, there might be a lot of noise from strace.
I am not able to verify the same when using SQLite shell, since the data model and virtual table implementation is tightly coupled with the rest of this bigger application. Hopefully, we have Tcl shell built-in, thus the profiling data was actually obtained using sqlite3 db::eval Tcl interface.
Anyway, the query was really simple, just "SELECT name, leafname FROM pin_list", thus I do not see any impact from SQLite planner (no constraints, no joined tables, no sorting).
(4) By Gunter Hick (gunter_hick) on 2021-01-07 14:06:08 in reply to 3 [source]
We have found it very convenient to dynamically load our virtual table implementations. That way we can test them in isolation using the SQLite shell alone.