Configuración de servidor para tablas grandes...

[Jorge Vidal - Disytel <jorgev(at)disytel(dot)com>]

Hola,

    Estoy configurando postgresql para un datamart. Por ahora estoy teniendo una performance
aceptable (no ideal), pero me preocupa el crecimiento en el futuro. Tengo un par de millones de
registros por mes. Para afrontar el tema realicé un particionamiento por mes, que funciona bien.
Los explain "dicen" que utiliza las particiones, sin mirar la no implicadas.
Me quedan dudas sobre la configuración de la base de datos. El servidor está corriendo
con centOS 5.0 y es un intel Xeon de 2.4 con discos SCSI y 4gb de ram. El centOS
tiene puesto el patch de kernel PAE (para que tome la memoria entera)...El shmmax me da
4095 mb...
¿Le estoy errando en algún parámetro? El servidor está solo para la base de datos, no tiene otras
aplicaciones...
Desde ya muchas gracias,
Jorge

add_missing_from	off
allow_system_table_mods	off
archive_command	(disabled)
archive_mode	off
archive_timeout	0
array_nulls	on
authentication_timeout	1min
autovacuum	on
autovacuum_analyze_scale_factor	0.1
autovacuum_analyze_threshold	50
autovacuum_freeze_max_age	200000000
autovacuum_max_workers	3
autovacuum_naptime	10min
autovacuum_vacuum_cost_delay	20ms
autovacuum_vacuum_cost_limit	-1
autovacuum_vacuum_scale_factor	0.2
autovacuum_vacuum_threshold	50
backslash_quote	safe_encoding
bgwriter_delay	200ms
bgwriter_lru_maxpages	100
bgwriter_lru_multiplier	2
block_size	8192
bonjour_name	xxxx
check_function_bodies	on
checkpoint_completion_target	0.5
checkpoint_segments	6
checkpoint_timeout	5min
checkpoint_warning	30s
client_encoding	UNICODE
client_min_messages	notice
commit_delay	0
commit_siblings	5
config_file	/u01/postgres/data/postgresql.conf
constraint_exclusion	on
cpu_index_tuple_cost	5
cpu_operator_cost	0.0025
cpu_tuple_cost	0.01
custom_variable_classes
data_directory	/u01/postgres/data
DateStyle	ISO, MDY
db_user_namespace	off
deadlock_timeout	1s
debug_assertions	off
debug_pretty_print	off
debug_print_parse	off
debug_print_plan	off
debug_print_rewritten	off
default_statistics_target	10
default_tablespace
default_text_search_config	pg_catalog.english
default_transaction_isolation	read committed
default_transaction_read_only	off
default_with_oids	off
dynamic_library_path	$libdir
effective_cache_size	128MB
enable_bitmapscan	on
enable_hashagg	on
enable_hashjoin	on
enable_indexscan	on
enable_mergejoin	on
enable_nestloop	on
enable_seqscan	on
enable_sort	on
enable_tidscan	on
escape_string_warning	on
explain_pretty_print	on
external_pid_file
extra_float_digits	0
from_collapse_limit	8
fsync	on
full_page_writes	on
geqo	on
geqo_effort	5
geqo_generations	0
geqo_pool_size	0
geqo_selection_bias	2
geqo_threshold	12
gin_fuzzy_search_limit	0
hba_file	/u01/postgres/data/pg_hba.conf
ident_file	/u01/postgres/data/pg_ident.conf
ignore_system_indexes	off
integer_datetimes	off
join_collapse_limit	8
krb_caseins_users	off
krb_realm
krb_server_hostname
krb_server_keyfile
krb_srvname	postgres
lc_collate	en_US.UTF-8
lc_ctype	en_US.UTF-8
lc_messages	en_US.UTF-8
lc_monetary	en_US.UTF-8
lc_numeric	en_US.UTF-8
lc_time	en_US.UTF-8
listen_addresses	*
local_preload_libraries
log_autovacuum_min_duration	30ms
log_checkpoints	off
log_connections	off
log_destination	stderr
log_directory	/u07/postgres/log
log_disconnections	off
log_duration	on
log_error_verbosity	default
log_executor_stats	off
log_filename	postgresql-%Y-%m-%d_%H%M%S.log
log_hostname	off
log_line_prefix	%u %m %r %t
log_lock_waits	off
log_min_duration_statement	-1
log_min_error_statement	error
log_min_messages	notice
log_parser_stats	off
log_planner_stats	off
log_rotation_age	7d
log_rotation_size	500MB
log_statement	none
log_statement_stats	off
log_temp_files	-1
log_timezone	America/Buenos_Aires
log_truncate_on_rotation	off
logging_collector	on
maintenance_work_mem	16MB
max_connections	600
max_files_per_process	1000
max_fsm_pages	204800
max_fsm_relations	1000
max_function_args	100
max_identifier_length	63
max_index_keys	32
max_locks_per_transaction	64
max_prepared_transactions	10
max_stack_depth	2MB
password_encryption	on
port	5432
post_auth_delay	0
pre_auth_delay	0
random_page_cost	4
regex_flavor	advanced
search_path	$user",public"
seq_page_cost	1
server_encoding	UTF8
server_version	08/03/00
server_version_num	80300
session_replication_role	origin
shared_buffers	1500MB
shared_preload_libraries
silent_mode	off
sql_inheritance	on
ssl	off
standard_conforming_strings	off
statement_timeout	0
superuser_reserved_connections	3
synchronize_seqscans	on
synchronous_commit	on
syslog_facility	LOCAL0
syslog_ident	postgres
tcp_keepalives_count	9
tcp_keepalives_idle	7200
tcp_keepalives_interval	75
temp_buffers	1024
temp_tablespaces
TimeZone	America/Buenos_Aires
timezone_abbreviations	Default
trace_notify	off
trace_sort	off
track_activities	on
track_counts	on
transaction_isolation	read committed
transaction_read_only	off
transform_null_equals	off
unix_socket_directory
unix_socket_group
unix_socket_permissions	511
update_process_title	on
vacuum_cost_delay	0
vacuum_cost_limit	200
vacuum_cost_page_dirty	20
vacuum_cost_page_hit	1
vacuum_cost_page_miss	10
vacuum_freeze_min_age	100000000
wal_buffers	64kB
wal_sync_method	fdatasync
wal_writer_delay	200ms
work_mem	5MB
xmlbinary	base64
xmloption	content
zero_damaged_pages	off