Author Topic: InnoDB grew to 12 GB. How do I shrink it?  (Read 12770 times)

cockhands

  • Guest
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #15 on: 2014-07-30, 02:13:41 am »
Did that but parts_11 is still nearly 16 GB.

Offline c4rv

  • Decent Indexer
  • ***
  • Posts: 75
  • Helpful: +3/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #16 on: 2014-07-30, 04:26:10 pm »
Parts_11  I think is a.b.boneless which has the most posts per day, 2x that of cores and mom. It also contains a load of c**p.

Are you currently backfilling ?

How many hours is your parts retention set to ?

Also, something wrong as your index should be 1/2 the size of data
« Last Edit: 2014-07-30, 04:28:22 pm by c4rv »

cockhands

  • Guest
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #17 on: 2014-08-01, 01:08:13 am »
Parts retention is 72 hours
Release retention is 1825 days
Other retention is 0

@Bart39: Tried running your SQL statement from the MariaDB command line, but received the error "ERROR 1046 (3D000): No database selected"

Offline c4rv

  • Decent Indexer
  • ***
  • Posts: 75
  • Helpful: +3/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #18 on: 2014-08-01, 02:25:51 am »
unless your system falls over a lot or is very slow then 72 hours is not needed.

Basically its saying that it will wait for 3 days for each incomplete release to see if the rest of the parts turn up. Even with 100GB+ releases, generally they don't take more then a few hours to upload. I've got mine set to 18 hours and haven't noticed missing any major releases.

cockhands

  • Guest
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #19 on: 2014-08-01, 03:19:35 am »
That shouldn't account for the huge parts_11 table, though, no? Seems like the table got screwed up somewhere along the line and I need to rebuild it, but Bart39's SQL statement isn't working for me.

Offline c4rv

  • Decent Indexer
  • ***
  • Posts: 75
  • Helpful: +3/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #20 on: 2014-08-01, 03:28:08 am »
parts_11 can easily grow when doing backfill.

If you are not doing backfill, then as I suggested reduced your parts retention to something like 24 hours and give it a few hours to see if it starts reducing.

Offline Wally73

  • Overlord
  • ******
  • Posts: 266
  • Helpful: +31/-1
  • i'm nuts
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #21 on: 2014-08-01, 04:54:56 am »
even 24 parts retention is overkill (all above 4 hours is)

Offline Bart39

  • Overlord
  • ******
  • Posts: 171
  • Helpful: +19/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #22 on: 2014-08-02, 09:36:07 am »

Parts retention is 72 hours
Release retention is 1825 days
Other retention is 0

@Bart39: Tried running your SQL statement from the MariaDB command line, but received the error "ERROR 1046 (3D000): No database selected"

If it's the first one I missed the crucial step....

in mysql prompt do "use information_schema" minus quotes first

Offline Bart39

  • Overlord
  • ******
  • Posts: 171
  • Helpful: +19/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #23 on: 2014-08-02, 09:38:09 am »

That shouldn't account for the huge parts_11 table, though, no? Seems like the table got screwed up somewhere along the line and I need to rebuild it, but Bart39's SQL statement isn't working for me.
Yes it would, your telling It to hold on to all the parts (from incomplete collections) for 3days before creating a partial release (completion <100%)

Offline c4rv

  • Decent Indexer
  • ***
  • Posts: 75
  • Helpful: +3/-0
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #24 on: 2014-08-03, 04:41:36 am »
just to give an example, when I was messing around and backfilling boneless (only 12bn headers, lol). With parts retention set to 24 hours the boneless parts table was running at around 3.5GB.

cockhands

  • Guest
Re: InnoDB grew to 12 GB. How do I shrink it?
« Reply #25 on: 2014-08-05, 06:04:56 am »
Switching part retention to 24 hours and running Bart39's SQL statement brought my DB size below 2 GB. Thanks for all your help.