Fix performance regression affecting Banshee

Ubuntu, let's roll some useful updates.

There are probably people here because of the OMG! Ubuntu! Article. Please simply mark yourselves as affected if you are, if you wish to receive updates on when this becomes available for you simple subscribe to the bug and you will get emails with instructions when they are ready.

Any questions and non-essential comments should be posed in the comment section on the OMG! Ubuntu! article.

http://www.omgubuntu.co.uk/2010/12/help-improving-banshees-performance-on-maverick/

Thank you.

here to.

I have been advice that this needs to go through something Ubuntu calls the SRU process see: https://wiki.ubuntu.com/StableReleaseUpdates

I've rolled the natty package into a ppa for maverick here:
https://launchpad.net/~smorar/+archive/random

This is an excellent contribution!

Thank you for reporting this to Ubuntu. Unfortunately, we cannot drop a new version of sqlite3 into a stable release (see https://wiki.ubuntu.com/StableReleaseUpdates) without a lot of testing since so many apps use sqlite. Also, per http://sqlite.org/news.html, the sqlite authors say upgrading is optional from 3.7.2 to 3.7.3 or 3.7.4. If there is an improvement to be had, it would be much better to be able to find that commit from sqlite that fixes it and have a test case to verify that it does indeed solve a problem. A minimal change is desired for a stable release.

I would frankly prefer a well tested rollout of 3.7.4, cherrypicking the specific commits that fix this seems.. problematic given upstreams tools.

Regardless nobody has reported any regression in Banshee or outside of it in all the time we have recommended users upgrade to 3.7.3+. Nothing in the commit logs for SQLite seems overtly scary and much of it downright desirable.

I think providing we can get this in -proposed and leave it till comfort is found to unleash it as a stable update things will be great. I would be happy to help point users who report bugs to Banshee to testing procedures, and have them provide feedback. I am however categorically not subjecting myself to Fossil to cherrypick any patch(es), it is time away from handling Banshee bugs I cannot afford nor a task I am at all qualified to do, I am not after all the SQLite maintainer. I am merely looking to fix a case of Ubuntu making Banshee broken in my role as upstream bug triager and developer.

I agree with Micah about not performing an SRU with the latest sqlite release.
The more appropriate route would be to get 3.7.4 it into -backports.

That said, I am keen on figuring out what patch fixes the performance regression.
David, are you able to come up with a robust test case that demonstrates the performance regression?
The kind of thing that I'm looking for is the SQL query that banshee makes which is slow in sqlite 3.7.2, which is improved in sqlite 3.7.4.

The problem in question seems to rear it's head most obviously with the Shuffle modes according to my bug reports:

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomByAlbum.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomByArtist.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomByRating.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomByScore.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomBySlot.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

http://git.gnome.org/browse/banshee/tree/src/Core/Banshee.Services/Banshee.Collection.Database/RandomByTrack.cs?id=68e89a4d390ec16be14114cb7ed8a29aa2038e95

SQL queries are embedded in the code. All that should be needed to reproduce would be a Banshee 1.8.0 database.

I have found two performance related checkins since 3.7.2:
Fixed in 3.7.3: http://www.sqlite.org/src/info/ece641eb89
Fixed in 3.7.4: http://www.sqlite.org/src/info/1e0db99797

These commits relate to the use of the WHERE clause in SQL queries.

Only the 3.7.3 change should strictly be needed as users have reported improvements with just 3.7.3 (which is also what Laney states was the first SQLite 3.7.x release without regressions he was aware of).

Seems like we have a candidate, I don't have a maverick machine handy would I have plenty of users. A build with that enabled would be very helpful, I can point users towards it and see if their problem is solved, have them report back here.

Upon further investigation, the 3.7.4 fix is only a testcase.

I am busy patching the 3.7.2 package, and will upload to a ppa for
testing.

I have patched the maverick package and uploaded it to the following
ppa: https://launchpad.net/~smorar/+archive/bugfixes/+packages

Once it's built, could you test it to check that the fix works?

By performing a qualitative test using shuffle by score and then pressing next, the patch seems to deal with this issue.

Can someone else also verify that this fix works using the package in the ppa, before I subscribe ubuntu-sponsors?

TEST CASE:

1) Download attachments: banshee.db and banshee-test.sql
2) Run the following command:

time sqlite3 banshee.db < banshee-test.sql

My results using sqlite3 3.7.2-1 from maverick:
real 0m8.317s
user 0m1.840s
sys 0m0.004s

My results using sqlite3 3.7.2-1ubuntu0.1 from ppa:smorar/bugfixes,
patched with the attached patch:
real 0m0.202s
user 0m0.176s
sys 0m0.012s

Note the improvement in execution time

the issue is fixed in natty

Thank you for your work, I've sponsored the debdiff now

The diff looks fine, but please reupload with a fixed bug reference in the changelog.

sorry Sameer for not commenting but I've fixed and reuploaded the update already

Accepted sqlite3 into maverick-proposed, the package will build now and be available in a few hours. Please test and give feedback here. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Thank you in advance!

The proposed package works for me

My results using the testcase are as follows:
sqlite3 3.7.2-1 from maverick:

real 0m8.468s
user 0m8.193s
sys 0m0.016s

sqlite3 3.7.2.1ubuntu0.1 from maverick-proposed:

real 0m0.463s
user 0m0.184s
sys 0m0.012s

I would think due to the nature of this fix, someone else should verify.

Fair enough. Re-tagging that verification is needed.

I've following the test case and the results are:

with the current sqlite on Maverick:

real 0m4.811s
user 0m4.748s
sys 0m0.012s

with the proposed version:

real 0m0.113s
user 0m0.104s
sys 0m0.004s

Tested Banshee as well and you can feel that the performance is improved specially when analyzing the whole library. Marking this as verification done, thanks all.

Thank you on behalf of the Banshee team, our Maverick testers for the Natty switch to Banshee will be able to produce far more useful feedback to us now.

I was affected too and i solved it with Sameer Morar's PPA

1) exit banshee

2) add the PPA: ppa:smorar/random

3) update your system

Step 2 and 3 can be easily performed in a gnome-terminal thusly:

sudo apt-add-repository ppa:smorar/random

sudo apt-get update && sudo apt-get upgrade

Hi gsiliceo

Could you rather use the package that has been uploaded to the maverick-proposed (https://wiki.ubuntu.com/Testing/EnableProposed) repository. Additional verification will help get the fixed package into maverick-updates sooner.

This bug was fixed in the package sqlite3 - 3.7.2-1ubuntu0.1

---------------
sqlite3 (3.7.2-1ubuntu0.1) maverick-proposed; urgency=low

  * debian/patches/21-688781-where-performance-regression.patch:
    Backport a fix for a performance regression relating to SQL queries
    using the WHERE conditional (lp: #688781). Patch adapted from
    upstream commit: ece641eb89
 -- Sameer Morar <email address hidden> Mon, 13 Dec 2010 18:01:14 +0200