Mailing List Archive: dbmail_authlog performance

dbmail_authlog performance

Feb 23, 2015, 12:05 PM

Post #1 of 3 (1517 views)

Hi,

I am running Dbmail 3.1.17 with authlog enabled. Database is PgSQL.

Server has lots of processing power (2 sixcore processors, > 128 GB RAM, fast SAS disks, RAID-6)

The server has 50 simultaneous connections. DB size is 300 GB.

I have noticed a huge performance issue when authlog is enabled. I am monitoring slow statements in DB size and almost all UPDATE queries in dbmail_authlog take 2 seconds or more. Tried to delete all records in this table and still no luck with any performance improvement. In this scenario the CPU processing is between in the range of 80-95%.

< 2015-02-23 16:39:18.401 BRT >LOG: duration: 2009.685 ms execute 34345: UPDATE dbmail_authlog SET logout_time=NOW(), status=$1, bytes_rx=$2, bytes_tx=$3 WHERE id=$4

If I disable authlog, CPU processing goes to 20%.

Considering authlog is important for auditing purposes, is it possible to tweak its performance? There is a huge gap in performance when using authlog feature.

From my understanding, it should be nice to have a separate database for just authlog processing.

Sincerely.

Rogerio

Re: dbmail_authlog performance [ In reply to ]

ahicks at p-o

Feb 24, 2015, 3:21 AM

Post #2 of 3 (1481 views)

Permalink

Hi,

The following update should add the missing primary key so you get sub
second response times.

ALTER TABLE dbmail_authlog
ADD PRIMARY KEY (id);

Kind regards,
Alan

On 23/02/2015 20:05, Rogerio Pereira wrote:
> Hi,
> I am running Dbmail 3.1.17 with authlog enabled. Database is PgSQL.
> Server has lots of processing power (2 sixcore processors, > 128 GB
> RAM, fast SAS disks, RAID-6)
> The server has 50 simultaneous connections. DB size is 300 GB.
> I have noticed a huge performance issue when authlog is enabled. I am
> monitoring slow statements in DB size and almost all UPDATE queries in
> dbmail_authlog take 2 seconds or more. Tried to delete all records in
> this table and still no luck with any performance improvement. In this
> scenario the CPU processing is between in the range of 80-95%.
> < 2015-02-23 16:39:18.401 BRT >LOG: duration: 2009.685 ms execute
> 34345: UPDATE dbmail_authlog SET logout_time=NOW(), status=$1,
> bytes_rx=$2, bytes_tx=$3 WHERE id=$4
> If I disable authlog, CPU processing goes to 20%.
> Considering authlog is important for auditing purposes, is it possible
> to tweak its performance? There is a huge gap in performance when
> using authlog feature.
> From my understanding, it should be nice to have a separate database
> for just authlog processing.
> Sincerely.
> Rogerio
>
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--
Persistent Objects Ltd
128 Lilleshall Road
Morden SM4 6DR

The Home of Lasting Solutions

Mobile: 079 3030 5004
Tel: 020 8544 5292
Web: http://p-o.co.uk
Skype: alan-hicks-london
Google+: https://plus.google.com/+AlanHicksLondon
Twitter: https://twitter.com/alanhickslondon
Facebook: https://www.facebook.com/alan.hicks.london
LinkedIn: https://uk.linkedin.com/in/alanhickslondon/
GitHub: https://github.com/alan-hicks

Re: dbmail_authlog performance [ In reply to ]

rogerio at porta80

Feb 24, 2015, 2:37 PM

Post #3 of 3 (1475 views)

Permalink

Defintely it makes sense and it works. I noticed the absence of a primary key before. Millions of records in a nonindexed table? Hummm...It doesnâ€™t feel right. Thanks for the quick response. I suggest adding this PK on new dbmail releases. It will boost performance in heavy loaded servers. Sincerely. Rogerio >Hi,
>
>The following update should add the missing primary key so you get sub second response times.
>ALTER TABLE dbmail_authlog
> ADD PRIMARY KEY (id);
>
>Kind regards,
>Alan