High I/O disk from postgres stats collector process

[moylop260]

Currently, our runbot is consuming I/O disk from postgres stats collector process:

• 

The instances used for the big-image are semi non-durability type since that is not used for a production environment (just testing).

Then we could change the postgresql configuration (for all versions) in order to reduce the I/O disk.

Maybe, reducing autovacuum process too.

Offtopic other process consuming I/O disk is:

• 

I don't know if it is related with the postgres stats collector process

[luisg123v]

@moylop260 As you might be already aware, most of the optimization for reducing disk usage is already applied:

openerp_test=# SHOW fsync; SHOW synchronous_commit; SHOW checkpoint_timeout; SHOW full_page_writes; SHOW max_wal_size; SHOW autovacuum;
fsync | off
synchronous_commit | off
checkpoint_timeout | 45min
full_page_writes | off
max_wal_size | 1GB
autovacuum | on

The only parameter that seems promising is autovacuum | on, that I'm guessing might be the cause for the high I/O of the collector.

Another alternative is to set all tables as unlogged, which is not currently the case:

openerp_test=# SELECT relname FROM pg_class WHERE relpersistence = 'u';
(0 rows)

However, AFAIK, that is not a feature that could be set by default, so we'd need to modify Odoo's code so all tables are created this way. To do so, CREATE TABLE sentences would need to be replaced by CREATE UNLOGGED TABLE here where model's tables are created and here where relational field's tables are created.

What do you think? Which one of these solutions do you think should be applied?

[moylop260]

fsync off

Are these parameters available for all postgresql versions?

autovacuum off +1 @ruiztulio what docyou think?

CREATE UNLOGGED TABLE Maybe replacing the original one CREATE TABLE I dont know if it is possible

[luisg123v]

@moylop260 Yes, they're available for all versions. If you check any postgresql.conf file, they all have the following lines at the end:

fsync=off
full_page_writes=off
checkpoint_timeout=45min
synchronous_commit=off

Not sure where these ones are added, though. There are not present in a standard installation, but they don't seem to be set up here in this project. Maybe they come from the shippable image?

[ruiztulio]

@moylop260 Autovacumm was disabled here, perhaps we should check why it is not applied as expected.

@luisg123v Yesy, it can be set by default, check it here and also is in the postgres config file:

[luisg123v]

@ruiztulio Yes, autovacuum may be enabled/disabled by default. However, what we really wonder is if tables may be created as unlogged by default, or at least I do.

[ruiztulio]

@luisg123v The only way I know is during the create table or alter them after they are created. If it is done during the create this should be done only in test instances, because we need the WAL (I mention in case is done via odoo)

[moylop260]

FYI I just ran the following command: docker run -it --rm vauxoo/odoo-80-image-shippable-auto cat /etc/postgresql/9.6/main/postgresql.conf | grep "settings for extensions" -A40

I saw that we are using a custom configuration file:

# Add settings for extensions here

logging_collector=on
log_destination='stderr'
log_directory='pg_log'
log_filename='postgresql.log'
log_rotation_age=0
log_checkpoints=on
log_hostname=on
log_line_prefix='%t [%p]: [%l-1] db=%d,user=%u '
fsync=off
full_page_writes=off
checkpoint_timeout=45min
synchronous_commit=off

IMHO we should re-use the configuration file of @ruiztulio like as an include.

[moylop260]

Running docker run -it --rm vauxoo/odoo-80-image cat /etc/postgresql/9.6/main/postgresql.conf | grep "Add settings for extensions here" -A2 The output is: include = '/etc/postgresql-common/common-vauxoo.conf'

[luisg123v]

@moylop260 Yes, it should be applied in all versions. See for example for v11:

https://github.com/Vauxoo/docker-odoo-image/blob/a6df1a9d58fd344055d547352f6f23d773fb61d7/odoo110/scripts/build-image.sh#L118

and it's in all other images

[moylop260]

For record,

• After deploy this change.
• Tulio applied yesterday the following changes to runbot's server:
  • Run updates including kernel update
  • FS optimizations
  • Use docker driver overlay2 instead of btrfs
  • Exclude /docker folder from updatedb script
  • Clean journal from systemctl
  • Disable journal

Now,

• postgres stats collector process is not displayed anymore on iotop output.
• The load average is 7 average. (Before it was 20)

Thanks for the great works @ruiztulio and @luisg123v

[luisg123v]

Great! I'm glad to hear that @moylop260.