Re: Issue 3847 in clementine-player: Clementine slow with sqlite 3.8

[clementi...@googlecode.com]

Updates:
Summary: Clementine slow with sqlite 3.8
Labels: Component-MusicLibrary

Comment #6 on issue 3847 by arnaud.bienner: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

(No comment was entered for this change.)

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[clementi...@googlecode.com]

Comment #7 on issue 3847 by john.maguire: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Can you try using a package we built, rather than debian? We don't have
Jessie builds but maybe wheezy ones work:
http://builds.clementine-player.org/

For devs:
This is probably due to debian overriding the STATIC_SQLITE=ON setting and
FTS3 failing to setup properly causing us to fall back to having no text
indices.

[clementi...@googlecode.com]

Comment #8 on issue 3847 by john.maguire: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

There are jessie packages built now:
http://builds.clementine-player.org/debian-jessie/

Can you try these?

[clementi...@googlecode.com]

Comment #9 on issue 3847 by tripno...@voila.fr: Clementine slow with sqlite
3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Hi ! sorry for the delay.

I tried the jessie build (with wheezy build I'll have to downgrade
liblastfm) and It works great with libsqlite 3.8.0.2 ! the search speed is
back to usual that's mean instant. Thanks !

[clementi...@googlecode.com]

Comment #10 on issue 3847 by tripno...@voila.fr: Clementine slow with

sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Hi ! sorry for the delay.

I tried the jessie build (with wheezy build I'll have to downgrade
liblastfm) and It works great with libsqlite 3.8.0.2 ! the search speed is

back to usual that's mean instant. Thanks ! so it's a debian packaging bug ?

[clementi...@googlecode.com]

Updates:
Status: WontFix

Comment #11 on issue 3847 by john.maguire: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Yes - this is a debian packaging bug.

They either need to switch STATIC_SQLITE=ON or ensure that the qt4 sqlite
plugin is built with all the sqlite3 symbols visible.

This is not something we can fix.

[clementi...@googlecode.com]

Comment #12 on issue 3847 by arnaud.bienner: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Can someone open a bug to Debian so they can fix this?

[clementi...@googlecode.com]

Comment #13 on issue 3847 by timro...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=722471

[clementi...@googlecode.com]

Comment #14 on issue 3847 by rea...@gmail.com: Clementine slow with sqlite
3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

I have the same problem on Gentoo. Typing something in the search line
results in nothing happening for about 20 seconds, then Clementine totally
freezing for about half a minute before it finds anything. Sometimes longer.

Downgrading to dev-db/sqlite-3.7 fixes the issue.

I opened a Gentoo bug for this too:
https://bugs.gentoo.org/show_bug.cgi?id=485220

[clementi...@googlecode.com]

Comment #15 on issue 3847 by Nickollai: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Hello, i am Gentoo package maintainer and would like to know why is it
really required to use one more internal lib instead of shared system copy?
Did you try talking to sqlite or Qt devs and asking them to fix shared
libs? Do you have any suggestion about building qt and sqlite packages?

[clementi...@googlecode.com]

Comment #16 on issue 3847 by audvare: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Nickollai does setting STATIC_SQLITE=ON in /etc/portage/env actually make
an effect? Is there any way besides having a new ebuild to build without
system-lib?

I certainly would like to not use a static build in qtsql but if it works
better and makes Clementine usable it would be preferred for now.

[clementi...@googlecode.com]

Comment #17 on issue 3847 by rea...@gmail.com: Clementine slow with sqlite
3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Actually, Clementine seems to have it wrong. If it's using sqlite
functions, then it should not depend only on QtSQL. If you use a library,
you have to link against it. You should not assume that QtSQL will export
sqlite symbols to you.

[clementi...@googlecode.com]

Comment #18 on issue 3847 by john.maguire: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

But you can't guarantee that libqsqlite is dynamically linked against
libsqlite. This is more complicated than you're assuming.

[clementi...@googlecode.com]

Comment #19 on issue 3847 by rea...@gmail.com: Clementine slow with sqlite
3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Why not use sqlite directly though? Why through Qt?

[clementi...@googlecode.com]

Comment #20 on issue 3847 by master.o...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

I can confirm the same problem on the recent Fedora 20, so clementine v
1.1.1, sqlite 3.8.0.2. Attached you find callgrind-generated profiling data.

As I'm not very involved in clementine development, I'm not the one to
raise fingers, but since when was relying on statically linking against
an "ancient" version of a library a packaging problem of distributions?

Also, issue 3865 points out that v1.1.1 might have introduced this extreme
slowness bug.

Attachments:
callgrind.out.11084 946 KB

[clementi...@googlecode.com]

Comment #21 on issue 3847 by master.o...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

by the way: It seems that the fedora spec file differs greatly from the
clementine source tree spec file, see
https://apps.fedoraproject.org/packages/clementine/sources/spec/

[clementi...@googlecode.com]

Comment #22 on issue 3847 by elAndroi...@googlemail.com: Clementine slow

with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

i got the same issue on fedora 19 using clementine 1.1.1

[clementi...@googlecode.com]

Comment #23 on issue 3847 by perezme...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Having a statically-built libsqlite in Debian is definitely a non-go. That
would mean we maintainers would need to backport any possible security
issues found in libsqlite.

Is there another way to solve this?

By the way, this also means that users of clementine don't get libsqlite
security fixes.

[clementi...@googlecode.com]

Comment #24 on issue 3847 by perezme...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

By the way:

"or ensure that the qt4 sqlite plugin is built with all the sqlite3
symbols visible."

This is also a non-go, it creates unnecesary burden to maintainers. If
sqlite3 symbols happen to change we would need to change the soname of Qt.
Not funny at all :-/

[clementi...@googlecode.com]

Comment #25 on issue 3847 by arnaud.bienner: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Issue 3979 has been merged into this issue.

[clementi...@googlecode.com]

Comment #26 on issue 3847 by arnaud.bienner: Clementine slow with sqlite 3.8

[clementi...@googlecode.com]

Comment #27 on issue 3847 by manderss...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

I believe I have found the cause of this issue.

When we search the library for every word that starts with "w" the
following query is performed:

SELECT DISTINCT artist FROM songs INNER JOIN songs_fts AS fts ON
songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND
effective_compilation = 0 AND unavailable = 0

On my computer this query takes 20 seconds with sqlite 3.8.1 compared with
0.03 seconds with sqlite 3.7.17.

The problem lies in the clause "effective_compilation = 0". The column
effective_compilation is created with the properties "NOT NULL DEFAULT 0".
In contrast, similar columns (e.g. forced_compilation_on) are created
with "INTEGER NOT NULL DEFAULT 0".

From what I understand from the documentation of sqlite's type affinity,
the column "effective_compilation" will have type affinity NONE. When we
put integers in this column, they will have the storage class "integer".
This can be seen by running "SELECT typeof(effective_compilation) FROM
songs;". In this regard, the above query should not be a problem, and in
sqlite 3.7 there is none. In sqlite 3.8 however, there seems to be some
kind of type conversion going on when the "=" operator is used which slows
the query down to a crawl.

The quick and dirty solution is to change the query to use the "LIKE"
operator instead of "=". This is done in the attached patch.

A better solution is probably to recreate the "effective_compilation"
column with the properties "INTEGER NOT NULL DEFAULT 0" but as far as I can
tell this would require a complete rebuild of the songs table.


Attachments:
libraryquery.patch 591 bytes

[clementi...@googlecode.com]

Updates:
Labels: PatchAttached

Comment #28 on issue 3847 by arnaud.bienner: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

You said it takes 20 seconds with sqlite 3.8.1. How long does it take with
your patch applied?
Even if it might improve things, I'm not sure it will be as good as if
we're using FTS3 (but we can integrate your patch anyway if it makes things
better for everyone).

Btw, John started to have a look to this issue (mainly revision
4c23072bef94, and revision 3b8cde6691e7, revision 34103a11cf3f and revision
35f776001477): looks like dynamically linking against sqlite3 fixes the
issue. Maybe distros maintainers can try to integrate this and let us know
if it fixes the issue for them?

[clementi...@googlecode.com]

Comment #29 on issue 3847 by davidsansome: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Was your 20 second example using a distro-provided package, or a Clementine
you compiled yourself?
If you recompiled Clementine to apply your patch it's likely you
accidentally used the statically linked version which has FTS available and
is faster anyway.

I can't reproduce your speed difference with a minimal test case:

[david@hoover ~] sqlite3 test.db ".schema";
CREATE TABLE T (c NOT NULL DEFAULT 0);
[david@hoover ~] sqlite3 --version
3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
[david@hoover ~] time sqlite3 test.db "select count(*) from T;"
100000000

real 0m0.624s
user 0m0.192s
sys 0m0.424s
[david@hoover ~] ./sqlite-amalgamation-3080000/sqlite --version
3.8.0 2013-08-26 04:50:08 f64cd21e2e23ed7cff48f7dafa5e76adde9321c2
[david@hoover ~] time ./sqlite-amalgamation-3080000/sqlite test.db "select
count(*) from T;"
100000000

real 0m0.662s
user 0m0.200s
sys 0m0.456s

[clementi...@googlecode.com]

Comment #30 on issue 3847 by davidsansome: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Oops I forgot the "where c = 0" in that example, but the timings are still
similar:

[david@hoover ~] time sqlite3 test.db "select count(*) from T where c = 0;"
100000000

real 0m14.033s
user 0m13.577s
sys 0m0.428s
[david@hoover ~] time sqlite3 test.db "select count(*) from T where c = 0;"
100000000

real 0m14.386s
user 0m13.881s
sys 0m0.476s

