Re: tuning tips, speed problem

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

 



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



--
Brahma Prakash Tiwari
Database Administrator
iBoss Tech Solution Noida

[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