Thread: BUG #12834: Avoiding Disk Control Writes for better Laptop / SSD compatibility. pgstats

The following bug has been logged on the website:

Bug reference:      12834
Logged by:          Bryce Nesbitt
Email address:      bryce2@obviously.com
PostgreSQL version: 9.4.1
Operating system:   Linux
Description:

This is an issue primarily for home computers and laptops which run
postgres, but not under heavy load.

Every minute postgresql runs the "stats" process which writes the following
files:

postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_0.tmp
postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.tmp
postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_12066.tmp
postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.stat
postgres(568): CW /var/lib/postgresql/9.4/main/base/12066/11868
unknown(568): CW /var/lib/postgresql/9.4/main/global/12062
unknown(568): CW /var/lib/postgresql/9.4/main/base/12066/11828
unknown(568): CW /var/lib/postgresql/8.4/main/base/12066/11833

This data was collected with "fatrace".  There's one set of writes per
installed version of postgres on the test system (8.1,9.1,9.3,9.4).

----

Would it be possible to have the stats process take no action and make no
writes to the disk, if there has been no database activity in the interval?

---
For laptops disk writes wake up the machine and/or harddrive.  For SSD's
it's a wear leveling issue.  With this write happening 24/7 on millions of
computers this is also an energy use optimization potential (reduce
electricity use from your keyboard!)

bryce2@obviously.com writes:
> Every minute postgresql runs the "stats" process which writes the following
> files:

> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_0.tmp
> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.tmp
> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_12066.tmp
> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.stat
> postgres(568): CW /var/lib/postgresql/9.4/main/base/12066/11868
> unknown(568): CW /var/lib/postgresql/9.4/main/global/12062
> unknown(568): CW /var/lib/postgresql/9.4/main/base/12066/11828
> unknown(568): CW /var/lib/postgresql/8.4/main/base/12066/11833

The stats collector only writes something when asked to.  In this
case what's asking for stats is probably the autovacuum launcher.
You could dial back the autovacuum naptime if you want fewer
autovacuum wakeups.

            regards, tom lane

On Thu, Mar 5, 2015 at 10:23 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> bryce2@obviously.com writes:
>> Every minute postgresql runs the "stats" process which writes the following
>> files:
>
>> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_0.tmp
>> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.tmp
>> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/db_12066.tmp
>> postgres(3814): CW /var/lib/postgresql/9.4/main/pg_stat_tmp/global.stat
>> postgres(568): CW /var/lib/postgresql/9.4/main/base/12066/11868
>> unknown(568): CW /var/lib/postgresql/9.4/main/global/12062
>> unknown(568): CW /var/lib/postgresql/9.4/main/base/12066/11828
>> unknown(568): CW /var/lib/postgresql/8.4/main/base/12066/11833
>
> The stats collector only writes something when asked to.  In this
> case what's asking for stats is probably the autovacuum launcher.
> You could dial back the autovacuum naptime if you want fewer
> autovacuum wakeups.

I'm aware that it's possible to configure postgres for fewer wakeups.
This report is more about the default configuration, and about
teaching autovaccum/stats to do nothing if the cluster has been idle.

Given the size of these files, the cumulative impact on a consumer SSD
is pretty high.  Chances are most people will leave naptime at the
default value in small setups.  In big setups this does not matter, as
enterprise disks are set up for this level of wear.

Is autovac writing anything of import during its periodic wakeup on an
idle cluster?

Bryce Nesbitt wrote:

> Is autovac writing anything of import during its periodic wakeup on an
> idle cluster?

Autovacuum is not writing anything.  What it is doing is asking whether
anyone has written anything -- and the stats collector responds by
writing these stat files.

The issue is that the timestamp-of-last-update bit, which is critical to
autovacuum, does not get written unless the whole file is.  I guess it
is possible to optimize that, so that the file is not touched other than
the timestamp if the database has been completely idle.  I have no idea
how complex this is, or whether this scenario is really worth
optimizing.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Mar 5, 2015 at 11:44 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> Bryce Nesbitt wrote:
> > Is autovac writing anything of import during its periodic wakeup on
> an idle cluster?
>
> Autovacuum is not writing anything....
> The issue is that the timestamp-of-last-update bit, which is critical to
> autovacuum, does not get written unless the whole file is.  I guess it
> is possible to optimize that, so that the file is not touched other than
> the timestamp if the database has been completely idle.


