MySQL 의 느린 쿼리 로그 기능을 사용하면 미리 정의된 시간 제한을 초과하는 쿼리를 기록할 수 있습니다. 이는 비효율적이거나 시간 소모적인 쿼리를 찾는 작업을 크게 단순화합니다. 이러한 로그를 Cloud Logging 및 Monitoring과 통합하면 데이터베이스 성능을 주시하여 최적화가 필요한 쿼리를 식별하고 선택적으로 느린 쿼리가 너무 많은 경우 알림을 받을 수 있습니다.
이 가이드는 Compute Engine에서 실행되는 자체 관리형 MySQL 인스턴스에도 사용할 수 있습니다.
목표
Cloud SQL에서 느린 쿼리 로그를 사용 설정하는 방법을 알아보세요.
Cloud Logging에서 느린 쿼리 로그에 액세스하는 방법을 알아보세요.
경고 및 문제 해결을 위한 로그 기반 측정항목을 만드는 방법을 알아보세요.
비용
본 문서에서는다음 Google Cloud 구성요소를 사용합니다. 비용이 발생합니다.
MySQL용 Cloud SQL(본 문서에스는 demo-mysql 인스턴스ID를 사용)
클라우드 로깅
클라우드 모니터링
MySQL용 Cloud SQL에 느린 쿼리 로그 사용 설정
MySQL용 Cloud SQL은 GCP에서 MySQL 데이터베이스를 쉽게 설정, 유지, 관리할 수 있는 완전 관리형 데이터베이스 서비스입니다. Cloud SQL에는 Cloud Logging 통합이 내장 되어 있으므로 로그 뷰어에서 MySQL 로그를 사용할 수 있습니다.
느린 쿼리 로그는 기본적으로 2초 이상 걸리는 SQL 문을 로깅합니다.long_query_time플래그값을 조정하여 이 필터링을 제어할 수 있습니다 .기본적으로 느린 쿼리 로그는 MySQL에서 활성화되어 있지 않습니다.느린 쿼리가 활성화되지 않은 기존 데이터베이스 인스턴스를 사용하는 경우 다음을 사용gcloud sql instances patch하여 플래그를 설정할 수 있습니다.
Connecting to database with SQL user [root]. Enter password:
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:00
리소스를 많이 사용하는 SQL 쿼리 목록이 있는 파일을 만듭니다.
cat << EOF >select_query.sql
use employees;
SELECT * FROM salaries WHERE salary > 0 LIMIT 100000;
SELECT departments.dept_name,dept_emp.from_date,employees.* FROM departments LEFT JOIN dept_emp ON (departments.dept_no = dept_emp.dept_no) LEFT JOIN employees ON (dept_emp.emp_no = employees.emp_no) WHERE to_date = '9999-01-01' LIMIT 100000;
SELECT employees.* FROM employees LEFT JOIN dept_emp ON ( dept_emp.emp_no = employees.emp_no ) LEFT JOIN salaries ON ( salaries.emp_no = salaries.emp_no ) WHERE employees.first_name LIKE '%Jo%' AND salaries.from_date > '1993-01-21' AND salaries.to_date < '1998-01-01' limit 1000000;
EOF
Cloud Shell 인스턴스의 공개 IP 주소에 Cloud SQL 인스턴스에 대한 액세스 권한을 부여합니다.
Benchmark
Average number of seconds to run all queries: 103.712 seconds
Minimum number of seconds to run all queries: 103.712 seconds
Maximum number of seconds to run all queries: 103.712 seconds
Number of clients running queries: 10
Average number of queries per client: 50
테스트에는 몇 분이 소요될 수 있습니다.
테스트가 완료되면 Cloud Logging 로그 뷰어 페이지 로 돌아갑니다.(검색창에 logging을 입력한 후 검색된 "로깅" 을 클릭합니다.)
리소스 드롭다운에서 Cloud SQL을 클릭한 후 및 본인이 생성한 Cloud SQL 인스턴스ID를 선택 후 [추가]를 클릭합니다.
로그이름 드롭다운에서 mysql-slow.log 를 선택한 후 [추가] 버튼을 클릭합니다.
SELECT 로 시작하는 부하 테스트의 느린 쿼리를 볼 수 있습니다.
sudo service google-fluentd restart
MySQL 느린 쿼리 로그 켜기
기본적으로 MySQL은 느린 쿼리 를 기록하지 않습니다 . 이를 활성화하려면 루트로 MySQL에 연결하고 루트 암호를 입력하십시오.
mysql -u root -p
mysql>프롬프트 에서 다음 명령을 입력 합니다.
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log = 'ON';
이제 MySQL은 2초 이상 걸리는 모든 쿼리를 기록합니다. 의 값을 조정하여 이 임계값을 변경할 수 있습니다 long_query_time.
이제 느린 쿼리 로그가 google-fluentd예상되는 위치에 기록되고 있는지 확인합니다.
sudo cat /var/log/mysql/mysql-slow.log
예상 출력:
/usr/sbin/mysqld, Version: 5.7.26-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
이 설정은 일시적이며 MySQL을 다시 시작하면 손실됩니다. 이를 유지하려면 MySQL 옵션 파일 (일반적으로 /etc/mysql/mysql.conf.d/mysqld.cnf)을 찾아 루트로 파일을 편집하고 다음 행을 추가하거나 주석 처리를 제거하십시오.
"승인된 네트워크" 항목에서는 Cloud SQL에 접속할 수 있는 네트워크 대역을 설정합니다. 본 문서에서는 외부에서 접속을 가정하여 0.0.0.0/0 으로 설정했습니다. 공개 IP에 연결하려면 승인된 네트워크 또는 Cloud SQL 프록시를 사용해야 합니다.
유지보수 :
- 유지보수를 위한 업데이트되는 동안 인스턴스가 다시 시작되므로 서비스 중단이 발생할 수 있다. 이를 방지하기 위해서 유지보수 거부기간을 설정한다. 단 최대 90일 까지만 적용되므로 운영 단계에서는 주기적으로 설정해야 한다.
플래그 : MySQL DB 파라미터. 변경을 원하는 파라미터가 있을 경우 플래그 항목에서 설정
플래그 중 lower_case_table_names, default_time_zone, character_set_server 는 반드시 자신의 시스템에 맞도록 설정해야 합니다. character_set_server 는 utf8과 utf8mb4가 있는데 emoji 등 4byte 문자를 사용할 경우에는 utf8mb4를 지정하고 그렇지 않을 경우 공간의 효율을 위해 utf8로 설정합니다.
--> 승인된 네트워크 설정 시 subnet인 10.0.0.0/22 입력값이 오류가 발생. 0.0.0.0/0 을 입력하니 오류는 발생하지 않음.
7. 아래와 같이 GCP 에서 제공하는 Cloud SQL인 mysql 서버가 생성된 것을 확인할 수 있습니다.
Cloud SQL (MySQL) 접속 방법
Cloud SQL DB에 접속하는 방법으로 DB관리 툴, GCP Cloud shell, Data Studio로 연결하는 방법을 설명합니다. DB 접속 도구는 MySQL Workbench, Toad for MySQL, SQuirrel, DBeaver 등이 있습니다. 본 문서에서는 HeidiSQL 툴로 접속하는 방법을 설명합니다.
1. DB 관리 툴(HeidiSQL)로 연결
GCP 콘솔에 로그인한 다음 좌측 상단의 햄버거 메뉴를 클릭한 후 SQL 메뉴를 클릭한다.
위에서 생성한 Cloud SQL 인스턴스에 대한 정보가 아래와 같이 표시됩니다. 공개IP주소는 외부에서 DB로 연결 시 필요한 정보입니다. 추가로, 외부에서 접속하기 위한 네트워크 설정(inbound 설정)이 필요합니다.
네트워크 설정을 위해 인스턴스를 클릭한 후 좌측의 [연결] 메뉴를 클릭합니다. 우측에 나타나는 화면에서 [네트워크 추가]를 클릭합니다.
[네트워크 추가] 클릭 후 이름 항목에 원하는 이름을 입력하고(본 문서에서는 ALL 입력) 네트워크 항목에는 0.0.0.0/0 을 입력하고 [완료]를 클릭합니다. 최종 저장을 위해 하단의 [저장] 버튼을 클릭하여 변경된 사항을 저장합니다.
Google Cloud Console에서 Cloud SQL 인스턴스 페이지로 이동합니다. 좌측 햄버거 메뉴에서 "SQL" 을 클릭하던지 클라우드 콘솔 검색창에 sql을 입력하여 [제품 및 페이지]에서 SQL을 선택합니다.
인스턴스 만들기를 클릭합니다.
MySQL을 클릭합니다.
Compute API를 사용 설정하라는 메시지가 표시되면 API 사용 설정 버튼을 클릭합니다.
인스턴스 ID에 myinstance를 입력합니다.
루트 사용자의 비밀번호를 입력합니다.
다른 필드에는 기본값을 사용합니다.
만들기를 클릭합니다.참고: 이 예시에서는 공개 IP 주소를 포함한 기본 설정을 사용하여 인스턴스가 생성됩니다.
mysql 클라이언트 설치
본 문서에서는 Google VM instance OS인 Debian linux 에 설치합니다.
$ sudo apt-get install default-mysql-client
Cloud SQL 인증 프록시 클라이언트 설치
사용 중인 운영체제 및 운영체제가 사용하는 커널(32비트 또는 64비트)에 따라 다른 Cloud SQL 인증 프록시 바이너리를 다운로드합니다. 대부분의 최신 하드웨어는 64비트 커널을 사용합니다. 머신에서 32비트 커널을 실행 중인지 또는 64비트 커널을 실행 중인지 확실하지 않은 경우 Linux 또는 macOS에서 uname -a 명령어를 사용합니다. Windows의 경우 Windows 문서를 참조하세요.
본 문서에서는 GCP에 생성한 VM 인스턴스에 설치합니다.
sql proxy 서버로 사용할 새로운 VM instance를 생성하고 접속하여 아래 명령어를 수행합니다.
## wget 이 설치되어 있지 않을 경우 wget 설치
$ sudo apt install wget
$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
$ chmod +x cloud_sql_proxy
인스턴스 연결 이름 확인
Cloud SQL 인증 프록시에 접속할 인스턴스명을 확인하기 위해 Cloud SQL의 인스턴스 연결이름을 확인합니다.
본 문서에서는 advance-casing-340704:asia-northeast3:demo-mysql 이름을 사용합니다.
Google Cloud Console에서 Cloud SQL 인스턴스 페이지로 이동합니다.
인스턴스 이름을 클릭하여 개요 페이지를 엽니다.
인스턴스에 연결 섹션에서 연결 이름을 복사합니다. 연결 이름은 projectID:region:instanceID 형식입니다.
$ ./cloud_sql_proxy -instances=advance-casing-340704:asia-northeast3:demo-mysql=tcp:3306
2022/02/16 08:31:16 Rlimits for file descriptors set to {Current = 8500, Max = 1048576}
2022/02/16 08:31:17 errors parsing config:
googleapi: Error 403: Request had insufficient authentication scopes.
Details:
[
{
"@type": "type.googleapis.com/google.rpc.ErrorInfo",
"domain": "googleapis.com",
"metadata": {
"method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
"service": "sqladmin.googleapis.com"
},
"reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT"
}
]
More details:
Reason: insufficientPermissions, Message: Insufficient Permission
위와 같이 Insufficient Permission 이 발생할 경우, VM 인스턴스를 중지한 후
Compute Engine > VM instance details > Identity and API access(api 및 ID 관리) 항목에서 서비스 계정의 액세스 범위를
"모든 Cloud API에 대한 전체 액세스 허용"으로 변경 후 인스턴스를 기동합니다.
또는 Identity and API access(api 및 ID 관리) 항목에서 "각 API에 액세스 설정" 선택 후 Cloud SQL 항목을 "사용 설정됨"으로 선택합니다.
다시 접속을 시도해 보니 아래와 같은 에러가 발생했습니다.
googleapi: Error 403: The client is not authorized to make this request., notAuthorized
두 번째 오류 처리를 위해 아래 명령어를 수행하고 다시 접속시도를 했으나, 동일한 오류가 발생했습니다.
아래 명령어를 수행하면 url 접속 정보가 뜨는데 브라우저로 접속하면 구글 접속계정이 화면에 표시되고 계정을 클릭하면 인증코드가 발행됩니다. 이 인증코드를 아래 화면에 입력하면 인증파일이 생성됩니다.
$ gcloud auth application-default login
You are running on a Google Compute Engine virtual machine.
The service credentials associated with this virtual machine
will automatically be used by Application Default
Credentials, so it is not necessary to use this command.
... ...
Do you want to continue (Y/n)? Y
Go to the following link in your browser:
https://accounts.google.com/o/oauth2/auth?response_type=code&client_id=764086051850-6qr4p6gpi6hn506pt8ejuq83di341hur.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&scope=openid+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fcloud-platform+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Faccounts.reauth&state=jyPBenHBlKW9ZGmL090wTmjX5w63fI&prompt=consent&access_type=offline&code_challenge=VCXpHUS9G_Y0yEWPDuHZpgFhQsNV3UKBOoOSfuG5Qdc&code_challenge_method=S256
Enter verification code:
프록시를 사용하여 연결할 때는 프록시가 구글 클라우드 플랫폼으로 인증해야 합니다. Cloud SDK 사용자 인증 정보를 사용하거나, 생성한 서비스 계정의 로컬 키 파일 경로를 프록시에 제공할 수 있습니다.
추가로, SQL 클라이언트의 모든 송신 port가 deny 되어 있는 경우, SQL proxy를 사용하려면 3307 port가 오픈되어 있어야 합니다.
참고:Cloud SQL Auth 프록시는 3307을 사용하여 Cloud SQL Auth 프록시 서버에 연결합니다. 포트 3306은 직접 TCP 연결의 MySQL 프로토콜에 대한 기본 포트입니다(Cloud SQL 인증 프록시 사용 안 함)
Listening on 127.0.0.1:3306 for INSTANCE_CONNECTION_NAME
Ready for new connections
만일, 프록시 서버의 IP 또는 프록시 서버의 local IP로 Cloud SQL DB 접속이 가능하게 하려면, 아래처럼 instances에 나열해 주면 됩니다. 마지막의 '&' 문자는 백그라운드로 실행하라는 명령문입니다. foreground로 실행하려면 '&' 문자를 없애고 실행하면 됩니다.
