Re: count rows in a partition
Not at all Rick, I always appreciate your comments.
This is related an old issue that I have had on the forums before. I feel these is no simple way for auto maintenance in mysql. Maybe not a problem for a huge company with permanent IT dept. Not so good for embedded systems with no IT dept to look after it. Especially when DB made by engineers with not all the time in the world to fiddle with the database.
I have a mySQL DB storing status and log data from an embedded control system. 60 log tables with 3-5 columns being filled once a second with data used for trend views. The system has been running for two months now so I have 5million rows in these tables.
Server machine is fast with INTEL S3700 SSD 400GB. The machine/DB seems to be handling this no sweat at all I am happy to say. InnoDB with 12GB buffer pool size helps also. (tip from you I think).
My trend view page in user interface has sampling code so that I can read from these tables eficiently even when large.
The system is on a ship so remote support is not easy and neither is maintenance. Customer specified they just want 1 month max data and older data can be deleted.
I seem to have two options:
- let tables grow and just limit to last month in trend view software, be a while before 400GB is full. 2 month and db is 10GB. Then swap disks at some point. I am unsure of DB performance after operating like this for many months though.
- use partitions to keep trimming down the tables. Ideally automatically with event scheduler in db or by calling sql scripts using windows server event scheduler. Even if I cannot get this auto function to work I at least have partitions so I can trim tables down quickly and avoid TRUNCATE or DELETE.
One concern is the database will continue to grow in size on disk even if I have auto partition dropping? Comments on this and how to avoid?
I have run create partitions on these tables now and that went well. Next I will test some drop/recreate partitions, then try some event to call a routine to do this.
Question. If I have monthly paritions what happens when I get into a new year, will this screw things up?
EXPLAIN PARTITIONS SELECT * FROM log_01;
1 SIMPLE log_superwide_prt p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL 5048263
This what you wanted?
ALTER TABLE pgs.log_01
PARTITION BY LIST ( month( time ) )
(
PARTITION p0 VALUES IN ( 0 ),
PARTITION p1 VALUES IN ( 1 ),
PARTITION p2 VALUES IN ( 2 ),
PARTITION p3 VALUES IN ( 3 ),
PARTITION p4 VALUES IN ( 4 ),
PARTITION p5 VALUES IN ( 5 ),
PARTITION p6 VALUES IN ( 6 ),
PARTITION p7 VALUES IN ( 7 ),
PARTITION p8 VALUES IN ( 8 ),
PARTITION p9 VALUES IN ( 9 ),
PARTITION p10 VALUES IN ( 10),
PARTITION p11 VALUES IN ( 11 ),
PARTITION p12 VALUES IN ( 12 )
);
Schedule I will try:
DELIMITER $$
USE `pgs`$$
CREATE EVENT dump_log_partitions
ON SCHEDULE
EVERY 1 DAY
STARTS '2013-01-01 11:15:00' -- edit the date
DO
BEGIN
DECLARE iMonth INTEGER;
SET iMonth = Month(CURRENT_DATE - INTERVAL 2 MONTH);
call logg_recreate_partition( iMonthSelect );
END$$
routine I will try:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `logg_recreate_partition`( partno int )
BEGIN
set @stmt1 = concat( 'alter table log_01 drop partition p', partno );
prepare stmt1 FROM @stmt1;
execute stmt1;
deallocate prepare stmt1;
set @stmt1 = concat ('alter table log_01 add partition ( partition p', partno,
' values in ( ', partno, ' ))' );
prepare stmt1 FROM @stmt1;
execute stmt1;
deallocate prepare stmt1;
END