Database Maintenance again

Hi to all,

I'm using gps very well, except for database maintenance.
At start I've set up to run db maintenance (delete not confirmed triplets) every hour, but it takes too long and so I decided to run 2 maintenance (delete not confirmed triplet and older than 14days) at night, closing mysql from frontend servers. I have to close mysql access from remote or during maintenance the queries from frontend are in stale, blocked. Closing the connection, gps client skip the check.

I suppose to have the 1.005 release and this is my db, making a structure dump:

CREATE TABLE IF NOT EXISTS `triplet` (
`client_address` varchar(40) default NULL,
`sender` varchar(160) NOT NULL default '',
`recipient` varchar(160) NOT NULL default '',
`ip64` decimal(4,0) NOT NULL default '0',
`ip32` decimal(4,0) NOT NULL default '0',
`ip16` decimal(4,0) NOT NULL default '0',
`ip8` decimal(4,0) NOT NULL default '0',
`count` int(11) NOT NULL default '0',
`uts` int(11) NOT NULL default '0',
PRIMARY KEY (`recipient`,`sender`,`ip64`,`ip32`,`ip16`,`ip8`),
KEY `sender_recipient_index` (`sender`(15),`recipient`(15)),
KEY `uts` (`uts`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Is there a way to optimize the DB maintenance?
Adding indexes or something other? (count field?)
I don't know if this structure is as well the last released.
The night DB maintenance (2 scripts) takes 3hours!
At the moment my db count 3Million of records.

Thank's!

Simon

Forums: 

I think you could try upgrading to the latest SVN version. There is a new column in it - also I've been running it for a while now so I consider it stable - and the primary index is now a normal ID column. You need to add the ID column to your MySQL DB.

CREATE TABLE `triplet` (
`id` bigint(20) NOT NULL auto_increment,
`client_address` varchar(40) default NULL,
`sender` varchar(160) NOT NULL default '',
`recipient` varchar(160) NOT NULL default '',
`ip64` decimal(4,0) NOT NULL default '0',
`ip32` decimal(4,0) NOT NULL default '0',
`ip16` decimal(4,0) NOT NULL default '0',
`ip8` decimal(4,0) NOT NULL default '0',
`count` int(11) NOT NULL default '0',
`uts` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `sender` (`sender`,`recipient`,`ip64`,`ip32`,`ip16`,`ip8`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=74306838 ;

You should disable gps/greylist during the upgrade. I had similar problems with the maintenance. The ID got rid of them.

For svn checkout use this:

svn co https://greylist.svn.sourceforge.net/svnroot/greylist/trunk greylist

Thsi phorum does automatically htmlize the URL -- which should read https greylist.svn.sourceforge.net/svnroot/greylist/trunk

Let me know how it goes.

Mimo