따봉 구걸남's 블로그

mysql 에서 사용하는 로그들 정리해보자 

1. General log 

general log 를 활용하면 client 에서 어떤 쿼리들을 mysql server 에 보내는지 확인 할수 있다. 

 1) Binary log 와의 차이

general log 는 서버에 쿼리 요청이 들어오는 순서대로 로그에 기록된다. 
반면 binary log 는 실행시간에 따라 기록이 된다. 
또한 binary log 는 select 는 포함하지 않는다. 
general log 는 조회 쿼리도 포함되기에 log 의 양이 많다.  

 2) output 종류 (slow 동일)

아래 log_output 을 아래 세가지 중 하나를 선택할 수 있다. 
none 으로 설정을 하면 우선순위가 앞서며 general_log=on 으로 설정해도 로그가 남지 않는다. 

 - file      =>  *.log 를 생성
 - table   =>  *.csv 를 생성 
 - none 

table 을 선택하면 general_log 이름으로 DB에서 조회가 가능해진다. 
테이블을 사용하는데 size 가 걱정되어 테이블 사이즈를 조회하는 쿼리를 날리면 0 으로 나온다. 
이것은 general_log 테이블이 테이블이라는 object 에 쌓는 것이 아니고
실제로는 CSV 엔진으로 general_log.CSV 라는 파일에 덤프를 쌓고 있기 때문이다. 

 3) Table output 의 장점 

 - query 에 조건문을 넣어서 질의 하는 것이 가능해진다.
 - 원격에서도 확인이 가능 
 - csv 파일 접근 가능한 스프레드시트에서 활용이 가능해진다. 


 4) general log 사용하기 

show variables like 'general%';
set global general_log=on;
set global log_output='TABLE';
set global general_log=off

2. Slow log

실행된 쿼리 중 오래 돌았던 쿼리를 기록. 

남기는 기준은 long_query_time 파라미터 (min 0, default 10)

사용여부 : slow_query_log = 0 disable, 1 enable
파일이름 : slow_query_log_file=file_name

general log 와 마찬가지로 log_output 이 table 일 경우 CSV 엔진을 사용하여 *.CSV 파일을 만든다. 

size 가 커질 경우 조회

3. Bin/relay log 

data 를 변경하는 statement 를 기록. select 는 포함되지 않는다.

복제서버 구조에서 source 쪽의 binlog 를 target 에 전달하면 target 의 relay log 에 동일한 포맷으로 기록된다.

로그포맷 형식에는 아래 3가지가 있다. 

  - Statement-Based Logging (SBL)

  - Row-Based Logging  (RBL) 

  - Mixed

binmysqlbinlog

4. Error log 

mysqld 를 운영, 중지, 시작시 만나게 되는 에러를 기록 

 - 기동관련 정보성 및 에러메시지
 - 비정상종료 복구 메세지
 - 쿼리처리 도중 발생 에러메시지 
 - aborted connection
    max_connect_errors
    
 - innodb모니터링, show engine innodb status 결과 
 - mysql 종료메시지

이렇게 정리해놓고 봐도 알수있는게 mysql 은 네이밍에 일관성이 없는 것이 특징이다.  

자주 사용되는 시스템 뷰

pg_tables: 데이터베이스의 모든 테이블 정보 
pg_views: 모든 뷰에 대한 정보 
pg_indexes: 데이터베이스의 모든 인덱스 정보 
pg_stats: 플래너(query planner)가 사용하는 통계 정보 
pg_settings: 서버 설정 파라미터에 대한 정보 
pg_roles: 데이터베이스 롤(사용자 및 그룹)에 대한 정보 
pg_user: 데이터베이스 사용자 정보 

추가적인 유용한 시스템 뷰

pg_locks: 현재 보유 중인 락(lock)에 대한 정보 
pg_stat_activity: 현재 실행 중인 쿼리와 세션에 대한 정보 
pg_prepared_statements: 준비된 구문(prepared statements)에 대한 정보 
pg_available_extensions: 사용 가능한 확장(extensions)에 대한 정보 
pg_cursors: 현재 열려있는 커서에 대한 정보 
pg_file_settings: 설정 파일의 내용 요약을 제공 
pg_matviews: 모든 물리화된 뷰(materialized views)에 대한 정보 

