오늘날 개발자들은 LAMP(Linux®, Apache, MySQL 및 PHP/Perl) 아키텍처를 사용하여 계속해서 애플리케이션을 개발하고 배포하고 있습니다. 그러나 다른 사람이 응용 프로그램을 작성했기 때문에 서버 관리자는 응용 프로그램 자체를 거의 제어할 수 없는 경우가 많습니다. 세 부분으로 구성된 이 시리즈에서는 애플리케이션 성능에 영향을 미칠 수 있는 다양한 서버 구성 문제에 대해 설명합니다. 이 시리즈의 세 번째이자 마지막 부분인 이 기사에서는 효율성을 극대화하기 위해 데이터베이스 계층을 조정하는 방법에 중점을 둘 것입니다.
MySQL 튜닝과 관련하여
MySQL 서버의 실행 속도를 높이는 방법에는 세 가지가 있습니다. 효율성이 낮은 것부터 높은 것 순으로
문제가 있는 하드웨어를 교체합니다. MySQL 프로세스의 설정을 조정합니다. 쿼리를 최적화합니다.
데이터베이스가 많은 리소스를 차지할 수 있기 때문에 문제가 있는 하드웨어를 교체하는 것이 가장 먼저 고려되는 경우가 많습니다. 하지만 이 솔루션은 지금까지만 적용됩니다. 실제로 중앙 처리 장치(CPU)나 디스크의 속도를 두 배로 늘리고 메모리를 4~8배까지 늘릴 수 있는 경우가 많습니다.
두 번째 방법은 MySQL 서버(mysqld라고도 함)를 조정하는 것입니다. 이 프로세스를 조정한다는 것은 메모리를 적절하게 할당하고 mysqld에 어떤 유형의 부하가 가해지는지를 알려주는 것을 의미합니다. 디스크 작업 속도를 높이는 것은 필요한 디스크 액세스 횟수를 줄이는 것만큼 중요하지 않습니다. 마찬가지로, MySQL 프로세스가 올바르게 작동한다는 것은 임시 디스크 테이블 작업이나 파일 열기 및 닫기와 같은 백그라운드 작업보다 쿼리를 처리하는 데 더 많은 시간을 소비한다는 것을 의미합니다. mysqld 튜닝이 이 글의 초점이다.
가장 좋은 방법은 쿼리가 최적화되었는지 확인하는 것입니다. 이는 적절한 인덱스가 테이블에 적용되고 쿼리가 MySQL 기능을 최대한 활용하는 방식으로 작성됨을 의미합니다. 이 기사에서는 쿼리 튜닝(많은 책에서 다룬 주제)을 다루지는 않지만 튜닝이 필요할 수 있는 쿼리를 보고하도록 mysqld를 구성합니다.
이러한 작업에 순서가 지정되어 있지만 쿼리를 적절하게 조정하려면 하드웨어 및 mysqld 설정에 여전히 주의를 기울여야 합니다. 머신이 느려도 괜찮습니다. mysqld가 많은 바쁜 작업으로 점유되어 쿼리를 제공할 수 없었기 때문에 잘 설계된 쿼리를 실행할 때 과부하로 인해 매우 빠른 머신이 실패하는 것을 보았습니다.
느린 쿼리 로깅
SQL 서버에서 데이터 테이블은 디스크에 저장됩니다. 인덱스는 전체 테이블을 검색하지 않고도 테이블에서 특정 데이터 행을 찾는 방법을 서버에 제공합니다. 테이블 전체를 검색해야 하는 경우를 테이블 스캔이라고 합니다. 일반적으로 말하면 테이블에 있는 데이터의 하위 집합만 얻으려고 할 수 있으므로 전체 테이블 스캔은 많은 디스크 I/O를 낭비하므로 시간도 많이 걸립니다. 데이터를 조인해야 하는 경우 조인 양쪽에 있는 여러 데이터 행을 비교해야 하므로 이 문제는 더욱 복잡해집니다.
물론 테이블 스캔이 항상 문제를 일으키는 것은 아닙니다. 때로는 데이터의 하위 집합을 선택하는 것보다 전체 테이블을 읽는 것이 더 효율적입니다. 이러한 결정을 내리는 데는 서버 프로세스의 쿼리 플래너가 사용됩니다. 인덱스를 비효율적으로 사용하거나 전혀 사용할 수 없는 경우 쿼리 속도가 느려지고, 서버의 부하가 증가하고 테이블 크기가 커질수록 이러한 문제는 더욱 심각해집니다. 지정된 시간 범위보다 실행하는 데 시간이 오래 걸리는 쿼리를 느린 쿼리라고 합니다.
이러한 느린 쿼리를 적절한 이름의 느린 쿼리 로그에 기록하도록 mysqld를 구성할 수 있습니다. 그런 다음 관리자는 이 로그를 검토하여 응용 프로그램의 어떤 부분에 추가 조사가 필요한지 결정하는 데 도움을 줍니다. 목록 1은 느린 쿼리 로깅을 활성화하기 위해 my.cnf에서 수행해야 하는 구성을 보여줍니다.
목록 1. MySQL 느린 쿼리 로그 활성화
[mysqld]; 느린 쿼리 로그 활성화(기본값 10초)log-slow-queries; 5초 이상 걸리는 로그 쿼리long_query_time = 5; MySQL 4.1 이상에서만 인덱스를 사용하지 않는 로그 쿼리; 쿼리를 사용하지 않는 인덱스
이 세 가지 설정은 실행하는 데 5초 이상 걸리고 인덱스를 사용하지 않는 쿼리를 기록하는 데 함께 사용됩니다. log-queries-not-using-indexes에 대한 경고에 유의하십시오. MySQL 4.1 이상을 사용해야 합니다. 느린 쿼리 로그는 MySQL 데이터 디렉터리에 저장되며 이름은 hostname-slow.log입니다. 다른 이름이나 경로를 사용하려면 my.cnf에서 log-slow-queries = /new/path/to/file을 사용하면 됩니다.
느린 쿼리 로그를 읽는 것은 mysqldumpslow 명령을 통해 수행하는 것이 가장 좋습니다. 로그 파일의 경로를 지정하면 로그 파일에서 발생하는 횟수와 함께 느린 쿼리의 정렬된 목록을 볼 수 있습니다. 매우 유용한 기능은 mysqldumpslow가 결과를 비교하기 전에 사용자가 지정한 모든 데이터를 제거하므로 동일한 쿼리에 대한 여러 호출이 하나로 계산된다는 것입니다. 이는 가장 많은 작업이 필요한 쿼리를 식별하는 데 도움이 될 수 있습니다.
쿼리 캐싱
많은 LAMP 애플리케이션은 데이터베이스에 크게 의존하지만 동일한 쿼리를 계속해서 실행합니다. 쿼리가 실행될 때마다 데이터베이스는 쿼리를 분석하고, 실행 방법을 결정하고, 디스크에서 정보를 로드하고, 결과를 클라이언트에 반환하는 등 동일한 작업을 수행해야 합니다. MySQL에는 쿼리 결과(나중에 사용됨)를 메모리에 저장하는 쿼리 캐시라는 기능이 있습니다. 대부분의 경우 이렇게 하면 성능이 크게 향상됩니다. 그러나 문제는 쿼리 캐싱이 기본적으로 비활성화되어 있다는 것입니다.
32MB 쿼리 캐시를 활성화하려면 query_cache_size = 32M을 /etc/my.conf에 추가하세요.
쿼리 캐시 모니터링
쿼리 캐시를 활성화한 후에는 쿼리 캐시가 효과적으로 사용되고 있는지 이해하는 것이 중요합니다. MySQL에는 캐시에서 무슨 일이 일어나고 있는지 이해하기 위해 살펴볼 수 있는 여러 변수가 있습니다. Listing 2는 캐시의 상태를 보여준다.
목록 2. 쿼리 캐시 통계 표시
mysql> 'qcache%'와 같은 상태 표시;+------------+------------+ | 변수_이름 |+------------+------------+| || Qcache_free_memory | 2581646882 || Qcache_lowmem_prunes | 7 || -------- ---+---+8행 세트(0.00초)
해당 항목에 대한 설명은 표 1에 나와 있습니다.
표 1. MySQL 쿼리 캐시 변수 변수 이름 설명
Qcache_free_blocks 캐시에 있는 인접한 메모리 블록의 수입니다. 숫자가 크면 조각이 있을 수 있음을 나타냅니다. FLUSH QUERY CACHE는 캐시 조각 모음을 수행하여 여유 블록을 얻습니다.
Qcache_free_memory 캐시의 메모리를 비웁니다.
Qcache_hits는 쿼리가 캐시에 적중할 때마다 증가합니다.
Qcache_inserts는 쿼리가 삽입될 때마다 증가됩니다. 미스 비율은 적중 수를 삽입 수로 나눈 값입니다. 이 값을 1에서 빼면 적중률이 계산됩니다. 위의 예에서는 쿼리의 약 87%가 캐시에 적중되었습니다.
Qcache_lowmem_prunes 캐시에 메모리가 부족하여 더 많은 쿼리를 위한 공간을 확보하기 위해 제거해야 했던 횟수입니다. 이 숫자는 장기간에 걸쳐 가장 잘 볼 수 있습니다. 숫자가 증가하면 심각한 조각화 또는 메모리 부족을 나타낼 수 있습니다. (위의 free_blocks 및 free_memory를 보면 어떤 경우인지 알 수 있습니다.)
Qcache_not_cached 일반적으로 SELECT 문이 아니기 때문에 캐싱에 적합하지 않은 쿼리 수입니다.
Qcache_queries_in_cache 현재 캐시된 쿼리(및 응답) 수입니다.
Qcache_total_blocks 캐시의 블록 수입니다.
종종 이러한 변수를 몇 초 간격으로 표시하여 차이점을 확인할 수 있으며, 이는 캐시가 효율적으로 사용되고 있는지 확인하는 데 도움이 될 수 있습니다. FLUSH STATUS를 실행하면 일부 카운터가 재설정될 수 있으며, 이는 서버가 한동안 실행된 경우 매우 유용할 수 있습니다.
매우 큰 쿼리 캐시를 사용하고 모든 것을 캐시할 것으로 기대하는 것은 매우 유혹적입니다. mysqld는 메모리가 부족할 때 정리를 수행하는 등 캐시에 대한 유지 관리를 수행해야 하기 때문에 서버가 캐시를 관리하려고 하면 중단될 수 있습니다. 일반적으로 FLUSH QUERY CACHE에 시간이 오래 걸리면 캐시가 너무 큰 것입니다.
제한 적용
시스템 로드로 인해 리소스가 고갈되지 않도록 mysqld에 제한을 적용할 수 있습니다. 목록 3은 my.cnf의 몇 가지 중요한 리소스 관련 설정을 보여줍니다.
목록 3. MySQL 리소스 설정
설정 변수=max_connections=500설정 변수=wait_timeout=10max_connect_errors = 100
최대 연결 수는 첫 번째 줄에서 관리됩니다. Apache의 MaxClients와 유사하게 서비스에서 허용하는 연결 수만 이루어지도록 하는 것이 아이디어입니다. 현재 서버에 설정된 최대 연결 수를 확인하려면 SHOW STATUS LIKE 'max_used_connections'를 실행합니다.
2번째 줄은 mysqld에게 10초 이상 유휴 상태인 모든 연결을 종료하라고 지시합니다. LAMP 애플리케이션에서 데이터베이스에 연결하는 데 걸리는 시간은 일반적으로 웹 서버가 요청을 처리하는 데 걸리는 시간입니다. 때로는 로드가 너무 무거우면 연결이 중단되고 연결 테이블 공간을 차지하게 됩니다. 대화형 사용자가 여러 명이거나 데이터베이스에 대한 지속적인 연결을 사용하는 경우 이 값을 낮게 설정하는 것은 바람직하지 않습니다!
마지막 줄은 안전한 방법입니다. 호스트가 서버에 연결하는 데 문제가 있고 포기하기 전에 여러 번 재시도하면 호스트가 잠기고 FLUSH HOSTS가 끝날 때까지 실행할 수 없습니다. 기본적으로 10번의 실패만으로도 잠금을 유발할 수 있습니다. 이 값을 100으로 변경하면 서버가 문제를 복구할 수 있는 충분한 시간을 얻을 수 있습니다. 100회 재시도 후에도 연결을 설정할 수 없는 경우 더 높은 값을 사용해도 큰 도움이 되지 않으며 전혀 연결되지 않을 수 있습니다.
버퍼 및 캐싱
MySQL은 100개 이상의 조정 가능한 설정을 지원하지만 다행히도 몇 가지만 익히면 대부분의 요구 사항을 충족할 수 있습니다. 이러한 설정에 대한 올바른 값을 찾으려면 SHOW STATUS 명령을 통해 상태 변수를 보면 mysqld가 예상대로 작동하는지 확인할 수 있습니다. 버퍼와 캐시에 할당된 메모리는 시스템에서 사용 가능한 메모리를 초과할 수 없으므로 일반적으로 조정에는 약간의 절충이 필요합니다.
MySQL 조정 가능 설정은 전체 mysqld 프로세스 또는 개별 클라이언트 세션에 적용될 수 있습니다.
서버 측 설정
각 테이블은 먼저 열어서 읽어야 하는 디스크의 파일로 표시될 수 있습니다. 파일에서 데이터를 읽는 프로세스 속도를 높이기 위해 mysqld는 /etc/mysqld.conf의 table_cache에 지정된 최대 수까지 이러한 열린 파일을 캐시합니다. 목록 4는 테이블 열기와 관련된 활동을 표시하는 방법을 보여줍니다.
목록 4. 테이블을 여는 활동 표시
mysql> 'open%tables'와 같은 상태 표시;+---------------+-------+| -----+-------+| Open_tables || Open_tables 195 |+---------------+----- 세트당 +2줄(0.00초)
목록 4에서는 현재 5,000개의 테이블이 열려 있고 캐시에 사용 가능한 파일 설명자가 없기 때문에 195개의 테이블을 열어야 함을 보여줍니다(통계가 이전에 지워졌으므로 열린 테이블이 195개만 있을 수 있음). . SHOW STATUS 명령을 다시 실행하여 Opened_tables가 급격히 증가하면 캐시 적중률이 부족하다는 의미입니다. Open_tables가 table_cache 설정보다 훨씬 작으면 값이 너무 큰 것입니다(그러나 성장할 공간이 있다는 것은 결코 나쁜 것이 아닙니다). 예를 들어 table_cache = 5000을 사용하여 테이블 캐시를 조정합니다.
테이블 캐시와 유사하게 스레드에 대한 캐시도 있습니다. mysqld는 연결을 수신할 때 필요에 따라 스레드를 생성합니다. 연결이 빠르게 변경되는 사용량이 많은 서버에서는 나중에 사용하기 위해 스레드를 캐싱하면 초기 연결 속도를 높일 수 있습니다.
Listing 5에서는 충분한 스레드가 캐시되었는지 확인하는 방법을 보여줍니다.
목록 5. 스레드 사용 통계 표시
mysql> 'threads%'와 같은 상태 표시;+------+---------+| Variable_name | ---------------+-------+| Threads_connected 15 || Threads_running | ---------------+---------+4행 세트(0.00초)
여기서 중요한 값은 Threads_created이며, 이 값은 mysqld가 새 스레드를 생성해야 할 때마다 증가됩니다. 연속적인 SHOW STATUS 명령을 실행할 때 이 숫자가 급격히 증가하면 스레드 캐시를 늘려야 합니다. 예를 들어 my.cnf에서 thread_cache = 40을 사용하여 이를 달성할 수 있습니다.
키 버퍼는 MyISAM 테이블의 인덱스 블록을 보유합니다. 이상적으로는 이러한 블록에 대한 요청이 디스크가 아닌 메모리에서 이루어져야 합니다. Listing 6은 디스크에서 읽은 블록 수와 메모리에서 읽은 블록 수를 확인하는 방법을 보여줍니다.
목록 6. 키워드 효율성 결정
mysql> '%key_read%'와 같은 상태 표시;+------+----------+| Variable_name | ------+----------+| Key_read_requests || Key_reads |+------- - ---------+------------+2행 세트(0.00초)
Key_reads는 디스크에 적중하는 요청 수를 나타내고 Key_read_requests는 총 수입니다. 미스 비율은 디스크에 도달한 읽기 요청 수를 총 읽기 요청 수로 나눈 값입니다. 이 경우 요청 1,000개당 메모리에서 약 0.6개의 미스가 발생합니다. 디스크 적중 횟수가 요청 1,000개당 1회를 초과하는 경우 키워드 버퍼 증가를 고려해야 합니다. 예를 들어 key_buffer = 384M은 버퍼를 384MB로 설정합니다.
임시 테이블은 추가 처리(예: GROUP BY 절) 전에 데이터를 임시 테이블에 저장해야 하는 고급 쿼리에서 사용할 수 있으며, 이상적으로는 임시 테이블이 메모리에 생성됩니다. 그러나 임시 테이블이 너무 커지면 디스크에 기록해야 합니다. Listing 7은 임시 테이블 생성과 관련된 통계를 제공한다.
목록 7. 임시 테이블 사용 결정
mysql> 'created_tmp%';+---------------+-------+값과 같은 상태 표시 | | +------------+------+| Created_tmp_tables || Created_tmp_tables | | +---------+---------+3행 세트(0.00초)
Created_tmp_tables는 임시 테이블이 사용될 때마다 증가하며 디스크 기반 테이블의 경우에도 Created_tmp_disk_tables가 증가합니다. 관련된 쿼리에 따라 달라지므로 이 비율에 대한 엄격한 규칙은 없습니다. 시간 경과에 따른 Created_tmp_disk_tables를 관찰하면 생성된 디스크 테이블의 비율이 표시되고 설정의 효율성을 확인할 수 있습니다. tmp_table_size와 max_heap_table_size는 모두 임시 테이블의 최대 크기를 제어하므로 my.cnf에 두 값이 모두 설정되어 있는지 확인하세요.
세션별 설정
다음 설정은 각 세션마다 다릅니다. 존재할 수 있는 연결 수를 곱하면 이 옵션이 많은 양의 메모리를 나타내기 때문에 이 숫자를 설정할 때 매우 주의하십시오! 코드를 통해 세션 내에서 이러한 숫자를 수정하거나 모든 세션에 대해 my.cnf에서 이러한 설정을 수정할 수 있습니다.
MySQL은 정렬해야 할 때 디스크에서 읽을 때 데이터 행을 보관하기 위해 정렬 버퍼를 할당합니다. 정렬할 데이터의 양이 너무 많으면 디스크의 임시 파일에 데이터를 저장한 후 다시 정렬해야 합니다. sort_merge_passes 상태 변수가 큰 경우 이는 디스크 활동을 나타냅니다. Listing 8은 정렬과 관련된 일부 상태 카운터 정보를 보여줍니다.
목록 8. 정렬 통계 표시
mysql> "sort%"와 같은 상태 표시;+------+---------+| Variable_name | ---+---------+| Sort_range | 2066532 || ------+---------+4행 세트(0.00초)
sort_merge_passes가 크다면 sort_buffer_size에 주의해야 한다는 의미입니다. 예를 들어, sort_buffer_size = 4M은 정렬 버퍼를 4MB로 설정합니다.
MySQL은 또한 테이블을 읽기 위해 일부 메모리를 할당합니다. 이상적으로 인덱스는 필요한 행만 읽을 수 있을 만큼 충분한 정보를 제공하지만 때로는 쿼리(잘못 설계되었거나 데이터 특성으로 인해)가 테이블에서 많은 양의 데이터를 읽어야 하는 경우도 있습니다. 이 동작을 이해하려면 SELECT 문이 실행된 횟수와 인덱스를 통해 직접 액세스하지 않고 테이블의 다음 데이터 행을 읽어야 하는 횟수를 알아야 합니다. 이 기능을 구현하는 명령은 목록 9에 나와 있습니다.
목록 9. 테이블 스캔 비율 결정하기
mysql> "com_select"와 같은 상태 표시;+---------------+---------+| ------+-------+| Com_select | 318243 |+----------+---------+1 행(0.00초) mysql> "handler_read_rnd_next"와 같은 상태 표시;+------------+------------ Variable_name | |+----------+----------+| Handler_read_rnd_next |+- ----- ------+------------+1행 세트(0.00초)
Handler_read_rnd_next / Com_select는 테이블 스캔 비율(이 경우 521:1)을 생성합니다. 값이 4000을 초과하는 경우 read_buffer_size를 확인해야 합니다(예: read_buffer_size = 4M). 이 숫자가 8M을 초과하면 개발자와 이러한 쿼리 조정에 대해 논의할 때입니다!
3 필수 도구
특정 설정을 이해할 때 SHOW STATUS 명령이 매우 유용할 수 있지만, mysqld가 제공하는 대량의 데이터를 해석하려면 몇 가지 도구도 필요합니다. 제가 중요하다고 생각하는 세 가지 도구가 있습니다. 리소스 섹션에서 해당 도구에 대한 링크를 찾을 수 있습니다.
대부분의 시스템 관리자는 작업에 사용되는 CPU 및 메모리에 대해 지속적으로 업데이트되는 보기를 제공하는 top 명령에 익숙합니다. mytop은 top을 에뮬레이트합니다. 연결된 모든 클라이언트와 클라이언트가 실행 중인 쿼리에 대한 보기를 제공합니다. mytop은 또한 키워드 버퍼 및 쿼리 캐시 효율성에 대한 실시간 및 기록 데이터는 물론 실행 중인 쿼리에 대한 통계도 제공합니다. 이는 시스템에서 무슨 일이 일어나고 있는지(예: 10초 이내) 확인하는 데 유용한 도구로, 서버 상태 정보를 확인하고 문제를 일으키는 모든 연결을 표시할 수 있습니다.
mysqlard는 MySQL 서버에 연결된 데몬으로, 5분마다 데이터를 수집하여 백그라운드에서 라운드 로빈 데이터베이스에 저장하는 역할을 합니다. 테이블 캐시 사용량, 키워드 효율성, 연결된 클라이언트, 임시 테이블 사용량 등의 데이터를 표시하는 웹 페이지가 있습니다. mytop은 서버 상태 정보의 스냅샷을 제공하지만 mysqlard는 장기적인 상태 정보를 제공합니다. 보너스로 mysqlard는 수집한 정보 중 일부를 사용하여 서버 조정 방법에 대한 몇 가지 제안을 제공합니다.
SHOW STATUS 정보를 수집하는 또 다른 도구는 mysqlreport입니다. 서버의 모든 측면을 분석해야 하기 때문에 보고는 mysqlard보다 훨씬 더 복잡합니다. 이는 상태 변수에 대해 적절한 계산을 수행하여 수정해야 할 문제를 결정하는 데 도움이 되므로 서버 튜닝을 위한 훌륭한 도구입니다.
결론
이 기사에서는 MySQL 튜닝에 대한 몇 가지 기본 지식을 소개하고 LAMP 구성 요소 튜닝에 대한 3부 시리즈를 마무리했습니다. 튜닝에는 주로 구성 요소의 작동 방식을 이해하고, 제대로 작동하는지 확인하고, 일부 조정을 하고, 재평가하는 작업이 포함됩니다. Linux, Apache, PHP 또는 MySQL 등 각 구성 요소에는 다양한 요구 사항이 있습니다. 각 구성 요소를 개별적으로 이해하면 애플리케이션 속도를 저하시킬 수 있는 병목 현상을 줄이는 데 도움이 됩니다.