Rudder

This task isn't assigned to anyone.
I'm not sure that we will be able to fix it in time for 2.4.0~alpha7. It requires to have a specification about which postgresql configuration we wants to use by default, at least.

The community, and some of our production servers, have warning messages like :

2012-09-13 17:27:43 CEST LOG:  checkpoints are occurring too frequently (3 seconds apart)
2012-09-13 17:27:43 CEST HINT:  Consider increasing the configuration parameter "checkpoint_segments".

3 seconds is a too short delay, and cause overhead in large operations (like archiving) (http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html)
The default value for checkpoint_segments is 3; it should probably be raised to 6 for a start, to check that there are indeed improvement

checkpoint_segments=6

Similarly, this depend on the shared_buffer value, which has a defaut of 32 MB. We should really increase this value

shared_buffers = 64MB

The maintenance_work_mem determine the available memory for indexes modifications/table modification, which is typically done during archiving report. It default to 16 MB, which is clearly not enough for this use case

maintenance_work_mem=48MB

These value are conservative, to prevent using to much memory from Postgresql

As we discussed together Jon and me, this configuration should be set and check by CFEngine and not set by packaging.

And to answer my question:

By default, checkpoint_segments and maintenance_work_mem are commented but shared_buffers is not. shared_buffers will not be updated with the above logic.

What should we do with it ?

It would be more logical to force the set of these attributes if comments are not present and the user will be able to set its own values with adding comments.

Oh ! I've just notice that we don't have autovacuum enabled by default !
I would allow for collecting deleted lines (not reclaiming their space on disk, but leaving blanks for new entries, preventing the database to grow too much ( http://www.postgresql.org/docs/8.2/static/sql-vacuum.html ) and that would be important linked to #2974

we would probably need these parameters (based on what I read here : http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html http://www.postgresql.org/docs/8.2/static/runtime-config-autovacuum.html )

track_counts = on

autovacuum = on
autovacuum_naptime = 14400 # every 4 hours

Nicolas CHARLES wrote:

Oh ! I've just notice that we don't have autovacuum enabled by default !
I would allow for collecting deleted lines (not reclaiming their space on disk, but leaving blanks for new entries, preventing the database to grow too much ( http://www.postgresql.org/docs/8.2/static/sql-vacuum.html ) and that would be important linked to #2974

we would probably need these parameters (based on what I read here : http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html http://www.postgresql.org/docs/8.2/static/runtime-config-autovacuum.html )

> track_counts = on
> 
> autovacuum = on
> autovacuum_naptime = 14400 # every 4 hours
> 

I guess everything else could remain by defaultn but i'm not sure if they would fit (the only deletion we do is when we are archiving)

Hum, actually, there is an autovacuum launched process, even on SLES
Archiving reports lines don't trigger the vacuum afterwards, probably the default values are not as good as expected

I guess we would need to set the parameter

autovacuum_vacuum_scale_factor=0.1

for it will cause the vacuum to be triggered when 10% of the content has been deleted rather than 20% (default value)

Nicolas PERRON wrote:

As we discussed together Jon and me, this configuration should be set and check by CFEngine and not set by packaging.

And to answer my question:

By default, checkpoint_segments and maintenance_work_mem are commented but shared_buffers is not. shared_buffers will not be updated with the above logic.

What should we do with it ?

It would be more logical to force the set of these attributes if comments are not present and the user will be able to set its own values with adding comments.

To be more precise, we should add the magical comment "This comment permit Rudder to modify this value" if the attribute is commented or using default values

In the packaging, we should add this magical comment and with the system Techniques force the set of these attributes