2

Amazon announced they support a RAM disk for the stats_temp_directory in PostgreSQL on RDS.

This sounds like a good idea so I want to enable it. Now I'm wondering how to determine the optimal size for the RAM disk. Is there a way to show the current size of the directory on disk? The file system of PostgreSQL on RDS is not accessible, isn't it? Also, what happens if the RAM disk is configured too small?

1 Answer 1

3

The pg_stat_ramdisk_size controls the location and size of the stats_temp_directory on RAM. It's easy to determine the size on bare metal or out of the RDS by issuing a du -h over the directory specified on the variable. There is no way yet to access this folder size at RDS.

The used space on this folder, will depend strictly on the settings you have for track_* variables (See 27.2.1. Statistics Collection Configuration ). It is not necessarily a huge amount of space, although is very write intensive as it holds the counters per global and database basis (counters over database per table/index/functions). This also means it is more related to the number of databases and tables+indexes (as pgstat_write_statsfiles at postmaster/pgstat.c iterates, writing the PgStat_StatDBEntry) than the amount of data.

You don't want to have it smaller (as a crash will happen), so you may want to start with a value over 256MB.

Per configuration, it won't let you set pg_stat_ramdisk_size higher than 8 GB (accepts values from 1 - 8196 and the unit is MB).

PGSTAT_FILE_FORMAT_ID 1 byte
PgStat_StatTabEntry = 164 bytes
PgStat_StatFuncEntry = 28 bytes
closingChar = 'E'
describers is a char (T or F in this case)

As will be explained later, not all the tables, indexes, and functions are written on the db statsfile. A basic formula is SizeOfDBStatFile = PGSTAT_FILE_FORMAT_ID + describers + (tableCount * PgStat_StatTabEntry) + (funcCount * PgStat_StatFuncEntry) + closingChar.

A select count(*) * 164 "size in bytes" from pg_class where relkind IN ('r','i','S') will give you the estimate for the tables if all of them were flushed on the file. Also, you need to do the same within pg_proc, but instead the factor will be 28 bytes.

This file is one per database.

PgStat_StatDBEntry = 180 bytes
PgStat_GlobalStats = 92 bytes
PgStat_ArchiverStats = 114 bytes
describer is a char (D)

The global statfile is smaller and contains only the global stats and the counters across databases. Should be something close to PGSTAT_FILE_FORMAT_ID +describer + PgStat_GlobalStats + PgStat_ArchiverStats + (PgStat_StatDBEntry + describer) * numDatabases.

As you can see, the limitation imposed by AWS in regarding is way above the amount of data held on this directory in most of the databases that can run inside RDS expectations.

My blog post reference

1
  • I forgot to mention that not all the functions have an allocation on this file. Only users functions.
    – 3manuek
    Commented Sep 26, 2016 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.