1346: Optimize database (Complete) Never Ends [#7316]

Post a reply


In an effort to prevent automatic submissions, we require that you complete the following challenge.
Smilies
:D :) :( :o :-? 8) :lol: :x :P :oops: :cry: :evil: :roll: :wink:

BBCode is ON
[img] is ON
[flash] is OFF
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: 1346: Optimize database (Complete) Never Ends [#7316]

Re: 1346: Optimize database (Complete) Never Ends

by Ludek » Mon Feb 07, 2011 6:33 am

BTW: You are right, I found there is a huge performance leak because of rebuilding of full-text search index that takes half an hour on my DB while after optimization it takes only 2 minutes!

Fixed as http://www.ventismedia.com/mantis/view.php?id=7316

Thanks!

Re: 1346: Optimize database (Complete) Never Ends

by Ludek » Mon Feb 07, 2011 6:14 am

I only wanted to say that if you see instant:

Code: Select all


[1376] 3188 DB open SQL: SELECT WebCommercial, WebCopyright, WebFilepage, WebArtist, WebSource, WebRadio, WebPayment, WebPublisher, WebUser, PreviewStartTime, PreviewLength, CacheName, PreviewName, Copyright, Encoder, ISRC, InitialKey, Language, OrigLyricist, ParentalRating,PreGap, PostGap, TotalSamples, GaplessBytes, SignType, SignPart1, SignPart2, SignPart3, SignPart4, ContainerType, StreamCount, StreamInfo FROM Songs WHERE ID = 10 
[1376] 3188 DB lock finished, took 627714.
[1376] 3188 DB exec SQL: INSERT INTO SongsText (rowid,artist,album,albumartist,songtitle,genre,songpath,author,lyricist,conductor,groupdesc,subtitle,lyrics,comment,custom1,custom2,custom3,custom4,custom5,origartist,origtitle,origlyricist,publisher,encoder,copyright,mood,tempo,occasion,quality,involvedpeople) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
[1376] 3188 DB lock successful.
[1376] 3188 DB lock finished, took 97554533.
Then it is the full-text search index rebuilding and it is most probably the cause.
Solution would be to add new checkbox [x] Rebuild full-text search index and remove the action from [x] Optimize database (complete)
Reported as: http://www.ventismedia.com/mantis/view.php?id=7315

Re: 1346: Optimize database (Complete) Never Ends

by paulmt » Mon Feb 07, 2011 3:25 am

This seems to be the same issue I reported here although perhaps I reported it differently??
Same result though. A full optimization never seems to end, or if I left it long enough i.e. overnight and half the next day it finished, but doing it again after completion doesn't get any faster it goes into another laboriously long hour after hour process.
My database was checked by the SQL check plugin and had no errors. I clean installed MM4 1345 and 1346 and still got the same results. The database was fully optimized by MM3 prior to installing MM4 and took around 3 minutes!

My data base is around 430Mb. While running the MM4 optimization the MM-DB was 436 prior to starting. The optimization ran through once and the MM.DB file went to 408Mb in approx. 3-5 minute but then started to disappear and reappear on and off for another 35 minutes before I got fed up ended the MM4 process to get out of the programme. So the database was compacted but then the optimization went in another sort of process. I watched DBGVIEW and the process was optimizing individual files one after the other and I presume was gong to do this right through the 104k+ tracks ??

Where is the SQL number asked for by Ludek in the log I can't see it anywhere (if thats what he is asking for) or what is he asking for there in the previous post?

When running the full optimization in MM3 the Journal files rebuilds 1-1/2 times and is complete inside 5 minutes.

37000 tracks (180GB): 7 Minutes

by Friedrich » Mon Feb 07, 2011 2:12 am

Full optimization of ~ 37000 tracks (audio) (180GB) takes about 7-8 minutes here. (Windows XP, Athlon XP 2500+, 1024 MB ram)

Re: 1346: Optimize database (Complete) Never Ends

by Ludek » Sun Feb 06, 2011 1:52 pm

robojock wrote:I gave it a try :( my database is 570mb big and its been running now for about 6 hours and still counting :(
And what SQL do you see in DbgView ?

Re: 1346: Optimize database (Complete) Never Ends

by robojock » Sat Feb 05, 2011 7:42 am

I gave it a try :( my database is 570mb big and its been running now for about 6 hours and still counting :(

Re: 1346: Optimize database (Complete) Never Ends

by MDominik13 » Thu Feb 03, 2011 9:49 pm

I'll try that to see what's going on. I definitely ran the full optimization in 3.1 and it didn't take this long.

Re: 1346: Optimize database (Complete) Never Ends

by Ludek » Thu Feb 03, 2011 8:55 pm

MDominik13 wrote:No, I didn't see the tooltip. Was it in MM3 as well? As Lowlander pointed out, it never took this long for me in MM3. Pretty much the same database took several minutes but not hours.

When complete optimizing is performed then there is re-created also the full-text search index for each song, this was added in MM 3.1 and can take quite a lot of time.
You can run DbgView and if you will see instant "INSERT INTO SongText .... " SQL queries then it is the full-text search index recreating.

Re: 1346: Optimize database (Complete) Never Ends

by MDominik13 » Thu Feb 03, 2011 4:35 pm

No, I didn't see the tooltip. Was it in MM3 as well? As Lowlander pointed out, it never took this long for me in MM3. Pretty much the same database took several minutes but not hours.

Re: 1346: Optimize database (Complete) Never Ends

by Lowlander » Thu Feb 03, 2011 2:46 pm

But it never took this long in MM3, so it does seem something has changed.

Re: 1346: Optimize database (Complete) Never Ends

by nohitter151 » Thu Feb 03, 2011 2:44 pm

MDominik13 wrote:Yeah, even though it finally completed, there's definitely something wrong here.
vpsaxman wrote:Last time I did the complete optimization it took almost 4 hours...
Dreadlau wrote:Wow 1h seems excessively long.
I don't know, did you see the tooltip?
Image

Re: 1346: Optimize database (Complete) Never Ends

by MDominik13 » Thu Feb 03, 2011 11:16 am

Yeah, even though it finally completed, there's definitely something wrong here.

Re: 1346: Optimize database (Complete) Never Ends

by vpsaxman » Thu Feb 03, 2011 10:18 am

Last time I did the complete optimization it took almost 4 hours...

Re: 1346: Optimize database (Complete) Never Ends

by Dreadlau » Thu Feb 03, 2011 8:51 am

Wow 1h seems excessively long.

Re: 1346: Optimize database (Complete) Never Ends

by MDominik13 » Tue Feb 01, 2011 1:40 pm

Guess patience is a virtue, huh? It took just over 1 hour to complete.

Top