Skip site navigation (1) Skip section navigation (2)

Configuración de servidor para tablas grandes...

From: Jorge Vidal - Disytel <jorgev(at)disytel(dot)com>
To: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Configuración de servidor para tablas grandes...
Date: 2008-05-28 21:33:42
Message-ID: 483DCFB6.8010606@disytel.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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

pgsql-es-ayuda by date

Next:From: Luis Fernando Curiel CabreraDate: 2008-05-28 21:33:49
Subject: Ayuda con query
Previous:From: Diego GilDate: 2008-05-28 20:48:26
Subject: Re: Busco entrenamiento

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group