[MySQL] 메모리 사용 최적화

■ MySQL이 메모리를 사용하는 방법

MySQL은 데이터베이스 작업 성능을 향상시키기 위해 버퍼와 캐시를 할당합니다. 기본 구성은 약 512MB의 RAM이 있는 가상 머신에서 MySQL 서버를 시작하도록 설계되었습니다. 특정 캐시 및 버퍼 관련 시스템 변수의 값을 증가시켜 MySQL 성능을 향상시킬 수 있습니다. 메모리가 제한된 시스템에서 MySQL을 실행하도록 기본 구성을 수정할수도 있습니다.

 

다음 목록은 MySQL이 메모리를 사용하는 몇 가지 방법을 설명합니다. 해당되는 경우 관련 시스템 변수가 참조됩니다. 일부 항목은 스토리지엔진 또는 기능에 따라 다릅니다.

+ InnoDB 버퍼풀은 테이블, 인덱스 및 기타 보조 버퍼에 대해 캐시된 InnoDB 데이터를 보유하는 메모리 영역입니다. 대량 읽기 조작의 효율성을 위해 버퍼풀은 여러 행을 보유할 수 있는 페이지로 분할됩니다. 캐시 관리 효율성을 위해 버퍼풀은 링크된 페이지 목록으로 구현됩니다. 거의 사용되지 않는 데이터는 LRU알고리즘의 변형을 사용하여 캐시에서 만료됩니다. 버퍼 풀의 크기는 시스템 성능에 중요합니다.

- InnoDB는 malloc() 작업을 사용하여 서버 시작시 전체 버퍼풀에 메모리를 할당합니다. innodb_buffer_pool_size 시스템 변수는 버퍼풀 크기를 정의합니다. 일반적으로 권장되는 innodb_buffer_pool_size 값은 시스템 메모리의 50-75 %입니다. 서버가 실행되는 동안 innodb_buffer_pool_size를 동적으로 구성할 수 있습니다.

- 메모리가 많은 시스템에서는 버퍼 풀을 여러 버퍼 풀 인스턴스로 나누어 동시성을 향상시킬 수 있습니다. innodb_buffer_pool_instances 시스템 변수는 버퍼풀 인스턴스 수를 정의합니다.

- 버퍼 풀이 너무 작으면 페이지가 버퍼 풀에서 플러시되어 잠시 후에 다시 필요하므로 과도한 I/O소비가 발생할 수 있습니다.

- 버퍼 풀이 너무 크면 메모리 경쟁으로 인해 스와핑이 발생할 수 있습니다.

 

+ 모든 스레드는 MyISAM키 버퍼를 공유합니다. key_buffer_size 시스템 변수는 크기를 결정합니다.

서버가 여는 각 MyISAM 테이블에 대해 인덱스 파일이 한 번 열립니다. 데이터 파일은 테이블에 액세스하는 동시에 실행중인 각 스레드마다 한 번씩 열립니다. 각 동시 스레드에 대해 테이블 구조, 각 컬럼의 컬럼 구조 및 크기 3 * N의 버퍼가 할당됩니다 (여기서 N은 BLOB 컬럼을 계산하지 않은 최대 행 길이 입니다). BLOB 열에는 5-8 바이트와 BLOB 데이터 길이가 필요합니다. MyISAM 스토리지 엔진은 내부용으로 하나의 추가 행 버퍼를 유지합니다.

 

+ myisam_use_mmap 시스템 변수는 1로 설정하여 모든 MyISAM 테이블에 대해 메모리 매핑을 활성화 할 수 있습니다.

 

+ 내부 메모리 임시 테이블이 너무 커지면 (tmp_table_size 및 max_heap_table_size 시스템 변수를 사용하여 크기를 설정한 경우) MySQL은 자동으로 테이블을 메모리 내에서 디스크 내 형식으로 변환합니다. 온 디스크 임시 테이블은 internal_tmp_disk_storage_engine 시스템 변수로 정의된 스토리지 엔진을 사용합니다. 필요하면 허용된 크기 안에서 가능한 임시 테이블 크기를 늘릴 수 있습니다.

CREATE TABLE을 사용하여 명시적으로 작성된 MEMORY 테이블의 경우 max_heap_table_size 시스템 변수만으로 테이블이 커질 수 있는 크기가 결정되며 디스크 내장 형식으로 변환되지 않습니다.

 

+ MySQL성능 스키마는 낮은 수준에서 MySQL 서버 실행을 모니터링하는 기능입니다. 성능 스키마는 서버를 시작하는 동안 필요한 메모리를 할당하는 대신 메모리 사용을 실제 서버로드로 조정하여 메모리를 점진적으로 할당합니다. 메모리가 할당되면 서버가 다시 시작될 때까지 해제되지 않습니다.

 

+ 서버가 클라이언트 연결을 관리하는데 사용하는 각 스레드에는 스레드별 공간이 필요합니다. 다음 목록은 이러한 변수와 크기를 제어하는 ​​시스템 변수를 나타냅니다.

- 스택 (thread_stack)

- 연결 버퍼 (net_buffer_length)

- 결과 버퍼 (net_buffer_length)

연결 버퍼 및 결과 버퍼는 각각 net_buffer_length 바이트와 동일한 크기로 시작하지만 필요에 따라 max_allowed_packet바이트까지 동적으로 확장됩니다. 결과 버퍼는 각 SQL 문 다음에 net_buffer_length 바이트로 축소됩니다. 명령문이 실행되는 동안 현재 명령문 문자열의 사본도 할당됩니다.

각 연결 스레드는 명령문 다이제스트를 계산하기 위해 메모리를 사용합니다. 서버는 세션 당 max_digest_length 바이트를 할당합니다.

 

+ 모든 스레드는 동일한 기본 메모리를 공유합니다.

 

+ 스레드가 더 이상 필요하지 않으면 스레드에 할당된 메모리가 해제되고 스레드가 스레드 캐시로 다시 들어가지 않는 한 시스템으로 반환됩니다. 이 경우 메모리는 할당된 상태로 유지됩니다.

 

+ 테이블의 순차적 스캔을 수행하는 각 요청은 읽기 버퍼를 할당합니다. read_buffer_size 시스템 변수는 버퍼 크기를 결정합니다.

 

+ 임의의 순서로 행을 읽을 때 (예를 들어, 정렬 후), 디스크 탐색을 피하기 위해 임의 읽기 버퍼가 할당될 수 있습니다. read_rnd_buffer_size 시스템 변수는 버퍼 크기를 결정합니다.

 

+ 모든 조인은 단일 패스로 실행되며 임시 테이블을 사용하지 않고도 대부분의 조인을 수행 할 수 있습니다. 대부분의 임시 테이블은 메모리 기반 해시 테이블입니다. 행 길이가 길거나 (모든 열 길이의 합계로 계산) BLOB 열을 포함하는 임시 테이블은 디스크에 저장됩니다.

 

+ 정렬을 수행하는 대부분의 요청은 결과 세트 크기에 따라 정렬 버퍼와 0-2 개의 임시 파일을 할당합니다.

 

+ 거의 모든 구문 분석 및 계산은 스레드 로컬 및 재사용 가능한 메모리 풀에서 수행됩니다. 작은 항목에는 메모리 오버 헤드가 필요하지 않으므로 일반적인 느린 메모리 할당을 피하고 해제합니다. 메모리는 예기치 않게 큰 문자열에만 할당됩니다.

 

+ BLOB 컬럼이있는 각 테이블에 대해 큰 BLOB 값을 읽을 수 있도록 버퍼가 동적으로 확장됩니다. 테이블을 스캔하면 버퍼는 가장 큰 BLOB 값만큼 커집니다.

 

+ MySQL에는 테이블 캐시를위한 메모리와 디스크립터가 필요합니다. 모두 사용중인 테이블 처리기의 구조는 테이블 캐시에 저장되고 "선입 선출 (FIFO)"로 관리됩니다. table_open_cache 시스템 변수는 초기 테이블 캐시 크기를 정의합니다.

MySQL에는 테이블 정의 캐시를 위한 메모리도 필요합니다. table_definition_cache 시스템 변수는 테이블 정의 캐시에 저장할 수 있는 테이블 정의 수(.frm 파일에서)를 정의합니다. 많은 수의 테이블을 사용하는 경우 테이블을 빠르게 여는 큰 테이블 정의 캐시를 작성할 수 있습니다. 테이블 정의 캐시는 테이블 캐시와 달리 공간을 덜 차지하고 파일 설명자를 사용하지 않습니다.

 

+ FLUSH TABLES 문 또는 mysqladmin flush-tables 명령은 한 번에 사용하지 않는 모든 테이블을 닫고 현재 실행중인 스레드가 완료될 때 사용중인 모든 테이블이 닫히도록 표시합니다. 이렇게하면 대부분의 사용중인 메모리가 효과적으로 해제됩니다. FLUSH TABLES는 모든 테이블이 닫힐 때까지 반환되지 않습니다.

 

+ 서버는 GRANT, CREATE USER, CREATE SERVER 및 INSTALL PLUGIN 문의 결과로 메모리에 정보를 캐시합니다. 이 메모리는 해당 REVOKE, DROP USER, DROP SERVER 및 UNINSTALL PLUGIN 문에 의해 해제되지 않으므로 캐싱을 유발하는 명령문의 많은 인스턴스를 실행하는 서버의 경우 FLUSH PRIVILEGES로 캐시 메모리 점유를 해제시키지 않으면 캐시된 메모리 사용이 증가합니다.

 

 

ps 및 기타 시스템 상태 프로그램은 mysqld가 많은 메모리를 사용한다고 보고 할 수 있습니다. 다른 메모리 주소의 스레드 스택으로 인해 발생할 수 있습니다. 예를 들어, Solaris 버전의 ps는 사용되지 않은 메모리를 스택간에 사용된 메모리로 계산합니다. 이를 확인하려면 swap -s로 사용 가능한 스왑을 확인합니다. 여러 메모리 누수 감지기 (상업용 및 공개 소스)로 mysqld를 테스트하므로 메모리 누수가 없어야 합니다.

 

 

 

▶︎ MySQL 메모리 사용량 모니터링

다음 예제는 성능 스키마 및 sys 스키마를 사용하여 MySQL 메모리 사용을 모니터링하는 방법을 보여줍니다.

 

대부분의 성능 스키마 메모리 계측은 기본적으로 비활성화되어 있습니다. Performance Schema setup_instruments 테이블의 ENABLED 열을 업데이트하여 계측기를 사용할 수 있습니다. 메모리 계측기는 memory / code_area / instrument_name 형식의 이름을 가지며, 여기서 code_area는 sql 또는 innodb와 같은 값이고 instrument_name은 계측기 세부 사항입니다.

 

1. 사용 가능한 MySQL 메모리 인스트루먼트를 보려면 Performance Schema setup_instruments 테이블을 쿼리합니다. 다음 쿼리는 모든 코드 영역에 대해 수백 개의 메모리 장치를 반환합니다.

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory%';

코드 영역을 지정하여 결과를 좁힐 수 있습니다. 예를 들어, 코드 영역으로 innodb를 지정하여 결과를 InnoDB 메모리 장치로 제한 할 수 있습니다.

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

MySQL 설치에 따라 코드 영역에는 performance_schema, sql, client, innodb, myisam, csv, memory, blackhole, archive, partition 등이 포함될 수 있습니다.

 

2. 메모리 기기를 활성화하려면 MySQL 구성 파일에 performance-schema-instrument 규칙을 추가하십시오. 예를 들어 모든 메모리 장치를 활성화하려면 이 규칙을 구성 파일에 추가하고 서버를 다시 시작합니다.

performance-schema-instrument='memory/%=COUNTED'

 

노트

시작시 메모리 계측기를 활성화하면 시작시 발생하는 메모리 할당이 계산됩니다.

 

서버를 다시 시작한 후, 성능 스키마 setup_instruments 테이블의 ENABLED 컬럼은 활성화 한 메모리 장치에 대해 YES를 보고해야 합니다. 메모리 조작 시간이 지정되지 않았기 때문에 setup_instruments 테이블의 TIMED 컬럼은 메모리 계측기에서 무시됩니다.

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

 

3. 메모리 장치 데이터를 쿼리합니다. 이 예에서, EVENT_NAME별로 데이터를 요약하는 Performance Schema memory_summary_global_by_event_name 테이블에서 메모리 인스트루먼트 데이터가 조회됩니다. EVENT_NAME은 계측기의 이름입니다.

다음 쿼리는 InnoDB 버퍼 풀에 대한 메모리 데이터를 반환합니다.

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 137428992
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
   HIGH_NUMBER_OF_BYTES_USED: 137428992

 

sys 스키마 memory_global_by_current_bytes 테이블을 사용하여 동일한 기본 데이터를 조회할 수 있습니다.이 테이블은 서버 내의 현재 메모리 사용량을 할당 유형별로 세분화하여 표시합니다.

mysql> SELECT * FROM sys.memory_global_by_current_bytes
       WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 1
    current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
       high_count: 1
       high_alloc: 131.06 MiB
   high_avg_alloc: 131.06 MiB

 

이 sys 스키마 쿼리는 현재 할당된 메모리 (current_alloc)를 코드 영역별로 집계합니다.

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, sys.format_bytes(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 843.24 MiB    |
| memory/performance_schema | 81.29 MiB     |
| memory/mysys              | 8.20 MiB      |
| memory/sql                | 2.47 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 46.53 KiB     |
| memory/blackhole          | 512 bytes     |
| memory/federated          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+

 

 

 

■ 큰 페이지 지원 활성화

일부 하드웨어/운영체제 아키텍처는 기본값보다 큰 메모리 페이지(일반적으로 4KB)를 지원합니다. 이 지원의 실제 구현은 기본 하드웨어 및 운영 체제에 따라 다릅니다. 많은 메모리 액세스를 수행하는 응용 프로그램은 TLB(Translation Lookaside Buffer) 누락이 줄어들어 큰 페이지를 사용하여 성능을 향상시킬 수 있습니다.

 

MySQL에서는 InnoDB가 큰 페이지를 사용하여 버퍼풀 및 추가 메모리 풀에 메모리를 할당할 수 있습니다.

 

MySQL에서 대형 페이지를 표준으로 사용할 경우 지원되는 최대 크기(최대 4MB)를 사용합니다. Solaris에서 "초대형 페이지"기능을 사용하면 최대 256MB의 페이지를 사용할 수 있습니다. 이 기능은 최신 SPARC 플랫폼에서 사용할 수 있습니다. --super-large-pages 또는 --skip-super-large-pages 옵션을 사용하여 활성화하거나 비활성화 할 수 있습니다.

 

MySQL은 또한 큰 페이지 지원 (Linux에서는 HugeTLB)의 Linux 구현을 지원합니다.

 

Linux에서 큰 페이지를 사용하려면 이를 지원하도록 커널을 활성화해야하며 HugeTLB 메모리 풀을 구성해야합니다. 참고로 HugeTBL API는 Linux 소스의 Documentation / vm / hugetlbpage.txt 파일에 설명되어 있습니다.

 

Red Hat Enterprise Linux와 같은 일부 최신 시스템의 커널은 기본적으로 큰 페이지 기능을 사용하는 것으로 보입니다. 이것이 커널에 해당되는지 확인하려면 다음 명령을 사용하여“huge”가 포함 된 출력 라인을 찾습니다.

shell> cat /proc/meminfo | grep -i huge
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       4096 kB

비어 있지 않은 명령 출력은 큰 페이지 지원이 존재하지만 0값은 사용하도록 구성된 페이지가 없음을 나타냅니다.

 

큰 페이지를 지원하도록 커널을 다시 구성해야하는 경우 hugetlbpage.txt 파일을 참조합니다.

 

Linux 커널에 큰 페이지 지원이 사용 가능하다고 가정하면 다음 명령을 사용하여 MySQL에서 사용하도록 구성합니다. 일반적으로 시스템 부팅 순서 중에 실행되는 rc 파일 또는 이와 동등한 시작 파일에 파일을 넣어 시스템이 시작될 때마다 명령이 실행되도록합니다. 명령은 MySQL 서버가 시작되기 전에 부팅 순서의 초기에 실행되어야합니다. 시스템에 따라 할당 번호와 그룹 번호를 변경합니다.

# Set the number of pages to be used.
# Each page is normally 2MB, so a value of 20 = 40MB.
# This command actually allocates memory, so this much
# memory must be available.
# 사용할 페이지 수를 설정하십시오.
# 각 페이지는 보통 2MB이므로 20 = 40MB입니다.
#이 명령은 실제로 메모리를 할당하므로
# 메모리를 사용할 수 있어야합니다.
echo 20 > /proc/sys/vm/nr_hugepages

# 이 메모리에 접근 할 수있는 그룹 번호를 설정하십시오 (이 경우 102).
# mysql 사용자는 이 그룹의 멤버여야 합니다.
echo 102 > /proc/sys/vm/hugetlb_shm_group

# 세그먼트 당 허용되는 shmem의 양을 늘리십시오
# (이 경우 12G).
echo 1560281088 > /proc/sys/kernel/shmmax

# 공유 메모리의 총량을 늘립니다. 값은 페이지의 수 입니다.
# 4KB / 페이지에서 4194304 = 16GB입니다.
echo 4194304 > /proc/sys/kernel/shmall

 

 

MySQL 사용의 경우 일반적으로 shmmax의 값이 shmall의 값에 가깝도록 하려고 합니다.

큰 페이지 구성을 확인하려면 앞에서 설명한대로 / proc / meminfo를 다시 확인합니다. 이제 0이 아닌 값이 표시됩니다.

shell> cat /proc/meminfo | grep -i huge
HugePages_Total:      20
HugePages_Free:       20
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       4096 kB

 

hugetlb_shm_group을 사용하는 마지막 단계는 mysql 사용자에게 memlock 한계에 대한 "무제한"값을 제공하는 것입니다. 이 작업은 /etc/security/limits.conf를 편집하거나 mysqld_safe 스크립트에 다음 명령을 추가하여 수행 할 수 있습니다.

shell> ulimit -l unlimited

mysqld_safe에 ulimit 명령을 추가하면 루트 사용자가 mysql 사용자로 전환하기 전에 memlock 제한을 무제한으로 설정합니다. (이것은 mysqld_safe가 루트로 시작되었다고 가정합니다.)

 

 

MySQL에서 큰 페이지 지원은 기본적으로 비활성화되어 있습니다. 이를 활성화하려면 --large-pages 옵션을 사용하여 서버를 시작합니다. 예를 들어, 서버 my.cnf 파일에서 다음 행을 사용할 수 있습니다.

[mysqld]
large-pages

이 옵션을 사용하면 InnoDB는 버퍼풀 및 추가 메모리 풀에 자동으로 큰 페이지를 사용합니다. InnoDB가 이를 수행할 수 없는 경우 기존 메모리 사용으로 폴백하고 오류 로그에 경고를 기록합니다.

Warning: Using conventional memory pool

 

큰 페이지가 사용되고 있는지 확인하려면 /proc/meminfo를 다시 확인합니다.

shell> cat /proc/meminfo | grep -i huge
HugePages_Total:      20
HugePages_Free:       20
HugePages_Rsvd:        2
HugePages_Surp:        0
Hugepagesize:       4096 kB