Use config_pgtune for postgresql cookbook

[vuntz]

For a node with 131840784kB of memory, this result in this change:

--- /var/chef/backup//var/lib/pgsql/data/postgresql.conf.chef-20170216124858    2017-02-13 10:23:39.626408993 +0000
+++ /var/lib/pgsql/data/postgresql.conf 2017-02-16 15:57:41.981259862 +0000
@@ -3,8 +3,12 @@
 # Please refer to the PostgreSQL documentation for details on
 # configuration settings.

+checkpoint_completion_target = '0.9'
+checkpoint_segments = 16
 datestyle = 'iso, mdy'
+default_statistics_target = 100
 default_text_search_config = 'pg_catalog.english'
+effective_cache_size = '88GB'
 lc_messages = 'en_US.UTF-8'
 lc_monetary = 'en_US.UTF-8'
 lc_numeric = 'en_US.UTF-8'
@@ -16,6 +20,9 @@
 log_rotation_size = 0
 log_truncate_on_rotation = off
 logging_collector = on
+maintenance_work_mem = '1GB'
 max_connections = 1000
 port = 5432
-shared_buffers = '32MB'
+shared_buffers = '8GB'
+wal_buffers = '-1'
+work_mem = '64MB'

This looks good to me. @mkoderer: can you check if this is sensible?

[vuntz]

Forgot to mention:

+effective_cache_size = '88GB'

That's of course the one that may be an issue when colocating with openstack services.

[vuntz]

And somehow I forgot to link to the patch: https://github.com/crowbar/crowbar-openstack/compare/stable/3.0...vuntz:pgtune-3.0?expand=1 (not submitted to this org -- I need to create the staging branch for crowbar-openstack first)

[vuntz]

@mkoderer mentioned in a mail the use of dynamic_shared_memory_type = posix, but my understanding is that this is already the default here.

[mkoderer]

@vuntz I think we should start testing this. Can you open-up a pull request? Related to sap custom wrapper (crowbar-sapcustom-soc6/blob/master/recipes/postgres.rb#L9)

[vuntz]

For the record, with the wrapper cookbook, the config change is:

effective_cache_size = '16GB'
work_mem = '64MB'
wal_buffers = '16MB'
checkpoint_segments = 5
maintenance_work_mem = '256MB'

So the pgtune recipe is more aggressive.

[mkoderer]

Needs testing

[vuntz]

@mkoderer since this is fixed in custom wrapper cookbook, I'd drop the critical tag here.