Buffer Cache 초기화 후, Data Caching 을 위한 Script 공유

Buffer Cache 초기화 후, Data Caching 을 위한 Script 공유

dba_cache_data.sh

dba_cache_data.sql

안녕하세요. 준경대디 입니다.

 

금일 새벽점검 나왔다가 잠시 여유가 생겨,

Data Cache Script 을 만들어보았습니다.

 

■ 목적

점검시 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;

 

[출처] Buffer Cache 초기화 후, Data Caching 을 위한 Script 공유드립니다. (MySQL Power Group) |작성자 준경대디

[출처] Buffer Cache 초기화 후, Data Caching 을 위한 Script 공유드립니다. (MySQL Power Group) |작성자 준경대디