performance issues with external mysql database

[Alan Ezust]

Hi Matěj,

Here are the rowcounts for the tables, bad and good

		amarok2	amarok3
		(bad)	               (good)
albums		2485	2629
artists		2301	2335
composers	199	199
devices		12	0
genres		194	195
labels		0	0
tracks		18702	18661
urls		18699	18661
statistics	38446	18661

I thought the culprit was the "statistics" table.
There are quite a lot of entries in it that have a "deleted" = 1.
So I did a "delete from statistics where deleted=1" to see if that got
rid of the delays.
It did not.

Then I followed the instructions above and ran mysql with logging of
long transactions.
I started up Amarok with the bad DB and let it play to the end of the
first track.
It had a long delay to figure out what to play next, and then repeated
the same track.

Here is my mysql log.

root at cerberus:/var/log/mysql# tail -f mysql-slow.log
SELECT version FROM admin WHERE component = 'AMAROK_USERPLAYLIST';
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.000679  Lock_time: 0.000374 Rows_sent: 30  Rows_examined: 60
SET timestamp=1338914637;
SELECT id, parent_id, name, description, urlid FROM
playlists where parent_id=-1 ORDER BY name;
# Time: 120605  9:44:04
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.000307  Lock_time: 0.000087 Rows_sent: 1  Rows_examined: 5
SET timestamp=1338914644;
SELECT version FROM admin WHERE component = 'AMAROK_BOOKMARKS';
# Time: 120605  9:44:24
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.008794  Lock_time: 0.007860 Rows_sent: 0  Rows_examined: 200
SET timestamp=1338914664;
SELECT id, parent_id, name, url, description, custom FROM bookmarks
WHERE url LIKE '%ZmlsZTovLy9tbnQvbXVzaWMvQnlHZW5yZS9CcmFpbndhc2hlZC9ITkFTJTIwJiUyMGZyaWVuZHMvR2VnZW5zdGFuZGUlMjBmYWxsZW4lMjB6dSUyMGJvZGVuJTIwKDIwMDApLzAxJTIwLSUyME5hY2h0cyUyMFRyYXVtdGUlMjBFciUyMFZvbiUyMEVub3JtZXIlMjBMYXV0c3RhcmtlJTIwLSUyMEhOQVMubXAz%';
# Time: 120605  9:44:58
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.182019  Lock_time: 0.000078 Rows_sent: 18699
Rows_examined: 18699
SET timestamp=1338914698;
SELECT id, deviceid, rpath, directory, uniqueid FROM urls;
# Time: 120605  9:45:59
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.000966  Lock_time: 0.000085 Rows_sent: 0  Rows_examined: 200
SET timestamp=1338914759;
SELECT id, parent_id, name, url, description, custom FROM bookmarks
WHERE url LIKE '%ZmlsZTovLy9tbnQvbXVzaWMvQnlHZW5yZS9FY3RvL1ZhcnR0aW5hL3NlbGVuaWtvLzA3JTIwLSUyMHN1dXJldF9qYV9zb3JpYXQubXAz%';
# Time: 120605  9:48:57
# User at Host: amarok[amarok] @ localhost []
# Query_time: 0.000964  Lock_time: 0.000086 Rows_sent: 0  Rows_examined: 200
SET timestamp=1338914937;
SELECT id, parent_id, name, url, description, custom FROM bookmarks
WHERE url LIKE '%ZmlsZTovLy9tbnQvbXVzaWMvQnlHZW5yZS80QURTb3VuZHRyYWNrL1VudG8lMjBBc2hlcy9Nb29uJTIwT3Bwb3NlJTIwTW9vbi8wMV9UZWFjaCUyME1lJTIwSG93JTIwVG8lMjBEcm93bl9VbnRvJTIwQXNoZXMubXAz%';


On Mon, Jun 4, 2012 at 9:56 AM, Matěj Laitl <matej at laitl.cz> wrote:
> On 4. 6. 2012 Alan Ezust wrote:
>> At first, I thought my problem was related to how many tracks I had in
>> my playlist (6000) or
>> database (13000). Now i realize it is probably more related to how
>> many tracks I renamed over
>> the years.
>>
>> I still have the "bad" database if anyone wants me to supply any
>> measurements on it.
>> I suspect there is an operation Amarok should be doing from time to
>> time when it rescans the
>> database, to remove invalid entries from certain tables to keep them
>> nice and small.
>
> Hmm, we try to do that incrementally by never letting the database to get
> dirty, but that perhaps fails.
>
> Please switch back to the old db and do the following:
> 1. Post row counts for some important tables: albums, artist, composers,
> devices, directories, genres, labels, statistics, tracks, urls, years along
> with _expected_ counts (e.g. tracks, urls, statistics should be number of your
> collection tracks, artists number of distinct artists etc.; `find
> ~/path/to/music -type d` may help with dir count)
> 2. Enable MySQL Slow Query Log [1] with threshold of 1s and zero
> min_examined_row_limit. Post the most offending (slow) queries.
>
> [1] http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
>
> Thanks,
>                        Matěj