Deleting headers are exstreamly slow, 1 sec per header

[CountryLover]

Hi

I'm using 6.54 and I'm having serious trouble deleting headers from a large group 125,000+ it takes about 1 second per header.
The real problem in this is that while Newsbin are deleting, I cant start a download or should I say the download doesn't start because the header are not moved to the download list until Newsbin are finished deleting. In a group with 125,000+ I might be interested in max 10,000, so I usually delete the post that doesn't interest me, which means a 30 houer wait before I can start downloading, that's slow.
Newsbin isn't freezing, but is is using maximum cpu on one thread in my case 25% and it keeps doing that, very stable, through the whole delete process.
If I close Newsbin the GUI close all right, but Newsbin keeps running in the background deleting, using 25% cpu.

P.s. There is no trouble in small groups.

What can I do about this ??

Thanks in advance
Best regards
Countrylover

[Quade]

Tell me about your PC.

When you select files to delete and hit "Shift-Delete" Newsbin collects the posts up and sends them to a background process that deletes them. If you're just using "Delete" they're just being hidden. "Shift-Delete" will eventually make thing faster as posts are removed from storage. There are no performance improvements when you just "Delete".

When I do shift-delete, I can delete 1000's sec so, the question is why yours is so slow. I'd suggest upgrading to 6.55 at the very least. I suspect though that the underlying problem is disk speed.

[CountryLover]

Hi Quade

I don't think the problem is my PC, because it works well with other demanding processes such as compiling software.
I tried moving the the SPOOLV6 directory to a new disk and changed the DataPath in the nbi file, so it points to the new location, but it make no difference.
I think it is the size of the db3 files that is the problem, their sizes are:
Storage.db3 = 1,096,192 KB
StorageData.db3 = 15,063,328 KB

I have not done any DB programming since my education, so I don't know what is common practice, but if I delete 10,000 haders and then a little later I delete 10,000 more, before the first delete is finished, the db3 file isn't changing until the whole process is finished, actually I don't know if it is going to change at all, because the delete is still running and it have been doing so for the past 8 hours without the db3 file changing. When I started the delete process i did at least 10 different deletes so I think it should have changed by now. This might be a stupid question but: will the DB rollback not keep a full db file as long as the db file isn't written to the disk / closed ?

Yes of course I am using "shift delete".

Thanks for helping
Best regards
CountryLover

[Quade]

1 - Deleting doesn't change the size of the DB. It just free's up used block which will then be re-used when you download headers again.

2 - It's not clear if it's doing anything to the DB at this point. You could download and use "procmon" to watch what Newsbin is doing. I think I'd start small and work your way up. So, try deleting 50, then 100 then 500, then 1000 and see how it reacts.

3 - The db file is decently sizes but not terribly so. I doubt the size of the DB is the issue. All deletes are done using primary keys meaning they should be going as fast as the DB can delete them. If the data folder on an internal drive?

4 - When you delete in blocks like that. The transaction should also be in blocks that sized so, two sets of deletes should act like 2 discrete DB transactions.

I just think there's something wrong. It shouldn't take long at all to do 10,000 deletes. 8 hours is crazy.

Down in the "cache" line is there a number in parens (XXX)? If Newsbin is still importing headers then delete won't even start. The number in parens represents blocks of headers still importing.

[CountryLover]

Hi Quade

According to Procmon Newsbins is doing:
About 20,000 reading in Storage.db3 per second all success
About 15 LockFile on Storage.db3 per second all FAILS
About 15 LockFile on Download.db3 per second all FAILS

a few CreateFile QuireDirectory SloseFile on \Newsbin\Import (*.gz) all sucessful

And a few QuireFullSizeInformation on the two disk involved

That's about it.

And the whole process of deleting 105,000 header have now been going on for 17 hours
If I kill it now those 17 hours will be waisted.

Thanks for helping
Best regards
CountryLover

[CountryLover]

Hi again

Had to reboot my PC, so I had to kill Newsbin as it does not close the db process when the GUI is closed.

