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.