Bienvenue sur PostGIS.fr

Bienvenue sur PostGIS.fr , le site de la communauté des utilisateurs francophones de PostGIS.

PostGIS ajoute le support d'objets géographique à la base de données PostgreSQL. En effet, PostGIS "spatialise" le serverur PostgreSQL, ce qui permet de l'utiliser comme une base de données SIG.

Maintenu à jour, en fonction de nos disponibilités et des diverses sorties des outils que nous testons, nous vous proposons l'ensemble de nos travaux publiés en langue française.

source: trunk/workshop-foss4g/tuning.rst @ 1

Revision 1, 6.9 KB checked in by djay, 13 years ago (diff)

Initial import of the svn tree

RevLine 
[1]1.. _tuning:
2
3Section 21: Tuning PostgreSQL for Spatial
4=========================================
5
6PostgreSQL is a very versatile database system, capable of running efficiently in very low-resource environments and environments shared with a variety of other applications.  In order to ensure it will run properly for many different environments, the default configuration is very conservative and not terribly appropriate for a high-performance production database.  Add the fact that geospatial databases have different usage patterns, and the data tend to consist of fewer, much larger records than non-geospatial databases, and you can see that the default configuration will not be totally appropriate for our purposes. 
7
8All of these configuration parameters can edited in the database configuration file, :file:`C:\\Documents and Settings\\%USER\\.opengeo\\pgdata\\%USER`.  This is a regular text file and can be edited using Notepad or any other text editor.  The changes will not take effect until the server is restarted.
9
10.. image:: ./tuning/conf01.png
11
12An easier way of editing this configuration is by using the built-in "Backend Configuration Editor".  In pgAdmin, go to *File > Open postgresql.conf...*.  It will ask for the location of the file, and navigate to :file:`C:\\Documents and Settings\\%USER\\.opengeo\\pgdata\\%USER`.
13
14.. image:: ./tuning/conf02.png
15
16.. image:: ./tuning/conf03.png
17
18This section describes some of the configuration parameters that should be adjusted for a production-ready geospatial database.  For each section, find the appropriate item in the list, double-click on the line to edit the configuration.  Change the *Value* to the recommended value as described, make sure the item is *Enabled*, the click **OK**.
19
20.. note:: These values are recommendations only; each environment will differ and testing is required to determine the optimal configuration.  But this section should get you off to a good start.
21
22shared_buffers
23--------------
24
25Sets the amount of memory the database server uses for shared memory buffers.  These are shared amongst the back-end processes, as the name suggests.  The default values are typically woefully inadequate for production databases.
26
27  *Default value*: typically 32MB
28
29  *Recommended value*: 75% of database memory (500MB)
30
31.. image:: ./tuning/conf04.png
32
33work_mem
34--------
35
36Defines the amount of memory that internal sorting operations and hash tables can consume before the database switches to on-disk files.  This value defines the available memory for each operation; complex queries may have several sort or hash operations running in parallel, and each connected session may be executing a query.
37
38As such you must consider how many connections and the complexity of expected queries before increasing this value.  The benefit to increasing is that the processing of more of these operations, including ORDER BY, and DISTINCT clauses, merge and hash joins, hash-based aggregation and hash-based processing of subqueries, can be accomplished without incurring disk writes.
39
40  *Default value*: 1MB
41
42  *Recommended value*: 16MB
43
44.. image:: ./tuning/conf05.png
45
46maintenance_work_mem
47--------------------
48
49Defines the amount of memory used for maintenance operations, including vacuuming, index and foreign key creation.  As these operations are not terribly common, the default value may be acceptable.  This parameter can alternately be increased for a single session before the execution of a number of :command:`CREATE INDEX` or :command:`VACUUM` calls as shown below.
50
51  .. code-block:: sql
52
53    SET maintenance_work_mem TO '128MB';
54    VACUUM ANALYZE;
55    SET maintenance_work_mem TO '16MB';
56
57  *Default value*: 16MB
58
59  *Recommended value*: 128MB
60
61.. image:: ./tuning/conf06.png
62
63wal_buffers
64-----------
65
66Sets the amount of memory used for write-ahead log (WAL) data.  Write-ahead logs provide a high-performance mechanism for insuring data-integrity.  During each change command, the effects of the changes are written first to the WAL files and flushed to disk.  Only once the WAL files have been flushed will the changes be written to the data files themselves.  This allows the data files to be written to disk in an optimal and asynchronous manner while ensuring that, in the event of a crash, all data changes can be recovered from the WAL. 
67
68The size of this buffer only needs to be large enough to hold WAL data for a single typical transaction.  While the default value is often sufficient for most data, geospatial data tends to be much larger.  Therefore, it is recommended to increase the size of this parameter.
69
70  *Default value*: 64kB
71
72  *Recommended value*: 1MB
73
74.. image:: ./tuning/conf07.png
75
76checkpoint_segments
77-------------------
78
79This value sets the maximum number of log file segments (typically 16MB) that can be filled between automatic WAL checkpoints.  A WAL checkpoint is a point in the sequence of WAL transactions at which it is guaranteed that the data files have been updated with all information before the checkpoint.  At this time all dirty data pages are flushed to disk and a checkpoint record is written to the log file.  This allows the crash recovery process to find the latest checkpoint record and apply all following log segments to complete the data recovery.
80
81Because the checkpoint process requires the flushing of all dirty data pages to disk, it creates a significant I/O load.  The same argument from above applies; geospatial data is large enough to unbalance non-geospatial optimizations.  Increasing this value will prevent excessive checkpoints, though it may cause the server to restart more slowly in the event of a crash.
82
83  *Default value*: 3
84
85  *Recommended value*: 6
86
87.. image:: ./tuning/conf08.png
88
89random_page_cost
90----------------
91
92This is a unit-less value that represents the cost of a random page access from disk.  This value is relative to a number of other cost parameters including sequential page access, and cpu operation costs.  While there is no magic bullet for this value, the default is generally conservative.  This value can be set on a per-session basis using the ``SET random_page_cost TO 2.0`` command.
93
94  *Default value*: 4.0
95
96  *Recommended value*: 2.0
97
98.. image:: ./tuning/conf09.png
99
100seq_page_cost
101-------------
102
103This is the parameter that controls the cost of a sequential page access.  This value does not generally require adjustment but the difference between this value and ``random_page_cost`` greatly affects the choices made by the query planner.  This value can also be set on a per-session basis.
104
105  *Default value*: 1.0
106
107  *Recommended value*: 1.0
108
109.. image:: ./tuning/conf10.png
110
111Reload configuration
112--------------------
113
114After these changes are made, save changes and reload the configuration. 
115
116 * This is done by right-clicking on the server (``PostgreSQL 8.4 on localhost:54321``) in pgAdmin, selecting to *Disconnect*.
117 * Clicking *Shutdown* in the OpenGeo Dashboard, then clicking *Start*.
118 * Finally reconnecting to the server in pgAdmin (right-click on the server and select *Connect*).
119 
120 
121 
Note: See TracBrowser for help on using the repository browser.