[Fwd: Re: 100% CPU while searching in collection browser]
Dr. Diesel
dr.diesel at gmail.com
Sat Mar 1 15:37:19 UTC 2008
I have found the query, it would help if I typed in the correct search string!!
I've changed it to "SELECT distinct", it still takes about 10 seconds
to perform the query, but about 1000000x better and now usable!
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