Re: [GENERAL] Transaction ID Wraparound Monitoring

[William Dunn]

Hello Jan,

I think your calculation is slightly off because per the docs when
PostgreSQL comes within 1 million of the age at which an actual wraparound
occurs it will go into the safety shutdown mode. Thus the calculation
should be ((2^32)-1)/2-100 rather than just ((2^32)-1)/2 as I think you
are using.

When I first started building out my group's PostgreSQL monitoring solution
I too found the wording of transaction freeze to be a bit difficult to
understand. For my team's internal documentation I have summarized it as
follows, I hope it might be more clear:

...normal XIDs are compared using modulo-2^32 arithmetic, which means that
~(2^32-1)/2- transactions appear in the future and ~(2^32-1)/2 transactions
appear in the past.

This [Transaction ID freeze] behavior of autovacuum is primarily dependent
on the settings autovacuum_freeze_table_age and autovacuum_freeze_max_age,
which are set as database defaults but can also be specified on a per table
basis (as storage parameters in CREATE TABLE or ALTER TABLE)

   - When a table's oldest transaction reaches autovacuum_freeze_table_age,
   the next autovacuum that is performed on that table will be a vacuum freeze
  - PostgreSQL implicitly caps autovacuum_freeze_table_age at
  0.95*autovacuum_freeze_max_age.
   - When a table reaches autovacuum_freeze_max_age PostgreSQL will force
   an autovacuum freeze on that table, even if the table would not otherwise
   be autovacuumed or autovacuum is disabled.
  - PostgreSQL implicitly caps autovacuum_freeze_max_age at 2 billion
  (20)

The actual age that a wraparound occurs is ((2^32)/2)-1. When a PostgreSQL
database comes within 1 million of this age (2^32/2-1-100) the database
will go into the safety shutdown mode" and no longer accept commands,
including the vacuum commands, and your only recovery option is to stop the
server and use a single-user backend (where shutdown mode is not enforced)
to execute VACUUM. This should, obviously, be avoided at all costs.

References:

   -
   
http://www.PostgreSQL.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
   -
   
http://www.PostgreSQL.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
   -
   
http://www.PostgreSQL.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE


Based on the above explanation we consider the following to be the most
correct check for how close you are to an actual wraparound freeze:

CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS
real) AS perc_until_wraparound_server_freeze


(Note that we do this at the table level rather than the database level
like you did, though, so that we have the information we need to tune the
settings for individual tables.)

However it is better to set autovacuum max freeze age well below that value
and monitor that instead. Autovacuum should always do a vacuum freeze for a
table that has exceeded max freeze age, and if you are monitoring for that
you should avoid a wrap around freeze:

CAST (age(relfrozenxid) AS real) / CAST ((least(autovacuum_freeze_max_age,
20)) AS real) AS perc_until_freeze_max_age


And ensure that value does not exceed 100%. Though it is important to note
that max freeze age can be set on a per table basis, so to get the true
autovacuum_freeze_max_age of a table (or the real max of the database) you
would need to check the reloptions field of pg_class for that table and
only if there is no value specified for '%autovacuum_freeze_table_age%' use
current_setting('autovacuum_freeze_max_age')


Let me know if I missed something or can clarify it further.

*Will J. Dunn*
*willjdunn.com *

On Thu, Jul 30, 2015 at 11:44 AM, Adrian Klaver 
wrote:

> On 07/30/2015 08:41 AM, Jan Keirse wrote:
>
>> On Thu, Jul 30, 2015 at 2:56 PM, Adrian Klaver
>>  wrote:
>>
>>> On 07/30/2015 02:55 AM, Jan Keirse wrote:
>>>

 Hello,

 we have some very write heavy databases and I have our monitoring
 system watch the transaction age of my databases to be alerted before
 we get into problems in case autovacuum can't keep up to avoid
 transaction ID wraparound.

 The query I am executing is this:
 SELECT max(age(pg_database.datfrozenxid)) / 2147483648.0 * 100.0 AS
 "Percentage of transaction ID's used" FROM pg_database;

 My believe was that if this reaches 100 the database will stop
 accepting writes and one must vacuum. I have set alerts on 50 and 90,
 the result is around 9 so my believe was autovacuum is working fine
 for my workload.
 I often see autovacuum kicking in to prevent XID Wraparround, I
 thought that was just to be on the safe side and vacuum well before
 it's too late.

 However today I saw this post:


 http://blog.getsentry.com/2015/07/23/transaction-id-wraparound-in-postgres.html

 The following line has me worried:
 ... that dat

Re: [GENERAL] pghba.conf

