[GENERAL] PGpool question

[Andrew Kerber]

This may be the wrong list, but I am not sure where it needs to go.  I am
trying to set up pgpool, and I keeping on getting this message:

NOTICE:  add node from hostname:"xxx" port:9000 pgpool_port:
rejected.
Jul 28 22:11:49 xx pgpool[10768]: [172-2] 2017-07-28 22:11:49: pid
10768: DETAIL:  verify the other watchdog node configurations
Jul 28 22:11:49 x pgpool[10768]: [172-3] 2017-07-28 22:11:49: pid
10768: LOCATION:  watchdog.c:1481

I believe I have mismatched settings for the watchdog configuration, but I
cannot find them.  I have debugging turned all the way up, but nothing is
telling me what setting is the problem,  Is there any way I can figure out
what specific watchdog setting its complaining about?


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Huge Pages - setting the right value

[Andrew Kerber]

Yes, those should always be disabled using tuned or other methods. Using
tuned is described here (second method).  I think the grub.conf method
described is unique to RHEL/OEL/CENTOS.
http://houseofbrick.com/disabling-transparent-hugepages-using-tuned/



On Sun, Jun 11, 2017 at 5:00 PM, Lucas Possamai <drum.lu...@gmail.com>
wrote:

>
>
> 2017-06-12 9:52 GMT+12:00 Andrew Kerber <andrew.ker...@gmail.com>:
>
>> Was that transparent hugepages or standard hugepages?  databases commonly
>> have problems dealing with transparent hugepages.
>>
>>
>
>
> IN my case, it was the Transparent Hugepages
>
>
> Lucas
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Huge Pages - setting the right value

[Andrew Kerber]

Was that transparent hugepages or standard hugepages?  databases commonly
have problems dealing with transparent hugepages.

On Sun, Jun 11, 2017 at 4:39 PM, Lucas Possamai <drum.lu...@gmail.com>
wrote:

> 2017-06-12 7:52 GMT+12:00 Andrew Kerber <andrew.ker...@gmail.com>:
>
>> I am sure it does not.
>>
>> Sent from my iPhone
>>
>> > On Jun 11, 2017, at 10:50 AM, pinker <pin...@onet.eu> wrote:
>> >
>> > Andrew Kerber wrote
>> >> I can't give you an absolutely authoritative answer, but because of the
>> >> way hugepages are implemented and allocated, I can't think how they
>> could
>> >> be used for other processes.  Linux hugepages are either 2m or 1g, far
>> too
>> >> large for any likely processes to require. They cannot be allocated in
>> >> partial pages.
>> >
>> > thank you for your help.
>> > My system is using 2MB pages for shared buffers. I have checked and one
>> of
>> > my processes has used 606788kB of memory, so potentially could use ~ 300
>> > huge pages, but does postgres can use it for non shared memory?
>> >
>> >
>> >
>> >
>> > --
>> > View this message in context: http://www.postgresql-archive.
>> org/Huge-Pages-setting-the-right-value-tp5952972p5965963.html
>> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>> >
>> >
>> > --
>> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
>
> In my case, we had the HugePages enabled but not configured in our Master
> DB Server. When we increased the server resources (More RAM & CPU) we had
> lots of issues with HugePages. Specially I/O ones. Had to disabled it.
>
> Running Ubuntu 14.04 Server @ Amazon.
>
>
> Lucas
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Huge Pages - setting the right value

[Andrew Kerber]

I am sure it does not. 

Sent from my iPhone

> On Jun 11, 2017, at 10:50 AM, pinker <pin...@onet.eu> wrote:
> 
> Andrew Kerber wrote
>> I can't give you an absolutely authoritative answer, but because of the
>> way hugepages are implemented and allocated, I can't think how they could
>> be used for other processes.  Linux hugepages are either 2m or 1g, far too
>> large for any likely processes to require. They cannot be allocated in
>> partial pages.
> 
> thank you for your help. 
> My system is using 2MB pages for shared buffers. I have checked and one of
> my processes has used 606788kB of memory, so potentially could use ~ 300
> huge pages, but does postgres can use it for non shared memory?
> 
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965963.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Huge Pages - setting the right value

[Andrew Kerber]

I can't give you an absolutely authoritative answer, but because of the way 
hugepages are implemented and allocated, I can't think how they could be used 
for other processes.  Linux hugepages are either 2m or 1g, far too large for 
any likely processes to require. They cannot be allocated in partial pages.

Sent from my iPad