[clementi...@googlecode.com]

Comment #31 on issue 3847 by davidsansome: Clementine slow with sqlite 3.8

http://code.google.com/p/clementine-player/issues/detail?id=3847

Oops I forgot the "where c = 0" in that example, but the timings are still
similar:

[david@hoover ~] time sqlite3 test.db "select count(*) from T where c = 0;"
100000000

real 0m14.033s
user 0m13.577s
sys 0m0.428s

[david@hoover ~] time ./sqlite-amalgamation-3080000/sqlite test.db "select

count(*) from T where c = 0;"
100000000

real 0m15.427s
user 0m14.957s
sys 0m0.440s

[clementi...@googlecode.com]

Comment #32 on issue 3847 by manderss...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

It was revision 4c23072bef94 that introduced the problem for me. I'm using
archlinux where the distro provided package for 1.2.1 does not include any
patches. I therefore assume that it uses the bundled sqlite that was
removed in 4c23072bef9.

I have compiled the git version of Clementine myself both with and without
the patch. For the unpatched build the library search gets really slow with
sqlite 3.8.1 but if I install sqlite 3.7.17 it returns to normal speed.
I'm using the official arch Linux packages for both sqlite versions. The
patched Clementine is equally fast with both versions.

The timings where I provided were taken directly from the sqlite shell with
a copy of my Clementine database which contains 7800 rows in the songs
table.

If I run SELECT statements with WHERE clauses on the effective_compilation
column there are no noticible speed differences e.g:

sqlite> .version
SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a

sqlite> SELECT Count(DISTINCT Artist) FROM songs WHERE
effective_compilation = 0 AND unavailable = 0;
334
CPU Time: user 0.060000 sys 0.006666

sqlite> SELECT Count(DISTINCT Artist) FROM songs WHERE
effective_compilation LIKE 0 AND unavailable = 0;
334
CPU Time: user 0.040000 sys 0.026667

The problem arises when we JOIN with the songs_fts table as in the
following queries:

sqlite> .version
sqlite> SELECT Count(DISTINCT artist) FROM songs INNER JOIN songs_fts AS

fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND

effective_compilation = 0 AND unavailable = 0;
203
CPU Time: user 21.463332 sys 0.013334

sqlite> SELECT Count(DISTINCT artist) FROM songs INNER JOIN songs_fts AS

fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND

effective_compilation LIKE 0 AND unavailable = 0;
203
CPU Time: user 0.016667 sys 0.013333

As you can see there is a considerable difference in speed when I
use "LIKE" instead of "=".

For sqlite 3.7.17, the speed of the above queries is about the same:

sqlite> .version
SQLite 3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
sqlite> SELECT Count(DISTINCT artist) FROM songs INNER JOIN songs_fts AS

fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND

effective_compilation = 0 AND unavailable = 0;
203
CPU Time: user 0.026667 sys 0.006667

sqlite> SELECT Count(DISTINCT artist) FROM songs INNER JOIN songs_fts AS

fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND

effective_compilation LIKE 0 AND unavailable = 0;
203
CPU Time: user 0.026667 sys 0.006666

In order to make the above queries to run in the sqlite shell I had to make
a library of the Clementine's unicode tokenizer and load it into the shell.
You can avoid this by creating a copy of songs_fts using the simple
tokenizer and run the queries against this table instead. The speed
difference will be of the same order of magnitude.

[clementi...@googlecode.com]

Comment #33 on issue 3847 by john.maguire: Clementine slow with sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Can you try with this patch?

Attachments:
sqlite.patch 591 bytes

[clementi...@googlecode.com]

Comment #34 on issue 3847 by ran...@randallma.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Not sure if you're aware of this, but 1.2.1 jessie builds appear to have
re-broken clementine search for me. I've since downgraded back to 1.2-64
and everything is working fine.

[clementi...@googlecode.com]

Comment #35 on issue 3847 by rolandi...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Yeah the latest builds are terribly slow on regular library searches, but
work just fine if I search "everything" (the library results show up just
fine too).

[clementi...@googlecode.com]

Comment #36 on issue 3847 by pasthe...@gmail.com: Clementine slow with
sqlite 3.8
http://code.google.com/p/clementine-player/issues/detail?id=3847

Hello,

I have just 37K songs in my library, and altering that DB column seems to
have helped.

libsqlite3-0 3.8.2-1ubuntu1, clementine 1.2.0+dfsg-2, all on Ubuntu 13.04.

(Opened the sqlite shell, sqlite3 ~/.config/Clementine/clementine.db

.schema songs

Added the INTEGER attribute (effective_compilation NOT NULL DEFAULT 0 ->
effective_compilation INTEGER NOT NULL DEFAULT 0)

sqlite3 alter tables songs rename to songstemp;

Paste the edited schema, then

insert into songs select * from songstemp;

Finally, drop table songstemp;
)