[Bug 7096] New: Turn off synchronous_commit for PostgreSQL bayes and AWL/TxRep stores.

[bu...@bugzilla.spamassassin.org]

https://issues.apache.org/SpamAssassin/show_bug.cgi?id=7096

            Bug ID: 7096
           Summary: Turn off synchronous_commit for PostgreSQL bayes and
                    AWL/TxRep stores.
           Product: Spamassassin
           Version: SVN Trunk (Latest Devel Version)
          Hardware: PC
                OS: Linux
            Status: NEW
          Severity: enhancement
          Priority: P2
         Component: Learner
          Assignee: dev@spamassassin.apache.org
          Reporter: tometzky@batory.org.pl

Postgres from version 8.3 has feature which would make it much faster as a
Bayes store or AWL store in SpamAssassin. It can turn off synchronous commits
for non-critical transactions.

This feature will make saving data to a database much faster, say 2 orders of
magnitude faster on ordinary hard drives, as commits would not need to wait for
fsync. This would make it comparable to NoSQL databases but much safer.

This is safe setting - there's no risk of database corruption. In case of a
power failure some transactions from the last 0.6s (3 * default
wal_writer_delay setting) can be lost. But it does not matter at all for bayes
or AWL database.

Please add something like this to sub _connect_db in
lib/Mail/SpamAssassin/BayesStore/PgSQL.pm:

if ( $dbh->{pg_server_version} >= 80300 ) {
  $dbh->do('SET synchronous_commit=off')
}


As a workaround man can set "synchronous_commit=off" in postgresql.conf, but it
would affect all Postgres databases. And it is somewhat advanced.

Is there a standard benchmark for bayes stores to run?

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 7096] Turn off synchronous_commit for PostgreSQL bayes and AWL/TxRep stores.

[bu...@bugzilla.spamassassin.org]

https://issues.apache.org/SpamAssassin/show_bug.cgi?id=7096

Joe Quinn <jq...@pccc.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
                 CC|                            |jquinn+SAbug@pccc.com
         Resolution|---                         |FIXED

--- Comment #1 from Joe Quinn <jq...@pccc.com> ---
I agree with your code and analysis of data loss potential/impact. I don't know
of any benchmarks specific to SA you can run, but you could probably set up a
temporary mail server and flood it with garbage as an ad-hoc test, or run any
other SQL benchmark.

I don't see anything particularly dangerous about adding it so I am committing
your code.

Committed revision 1640221.

-- 
You are receiving this mail because:
You are the assignee for the bug.