From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Andreas Thiel <andreas(dot)thiel(at)u-blox(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum |
Date: | 2009-12-05 20:39:11 |
Message-ID: | dcc563d10912051239p2837ef2bx92f3571f7172277f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Dec 4, 2009 at 4:03 PM, Andreas Thiel <andreas(dot)thiel(at)u-blox(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-12-05 20:42:17 | Re: performance while importing a very large data set in to database |
Previous Message | Andres Freund | 2009-12-05 20:00:00 | Re: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum |