Amarok 1.4.8, mysql, and 8 to 10 s hangs every track change or rating

[Simon ESNEAULT]

Hello,

I've rebuild amarok with this debug 1 option, but on an other computer
(job's one) which is much (much) more powerful (double dual core xeon and 4
GO Ram). This one is running sqlite. Anyway each query seems to take quite a
long time (0.3 s) too, and the action to change a track also make the sytem
hangs for about 10 seconds, even on this very powerful system. So it might
definitly be the queries (mysql or slite) who are too heavy

One idea, on both computer I use exactly the same path for the database path
(so i can synch both database)
I don't know much about sql query, but i can see at the end "order by path".
All my music file path contain a lot of space and strange character, (-= =-)
in both computer, and both computer are slow on track change, sqlite or
mysql backend. Do you thing this can cause a significant difference on the
queries, compared to a clear path (aka no space) when ordering ?

Here are some examples of file path :

/home/simon/Autres/Musique/-=Albums=-/-= A =-/Aphex Twin/1995 - Classics/13
- Digeridoo Live in Cornwall 1990.flac
/home/simon/Autres/Musique/-=Albums=-/-= B =-/Boban Marković Orkestar/2002 -
Bistra Reka/14 - Bolujem ja.flac
/home/simon/Autres/Musique/-=Albums=-/-= M =-/My Bloody Valentine/1991 -
Loveless/01 - Only Shallow.flac
/home/simon/Autres/Musique/-=Albums=-/-= Y =-/Yo La Tengo/2005 - Prisoners
of Love (Disc 1)/02 - Sugarcube.flac
/home/simon/Autres/Musique/-=B.O Divers=-/-= B.O. Jeux Vidéos =-/Alex Kidd
In Miracle World (1988)/Alex Kidd in Miracle World - 02 - Start Level.mp3
/home/simon/Autres/Musique/-=B.O Divers=-/-= B.O. Films : Compilations
=-/Danny Elfman - Music for a Darkened Theatre - Volume Two (cd1)
(1996)/Danny Elfman - Music For A Darkened Theatre - Volume Two (Cd1) - 05 -
Edward Scissorhands The Grand Finale.mp3
/home/simon/Autres/Musique/-=B.O Films=-/-= 0...9 =-/8 Femmes (2001)/8
Femmes - 16 - Krishna Levy - Portrait De Gaby.mp3
/home/simon/Autres/Musique/-=Compil=-/Best of legal download 2006/Best Free
Legal Downloads 2006 - 21 - Sally Shapiro - Anorak Christmas.mp3

Attached is the log file of a track changed, with debug option set to 1.

Thanks a lot for your help ! And still apologize for french-glish
Simon

PS > Jer, i can log on the mysql server and run the command you asked, but i
don't understand how to interpret them. Both of them return very fast
answer, about 0.02 or something like this. What should I do next ? Thanks a
lot for helping

On Jan 27, 2008 11:08 PM, Jer Johnson <jer at gweep.net> wrote:

> Allo Simon,
>
> if you're compiling Amarok from source, you can add
>
> #define DEBUG 1
>
> at the top of collectiondb.cpp , for some more debug output on DB queries.
>
>
> you can check the efficiency of a MySQL query by using the 'explain'
> command.
>
> from your shell:
> $ dcop amarok collection query "explain select * from tags where 0 or (1
> and artist=2317)"
> 1
> SIMPLE
> tags
> ref
> artist_tag,tags_artist_index
> artist_tag
> 5
> const
> 22
> Using where
>
> this can also be run from the MySQL command line:
>
> mysql> explain select * from tags where artist = '2317';
>
> +----+-------------+-------+------+------------------------------+------------+---------+-------+------+-------------+
> | id | select_type | table | type | possible_keys                | key
>    | key_len | ref   | rows | Extra       |
>
> +----+-------------+-------+------+------------------------------+------------+---------+-------+------+-------------+
> |  1 | SIMPLE      | tags  | ref  | artist_tag,tags_artist_index |
> artist_tag | 5       | const |   22 | Using where |
>
>
> this is a simple select with a known key (artist_tag), and should be very
> quick.
>
>
> now, the same query run as a wildcard match:
>
> mysql> explain select * from tags where artist like '%2317';
>
> +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
> | id | select_type | table | type | possible_keys | key  | key_len | ref
>  | rows   | Extra       |
>
> +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
> |  1 | SIMPLE      | tags  | ALL  | NULL          | NULL | NULL    | NULL
> | 488568 | Using where |
>
>
> with the wildcard, the table index can't be used, and the
> entire table needs to be scanned.
>
>
> hopefully, this will help you locate where your MySQL queries are
> slowing down.  I've submitted a patch or two for collectiondb.cpp based
> on what 'explain' shows.. I think the Amarok crew is waiting for the
> Amarok2 rewrite .  hopefully that'll contain the needed query
> optimizations for big collections.
>
>                -jer johnson
>                jer at gweep.net
>
> --
> jer at sidehack.gweep.net           Programmer / Sysadmin / Gweep
> mpython at gnu.org          -><-    Author of Xtacy, an X11 Graphics Hack
> jer_johnson at real.life.edu        http://www.gweep.net/~jer/index.html<http://www.gweep.net/%7Ejer/index.html>
> ICQ#: 19599864  AIM: DruelChaos YM!: DruelChaos GT: druelchaos
>



-- 
------------------------------------------------------------------
Simon Esneault
Laboratoire Traitement du Signal et de l'Image, (LTSI, UMR-INSERM 642)
Université de Rennes I, Campus de Beaulieu,
35042 Rennes Cedex, France.
Tel : +33 (0)6 64 61 30 94
Mail : simon.esneault at univ-rennes1.fr
------------------------------------------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/amarok/attachments/20080128/7bdfd89f/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Amarok-sql-query-track-change.log
Type: text/x-log
Size: 101666 bytes
Desc: not available
URL: <http://mail.kde.org/pipermail/amarok/attachments/20080128/7bdfd89f/attachment.log>