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

Re: tuning tips, speed problem

From: BRAHMA PRAKASH TIWARI <prakashr2n5(at)gmail(dot)com>
To: Francesco Andreozzi <francesco(dot)andreozzi(at)gamestorm(dot)it>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: tuning tips, speed problem
Date: 2009-01-19 12:35:35
Message-ID: 2acc2c820901190435m4062f8d3v1e6099f32ed009af@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hi Francesco

most probabely this is due to the auto vacuum option on and if auto vacuum
is on then the in condition of high transaction on database it slows the
speed of the hole database.Set it off in postgres.conf and vacuum and
reindex transactional tables manualy with in  every two million
transactions.
like
vacuum full <table name>;
reindex table <table name>;



On Fri, Jan 16, 2009 at 8:48 PM, Francesco Andreozzi <
francesco(dot)andreozzi(at)gamestorm(dot)it> 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
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>



-- 
Brahma Prakash Tiwari
Database Administrator
iBoss Tech Solution Noida

In response to

Responses

pgsql-admin by date

Next:From: Thomas PundtDate: 2009-01-19 13:12:06
Subject: Re: tuning tips, speed problem
Previous:From: Jaume SabaterDate: 2009-01-19 12:23:35
Subject: Re: tuning tips, speed problem

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