Re: PLEASE GOD HELP US! - Mailing list pgsql-admin

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@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