prq 데이터 베이스 과부하 20 페이지(약400건) 미처리 문제

[Taebu]

임시 조치로 데이터 베이스를 템프 공간에 확보 하도록 조치

CREATE TABLE prq_cdr_201701 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201701 
select * from prq_cdr where cd_date like '2017-01%';
delete from prq_cdr where cd_date like '2017-01%';

형태로 진행.

[Taebu]

use prq;
select * from prq_cdr where cd_state=0 limit 1;
select count(*) from prq_cdr  where cd_state=0;

select * from prq_cdr;

insert into  prq_cdr_201701 
select * from prq_cdr where cd_date like '2017-01%';
delete from prq_cdr where cd_date like '2017-01%';

insert into  prq_cdr_201702 
select * from prq_cdr where cd_date like '2017-02%';

delete from prq_cdr where cd_date like '2017-02%';

insert into  prq_cdr_201703 
select * from prq_cdr where cd_date like '2017-03-0%';

delete from prq_cdr where cd_date like '2017-03-0%';

insert into  prq_cdr_201703 
select * from prq_cdr where cd_date like '2017-03-1%';

delete from prq_cdr where cd_date like '2017-03-1%';
-- 201612
CREATE TABLE prq_cdr_201612 
  AS (SELECT *
      FROM prq_cdr limit 0);

insert into  prq_cdr_201612 
select * from prq_cdr where cd_date like '2016-12%';
select count(*) cnt from   prq_cdr_201612 ;
delete from prq_cdr where cd_date like '2016-12%';
-- 201611
CREATE TABLE prq_cdr_201611 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201611 
select * from prq_cdr where cd_date like '2016-11%';
delete from prq_cdr where cd_date like '2016-11%';

-- 201610
CREATE TABLE prq_cdr_201610 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201611 
select * from prq_cdr where cd_date like '2016-10%';
delete from prq_cdr where cd_date like '2016-10%';

-- 201609
CREATE TABLE prq_cdr_201609 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201609 
select * from prq_cdr where cd_date like '2016-09%';
delete from prq_cdr where cd_date like '2016-09%';

-- 201608
CREATE TABLE prq_cdr_201608 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201608 
select * from prq_cdr where cd_date like '2016-08%';
delete from prq_cdr where cd_date like '2016-08%';

-- 201607
CREATE TABLE prq_cdr_201607 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201607 
select * from prq_cdr where cd_date like '2016-07%';
delete from prq_cdr where cd_date like '2016-07%';

-- 201606
CREATE TABLE prq_cdr_201606 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201606 
select * from prq_cdr where cd_date like '2016-06%';
delete from prq_cdr where cd_date like '2016-06%';

-- 201605
CREATE TABLE prq_cdr_201605 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201605 
select * from prq_cdr where cd_date like '2016-05%';
delete from prq_cdr where cd_date like '2016-05%';

-- 201604
CREATE TABLE prq_cdr_201604 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201604 
select * from prq_cdr where cd_date like '2016-04%';
delete from prq_cdr where cd_date like '2016-04%';

-- 201603
CREATE TABLE prq_cdr_201603 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201603 
select * from prq_cdr where cd_date like '2016-03%';
delete from prq_cdr where cd_date like '2016-03%';

-- 201602 
CREATE TABLE prq_cdr_201602 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201602 
select * from prq_cdr where cd_date like '2016-02%';
delete from prq_cdr where cd_date like '2016-02%';

-- 201601 
CREATE TABLE prq_cdr_201601 
  AS (SELECT *
      FROM prq_cdr limit 0);
insert into  prq_cdr_201601 
select * from prq_cdr where cd_date like '2016-01%';
delete from prq_cdr where cd_date like '2016-01%';

[Taebu]

우선 모든 데이터는아닌 일부 2016년 1월 부터 2017년 3월 20일 까지 데이터를 옮기는 것으로 처리.

차후 딜레이시 성능 개선의 일환으로 로그 발생시 바로 데이터 이전 하도록 성능 개선.