Bug 1161844 - huge slowdown in banshee with sqlite 3.8.7-1.fc21
Summary: huge slowdown in banshee with sqlite 3.8.7-1.fc21
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Fedora
Classification: Fedora
Component: banshee
Version: 23
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Tom "spot" Callaway
QA Contact: Fedora Extras Quality Assurance
URL:
Whiteboard:
: 1174025 (view as bug list)
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-11-08 12:33 UTC by Fabrice Bellet
Modified: 2016-02-27 01:19 UTC (History)
10 users (show)

Fixed In Version: banshee-2.6.2-8.fc21 banshee-2.6.2-15.fc23 banshee-2.6.2-15.fc22.1
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2016-02-26 19:25:15 UTC
Type: Bug


Attachments (Terms of Use)
sanitized banshee.sql.bz2 database (71.72 KB, application/x-bzip)
2014-11-08 18:39 UTC, Fabrice Bellet
no flags Details
sql queries executed by banshee at startup (1.14 KB, text/plain)
2014-11-08 18:45 UTC, Fabrice Bellet
no flags Details


Links
System ID Priority Status Summary Last Updated
GNOME Bugzilla 741274 None None None Never

Description Fabrice Bellet 2014-11-08 12:33:35 UTC
Hi!

I noticed a huge slowdown when banshee makes queries to its sqlite db at startup since sqlite moved from 3.8.6-2.fc21 to 3.8.7-1.fc21. Here is the related log :

[4 Debug 13:24:27.263] Executed in 12819ms
                    DELETE FROM CoreCache WHERE ModelID = 9;
                        INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, CoreTracks.TrackID
                FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks
                WHERE CoreTracks.Year IN
                    (SELECT CoreTracks.Year FROM CoreTracks, CoreCache
                        WHERE CoreCache.ModelID = 371 AND
                              CoreCache.ItemID = CoreTracks.TrackID )
                    ORDER BY Year

Reverting to 3.8.6, gives back a fast answer :

[4 Debug 13:21:05.433] Executed in 24ms
                    DELETE FROM CoreCache WHERE ModelID = 9;
                        INSERT INTO CoreCache (ModelID, ItemID) SELECT 9, CoreTracks.TrackID
                FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks
                WHERE CoreTracks.Year IN
                    (SELECT CoreTracks.Year FROM CoreTracks, CoreCache
                        WHERE CoreCache.ModelID = 371 AND
                              CoreCache.ItemID = CoreTracks.TrackID )
                    ORDER BY Year

Comment 1 Fabrice Bellet 2014-11-08 18:39:18 UTC
Created attachment 955349 [details]
sanitized banshee.sql.bz2 database

Okay, I think I could isolate the bug. It is related to the "ANALYZE" statement. Consider this sqlite3 database...

Comment 2 Fabrice Bellet 2014-11-08 18:45:17 UTC
Created attachment 955350 [details]
sql queries executed by banshee at startup

Here is the slowdown that happens _after_ the ANALYZE statement has been executed:

[bellet@helix banshee-1]$ rm -f banshee-clean.db ; bzcat banshee.sql.bz2 | sqlite3 banshee-clean.db 
[bellet@helix banshee-1]$ rpm -q sqlite
sqlite-3.8.7-1.fc21.x86_64
[bellet@helix banshee-1]$ time sqlite3 banshee-clean.db < foo.sql 

real	0m0.036s
user	0m0.031s
sys	0m0.005s
[bellet@helix banshee-1]$ echo "analyze;" | sqlite3 banshee-clean.db
[bellet@helix banshee-1]$ time sqlite3 banshee-clean.db < foo.sql 

real	0m12.877s
user	0m12.864s
sys	0m0.006s

Comment 3 alexf 2014-11-21 19:43:20 UTC
This is happening on F20 as well, which also has same sqlite version.

$ rpm -q sqlite
sqlite-3.8.7-1.fc20.x86_64

$ rpm -q banshee
banshee-2.6.2-2.fc20.x86_64

Comment 4 Jan Staněk 2014-11-25 13:25:23 UTC
I just submitted an update for sqlite to latest upstream version (3.8.7.2) [1]. When it lands in testing (should be tommorow), could you please test this issue with it?

The command for installing it should be
# yum --enable-repo=updates-testing update sqlite

