Backups are slow and ENORMOUS

rodalpho commented 5 years ago

I'm running NZBhydra2 on a relatively powerful platform (skylake i5, 16GB RAM, SSD) and noticed that updates were really slow, taking minutes to complete. Looking in the backups directory I was surprised to see that the latest backup was a whopping 1.2GB in size!

Is NZBhydra2 not properly rotating/purging logs or something?

Looking at the install, my nzbhydra.mv.db is a whopping 455MB, and my logs dir is 285MB, with the largest log nzbhydra2-log.json.2019-05 coming in at 122MB. Seems like a lot of disk space for what nzbhydra2 actually does.

root@nzb:/opt/nzbhydra2/data/backup# ls -lah
total 4.4G
drwxr-xr-x 2 ubuntu ubuntu    8 Feb  9 11:17  .
drwxr-xr-x 6 ubuntu ubuntu    9 Feb  9 11:18  ..
-rw------- 1 ubuntu ubuntu 255M Jan 27 12:21 'nzbhydra-2019-01-27 12-20-30.zip'
-rw------- 1 ubuntu ubuntu 793M Jan 28 20:58 'nzbhydra-2019-01-28 20-55-57.zip'
-rw------- 1 ubuntu ubuntu 970M Feb  1 08:20 'nzbhydra-2019-02-01 08-17-36.zip'
-rw------- 1 ubuntu ubuntu 955M Feb  2 19:25 'nzbhydra-2019-02-02 19-22-06.zip'
-rw------- 1 ubuntu ubuntu 1.2G Feb  6 20:39 'nzbhydra-2019-02-06 20-35-38.zip'
-rw------- 1 ubuntu ubuntu 742M Feb  9 11:20 'nzbhydra-2019-02-09 11-17-49.zip'

theotherp commented 5 years ago

Well, without knowing the content of the last backup it's hard to say. Open the zip and see what files take up the most space.

On Sat, 9 Feb 2019, 17:22 rodalpho <notifications@github.com wrote:

I'm running NZBhydra2 on a relatively powerful platform (skylake i5, 16GB RAM, SSD) and noticed that updates were really slow, taking minutes to complete. Looking in the backups directory I was surprised to see that the latest backup was a whopping 1.2GB in size!

Is NZBhydra2 not properly rotating/purging logs or something?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/theotherp/nzbhydra2/issues/311, or mute the thread https://github.com/notifications/unsubscribe-auth/ANtAeWnAR6QIw223A-W5dd8kHlamg9Jgks5vLvWrgaJpZM4aynKL .

rodalpho commented 5 years ago

Looks like one gigantor nzhhydra.mv.db. A whopping 2.7GB!

-rw------- 1 ubuntu ubuntu 1.2G Feb  6 20:39 'nzbhydra-2019-02-06 20-35-38.zip'
-rw-r--r-- 1 root   root   2.7G Feb  6 20:35  nzbhydra.mv.db
-rw-r--r-- 1 root   root    77K Feb  6 20:39  nzbhydra.yml

The current copy of that file is "only" 844MB.

drwxr-xr-x 2 ubuntu ubuntu    4 Feb  9 11:21 .
drwxr-xr-x 5 ubuntu ubuntu    8 Feb  9 11:51 ..
-rw-r--r-- 1 ubuntu ubuntu 844M Feb  9 11:50 nzbhydra.mv.db
-rw-r--r-- 1 ubuntu ubuntu 277K Jan 30 20:20 nzbhydra.trace.db

pengc99 commented 5 years ago

I am having the same problem on my local instance - not even sure what in the hell is making this database backup so large.

What file format is the db backup in?

pengc99 commented 5 years ago

After performing the steps in https://github.com/theotherp/nzbhydra2/issues/228#issuecomment-458406385, the DB size dropped down to ~350mb.

rodalpho commented 5 years ago

Just restarting nzbhydra dropped my DB from 1.9GB to 873MB. Running that defrag statement actually made no difference at all, it's still 873MB.

Seems like two problems here. First, old data isn't being purged at all while the application is running. Bouncing the application does a purge, but it still leaves WAY too much data in the DB. 873MB for a NZB proxy is kinda crazy.

theotherp commented 5 years ago

There's a bug in the database which makes the database files larger than it should be. I haven't been able to reproduce it so I can't create a ticket for the database library. My three years old database is 410MB big which is reasonable considering the amount of data contained.

You can configure Hydra to only store history for a certain time which should reduce the database further.

There's no way for me to fix this right now.

rodalpho commented 5 years ago

OK-- I dropped saved searches to 3 days and history from 14 weeks down to 2 weeks. Seems like the defaults are a bit too high there.

Unfortunately restarting didn't shrink my DB much, still at 874MB. Doing a "shutdown defrag" also didn't shrink it from 874MB. It seems like bouncing nzbhydra2 does the same purge, creating the nzbhydra.mv.db.tempfile then switching over to it.

Before I bounced/defragged it, my DB size had increased to 941MB. That's 67MB in like 12 hours.

I just scheduled nzbhydra2 to restart in cron every night, that should help a bit I guess.

theotherp commented 5 years ago

Make sure that it actually properly shuts down as that's when the database is actually defragmented.

rodalpho commented 5 years ago

Yeah, systemctl restart seems to work fine.

kylehendricks commented 5 years ago

Like @pengc99, I did the instructions in https://github.com/theotherp/nzbhydra2/issues/228#issuecomment-458406385 and my db went from ~4gb to ~134mb

FrostyWolf commented 5 years ago

My database file was 23,343,208 KB After a shutdown defrag command, it is 5,527,416 KB.

What should I be looking at to fix this?

theotherp commented 5 years ago

@FrostyWolf There's nothing you can do right now. I'll release a new version soon that compacts the database automatically at shutdown but that won't help with long running processes.

hferreira23 commented 3 years ago

Sorry for hijacking this issue but I'm having a similar problem on the latest version.

I'm running nzbhydra2 inside a docker container and the current size of the db is 8.6GB. When I try to do a shutdown defrag from the bugreport page it throws an error

Error while executing SQL Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection

And the database size stays the same.

Also I have setup the backups to be done once a day but for some reason in most days it does multiple backups (10/15) which uses a lot a space.

elit3ge commented 3 years ago

I'm having this issue as well, except my DB has blown to 102GB.

theotherp / nzbhydra2

Backups are slow and ENORMOUS #311