tuning tips, speed problem - Mailing list pgsql-admin
From | Francesco Andreozzi |
---|---|
Subject | tuning tips, speed problem |
Date | |
Msg-id | 20090116161802.v6tj5jmhw0c80sow@webmail.gamestorm.it Whole thread Raw |
Responses |
Re: tuning tips, speed problem
Re: tuning tips, speed problem Re: tuning tips, speed problem |
List | 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
pgsql-admin by date: