Обсуждение: tuning tips, speed problem
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
On Fri, Jan 16, 2009 at 04:18:02PM +0100, 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 > Francesco, Try increasing your work_memory value and shared_buffers. 8MB is not very large on a 2GB system. Also, 8.3.5 is much improved over 8.1. Cheers, Ken > shared_buffers 1000 ... > work_mem 1024
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
On Fri, Jan 16, 2009 at 4:37 PM, Oleg Bartunov <oleg@sai.msu.su> wrote: > Just a quick question - why do you use quite old release 8.1 instead of > 8.3.5 ? I presume it's because it is the version that comes by default in Debian Etch. So I'll take this opportunity to comment about backports.org. If you use that repository, you'll be able to have PostgreSQL 8.3.5 on a Debian Etch (current stable branch of Debian). I am using it right now until Lenny is released (new stable version of Debian). -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas"
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@gamestorm.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Brahma Prakash Tiwari
Database Administrator
iBoss Tech Solution Noida
On Montag, 19. Januar 2009, BRAHMA PRAKASH TIWARI wrote: | 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>; Please, a "vacuum full" should only be a measure of last resort ever. Regular vacuum (without the "full" keyword) should always be enough. And I don't recommend turning autovacuum off. You might need to increase the "max_fsm_pages" setting to avoid database bloat. The output of a database wide "vacuum verbose" should tell you, if it is set appropriately. And Francesco, if you say "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", could you provide the "explain analyze"-output for those queries? This should help analyzing your problems. Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
On Mon, Jan 19, 2009 at 5:35 AM, BRAHMA PRAKASH TIWARI <prakashr2n5@gmail.com> wrote: > 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>; Except for certain circumstances vacuum full should be avoided and vacuum (regular) used in its place until it is proven ineffective. Also, it is usually far better to turn up the autovacuum_vacuum_cost_delay to 10 or 20 and let autovacuum do its job. Unless you have a very starved I/O subsystem autovacuum with cost delay of 20 should have almost no noticeable effect on a transactional database.