PLEASE GOD HELP US!

[Shane | SkinnyCorp]

Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a
developer) for a small web development company. We recently switched over
to PostgreSQL after going over some of the powerful features that it holds
over MySQL.

However, after we launched one site, we slapped ourselves in the head. We
have run a message board for a few years now, and have always used MySQL for
the backend, but recently switched to PostgreSQL. It has always been
lightening fast with MySQL, but has slowed to nearly a halt in terms of
online access time. I can't seem to do anything about it!! PLEASE HELP
US!!

Now, I've read as much as I could about optimizing PostgreSQL for
performance, and nothing I do seems to help anything. Just so I don't get
20 links to the same sites I've read... I'll post what I've already used for
reference:

http://postgis.refractions.net/pipermail/postgis-users/2004-January/003757.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
http://www.lyris.com/lm_help/6.0/tuning_postgresql.html

Nothing makes much of a difference. I even attempted to use persistant
connections to the database to get around the connection overhead... and
THAT just seemed to eat up all the system's memory while giving little or no
gain in performance. All of my optimizations seem to help at first, but
everything soon gets just as slow as it was before... and I mean SLOW.


Sooo.... I'll give you as much info as I can... it may be too much, but I
suppose that's better than not enough....

# HARDWARE #
Pentium 4 2.5ghz
1.5 gb of DDR 400
Unlimited bandwidth
# MEMORY USAGE (top output sorted on memory) of persistant PgSQL
connections:
27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46 0
postmaster
27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25 0
postmaster
27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20 0
postmaster
27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36 0
postmaster
27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32 0
postmaster
27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40 0
postmaster
27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24 0
postmaster
27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25 0
postmaster
27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22 0
postmaster
27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18 0
postmaster
27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26 0
postmaster
27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15 0
postmaster
27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26 0
postmaster
27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17 0
postmaster
27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30 0
postmaster
27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03 0
postmaster
27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00 0
postmaster
27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00 0
postmaster
27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00 0
postmaster
27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02 0
postmaster
27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02 0
postmaster
27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02 0
postmaster
27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00 0
postmaster
27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00 0
postmaster
27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00 0
postmaster
27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00 0
postmaster
27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00 0
postmaster
27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00 0
postmaster
27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00 0
postmaster
27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00 0
postmaster
1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0
mdmpd
27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00 0
postmaster
27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00 0
postmaster



# WEBSITE #
30-60 users online at any given time
15,000 rows in the 'threads' table
joined on
300,000 rows in the 'posts' table
Total size of database on disk is 1.1 Gigabytes

# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
(this happened to be not so bad.... which may say a lot since it
took 10 seconds...)

8 Queries Totaling 10.7413 Seconds

SQL: SELECT count(*) AS count FROM thread_listing
Num Rows: 1
Affected Rows: 0
Exec Time: 0.75249910354614

SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND
threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND
promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND
promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754

SQL: SELECT userid,username FROM users WHERE userid IN (select *
from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348



# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------

#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION

#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)

#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072

# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG

#---------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each

# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING

#---------------------------------------------------------------------------

# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING

#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1

#silent_mode = false # DO NOT USE without Syslog!

# - What to Log -

debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false



#---------------------------------------------------------------------------
# RUNTIME STATISTICS

#---------------------------------------------------------------------------

# - Statistics Monitoring -

log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false

# - Query/Index Statistics Collector -

stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false



#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS

#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting

# - Other Defaults -

explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10



#---------------------------------------------------------------------------
# LOCK MANAGEMENT

#---------------------------------------------------------------------------

#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each



#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY

#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false






PLEASE HELP ME FIND THE BOTTLE NECK!!

Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds,
and that is absolutely unacceptable in terms of online use. If I can't fix
this I'm going to have to spend the month or two switching back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!

Thanks in advance!!

- Shane




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Rosser Schwarz]

Post by Shane | SkinnyCorp
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a
developer) for a small web development company. We recently switched over
to PostgreSQL after going over some of the powerful features that it holds
over MySQL.

What kind of query load are you experiencing? (That is,
hits/views/queries per second/minute/whatever.)

What's your disk subsystem?

What indices do you have defined on which tables? Run EXPLAIN ANALYZE
on your longer-running queries and check to see if your indices are
being used or not.

How often are you vacuuming?

/rls

[Joshua D. Drake]

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

Is t.status a big or small int? You might need to cast it... This also
goes for the other queries below. What are the explains for these queries?

explain SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0

When was the last time you ran Vacuum, Analyze?

Sincerely,

Joshua D. Drake

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND
threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND
promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND
promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754
SQL: SELECT userid,username FROM users WHERE userid IN (select *
from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348
# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
PLEASE HELP ME FIND THE BOTTLE NECK!!
Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds,
and that is absolutely unacceptable in terms of online use. If I can't fix
this I'm going to have to spend the month or two switching back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!
Thanks in advance!!
- Shane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

[Greg Spiegelberg]

I don't think ORDER BY X=Y will use an index even if casted. I may be wrong.
We're still using 7.3.5.

Post by Joshua D. Drake

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

Is t.status a big or small int? You might need to cast it... This also
goes for the other queries below. What are the explains for these queries?
explain SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
When was the last time you ran Vacuum, Analyze?
Sincerely,
Joshua D. Drake

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND
threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND
promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND
promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754
SQL: SELECT userid,username FROM users WHERE userid IN (select *
from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348
# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
PLEASE HELP ME FIND THE BOTTLE NECK!!
Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds,
and that is absolutely unacceptable in terms of online use. If I can't fix
this I'm going to have to spend the month or two switching back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!
Thanks in advance!!
- Shane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Greg Spiegelberg
Product Development Manager
Cranel, Incorporated.
Phone: [phone]
Fax: [phone]
Email: ***@cranel.com
Technology. Integrity. Focus.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Marc Mitchell]

-----Original Message-----

On Behalf Of Shane | SkinnyCorp
Sent: Friday, October 01, 2004 10:05 AM
To: PgSQL ADMIN
Subject: [ADMIN] PLEASE GOD HELP US!
..<snip>..

You can use pgmonitor to visually see general load and check if there is
any contention. Your sample queries were all SELECTs but the data has
to be getting in there somehow.

What's the relationship between the process that issues the queries and
the database machine? Could there be any latency between the two?
Individual queries could run quickly but if the requester takes a while
to retrieve the request results before submitting a subsequent query,
that can result in problems?

Without index, table size and sample distribution information (or
explain verbose output), it is difficult to say if any one query could
be made to execute faster.

Marc Mitchell
Enterprise Information Solutions, Inc.
Downers Grove, IL 60515
***@eisolution.com


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Goulet, Dick]

Shane,

Two things: 1) when was the last time you vacuumed the
database? From my experience that has a large effect on the database
performance. 2) Why do you have so many postmaster processes? I've got
an active database but only one postmaster.


Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Shane | SkinnyCorp [mailto:***@skinnycorp.com]
Sent: Friday, October 01, 2004 12:05 PM
To: PgSQL ADMIN
Subject: [ADMIN] PLEASE GOD HELP US!

Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly
a
developer) for a small web development company. We recently switched
over to PostgreSQL after going over some of the powerful features that
it holds over MySQL.

However, after we launched one site, we slapped ourselves in the head.
We have run a message board for a few years now, and have always used
MySQL for the backend, but recently switched to PostgreSQL. It has
always been lightening fast with MySQL, but has slowed to nearly a halt
in terms of online access time. I can't seem to do anything about it!!
PLEASE HELP US!!

Now, I've read as much as I could about optimizing PostgreSQL for
performance, and nothing I do seems to help anything. Just so I don't
get 20 links to the same sites I've read... I'll post what I've already
used for
reference:

http://postgis.refractions.net/pipermail/postgis-users/2004-January/0037
57.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
http://www.lyris.com/lm_help/6.0/tuning_postgresql.html

Nothing makes much of a difference. I even attempted to use persistant
connections to the database to get around the connection overhead... and
THAT just seemed to eat up all the system's memory while giving little
or no gain in performance. All of my optimizations seem to help at
first, but everything soon gets just as slow as it was before... and I
mean SLOW.


Sooo.... I'll give you as much info as I can... it may be too much, but
I suppose that's better than not enough....

# HARDWARE #
Pentium 4 2.5ghz
1.5 gb of DDR 400
Unlimited bandwidth
# MEMORY USAGE (top output sorted on memory) of persistant PgSQL
connections:
27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46
0
postmaster
27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25
0
postmaster
27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20
0
postmaster
27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36
0
postmaster
27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32
0
postmaster
27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40
0
postmaster
27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24
0
postmaster
27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25
0
postmaster
27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22
0
postmaster
27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18
0
postmaster
27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26
0
postmaster
27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15
0
postmaster
27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26
0
postmaster
27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17
0
postmaster
27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30
0
postmaster
27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03
0
postmaster
27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00
0
postmaster
27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00
0
postmaster
27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00
0
postmaster
27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02
0
postmaster
27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02
0
postmaster
27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02
0
postmaster
27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00
0
postmaster
27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00
0
postmaster
27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00
0
postmaster
27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00
0
postmaster
27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00
0
postmaster
27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00
0
postmaster
27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00
0
postmaster
27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00
0
postmaster
1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0
mdmpd
27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00
0
postmaster
27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00
0
postmaster



# WEBSITE #
30-60 users online at any given time
15,000 rows in the 'threads' table
joined on
300,000 rows in the 'posts' table
Total size of database on disk is 1.1 Gigabytes

# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
(this happened to be not so bad.... which may say a lot since it
took 10 seconds...)

8 Queries Totaling 10.7413 Seconds

SQL: SELECT count(*) AS count FROM thread_listing
Num Rows: 1
Affected Rows: 0
Exec Time: 0.75249910354614

SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0
AND threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1
AND promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3
AND promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713

SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754

SQL: SELECT userid,username FROM users WHERE userid IN (select
* from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348



# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------

#-----------------------------------------------------------------------
----
# CONNECTIONS AND AUTHENTICATION

#-----------------------------------------------------------------------
----
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#-----------------------------------------------------------------------
----
# RESOURCE USAGE (except WAL)

#-----------------------------------------------------------------------
----
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072

# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes
each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''


#-----------------------------------------------------------------------
----
# WRITE AHEAD LOG

#-----------------------------------------------------------------------
----
# - Settings -
fsync = true # turns forced synchronization on or
off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each

# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000


#-----------------------------------------------------------------------
----
# QUERY TUNING

#-----------------------------------------------------------------------
----

# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch
cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000 #from_collapse_limit =
8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
JOINs


#-----------------------------------------------------------------------
----
# ERROR REPORTING AND LOGGING

#-----------------------------------------------------------------------
----
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1

#silent_mode = false # DO NOT USE without Syslog!

# - What to Log -

debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false



#-----------------------------------------------------------------------
----
# RUNTIME STATISTICS

#-----------------------------------------------------------------------
----

# - Statistics Monitoring -

log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false

# - Query/Index Statistics Collector -

stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false



#-----------------------------------------------------------------------
----
# CLIENT CONNECTION DEFAULTS

#-----------------------------------------------------------------------
----

# - Statement Behavior -

#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
encoding

# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error
message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting

# - Other Defaults -

explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10



#-----------------------------------------------------------------------
----
# LOCK MANAGEMENT

#-----------------------------------------------------------------------
----

#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes
each



#-----------------------------------------------------------------------
----
# VERSION/PLATFORM COMPATIBILITY

#-----------------------------------------------------------------------
----

# - Previous Postgres Versions -

#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false






PLEASE HELP ME FIND THE BOTTLE NECK!!

Sometimes just ONE page load (approx. 13 queries) takes up to 30
seconds, and that is absolutely unacceptable in terms of online use. If
I can't fix this I'm going to have to spend the month or two switching
back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!

Thanks in advance!!

- Shane




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Uwe C. Schroeder]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

EXPLAIN is your friend here.
Well, the above query is nice for MySQL - being a fast reader. I bet it
doesn't use an index at all. You're selecting ALL rows from the table, then
sort ALL rows and only retrieve 25. This means if you have 1 million rows in
the table, postgres has to sort 1 million rows just to return the latest 25.
Maybe you want to rethink the approach to this. Databases are designed to pull
out specific records from a large set of records and that's where most
databases are good at. Design the query in a way that allows to isolate the
records you want in a "where clause".
In the above query you could already put an index on thread_listing.status and
write:
select * from thread_listing where status=5 order by lastreply desc limit 25
offset 0
Although not yet good, it will already only take the rows with status=5 into
consideration. If you only have 20% of records with status=5 it will not sort
1 million records anymore, but only 200000.

Your problem is not the speed of the DB, it's bad query design (and possibly
bad database design not using keys and indexes properly). Using indexes makes
write operations slower, but in your case that doesn't matter because nobody
will post 1000 threads in a second, however many may want to read them, which
is where the index will increase speed exponentially
Example: I have a data-warehouse on postgres. One particular operation would
store 32 records to one table and afterwards read the records just stored.
Without indexes the read took about 20 seconds, because a lot of joins to
other tables were involved. After I properly indexed the keys used to join to
the other tables the execution time went down to about a second - and the
database isn't even optimized for my application, it's a vanilla standard out
of the box configuration in postgresql.conf (except for increased shared
memory to allow more simultaneous connections). I bet I could get the
execution-time to less than 1/2 second if I'd care about this.


UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBXYp0jqGXBvRToM4RAgJkAJ4tu7tPyzieAjQ1+pwFr+6sa1CffgCgxCYb
K6szMn9iVelBmL5tqrE0YS4=
=tYYW
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Steve Crawford]

Post by Shane | SkinnyCorp
Hey, my name is Shane Witschen and I'm the Systems Administrator
(mainly a developer) for a small web development company. We
recently switched over to PostgreSQL after going over some of the
powerful features that it holds over MySQL.
However, after we launched one site, we slapped ourselves in the
head. We have run a message board for a few years now, and have
always used MySQL for the backend, but recently switched to
PostgreSQL. It has always been lightening fast with MySQL, but has
slowed to nearly a halt in terms of online access time. I can't
seem to do anything about it!! PLEASE HELP US!!

Others have mentioned issues with indexes, schema and such. Let me
explain vacuum.

If you haven't vacuumed regularly then you may have very large disk
files relative to your database size - expecially if you have much
update activity.

PG, unlike mysql, has full multi-version concurrency control. This is
just one piece of the power that sets it apart from mysql and others.
With MVCC in PG, every record that is updated within a transaction is
duplicated on-disk. The updated tuples are not visible to other
transactions until the updating transaction is complete. The old
versions of the updated tuples are no longer visible when all running
transactions no longer need the old tuples.

Vacuuming marks the unused space as reusable. If you did a lot of
development without regular vacuums then your disk size may be huge
compared to what is necessary. You can physically shrink the file by
running a "vacuum full" but this will lock the table for the duration
of the vacuum (probably not too much of a problem if you are already
seeing 10+ second page loads).

Note: getting your disk files to a size that allows them to be better
cached by the OS can make a huge difference in query speed -
especially if you are doing full table scans either intentionally or
due to bad design.

The "analyze" command updates the statistics that PG needs in order
for the query planner to make appropriate use of indexes and such.

After running your "vacuum full", be sure to run "vacuum analyze"
regularly. Check out the autovacuum daemon to help automate this
process.

I have a similar machine (but more RAM) and can easily run a count(*)
of a 4+ million row table in well under 4 seconds which makes me
suspicious of your vacuuming.

Cheers,
Steve


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Steve Crawford]

Funny...
And yes, that's great about your similar machine with more RAM...
second at any given moment?
...
I didn't need a friggin' tutorial on vacuum man... I need some
advice. I don't need a class in database design either, and I know
quite well how postgresql works, but aside from this I opted to
drop whatever I *think* I know about pgsql, and ask some of the
'experts' on this list...
And all I'm getting is shit straight out of the damn manual. Which
I've read. Many times...
Got any other suggestions?
- Shane

Um, a new attitude would help - if you knew all the answers you
wouldn't be asking the questions. Before biting off the heads of
those trying to help you, try reading
http://linuxmafia.com/faq/Essays/smart-questions.html by Eric S.
Raymond and Rick Moen.

I assume from your reading list you also read:
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

Some of your postgresql.conf settings look _way_ out of the normal
range. A couple that jump out:

...

wal_buffers = 8192 # min 4, 8KB each

64 MB for wal_buffers? What analysis led you to set this to over 2000
times higher than the default? Mine is set to 64 buffers which seems
fine but of course ymmv. At least it is shared rather than
per-process memory so it's not completely killing you.

vacuum_mem = 127072

Also looks way too high. This is settable per connection. Bump it up
when running your vacuum fulls if you need to.

You only showed pg processes from top. What is the swapping activity
on your machine? Do you have memory left over for caching?

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Gaetano Mendola]

Post by Shane | SkinnyCorp
# PGSQL Version 7.4.2

Upgrade to 7.4.5

Post by Shane | SkinnyCorp
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072

Here vacuum_mem is too high, memory wasted, if you do
regular vacuum or you use the autovacuum daemon then
usualy 32MB are enough

Post by Shane | SkinnyCorp
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each

you didn't show us the result of

vacuum full verbose


so I can not tell you if they are enough or not.

Post by Shane | SkinnyCorp
enable_seqscan = false

enable_seqscan = true

Do you think that an index scan is always worst then a seqscan ?

Post by Shane | SkinnyCorp
random_page_cost = 4 # units are one sequential page fetch cost

decrease this value to 2.5

Post by Shane | SkinnyCorp
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false

I suggest you to start the collectors

stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false



Regards
Gaetano Mendola

[Shane | SkinnyCorp]

Hi... I take vast offense to the suggestion that my query / database design
is at fault here. I highly doubt it, although I AM willing to admit fault
where the fault is indeed my own. However, on the topic of sorting all
15,000 rows of the thread select query and then limiting the output to 25,
that is absolutely unavoidable, and I don't see how query / table design is
at any fault of my own. How else do you suggest I grab the 25 most recent
threads posted?!? Exactly.

As for vacuum... good lord. I run vacuum full on the database every single
night through a cron job, simply because pg_autovacuum or whatever needs the
stats collector running, and that creates even MORE overhead, so I choose to
do it myself.

I must admit that I expected much more from this list than I've recieved so
far, as I've gotten about 20 ppl suggesting that I use vacuum (which
honestly should've been implied, but maybe it was my bad for not
mentioning), and one guy telling me that my queries are at fault...

I mean, wtf?

I do admit that there could be areas in which my queries could be better
written for speed, but nothing in that gentleman's post had any significance
or relevance to my issue whatsoever.

I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I can work with.

Also of note is that much of my design and indexing was in attempt to speed
up an already lagging database... as in, adding triggers to update totals
and insert them into the threads table instead of doing a count on posts
every query...

Anyhow, as for an explain analyze on some queries, I can provide that... but
it's ALWAYS using index scans (i made sure of this), and things like a
simple update on a 2000 row table can take up to 10 seconds... which is
ludicrous.

One last thing... even with ALL of the data intact (and yes, we DID do
testing... we just didn't have enough ppl to test the production server
load), if we shut out ALL of the users, and run these queries... they are
LIGHTNING fast... but with the user load... it's horrid.

The only information I can give at the moment about the number of queries
per second is this: there is an average of 60 users online at any given
time, and the average number of queries per page load is 12, and they are
refreshing and clicking etc quite a bit... so I'd say about 120 queries per
second or so... (VERY rough estimate)....


Got any suggestions now?!? I was sort of looking for more information /
insight on my postgresql.conf file... but it seems we had to get the "IS HE
A MORON" question answered :P

Anyhow, again thank you for any help you can lend...

- Shane Witschen


----- Original Message -----
From: "Goulet, Dick" <***@vicr.com>
To: "Shane | SkinnyCorp" <***@skinnycorp.com>; "PgSQL ADMIN"
<pgsql-***@postgresql.org>
Sent: Friday, October 01, 2004 11:46 AM
Subject: Re: [ADMIN] PLEASE GOD HELP US!

Post by Goulet, Dick
Shane,
Two things: 1) when was the last time you vacuumed the
database? From my experience that has a large effect on the database
performance. 2) Why do you have so many postmaster processes? I've got
an active database but only one postmaster.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Friday, October 01, 2004 12:05 PM
To: PgSQL ADMIN
Subject: [ADMIN] PLEASE GOD HELP US!
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly
a
developer) for a small web development company. We recently switched
over to PostgreSQL after going over some of the powerful features that
it holds over MySQL.
However, after we launched one site, we slapped ourselves in the head.
We have run a message board for a few years now, and have always used
MySQL for the backend, but recently switched to PostgreSQL. It has
always been lightening fast with MySQL, but has slowed to nearly a halt
in terms of online access time. I can't seem to do anything about it!!
PLEASE HELP US!!
Now, I've read as much as I could about optimizing PostgreSQL for
performance, and nothing I do seems to help anything. Just so I don't
get 20 links to the same sites I've read... I'll post what I've already
used for
http://postgis.refractions.net/pipermail/postgis-users/2004-January/0037
57.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
Nothing makes much of a difference. I even attempted to use persistant
connections to the database to get around the connection overhead... and
THAT just seemed to eat up all the system's memory while giving little
or no gain in performance. All of my optimizations seem to help at
first, but everything soon gets just as slow as it was before... and I
mean SLOW.
Sooo.... I'll give you as much info as I can... it may be too much, but
I suppose that's better than not enough....
# HARDWARE #
Pentium 4 2.5ghz
1.5 gb of DDR 400
Unlimited bandwidth
# MEMORY USAGE (top output sorted on memory) of persistant PgSQL
27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46
0
postmaster
27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25
0
postmaster
27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20
0
postmaster
27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36
0
postmaster
27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32
0
postmaster
27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40
0
postmaster
27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24
0
postmaster
27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25
0
postmaster
27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22
0
postmaster
27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18
0
postmaster
27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26
0
postmaster
27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15
0
postmaster
27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26
0
postmaster
27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17
0
postmaster
27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30
0
postmaster
27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03
0
postmaster
27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00
0
postmaster
27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00
0
postmaster
27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00
0
postmaster
27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02
0
postmaster
27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02
0
postmaster
27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02
0
postmaster
27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00
0
postmaster
27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00
0
postmaster
27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00
0
postmaster
27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00
0
postmaster
27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00
0
postmaster
27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00
0
postmaster
27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00
0
postmaster
27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00
0
postmaster
1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0
mdmpd
27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00
0
postmaster
27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00
0
postmaster
# WEBSITE #
30-60 users online at any given time
15,000 rows in the 'threads' table
joined on
300,000 rows in the 'posts' table
Total size of database on disk is 1.1 Gigabytes
# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
(this happened to be not so bad.... which may say a lot since it
took 10 seconds...)
8 Queries Totaling 10.7413 Seconds
SQL: SELECT count(*) AS count FROM thread_listing
Num Rows: 1
Affected Rows: 0
Exec Time: 0.75249910354614
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0
AND threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1
AND promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3
AND promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4
AND promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754
SQL: SELECT userid,username FROM users WHERE userid IN (select
* from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348
# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#-----------------------------------------------------------------------
----
# CONNECTIONS AND AUTHENTICATION
#-----------------------------------------------------------------------
----
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#-----------------------------------------------------------------------
----
# RESOURCE USAGE (except WAL)
#-----------------------------------------------------------------------
----
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes
each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''
#-----------------------------------------------------------------------
----
# WRITE AHEAD LOG
#-----------------------------------------------------------------------
----
# - Settings -
fsync = true # turns forced synchronization on or
off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#-----------------------------------------------------------------------
----
# QUERY TUNING
#-----------------------------------------------------------------------
----
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch
cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000 #from_collapse_limit =
8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
JOINs
#-----------------------------------------------------------------------
----
# ERROR REPORTING AND LOGGING
#-----------------------------------------------------------------------
----
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#-----------------------------------------------------------------------
----
# RUNTIME STATISTICS
#-----------------------------------------------------------------------
----
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false
#-----------------------------------------------------------------------
----
# CLIENT CONNECTION DEFAULTS
#-----------------------------------------------------------------------
----
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database
encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error
message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#-----------------------------------------------------------------------
----
# LOCK MANAGEMENT
#-----------------------------------------------------------------------
----
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes
each
#-----------------------------------------------------------------------
----
# VERSION/PLATFORM COMPATIBILITY
#-----------------------------------------------------------------------
----
# - Previous Postgres Versions -
#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
PLEASE HELP ME FIND THE BOTTLE NECK!!
Sometimes just ONE page load (approx. 13 queries) takes up to 30
seconds, and that is absolutely unacceptable in terms of online use. If
I can't fix this I'm going to have to spend the month or two switching
back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!
Thanks in advance!!
- Shane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Christian Fowler]

Post by Shane | SkinnyCorp
I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I can work with.

Here are three concise suggestions:

1. You say your issue is only under load, then I can probably guarantee
your issue is available connections:

max_connections = 50

That number is way too small for a site with decent traffic. make it like
500. how mindful are you about opening connections per page view?

2. You have a query taking 9 seconds. run that query by hand on the shell
to find out why. Rework the query, add or recreate indices as necessary.
Using conditionals in ORDER BY is a bad idea. Do it in the WHERE clause
per my previous email.

Post by Shane | SkinnyCorp
I take vast offense to the suggestion that my query / database design is
at fault here.... I must admit that I expected much more from this list
than I've recieved so far.

make it clear that you have a lot more room for growth as a developer. If
you stop biting the hands that help you, you will learn a lot more.

Post by Shane | SkinnyCorp
One last thing... even with ALL of the data intact (and yes, we DID do
testing... we just didn't have enough ppl to test the production server
load)

Another mistake showing your lack of experience. Use apache bench ( ab
command ) to simulate load.

Post by Shane | SkinnyCorp
The only information I can give at the moment about the number of queries
per second is this: there is an average of 60 users online at any given
time, and the average number of queries per page load is 12, and they are
refreshing and clicking etc quite a bit... so I'd say about 120 queries per
second or so... (VERY rough estimate)....

And you only have 50 max_connections for postgres? What are you thinking?
Of course every apache process is waiting for a connection. Also, do you
even have enough apache workers?

Your attitude sucks. Your problems are right under your nose, and
you are too cocky to learn your tools. I imagine you are getting hammered
by your co-workers to get things fixed. I will tell you empathy will
always get you much farther than animosity.


[ \ /
[ >X< ***@steelsun.com | http://www.steelsun.com/
[ / \

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Rosser Schwarz]

Post by Shane | SkinnyCorp
How else do you suggest I grab the 25 most recent
threads posted?!?

select *
from thread_listing t
where t.status = 5
order by lastreply desc
limit 25
offset 0

The WHERE clause is there to limit the number of tuples you're looking
at. Without a WHERE clause, every tuple in the relation is pulled.

Compare the following queries and query plans, run against our
production database. The columns and types are different, but the
logic is identical. Note from the row counts in the first query that
this is a 5+m row table. Such queries generally return in fractions
of seconds for us.

tci=> explain select * from account.cust order by prodid = 153::bigint
desc, createddt desc limit 25 offset 0;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=1785296.85..1785296.91 rows=25 width=272)
-> Sort (cost=1785296.85..1797950.28 rows=5061375 width=272)
Sort Key: (prodid = 153::bigint), createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=5061375 width=272)
(4 rows)

tci=> explain select * from account.cust where prodid = 153::bigint
order by createddt desc limit 25 offset 0;
QUERY PLAN
---------------------------------------------------------------------------
Limit (cost=208299.77..208299.83 rows=25 width=272)
-> Sort (cost=208299.77..208334.13 rows=13743 width=272)
Sort Key: createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=13743 width=272)
Filter: (prodid = 153::bigint)
(5 rows)

The query with a WHERE clause has to look at and sort 13,743 rows; the
query without has to look at and sort all 5+m.

Which would you expect to be faster?

Post by Shane | SkinnyCorp
Exactly.

/rls

[William Yu]

Post by Shane | SkinnyCorp
I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I can work with.

My 2 cents. When I see a message titled "PLEASE GOD HELP US", my first
thought is not "experienced db designer". Less bombastic statements,
more logs (vacuum verbose, explain analyze), db stats, cpu stats, i/o
stats -- would have (1) cut down on the obvious advice which you say you
already know and (2) let the experts hone in on the problem. It's not
like we can guess what your system is doing.



Here's a few things that may or may not mean anything. Just random
guesses off the top of my head due to the lack of detailed info.

Post by Shane | SkinnyCorp
Nothing makes much of a difference. I even attempted to use
persistant connections to the database to get around the connection
overhead... and THAT just seemed to eat up all the system's memory
while giving little or no gain in performance.

At buffers of 8192, that's just 64MB used up for shared memory + a few
MB per connection. At the max 60 users clicking on submit at exactly the
same time, that's about 300MB of memory compared to your total of 1.5GB.
If something is "eating up all the system memory", it isn't Postgres and
it certainly wouldn't be a connection pooling mechanism.

Post by Shane | SkinnyCorp
max_connections = 50

A bit low in db connections for a production website. Fine for
development but I personally max my DBs out at the full 1024. No harm in
setting it really high if you can allocate the shared memory. Also could
be causing delays if you don't have enough connections for the number of
users. Dunno

Post by Shane | SkinnyCorp
enable_seqscan = false

There are cases where seqscan will be faster than indexscans. For
example, your query to retrieve the latest 25 threads -- always faster
using seqscan. If it was using indexscan, that would explain the 9
seconds to run because the HD heads would have to jump back & forth from
index to table for every record. (And as an experienced sysadmin, you
should know that HD latency is way more expensive than HD throughput.)
On my dev system (P3/750, 1GB ram, 1 IDE HD), I get the following
results on a 36K table:

SELECT * FROM 16K_table ORDER BY un_indexed_fld DESC LIMIT 25
--> 5s -- 1st run
--> .013s -- 2nd run

Doing the selects on an indexed field -- my results start at < 100 msec
(1st run) and drops to < 1 msec for cached I/O.

[William Yu]

Post by William Yu
There are cases where seqscan will be faster than indexscans. For
example, your query to retrieve the latest 25 threads -- always faster
using seqscan. If it was using indexscan, that would explain the 9
seconds to run because the HD heads would have to jump back & forth from

