[Fwd: Re: 100% CPU while searching in collection browser]
Dr. Diesel
dr.diesel at gmail.com
Sat Mar 1 16:23:56 UTC 2008
I made a backup of the DB and tested the above:
mysql> alter ignore table images add primary key(path,deviceid);
Query OK, 185659 rows affected (3.12 sec)
Records: 185659 Duplicates: 178419 Warnings: 0
mysql>
I must be missing something here, after purging 178K records then
again running this query:
mysql> SELECT images.deviceid,images.path FROM images, artist, tags
WHERE images.artist = artist.name AND artist.id = tags.artist AND
tags.sampler = 1 AND images.album LIKE 'Metal Blade 15th Anniversary
Disc 1' ESCAPE '/' AND images.deviceid IN (1,2,3,-1);
I still get:
38068 rows in set (0.22 sec)
Just as before?
So far "select distinct" is the only work-a-around. What am I missing?
Andy
On Sat, Mar 1, 2008 at 10:17 AM, Lucky Captain Rabbit King
<jer at gweep.net> wrote:
> this MySQL command will make it so there can be only one
> entry for the pair of path,deviceid .
>
> alter ignore table images add primary key(path,deviceid);
>
>
> but before you add that, I would ask the developers if
> there was a reason why there wasn't a primary key on the
> images table in the first place.
>
> -jer
>
>
>
>
> On Sat, Mar 01, 2008 at 09:51:15AM -0500, Dr. Diesel wrote:
> > I've already dropped the tables and rescanned, hoping for a fix
> > earlier. Since then I've also deleted the album art for that folder,
> > suspecting it was the cause! So a rescan now would likely fix that
> > particular song. I've found others as well.
> >
> > I'll try any all hacks/fixes/proposals to help! Just let me know what
> > to attempt!
> >
> > Thanks to all,
> > Andy
> >
> > On Sat, Mar 1, 2008 at 9:41 AM, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
> > > I'm forwarding Andy's email back to the list. I found the source of his
> > > issues...it's a MySQL query that is returning 40,000-odd hits for an
> > > album image. Anyone know how/why his database may have gotten into this
> > > state? (see his email for the query that triggers it)
> > >
> > > My guess would be to just have him drop all those entries and rescan,
> > > but it'd be good to know why this happened in the first place.
> > >
> > > --Jeff
> > >
> > >
> > > Actually not all the same, but close! I've attached the results.
> > >
> > >
> > >
> > > On Sat, Mar 1, 2008 at 9:07 AM, Dr. Diesel <dr.diesel at gmail.com> wrote:
> > > > Jeff, I think you may have found it! This script:
> > > >
> > > > SELECT images.deviceid,images.path FROM images, artist, tags WHERE
> > > > images.artist = artist.name AND artist.id = tags.artist AND
> > > > tags.sampler = 1 AND images.album LIKE 'Metal Blade 15th Anniversary
> > > > Disc 1' ESCAPE '/' AND images.deviceid IN (1,2,3,-1)
> > > >
> > > > Returns 38068 records! I bet the 100% CPU is caused by loading all of
> > > > these images!
> > > >
> > > > Here is a glimpse, all of the records are the same:
> > > >
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > > |
> > > > | -1 | ./main/MP3/By Artists/Various Artists/Metal Blade 15th
> > > > Anniversary Disc 1/Folder.jpg
> > > >
> > > > What do you think?
> > > >
> > > > Again many thanks for all the help.
> > > >
> > > > Andy
> > > >
> > > >
> > > >
> > > >
> > > > On Sat, Mar 1, 2008 at 8:07 AM, Jeff Mitchell <kde-dev at emailgoeshere.com> wrote:
> > > > > On Friday 29 February 2008, you wrote:
> > > > > > Hi Jeff! I know your busy, but could you check to make sure those
> > > > > > files i sent you at least cause the 100% CPU problem on your machine!
> > > > > > Make sure I'm not the only one or going crazy!!!
> > > > >
> > > > > Andy--
> > > > >
> > > > > Sorry, I've been totally swamped.
> > > > >
> > > > > I haven't been able to reproduce it.
> > > > >
> > > > > Here's what I did:
> > > > >
> > > > > I created a collection containing a folder with only those two files (I didn't
> > > > > have a collection built of anything else since I wiped it out doing testing
> > > > > for another issue). I let the collection scan, made sure the collection is
> > > > > set to Artist/Album display mode, and typed "marshall"...no problem.
> > > > >
> > > > > So, is that sequence of events right?
> > > > >
> > > > > A few things to start looking at on your end. Have you tried it on any
> > > > > non-Fedora machine? (I'm on Gentoo) Have you tried it with sqlite? (I
> > > > > tried with both internal sqlite and MySQL).
> > > > >
> > > > > Also, try going into your MySQL database, and running the following pasted at
> > > > > the following URL (I didn't want to put them in the email here or they'd get
> > > > > broken up and you'd have a fun time putting them back together), and see how
> > > > > things go. This will help to figure out whether it's Amarok or MySQL that's
> > > > > really causing the issue here.
> > > > >
> > > > > http://amarok.pastebin.ca/924530
> > > > >
> > > > > --Jeff
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > --
> > > > projecthuh.com
> > > > All of my bits are free, are yours? Fedoraproject.org
> > > >
> > >
> > >
> > >
> > > --
> > > projecthuh.com
> > > All of my bits are free, are yours? Fedoraproject.org
> > >
> > > _______________________________________________
> > > Amarok mailing list
> > > Amarok at kde.org
> > > https://mail.kde.org/mailman/listinfo/amarok
> > >
> > >
> >
> >
> >
> > --
> > projecthuh.com
> > All of my bits are free, are yours? Fedoraproject.org
> > _______________________________________________
> > Amarok mailing list
> > Amarok at kde.org
> > https://mail.kde.org/mailman/listinfo/amarok
>
> --
> 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
> ICQ#: 19599864 AIM: DruelChaos YM!: DruelChaos GT: druelchaos
>
>
> _______________________________________________
> Amarok mailing list
> Amarok at kde.org
> https://mail.kde.org/mailman/listinfo/amarok
>
--
projecthuh.com
All of my bits are free, are yours? Fedoraproject.org
More information about the Amarok
mailing list