꿈꾸는 DB 전문가

Bitmap Index Scan - 인덱스와 테이블 간의 정렬관계가 불량할 때 사용됨.

cluster t1 using t1_ix01 ; -- 인덱스로 테이블 레코드정렬 

주의: DML뿐아니라 SELECT 와도 락 호완성이 없다. 

사용자접속을 막고 작업하는 것이 좋다. 

안녕하세요.오늘의 내용은  LIMIT 입니다.

select * 

from employees ignore index(employees_ix02)

where hire_date between '1986-05-02' and '1986-10-31'   -- 건수 1092

order by hire_date desc

limit 10 ; 

limit 조건은 오라클의 rownum 과 비슷하긴 하지만 작동하는 방식이 다르기 때문에 주의가 필요합니다.

위 쿼리의 순서입니다.

1.employees 테이블에서 where 조건에 만족하는 레코드를 전부 읽습니다

2.읽어온 레코드를 hire_date  내림차순으로 정렬합니다

3.정렬된 결과에서 상위 10건을 채우면 바로 반환합니다.

Limit 은 where 조건이 아니므로 항상 마지막에 수행합니다.

또한 쿼리에서 모든 레코드의 정렬이 완료되지 않았다 하더라도 limit 에서 필요한 레코드가 준비되면 

바로 쿼리를 종료시킵니다.

즉, 상위 10개를 채우면 더 이상 정렬하지 않고 사용자에게 결과를 보냄

limt을 사용하는 쿼리의 케이스별로 어떻게 동작하는지 살펴보겠습니다. 

아래는 쿼리 수행 전 후 Sort 누적 현황을 보여줍니다. 

-- 1 수행전 sort 누적현황

show session status like 'Sort%' ;

-- Sort_merge_passes 0 

-- Sort_range 0

-- Sort_rows 734

-- Sort_scan 76

-- 1 수행후 sort 누적현황 (full table scan 사용)

select * 

from employees ignore index(employees_ix02)

where hire_date between '1986-05-02' and '1986-10-31'   -- 건수 1092

order by hire_date desc 

limit 10; 

show session status like 'Sort%' ;

-- Sort_merge_passes 0

-- Sort_range 0

-- Sort_rows 744  --> (+10) 정렬횟수증가되었으나 limit 조건으로 인해 정렬횟수는 10 으로 제한  

-- Sort_scan 77   --> (+1)  테이블풀스캔을 사용한 결과로 정렬수행한 횟수 1 증가

인덱스가 없어 풀테이블스캔으로 결과를 가져오지만 1092건 전체를 정렬하지 않고도 멈춤

그래서 limit 을 하면 쿼리의 작업량을 상당히 줄여주는 역할은 가능합니다.

하지만 정렬은 피할 수가 없습니다.  

-- 2 수행전 sort 누적현황

show session status like 'Sort%' ;

-- Sort_merge_passes 0

-- Sort_range 0

-- Sort_rows 744

-- Sort_scan 77

create index employees_ix02 on employees(hire_date);  

select * 

from employees use index(employees_ix02) -- > 인덱스사용

where hire_date between '1986-05-02' and '1986-10-31'   -- 건수 1092

order by hire_date desc 

limit 10;

-- 2 수행후 sort 누적현황 (employees_ix02 사용)

show session status like 'Sort%' ;

-- Sort_merge_passes 0

-- Sort_range 0

-- Sort_rows 744

-- Sort_scan 77 

--> 정렬횟수 증가 없음 

-- employees_ix02 인덱스를 이용해 스트리밍방식으로 처리되면 정렬횟수가 증가하지 않음 (즉, 결과가 이미 정렬되어 있어 정렬과정자체가 불필요함)

-- 3 order by 에 인덱스에 포함되지 않는 last_nmae 추가 

select * 

from employees use index(employees_ix02)

where hire_date between '1986-05-02' and '1986-10-31'   -- 건수 1092

order by hire_date desc, last_name  

limit 10;

-- 3 수행후 sort 누적현황 (employees_ix02 사용)

show session status like 'Sort%' ; 

-- Sort_merge_passes 0

-- Sort_range 1

-- Sort_rows 754 -- > (+10) 

-- Sort_scan 77 

메모장 또는 아크로에딧으로 열기 

sqlplus sys/sys as sysdba

col name for a34

col value for a15

col descrition for a30

col is_default for a15

col ses_modifiable for a15

col sys_modifiable for a15

select ksppinm name, ksppstvl value, ksppstdf is_default, decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable, decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable, ksppdesc descrition from sys.x$ksppi i, sys.x$ksppcv v

where i.indx = v.indx and i.ksppinm like '%&1%';

또는 

select ksppinm name,
       ksppstvl value,
       ksppstdf is_default
from   sys.x$ksppi i, sys.x$ksppcv v
where i.indx = v.indx 
and ksppinm like '%query_execution%';

mysql 모니터링 중 innoDB Buffer Usage가 100%를 유지하는 현상이 발생

아래 공식대로 계산 후  innodb_buffer_pool_size 를 변경

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data'; -- IBPDataPages SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages

IBPctFull이 95%이상일 경우 DB 서버 RAM의 75%까지 innodb_buffer_pool_size를 설정.

 IBPctFull이 95%이하일 경우 IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05 의 계산 값을 innodb_buffer_pool_size로 설정.

[참조] - http://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why

출처: https://gihyun.com/16 [Note]

현재rds 사양기준으로 r5 2X large
aws 자동계산

64GB / 46GB -> 71.8% 할당
innodb_buffer_pool_size 사용률 --> 98.7%

innodb_buffer_pool_size 적정한 사용률은  ?

적정사용률을 유지하기 위해 

인스턴스사양을 한단계 늘리는것과 
innodb_buffer_pool_size 를 75% 이상이 되도록 수동으로 설정하는 것 중 권장되는 방법은 무었일까?

-- inodb_buffer_pool_size 계산방법
show global status like 'Innodb_buffer_pool_pages_data'; -- IBPDataPages  2976794
show global status like 'Innodb_buffer_pool_pages_total';-- IBPTotalPages 3014656
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS 16384

사용률 95% 이상인경우 : 전체DB서버 RAM의  75% 유지하도록 Innodb_page_size 할당 

select 2976794 * 100.0 / 3014656 as IBPPctFull from dual ; -- 98.7 %  

show global variables like 'innodb_buffer%' ;  -- 46GB (기존) 71.8% 할당된상태 

사이즈변경함 : Innodb_page_size (53392123904)   49.7GB 할당   --> 사용률  98.7 %  --> 90.8% 유지

사용률 95% 이하인경우

select 16384 * 2976794 / (1024*1024*1024) * 1.05 ; -- Innodb_page_size 할당  47.69