Re: need help on memory allocation

2018-01-19 Thread Laurenz Albe 
Rambabu V wrote:
> we are seeing idle sessions consuming memory in our database, could you 
> please help me
> how much memory an idle session can use max and how can we find how much 
> work_mem
> consuming for single process.
> 
> we are getting out of memory error,for this i'm asking above questions.

Are you sure that you see the private memory of the process and not the
shared memory common to all processes?

An "idle" connection should not hav a lot of private memory.

If you get OOM on the server, the log entry with the memory context dump
might be useful information.

Yours,
Laurenz Albe

SV: pgaudit and create postgis extension logs a lot inserts

2018-01-19 Thread Svensson Peter 

A test to create postgis extension made 4 rsyslog processes run for several 
minutes with high cpu util,
and when you have only 8 cpu:s this take lot of resources. 
The create command also have to wait until all the log are written so there are 
great impact.
Log file got 16 GB big only for this.

We have several databases in the same server, some of them with postgis.
Those databases are maintained bye different people, and tell them to disable 
pgaudit
every time they are doing something that can cause lot log will create a bad 
behaviour,
especially when we cannot see in the logs that they have disabled pgaudit.

I think postgis extension is not the only extention that creates both tables, 
functions and insert data,
but if there are a way to configure pgaudit so you get rid of the inserts maybe 
its a way to handle it.

/Peter

Från: Joe Conway [[email protected]]
Skickat: den 18 januari 2018 17:54
Till: Svensson Peter; [email protected]
Ämne: Re: pgaudit and create postgis extension logs a lot inserts

On 01/18/2018 04:12 AM, Svensson Peter wrote:
> When I then install  postgis extension in a database it writes a huge
> amount of logs which slow down the server a lot.
> Not only table creation and functions are logged,  even  all inserts in
> spatial_ref_sys are written to the audit-log.
>
> LOG:  AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> ..
> INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> 
>
> This behaviour make pgaudit useless in our environment due to the
> overhead in log-file write.

How often do you intend to install PostGIS? Disable pgaudit, install
PostGIS, enable pgaudit?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Re: pgaudit and create postgis extension logs a lot inserts

2018-01-19 Thread Magnus Hagander 
On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway  wrote:

> On 01/18/2018 04:12 AM, Svensson Peter wrote:
> > When I then install  postgis extension in a database it writes a huge
> > amount of logs which slow down the server a lot.
> > Not only table creation and functions are logged,  even  all inserts in
> > spatial_ref_sys are written to the audit-log.
> >
> > LOG:  AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> > ..
> > INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> > 
> >
> > This behaviour make pgaudit useless in our environment due to the
> > overhead in log-file write.
>
> How often do you intend to install PostGIS? Disable pgaudit, install
> PostGIS, enable pgaudit?
>

Would it make sense for pgaudit to, at least by option, not include DDL
statements that are generated as "sub-parts" of a CREATE EXTENSION? It
should still log the CREATE EXTENSION of course, but not necessarily all
the contents of it, since that's actually defined in the extension itself
already?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/

RE: pgaudit and create postgis extension logs a lot inserts

2018-01-19 Thread Karen Stone 
Please remove me from this list. Thanks.

Karen Stone| Technical Services| Eldorado |a Division of MphasiS 
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228 
Tel (928) 892 5735 | www.eldoinc.com | www.mphasis.com |[email protected] 


-Original Message-
From: Svensson Peter [mailto:[email protected]] 
Sent: Friday, January 19, 2018 4:04 AM
To: Joe Conway ; [email protected]
Subject: SV: pgaudit and create postgis extension logs a lot inserts


A test to create postgis extension made 4 rsyslog processes run for several 
minutes with high cpu util, and when you have only 8 cpu:s this take lot of 
resources. 
The create command also have to wait until all the log are written so there are 
great impact.
Log file got 16 GB big only for this.

We have several databases in the same server, some of them with postgis.
Those databases are maintained bye different people, and tell them to disable 
pgaudit every time they are doing something that can cause lot log will create 
a bad behaviour, especially when we cannot see in the logs that they have 
disabled pgaudit.

I think postgis extension is not the only extention that creates both tables, 
functions and insert data, but if there are a way to configure pgaudit so you 
get rid of the inserts maybe its a way to handle it.

/Peter

Från: Joe Conway [[email protected]]
Skickat: den 18 januari 2018 17:54
Till: Svensson Peter; [email protected]
Ämne: Re: pgaudit and create postgis extension logs a lot inserts

On 01/18/2018 04:12 AM, Svensson Peter wrote:
> When I then install  postgis extension in a database it writes a huge 
> amount of logs which slow down the server a lot.
> Not only table creation and functions are logged,  even  all inserts 
> in spatial_ref_sys are written to the audit-log.
>
> LOG:  AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> ..
> INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> 
>
> This behaviour make pgaudit useless in our environment due to the 
> overhead in log-file write.

How often do you intend to install PostGIS? Disable pgaudit, install PostGIS, 
enable pgaudit?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source 
Development

Re: pgaudit and create postgis extension logs a lot inserts

2018-01-19 Thread David Steele 
On 1/19/18 6:05 AM, Magnus Hagander wrote:
> 
> 
> On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway  > wrote:
> 
> On 01/18/2018 04:12 AM, Svensson Peter wrote:
> > When I then install  postgis extension in a database it writes a huge
> > amount of logs which slow down the server a lot.
> > Not only table creation and functions are logged,  even  all inserts in 
> > spatial_ref_sys are written to the audit-log.
> >
> > LOG:  AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> > ..
> > INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> > 
> >
> > This behaviour make pgaudit useless in our environment due to the
> > overhead in log-file write.
> 
> How often do you intend to install PostGIS? Disable pgaudit, install
> PostGIS, enable pgaudit?
> 
> 
> Would it make sense for pgaudit to, at least by option, not include DDL
> statements that are generated as "sub-parts" of a CREATE EXTENSION? It
> should still log the CREATE EXTENSION of course, but not necessarily all
> the contents of it, since that's actually defined in the extension
> itself already? 
That's doable, but I think it could be abused if it was always on and
installing extensions is generally not a daily activity.

It seems in this case the best action is to disable pgaudit before
installing postgis or install postgis first.

Regards,
-- 
-David
[email protected]

Re: pgaudit and create postgis extension logs a lot inserts

2018-01-19 Thread David Steele 
Hi Peter,

On 1/18/18 7:12 AM, Svensson Peter wrote:
> 
> Also noticed that setting a session log to none (set
> pgaudit.log='none';)  overrides parameter from postgresql.conf,  but
> does not get logged, and then you can do whatever you want without any
> audit.
> I supposed this changing of  audit session log parameter should be
> logged to file?

pgaudit is not intended to audit the superuser and only a superuser can
set pgaudit.log.

However, you can limit superuser access with the setuser extension:
https://github.com/pgaudit/set_user

Regards,
-- 
-David
[email protected]

Performance impact of lowering max_files_per_process

2018-01-19 Thread Thomas Kellerer 

We have a customer project where Postgres is using too many file handles during 
peak times (around 150.000)

Apart from re-configuring the operating system (CentOS) this could also be 
mitigated by lowering max_files_per_process.

I wonder what performance implications that has on a server with around 50-100 
active connections (through pgBouncer).

One of the reasons (we think) that Postgres needs that many file handles is the 
fact that the schema is quite large (in terms of tables and indexes) and the 
sessions are touching many tables during their lifetime.

My understanding of the documentation is, that Postgres will work just fine if 
we lower the limit, it simply releases the cached file handles if the limit is 
reached. But I have no idea how expensive opening a file handle is in Linux.

So assuming the sessions (and thus the queries) actually do need that many file 
handles, what kind of performance impact (if any) is to be expected by lowering 
that value for Postgres to e.g. 500?

Regards
Thomas