[1] https://admin.fedoraproject.org/updates/sqlite-3.8.7.2-1.fc21,spatialite-tools-4.2.0-5.fc21?_csrf_token=e6cb9b5f0dbe1802d39fa11c1ca6f0d73d66b144

Comment 5 Fabrice Bellet 2014-11-27 08:38:51 UTC
The bug is still in version 3.8.7.2

Comment 6 Jan Staněk 2014-12-09 11:13:51 UTC
OK, I just reported the slowdown to the sqlite upstream, hopefully they will come up with something.

Comment 7 Richard Hipp 2014-12-09 13:29:28 UTC
The slow query is this:

SELECT 9, CoreTracks.TrackID
  FROM (SELECT MIN(CoreTracks.TrackID) AS TrackID, CoreTracks.Year
          FROM CoreTracks GROUP BY CoreTracks.Year) AS CoreTracks
  WHERE CoreTracks.Year IN (SELECT CoreTracks.Year
                              FROM CoreTracks, CoreCache
                             WHERE CoreCache.ModelID = 371
                               AND CoreCache.ItemID = CoreTracks.TrackID )
 ORDER BY Year;

To make it go faster, consider using this rewrite:

SELECT 9, MIN(TrackID), Year
  FROM CoreTracks
 GROUP BY Year
HAVING Year IN (SELECT CoreTracks.Year
                  FROM CoreTracks, CoreCache
                 WHERE CoreCache.ModelID = 371
                   AND CoreCache.ItemID = CoreTracks.TrackID )
 ORDER BY Year;

SQLite version 3.8.6 used to do this rewrite for you automatically.  But, though the rewrite works in this case, it turns out not to be correct in every case, and so we had to disable the optimization that does this rewrite in 3.8.7.  Perhaps some future version of SQLite will be able to selectively reenable this optimization, in cases where it is appropriate.  But that is not something that is likely to happen soon.

Other possible fixes:

(2) Do not run ANALYZE.  And delete any ANALYZE result at application start using:

DROP TABLE IF EXISTS sqlite_stat1;
DROP TABLE IF EXISTS sqlite_stat3;
DROP TABLE IF EXISTS sqlite_stat4;
ANALYZE sqlite_master;

(3) Create an appropriate index on the temporary CoreCache table:

CREATE INDEX CoreCache_ix1 ON CoreCache(ModelId,ItemId);

Comment 8 Richard Hipp 2014-12-09 14:25:30 UTC
Another possible fix is to run:

(4)   ANALYZE CoreCache;

After the CoreCache table has been populated, but before running the slow query.

Comment 9 Jan Staněk 2014-12-09 16:14:13 UTC
Thanks for clarification, Richard.

Seeing that the fix should probably be in the banshee application, I'm switching this bug to that component.

Comment 10 Tom "spot" Callaway 2014-12-10 16:28:05 UTC
Can you test this scratch build?

http://koji.fedoraproject.org/koji/taskinfo?taskID=8337956

Upstream banshee thinks it might resolve the issue.

Comment 11 Fabrice Bellet 2014-12-12 20:22:22 UTC
Hi Tom,

I could not find the src.rpm in the scratch build, but if the patch is this one, then I confirm that it works for me :

https://git.gnome.org/browse/banshee/commit/?h=stable-2.6&id=700ad9b1686d0b46073b7b2e6cea8e01e30d5f52

Comment 12 Tom "spot" Callaway 2014-12-12 20:49:37 UTC
Yes, that patch and the corresponding hyena change. I'll go ahead and push this as an update and you can test there.

Comment 13 Samuel Gyger 2014-12-14 22:21:09 UTC
*** Bug 1174025 has been marked as a duplicate of this bug. ***

Comment 14 Samuel Gyger 2014-12-14 22:23:28 UTC
See the upstream bug: https://bugzilla.gnome.org/show_bug.cgi?id=740879

Comment 15 Fedora Update System 2014-12-15 14:32:57 UTC
banshee-2.6.2-8.fc21 has been submitted as an update for Fedora 21.
https://admin.fedoraproject.org/updates/banshee-2.6.2-8.fc21

