Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1f7aa64

Browse files
committed
Reorganize FAQ entry on performance.
1 parent7069a88 commit1f7aa64

File tree

2 files changed

+88
-74
lines changed

2 files changed

+88
-74
lines changed

‎doc/FAQ‎

Lines changed: 32 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated:Sun Jan30 21:44:35 EST 2005
4+
Last updated:Mon Jan31 15:40:24 EST 2005
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -346,41 +346,41 @@
346346
By default, PostgreSQL only allows connections from the local machine
347347
using Unix domain sockets or TCP/IP connections. Other machines will
348348
not be able to connect unless you modify listen_addresses in the
349-
postgresql.confand enable host-based authentication by modifying the
350-
file $PGDATA/pg_hba.confaccordingly.
349+
postgresql.conffile, enable host-based authentication by modifying
350+
the $PGDATA/pg_hba.conffile, and restart the server.
351351

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

354-
Certainly, indexes can speed up queries. The EXPLAIN ANALYZE command
355-
allows you to see how PostgreSQL is interpreting your query, and which
356-
indexes are being used.
357-
358-
If you are doing many INSERTs, consider doing them in a large batch
359-
using the COPY command. This is much faster than individual INSERTS.
360-
Second, statements not in a BEGIN WORK/COMMIT transaction block are
361-
considered to be in their own transaction. Consider performing several
362-
statements in a single transaction block. This reduces the transaction
363-
overhead. Also, consider dropping and recreating indexes when making
364-
large data changes.
365-
366-
There are several tuning options in the Administration Guide/Server
367-
Run-time Environment/Run-time Configuration. You can disable fsync()
368-
by using fsync option. This will prevent fsync()s from flushing to
369-
disk after every transaction.
370-
371-
You can use the shared_buffers option to increase the number of shared
372-
memory buffers used by the backend processes. If you make this
373-
parameter too high, the postmaster may not start because you have
374-
exceeded your kernel's limit on shared memory space. Each buffer is 8K
375-
and the default is 1000 buffers.
376-
377-
You can also use the sort_mem (from PostgreSQL 8.0: work_mem) options
378-
to increase the maximum amount of memory used by the backend processes
379-
for each temporary sort. The default is 1024 (i.e. 1MB).
380-
381-
You can also use the CLUSTER command to group data in tables to match
382-
an index. See the CLUSTER manual page for more details.
354+
There are three major areas for potential performance improvement:
383355

356+
Query Changes
357+
This involves modifying queries to obtain better performance:
358+
359+
+ Creation of indexes, including expression and partial indexes
360+
+ Use of COPY instead of multiple INSERTs
361+
+ Grouping of multiple statements into a single transaction to
362+
reduce commit overhead
363+
+ Use of CLUSTER when retrieving many rows from an index
364+
+ Use of LIMIT for returning a subset of a query's output
365+
+ Use of Prepared queries
366+
+ Use of ANALYZE to maintain accurate optimizer statistics
367+
+ Regular use of VACUUM or pg_autovacuum
368+
+ Dropping of indexes during large data changes
369+
370+
Server Configuration
371+
A number of postgresql.conf settings affect performance. For
372+
more details, see Administration Guide/Server Run-time
373+
Environment/Run-time Configuration for a full listing, and for
374+
commentary see
375+
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_co
376+
nf_e.html and
377+
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
378+
379+
Hardware Selection
380+
The effect of hardware on performance is detailed in
381+
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde
382+
x.html.
383+
384384
3.4) What debugging features are available?
385385

386386
PostgreSQL has several features that report status information that

‎doc/src/FAQ/FAQ.html‎

Lines changed: 56 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
alink="#0000ff">
1111
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1212

13-
<P>Last updated:Sun Jan30 21:44:35 EST 2005</P>
13+
<P>Last updated:Mon Jan31 15:40:24 EST 2005</P>
1414

1515
<P>Current maintainer: Bruce Momjian (<Ahref=
1616
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)
@@ -428,8 +428,8 @@ <H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
428428
RHDB Admin (<a
429429
href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhd
430430
b/</a>), TORA (<a
431-
href="http://www.globecom.net/tora/">http://www.globecom.net/tora/</
432-
a>,partly commercial), and Rekall (<a
431+
href="http://www.globecom.net/tora/">http://www.globecom.net/tora/</a>,
432+
partly commercial), and Rekall (<a
433433
href="http://www.rekallrevealed.org/">
434434
http://www.rekallrevealed.org/</a>). There is also PhpPgAdmin (<a
435435
href="http://phppgadmin.sourceforge.net/">
@@ -457,48 +457,64 @@ <H4><A name="3.2">3.2</A>) How do I control connections from other
457457
<P>By default, PostgreSQL only allows connections from the local
458458
machine using Unix domain sockets or TCP/IP connections. Other
459459
machines will not be able to connect unless you modify
460-
listen_addresses in the postgresql.conf<B>and</B> enable
461-
host-based authentication by modifying the file
462-
<I>$PGDATA/pg_hba.conf</I>accordingly.</P>
460+
<I>listen_addresses</I> in the<I>postgresql.conf</I> file, enable
461+
host-based authentication by modifying the
462+
<I>$PGDATA/pg_hba.conf</I>file, and restart the server.</P>
463463

