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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


I have some tables which tend to get huge (and will for sure hit the 
wall of my storage system soon, total DB ~700 GB now):

SELECT relfilenode, relpages,reltuples,relname FROM pg_class WHERE 
relpages > 10000 ORDER BY relpages DESC;
 relfilenode | relpages |  reltuples  |             relname
-------------+----------+-------------+---------------------------------
-
       72693 | 51308246 | 4.46436e+09 | result_orig
       72711 | 17871658 | 6.15227e+06 | test
       73113 | 12240806 | 4.46436e+09 | result_orig_test_id
       73112 | 12240806 | 4.46436e+09 | result_orig_prt_id
       72717 |   118408 | 6.15241e+06 | test_orig
       72775 |    26489 | 6.15241e+06 | test_orig_lt_id
       72755 |    19865 | 6.15241e+06 | test_orig_test_id_key
       73147 |    16872 | 6.15227e+06 | test_test_id
       73146 |    16872 | 6.15227e+06 | test_lt_id


I'm going to work on the table size of the largest table (result_orig) 
itself by eliminating columns, stuffing n Booleans into bit(n)'s, 
replacing double precision by reals, etc.. By this I should be able to 
reduce the storage per row to ~1/3 of the bytes currently used.

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.

But still:

### My Issue No. 1: Index Size
What really worries me is the size of the two largest indexes 
(result_orig_test_id, result_orig_prt_id) I'm using. Both are roughly 
1/3 of the result_orig table size and each index only b-tree indexes a 
single bigint column (prt_id, test_id) of result_orig. Roughly every 
group of 100 rows of result_orig have the same prt_id, roughly every 
group of 1000-10000 rows have the same test_id.  Each of these two cols 
is a Foreign Key (ON DELETE CASCADE).

So my fear is now, even if I can reduce the amount of data per row in 
result_orig, my indexes will remain as large as before and then dominate 
disk usage.

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.


### 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). 


### 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.

I use these kernel settings:
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 2147483648
vm.overcommit_memory = 2

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 
bytes each
max_fsm_relations = 4194304             # min 100, ~70 bytes each
#max_files_per_process = 1000           # min 25
#shared_preload_libraries = ''          # (change requires restart)

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. 


###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?
 
#-----------------------------------------------------------------------
-------
# AUTOVACUUM PARAMETERS
#-----------------------------------------------------------------------
-------

autovacuum = on                         # Enable autovacuum subprocess?  
'on'
log_autovacuum_min_duration = 1000      # -1 disables, 0 logs all 
actions and
autovacuum_max_workers = 3              # max number of autovacuum 
subprocesses
autovacuum_naptime = 1min               # time between autovacuum runs
autovacuum_vacuum_threshold = 50        # min number of row updates 
before
autovacuum_analyze_threshold = 50       # min number of row updates 
before
autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before 
vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of table size before 
analyze
autovacuum_freeze_max_age = 200000000   # maximum XID age before forced 
vacuum
autovacuum_vacuum_cost_delay = 20       # default vacuum cost delay for
autovacuum_vacuum_cost_limit = -1       # default vacuum cost limit for

How would I check it is running correctly? I don't see any error 
messages in syslog from autovacuum.



Any help, also on tuning postgresql.conf to this application, is greatly 
appreciated!

Thanks

Andy




-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux