Re: PLEASE GOD HELP US!

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:shanew(at)skinnycorp(dot)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