Re: dbmail_authlog performance

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
> [email protected]
> 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

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail