꿈꾸는 DB 전문가

bstar36.tistory.com/293

minsql.com/mysql/MySQL-slow-query-%EB%A5%BC-%ED%9A%A8%EC%9C%A8%EC%A0%81%EC%9C%BC%EB%A1%9C-%EC%9D%B4%EC%9A%A9%ED%95%B4%EB%B3%B4%EC%9E%90/

https://m.blog.naver.com/jevida/221239783586

thefunky-monkey.com/page-364/centos-7-mysql-8-0/

> wget -O /usr/bin/mysqltuner https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
> chmod 755 /usr/bin/mysqltuner




> mysqltuner
 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[!!] Currently running unsupported MySQL version 10.0.12-MariaDB-1~wheezy-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 166M (Tables: 84)
[--] Data in InnoDB tables: 763M (Tables: 293)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 49

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 2h 43m 31s (37M q [47.313 qps], 291K conn, TX: 42B, RX: 6B)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 1.3G global + 23.3M per thread (100 max threads)
[OK] Maximum possible memory usage: 3.6G (11% of installed RAM)
[OK] Slow queries: 0% (325/37M)
[OK] Highest usage of available connections: 33% (33/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/35.9M
[OK] Key buffer hit rate: 97.9% (22M cached / 464K reads)
[OK] Query cache efficiency: 36.3% (15M cached / 42M selects)
[!!] Query cache prunes per day: 127508
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 253K sorts)
[!!] Joins performed without indexes: 3127
[OK] Temporary tables created on disk: 15% (133K on disk / 851K total)
[OK] Thread cache hit rate: 99% (33 created / 291K connections)
[!!] Table cache hit rate: 0% (457 open / 2M opened)
[OK] Open file limit used: 22% (229/1K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[OK] InnoDB buffer pool / data size: 768.0M/763.6M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 384M) [see warning above]
    join_buffer_size (> 16.0M, or always use indexes with joins)
    table_open_cache (> 457)

hunjang.tistory.com/16

[AWS] EC2 Centos MobaXterm (SSH) 접속 방법, login as 값

출처: https://gethlemn.tistory.com/12 [Solution developer]

MySQL / MariaDB  root 비밀번호 설정

WorkBench EC2 MaraDB 외부접속
출처: https://jogoon87.tistory.com/entry/MySQL-Workbench로-AWS-MariaDB-연결-설정 [Jo's story]

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