Deleting Headers not deleting?

[DeadSerious]

I've got a question--I've been trying to reduce the size that Newsbin data takes up on the hard drive. I had a somewhat large group, and I used shift-delete to delete all of the headers, but noticed that the corresponding folder in the SPOOL_V6 folder was still the same size (over 11 gb). Doesn't using shift-delete (rather than just delete) remove the headers from storage? With another group, I loaded all headers, using no filters, and the group was blank. Yet the SPOOL_V6 folder was using over 8 gb, with 5.3 in storageData.db3, and 2.7 in storage.db3. Am I doing something wrong?

[itimpi]

Deleting the headers just marks them as removed within the .db3 file and makes the space available for re-use - it does not actually shrink the size of the .db3 file. The .db3 file therefore stays at the largest size it ever reached as far as disk space is concerned.

It is possible to shrink the size to only the allocated records using the sqlite3 utility but it is a time consuming process.

[DeadSerious]

Okay, I thought that was what you got by just using "delete." I thought "shift-delete" actually removed them. So what's the best way to reduce the space used on the hard disk?

BTW, what, then, IS the difference between delete/shift-delete?

[itimpi]

The difference between Delete and Shift-Delete is that the first merely marks headers as "Read" in the database. This means they are still available for searches and the like and can be seen if you set the filters to show read headers. The Shift-Delete option marks them as deleted in the database so they are no longer seen in any sort of search regardless of filters.

The fact that the physical disk space is not released when records are deleted is a function of the way that SQLite databases work. The space is marked internally in the database as available for re-use but not returned to the OS. There was actually a new feature introduced in the 3.1 release of SQLite called 'auto-vacuum' that DOES release space in databases but at the expense of potential performance degradation. I do not know if Quade makes use of this in Newsbin - I suspect not as the databases do not seem to shrink when headers are deleted..

To reclaim space you seem to have a couple of options:

• Delete the headers for groups in question and then redownload as many days of headers as you want to keep. This has the downside of having to redownload headers, and will also lose any read/unread sttus on the headers.
• Manually run the SQLIte3 utility that comes with Newsbin with the "vacuum" parameter to rebuild the databases in question to eliminate unused space. I have not actually tried this myself so if you do decide to try it I would do it first on one group to check there are no undesirable side-effects.
• It is possible Quade may have some further suggestions - we will have to wait and see:)

I do not know if Quade has any plans for changes to Newsbin which will improve the space usage scenario. For most people it is not an issue as the header databases tend to reach a 'steady-state' size and stay there.

[Quade]

Right click "Post Storage/Compact Database" will re-write the database removing the deleted records.

[DeadSerious]

Thanks for all the info!

Quade, regarding the compacting of the database, that was one of the first things I tried. Unfortunately, a lot of the time, compacting the database makes the folder LARGER. I just ran it on one group and went from 1.93 GB to 2.02 GB. I loaded the group, used shift-delete to remove ALL headers, and then compacted. I've noticed that happening with a number of other ones, too, which is one of the reasons I was looking for other ways to save space.

Is there any reason that compacting the db would result in a larger folder?

[Quade]

compacting the database makes the folder LARGER. I just ran it on one group and went from 1.93 GB to 2.02 GB

I just discovered there's a trick to it. If you actually Shift-Delete all the files, the compact will fail. If you leave a couple though. It'll work. I'll have to figure out why. I suspect the compaction mechanism is simply always looking for at least one file.

[DeadSerious]

Okay, I'll try leaving a couple. That's actually what I was looking to do, anyway. I wanted to be able to go through a group, leave some posts to check/download later, and clear the rest from the database to save room. I was just experimenting by getting rid of ALL of them, which must be what was causing the issue. I'll let you know how I make out leaving some.

UPDATE: Didn't work for me. After and update, I left 21 headers in the same group I mentioned previously, and it still got larger. From 2.02 GB to 2.13 GB! That's with shift-deleting all the other headers in the group. About how many should I be leaving?

[Quade]

I was able to purge them. I'm not clear why you couldn't. If you look in the folder, if you see "tmp" files left behind that means the compact failed.

I'll have to think about it.

[DeadSerious]

Yup, I'm seeing the .tmp files in there, which looks like they're the right size to account for the size increase. I tried it with 21 left, then again with about 10, and then again with about 3, but never saw a decrease. I'll keep playing and see if I can do anything to get it to compact.

[Quade]

Having temp files there, means the compact is failing. That's the clue I need to figure out.

You find other Sqlite tools on the net too. You'll want to do a "vacuum" on the DB3 files. It might indicate the files are damaged somehow.

[DeadSerious]

