On 11/5/2004 6:28 AM, Mark Jaroski wrote:

> Hi!
> 
> I'm trying to set up replication for a largish db, and am
> running into a problem with what appears to be run-away disk
> usage.
> 
> The master db takes up around 7.5Gb after a full vacuum, but
> after I start slony the slave db grows to 27Gb at which
> point it has filled it's partition and starts producing disk
> full errors on both sides.

Do you vacuum the slave similarly to the master?


Jan


> 
> Aside from changing the env variables and the setup script
> I'm following the documented setup proceedure exactly as
> with pgbench (when I do it for pgbench everything works
> fine).  I've tried starting with the slave db both empty and
> restored.
> 
> Can anybody suggest ways to prevent slony from using quite
> so much disk space?
> 
> I've attached my slonik setup script, and postgres.conf in
> case those might help.
> 
> Thanks in advance,
> 
> -mark
> 
> 
> 
> ------------------------------------------------------------------------
> 
> # -----------------------------
> # PostgreSQL configuration file
> # -----------------------------
> #
> # This file consists of lines of the form:
> #
> #   name = value
> #
> # (The '=' is optional.) White space may be used. Comments are introduced
> # with '#' anywhere on a line. The complete list of option names and
> # allowed values can be found in the PostgreSQL documentation. The
> # commented-out settings shown in this file represent the default values.
> #
> # Any option can also be given as a command line switch to the
> # postmaster, e.g. 'postmaster -c log_connections=on'. Some options
> # can be changed at run-time with the 'SET' SQL command.
> #
> # This file is read on postmaster startup and when the postmaster
> # receives a SIGHUP. If you edit the file on a running system, you have 
> # to SIGHUP the postmaster for the changes to take effect, or use 
> # "pg_ctl reload".
> 
> 
> #---------------------------------------------------------------------------
> # CONNECTIONS AND AUTHENTICATION
> #---------------------------------------------------------------------------
> 
> # - Connection Settings -
> 
> tcpip_socket = true
> max_connections = 12
> 	# note: increasing max_connections costs about 500 bytes of shared
> 	# memory per connection slot, in addition to costs from shared_buffers
>         # and max_locks_per_transaction.
> #superuser_reserved_connections = 2
> #port = 5432
> #unix_socket_directory = ''
> #unix_socket_group = ''
> #unix_socket_permissions = 0777	# octal
> #virtual_host = ''		# what interface to listen on; defaults to any
> #rendezvous_name = ''		# defaults to the computer name
> 
> # - Security & Authentication -
> 
> #authentication_timeout = 60	# 1-600, in seconds
> #ssl = false
> #password_encryption = true
> #krb_server_keyfile = ''
> #db_user_namespace = false
> 
> 
> #---------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
> #---------------------------------------------------------------------------
> 
> # - Memory -
> 
> shared_buffers = 10000		# min 16, at least max_connections*2, 8KB each
> sort_mem = 16384                # min 64, size in KB
> vacuum_mem = 16384              # min 1024, size in KB
> 
> # - Free Space Map -
> 
> max_fsm_pages = 20000		# min max_fsm_relations*16, 6 bytes each
> max_fsm_relations = 1000	# min 100, ~50 bytes each
> 
> # - Kernel Resource Usage -
> 
> max_files_per_process = 1000	# min 25
> #preload_libraries = ''
> 
> 
> #---------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #---------------------------------------------------------------------------
> 
> # - Settings -
> 
> fsync = false		        # turns forced synchronization on or off
> #wal_sync_method = fsync	# the default varies across platforms:
> 				# fsync, fdatasync, open_sync, or open_datasync
> wal_buffers = 32	        # min 4, 8KB each
> 
> # - Checkpoints -
> 
> checkpoint_segments = 32	# in logfile segments, min 1, 16MB each
> #checkpoint_timeout = 300	# range 30-3600, in seconds
> #checkpoint_warning = 30	# 0 is off, in seconds
> commit_delay = 1000             # range 0-100000, in microseconds
> commit_siblings = 5		# range 1-1000
> 
> 
> #---------------------------------------------------------------------------
> # QUERY TUNING
> #---------------------------------------------------------------------------
> 
> # - Planner Method Enabling -
> 
> #enable_hashagg = true
> #enable_hashjoin = true
> #enable_indexscan = true
> #enable_mergejoin = true
> #enable_nestloop = true
> #enable_seqscan = true
> #enable_sort = true
> #enable_tidscan = true
> 
> # - Planner Cost Constants -
> 
> effective_cache_size = 290493   # typically 8KB each
> random_page_cost = 2		# units are one sequential page fetch cost
> #cpu_tuple_cost = 0.01		# (same)
> #cpu_index_tuple_cost = 0.001	# (same)
> #cpu_operator_cost = 0.0025	# (same)
> 
> # - Genetic Query Optimizer -
> 
> #geqo = true
> geqo_threshold = 25
> #geqo_effort = 1
> #geqo_generations = 0
> #geqo_pool_size = 0		# default based on tables in statement,
> 				# range 128-1024
> #geqo_selection_bias = 2.0	# range 1.5-2.0
> 
> # - Other Planner Options -
> 
> #default_statistics_target = 10	# range 1-1000
> #from_collapse_limit = 8
> #join_collapse_limit = 8	# 1 disables collapsing of explicit JOINs
> 
> 
> #---------------------------------------------------------------------------
> # ERROR REPORTING AND LOGGING
> #---------------------------------------------------------------------------
> 
> # - Syslog -
> 
> #syslog = 0			# range 0-2; 0=stdout; 1=both; 2=syslog
> #syslog_facility = 'LOCAL0'
> #syslog_ident = 'postgres'
> 
> # - When to Log -
> 
> #client_min_messages = notice	# Values, in order of decreasing detail:
> 				#   debug5, debug4, debug3, debug2, debug1,
> 				#   log, info, notice, warning, error
> 
> #log_min_messages = notice	# Values, in order of decreasing detail:
> 				#   debug5, debug4, debug3, debug2, debug1,
> 				#   info, notice, warning, error, log, fatal,
> 				#   panic
> 
> #log_error_verbosity = default   # terse, default, or verbose messages
> 
> #log_min_error_statement = panic # Values in order of increasing severity:
> 				 #   debug5, debug4, debug3, debug2, debug1,
> 				 #   info, notice, warning, error, panic(off)
> 				 
> #log_min_duration_statement = -1 # Log all statements whose
> 				 # execution time exceeds the value, in
> 				 # milliseconds.  Zero prints all queries.
> 				 # Minus-one disables.
> 
> #silent_mode = false		 # DO NOT USE without Syslog!
> 
> # - What to Log -
> 
> #debug_print_parse = false
> #debug_print_rewritten = false
> #debug_print_plan = false
> #debug_pretty_print = false
> #log_connections = false
> #log_duration = false
> #log_pid = false
> #log_statement = false
> #log_timestamp = false
> #log_hostname = false
> #log_source_port = false
> 
> 
> #---------------------------------------------------------------------------
> # RUNTIME STATISTICS
> #---------------------------------------------------------------------------
> 
> # - Statistics Monitoring -
> 
> #log_parser_stats = false
> #log_planner_stats = false
> #log_executor_stats = false
> #log_statement_stats = false
> 
> # - Query/Index Statistics Collector -
> 
> #stats_start_collector = true
> #stats_command_string = false
> #stats_block_level = false
> #stats_row_level = false
> #stats_reset_on_server_start = true
> 
> 
> #---------------------------------------------------------------------------
> # CLIENT CONNECTION DEFAULTS
> #---------------------------------------------------------------------------
> 
> # - Statement Behavior -
> 
> #search_path = '$user,public'	# schema names
> #check_function_bodies = true
> #default_transaction_isolation = 'read committed'
> #default_transaction_read_only = false
> statement_timeout = 0         # 0 is disabled, in milliseconds
> 
> # - Locale and Formatting -
> 
> #datestyle = 'iso, mdy'
> #timezone = unknown		# actually, defaults to TZ environment setting
> #australian_timezones = false
> #extra_float_digits = 0		# min -15, max 2
> #client_encoding = sql_ascii	# actually, defaults to database encoding
> 
> # These settings are initialized by initdb -- they may be changed
> lc_messages = 'en_US.UTF-8'		# locale for system error message strings
> lc_monetary = 'en_US.UTF-8'		# locale for monetary formatting
> lc_numeric = 'en_US.UTF-8'		# locale for number formatting
> lc_time = 'en_US.UTF-8'			# locale for time formatting
> 
> # - Other Defaults -
> 
> #explain_pretty_print = true
> #dynamic_library_path = '$libdir'
> #max_expr_depth = 10000		# min 10
> 
> 
> #---------------------------------------------------------------------------
> # LOCK MANAGEMENT
> #---------------------------------------------------------------------------
> 
> #deadlock_timeout = 1000	# in milliseconds
> #max_locks_per_transaction = 64	# min 10, ~260*max_connections bytes each
> 
> 
> #---------------------------------------------------------------------------
> # VERSION/PLATFORM COMPATIBILITY
> #---------------------------------------------------------------------------
> 
> # - Previous Postgres Versions -
> 
> #add_missing_from = true
> #regex_flavor = advanced	# advanced, extended, or basic
> #sql_inheritance = true
> 
> # - Other Platforms & Clients -
> 
> #transform_null_equals = false
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #