From 1f7aa643b6302fe090da828cf3ba17eed9f2f917 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Mon, 31 Jan 2005 20:40:46 +0000 Subject: Reorganize FAQ entry on performance. --- doc/src/FAQ/FAQ.html | 98 ++++++++++++++++++++++++++++++---------------------- 1 file changed, 56 insertions(+), 42 deletions(-) (limited to 'doc/src') diff --git a/doc/src/FAQ/FAQ.html b/doc/src/FAQ/FAQ.html index fadb993467d..8ea5f32443b 100644 --- a/doc/src/FAQ/FAQ.html +++ b/doc/src/FAQ/FAQ.html @@ -10,7 +10,7 @@ alink="#0000ff">

Frequently Asked Questions (FAQ) for PostgreSQL

-

Last updated: Sun Jan 30 21:44:35 EST 2005

+

Last updated: Mon Jan 31 15:40:24 EST 2005

Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us) @@ -428,8 +428,8 @@ RHDB Admin (http://sources.redhat.com/rhd b/ ), TORA (http://www.globecom.net/tora/, partly commercial), and Rekall (http://www.globecom.net/tora/, + partly commercial), and Rekall ( http://www.rekallrevealed.org/). There is also PhpPgAdmin ( @@ -457,48 +457,64 @@

By default, PostgreSQL only allows connections from the local machine using Unix domain sockets or TCP/IP connections. Other machines will not be able to connect unless you modify - listen_addresses in the postgresql.conf and enable - host-based authentication by modifying the file - $PGDATA/pg_hba.conf accordingly.

+ listen_addresses in the postgresql.conf file, enable + host-based authentication by modifying the + $PGDATA/pg_hba.conf file, and restart the server.

3.3) How do I tune the database engine for better performance?

-

Certainly, indexes can speed up queries. The - EXPLAIN ANALYZE command allows you to see how - PostgreSQL is interpreting your query, and which indexes are - being used.

- -

If you are doing many INSERTs, consider doing - them in a large batch using the COPY command. This - is much faster than individual INSERTS. Second, - statements not in a BEGIN WORK/COMMIT transaction - block are considered to be in their own transaction. Consider - performing several statements in a single transaction block. This - reduces the transaction overhead. Also, consider dropping and - recreating indexes when making large data changes.

- -

There are several tuning options in the There are three major areas for potential performance + improvement:

+ +
+
Query Changes
+ +
This involves modifying queries to obtain better + performance: +
    +
  • Creation of indexes, including expression and partial + indexes
    • +
  • Use of COPY instead of multiple INSERTs
    • +
  • Grouping of multiple statements into a single transaction to + reduce commit overhead
    • +
  • Use of CLUSTER when retrieving many rows from an + index
    • +
  • Use of LIMIT for returning a subset of a query's + output
    • +
  • Use of Prepared queries
    • +
  • Use of ANALYZE to maintain accurate optimizer + statistics
    • +
  • Regular use of VACUUM or pg_autovacuum +
  • Dropping of indexes during large data changes
    • +

+
+
+ +
Server Configuration
+ +
A number of postgresql.conf settings affect performance. + For more details, see - Administration Guide/Server Run-time Environment/Run-time Configuration. - You can disable fsync() by using fsync option. This will - prevent fsync()s from flushing to disk after every - transaction.

- -

You can use the shared_buffers option to - increase the number of shared memory buffers used by the backend - processes. If you make this parameter too high, the - postmaster may not start because you have exceeded your - kernel's limit on shared memory space. Each buffer is 8K and the - default is 1000 buffers.

- -

You can also use the sort_mem (from PostgreSQL 8.0: work_mem) - options to increase the maximum amount of memory used by the backend - processes for each temporary sort. The default is 1024 (i.e. 1MB).

- -

You can also use the CLUSTER command to group - data in tables to match an index. See the CLUSTER - manual page for more details.

+ Administration Guide/Server Run-time Environment/Run-time + Configuration for a full listing, and for commentary see + http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html + and + http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html. +
+
+
+ +
Hardware Selection
+ +
The effect of hardware on performance is detailed in + http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html. +
+
+
+

3.4) What debugging features are available?

@@ -1196,5 +1212,3 @@ BYTEA bytea variable-length byte array (null-byte safe) compiler compute the dependencies automatically.

- - -- cgit v1.2.3