tuning tips, speed problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux