Re: SV: pgaudit and create postgis extension logs a lot inserts

[Bruce Momjian]

On Fri, Jan 19, 2018 at 11:03:42AM +, Svensson Peter wrote:
> 
> 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.

Uh, that seems odd.  Is rsyslog fsync'ing each write?  You should check
the docs on that.  Here is an example report:

http://kb.monitorware.com/simple-question-what-does-the-dash-t10237.html

I don't see the dash behavior mentioned in my Debian Jessie rsyslogd
manual page though.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

SV: pgaudit and create postgis extension logs a lot inserts

[Svensson Peter]

OK,  thanks a lot. 

Regards,
Peter

Från: David Steele [da...@pgmasters.net]
Skickat: den 19 januari 2018 14:41
Till: Magnus Hagander; Joe Conway
Kopia: Svensson Peter; pgsql-performa...@postgresql.org
Ämne: Re: pgaudit and create postgis extension logs a lot inserts

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
da...@pgmasters.net

SV: pgaudit and create postgis extension logs a lot inserts

[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 [m...@joeconway.com]
Skickat: den 18 januari 2018 17:54
Till: Svensson Peter; pgsql-performa...@postgresql.org
Ä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