That all sounded really reasonable.    So I checked into turning off vac
for this particular laptop
and discovered:

/etc/postgresql/9.3/main/postgresql.conf
#autovacuum = on
#autovacuum_naptime = 1min

(At Ubuntu's default).  So autovacuum is *not the droid we're looking
for...*

On 3/5/2015 6:10 PM, Bryce Nesbitt wrote:
>
> /etc/postgresql/9.3/main/postgresql.conf
> #autovacuum = on
> #autovacuum_naptime = 1min
>
> (At Ubuntu's default).  So autovacuum is /not the droid we're looking
> for.../


the commented out lines in postgresql.conf are the defaults.   so
autovacuum is on.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Bryce Nesbitt wrote:

> That all sounded really reasonable.    So I checked into turning off vac
> for this particular laptop
> and discovered:
>
> /etc/postgresql/9.3/main/postgresql.conf
> #autovacuum = on
> #autovacuum_naptime = 1min
>
> (At Ubuntu's default).

You need to uncomment and set off.

> So autovacuum is *not the droid we're looking for...*

Use "SHOW autovacuum" in psql to avoid being confounded by
mind-controlling superpowers.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Fri, Mar 6, 2015 at 6:19 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

> Use "SHOW autovacuum" in psql to avoid being confounded by
> mind-controlling superpowers.
>

Indeed.  My mistake. Autovac *was* the droid I was looking for. On orders
of the Empire, that droid has been eliminated on twenty five laptops.
Millions more exist however, each chewing through the write cycles on SSD's
in a futile attempt to bring down the social order.

--
More seriously: I've been cataloguing what churns the SSD at idle.  The top
two are Google Chrome's constant rewriting of HTML5 Local Storage elements,
and PostgreSQL.  This is true both in terms of raw writes and blocks
written.  It's a significant fraction of the SSD's life (some 40% in the
first laptop analyzed above).

This will never show up in a PostgreSQL benchmark, and never cause a
problem for the PSQL team.  But it's eating through SSD's wherever default
mode Postgres is installed but not heavily used.

Bryce Nesbitt <bryce2@obviously.com> wrote:

> Autovac was the droid I was looking for. On orders of the Empire,
> that droid has been eliminated on twenty five laptops.

What, exactly, did you do to "eliminate" that?  If you do not have
a sufficiently aggressive vacuum regimen you will have far worse
problems somewhere down the road.  You can probably tune autovacuum
to minimize the problems you described.  You could also supplement
autovacuum with scheduled VACUUM commands (or possibly even replace
autovacuum with such commands if you are *very* careful).  Just
turning off autovacuum is roughly the equivalent of trying to keep
vehicles on the road for more time by skipping all oil changes and
other maintenance.  It will appear to work really well for some
amount of time.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Mar 6, 2015 at 10:33 AM, Kevin Grittner <kgrittn@ymail.com> wrote:

> Bryce Nesbitt <bryce2@obviously.com> wrote:
>
> > Autovac was the droid I was looking for. On orders of the Empire,
> > that droid has been eliminated on twenty five laptops.
>
> What, exactly, did you do to "eliminate" that?
>

Given the use case on these machines, turning off auto vacuum completely
was appropriate.
The 1 minute vacuum is just fine for an active cluster, but the write churn
on the SSD
was not worth it here.  A read only workload would be fine.

On 3/6/2015 10:46 AM, Bryce Nesbitt wrote:
> Given the use case on these machines, turning off auto vacuum
> completely was appropriate.
> The 1 minute vacuum is just fine for an active cluster, but the write
> churn on the SSD
> was not worth it here.  A read only workload would be fine.


eventually, you HAVE to vacuum for various reasons.  you might use a
cron job (or task scheduler) to setup a weekly job that does a vacuumdb -a).


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Fri, Mar 6, 2015 at 5:45 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 3/6/2015 10:46 AM, Bryce Nesbitt wrote:
>>
>> Given the use case on these machines, turning off auto vacuum completely
>> was appropriate.
>> The 1 minute vacuum is just fine for an active cluster, but the write
>> churn on the SSD
>> was not worth it here.  A read only workload would be fine.
>
>
>
> eventually, you HAVE to vacuum for various reasons.  you might use a cron
> job (or task scheduler) to setup a weekly job that does a vacuumdb -a).


Or put a naptime of 6h.