Hello, We are also trying to automatically narrow down the root cause of the performance regression by using traditional statistical debugging technique. (e.g., http://people.cs.uchicago.edu/~shanlu/preprint/oopsla161-song.pdf)
From the process, statistical debugger returns file/function name which is closely related with the problem. To support your analysis, we would like to share the result that we acquired from the debugger. We do analysis on queries from our second/third report: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083868.html http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083970.html 1) 6.sql (second report) - bisect commit: 57eb2abd5b - stat-dbg result: + file: select.c + function: sqlite3Select ==> especially places near /* Do the constant propagation optimization */ causes slow down 2) 19.sql (second report) - bisect commit: 7d9072b027 - stat-dbg result: + file: expr.c + function: codeApplyAffinity ==> removal of this function introduces slow down 3) 10002.sql (third report) - bisect commit: 9fb646f29c - stat-dbg result: + file: expr.c + function: sqlite3VdbeJumpHere, sqlite3VdbeAddOp0 4) 10052.sql (third report) - bisect commit: 7153552bac - stat-dbg result: + file: expr.c + function: sqlite3VdbeJumpHere, sqlite3VdbeAddOp0 Hope this help for your further analysis. Best regards, Jinho Jung On Sat, Apr 6, 2019 at 9:47 AM Jinho Jung <jinho...@usc.edu> wrote: > > Hello, > > We are submitting the third report with two more interesting cases with > bisecting result using "SQL-Perf-Fuzzer". > > This is the timeline of our activities: > > [Apr 1, 2019] Submitted 1st report (3 queries, 1 bisected commits) > ( > http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083863.html > ) > ==> confirmed the problem with correct bisect result (Apr/1) > > [Apr 2, 2019] Submitted 2nd report: 2 queries, 2 unique bisected commits > ( > http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083868.html > ) > ==> not yet confirmed > > [Apr 5, 2019] Submitted 3rd report (2 queries, 2 unique bisected commits) > ==> this report > > > Here are the steps for reproducing our observations. All steps are same > except for the link to download new test-cases: > > [Our test environment] > * Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon > Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux" > * Database: TPC-C benchmark > > [Setup Test Environment] > > 1. build SQLite 3.27.2 (verion of Feb 2019) > $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip > $ unzip sqlite-src-3270200.zip > $ mv sqlite-src-3270200 sqlite327 > $ cd sqlite327 > $ ./configure > $ make > $ cd .. > > 2. build SQLite 3.23.0 (verion of Apr 2018) > $ wget https://www.sqlite.org/2018/sqlite-src-3230000.zip > $ unzip sqlite-src-3230000.zip > $ mv sqlite-src-3230000 sqlite323 > $ cd sqlite323 > $ ./configure > $ make > $ cd .. > > 3. download tpc-c for sqlite3 (scale-factor of 1) > $ mkdir testcase > $ cd testcase > > $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz > $ tar xzvf tpcc_sqlite.tar.gz > > ; download regression queries > $ wget https://gts3.org/~/jjung/sqlite/report3.tar.gz > $ tar xzvf report1.tar.gz > $ cd .. > > 4. launch two SQLites > - start > $ sqlite327/sqlite3 testcase/test.db > $ sqlite323/sqlite3 testcase/test.db > > - for each DB, set up timer > sqlite> .timer on > > - copy and paste extracted queries > > > Here’s the time taken to execute four SQL queries on old (v3.23) and newer > version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to > know which commit activate the regression. > > +----------------------+--------+ > | Query | Time | > +----------------------+--------+ > | 10002.sql (v3.23) | 789 | > | 10002.sql (v3.27.2) | 1270 | > +----------------------+--------+ > | 10052.sql (v3.23) | 3094 | > | 10052.sql (v3.27.2) | 4478 | > +----------------------+--------+ > > 1) 10002.sql shows 60% performance regression > - bisect fossil commit: > === 2018-12-31 === > [9fb646f29c] *MERGE* Merge enhancements and bug fixes from trunk. (user: > drh tags: reuse- > subqueries) > > 2) 10052.sql shows 40% performance regression > - bisect fossil commit: > === 2018-12-24 === > [7153552bac] Improvements to EXPLAIN QUERY PLAN formatting. The > MULTI-INDEX OR now > shows a separate "INDEX" subtree for each index. SCALAR SUBQUERY > entries provide a > subquery number that is related back to the .selecttrace output. (user: > drh tags: reuse- > subqueries) > > Thanks for your checking and support. > > Best regards, > Jinho Jung > > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
