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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83