[postgis-tickets] r15139 - Bring Tuning section up to date, by incorporating some of Mike Toews changes
Regina Obe
lr at pcorp.us
Sun Sep 25 00:32:12 PDT 2016
Author: robe
Date: 2016-09-25 00:32:12 -0700 (Sun, 25 Sep 2016)
New Revision: 15139
Modified:
trunk/NEWS
trunk/doc/performance_tips.xml
Log:
Bring Tuning section up to date, by incorporating some of Mike Toews changes
Add mention of configs to enable parallel queries
Get rid of broken link to Kevin Neufeld's old press and replace with PostgreSQL wiki
Closes #3452
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2016-09-25 06:10:51 UTC (rev 15138)
+++ trunk/NEWS 2016-09-25 07:32:12 UTC (rev 15139)
@@ -54,6 +54,8 @@
- #3613, Segmentize geography using equal length segments
(Hugo Mercier of Oslandia)
+ * Bug Fixes
+ - #2841, ST_MinimumBoundingCircle not covering original
* Performance Enhancements *
- #75, Enhancement to PIP short circuit (Dan Baston)
@@ -61,6 +63,7 @@
(Dan Baston)
- #3400, Minor optimization of PIP routines (Dan Baston)
- Make adding a line to topology interruptible (Sandro Santilli)
+ - Documentation updates from Mike Toews
PostGIS 2.3.0beta1
2016/09/06
Modified: trunk/doc/performance_tips.xml
===================================================================
--- trunk/doc/performance_tips.xml 2016-09-25 06:10:51 UTC (rev 15138)
+++ trunk/doc/performance_tips.xml 2016-09-25 07:32:12 UTC (rev 15139)
@@ -142,18 +142,22 @@
<sect1 id="database_tuning_configuration">
<title>Tuning your configuration</title>
- <para>These tips are taken from Kevin Neufeld's presentation "Tips for the
- PostGIS Power User" at the FOSS4G 2007 conference. Depending on your
- use of PostGIS (for example, static data and complex analysis vs frequently
- updated data and lots of users) these changes can provide significant
- speedups to your queries.</para>
+ <para>Tuning for PostGIS is much like tuning for any PostgreSQL workload. The only additional note to keep in mind is that
+ geometries and rasters are heavy so memory related optimizations generally have more of an impact on PostGIS than other types of PostgreSQL queries.</para>
- <para>For a more tips (and better formatting), the original presentation
- is at
- <ulink url="http://2007.foss4g.org/presentations/view.php?abstract_id=117">
- http://2007.foss4g.org/presentations/view.php?abstract_id=117</ulink>.
- </para>
+ <para>For general details about optimizing PostgreSQL, refer to <ulink url="https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server">Tuning your PostgreSQL Server</ulink>.</para>
+
+ <para>For PostgreSQL 9.4+ all these can be set at the server level without touching postgresql.conf or postgresql.auto.conf
+ by using the <code>ALTER SYSTEM..</code> command.</para>
+ <programlisting>ALTER SYSTEM SET work_mem = '256MB';
+-- this will force, non-startup configs to take effect for new connections
+SELECT pg_reload_conf();
+-- show current setting value
+-- use SHOW ALL to see all settings
+SHOW work_mem;</programlisting>
+ <para>In addition to these settings, PostGIS also has some custom settings which you can find listed in <xref linkend="PostGIS_GUC" />.</para>
+
<sect2>
<title>Startup</title>
@@ -162,62 +166,59 @@
</para>
<para>
- <ulink url="http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS">checkpoint_segments</ulink>
+ <ulink url="http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION">constraint_exclusion</ulink>
</para>
<itemizedlist>
<listitem>
<para>
- Maximum number of log file segments between automatic WAL checkpoints
- (each segment is normally 16MB); default is 3
+ Default: partition
</para>
</listitem>
<listitem>
<para>
- Set to at least 10 or 30 for databases with heavy write activity, or
- more for large database loads. Another article on the topic worth reading <ulink url="http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm">Greg Smith: Checkpoint and Background writer</ulink>
+ This is generally used for table partitioning. The default for this is set to "partition" which is ideal for PostgreSQL 8.4 and above since
+ it will force the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy
+ and not pay the planner penalty otherwise.
</para>
</listitem>
- <listitem>
- <para>
- Possibly store the xlog on a separate disk device
- </para>
- </listitem>
</itemizedlist>
<para>
- <ulink url="http://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION">constraint_exclusion</ulink>
+ <ulink url="http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</ulink>
</para>
<itemizedlist>
<listitem>
<para>
- Default: off (prior to PostgreSQL 8.4 and for PostgreSQL 8.4+ is set to partition)
+ Default: ~32MB
</para>
</listitem>
<listitem>
<para>
- This is generally used for table partitioning. If you are running PostgreSQL versions below 8.4, set to "on" to ensure the query planner will optimize as desired.
- As of PostgreSQL 8.4, the default for this is set to "partition" which is ideal for PostgreSQL 8.4 and above since
- it will force the planner to only analyze tables for constraint consideration if they are in an inherited hierarchy
- and not pay the planner penalty otherwise.
+ Set to about 1/3 to 3/4 of available RAM
</para>
</listitem>
</itemizedlist>
-
- <para>
- <ulink url="http://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-SHARED-BUFFERS">shared_buffers</ulink>
+
+
+ <para>
+ <ulink url="https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAX-WORKER-PROCESSES">max_worker_processes</ulink>
+ This setting is only available for PostgreSQL 9.4+. For PostgreSQL 9.6+ this setting has additional importance in that it controls the
+ max number of processes you can have for parallel queries.
</para>
<itemizedlist>
<listitem>
<para>
- Default: ~32MB
+ Default: 8
</para>
</listitem>
<listitem>
<para>
- Set to about 1/3 to 3/4 of available RAM
+ Sets the maximum number of background processes that
+ the system can support. This parameter can only be set at
+ server start.
</para>
</listitem>
</itemizedlist>
@@ -233,7 +234,7 @@
<itemizedlist>
<listitem>
<para>
- Default: 1MB
+ Default: 1-4MB
</para>
</listitem>
<listitem>
@@ -250,7 +251,7 @@
<para>
If you have lots of RAM and few developers:
<programlisting>
- SET work_mem TO 1200000;
+ SET work_mem TO '256MB';;
</programlisting>
</para>
</listitem>
@@ -263,7 +264,7 @@
<itemizedlist>
<listitem>
<para>
- Default: 16MB
+ Default: 16-64MB
</para>
</listitem>
<listitem>
@@ -273,15 +274,44 @@
</listitem>
<listitem>
<para>
- Recommend 32MB to 256MB on production servers w/lots of RAM, but depends
+ Recommend 32MB to 1GB on production servers w/lots of RAM, but depends
on the # of concurrent users. If you have lots of RAM and few developers:
<programlisting>
- SET maintainence_work_mem TO 1200000;
+ SET maintenance_work_mem TO '1GB';
</programlisting>
</para>
</listitem>
</itemizedlist>
+
+ <para>
+ <ulink url="https://www.postgresql.org/docs/current/static/runtime-config-resource.html#GUC-MAX-PARALLEL-WORKERS-PER-GATHER">max_parallel_workers_per_gather</ulink>
+ This setting is only available for PostgreSQL 9.6+ and will only affect PostGIS 2.3+, since only PostGIS 2.3+ supports parallel queries.
+ If set to higher than 0, then some queries such as those involving relation functions like <code>ST_Intersects</code> can use multiple processes and can run
+ more than twice as fast when doing so. If you have a lot of processors to spare, you should change the value of this to as many processors as you have.
+ Also make sure to bump up <code>max_worker_processes</code> to at least as high as this number.
+ </para>
+ <itemizedlist>
+ <listitem>
+ <para>
+ Default: 0
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Sets the maximum number of workers that can be started
+ by a single <varname>Gather</varname> node.
+ Parallel workers are taken from the pool of processes
+ established by <varname>max_worker_processes</varname>.
+ Note that the requested number of workers may not
+ actually be available at run time. If this occurs, the
+ plan will run with fewer workers than expected, which may
+ be inefficient. Setting this value to 0, which is the
+ default, disables parallel query execution.
+ </para>
+ </listitem>
+ </itemizedlist>
+
</sect2>
</sect1>
More information about the postgis-tickets
mailing list