Fix performance regression affecting Banshee

[David Nielsen]

Binary package hint: sqlite3

In Banshee we are seeing a number of severe performance problems with the SQLite 3.7.2 which is shipped in Maverick (like we did with 3.7.1 and 3.7.0 before it). These are massively improved with 3.7.3, and the changes for 3.7.4 do not seem harmful in casual testing during development and bug triaging Banshee on Natty so I should suggest bringing it up directly to 3.7.4 in compliance with the advice on SQLite.org currently available.

A sample of the type of bugs you can make disappear for us:
https://bugzilla.gnome.org/show_bug.cgi?id=631180

[n3rd (n3rd)]

Ubuntu, let's roll some useful updates.

[David Nielsen (davidnielsen-deactivatedaccount)]

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.

[Carlos Alexandro Becker (caarlos0)]

here to.

[David Nielsen (davidnielsen-deactivatedaccount)]

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

[Sameer Morar (smorar)]

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

[n3rd (n3rd)]

This is an excellent contribution!

[Micah Gersten (micahg)]

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.

[David Nielsen (davidnielsen-deactivatedaccount)]

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.

[Sameer Morar (smorar)]

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.

[David Nielsen (davidnielsen-deactivatedaccount)]

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.

[Sameer Morar (smorar)]

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.

[David Nielsen (davidnielsen-deactivatedaccount)]

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.

[Sameer Morar (smorar)]

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.

[Sameer Morar (smorar)]

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?

[Sameer Morar (smorar)]

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

[Sameer Morar (smorar)]

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

[Sameer Morar (smorar)]

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

[Sebastien Bacher (seb128)]

the issue is fixed in natty

[Sebastien Bacher (seb128)]

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

[Martin Pitt (pitti)]

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

[Sebastien Bacher (seb128)]

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

[Martin Pitt (pitti)]

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!

[Sameer Morar (smorar)]

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

[Micah Gersten (micahg)]

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

[Sameer Morar (smorar)]

Fair enough. Re-tagging that verification is needed.

[Pedro Villavicencio (pedro)]

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.

[David Nielsen (davidnielsen-deactivatedaccount)]

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.

[gsiliceo (nombre-falso)]

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

[Sameer Morar (smorar)]

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.

[Launchpad Janitor (janitor)]

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