# 테이블 권한 부여하기 

select *
  from pg_tables
 where schemaname='public'
   and tablename like 'abc%'
;

select *
  from pg_views
 where schemaname='public'
   and tablename like 'abc%'
;

   
select *
  from information_schema.role_table_grants
 where grantee='USER1'
   and table_name like 'abc%'
 
grant select on table public.abc to "USER1";

 
   
   

* dbeaver 유용한 단축키 
 
- 한줄주석 : 블럭잡고 ctrl + / 
- 쿼리 여러줄 실행 : 블럭잡고 alt + x
- 실행계획 확인 : ctrl + shift + E 

* 기타 유용한 기능

- 행번호 표시 : 에디터 맨좌측에서 우클릭
-system object (mysql) 보기 : 커넥션이름에서 우클릭 -> connection view -> show system objects
-테이블 정보 : ctrl 누르고 마우스 커서를 테이블 이름 위에 
- 디비 연결정보 옮기기 : 파일 -> 내보내기,
다른pc에서 가져오기, .dbp파일 선택 -> set active project

SWAP 은 리눅스 커널에서 나타나는 정상적인 메커니즘이다. 

DB 서버의 경우, 클라이언트의 요청으로 데이터를 찾을때, 캐시에서 못찾은애들 디스크에서 퍼오게 되는데
메모리에 공간이 없으면 기존 메모리에 오래 상주한 애들을 swap영역으로 flush 함으로써 메모리를 확보하게 된다. 

위의 사진을 보면 DBLoad 가 튀는 순간에 swapUsage 가 올라가는데 dbload 의 압박으로 스왑이 사용된것으로 해석된다.

스왑 사용으로 일시적으로 떨어진 freememory 가 다시 올라오는 모습이다. 

 스왑은 더 많은 메모리를 확보하기 위한 리눅스 커널의 정상적인 활동이지만 스왑 공간은 물리적 메모리가 아니기에 스와핑 작업이 계속해서 일어나면 성능적으로 불리해진다. 

메모리가 부족한건 아닌지 확인하거나 해당 workload 의 SQL 을 확인해본다. 

실무에서 지금까지는 대부분 엔터프라이즈 버전(EE) 를 주로 사용해왔다. 

개인공부 용도로 사용하는 것도 무료이니 EE 만 써왔고, 기업에서도 메인서비스는 RAC 를 사용하기 때문에 그래왔다.

굳이 제약이 있는 Standard Edition 을 쓸 일이 없었는데..  클라우드가 대세가 되면서 이것에도 변화가 조금 생겼는데..

대부분 mysql 로 전환하지만, 기존시스템을 완전히 버리지 못해 오라클을 일부는 남기지만 EE 대신 SE 를 쓴다는 것이다. 

물론 비용적 이유인데... DBA 입장에서는 당연히 제약많은 SE 보다 EE 가 좋지만 고객사의 니즈와 의중을 안 이상 

적응해야지.. 어쩔도리가 없다. 

각설하고 오라클 SE, EE 의 큰 차이점과 그로인한 이슈를 어떻게 회피할수 있는지 알아보자

1. 파티션 테이블

월별테이블 생성 + 뷰 

2. AWR 

 statspact 사용

3. 인덱스 온라인 

 별도 테이블 생성 + 리네임 + 데이터 후보정 작업

여기까지는 그래도 대안이 있다. 반면 다음의 feature 들은 대안이 없기에 SE 로 넘어갈때 신중히 고려해야 한다. 

4. 압축
5.  SQL 병렬 작업 

https://docs.oracle.com/cd/E55822_01/DBLIC/editions.htm#DBLIC109

https://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/evaluate-downgrading-oracle-edition/compare-features.html

# SE 버전에 따른 cpu 제한 

1) Standard Edition One (SE1):
     2 CPU까지 확장 가능한 서버에 설치 가능
