Re: sloooow query - Mailing list pgsql-performance

Hi Marie,

Ok, not sure about the SQL side of things (got scared just *looking* at
that query), but if this is at least a mostly-dedicated database server
then you might want to bump up some of those buffer values.  They look
like defaults (except the max_connections and shared buffers).

Initial thought is making just sort_mem = 8192 or so as a minimum (it
could go a lot higher, but not sure of your memory configuration), as
see if that makes a difference.

Not sure the wal_files = 0 bit is good either.  Haven't seen that set to
0 before.

Might not assist with your present crisis, but am guessing PostgreSQL is
chewing a lot of CPU and being slow in general with the present
settings.

:-)

Regards and best wishes,

Justin Clift


"Marie G. Tuite" wrote:
>
> Here is a show all:
>
> Thanks,
>
> project-# ;
> NOTICE:  enable_seqscan is on
> NOTICE:  enable_indexscan is on
> NOTICE:  enable_tidscan is on
> NOTICE:  enable_sort is on
> NOTICE:  enable_nestloop is on
> NOTICE:  enable_mergejoin is on
> NOTICE:  enable_hashjoin is on
> NOTICE:  ksqo is off
> NOTICE:  geqo is on
> NOTICE:  tcpip_socket is on
> NOTICE:  ssl is off
> NOTICE:  fsync is on
> NOTICE:  silent_mode is off
> NOTICE:  log_connections is off
> NOTICE:  log_timestamp is off
> NOTICE:  log_pid is off
> NOTICE:  debug_print_query is off
> NOTICE:  debug_print_parse is off
> NOTICE:  debug_print_rewritten is off
> NOTICE:  debug_print_plan is off
> NOTICE:  debug_pretty_print is off
> NOTICE:  show_parser_stats is off
> NOTICE:  show_planner_stats is off
> NOTICE:  show_executor_stats is off
> NOTICE:  show_query_stats is off
> NOTICE:  stats_start_collector is on
> NOTICE:  stats_reset_on_server_start is on
> NOTICE:  stats_command_string is off
> NOTICE:  stats_row_level is off
> NOTICE:  stats_block_level is off
> NOTICE:  trace_notify is off
> NOTICE:  hostname_lookup is off
> NOTICE:  show_source_port is off
> NOTICE:  sql_inheritance is on
> NOTICE:  australian_timezones is off
> NOTICE:  fixbtree is on
> NOTICE:  password_encryption is off
> NOTICE:  transform_null_equals is off
> NOTICE:  geqo_threshold is 11
> NOTICE:  geqo_pool_size is 0
> NOTICE:  geqo_effort is 1
> NOTICE:  geqo_generations is 0
> NOTICE:  geqo_random_seed is -1
> NOTICE:  deadlock_timeout is 1000
> NOTICE:  syslog is 0
> NOTICE:  max_connections is 64
> NOTICE:  shared_buffers is 128
> NOTICE:  port is 5432
> NOTICE:  unix_socket_permissions is 511
> NOTICE:  sort_mem is 1024
> NOTICE:  vacuum_mem is 8192
> NOTICE:  max_files_per_process is 1000
> NOTICE:  debug_level is 0
> NOTICE:  max_expr_depth is 10000
> NOTICE:  max_fsm_relations is 100
> NOTICE:  max_fsm_pages is 10000
> NOTICE:  max_locks_per_transaction is 64
> NOTICE:  authentication_timeout is 60
> NOTICE:  pre_auth_delay is 0
> NOTICE:  checkpoint_segments is 3
> NOTICE:  checkpoint_timeout is 300
> NOTICE:  wal_buffers is 8
> NOTICE:  wal_files is 0
> NOTICE:  wal_debug is 0
> NOTICE:  commit_delay is 0
> NOTICE:  commit_siblings is 5
> NOTICE:  effective_cache_size is 1000
> NOTICE:  random_page_cost is 4
> NOTICE:  cpu_tuple_cost is 0.01
> NOTICE:  cpu_index_tuple_cost is 0.001
> NOTICE:  cpu_operator_cost is 0.0025
> NOTICE:  geqo_selection_bias is 2
> NOTICE:  default_transaction_isolation is read committed
> NOTICE:  dynamic_library_path is $libdir
> NOTICE:  krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab
> NOTICE:  syslog_facility is LOCAL0
> NOTICE:  syslog_ident is postgres
> NOTICE:  unix_socket_group is unset
> NOTICE:  unix_socket_directory is unset
> NOTICE:  virtual_host is unset
> NOTICE:  wal_sync_method is fdatasync
> NOTICE:  DateStyle is ISO with US (NonEuropean) conventions
> NOTICE:  Time zone is unset
> NOTICE:  TRANSACTION ISOLATION LEVEL is READ COMMITTED
> NOTICE:  Current client encoding is 'SQL_ASCII'
> NOTICE:  Current server encoding is 'SQL_ASCII'
> NOTICE:  Seed for random number generator is unavailable
> SHOW VARIABLE
> project=#
>
> > -----Original Message-----
> > From: pgsql-performance-owner@postgresql.org
> > [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Justin Clift
> > Sent: Monday, October 07, 2002 2:30 PM
> > To: josh@agliodbs.com
> > Cc: marie.tuite@edisonaffiliates.com; pgsql-performance@postgresql.org
> > Subject: Re: [pgsql-performance] sloooow query
> >
> >
> > Josh Berkus wrote:
> > >
> > > Marie,
> > >
> > > > I am experiencing slow db performance.  I have vacuumed,
> > analyzed, reindexed
> > > > using the force option and performance remains the same -
> > dog-slow :(  If I
> > > > drop and recreate the database, performance is normal, so
> > this suggests a
> > > > problem with the indexes?  I also took a look at the
> > postgresql.conf and all
> > > > appears fine.  There are many instances of the same database
> > running on
> > > > different servers and not all servers are experiencing the problem.
> > >
> > > Please post the following:
> > > 1) A copy of the relevant portions of your database schema.
> > > 2) The query that is running slowly.
> > > 3) The results of running EXPLAIN on that query.
> > > 4) Your PostgreSQL version and operating system
> > > 5) Any other relevant information about your databases, such as
> > the quantity
> > > of inserts and deletes on the relevant tables.
> >
> > 6) And the sort_mem, shared_buffers, vacuum_mem, wal_buffers, and
> > wal_files settings from your postgresql.conf file, if possible.
> >
> > :-)
> >
> > Regards and best wishes,
> >
> > Justin Clift
> >
> >
> > > --
> > > -Josh Berkus
> > >  Aglio Database Solutions
> > >  San Francisco
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> > --
> > "My grandfather once told me that there are two kinds of people: those
> > who work and those who take the credit. He told me to try to be in the
> > first group; there was less competition there."
> >    - Indira Gandhi
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi