■ 목적
점검시 DB Restart 가 진행되어 Buffer Memory 가 초기화 되었을 때,
점검 오픈전 데이터들을 일부 Buffer Memory 로 Caching 함으로서
유저유입시 발생하는 Disk IO 충격을 완화함
■ Reference
- MySQL NF 5.6 - InnoDB Buffer Pool Warm-Up (5.6.3~)
: http://cafe.naver.com/mysqlpg/102
- MariaDB InnoDB Buffer Pool Warm-Up
: http://cafe.naver.com/mysqlpg/75
■ dba_cache_data.sh
#!/bin/bash source /usr1/mysql/dba/.env.ini i f [ -z "$1" ]
then
SLEEP_TIME=1;
else
SLEEP_TIME=$1;
fi LoginPathStatus=`$MySQL_HOME/bin/mysql_config_editor print --all | grep dbmon | wc -l` if [ $LoginPathStatus -eq 1 ];
then
DBMonIdPwd=`echo --login-path=dbmon`
else
DBMonIdPwd=`echo --user="${_DB_MON_UID}" --password="${_DB_MON_PWD}"`
fi $MySQL_HOME/bin/mysql ${DBMonIdPwd} --skip-column-names -s < dba_cache_data.sql > dba_cache_data_tmp.sql
$MySQL_HOME/bin/mysql ${DBMonIdPwd} --skip-column-names -s < dba_cache_data_tmp.sql |
■ dba_cache_data.sql
select replace(sql_text, "count(*)", concat('count(*) as "[',rno,'] ', table_name, '"')) xxx
from (
select @no := @no + 1 as rno
, concat(table_schema, '.', table_name) as table_name
, concat('select count(*) from ', table_schema, '.', table_name,';') as sql_text
from (select @no := 0 as rno) x, information_schema.tables
where table_schema not in ('information_schema','common_schema','moniter'
,' mysql','perf_mon','performance_schema','ps_helper','test','zzdba','sys','mysql')
and table_name not like '%log%'
and table_name not like 'z_drop%'
and table_name not like '%tmp%'
and table_name not like '%temp%'
and table_name not like '%event%'
and table_name not like '%hist%'
and engine not in ('FEDERATED')
and engine is not null
) z;
|
■ dba_cache_data_old.sql
select concat('select "[', rno, '] ', table_name,'"; ', sql_text) xxx
from (
select @no := @no + 1 as rno
, concat(table_schema, '.', table_name) as table_name
, concat('select count(*) from ', table_schema, '.', table_name,';') as sql_text
from (select @no := 0 as rno) x, information_schema.tables
where table_schema not in ('information_schema','common_schema','moniter' ,' mysql','perf_mon','performance_schema','ps_helper','test','zzdba','sys','mysql')
and table_name not like '%log%'
and table_name not like 'z_drop%'
and table_name not like '%tmp%'
and table_name not like '%temp%'
and table_name not like '%event%'
and table_name not like '%hist%'
) z; |