Re: tuning tips, speed problem

Just a quick question - why do you use quite old release 8.1 instead of
8.3.5 ?

Post only changes in postgresql.conf and explain analyze of your slow
query.

Oleg
On Fri, 16 Jan 2009, Francesco Andreozzi wrote:

> 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
>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83