꿈꾸는 DB 전문가

select * from statements_with_full_table_scans ;

select * from sys.schema_unused_indexes

select * from schema_redundant_indexes

CPU 사용률

CPU가 처리해 하는 작업이 얼마나 쌓여 있는지 확인 - 대략적인 서버 부하가 어느정도인지 판단.

shll> uptime

- load average 확인 

shell> vmstat 1

- procs -r : 프로세스 큐에서 CPU가 실행주기를 기다리는 프로세스의 수

- procs -b : 지정된 이벤트가 발생하기 전까지는 실행될 수 없는 프로세스의 수

-cpu -us : 사용자 프로세스를 위해 사용한 CPU 사용률로 application의 코드가 사용한 CPU 시간 

   (대표적으로 정렬, group by , 압축 )

-cpu -sy : 커널이 시용한 CPU 시간

-cpu -id : CPU가 아무것도 하지 않고 대기(idle) 하는데 사용한 CPU 사용률. 아무런 작업도 하지않는 장비에서 이 값이 높게 나타남

cpu -wa : 디스크 입출력을 기다리는 데(wait) 사용한 CPU 사용률 . 주로 DBMS서버가 실행중인 장비에서 관심을 갖고 봐야함.  "wa(wait I/O)" 값이 높으면 디스크IO가 과도하게 많이 발생해 CPU가 데이터의 입출력을 기다리는 시간이 많다는 것을  의미.

cpu- st : 가상 머신으로부터 빼앗긴 시간을 의미 : 크게 신경쓰지 않아도 됨.

메모리 사용률

가장 중요한 변수는  InnoDB 버퍼 풀과 스레드 캐시!

-- MySQL 전체 상태 조회
SHOW GLOBAL STATUS ;

show global variables like 'tmp_table_size';
show global variables like 'innodb_buffer_pool_size';
show global variables like 'innoDB_flush_method';
show global variables like 'tmp_table_size';
show global variables like 'max_heap_table_size';
show global variables like 'query_cache_size';
show global variables like 'query_cache_type';
show global variables like 'query_cache_limit';
show global variables like 'table_open_cache';
show global variables like 'opened_tables' ;
show global variables like 'innodb_adaptive_hash_index';
show global variables like 'innodb_thread_concurrency';
show global variables like 'innodb_read_io_threads';
show global variables like 'innodb_write_io_threads';
-- show global variables like 'innodb_spin_wait_delay';  -> 0
-- show global variables like 'innodb_buffer_pool_instances'; -> 4


-- The thread cache
SHOW GLOBAL STATUS LIKE 'Connections'; -- 16
SHOW GLOBAL STATUS LIKE 'Threads_created';  -- 4
SHOW GLOBAL STATUS LIKE 'Max_used_connections';  -- 4

-- Threads_created/Connections
select 4 / 16 ;  -- 0.2500  > 0.01 이면  thread_cache_size 증가필요  --> Max_used_connections 보다 커야함
show global variables like 'thread_cache_size';

innodb_flush_method=O_DIRECT쓸 때 이중 버퍼를 피하기 위해 사용 합니다.
innodb_buffer_pool_size전체 InnoDB 데이터를 메모리에로드하도록 설정  하면 디스크에서 읽는 것을 방지 할 수 있습니다.
innodb_log_file_size너무 높게 만들지 마십시오 . 더 빠른 디스크를 사용하면 종종 플러시가 나쁘지 않으며 서버에서 충돌하는 동안 복구 시간이 단축됩니다.
다음 두 변수를 혼합하지 마십시오 : innodb_thread_concurrency및 thread_concurrency. 이것은 MySQL 성능에 더 나은 영향을 미칩니다.
단지 할당  max_connections. 너무 많은 연결을 사용하면 MySQL이 많은 RAM을 사용하고 불필요하게 MySQL 서버 속도를 늦출 수 있습니다.
thread_cache비교적 높은 설정을 유지하십시오 . 연결을 열 때 속도가 느려지는 것을 방지하려면 최소 16 개가 충분해야합니다.
skip-name-resolve DNS 조회를 제거하는 데 사용 합니다.
쿼리가 자주 반환되고 데이터가 너무 많이 변경되지 않는 경우 쿼리 캐시를 사용합니다.
또 다른 해결책은 temp_table_size디스크 쓰기를 방지하기 위해 늘리는 것입니다.
max_heap_table_size 을 늘리면 디스크 쓰기가 방지됩니다.
sort_buffer_size이 변수는 연결별로 구성되고 모든 메모리를 매우 빠르게 사용할 수 있으므로 너무 높게 설정하지 마십시오 .
설정  innodb_flush_log_at_trx_commit = 0은 성능을 향상시킬 수 있습니다.



--문자열타입 스키마체크
select table_schema,  table_name, column_name, column_type,
     , character_set_name, collation_name
from information_schema.columns
where table_schema='employee' and column_name ='%dept_no%' ;

-- 숫자타입의 스키마체크
select table_schema,  table_name, column_name
     , column_type, character_set_name, collation_name
from information_schema.columns
where table_schema='sakila' 
and column_name like '%film_id%' ; 

Slow queries

SHOW GLOBAL VARIABLES LIKE '%SLOW_QUERY%';

SET GLOBAL SLOW_QUERY_LOG=1;

SHOW GLOBAL VARIABLES LIKE '%LONG_QUERY%';

SET GLOBAL LONG_QUERY_TIME=1;

(/etc/mysql/my.cnf)

[mysqld]

slow_query_log=1

slow_query_log_file = /var/log/mysql/mariadb-slow.log

long_query_time=1

Profiling

SET PROFILING=1;

• Block I/O

• Context switches

• CPU

• IPC

• Memory

• Page faults

• Source

• Swaps

• All

SHOW PROFILES;

MariaDB [none]> SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.30798532 | select * from s_explain |
|        2 | 0.25341312 | select * from s_explain |
+----------+------------+-------------------------+

MariaDB [none]> SHOW PROFILE CPU[ALL] FOR QUERY 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000034 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000009 | 0.000000 |   0.000000 |
| init                           | 0.000008 | 0.000000 |   0.000000 |
[...]
| init                           | 0.000016 | 0.000000 |   0.000000 |
| optimizing                     | 0.000011 | 0.000000 |   0.000000 |
| statistics                     | 0.000050 | 0.000000 |   0.000000 |
| preparing                      | 0.000017 | 0.000000 |   0.000000 |
| executing                      | 0.000008 | 0.000000 |   0.000000 |
| Sending data                   | 0.007369 | 0.004001 |   0.000000 |
| Waiting for query cache lock   | 0.000020 | 0.000000 |   0.000000 |
| Sending data                   | 0.003420 | 0.004000 |   0.000000 |
[...]
| Sending data                   | 0.271156 | 0.272017 |   0.000000 |
| end                            | 0.000020 | 0.000000 |   0.000000 |
| query end                      | 0.000010 | 0.000000 |   0.000000 |
| closing tables                 | 0.000015 | 0.000000 |   0.000000 |
| freeing items                  | 0.000009 | 0.000000 |   0.000000 |
| updating status                | 0.000041 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000029 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+

Performance schema

performance_schema=on

SHOW VARIABLES LIKE 'performance_schema';

MariaDB [(none)]> USE PERFORMANCE_SCHEMA;

MariaDB [performance_schema]> show tables;

User statistics

MariaDB [(none)]> SET GLOBAL userstat=1;

[mysqld]
userstat = 1

MariaDB [(none)]> FLUSH TABLE_STATISTICS
MariaDB [(none)]> FLUSH INDEX_STATISTICS
MariaDB [(none)]> FLUSH USER_STATISTICS
MariaDB [(none)]> FLUSH CLIENT_STATISTICS
MariaDB [(none)]> SHOW CLIENT_STATISTICS
MariaDB [(none)]> SHOW USER_STATISTICS
MariaDB [(none)]> SHOW INDEX_STATISTICS
MariaDB [(none)]> SHOW TABLE_STATISTICS

Sysbench

-fileio : 파일 I / O 테스트를 수행합니다.

-cpu : CPU 성능 테스트를 수행합니다.

-메모리 : 메모리 기능 속도 테스트를 수행합니다.

-스레드 : 스레드 하위 시스템 성능 테스트를 수행합니다.

-뮤텍스 : 뮤텍스 성능 테스트를 수행합니다.

-oltp : OLTP 테스트를 수행합니다.

설치명령어 

aptitude install sysbench

Performance Optimizations

[mysqld]
skip_name_resolve

SELECT USER,HOST FROM mysql.user;

+------------------+-----------+

| USER | HOST |

+------------------+-----------+

| root | 127.0.0.1 |

| root | ::1 |

| debian-sys-maint | localhost |

| root | localhost |

| root | mariadb |

+------------------+-----------+

Maximum connections

[mysqld]
max_connections = 200

select @@global.max_connections;

SET GLOBAL max_connections=1000;

The binlogs cache

show global status like 'Binlog_cache%';

Binlog_cache_use : 최대 성능을 얻으려면이 값이 커야합니다. 캐시에 기록 된 트랜잭션 수를 나타냅니다. 그래서 그것이 커진다면 그것은 캐시가 사용된다는 것을 의미하고 이것은 좋습니다.

Binlog_cache_disk_use :이 값은 0으로 유지되어야합니다.이 값이 커지면 트랜잭션이 너무 커서 캐시에 들어갈 수 없음 (32K 이상)을 의미합니다. 따라서 디스크를 사용해야하므로 성능이 저하됩니다.

동적변경

SET global binlog_cache_size=64*1024;

영구변경

[mysqld]

binlog_cache_size = 64k

주의: 4G초과하지 말것

비트랜잭션 캐시 동적변경

SET global binlog_stmt_cache_size=64*1024;

비트랜잭션 캐시 영구변경

[mysqld] binlog_stmt_cache_size=64k;

Temporary tables

show global status like 'Created_tmp%';

Created_tmp_disk_tables :이 값은 데이터베이스에서 BLOB 또는 TEXT 열을 가져 오는 대신 0으로 유지되어야합니다. 인 메모리 테이블이 너무 커질 때 사용됩니다.

Created_tmp_files : 생성 된 임시 파일 수입니다. Created_tmp_disk_tables가 충분하지 않으면 디스크 파일이 생성되고 카운터가 증가합니다.

Created_tmp_tables : 생성 된 내부 임시 테이블의 수입니다.

Created_tmp_disk_tables 값이 너무 커지면 tmp_table_size 또는 max_heap_table_size 값을 조정해야합니다. 

최대 Created_tmp_disk_tables 크기는 최소 tmp_table_size입니다.

BLOB 또는 TEXT 열이 절대적으로 필요하고 더 나은 성능을 원한다면 임시 디렉토리로 tmpfs를 고려하는 것이 좋습니다.

show global variables like 'tmp_table_size';

show global variables like 'max_heap_table_size';

The default value is 32 M  -> 크기를 늘리고 싶다면.

[mysqld]

tmp_table_size = 64M

max_heap_table_size = 64M

동적변경 적용

MariaDB [(none)]> SET global tmp_table_size=64*1024*1024;

MariaDB [(none)]> SET global max_heap_table _size=64*1024*1024

Open tables

SHOW global STATUS LIKE 'Open%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| Open_files | 288 |

| Open_streams | 0 |

| Open_table_definitions| 329 |

| Open_tables | 222 |

| Opened_files | 767 |

| Opened_table_definitions | 296 |

| Opened_tables | 525 |

| Opened_views | 0 |

+--------------------------+-------+

select @@table_open_cache;

+--------------------+

| @@table_open_cache |

+--------------------+

| 400 |

+--------------------+

[mysqld]

table_open_cache = 1024

table_open_cache 값을 4096보다 크게 설정하지 말것. 그렇지 않으면 성능이 저하 될 수 있음

[mysqld]

table_definition_cache = 16384

table_definition_cache 는 테이블 열기속도를 높임 

cache size 를 높이는 테이블 수가 너무 많은 경우 이 값을 증가 시켜야함 (default 400) 

The query cache

SHOW VARIABLES LIKE 'query_cache_type';

[mysqld]
query_cache_type = 1

0 / disabled : 캐시 된 쿼리가 없습니다.

1 / 활성화 됨 : SQL_NO_CACHE 절을 포함하는 쿼리 만이 아닌 모든 쿼리가 캐시됩니다.

2 / demand : SQL_CACHE 절을 포함하는 쿼리 만 캐시되지 않고 캐시되지 않습니다.

쿼리캐쉬값들...

SHOW global STATUS LIKE 'Qc%';

Qcache_not_cached : 캐시되지 않은 쿼리를 나타냅니다. 이 값은 쿼리를 강제로 캐시하도록 query_cache_type 값을 변경했는지 또는 쿼리를 캐시 할 수 없기 때문에 Qcache를 비활성화했는지 여부를 아는 데 중요합니다.

cache_hits 값이 Qcache_inserts보다 작 으면 대부분의 경우 쿼리 캐시를 비활성화하는 것을 고려해야합니다. 하나의 쿼리가 쿼리 캐시의 100 %를 차지하는 경우에는 해당되지 않으며이를 제거하면 성능이 저하 될 수 있습니다.

ache_free_memory : 쿼리 (SQL 요청 및 결과 쿼리)를 저장하는 데 사용 가능한 메모리입니다.

Qcache_hits :이 값은 캐시가 적중 된 횟수에 해당합니다.

Qcache_inserts : 캐시에 들어간 쿼리의 수입니다. 올바른 성능을 얻고 Qcache가 잘 사용되는지 확인하려면 Qcache_hits 값이 Qcache_inserts 값보다 커야합니다.

Qcache_lowmem_prunes : 캐시에 충분한 공간이 없으면 가장 오래된 요청이 새 요청으로 대체됩니다.

Qcache_not_cached : 캐시되지 않은 쿼리를 나타냅니다. 이 값은 쿼리를 강제로 캐시하도록 query_cache_type 값을 변경했는지 또는 쿼리를 캐시 할 수 없기 때문에 Qcache를 비활성화했는지 여부를 아는 데 중요합니다.

cache_not_cached 값이 Qcache_hits 및 Qcache_inserts 값을 추가 한 것보다 작 으면 쿼리 캐시 크기와 쿼리 당 캐시 크기를 늘리십시오. 그런 다음 Qcache_not_cached가 계속 증가하면 쿼리 캐시를 비활성화합니다.

Qcache_lowmem_prunes 값이 증가하면 Qcache에 더 많은 메모리를 추가하십시오.

Modifying the query cache

SHOW global VARIABLES LIKE 'query_cache%';  -- current set memory

The important new information

query_cache_size : 이것은 Qcache의 전용 메모리 크기입니다. 여기에는 64MB가 있습니다.
query_cache_limit : 쿼리 당 최대 캐시 크기입니다.

쿼리 당 2메가바이트 및 쿼리 캐시 128 MB의 비행에 이러한 설정을 변경하려면, 다음 명령을 사용합니다 :

MariaDB [(none)]> SET global query_cache_limit = 2*1024*1024;
MariaDB [(none)]> SET global query_cache_size = 128*1024*1024;

주의 : 성능 문제를 방지하려면 256MB 이상의 쿼리 캐시를 사용하지 않아야합니다.

[mysqld]
query_cache_limit = 2M
query_cache_size = 128M

Optimizing storage engines

To get your database, index, and table sizes

MariaDB [(none)]>

SELECT TABLE_SCHEMA, ENGINE, SUM(TABLE_ROWS), SUM(DATA_LENGTH), SUM(INDEX_LENGTH)

FROM INFORMATION_SCHEMA.TABLES

GROUP BY ENGINE,TABLE_SCHEMA

ORDER BY TABLE_SCHEMA ;

SUM (DATA_LENGTH) : 인덱스가없는 데이터 크기
SUM (INDEX_LENGTH) : 데이터없는 인덱스 크기

팁
이러한 종류의 쿼리를 실행하면 리소스가 많이 소모됩니다. 

테이블 수가 많은 경우 몇 분 동안 서버가 정지 될 수 있습니다. 

테이블이 10,000 개 있으면 10 분,

 innodb_stats_on_metadata를 비활성화하면 1 분을 쉽게 계산할 수 있습니다.

