On Sat, Jul 3, 2010 at 4:40 AM, Michael Scheidell <scheid...@secnap.net> wrote: > On 7/2/10 8:29 PM, Nathan M wrote: > > I've got a production amavisd-new array of servers handling roughly > 16,000,000 messages per day. Users can toggle quarantining. With the > quarantines that are enabled and a 2 week retention we have roughly 12 > > > one word: partitions > (or at the very least, use partition_tags in your queries) >
Michael, We did partition the db some time ago; however, the current queries do not reference the partitions. Each table is has a partition tagged to the week of the year. Since we only retain 2 weeks worth of quarantine the data we need for our report spans over 2 partitions. However, each query relies on data from within both partitions. All the other 51 partitions are empty. With this partitioning scheme do you believe it would still make a difference to add in the partitions to the query? For reference, here's the CREATE statement for the msgs table: CREATE TABLE `amavisd_stats`.`msgs` ( `mail_id` varchar(12) NOT NULL, `secret_id` varchar(12) DEFAULT '', `am_id` varchar(20) NOT NULL, `time_num` int(10) unsigned NOT NULL, `time_iso` char(16) NOT NULL, `sid` int(10) unsigned NOT NULL, `policy` varchar(255) DEFAULT '', `client_addr` varchar(255) DEFAULT '', `size` int(10) unsigned NOT NULL, `content` char(1) DEFAULT NULL, `quar_type` char(1) DEFAULT NULL, `quar_loc` varchar(255) DEFAULT '', `dsn_sent` char(1) DEFAULT NULL, `spam_level` float DEFAULT NULL, `message_id` varchar(255) DEFAULT '', `from_addr` varchar(255) DEFAULT '', `subject` varchar(255) DEFAULT '', `host` varchar(255) NOT NULL, `partition_tag` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`mail_id`,`partition_tag`) USING BTREE, KEY `msgs_idx_sid` (`sid`), KEY `msgs_idx_time_num` (`time_num`), KEY `msgs_idx_mess_id` (`message_id`), KEY `partition_tag` (`partition_tag`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY LIST (partition_tag) (PARTITION p19 VALUES IN (19) ENGINE = InnoDB, PARTITION p46 VALUES IN (46) ENGINE = InnoDB, PARTITION p24 VALUES IN (24) ENGINE = InnoDB, PARTITION p25 VALUES IN (25) ENGINE = InnoDB, PARTITION p26 VALUES IN (26) ENGINE = InnoDB, PARTITION p0 VALUES IN (0) ENGINE = InnoDB, PARTITION p1 VALUES IN (1) ENGINE = InnoDB, PARTITION p2 VALUES IN (2) ENGINE = InnoDB, PARTITION p3 VALUES IN (3) ENGINE = InnoDB, PARTITION p4 VALUES IN (4) ENGINE = InnoDB, PARTITION p5 VALUES IN (5) ENGINE = InnoDB, PARTITION p6 VALUES IN (6) ENGINE = InnoDB, PARTITION p7 VALUES IN (7) ENGINE = InnoDB, PARTITION p8 VALUES IN (8) ENGINE = InnoDB, PARTITION p9 VALUES IN (9) ENGINE = InnoDB, PARTITION p10 VALUES IN (10) ENGINE = InnoDB, PARTITION p11 VALUES IN (11) ENGINE = InnoDB, PARTITION p12 VALUES IN (12) ENGINE = InnoDB, PARTITION p13 VALUES IN (13) ENGINE = InnoDB, PARTITION p14 VALUES IN (14) ENGINE = InnoDB, PARTITION p15 VALUES IN (15) ENGINE = InnoDB, PARTITION p16 VALUES IN (16) ENGINE = InnoDB, PARTITION p17 VALUES IN (17) ENGINE = InnoDB, PARTITION p18 VALUES IN (18) ENGINE = InnoDB, PARTITION p20 VALUES IN (20) ENGINE = InnoDB, PARTITION p21 VALUES IN (21) ENGINE = InnoDB, PARTITION p22 VALUES IN (22) ENGINE = InnoDB, PARTITION p23 VALUES IN (23) ENGINE = InnoDB, PARTITION p27 VALUES IN (27) ENGINE = InnoDB, PARTITION p28 VALUES IN (28) ENGINE = InnoDB, PARTITION p29 VALUES IN (29) ENGINE = InnoDB, PARTITION p30 VALUES IN (30) ENGINE = InnoDB, PARTITION p31 VALUES IN (31) ENGINE = InnoDB, PARTITION p32 VALUES IN (32) ENGINE = InnoDB, PARTITION p33 VALUES IN (33) ENGINE = InnoDB, PARTITION p34 VALUES IN (34) ENGINE = InnoDB, PARTITION p35 VALUES IN (35) ENGINE = InnoDB, PARTITION p36 VALUES IN (36) ENGINE = InnoDB, PARTITION p37 VALUES IN (37) ENGINE = InnoDB, PARTITION p38 VALUES IN (38) ENGINE = InnoDB, PARTITION p39 VALUES IN (39) ENGINE = InnoDB, PARTITION p40 VALUES IN (40) ENGINE = InnoDB, PARTITION p41 VALUES IN (41) ENGINE = InnoDB, PARTITION p42 VALUES IN (42) ENGINE = InnoDB, PARTITION p43 VALUES IN (43) ENGINE = InnoDB, PARTITION p44 VALUES IN (44) ENGINE = InnoDB, PARTITION p45 VALUES IN (45) ENGINE = InnoDB, PARTITION p47 VALUES IN (47) ENGINE = InnoDB, PARTITION p48 VALUES IN (48) ENGINE = InnoDB, PARTITION p49 VALUES IN (49) ENGINE = InnoDB, PARTITION p50 VALUES IN (50) ENGINE = InnoDB, PARTITION p51 VALUES IN (51) ENGINE = InnoDB, PARTITION p52 VALUES IN (52) ENGINE = InnoDB, PARTITION p53 VALUES IN (53) ENGINE = InnoDB, PARTITION p54 VALUES IN (54) ENGINE = InnoDB) */; ------------------------------------------------------------------------------ This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone? Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user Please visit http://www.ijs.si/software/amavisd/ regularly For administrativa requests please send email to rainer at openantivirus dot org