Oops, I didn't write down my thoughts clearly. I meant to say --
DEPENDING ON THE DISTRIBUTION OF THE VALUES -- the query might be faster
using seqscan.

[Geoffrey]

Post by William Yu

Post by Shane | SkinnyCorp
I would like to also add that I've been a sysadmin for quite some time, and
I've been designing databases for quite some time as well. I'm no idiot, I
just can't find the bottleneck here (if one does in fact exist). So in light
of this, please send me some suggestions I can work with.

My 2 cents. When I see a message titled "PLEASE GOD HELP US", my first
thought is not "experienced db designer".

Not to drag this off topic too far, but I never caught the post that
began this thread, because the subject line caused an immediate delete
as I just new it was a variation of a scam..

Useful subject lines would be appreciated in the future.

[Matt Clark]

Post by Shane | SkinnyCorp
Got any suggestions now?!? I was sort of looking for more information /
insight on my postgresql.conf file... but it seems we had to get the "IS HE
A MORON" question answered :P
Anyhow, again thank you for any help you can lend...

Well, try not to SHOUT is a good suggestion. Also, how about posting
the output of explain analyze for the queries that go into a making
typical page (taken while the DB is under load preferably).

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Christian Fowler]

Hi Shane,

As many others have alluded to - performance like this is almost always
attributable to your queries not using an index. Be it on Oracle, Mysql,
or postgres, i have seen this problem popup often.

Also, could you tell us what language you are using, and if you are using
a DB abstraction layer?

Post by Shane | SkinnyCorp
# WEBSITE #
# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
8 Queries Totaling 10.7413 Seconds

Since one query is taking 90% of the time, it clearly is the first

Post by Shane | SkinnyCorp
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464

Your SQL here seems what I would consider not typical. I would write it
as:

SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY t.lastreply
desc LIMIT 25 OFFSET 0;

Run that from a psql shell, and see if that speed things up. If not, run:

db=> EXPLAIN ANALYSE SELECT * FROM thread_listing AS t WHERE t.status=5
ORDER BY t.lastreply desc LIMIT 25 OFFSET 0;

and

db=> \d thread_listing

And send it to the list. You are in good shape I think, and porting won't
be necessary. I've used many db's and postgres is my favorite by far. I'd
say you've made a good choice ;-)


[ \ /
[ >X< ***@steelsun.com | http://www.steelsun.com/
[ / \

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Shane | SkinnyCorp]

Okay, just so no one posts about this again...

the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

:/


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Uwe C. Schroeder]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

One quick idea that comes to my head without knowing your database structure
(which would be neccessary to do this properly) is:
use a serial field as row key (int4 with a sequence as default)
in your query grab the current value from the sequence, subtract 25 and select
all records with a key larger than that number.
Et voila: you only select 25 records with a where clause that will use an
index properly. I bet that runs in a couple of milliseconds.

BTW: You're asking and complaining about a "lagging database". You think
nothing you did is at fault. Why then ask in the first place if you don't
want to hear the answers?
I've been working with databases long before postgres was born (over 23
years). I still ask questions on this list and usually get good responses.


UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFBXcKpjqGXBvRToM4RAv4YAJ9cj/P+vCbbJXNf1G5Dh3dWnZzkDQCgqdfa
lxicqf7qRS6hA7Zzv0v94/U=
=e94/
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Michael Paesold]

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.
:/

Perhaps I missed it, but you did not yet send the output of the explain of
this query, did you?

Could you please do:

EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC,
t.lastreply DESC LIMIT 25 OFFSET 0;

EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY
t.lastreply DESC LIMIT 25 OFFSET 0;

EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25
OFFSET 0;

(and post the results here)

The first one will certainly do a sequential scan, the last one will use an
index if available. For the second you will need a partial index on
lastreply with a where clause WHERE status=5, I believe. So a solution would
perhaps be to use two queries, one with WHERE t.status=5, another without.
Make both use an index. Then combine them in your application. Of course
this can only work if you do not need an offset other then 0.

Also, did you check that your system does not swap (using vmstat)?

Best Regards,
Michael Paesold


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Stephan Szabo]

Post by Michael Paesold

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.
:/

Perhaps I missed it, but you did not yet send the output of the explain of
this query, did you?
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.status=5 DESC,
t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t WHERE t.status=5 ORDER BY
t.lastreply DESC LIMIT 25 OFFSET 0;
EXPLAIN SELECT * FROM thread_listing AS t ORDER BY t.lastreply DESC LIMIT 25
OFFSET 0;
(and post the results here)
The first one will certainly do a sequential scan, the last one will use an
index if available. For the second you will need a partial index on
lastreply with a where clause WHERE status=5, I believe. So a solution would

Actually, I think he'd want an expression index on ((status=5),lastreply).
In simple tests (admittedly on 8.0b3) it looks like such an index can be
used rather than a separate sort step.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Scott Marlowe]

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?

SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Assuming 5 is the highest number. If not, a where clause entry might
make sense.

SELECT * FROM thread_listing AS t
WHERE t.status <= 5
ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Or is the data set put together some weird way that makes that
impossible?


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

Post by Scott Marlowe

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?
SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Probably not, because I don't think he wants the other statuses to have
special ranking over the others, so a status=4 and status=1 row should be
sorted by lastreply only effectively. This is the problem of combining
separate status flags into a single field if you want to be doing these
sorts of queries.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Scott Marlowe]

Post by Stephan Szabo

Post by Scott Marlowe

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?
SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Probably not, because I don't think he wants the other statuses to have
special ranking over the others, so a status=4 and status=1 row should be
sorted by lastreply only effectively. This is the problem of combining
separate status flags into a single field if you want to be doing these
sorts of queries.

So would a union give good performance? Just union the first 25 or less
with status=5 with the rest, using a 1 and 0 in each union to order by
first? Hopefully the indexes would then be used.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

Post by Scott Marlowe

Post by Stephan Szabo

Post by Scott Marlowe

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?
SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Probably not, because I don't think he wants the other statuses to have
special ranking over the others, so a status=4 and status=1 row should be
sorted by lastreply only effectively. This is the problem of combining
separate status flags into a single field if you want to be doing these
sorts of queries.

