tuning tips, speed problem

From: Francesco Andreozzi <francesco(dot)andreozzi(at)gamestorm(dot)it>
To: pgsql-admin(at)postgresql(dot)org
Subject: tuning tips, speed problem
Date: 2009-01-16 15:18:02
Message-ID: 20090116161802.v6tj5jmhw0c80sow@webmail.gamestorm.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all, i send this mesage because i just made a migration from my old
mysql db on a more robust postgres server.
I'm using version 8.1 on a linux debian 4.0
i have a dual core XEON 2.33GHz and 2Gb of ram ... 2 disk 15k mirrored
database works good but the performance are horrible! i hope is my
fault of settings.... and this message is just a help request to see
whats i missing to set!
i did only few changes on a default postgresql.conf
i simply playd with shared buffer... but on a mysql to execute a
simple select with an order by the time was jus 1 or 2 seconds ... on
this installation the time it's about 10 seconds or more .... and if
more than one query is executed at the same time the time increase!

i just add here a show all output to see if somethiong uis wrong ... i
hope someone can help me ! i really would like to use postgres on this
database!!

Thankyou
Francesco

begin show all command ....
-------
add_missing_from off
archive_command unset
australian_timezones off
authentication_timeout 60
autovacuum on
autovacuum_analyze_scale_factor 0.2
autovacuum_analyze_threshold 500
autovacuum_naptime 60
autovacuum_vacuum_cost_delay -1
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.4
autovacuum_vacuum_threshold 1000
backslash_quote safe_encoding
bgwriter_all_maxpages 5
bgwriter_all_percent 0.333
bgwriter_delay 200
bgwriter_lru_maxpages 5
bgwriter_lru_percent 1
block_size 8192
bonjour_name unset
check_function_bodies on
checkpoint_segments 3
checkpoint_timeout 300
checkpoint_warning 30
client_encoding UTF8
client_min_messages notice
commit_delay 0
commit_siblings 5
config_file /etc/postgresql/8.1/main/postgresql.conf
constraint_exclusion off
cpu_index_tuple_cost 0.001
cpu_operator_cost 0.0025
cpu_tuple_cost 0.01
custom_variable_classes unset
data_directory /var/lib/postgresql/8.1/main
DateStyle ISO,
db_user_namespace off
deadlock_timeout 1000
debug_pretty_print off
debug_print_parse off
debug_print_plan off
debug_print_rewritten off
default_statistics_target 10
default_tablespace unset
default_transaction_isolation read
default_transaction_read_only off
default_with_oids off
dynamic_library_path $libdir
effective_cache_size 1000
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 off
explain_pretty_print on
external_pid_file /var/run/postgresql/8.1-main.pid
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
hba_file /etc/postgresql/8.1/main/pg_hba.conf
ident_file /etc/postgresql/8.1/main/pg_ident.conf
integer_datetimes on
join_collapse_limit 8
krb_caseins_users off
krb_server_hostname unset
krb_server_keyfile FILE:/etc/postgresql/krb5.keytab
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 localhost
log_connections off
log_destination stderr
log_directory pg_log
log_disconnections off
log_duration off
log_error_verbosity default
log_executor_stats off
log_filename postgresql-%Y-%m-%d_%H%M%S.log
log_hostname off
log_line_prefix %t
log_min_duration_statement -1
log_min_error_statement panic
log_min_messages notice
log_parser_stats off
log_planner_stats off
log_rotation_age 1440
log_rotation_size 10240
log_statement none
log_statement_stats off
log_truncate_on_rotation off
maintenance_work_mem 16384
max_connections 100
max_files_per_process 1000
max_fsm_pages 20000
max_fsm_relations 1000
max_function_args 100
max_identifier_length 63
max_index_keys 32
max_locks_per_transaction 64
max_prepared_transactions 5
max_stack_depth 2048
password_encryption on
port 5432
pre_auth_delay 0
preload_libraries unset
random_page_cost 4
redirect_stderr off
regex_flavor advanced
search_path $user,public
server_encoding UTF8
server_version 8.1.11
shared_buffers 1000
silent_mode off
sql_inheritance on
ssl on
standard_conforming_strings off
statement_timeout 0
stats_block_level off
stats_command_string off
stats_reset_on_server_start off
stats_row_level on
stats_start_collector on
superuser_reserved_connections 2
syslog_facility LOCAL0
syslog_ident postgres
tcp_keepalives_count 0
tcp_keepalives_idle 0
tcp_keepalives_interval 0
temp_buffers 1000
TimeZone localtime
trace_notify off
trace_sort off
transaction_isolation read
transaction_read_only off
transform_null_equals off
unix_socket_directory /var/run/postgresql
unix_socket_group unset
unix_socket_permissions 511
vacuum_cost_delay 0
vacuum_cost_limit 200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10
wal_buffers 8
wal_sync_method fdatasync
work_mem 1024
zero_damaged_pages off
-----
end show all

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kenneth Marshall 2009-01-16 15:25:43 Re: tuning tips, speed problem
Previous Message Jan-Peter Seifert 2009-01-15 17:34:30 Re: MD5 password issue