Re: sloooow query

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(at)postgresql(dot)org
> > [mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Justin Clift
> > Sent: Monday, October 07, 2002 2:30 PM
> > To: josh(at)agliodbs(dot)com
> > Cc: marie(dot)tuite(at)edisonaffiliates(dot)com; pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)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