Re: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum

On Fri, Dec 4, 2009 at 4:03 PM, Andreas Thiel <andreas.thiel@u-blox.com> wrote:
> Hi All,
>
> Maybe some questions are quite newbie ones, and I did try hard to scan
> all the articles and documentation, but I did not find a satisfying
> answer.
>
> I'm running PostgreSQL 8.3.6 on a 32-Bit Centos 4 machine (which I
> probably should update to 64 Bit soon)

Yeah, 64 bit is worth the migration.

> I have the same information stored in an Oracle 10g DB which consumes
> only 70G data and 2G for indexes. The schema may be better optimized,
> but for sure there is a table with 4 billion rows inside as well. So
> it's about 10x smaller in disk space than PgSQL. I wonder why.

You've probably got a bloated data store.

> Is such disk usage for indexes expected? What can I do to optimize? I
> could not run yet a VACUUM on result_orig, as I hit into max_fsm_pages
> limit (still trying to adjust that one). I tried REINDEX, it didn't
> change anything.

OK, you've got a problem with max_fsm_pages not being big enough, so
pretty much anything you do vacuum wise is a wasted effort until you
fix that.

> ### My Issue No. 2: relpages and VACUUM
> I have another table "test" which is - as starting point - created by
> INSERTs and then UPDATE'd. It has the same columns and roughly the same
> number of rows as table test_orig,  but consumes 160 times the number of
> pages. I tried VACUUM on this table but it did not change anything on
> its relpages count. Maybe this is just because VACUUM without FULL does
> not re-claim disk space, i.e. relpages stays as it is? I did observe
> that after VACUUM, a REINDEX on this table did considerably shrink down
> the size of its indexes (test_test_id, test_lt_id).

Vacuum does NOT shrink tables.  It reclaims free space to be reused.
If you have a large table that's 95% dead space regular vacuum can't
do anything for you.  Note that not having a large enough free space
map is likely the cause of your problems.

> ### My Issue No 3: VACCUM FULL out of memory
> I tried to do a VACCUM FULL on the two tables (test, result_orig)
> mentioned above. In both cases it fails with a very low number on out of
> memory like this:
>
> ERROR:  out of memory
> DETAIL:  Failed on request of size 224.

It's likely doing a LOT of other memory allocations before this one
fails.  64 bit and more memory might help.

> I use these kernel settings:
> kernel.shmmni = 4096
> kernel.shmall = 2097152
> kernel.shmmax = 2147483648

None of those have anything to do with how much vacuum full can allocate really.

> vm.overcommit_memory = 2

This will keep vacuum from allocating memory that may be available but
is already "spoken for" so to speak.  How much memory dos your machine
have?

> And these postgresql.conf settings:
> shared_buffers = 512MB                  # min 128kB or
> max_connections*16kB
> temp_buffers = 128MB                    # min 800kB
> max_prepared_transactions = 1024        # can be 0 or more
> work_mem = 16MB                         # min 64kB
> maintenance_work_mem = 256MB            # min 1MB
> max_stack_depth = 8MB                   # min 100kB
> max_fsm_pages = 70000000                # min max_fsm_relations*16, 6

Wow, and you're still running out?  Do you have autovacuum turned off
or something?

> What's going wrong here? I know, one should not use VACUUM FULL, but I
> was curious to see if this would have any impact on relpages count
> mentioned in Issue 2.

Vacuum full is perfectly cromulent, assuming you know the downsides
and avoid using it for regular periodic maintenance.  For instance, my
main production database had a drive go out a few days ago, and it
held a query up for several days, during which vacuum couldn't reclaim
space freed up during that time.  I set a maintenance window last
night and ran vacuum full plus reindex on a couple of tables that had
gotten particularly bloated.

> ###My Issue No. 4: Autovacuum
> I have the feeling that Autovacuum is not really running, else why are
> tables and indexes growing that much, especially "test" table?

Could it be a single long running query or transaction is keeping
vacuum from reclaiming free space?  check pg_stat_activity for long
running queries or transactions.