Thread: PLEASE GOD HELP US!

PLEASE GOD HELP US!

From
"Shane | SkinnyCorp"
Date:
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




Re: PLEASE GOD HELP US!

From
Rosser Schwarz
Date:
while you weren't looking, Shane | SkinnyCorp wrote:

> 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

--
:wq

Re: PLEASE GOD HELP US!

From
"Joshua D. Drake"
Date:
>         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


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


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment

Re: PLEASE GOD HELP US!

From
"Marc Mitchell"
Date:
-----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> 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
marcm@eisolution.com


Re: PLEASE GOD HELP US!

From
Greg Spiegelberg
Date:
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.



Joshua D. Drake wrote:
>>         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
>
>
>>
>>         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: 614.318.4314
  Fax:   614.431.8388
  Email: gspiegelberg@cranel.com
Technology. Integrity. Focus.


Re: PLEASE GOD HELP US!

From
"Goulet, Dick"
Date:
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

Re: PLEASE GOD HELP US!

From
"Uwe C. Schroeder"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 01 October 2004 09:04 am, you wrote:

>         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:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

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


Re: PLEASE GOD HELP US!

From
Steve Crawford
Date:
On Friday 01 October 2004 9:04 am, Shane | SkinnyCorp wrote:
> 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


Re: PLEASE GOD HELP US!

From
"Shane | SkinnyCorp"
Date:
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" <DGoulet@vicr.com>
To: "Shane | SkinnyCorp" <shanew@skinnycorp.com>; "PgSQL ADMIN"
<pgsql-admin@postgresql.org>
Sent: Friday, October 01, 2004 11:46 AM
Subject: Re: [ADMIN] 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@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 majordomo@postgresql.org)


Re: PLEASE GOD HELP US!

From
Christian Fowler
Date:
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?

On to the particulars:

> # 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
cuplrit:

>        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<   spider@steelsun.com   |   http://www.steelsun.com/
[ / \

Re: PLEASE GOD HELP US!

From
Gaetano Mendola
Date:
Shane | SkinnyCorp wrote:>  # PGSQL Version 7.4.2

Upgrade to 7.4.5

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

>  # - 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.

>  enable_seqscan = false

enable_seqscan = true

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

>  random_page_cost = 4            # units are one sequential page fetch cost

decrease this value to 2.5
>  # - 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














Re: PLEASE GOD HELP US!

From
Christian Fowler
Date:

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

3. Leave your attitude at the door, or just leave. Making comments like:

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

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

>
> 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<   spider@steelsun.com   |   http://www.steelsun.com/
[ / \

Re: PLEASE GOD HELP US!

From
Rosser Schwarz
Date:
while you weren't looking, Shane | SkinnyCorp wrote:

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

> Exactly.

/rls

--
:wq

Re: PLEASE GOD HELP US!

From
"Shane | SkinnyCorp"
Date:
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.

:/


Re: PLEASE GOD HELP US!

From
"Uwe C. Schroeder"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 01 October 2004 01:26 pm, Shane | SkinnyCorp wrote:
> 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:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

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


Re: PLEASE GOD HELP US!

From
"Michael Paesold"
Date:
Shane | SkinnyCorp wrote:

> 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


Re: PLEASE GOD HELP US!

From
William Yu
Date:
Shane | SkinnyCorp wrote:

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

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


 > 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



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

Re: PLEASE GOD HELP US!

From
Stephan Szabo
Date:
On Fri, 1 Oct 2004, Michael Paesold wrote:

> Shane | SkinnyCorp wrote:
>
> > 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

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.

Re: PLEASE GOD HELP US!

From
Steve Crawford
Date:
On Friday 01 October 2004 12:26 pm, Shane | SkinnyCorp wrote:
> Funny...
>
> I vacuum full EVERY night @ midnight...
>
> And yes, that's great about your similar machine with more RAM...
> only... does YOUR table have 60+ users @ 120-some queries per
> 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

Re: PLEASE GOD HELP US!

From
Matt Clark
Date:
>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).

Re: PLEASE GOD HELP US!

From
William Yu
Date:
William Yu wrote:

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

Re: PLEASE GOD HELP US!

From
"Scott Marlowe"
Date:
On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> 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?


Re: PLEASE GOD HELP US!

From
Stephan Szabo
Date:
On Fri, 1 Oct 2004, Scott Marlowe wrote:

> On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > 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.

Re: PLEASE GOD HELP US!

From
"Scott Marlowe"
Date:
On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> On Fri, 1 Oct 2004, Scott Marlowe wrote:
>
> > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > > 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.


Re: PLEASE GOD HELP US!

From
Stephan Szabo
Date:
On Sat, 2 Oct 2004, Scott Marlowe wrote:

> On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> > On Fri, 1 Oct 2004, Scott Marlowe wrote:
> >
> > > On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> > > > 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.


Re: PLEASE GOD HELP US!

From
"Scott Marlowe"
Date:
On Sat, 2004-10-02 at 15:42, Stephan Szabo wrote:
> On Sat, 2 Oct 2004, Scott Marlowe wrote:
> > 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?


Re: PLEASE GOD HELP US!

From
Gaetano Mendola
Date:
Scott Marlowe wrote:
> On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
>
>>On Fri, 1 Oct 2004, Scott Marlowe wrote:
>>
>>
>>>On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
>>>
>>>>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


Re: PLEASE GOD HELP US!

From
Geoffrey
Date:
William Yu wrote:
> Shane | SkinnyCorp wrote:
>
>> 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.

--
Until later, Geoffrey       Registered Linux User #108567
                             AT&T Certified UNIX System Programmer - 1995

Re: PLEASE GOD HELP US!

From
Bradley Kieser
Date:
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

Shane | SkinnyCorp wrote:

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

Re: PLEASE GOD HELP US!

From
Stephan Szabo
Date:
On Sun, 3 Oct 2004, Gaetano Mendola wrote:

> Scott Marlowe wrote:
> > On Sat, 2004-10-02 at 09:14, Stephan Szabo wrote:
> >
> >>On Fri, 1 Oct 2004, Scott Marlowe wrote:
> >>
> >>
> >>>On Fri, 2004-10-01 at 14:26, Shane | SkinnyCorp wrote:
> >>>
> >>>>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.

Re: PLEASE GOD HELP US!

From
Tsirkin Evgeny
Date:
Are there any updates/deleteed/adding to the db?What
did help you ?Are you using transactions?

> 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.
>
--
Evgeny.

Re: PLEASE GOD HELP US!

From
Tsirkin Evgeny
Date:
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 .
--
Evgeny.

Re: PLEASE GOD HELP US!

From
Chris Travers
Date:

I have a question:

You say this happens under load and that it slows down.  I would think
that a problem with max connections would cause other problems but not
the slowness.  Someone can correct me though.

What sort of performance tuning have you done?  I suspect we can help
you narrow down the problems.  Historically, the default settings are
designed around systems with limited memory so that it will *start* on
anything.  To get decent performance under load you have to change some
settings.

Also how much RAM do you have?  Which version of PostgreSQL are you running?

Best Wishes,
Chris Travers
Metatron Technology Consulting

Christian Fowler wrote:

>
>
>> 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.
>
> 3. Leave your attitude at the door, or just leave. Making comments like:
>
>> 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.
>
>> 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.
>
>>
>> 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<   spider@steelsun.com   |   http://www.steelsun.com/
> [ / \
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>


Attachment