2) Standard Edition (SE):
     4 CPU까지 확장 가능한 서버에 설치 가능
3) Standard Edition 2 (SE2):
    Oracle Database 12c Release 1 (12.1.0.2) 이상부터 도입됨.
    2 소켓까지 사용 가능하며, 최대 16 CPU 스레드로 제한됨.
   RAC(Real Application Cluster) 구성 시, 2개의 서버 각각 1 소켓 8 CPU 스레드만 사용 가능

스태츠팩 리포트를 생성하고, 이를 로컬에 다운받는 방법을 알아보자.

1. 스태츠팩 리포트 생성 

select snap_id, snap_time
  from stats$snapshot
 where snap_time > trunc(sysdate) -6
order by 1
;

begin rdsadmin.rds_run_spreport('start snapshot number', 'end snapshot number');
end;

2. AWS 콘솔에서 다운받기

- 콘솔에 로긴후  "로그 및 이벤트" 탭

아래 로그쪽에 'spreport' 를 입력..

이름이 일치하는 파일이 나오면 클릭하여 오른쪽 다운로드 버튼 클릭

* 보안 등의 이유로 다운이 되지 않는 다면 아래 유틸로 툴에서 확인가능하다. 

select text
  from table(rdsadmin.rds_file_util.read_text_file('DIR_NAME','FILE_NAME'))
;
  
 

아마존 AWS 프리티어 계정으로 EC2 생성하기
EC2 는 Elastic Compute Cloud 의 약자이며, RDS 는 DBaas 로 DB만 서비스로 제공해주는데 EC2 는
OS만 깔린 서버를 제공해준다. 
 

 
정확하게는 저장된 AMI (amazon machine image)  이미지를 사용하여 서버를 제공해주기에 매우 빠른시간에(수초) 서버 사용이 가능해진다. 
 

1. AWS 프리티어 계정에 로긴

프리티어 계정으로 로긴하자
서비스옆에 네모박스 클릭해서 자주가는 서비스를 별표 클릭하여 즐겨찾기 해놓자

2. EC2 인스턴스 시작 

상단의 EC2 를 클릭한 후 '인스턴스 시작' 을 클릭 

 
AWS 에서는 생성된 가상머신 하나 하나를 인스턴스라고 부르는데, EC2 인스턴스를 생성하자.
이름은 적당히 하나 정해서 만들자. 
이미 저장된 AMI 로 인스턴스를 생성할건데 나는 우분투 를 선택했다. 

 
인스턴스 유형은 디폴트를 선택했다. 

"새 키 페어 생성" 을 눌러 새로 키 페어를 생성한다.
팝업창에서 이름만 새로 지정해주고 생성한다.

 네트웍설정은 vpc 랑 서브넷은 기존에 있는거 잡아주고 퍼블릭 ip 만 활성화로 설정하자

소스유형은 내 ip를 알면 내IP 로 잡는게 좋다. 
 
스토리지는 프리티어는 최대 30까지 사용가능하다. 

 
인스턴스 시작을 눌러 ec2인스턴스를 시작하자.

 
3. EC2 에 연결하기 
AMI 로 인스턴스를 시작하는 것은 순식간이다. 
이제 생성된 인스턴스에 연결하자. 

 
 - EC2 instance connect 

콘솔에서 바로 연결하는 방법도 있다. 우측 하단의 연결 버튼을 클릭하자.

정상적으로 접속이 된 화면이다. 
 
- SSH 클라이언트
SSH 클라이언트로 연결하자. 

아까 키페어를 생성한게 있는데 그걸로 접속을 할것이다.
MobaXterm 이라는 툴로 ec2 에 연결하자. 없으면 설치하자. 무료다 

 
아까 생성한 키페어를 터미널 폴더에 카피한다.  

콘솔에 나온 명령어 ssh -i "ec2-xxxx" 카피해서 실행하면 접속이 된다. 

무과금으로 AWS RDS mysql 생성하기

본 포스팅의 목적은 무과금으로 AWS RDS db인스턴스를 생성함에 있다. 

