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...

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

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

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

The bug is still in version 3.8.7.2

OK, I just reported the slowdown to the sqlite upstream, hopefully they will come up with something.

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);

Another possible fix is to run:

(4)   ANALYZE CoreCache;

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

Thanks for clarification, Richard.

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

Can you test this scratch build?

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

Upstream banshee thinks it might resolve the issue.

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

Yes, that patch and the corresponding hyena change. I'll go ahead and push this as an update and you can test there.

*** Bug 1174025 has been marked as a duplicate of this bug. ***

See the upstream bug: https://bugzilla.gnome.org/show_bug.cgi?id=740879

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

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).

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.

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

I see the same behaviour. There is also already a new bug report about it, see bug #1299573.

(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.

We could compare sqlite_source_id() instead ? month number and day of the month are properly zero-padded for string comparison.

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.

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.

banshee-2.6.2-15.fc22.1 has been submitted as an update to Fedora 22. https://bodhi.fedoraproject.org/updates/FEDORA-2016-a119794020

banshee-2.6.2-15.fc23 has been submitted as an update to Fedora 23. https://bodhi.fedoraproject.org/updates/FEDORA-2016-5f413ccc41

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

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

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.

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.