benmullins
commented
5 years ago I apologize, I posted this issue a bit too hastily. I don't think it's the problem detection task, but the delete old search results task.
Manually triggering that task causes one of the regular instances of several minutes of high CPU usage that got me looking into what Hydra was up to in the first place. It also starts generating temp.db files (though the main nzbhydra.mv.db file doesn't grow very noticeably beyond the 15 GB size it reached on the previous run; maybe to 15.1 or 15.2 GB at most).
2019-06-18 02:39:51.542 INFO --- [0.0-5076-exec-1] org.nzbhydra.tasks.HydraTaskScheduler : [Search: 840714, Host: 192.168.1.50] Running task "Delete old search results" now
2019-06-18 02:52:31.485 WARN --- [ HydraTask4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 50200, SQLState: HYT00
2019-06-18 02:52:31.485 ERROR --- [ HydraTask4] o.h.engine.jdbc.spi.SqlExceptionHelper : Timeout trying to lock table {0}; SQL statement:
delete from SEARCHRESULT where FIRST_FOUND < DATEADD('SECOND', ?, DATE '1970-01-01') AND ID not in (select SEARCH_RESULT_ID from INDEXERNZBDOWNLOAD where SEARCH_RESULT_ID is not null) [50200-199]
2019-06-18 02:52:31.527 ERROR --- [ HydraTask4] o.s.s.s.TaskUtils$LoggingErrorHandler : Unexpected error occurred in scheduled task.
javax.persistence.PessimisticLockException: could not execute statementTraceback omitted; identical to the one in the post above.
This makes more sense given what the error said in the first place. Although the inflation of the database file only happens the first time, this 12 or so minutes of CPU thrashing followed by an error dump in the log happens every time the "delete old search results" task runs, i.e. about once an hour.
theotherp
ghost
LachlanGytha
SBBH
naq90
CollinChaffin
hanzoh
I'm having an issue where my
nzbhydra.mv.dbfile will inflate hugely shortly after startup, seemingly after the first time the problem detection task runs. I've seen the other issues on here about large database files, but I haven't seen anyone mention this specific problem.Basically, if I shut down or restart Hydra, the database file will be reduced in size to a little under 300 MB, but sometime within half an hour to a couple hours at most after the program starts, it will begin to inflate the database file to 15 GB (it always stops at 15 GB, every time). Once it gets started doing this this happens within around 10-12 minutes or so, hammering the CPU the entire time it's doing it.
I turned on debug logging, and this is an example of what is logged, starting around the time the database starts inflating and ending when it gets to its final destination of 15 GB:
I've watched this happen a few times and it seems like the DB inflation is triggered immediately after Hydra runs these problem detection tasks, at which point large temporary db files start getting written to the database directory and (I presume) rolled into the main db file. The SQL exception and Java traceback seem to be identical each time.
Although I don't think it has anything to do with this, since the memory usage was comfortably below 256MB anyway, I tried increasing the allocation to 512MB anyway because of the OOM detector task, and nothing changed.
And again, the database will be reduced back down to less than 300 MB if Hydra is shut down, but after starting again this cycle repeats. I've also tried running the manual shutdown defrag command mentioned in another issue, and while it did reduce my database size this inflation happened again afterward with no apparent change.
I'm using the linuxserver.io container, and it's up-to-date with the latest 2.6.11 release of Hydra.