실제 DB를 테스트 용도로 사용하려면 세부 옵션들의 변경이 필요함을 숙지하자. 

- 프리티어 가입

- RDS 생성

로그인 후 '서비스 옆 네모박스' -> 데이터베이스 -> RDS 클릭  

* RDS 옆에 별표시를 클릭해서 즐겨찾기 등록을 하자

 '데이터베이스 생성' 버튼을 누르자

아직 생성된 인스턴스가 없어서 0 개로 표시된다. 

표준생성 선택후  DBMS 는 mysql 로 선택을 하자 

엔진버전은 뭘 해도 상관없지만 나는 8.0.33 을 선택했다. 

샘플템플릿은 '프리티어'를 선택했다. 

DB 인스턴스 식별자를 적어준다. 

master 사용자 이름은 dbadm 으로 넣어줬다. root 를 사용하던 편한대로 넣어준다. 

프리티어에서 버스터블 클래스 만 선택이 가능하다. 

디폴트인 db.t3.micro 를 선택했다.  

과금이 무서우니까 스토리지 자동조정은 꺼놓자. 

아마존은 무서운 놈들이다. 

연결설정은 로컬pc에서 붙을수 있게 퍼블릭액세스에 '예' 를 선택한다.

데이터베이스를 생성 하자. 

추가 기능에 대한 팝업창이 뜬다. 

ElastiCache , RDS 프록시를 사용할것을 추천해준다. 

지금은 필요가 없으니 '닫기' 버튼을 누른다. 

화면으로 돌아오면 상태가 '생성중' -> '백업중' -> '사용가능'  으로 최종 완료된다. 

- client tool 로 접속하기 

위의 화면에서 db식별자를 클릭하면 아래 화면으로 이동한다. 

엔드포인트와 포트정보를 확인한다. 

DBeaver 에서 information_schema, performance_schema 보이게 하기. 

디비버 설치 직후 디폴트 상태에서는 information/performance schema 가 아래처럼 보이지 않습니다. 

그것을 보려면 아래처럼 따라하면 됩니다. 

커넥션에서 우클릭 -> Connection view -> show system objects 선택

선택후 재접속하면 Databases 아래에 information/performance schema 가 보이게 됩니다. 

이상 끗

mysql 접속시 유저/패스워드 입력안하고 로긴하는 방법을 알아보자 

lee@mysql-m:~$ 
lee@mysql-m:~$ mysql_config_editor print --all
lee@mysql-m:~$ 
lee@mysql-m:~$ mysql_config_editor set --login-path=rootconn --host=localhost --user=root --password
Enter password: 
lee@mysql-m:~$ 
lee@mysql-m:~$ 
lee@mysql-m:~$ ls -al
합계 48
drwxr-xr-x  6 lee  lee  4096  1월 16 21:43 .
drwxr-xr-x  3 root root 4096  1월 13 01:00 ..
-rw-------  1 lee  lee   106  1월 14 19:46 .Xauthority
-rw-------  1 lee  lee  1416  1월 16 17:57 .bash_history
-rw-r--r--  1 lee  lee   220  1월 13 01:00 .bash_logout
-rw-r--r--  1 lee  lee  3771  1월 13 01:00 .bashrc
drwx------ 11 lee  lee  4096  1월 13 01:29 .cache
drwx------ 11 lee  lee  4096  1월 14 18:21 .config
drwx------  3 lee  lee  4096  1월 13 01:14 .gnupg
drwxr-xr-x  3 lee  lee  4096  1월 13 01:14 .local
-rw-------  1 lee  lee   136  1월 16 21:44 .mylogin.cnf
lee@mysql-m:~$ mysql_config_editor print --all
[rootconn]
user = "root"
password = *****
host = "localhost"
lee@mysql-m:~$ 

mysql_config_editor 명령으로 credential 을 생성하면 유저 홈 디렉토리에 ".mylogin.cnf" 파일이 생성이 된다.

이후부터는 mysql 로긴시 패스워드를 입력할 필요없이 login-path 옵션을 붙여주면 된다.