464464
<H4><Aname="3.3">3.3</A>) How do I tune the database engine for
465465
better performance?</H4>
466466

467-
<P>Certainly, indexes can speed up queries. The
468-
<SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how
469-
PostgreSQL is interpreting your query, and which indexes are
470-
being used.</P>
471-
472-
<P>If you are doing many<SMALL>INSERTs</SMALL>, consider doing
473-
them in a large batch using the<SMALL>COPY</SMALL> command. This
474-
is much faster than individual<SMALL>INSERTS</SMALL>. Second,
475-
statements not in a<SMALL>BEGIN WORK/COMMIT</SMALL> transaction
476-
block are considered to be in their own transaction. Consider
477-
performing several statements in a single transaction block. This
478-
reduces the transaction overhead. Also, consider dropping and
479-
recreating indexes when making large data changes.</P>
480-
481-
<P>There are several tuning options in the<ahref=
467+
<P>There are three major areas for potential performance
468+
improvement:</P>
469+
470+
<DL>
471+
<DT><B>Query Changes</B></DT>
472+
473+
<DD>This involves modifying queries to obtain better
474+
performance:
475+
<ul>
476+
<li>Creation of indexes, including expression and partial
477+
indexes</li>
478+
<li>Use of COPY instead of multiple<SMALL>INSERT</SMALL>s</li>
479+
<li>Grouping of multiple statements into a single transaction to
480+
reduce commit overhead</li>
481+
<li>Use of<SMALL>CLUSTER</SMALL> when retrieving many rows from an
482+
index</li>
483+
<li>Use of<SMALL>LIMIT</SMALL> for returning a subset of a query's
484+
output</li>
485+
<li>Use of Prepared queries</li>
486+
<li>Use of<SMALL>ANALYZE</SMALL> to maintain accurate optimizer
487+
statistics</li>
488+
<li>Regular use of<SMALL>VACUUM</SMALL> or<I>pg_autovacuum</I>
489+
<li>Dropping of indexes during large data changes</li>
490+
</ul><BR>
491+
<BR>
492+
</DD>
493+
494+
<DT><B>Server Configuration</B></DT>
495+
496+
<DD>A number of<I>postgresql.conf</I> settings affect performance.
497+
For more details, see<ahref=
482498
"http://www.postgresql.org/docs/current/static/runtime.html">
483-
Administration Guide/Server Run-time Environment/Run-time Configuration</a>.
484-
You can disable<I>fsync()</I> by using<i>fsync</I> option. This will
485-
prevent<I>fsync()</I>s from flushing to disk after every
486-
transaction.</P>
487-
488-
<P>You can use the<I>shared_buffers</I> option to
489-
increase the number of shared memory buffers used by the backend
490-
processes. If you make this parameter too high, the
491-
<I>postmaster</I> may not start because you have exceeded your
492-
kernel's limit on shared memory space. Each buffer is 8K and the
493-
default is 1000 buffers.</P>
494-
495-
<P>You can also use the<I>sort_mem</I> (from PostgreSQL 8.0:<I>work_mem</I>)
496-
options to increase the maximum amount of memory used by the backend
497-
processes for each temporary sort. The default is 1024 (i.e. 1MB).</P>
498-
499-
<P>You can also use the<SMALL>CLUSTER</SMALL> command to group
500-
data in tables to match an index. See the<SMALL>CLUSTER</SMALL>
501-
manual page for more details.</P>
499+
Administration Guide/Server Run-time Environment/Run-time
500+
Configuration</a> for a full listing, and for commentary see<a
501+
href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html">
502+
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a>
503+
and<ahref="http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html">
504+
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html</a>.
505+
<BR>
506+
<BR>
507+
</DD>
508+
509+
<DT><B>Hardware Selection</B></DT>
510+
511+
<DD>The effect of hardware on performance is detailed in<a
512+
href="http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html">
513+
http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html</a>.
514+
<BR>
515+
<BR>
516+
</DD>
517+
</DL>
502518

503519
<H4><Aname="3.4">3.4</A>) What debugging features are
504520
available?</H4>
@@ -1196,5 +1212,3 @@ <H4><A name="5.4">5.4</A>) I have changed a source file. Why does
11961212
compiler compute the dependencies automatically.</P>
11971213
</BODY>
11981214
</HTML>
1199-
1200-

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp