sys_schema를 사용하여 Azure Database for MySQL - 유연한 서버에서 성능 튜닝 및 데이터베이스 유지 관리
MySQL 5.5에 처음 제공된 MySQL performance_schema에는 메모리 할당, 저장된 프로그램, 메타데이터 잠금 등과 같은 다수의 중요한 서버 리소스에 대한 계측이 제공됩니다. 하지만 performance_schema에는 80개를 초과하는 테이블이 포함되어 있어서 필요한 정보를 얻으려면 performance_schema 내의 테이블과 information_schema의 테이블을 조인해야 합니다. performance_schema 및 information_schema 기반으로 하는 이 sys_schema 읽기 전용 데이터베이스에서 사용자 친화적인 보기의 강력한 컬렉션을 제공하며 Azure Database for MySQL 유연한 서버 버전 5.7에서 완전히 사용하도록 설정됩니다.
sys_schema에는 52개의 보기가 있고 각 보기에는 다음 접두사 중 하나가 있습니다.
- Host_summary 또는 IO: I/O 관련 대기 시간.
- InnoDB: InnoDB 버퍼 상태 및 잠금.
- Memory: 호스트 및 사용자별 메모리 사용량.
- Schema: 스키마 관련 정보(예: 자동 증분, 인덱스 등).
- Statement: SQL 문에 대한 정보; 전체 테이블 스캔 또는 긴 쿼리 시간을 유발하는 명령문이 될 수 있습니다.
- User: 사용자별로 소비되고 그룹화된 리소스. 예: 파일 I/O, 연결 및 메모리.
- Wait: 호스트 또는 사용자별로 그룹화된 대기 이벤트.
이제 sys_schema의 몇 가지 일반적인 사용량 패턴을 살펴보겠습니다. 먼저 사용량 패턴을 성능 튜닝 및 데이터베이스 유지 관리의 두 가지 범주로 그룹화합니다.
성능 튜닝
sys.user_summary_by_file_io
IO는 데이터베이스에서 비용이 가장 높은 작업입니다. sys.user_summary_by_file_io 보기를 쿼리하면 평균 IO 대기 시간을 찾을 수 있습니다. 기본적으로 프로비전된 스토리지가 125GB인 상태에서 내 IO 대기 시간은 약 15초입니다.
Azure Database for MySQL 유연한 서버는 스토리지와 관련하여 IO 크기를 조정하므로 프로비전된 스토리지를 1TB로 늘리면 IO 대기 시간이 571ms로 줄어듭니다.
sys.schema_tables_with_full_table_scans
신중한 계획에도 불구하고 전체 테이블 검색을 유발하는 쿼리가 여전히 많습니다. 인덱스 유형 및 최적화 방법에 대한 자세한 내용은 이 문서를 참조하세요. 설명(EXPLAIN을 사용하여 Azure Database for MySQL - 유연한 서버의 프로필 쿼리 성능)을 참조하세요. 전체 테이블 검색에는 리소스가 많이 사용되기 때문에 데이터베이스 성능이 저하됩니다. 전체 테이블 검색으로 테이블을 찾는 가장 빠른 방법은 sys.schema_tables_with_full_table_scans보기를 쿼리하는 것입니다.
sys.user_summary_by_statement_type
데이터베이스 성능 문제를 해결하려면 데이터베이스 내에서 발생하는 이벤트를 식별하는 것이 유용할 수 있으며, sys.user_summary_by_statement_type 보기를 사용하면 유용한 작업을 수행할 수 있습니다.
이 예제에서 Azure Database for MySQL 유연한 서버는 느린 쿼리 로그를 44579번 플러시하는 데 53분이 소요되었습니다. 오랜 시간과 많은 IO가 소비되었습니다. 느린 쿼리 로그를 사용하지 않도록 설정하거나 Azure Portal에 느린 쿼리 로그의 빈도를 줄여서 이 작업을 줄일 수 있습니다.
데이터베이스 유지 관리
sys.innodb_buffer_stats_by_table
[!IMPORTANT]
이 뷰를 쿼리하면 성능에 영향을 줄 수 있습니다. 사용량이 적은 업무 시간에 이 문제를 해결하는 것이 좋습니다.
InnoDB 버퍼 풀은 메모리에 상주하며 DBMS와 스토리지 사이의 주요 캐시 매커니즘입니다. InnoDB 버퍼 풀의 크기는 성능 계층에 연결되며 다른 제품 SKU를 선택하지 않는 한 변경할 수 없습니다. 운영 체제의 메모리와 마찬가지로 새로운 데이터를 위한 공간을 확보하기 위해 오래된 페이지가 스왑 아웃(swap out)됩니다. 어떤 테이블이 대부분의 InnoDB 버퍼 풀 메모리를 소비하는지 찾아내려면 sys.innodb_buffer_stats_by_table 보기를 쿼리합니다.
위의 그래프에서 보면, 시스템 테이블과 보기 외에 WordPress 사이트 중 하나를 호스트하는 mysqldatabase033 데이터베이스의 각 테이블이 메모리의 데이터를 16KB 또는 1페이지를 차지하고 있습니다.
Sys.schema_unused_indexes & sys.schema_redundant_indexes
인덱스는 읽기 성능을 향상시키는 뛰어난 도구이지만 삽입과 스토리지에 추가 비용이 발생합니다. Sys.schema_unused_indexes 및 sys.schema_redundant_indexes는 사용하지 않은 인덱스나 중복 인덱스에 대한 정보를 제공합니다.
결론
요약하자면, sys_schema는 성능 튜닝 및 데이터베이스 유지 관리 모두에 유용한 도구입니다. Azure Database for MySQL 유연한 서버 인스턴스에서 이 기능을 활용해야 합니다.