[Raymond O'Donnell]

On 03/08/2015 10:52, Ramesh T wrote:
> Hi All,
>   I changed in pg_hba.conf like,in postgres 9.3 under linux
> host replication base_backup_user   127.0.0.1/32
>  trust
> 
> after above changes ,restarted it .
> 
> when i try pgbasebackp getting  error like,
> 
> pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry
> for replication connection from host "192.168.02.64", user
> "base_backup_user", SSL off


Well, the error says it all: your replication connection is coming from
192.168.02.64, whereas you specified 127.0.0.1 in pg_hba.conf.

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pghba.conf

[Ramesh T]

Hi All,
  I changed in pg_hba.conf like,in postgres 9.3 under linux
host replication base_backup_user   127.0.0.1/32 trust

after above changes ,restarted it .

when i try pgbasebackp getting  error like,

pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry
for replication connection from host "192.168.02.64", user
"base_backup_user", SSL off

any help to resolve this issue..

Re: [GENERAL] scaling postgres

[Seref Arikan]

On Mon, Aug 3, 2015 at 8:22 AM, Chris Withers 
wrote:

> On 03/08/2015 08:15, Jony Cohen wrote:
>
>> SSD disks are cheep these days but they don't like repeated
>> writes/deletes so it might cause problems down the line (hence my first RAM
>> recommendation)
>>
>> as for keeping the raw data - you could easily do it if you use
>> partitions, if you have daily partitions inheriting from a master table you
>> can quickly access the last day (or even several days)
>> but do take note that a full table scan takes time and you'll need to
>> manage writing to the right partition yourself (not that hard - simply
>> insert to _)
>>
>> if you can write the data daily, keeping it will not add any real load (a
>> little on parse times for queries that access the master table)
>>
>
> Interesting, you seem a lot less fussed by these numbers than I am, which
> is good to hear!
>
> At what point does postgres stop scaling?
> What happens when the computational load no longer fits on one machine?
> What are the options then?


I think it is hard to come up with blanket responses to generic questions
such as  "What happens when the computational load no longer fits on one
machine?"
I'd say consider a scaling strategy that may be able to make use of your
domain model first, if that is possible of course.

I work in healthcare and patient centric records let me consider multiple
servers for lots and lots of patients. The engineering team from instagram
has been sharing their experience with postgres, which is possible due to
their domain model.

So my humble suggestion is: start from the simplest scenario, ask yourself
if you can use multiple, independent postgres installations, if your domain
model and its data model allow you to do this. If not, you may still have
other options, but it all depends on your access patterns etc.

All the best
Seref


>
> cheers,
>
> Chris
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] scaling postgres

[Chris Withers]

On 03/08/2015 08:15, Jony Cohen wrote:
SSD disks are cheep these days but they don't like repeated 
writes/deletes so it might cause problems down the line (hence my 
first RAM recommendation)


as for keeping the raw data - you could easily do it if you use 
partitions, if you have daily partitions inheriting from a master 
table you can quickly access the last day (or even several days)
but do take note that a full table scan takes time and you'll need to 
manage writing to the right partition yourself (not that hard - simply 
insert to _)


if you can write the data daily, keeping it will not add any real load 
(a little on parse times for queries that access the master table)


Interesting, you seem a lot less fussed by these numbers than I am, 
which is good to hear!


At what point does postgres stop scaling?
What happens when the computational load no longer fits on one machine? 
What are the options then?


cheers,

Chris



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] scaling postgres

[Jony Cohen]

Hi,
I'd recommend looking into 2 solutions here, depending if you want to keep
the data or not and how "fault tolerant" is the app (could you loose data
from the last hour?)
If you could loose some data and you don't intend keeping the whole data
set - I'd look at using RAM as your storage - it's the fastest place to
store data and you can easily get servers with lot's of RAM these days.
If you can't loose data then you'll need to use disks - depending on how
big each row is, compute your expected read/write throughput and go
shopping :)
for 1kb rows you get 60GB per hour = 16MB per sec - simple disks can handle
this.
for 10kb rows you get 160MB/s - now you need better disks :)

SSD disks are cheep these days but they don't like repeated writes/deletes
so it might cause problems down the line (hence my first RAM recommendation)

as for keeping the raw data - you could easily do it if you use partitions,
if you have daily partitions inheriting from a master table you can quickly
access the last day (or even several days)
but do take note that a full table scan takes time and you'll need to
manage writing to the right partition yourself (not that hard - simply
insert to _)

if you can write the data daily, keeping it will not add any real load (a
little on parse times for queries that access the master table)

Just my 2 cents,
Regards,
 - Jony


On Mon, Aug 3, 2015 at 9:53 AM, Chris Withers 
wrote:

> Hi All,
>
> I'm curious if there are recommendations for scaling postgres to what, for
> me, seems like "a lot" of data...
>
> The app in question currently writes around 1.5 billion rows into a table
> before rolling them up into tables that have a few million roll up rows
> each. That 1.5 billion row table is emptied and refilled each day, so we're
> talking about quite high write as well as quite high read. Where can I find
> could examples/docs of how to scale postgres for this kind of data load?
> What sort of hardware would I be looking to spec?
>
> Okay, now this app may well eventually want to progress to storing those
> 1.5 billion rows per day. Is that feasible with postgres? If not, what
> storage and processing solutions would people recommend for that kind of
> data load?
>
> cheers,
>
> Chris
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>