So would a union give good performance? Just union the first 25 or less
with status=5 with the rest, using a 1 and 0 in each union to order by
first? Hopefully the indexes would then be used.

You'd want to use union all I think and you may have to put limits on the
unioned arms to get good behavior. I think the expression index would
probably work without changing the query (at least for relatively low
offsets) at the cost of having an extra index to maintain.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Scott Marlowe]

Post by Stephan Szabo

Post by Scott Marlowe
So would a union give good performance? Just union the first 25 or less
with status=5 with the rest, using a 1 and 0 in each union to order by
first? Hopefully the indexes would then be used.

You'd want to use union all I think and you may have to put limits on the
unioned arms to get good behavior. I think the expression index would
probably work without changing the query (at least for relatively low
offsets) at the cost of having an extra index to maintain.

I wonder if this would offer any improvement:

select * from (
(SELECT 2 as a,* FROM thread_listing AS t
where t.status=5
ORDER BY t.lastreply desc limit 25)
union
(SELECT 1 as a,* FROM thread_listing AS t
where t.status<>5
order by t.lastreply desc limit 25)
) as p
order by p.a desc, p.lastreply desc limit 25;

Or is there a moving offset we have to deal with here?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Gaetano Mendola]

Post by Scott Marlowe

Post by Stephan Szabo

Post by Scott Marlowe

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?
SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Probably not, because I don't think he wants the other statuses to have
special ranking over the others, so a status=4 and status=1 row should be
sorted by lastreply only effectively. This is the problem of combining
separate status flags into a single field if you want to be doing these
sorts of queries.

So would a union give good performance? Just union the first 25 or less
with status=5 with the rest, using a 1 and 0 in each union to order by
first? Hopefully the indexes would then be used.

anyone seen that the OP is running the server with sequential scan disabled ?


Reagards
Gaetano Mendola

[Stephan Szabo]

Post by Gaetano Mendola

Post by Scott Marlowe

Post by Stephan Szabo

Post by Scott Marlowe

Post by Shane | SkinnyCorp
Okay, just so no one posts about this again...
the 'ORDER BY t.status=5,lastreply' clause is meant to float the threads
with a status of '5' to the top of the list... it is NOT meant to only grab
threads where the status = 5. Oh and believe me, when I take this out of
the query, it CERTAINLY doesn't add any more than possible 1/4 of a
millesecond to the speed of the SELECT statement.

Wouldn't this work just as well?
SELECT * FROM thread_listing AS t ORDER BY t.status
DESC,t.lastreply desc LIMIT 25 OFFSET 0

Probably not, because I don't think he wants the other statuses to have
special ranking over the others, so a status=4 and status=1 row should be
sorted by lastreply only effectively. This is the problem of combining
separate status flags into a single field if you want to be doing these
sorts of queries.

So would a union give good performance? Just union the first 25 or less
with status=5 with the rest, using a 1 and 0 in each union to order by
first? Hopefully the indexes would then be used.

anyone seen that the OP is running the server with sequential scan disabled ?

Yep, but he's doing some queries for which the indexes weren't useful
unless he had some very specific ones, so it'd still be choosing
sequential scans for those AFAICT (for example I believe the 9s get the
first 25 threads query).

I think someone'd already pointed out that running with sequential scan
disabled is generally a bad idea, so I didn't think it was worth
mentioning again.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bradley Kieser]

Sounds like all you need to do is to check what keys your app needs on
the tables and then ensure that you have these set up.
There is no way that MySQL will be faster than PG... seems like your
MySQL DB was optimised and your PG wasn't.

Should be very easy and quick to sort out.

Brad

Post by Shane | SkinnyCorp
Hey, my name is Shane Witschen and I'm the Systems Administrator (mainly a
developer) for a small web development company. We recently switched over
to PostgreSQL after going over some of the powerful features that it holds
over MySQL.
However, after we launched one site, we slapped ourselves in the head. We
have run a message board for a few years now, and have always used MySQL for
the backend, but recently switched to PostgreSQL. It has always been
lightening fast with MySQL, but has slowed to nearly a halt in terms of
online access time. I can't seem to do anything about it!! PLEASE HELP
US!!
Now, I've read as much as I could about optimizing PostgreSQL for
performance, and nothing I do seems to help anything. Just so I don't get
20 links to the same sites I've read... I'll post what I've already used for
http://postgis.refractions.net/pipermail/postgis-users/2004-January/003757.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.phpbuilder.com/columns/smith20010821.php3?page=2
http://techdocs.postgresql.org/techdocs/pgsqldbtuning.php
http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
Nothing makes much of a difference. I even attempted to use persistant
connections to the database to get around the connection overhead... and
THAT just seemed to eat up all the system's memory while giving little or no
gain in performance. All of my optimizations seem to help at first, but
everything soon gets just as slow as it was before... and I mean SLOW.
Sooo.... I'll give you as much info as I can... it may be too much, but I
suppose that's better than not enough....
# HARDWARE #
Pentium 4 2.5ghz
1.5 gb of DDR 400
Unlimited bandwidth
# MEMORY USAGE (top output sorted on memory) of persistant PgSQL
27149 postgres 15 0 74840 73M 71360 S 0.3 4.8 1:46 0
postmaster
27392 postgres 15 0 72976 71M 70084 S 0.0 4.7 0:25 0
postmaster
27448 postgres 16 0 72708 70M 70052 S 2.5 4.6 0:20 0
postmaster
27367 postgres 15 0 72036 70M 70132 S 0.0 4.6 0:36 0
postmaster
27401 postgres 15 0 71908 70M 69920 S 0.0 4.6 0:32 0
postmaster
27320 postgres 15 0 71900 70M 69844 S 0.5 4.6 0:40 0
postmaster
27443 postgres 17 0 71880 70M 69368 S 8.5 4.6 0:24 0
postmaster
27441 postgres 15 0 71832 70M 69336 S 0.0 4.6 0:25 0
postmaster
27437 postgres 16 0 71828 70M 69812 S 0.7 4.6 0:22 0
postmaster
27466 postgres 16 0 71788 70M 69432 S 2.5 4.6 0:18 0
postmaster
27403 postgres 16 0 71780 70M 69816 S 0.1 4.6 0:26 0
postmaster
27467 postgres 15 0 71728 69M 69384 S 0.0 4.6 0:15 0
postmaster
27405 postgres 15 0 71496 69M 69612 S 0.0 4.6 0:26 0
postmaster
27468 postgres 15 0 71392 69M 69108 S 0.0 4.6 0:17 0
postmaster
27439 postgres 15 0 71184 69M 69456 S 0.0 4.5 0:30 0
postmaster
27488 postgres 15 0 71184 69M 68996 S 10.5 4.5 0:03 0
postmaster
27489 postgres 15 0 70176 68M 68752 S 1.1 4.5 0:00 0
postmaster
27526 postgres 20 0 70020 68M 68752 S 17.3 4.5 0:00 0
postmaster
27499 postgres 16 0 61204 59M 59620 S 5.9 3.9 0:00 0
postmaster
27507 postgres 17 0 55040 53M 52888 S 24.1 3.5 0:02 0
postmaster
27491 postgres 15 0 53988 52M 51824 S 0.0 3.4 0:02 0
postmaster
27490 postgres 15 0 53040 51M 50880 S 0.0 3.4 0:02 0
postmaster
27520 postgres 15 0 41960 40M 40428 S 1.3 2.7 0:00 0
postmaster
27494 postgres 15 0 41224 40M 39876 S 0.7 2.6 0:00 0
postmaster
27492 postgres 15 0 38980 38M 37552 S 0.3 2.5 0:00 0
postmaster
27517 postgres 15 0 18444 17M 17308 S 0.5 1.1 0:00 0
postmaster
27522 postgres 18 0 14112 13M 12976 S 0.3 0.9 0:00 0
postmaster
27524 postgres 19 0 14040 13M 12908 S 0.3 0.9 0:00 0
postmaster
27521 postgres 18 0 13364 12M 12228 S 0.0 0.8 0:00 0
postmaster
27523 postgres 18 0 12848 12M 11716 S 0.0 0.8 0:00 0
postmaster
1935 root 15 0 12144 11M 1372 S 0.0 0.7 0:00 0
mdmpd
27516 postgres 18 0 12028 11M 10980 S 0.0 0.7 0:00 0
postmaster
27518 postgres 17 0 11932 11M 10800 S 0.0 0.7 0:00 0
postmaster
# WEBSITE #
30-60 users online at any given time
15,000 rows in the 'threads' table
joined on
300,000 rows in the 'posts' table
Total size of database on disk is 1.1 Gigabytes
# SAMPLE DUMP OF COMMON PAGE-SPECIFIC QUERIES
(this happened to be not so bad.... which may say a lot since it
took 10 seconds...)
8 Queries Totaling 10.7413 Seconds
SQL: SELECT count(*) AS count FROM thread_listing
Num Rows: 1
Affected Rows: 0
Exec Time: 0.75249910354614
SQL: SELECT * FROM thread_listing AS t ORDER BY t.status=5
DESC,t.lastreply desc LIMIT 25 OFFSET 0
Num Rows: 25
Affected Rows: 0
Exec Time: 9.1602659225464
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=0 AND
threadcategoryid<>0 ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.81906294822693
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=1 AND
promoted=true ORDER BY orderid ASC
Num Rows: 9
Affected Rows: 0
Exec Time: 0.0021350383758545
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=2 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0019958019256592
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=3 AND
promoted=true ORDER BY orderid ASC
Num Rows: 4
Affected Rows: 0
Exec Time: 0.0019819736480713
SQL: SELECT * FROM thread_categories WHERE parentcategoryid=4 AND
promoted=true ORDER BY orderid ASC
Num Rows: 5
Affected Rows: 0
Exec Time: 0.0021347999572754
SQL: SELECT userid,username FROM users WHERE userid IN (select *
from buddynetwork(0,2)) ORDER BY username ASC
Num Rows: 1
Exec Time: 0.0011849403381348
# PGSQL Version 7.4.2
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 50
#superuser_reserved_connections = 2
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#virtual_host = ''
#rendezvous_name = ''
# - Security & Authentication -
#authentication_timeout = 60
ssl = true
password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192
sort_mem = 8192
vacuum_mem = 127072
# - Free Space Map -
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
max_files_per_process = 3052 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync
wal_buffers = 8192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
enable_seqscan = false
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 4 # units are one sequential page fetch cost
cpu_tuple_cost = .01 # (same) default .01
cpu_index_tuple_cost = .001 # (same) default .001
cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error
log_min_messages = error
log_error_verbosity = default
log_min_error_statement = panic
log_min_duration_statement = -1
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
debug_pretty_print = false
log_connections = false
log_duration = false
log_pid = false
log_statement = false
log_timestamp = false
log_hostname = false
log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = false
log_planner_stats = false
log_executor_stats = false
log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false
stats_command_string = false
stats_block_level = false
stats_row_level = false
stats_reset_on_server_start = false
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment
setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message
strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
PLEASE HELP ME FIND THE BOTTLE NECK!!
Sometimes just ONE page load (approx. 13 queries) takes up to 30 seconds,
and that is absolutely unacceptable in terms of online use. If I can't fix
this I'm going to have to spend the month or two switching back to MySQL...
and I really don't want to do that, so anything you could do to help us
would be amazing!!
Thanks in advance!!
- Shane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tsirkin Evgeny]

Are there any updates/deleteed/adding to the db?What
did help you ?Are you using transactions?

Post by Shane | SkinnyCorp
gain in performance. All of my optimizations seem to help at first, but
everything soon gets just as slow as it was before... and I mean SLOW.

[Tsirkin Evgeny]

it sometimes happens that mysql is faster .obviosly you have a case
when you have to try force the query to use indexes .
here are some not nice and trivial solutions:

[1] add another column that marks the rows that are in the upper 25
now, set up trigger to update the field .that would be VERY fast
(probably much more faster then mySql or any design in any db).

[2] add a column that inserts a serial number of every row
,set a trigger to update it on insert now use the max() to find
the latest post and < max()-25 to find the others.

I can suggest more ,but you got the idea.
I understand that this complicates things and the design is less
simple/nice ,but it should speed it up.
Hope it helps.
sorry for english .