Comment 16 Fedora Update System 2014-12-17 04:49:07 UTC
Package banshee-2.6.2-8.fc21:
* should fix your issue,
* was pushed to the Fedora 21 testing repository,
* should be available at your local mirror within two days.
Update it with:
# su -c 'yum update --enablerepo=updates-testing banshee-2.6.2-8.fc21'
as soon as you are able to.
Please go to the following url:
https://admin.fedoraproject.org/updates/FEDORA-2014-17062/banshee-2.6.2-8.fc21
then log in and leave karma (feedback).

Comment 17 Fedora Update System 2014-12-20 08:32:47 UTC
banshee-2.6.2-8.fc21 has been pushed to the Fedora 21 stable repository.  If problems still persist, please make note of it in this bug report.

Comment 18 Fabrice Bellet 2016-02-04 20:25:42 UTC
Hi!

unfortunately the bug seems to be back, since sqlite3 >= 3.10.0 in Fedora 23, probably because the upstream fix compares sqlite3 version textually, and not numerically:


sqlite> SELECT sqlite_version ();
3.10.2
sqlite> SELECT sqlite_version () >= '3.8.1';
0
sqlite> SELECT sqlite_version () >= '3.10.0';
1

Comment 19 alexf 2016-02-04 20:30:23 UTC
I see the same behaviour. There is also already a new bug report about it, see bug #1299573.

Comment 20 Richard Hipp 2016-02-04 20:44:46 UTC
(In reply to Fabrice Bellet from comment #18)
> Hi!
> 
> unfortunately the bug seems to be back, since sqlite3 >= 3.10.0 in Fedora
> 23, probably because the upstream fix compares sqlite3 version textually,
> and not numerically:
> 
> 
> sqlite> SELECT sqlite_version ();
> 3.10.2
> sqlite> SELECT sqlite_version () >= '3.8.1';
> 0
> sqlite> SELECT sqlite_version () >= '3.10.0';
> 1

The preferred way to do this sort of thing with the C api sqlite3_libversion_number() which returns an integer that always increases with each new release.

Or, better, you could rewrite the query as I suggested in comment 7 and then the query would be fast in *ALL* versions of SQLite and you wouldn't need to do any conditional logic at all.

Comment 21 Fabrice Bellet 2016-02-04 21:32:44 UTC
We could compare sqlite_source_id() instead ? month number and day of the month are properly zero-padded for string comparison.

Comment 22 alexf 2016-02-05 00:42:23 UTC
What Frabrice proposed in comment 7 and commment 20 seems to be more sane in order to make sure this bug doesn't come around in a few months for a third time.

Comment 23 Tom "spot" Callaway 2016-02-12 19:52:45 UTC
Rewriting the query is a non-trivial patch, and while I do not disagree that it seems to be the right path forward, I'm not comfortable doing that without it first being merged upstream. I would strongly recommend that Richard submit a patch upstream to rewrite the query.

For now, I'm just going to fix the version check as proposed in the upstream bug ticket.

Comment 24 Fedora Update System 2016-02-12 20:29:14 UTC
banshee-2.6.2-15.fc22.1 has been submitted as an update to Fedora 22. https://bodhi.fedoraproject.org/updates/FEDORA-2016-a119794020

Comment 25 Fedora Update System 2016-02-12 20:29:14 UTC
banshee-2.6.2-15.fc23 has been submitted as an update to Fedora 23. https://bodhi.fedoraproject.org/updates/FEDORA-2016-5f413ccc41

Comment 26 Fedora Update System 2016-02-15 04:52:46 UTC
banshee-2.6.2-15.fc22.1 has been pushed to the Fedora 22 testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2016-a119794020

Comment 27 Fedora Update System 2016-02-15 05:24:25 UTC
banshee-2.6.2-15.fc23 has been pushed to the Fedora 23 testing repository. If problems still persist, please make note of it in this bug report.
See https://fedoraproject.org/wiki/QA:Updates_Testing for
instructions on how to install test updates.
You can provide feedback for this update here: https://bodhi.fedoraproject.org/updates/FEDORA-2016-5f413ccc41

Comment 28 Fedora Update System 2016-02-26 19:25:06 UTC
banshee-2.6.2-15.fc23 has been pushed to the Fedora 23 stable repository. If problems still persist, please make note of it in this bug report.

Comment 29 Fedora Update System 2016-02-27 01:19:51 UTC
banshee-2.6.2-15.fc22.1 has been pushed to the Fedora 22 stable repository. If problems still persist, please make note of it in this bug report.


Note You need to log in before you can comment on or make changes to this bug.