Don't know if this helps, but some (not all) of the groups have .rv4 files in them with the name of my server in them. Some also have the name of an old server that I no longer use in them.

[DeadSerious]

Still having issues with this. Has there been any solution found? I just tried compacting and MOST of the groups got bigger. The worst was a.b.boneless, which went from 7.22 GB to 12.53!! And yes, the .tmp files are there, indicating compact failure. Can I just delete those?

[DeadSerious]

Wow...what is going on?! It seems to be stuck on a.b.multimedia, which started at 1.82 GB, and is now...55 GB?! The big file seems to be a .tmp-wal file. I'm going to have to stop it soon, as it's consuming all the empty space on the drive.

[Quade]

Sounds like you might be running out of disk space and the compaction failing. Boneless is significantly bigger than multimedia.

I'll have to see what B15 does. Hopefully it cleans up the tmp files on failure.

[DeadSerious]

There's still some space there, so I don't think it's a lack of space. I mean, how does the cleanup ADD 54 GB of data? Also, all of a sudden my downloads are going to a folder called "place holder" instead of one named for the group, as is what usually happens. That's new since this other issue started. It looks like there are a lot of files in the "input" folder. Is that normal? (It's about 20 GB worth.)

[Quade]

Wow...what is going on?! It seems to be stuck on a.b.multimedia, which started at 1.82 GB, and is now...55 GB?! The big file seems to be a .tmp-wal file. I'm going to have to stop it soon, as it's consuming all the empty space on the drive

Here you imply you're running short of space on a group that's not the largest group you're processing. So, I still think the idea you're running out is probably something that needs looking at.

I mean, how does the cleanup ADD 54 GB of data?

It has to make a copy of the data to regenerate the database. So, basically you need 2-3 times as much disk space as the size of the group for the purge to work. For safety, Newsbin doesn't operate on the original files but, on copies.

That's new since this other issue started. It looks like there are a lot of files in the "input" folder. Is that normal? (It's about 20 GB worth.)

If you mean the "import" folder, those are records that need to be imported into the header database. I'd just leave Newsbin running so, it can process those. It suggests you've not been getting the most current headers when you view the post lists because they've not been imported yet.

[DeadSerious]

Sorry, maybe I wasn't clear. There's still well over 50 GB left, but one group went from just over 1 GB to 54 GB in about an hour, and was still running. My drive was heating up from running non-stop, and the process showed no signs of stopping. The percentage bar was barely there for that group, maybe 3 or 4 percent. That's what I meant by consuming the space--not that it had, but that if it continued at that pace (50 GB x 4% for only one group), it would have. There's several hundred GB left on the drive; not a space issue.

And I can understand 2-3 times the data. No big deal. But this was 54 TIMES the data. Again, maybe I wasn't clear, but that 54 GB was for ONE GROUP. That's not 2-3 times.

I did let the "import" file clean itself out, so hopefully that will help.

EDIT: If it helps for comparison, a.b.boneless, which as you pointed out, is a bigger group, went from 7 GB to 12 GB, basically adding a 5 GB temp file. a.b.multimedia, however, went from just over 1 GB to 54 GB by adding a 52 GB temp file, and--according to the progress bar--had only completed a small percentage. That, to me, just seems unusual.

[Quade]

basically adding a 5 GB temp file

The temp file is trash, residue from a failed compression. You can search for and delete all temp files you find in the spool_v6 folder.

My alt.binaries.multimedia folder is 16 gigs or so. I imagine during compression it'll get pretty large. First double that size for a copy then however much space it needs to build a new clean database. You're talking about job-lots of data. My 16 GB folder is probably tracking 5-10 times that much data.

I compressed a.b.multimedia here. It took a goodly amount of time. Got me back 2 GB of space. 16 to 14 gigs.

[DeadSerious]

Sweet. Thanks. I figured I could delete them, but wanted to make sure. In the case of a.b.multimedia, what I did was essentially completely clear the group, and redownload all headers. That seems to have fixed it, I think, as it's only about 1.4 GB now (and no temp files). It also seems to have fixed the issue with the "place holder"-titled folder, as--at least in this case--it's saving it to the properly-named folder. I'll check some of the others just to make sure.

FWIW, I think that the crazy large .tmp file must have been caught in some kind of loop. I don't keep that much data for groups, so while your a.b.multimedia is around 15 GB, my whole spools folder is about 30 GB, and the a.b.multimedia is about 1-2 GB. That's why I figured there must be something wrong there, when the temp file for one group was almost double what my entire spools folder had been. Even accounting for doubling (or tripling) in the group, that was a lot.

I'll keep my eye on it, so that if it happens again, maybe I can figure out what triggered it. (e.g. why THAT group?) Thanks so much for your help!