Now I tried deleting just a single header of the [1 file 0 par] kind it takes about 400 lines of something that looks like work then there is about 24,000 lines of ReadFile to Storage.db3 folowed by 150 lines doing some work, all in all 25,300 lines in procmon when deleting one line of header.
The workpart is don on both Storage.db3 and StorageData.db3.
Deleting two headers gives 50,100 lines in procmon
Deleting four headers gives 99,737 lines in procmon
Deleting ten headers gives 248,582 lines in procmon

Deleting from a small group gives 515 lines in procmon the first and the last work part looks more or less the same but the ReadFile part in between is only about 30 lines.
Deleting two headers gives the same result
Deleting four headers gives the same result and so does 10 headers

Hope this give you a clue
Best regards
Asger

[Quade]

Yeah. That's some good trouble-shooting. I just tried to delete 5000 images and it's still going a couple minutes later so, I can reproduce your results. It acts like it's doing a sequential scan per post instead of using the primary key.

Thanks. I'll check it out.

[Quade]

I made a change to one of the indexes and was able to delete 16,000 posts in about 20 seconds. So, I'm going to declare it "fixed". This fix will be in 6.60B4 which should be out in a couple days.

[CountryLover]

Hi Quade

Sounds good looking forward to an update.

But why is there difference in the behaviour when deleting from small and large groups ?

Best regards
CountryLover

[Quade]

Not having a proper index means it has to scan the entire DB. Smaller DB's scan faster than larger ones.

That would be my guess anyway.

[CountryLover]

Hi Quade

That sounds logic, but then the number of ReadFile's would increase when deleting in small DB's as well and that does not happen, at least not in my test, there is the same number of lines in procmon when deleting one header as when deleting ten headers.
I'll do a test on a 100 items later when newsbin is ready.

Best regards
Asger

[CountryLover]

Hi Quade

Deleting from from a medium size group:
StorageData.db3 256,752 KB
Storage.db3 43,360 KB

1 header 863 lines in procmon
2 header 872 lines in procmon
100 header 1095 lines in procmon
all the rest of the headers 1400 header 1081 lines in procmon

Not really making a lot of sense.

Best regards
Asger

[Quade]

The database does what it does. You'd have to look at the code to see why it's talking on disk. I'm satisfied that it's much faster. I wasn't gong to dig any deeper than that.

[CountryLover]

Hi Quade

I wasn't expecting an explanation, it was just information about how newsbin behaves when deleting in a medium group.

One thing I would like to suggest, if possible, is to make adding to the download list possible while newsbin is deleting in the other thread. If that is possible, it doesn't matter much how much time newsbin use on deleting, because it will be running in the background anyway.
The way I works now is: I download all headers, I delete all the header of no interest 90%, I start download of the first 2-300 in the remaining list, but the download does not start until the delete is finished, several hours later.

Hope that can be fixed
Thanks in advanse
Best regards
Country lover

[Quade]

I wasn't expecting an explanation, it was just information about how newsbin behaves when deleting in a medium group.

All deletes are 100% the same inside Newsbin.

One thing I would like to suggest, if possible, is to make adding to the download list possible while newsbin is deleting in the other thread. If that is possible, it doesn't matter much how much time newsbin use on deleting, because it will be running in the background anyway.

I suspect this whole topic will go away once you get the quick deletes.

I download all headers

Hopefully you only do this once per group and then never again. There's never a point to download all over and over again. The old headers don't change so, re-downloading them buys you nothing.

[CountryLover]

Hi Quade

Hopefully you only do this once per group and then never again. There's never a point to download all over and over again. The old headers don't change so, re-downloading them buys you nothing.

Yes of course, my mistake, I meant all NEW headers.

I suspect this whole topic will go away once you get the quick deletes.

Yes you might be right, but deleting 90% of all new headers (+ all the underlying multi-part headers) can sum up to millions of actual headers to delete, so my guess is that it will be difficult to make it really fast. but I'm looking forward to any kind of fix.

Thanks again
Best regards
CountryLover