MariaDB [(none)]> SET innodb_stats_on_metadata = 0;

InnoDB/XtraDB

그 성능에는 비용이 듭니다! 비용은 데이터베이스 크기보다 사용 가능한 충분한 RAM이 있어야한다는 것입니다. 인덱스도 캐시되기 때문입니다. InnoDB의 일반적인 값은 다음 표에 나와 있습니다.

Pool size and statistics

엔진을 위해 예약해야하는 RAM 양을 정확히 알 수있는 가장 좋은 방법은 

다음 명령을 실행하여 엔진 통계를 표시하는 것

MariaDB [(none)]> SHOW engine innodb STATUS\G;
*************************** 1. row ***************************
  Type: InnoDB
[...]
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 826540032; in additional pool allocated 0
Total memory allocated by read views 112
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 18423648   (12750568 + 5673080)
    Page hash           797656 (buffer pool 0 only)
    Dictionary cache    4132069   (3189008 + 943061)
    File system         171664   (82672 + 88992)
    Lock system         1993392   (1993016 + 376)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 943061
Buffer pool size        49151
Buffer pool size, bytes 805289984
Free buffers            18830
Database pages          29975
Old database pages      11052
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 50, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 28131, created 1844, written 281524
0.00 reads/s, 0.00 creates/s, 16.12 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 29975, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
[...]
1 row in set (0.00 sec)

• Buffer pool size: 49151 * 16 * 1024 = 768 MB

• Free buffers: 18830 * 16 * 1024 = 294 MB

• 위 결과는 충분함. 만약 충분하지 않다면 더 크게 설정할 수 있음

[mysqld]

innodb_buffer_pool_size = 1024M ( 인스턴스 재시작 필요)

Redo logs

리두로그 크기 변경시 다음 순서대로 해야함(오류방지를 위해)

 
Change the size of the redo logs per file (2 by default) in your configuration file:

Copy
[mysqld]
innodb_log_file_size = 512M
Force the syncing data to the redo logs:

Copy
MariaDB [(none)]> SET GLOBAL innodb_fast_shutdown = 0; 
Stop the MariaDB instance:

Copy
service mysql stop
Remove the current redo logs:

Copy
rm -f /var/lib/mysql/ib_logfile*
Start MariaDB:

Copy
service mysql start

Transaction commits and logs

innodb_flush_log_at_trx_commit  성능에 영향을 준다

SHOW SESSION VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

1: 가장 안전함 그러나 쓸때마다 동기화 -> 오버헤드 발생할 수 있음

2: 초당 한번 디스크에 쓴다 - 덜 안전 -> 하지만 복제를 사용하는 경우 문제가 되지 않음

3: 가장 빠름 -> 데이터가 손실되어도 되는경우 사용 -> 가장위험 -> 따라서 레플리케이션(슬레이브)에만 사용할것

[mysqld]

innodb_flush_log_at_trx_commit = 2

sync_binlog=0

sync_binlog : 1 -> 가장 안전 

sync_binlog : 0 -> 성능에 가장 좋음 ->  배터리 보호 기능이있는 RAID 카드를 얻는 것이 좋다.

sync_binlog value값 확인

SHOW SESSION VARIABLES LIKE 'sync_binlog';

즉시변경을 원할 경우

MariaDB [(none)]> SET global sync_binlog=0;
MariaDB [(none)]> SET GLOBAL innodb_flush_log_at_trx_commit=2;

마지막으로 중요한 옵션은 innodb_max_dirty_pages_pct입니다. 

설정 한 InnoDB / XtraDB 버퍼 풀 크기에 포함 된 최대 더티 페이지의 백분율입니다.

예를 들어 버퍼 풀 크기를 100GB로 설정하고 리두 로그를 2GB로 설정 한 경우 기본적으로 RAM에서 수정 된 데이터의 75 % 만 허용됩니다. 그러나 리두 로그 (ib_logfile) 크기는 2GB에 불과합니다! innodb_max_dirty_pages_pct의 백분율을 줄이면 InnoDB / XtraDB가 더 자주 플러시되고 시스템 충돌로부터 더 잘 보호됩니다.

[mysqld]
innodb_io_capacity = 1000
innodb_max_dirty_pages_pct = 85

Buffer pool instances

인스턴스 크기 = innodb_buffer_pool_size / innodb_buffer_pool_instance

좋은 성능을 얻으려면 인스턴스 당 1GB보다 작아서는 안됩니다.

현재 값 확인

MariaDB [(none)]> show variables like 'innodb_buffer_pool_instances';

영구적으로 변경시

[mysqld]
innodb_buffer_pool_instances = 4

The flush method

데이터와 로그가 디스크로 플러시되는 방식을 제어

RAID 인경우 가능 

파일 시스템 캐시 (낭비) 이노 캐시 캐시 간의 이중 버퍼링을 방지하게된다.

MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'innodb_flush_method';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| innodb_flush_method | O_DIRECT |
+---------------------+----------+
1 row in set (0.00 sec)

영구적으로 변경시

[mysqld]

innodb_flush_method = O_DIRECT (재시작 필요)

RAID 카드가없는 경우 다른 가능성은 값을 O_DSYNC로 설정하는 것입니다. 

두 시나리오를 모두 테스트하고 하드웨어에 따라 최상의 솔루션을 선택하는 것이 좋습니다.

mysqltuner 설명

weplaythedb.tistory.com/49

애플리케이션 속도가 느리고 데이터베이스로 인한 경우 다음 사항을 확인해야합니다.

긴 요청 (느린 쿼리)을 찾아서 Explain 명령을 사용하여 최적화하십시오.

좋은 엔진을 사용하고 있고 올바르게 조정했는지 확인하십시오.

필요한 경우에만 인덱스를 사용하고 있는지 확인

하드웨어가 고성능을 위해 올바르게 조정되었는지 확인하십시오.

이 모든 단계가 괜찮다면 다음을 진행하는 것을 고려해야합니다.

MySQL/MariaDB, 슬로우 쿼리 원인 분석 및 성능 튜닝하기

출처 참고사이트 : jsonobject.tistory.com/408

출처 참고사이트 : m.blog.naver.com/islove8587/221977641268

https://sarc.io/index.php/mariadb/1768-mysql-8-0-18-hash-join

https://mariadb.com/kb/en/optimizing-table_open_cache/

1. 운영체제 부분

2. MySQL 부분

운영체제 요소 -  씨 메 디 네 (CPU , Memory, Disk, Network ) 사용량

MySQL 요소 -

쿼리 실행량 QPS / QPM

커넥션 - 최대커넥션 , 실행중인 커넥션, 캐싱된 커넥션 수

쓰레드 - 최대쓰레드 캐시 수 , 사용중인 쓰레드 개수, 캐싱된 쓰레드 개수  -> 락경합이나 과도한 실행시간(헤비쿼리)

참고 : 접속주체가 자체적인 커넥션풀을 사용하거나 1개의 커넥션당 다수의 쓰레드를 사용하는 환경에서 커넥션풀을 제대로 관리하지 않으면 트랜잭션 관련문제가 더 많이 발생할 수 있음

커넥션 - 클라이언트와 MySQL간 작업요청 / 결과전달이 이루어지는 연결

쓰레드 - 요청된 작업을 처리하는 주체, 쓰레드풀로부터 매핑

락 - 모든 DB에서 반드시 모니터링해야 하는 요소  -락 획득을 위한 대기시간 길어지는 현상- mysql 서버 자체보다

애플리케이션 로직문제일 가능성이 높다

락 -> 트랜잭션 보장

임시테이블 사용량

임시테이블 생성조건 

-MySQL 내부적 생성  : group by, order by, join, union , subquery 처리

-사용자 정의 생성 

5.슬로우쿼리 - long query time 을 넘는 실행시간을 소요하느 쿼리 

 
set @rownum := 0;

insert into indextest 
select s.*
from (
		select @rownum = @rownum + 1 rownum,  customer_id,  inventory_id, rental_date, last_update, 0, staff_id 
		from sakila.rental ) s , (select 1 from performance_schema.global_variables limit 10 ) v, (select @rownum := 0) temp 
limit 2000000        ;

안녕하세요.오늘의 내용은  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 

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