> On Jun 11, 2017, at 10:02 AM, pinker  wrote:
> 
> We are experiencing some performance issues because of high CPU load. So I
> would like to ask one more time. The exact question is:
> Does PostgreSQL can use huge pages for processes or only for shared buffers?
> (Does it make any sense to set the number of huge pages above the
> shared_buffers?)
> Any help or suggestions would be much appreciated! 
> 
> 
> 
> --
> View this message in context: 
> http://www.postgresql-archive.org/Huge-Pages-setting-the-right-value-tp5952972p5965956.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] Error that shouldn't happen?

[Andrew Kerber]

It appears to me you might be making this a lot more difficult than
necessary. Why not just pre-create the required partitions daily or weekly
or monthly? Or do you have a requirement that a new partition only be
created the first time it is required?

On Thu, May 18, 2017 at 3:31 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, May 18, 2017 at 1:18 PM, Rob Brucks 
> wrote:
>
>> According to this post, adding "if not exists" won't really help for race
>> conditions.
>>
>>
>>
>> "The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend to
>>
>> handle concurrency issues any better than regular old CREATE TABLE,
>>
>> which is to say not very well." - Robert Haas
>>
>>
>>
>> https://www.postgresql.org/message-id/CA+TgmoZAdYVtwBfp1FL2s
>> mzbihcwt4uprzrlnnx1nb30ku3...@mail.gmail.com
>>
>>
>>
>> It still doesn't explain how the function got past creating the table,
>> but failed on the index.  If another thread was also creating the table
>> then there should have been lock contention on the create table statement.
>>
>>
>>
> A​T1: Insert, failed, cannot find table
> AT2: Insert, failed, cannot find table
> BT2: Create Table, succeeds
> BT1: Create Table; fails, it exists now, if exists converts to a warning
> CT2: Create Index, succeeds
> CT1: Create Index, fails , hard error
> DT2: Insert, succeeds
> ​DT1: Never Happens
>
> What that post seems to be describing is that it is possible the "BT1"
> actually hard errors instead of just being converted into a notice.  There
> is no statement visible action to show that interleave but there is an
> underlying race condition since both BT1 and BT2 are executing concurrently.
>
> In short even with IF NOT EXISTS you are not guaranteed to not fail.  But
> at least IF NOT EXISTS makes the probability of not failing > 0.  It
> doesn't handle the concurrency any better - but it does change the outcome
> in some of those less-than-ideally handled situations.
>
> David J.
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

Yes, that was the first item on my list (disk space)...

On Fri, Apr 28, 2017 at 11:56 AM, Scott Marlowe <scott.marl...@gmail.com>
wrote:

> On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber <andrew.ker...@gmail.com>
> wrote:
> > I am a fairly experienced Oracle DBA, and we are starting to move in to
> the
> > PostgreSQL world.  I would expect the standard monitoring items are
> required
> > for mission critical postgres apps, Ie, disk space, wal log space, log
> > monitoring, process counts,software running, connection available on the
> > correct port, CPU usage.
> >
> > Are there additional PostgreSQL specific items that need to be monitored?
> > if so, what items?
>
> Amid all the other recommendations standard OS level monitoring is a
> good idea. You don't wanna run out of space on drives etc.
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

yes, replication monitoring is high on the oracle list also, just forgot to
mention it.  I ran into a similar transaction issue in Oracle when they
were running queries across database links and not committing.  Its a
little known fact that any oracle query that runs across a database link
starts a transaction even without any Insert/update/delete command, so I
had to explain that to my developers.

On Fri, Apr 28, 2017 at 11:04 AM, John R Pierce <pie...@hogranch.com> wrote:

> On 4/28/2017 7:39 AM, Andrew Kerber wrote:
>
>> I am a fairly experienced Oracle DBA, and we are starting to move in to
>> the PostgreSQL world.  I would expect the standard monitoring items are
>> required for mission critical postgres apps, Ie, disk space, wal log space,
>> log monitoring, process counts,software running, connection available on
>> the correct port, CPU usage.
>>
>
> the nagios project has a rather handy monitoring script, check_postgres,
> this is a perl script that can be invoked from most any configurable
> monitoring framework, and has options to do 100s of different sorts of
> things, returning simple terse text output that can be parsed by said
> monitoring framework.
>
>
> Are there additional PostgreSQL specific items that need to be monitored?
>> if so, what items?
>>
>
>
> its always a good idea to watch for stale 'idle in transaction'
> connections, as they gum up the important VACUUM processing.   you can make
> a simple query against pg_stat_activity to find the oldest 'idle in
> transaction', and if there are any more than, say, 1 hour old, its worth
> tracking down why they are happening and hammering the developers to fix
> it.oracle developers working in java seem to generate a lot of these
> (speaking from experience) if they aren't careful to avoid it.   Postgres
> JDBC starts a transaction on a simple SELECT, and if the app then just sits
> there doing nothing, that transaction stays open indefinitely.   I had a
> lot of pushback from developers insisting that SELECT's should not need
> commit.
>
> the one big thing I don't see mentioned in your list above is monitoring
> replication
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

[GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

I am a fairly experienced Oracle DBA, and we are starting to move in to the
PostgreSQL world.  I would expect the standard monitoring items are
required for mission critical postgres apps, Ie, disk space, wal log space,
log monitoring, process counts,software running, connection available on
the correct port, CPU usage.

Are there additional PostgreSQL specific items that need to be monitored?
if so, what items?

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] The Contractor Conundrum

[Andrew Kerber]

All I can say is welcome to the world of contracting. You have successfully
described the nature of the business.

On Wed, Apr 26, 2017 at 10:33 AM, Geoff Winkless 
wrote:

> On 26 April 2017 at 15:45, Melvin Davidson  wrote:
>
>> In summary, I can only advise that aspiring contractors find out as much
>> about a system/schema/policies before commiting to a contract, no matter
>> how much they offer to pay.
>
>
> An interesting perspective, thanks.
>
> From the other side, I'd be more inclined to say, be prepared to do what
> is asked of you and no more, no matter how much the temptation would be to
> say "but if you just...".
>
> Contractors are treated with suspicion by perm staff, partly because of
> financial jealousy (most salaried staff can't even dream of the sort of
> rates contractors will command) but also because outsiders will often point
> out previous poor work to the boss, often with no understanding of the
> historical reasons behind it.
>
> Trying to change people's attitudes, unless you're explicitly brought in
> with that brief, is likely to provoke exactly that sort of negative
> pushback, and does little but make your life harder.
>
> ​Geoff​
>
>


-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Largepages in Windows

[Andrew Kerber]

Oh, I can answer that.  The owner of the postgreSQL executable must have
the privilege to lock pages in memory.

On Tue, Jan 17, 2017 at 9:38 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Andrew Kerber <andrew.ker...@gmail.com> writes:
> > Does PostgreSQL 9.4 support large pages in windows?  The setting is there
> > in the postgresql.conf, but I cant tell if it is supported in windows?
>
> No, but there's a pending patch for that:
>
> https://www.postgresql.org/message-id/flat/0A3221C70F24FB4583343325556920
> 4D1F5F1751@G01JPEXMBYT05
>
> Looks like it's blocked on the question of what privileges are required
> to use the feature.
>
> regards, tom lane
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] Largepages in Windows

[Andrew Kerber]

Thats what I needed, thank you.  Windows generally calls them large pages,
AIX also calls them large pages, really they are typically only called
hugepages on Linux.

On Tue, Jan 17, 2017 at 9:28 AM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:

> On 01/17/2017 07:20 AM, Andrew Kerber wrote:
>
>> Does PostgreSQL 9.4 support large pages in windows?  The setting is
>> there in the postgresql.conf, but I cant tell if it is supported in
>> windows?
>>
>
> Are you talking about this?:
>
> https://www.postgresql.org/docs/9.4/static/runtime-config-resource.html
>
> huge_pages (enum)
>
> Enables/disables the use of huge memory pages. Valid values are try
> (the default), on, and off.
>
> At present, this feature is supported only on Linux. The setting is
> ignored on other systems when set to try.
>
> The use of huge pages results in smaller page tables and less CPU time
> spent on memory management, increasing performance. For more details, see
> Section 17.4.4.
>
> With huge_pages set to try, the server will try to use huge pages, but
> fall back to using normal allocation if that fails. With on, failure to use
> huge pages will prevent the server from starting up. With off, huge pages
> will not be used.
>
>
>
>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

[GENERAL] Largepages in Windows

[Andrew Kerber]

Does PostgreSQL 9.4 support large pages in windows?  The setting is there
in the postgresql.conf, but I cant tell if it is supported in windows?

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

[GENERAL] Looking for a good programming reference

[Andrew Kerber]

So, I am a decent oracle SQL and PL/SQL programmer looking to expand into
PostgreSQL.  Can someone point me to a decent programming book on the
topic?  I have looked Amazon and Apress and not found much, so I am not
sure where to turn.  Or perhaps I am looking the wrong places.

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'