quickly followed by disk. • Not having fast enough disk can cause your ibdata file to grow uncontrollably for undo logs and more. • CPU is important for the purposes of sorting quickly.
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 25638013 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from user where is_user = 1 and first_name like 'Mike' and last_name like 'Willbanks%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user type: range possible_keys: first_name key: first_name key_len: 276 ref: NULL rows: 3 Extra: Using index condition; Using where 1 row in set (0.00 sec)
BEGIN IF (OLD.`state` <> 'ACTIVE' AND NEW.`state` = 'ACTIVE') THEN INSERT INTO `alert_policy_history` SELECT … WHERE `pv`.`state` = 'ACTIVE' AND `pv`.`policyId` = NEW.`policyId`; END IF; IF (OLD.`state` = 'ACTIVE' AND NEW.`state` <> 'ACTIVE') THEN UPDATE `alert_policy_history` SET `endTimeStamp` = NOW() WHERE `refType` = 'policy' AND `refId` = NEW.`policyId` AND `endTimeStamp` IS NULL; END IF; END
l_loop bigint default 0; set l_loop :=fromTime div (24 * 3600) * (24 * 3600); loop1: loop replace into value_cube_daily(…) select …. from value_cube where timeStamp >= l_loop and timeStamp < (l_loop + 24 * 3600) ; set l_loop := l_loop + 24 * 3600; IF l_loop >= toTime THEN leave loop1; end if; end loop loop1; END
declare v double; set @leftHrTime := fromTime div 3600 * 3600; set @rightHrTime := toTime div 3600 * 3600; set @leftDayTime := @leftHrTime div @DAY * @DAY; set @rightDayTime := @rightHrTime div @DAY * @DAY; set @leftDayTime := if(@leftDayTime < @leftHrTime, @leftDayTime + @DAY, @leftDayTime); set v = 0; select sum(value) as value into v from ( # left hourly margin select sum(value) as value from node_values_cube where timeStamp >= @leftHrTime and timeStamp < LEAST(@leftDayTime, @rightHrTime) union # daily core select sum(value) as value from node_values_ddcube where timeStamp >= @leftDayTime and timeStamp < @rightDayTime union # right hourly margin select sum(value) as value from node_values_cube where timeStamp >= GREATEST(@rightDayTime, @leftHrTime) and timeStamp < @rightHrTime ) as x; return v; END;
--sleep=500 backup if [ $? -ne 0 ]; then echo 'Something went wrong!' exit 1 fi exit 0 /usr/bin/mysqlbackup --backup-dir=/backup/mysql --limit-memory=4096 apply-log if [ $? -ne 0 ]; then echo 'Unable to apply log!' exit 1 fi
TRANSACTION 6305BF9D4, ACTIVE 1 sec starting index read mysql tables in use 9, locked 9 LOCK WAIT 236 lock struct(s), heap size 31160, 1858 row lock(s) MySQL thread id 2307727, OS thread handle 0x7f4124b3d700, query id 15395148787 event_scheduler Copying to tmp table INSERT IGNORE INTO `mytable` SELECT … FROM other_table *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 881 page no 1576 n bits 224 index `PRIMARY` of table `mydb`.`other_table ` trx id 6305BF9D4 lock mode S waiting Record lock, heap no 49 PHYSICAL RECORD: n_fields 6; compact format; info bits 0 0: len 8; hex 170000000000214d; asc !M;; 1: len 4; hex 80000001; asc ;; 2: len 6; hex 0006305bf9cb; asc 0[ ;; 3: len 7; hex 3400010014335b; asc 4 3[;; 4: len 8; hex 8000000056475279; asc VGRy;; 5: len 8; hex 650c107eb14e9041; asc e ~ N A;; …