Differences between current version and predecessor to the previous major change of PostgreSQLNotes.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 9 | Last edited on Friday, June 23, 2006 3:55:41 am | by AristotlePagaltzis | |
| Older page: | version 8 | Last edited on Thursday, October 13, 2005 12:30:06 pm | by MattBrown | Revert |
@@ -48,4 +48,56 @@
Now you can go ''SELECT column_name FROM table_pkeys WHERE table_name='foo''' and get back a list of the columns in the table ''foo'' that are primary keys. No records will be returned if ''foo'' does not have a primary key defined.
I added the restrictions on the ''relowner'' and ''relkind'' fields so that queries such as ''SELECT * FROM table_pkeys'' are a bit cleaner. The restrictions aim to filter the list so that it only contains user created tables. I chose the values based on trial and error, they work for me, but your milage may vary. If you are having problems simply remove those to restrictions.
+
+!! Tuning the [WAL]
+
+Some configuration parameters for configuring [WAL] behaviour are available. They are all documented in the online [PostgreSQL documentation | http://www.postgresql.org/docs/]. A good overview of some of the specific configuration parameters is available [here | http://www.postgresql.org/docs/8.0/static/wal-configuration.html].
+
+Many people extol the virtue of placing the [WAL] on its own device, seperate from the main data files. Extensive PgBench testing leads to questioning that advice; the performance increase appears to be related to the increase in number of spindles more than the specific disk configuration. Compare the 4-disk RAID1+0 and dual RAID1 configurations. Those tests, however, were performed using a battery-backed write cache; it is entirely possible that without a write-cache the fsync(2) seek latency would significantly impact the main data file [I/O].
+
+Some additional configuration notes:
+
+<tt>[commit_delay | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-COMMIT-DELAY]</tt> %%% <tt>[commit_siblings | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-COMMIT-SIBLINGS]</tt>:
+
+ These settings trade throughput for <tt>COMMIT</tt> latency.
+ The circumstances under which increasing <tt>commit_delay</tt> has most notable effect are when:
+
+ * There is no battery-backed write cache
+ * There are a lot of small transactions
+ * The [WAL] device has a high fsync(2) latency
+ * There are a large number of concurrent connections
+
+ GuyThornley's favourite settings for these parameters are:''''
+
+ <verbatim>
+ commit_delay = 20000
+ commit_siblings = 3
+ </verbatim>
+
+<tt>[wal_buffers | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-WAL-BUFFERS]</tt>:
+
+ This setting controls the upper bound on the size of write(2) requests to [WAL] segment files. Modern operating systems and disks work very well with writes in the range of 128kiB to 512kiB, often reaching peak [I/O] bandwidth with write(2) requests in the range.
+
+ Before generating some new [WAL] activity, [PostgreSQL] writes out the [WAL] buffers if more than half are full. Given a default block size of 8 kiB, and the disk performance above, this suggests that <tt>wal_buffers = 128</tt> is a good starting point. The default setting of <tt>8</tt> is surely too small for most systems.
+
+ Doing <tt>COMMIT</tt> both writes out all the used [WAL] buffers and issues an expensive fsync(2). Thus small transactions come with a heavy [I/O] penalty, and the [WAL] buffers are wasted. Using <tt>commit_delay</tt> may help significantly in this situation.
+
+<tt>[checkpoint_segments | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-SEGMENTS]</tt> %%% <tt>[checkpoint_timeout | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-TIMEOUT]</tt> %%% <tt>[checkpoint_warning | http://www.postgresql.org/docs/8.0/static/runtime-config.html#GUC-CHECKPOINT-WARNING]</tt>:
+
+ Good settings for these parameters are particularly hard to determine. On one hand, you want to allow [PostgreSQL] and the operating system perform write-caching of the main data files; on the other hand, you want to constrain crash-recovery times to reasonable levels.
+
+ Current reccomendations are to have checkpoints occur no more frequently than 5 minute intervals. Thus the settings
+
+ <verbatim>
+ checkpoint_timeout = 600
+ checkpoint_warning = 300
+ </verbatim>
+
+ seem reasonable.
+
+ It is impossible to tune <tt>checkpoint_segments</tt> for worst-case [WAL] activity, such as a bulk load using <tt>COPY FROM STDIN</tt> [SQL] command. This might generate 30MiB/s or more of WAL activity. 30MiB/s for 5 minutes is 8.7GiB of WAL data! The best suggestion appears to be let the server run for a while, then study the timestamps on the WAL segment files in <tt>pg_xlog</tt> to calculate the [WAL] write rate.
+
+ Once the [WAL] write rate is deduced, the number of log segments required for 10 minutes of [WAL] activity can be calculated as <i>checkpoint_segments = (wal_write_rate * 600) / 16</i>
+
+ It is the author's experience that crash recovery from the [WAL] is a fairly slow operation. [I/O] rates in the region of 2MiB/s to 4MiB/s should be expected. The <tt>checkpoint_segments</tt> setting could be limited to the longest permissible recovery time. For example, to complete recovery within 5 minutes at 3.0MiB/sec, the maximum setting of <tt>checkpoint_segments</tt> is <i>(300 * 3) / 16 = 56</i>
