Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database that is growing rapidly. VERY rapidly. My /db directory is at 100% now, and there are a bunch of new files in pg_xlog.

My background is in Oracle and MySQL. I do not know what is going on with this database. I know that I need to hire a Postgres DBA quickly.

Is there anything in the immediate term that I can do to fix my issue? I notice that deleting data does nothing to mitigate my space issue.

Output from pg_settings is:

archive_command             /bin/true                       configuration file
archive_mode                on                              configuration file
archive_timeout             1min                            configuration file
checkpoint_segments         100                             configuration file
checkpoint_timeout          5min                            configuration file
client_encoding             UTF8                            session
DateStyle                   ISO, MDY                        configuration file
default_statistics_target   100                             configuration file
default_text_search_config  pg_catalog.english              configuration file
effective_cache_size        1358MB                          configuration file
hot_standby                 on                              configuration file
hot_standby_feedback        on                              configuration file
lc_messages                 C                               configuration file
lc_monetary                 C                               configuration file
lc_numeric                  C                               configuration file
lc_time                     C                               configuration file
listen_addresses            *                               configuration file
log_destination             csvlog                          configuration file
log_line_prefix             %m:                             proc=%p,user=%u,db=%d,host=%r   configuration file
log_min_duration_statement  2s                              configuration file
log_rotation_age            1d                              configuration file
log_rotation_size           100MB                           configuration file
logging_collector           on                              configuration file
maintenance_work_mem        128MB                           configuration file
max_connections             512                             configuration file
max_files_per_process       65535                           configuration file
max_stack_depth             6MB                             configuration file
max_standby_streaming_delay -1                              configuration file
max_wal_senders             5                               configuration file
port                        5432                            configuration file
search_path                 public, "$user", public         session
shared_buffers              424MB                           configuration file
temp_tablespaces                                            configuration file
wal_buffers                 8MB                             configuration file
wal_keep_segments           128                             configuration file
wal_level                   hot_standby                     configuration file
wal_writer_delay            200ms                           configuration file
work_mem                    32MB                            configuration file
share|improve this question
1  
What version of Postgresql? Is your pg_xlog directory on the same partition as your data directory? What is the capacity of your system? Is your cluster running a hot standby? Could you post the results of the following query: SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override') –  bma Nov 15 '13 at 14:42
1  
In psql type show checkpoint_segments; and post what that setting is. In your pg_xlog directory, how many WAL segments are there? Eg. ls -l 000* | wc -l –  bma Nov 15 '13 at 14:49
1  
Do you know what your largest tables are? For example select tablename, pg_total_relation_size(schemaname||'.'||tablename) as tblsize from pg_tables where schemaname <> ALL (ARRAY['information_schema'::name, 'pg_catalog'::name]) order by 2 desc limit 30; –  bma Nov 15 '13 at 14:52
1  
Try dropping your checkpoint_segments down to 32 or so, that should buy you a bit of space. The answer is more likely to buy some bigger disks (longer term). –  bma Nov 15 '13 at 14:58
1  
I'm not going to get into DBA 101 about WAL segments are, however, you can make that change in your postgresql.conf and issue SELECT pg_reload_conf(); as the postgres user (in any database) to make that change live. What the change to that setting does in this case is to reduce the number of WAL segments you have in your pg_xlog directory. If you have a lot of data churn in your db you will want to keep the checkpoint_segments setting at 32 minimum (see postgresql.org/docs/current/static/… for more details) –  bma Nov 15 '13 at 15:06

1 Answer 1

up vote 4 down vote accepted

You can revise the checkpoint_segments setting in your postgresql.conf and issue SELECT pg_reload_conf(); as the postgres (super)user (in any database) to make that change live. What the change to that setting does is reduce the number of WAL segments that remain resident in your pg_xlog directory. If you have a lot of data churn in your db you will want to keep the checkpoint_segments setting at 32 minimum (see http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS for more details).

Some general tips:

  • Ensure your autovacuum settings are pretty aggressive.
  • Move the pg_xlog directory to a different volume than the $PGDATA directory. I have measured performance increases from 10% - 30% from that change alone.
  • Set up a monitoring tool to keep an eye on table/index/database/server size increases. Eg. Nagios, New Relic,
share|improve this answer
    
I wasn't aware New Relic could do that, I am on it. –  AKWF Nov 15 '13 at 20:22
    
There are some pretty handy plugins in New Relic specifically for Postgresql. Some of the better ones I've seen are from EnterpriseDB, who employ some core Postgresql developers. –  bma Nov 15 '13 at 20:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.