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

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)