svn commit: r1673195 - in /spamassassin/trunk/sql: README.txrep txrep_mysql.sql txrep_pg.sql

[jq...@apache.org]

Author: jquinn
Date: Mon Apr 13 13:55:47 2015
New Revision: 1673195

URL: http://svn.apache.org/r1673195
Log:
Added notes about culling old txrep data

Modified:
    spamassassin/trunk/sql/README.txrep
    spamassassin/trunk/sql/txrep_mysql.sql
    spamassassin/trunk/sql/txrep_pg.sql

Modified: spamassassin/trunk/sql/README.txrep
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/README.txrep?rev=1673195&r1=1673194&r2=1673195&view=diff
==============================================================================
--- spamassassin/trunk/sql/README.txrep (original)
+++ spamassassin/trunk/sql/README.txrep Mon Apr 13 13:55:47 2015
@@ -38,9 +38,9 @@ database server is listening.
 user_awl_dsn                DBI:mysql:spamassassin:localhost
 
 Would tell SpamAssassin to connect to the database named spamassassin using
-MySQL on the local server, and since <port> is omitted, the driver will use the
-default port number.  The other two required options tells SpamAssassin to use
-the defined username and password to establish the connection.
+MySQL on the local server, and since <port> is omitted, the driver will use
+the default port number.  The other two required options tells SpamAssassin
+to use the defined username and password to establish the connection.
 
 If the user_awl_dsn option does not exist, SpamAssassin will not attempt
 to use SQL for tracking reputations.
@@ -85,3 +85,12 @@ required lines to your global configurat
 you must specify the proper storage backend in the config file in order
 for this to work and the current username must be passed to spamd.
 
+Maintenance
+---------------
+
+It is recommended to keep user_awl_sql_table clear of stale data, for
+performance reasons. A sample query that can be run on a regular
+schedule is below:
+
+DELETE FROM txrep WHERE last_hit <= (now() - INTERVAL 120 day);
+

Modified: spamassassin/trunk/sql/txrep_mysql.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/txrep_mysql.sql?rev=1673195&r1=1673194&r2=1673195&view=diff
==============================================================================
--- spamassassin/trunk/sql/txrep_mysql.sql (original)
+++ spamassassin/trunk/sql/txrep_mysql.sql Mon Apr 13 13:55:47 2015
@@ -5,5 +5,7 @@ CREATE TABLE txrep (
   count int(11) NOT NULL default '0',
   totscore float NOT NULL default '0',
   signedby varchar(255) NOT NULL default '',
-  PRIMARY KEY (username,email,signedby,ip)
+  last_hit timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (username,email,signedby,ip),
+  KEY last_hit (last_hit)
 ) ENGINE=InnoDB;

Modified: spamassassin/trunk/sql/txrep_pg.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/txrep_pg.sql?rev=1673195&r1=1673194&r2=1673195&view=diff
==============================================================================
--- spamassassin/trunk/sql/txrep_pg.sql (original)
+++ spamassassin/trunk/sql/txrep_pg.sql Mon Apr 13 13:55:47 2015
@@ -5,7 +5,9 @@ CREATE TABLE txrep (
   count int(11) NOT NULL default '0',
   totscore float NOT NULL default '0',
   signedby varchar(255) NOT NULL default '',
-  PRIMARY KEY (username,email,signedby,ip)
+  last_hit timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (username,email,signedby,ip),
+  KEY last_hit (last_hit)
 );
 
 ALTER TABLE txrep SET (fillfactor=95);