반응형

지난 innobackupex 명령어를 단순히 따라 하는 것에서 벗어나


하나씩 저희 회사에 맞게 변경을 하고 있습니다.


이게 진정 회사를 위한 공부겠죠..?

각설하고...


지난 명령어는 아래와 같습니다.



[root@DBTEST02:/root]# innobackupex --user=DB유저명 --password=비밀번호--socket=/tmp/mysql_8003.sock  /home/backup/game/



나름 저희 회사에 맞게 변경한다고 한 것이지만 여기서 빼놓고 간 부분이 있습니다.

위와 같이 진해을 하면 Default DB 의 위치를 backup 한 다는 것을..


잠시 /etc/my.cnf 를 확인해 보면


[mysqld]

user            = mysql

basedir         = /usr/local/mysql

datadir         = /home/DATABASE/Default_DB

tmpdir          = /usr/local/mysql/DB/tmp


datadir 가 Default DB 위치다.


그래서 위의 innobackupex 를 하면 port 가 8003 인 곳의 datadir 가 아닌 Default 위치의 데이터가 백업 되는 것을 확인할 수 있다.


그렇다면, 어떻게 진행해야 될까?


아래와 같이 defaults-file 를 선언해 주면 된다.


[root@DBTEST02:/root]#innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock /home/backup/game/


여기서 --defaults-file 은 앞쪽에 위치해야 한다. 혹시나 해당 변수를 뒤에다 선언하면 에러가 발생한다.


xtrabackup: Error: --defaults-file must be specified first on the command line


제대로 백업이 되는지는 로그만 봐도 알 수 있다.

아래 보면 해당 위치의 DB까지 모두 백업 되는 것을 확인할 수 있다.



[root@DBTEST02:/home/backup/game]# innobackupex --defaults-file=/home/DATABASE/InnoDB/Game2/ --user=root --password= --socket=/tmp/mysql_8003.sock --databases=DARKEDEN /home/backup/game/

160920 10:52:00 innobackupex: Starting the backup operation


IMPORTANT: Please check that the backup run completes successfully.

           At the end of a successful backup run innobackupex

           prints "completed OK!".


160920 10:52:00  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql_8003.sock' as 'root'  (using password: NO).

160920 10:52:00  version_check Connected to MySQL server

160920 10:52:00  version_check Executing a version check against the server...

160920 10:52:00  version_check Done.

160920 10:52:00 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: /tmp/mysql_8003.sock

Using server version 5.6.32-debug-log

innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2)

xtrabackup: uses posix_fadvise().

xtrabackup: cd to /home/DATABASE/InnoDB/Game2/

xtrabackup: open files limit requested 0, set to 1024

xtrabackup: using the following InnoDB configuration:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = ./

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

InnoDB: Number of pools: 1

160920 10:52:00 >> log scanned up to (21174002)

xtrabackup: Generating a list of tablespaces

InnoDB: Allocated tablespace ID 442 for DARKEDEN/ShoulderArmorInfo, old maximum was 0

160920 10:52:01 >> log scanned up to (21174002)

160920 10:52:02 >> log scanned up to (21174002)

160920 10:52:03 >> log scanned up to (21174002)

160920 10:52:04 [01] Copying ./ibdata1 to /home/backup/game/2016-09-20_10-52-00/ibdata1

160920 10:52:04 >> log scanned up to (21174002)

160920 10:52:05 >> log scanned up to (21174002)

160920 10:52:05 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/ContributeDegree.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/ContributeDegree.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/OustersWristletObject.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/OustersWristletObject.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/SubInventoryInfo.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/SubInventoryInfo.ibd

160920 10:52:06 [01]        ...done

160920 10:52:06 [01] Copying ./DARKEDEN/HighEnchantLog.ibd to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/HighEnchantLog.ibd

...

160920 10:52:10 [01] Copying ./DARKEDEN/VampireETCInfo.frm to /home/backup/game/2016-09-20_10-52-00/DARKEDEN/VampireETCInfo.frm

160920 10:52:10 [01]        ...done

160920 10:52:10 Finished backing up non-InnoDB tables and files

160920 10:52:10 [00] Writing xtrabackup_binlog_info

160920 10:52:10 [00]        ...done

160920 10:52:10 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '21174002'

xtrabackup: Stopping log copying thread.

.160920 10:52:10 >> log scanned up to (21174002)


160920 10:52:10 Executing UNLOCK TABLES

160920 10:52:10 All tables unlocked

160920 10:52:10 Backup created in directory '/home/backup/game/2016-09-20_10-52-00'

MySQL binlog position: filename 'Bin_Log.000051', position '120'

160920 10:52:10 [00] Writing backup-my.cnf

160920 10:52:10 [00]        ...done

160920 10:52:10 [00] Writing xtrabackup_info

160920 10:52:10 [00]        ...done

xtrabackup: Transaction log of lsn (21174002) to (21174002) was copied.

160920 10:52:11 completed OK!



incremental 백업도 비슷하게 진행하면 될 것 같다.


반응형
반응형

Xtrabackup 을 사용하는 방법에 대해 테스트 해 보았다.

마음도 심숭생숭하여 빨리 진행될 일을 너무 느긋하게 진행 되었다.


InnoDB 를 이용한 백업 으로써 Incremental backup 도 함께 해 보았다.

Incremental 에 대한 개념은 Oracle Rman 백업과 같은 개념으로 생각해도 될 것 같다.


Full Backup 의 경우 데이터의 양이 증가할 수록 시간도 오래 걸린다.

이에 따라, 변경된 데이터만 백업을 함으로서 백업의 시간을 획기적으로 단축할 수 있다.


이후 복구 시에는 Full backup 으로 복구 후 나머지 변경된 데이터만 Incremental 백업을 가지고 적용함으로써 

무손실로 복구를 할 수 있다.


물론 이 개념들은 모두 Oracle Rman 백업 / 복구 때의 지식으로 Xtrabackup 의 경우 다를 수도 있다.


추가적으로 MySQL LSN 이 있는데

* lsn 이 log sequence number 의 약자로써 전체 DB의 버전 정보를 뜻함

  - 변경 사항이 발생할 때마다 번호가 증가하여 채번

* Oracle로 따져보면 SCN 와 비슷

  (DB Link를 많이 사용하다 보면 SCN 관련하여 full 이 차서 DB down 되는 현상이 있엇던 것이 기억난다...11g 등에서 어느정도 해결되었다고는 했는데..)



1. Full Backup


* innobackupex 명령어를 이용하여 user 와 passwd 를 입력하여 진행

* Port를 따로 지정한 경우나, 멀티로 띄우는 경우 --socket 옵션을 지정하여 명시

* /home/backup/game/  으로 어느 곳에 백업을 할 것인지 명시를 하게 되면, 자동으로 '2016-09-09_16-58-17'  처럼 날짜 시간으로 디렉토리 자동생성

* config 파일을 지정시 --defaults-file=MY.CNF 처럼 명시 가능


[root@DBTEST02:/root]# innobackupex --user=DB유저명 --password=비밀번호--socket=/tmp/mysql_8003.sock  /home/backup/game/

....


160909 16:58:19 Executing UNLOCK TABLES

160909 16:58:19 All tables unlocked

160909 16:58:19 Backup created in directory '/home/backup/game/2016-09-09_16-58-17'

MySQL binlog position: filename 'Bin_Log.000049', position '7396794'

160909 16:58:19 [00] Writing backup-my.cnf

160909 16:58:19 [00]        ...done

160909 16:58:19 [00] Writing xtrabackup_info

160909 16:58:19 [00]        ...done

xtrabackup: Transaction log of lsn (1600627) to (1600627) was copied.

160909 16:58:19 completed OK!


2. Preparing a full backup with innobackupex


* 백업을 생성한 후에 해당 백업 데이터는 아직 restore 할 수 있는 상태가 아님. 
* rollback 하는 commit되지 않은 transaction이나 재실행 되어야 하는 transaction이 있을 수 있음
* 미완된 작업들을 정리하는 작업을 함으로서 데이터 파일의 일관성을 유지하는 작업을 Prepare작업
* 해당 작업이 정상적으로 완료 되면 백업된 데이터 파일들은 이제 복구용으로 사용 가능

* innobackupex를 사용하여 백업 파일의 일관성을 맞추기 위해 --apply-log 옵션을 사용하면 된다. 백업된 위치를 옵션으로 주어 작업을 진행한다.

* Prepare 작업 동안에 백업이 진행되는 동안에 생성된 Transaction 내용을 담은 로그 파일안에 커밋된 Transaction 들을 재실행을 한다. 그리고, 그것들 중에 커밋 구문이 실행되지 않은 transaction들은 롤백된다.
이 작업을 완료하게 되면, 모든 정보들은 테이블 스페이스에 저장되고, 새로운 로그 파일이 생성된다.

내부적으로는 xtrabackup --prepare 를 2번 실행하게 된다. 
Prepare 작업 시에 좀 더 빨리 진행하고 싶다면 --use-memoryh 옵션을 사용할 수 있다.(Default 100mb)

* innobackupex --apply-log --use-memory=4G /home/backup/game/2016-09-09_16-58-17

※ Transaction 의 일관성을 유지하기 위해서 innobackupex 의 Full backup을 진행한 후 완료된 작업에 대해(생성된 백업 디렉토리) --apply-log 옵션을 추가하여 한번 더 진행하면 파일의 일관성을 맞출 수 있다.


* 아래는 apply-log 를 진행 한 후의 메시지 들이다.


3. Incremental backup

- Incremental backup에 대한 개념은 초반에 살짝 언급한 바와 같이 백업 이후의 변경된 내용들에 대해서만 백업을 진행

- 기준은 LSN 의 정보를 보고 추적하여 진행


* --incremental-basedier 의 경우 마지막으로 full backup 한 위치에 대한 데이터 내용을 뜻하는 것으로, 해당 내용들을 확인 후 incremental backup을 진행하기 위한 기본 정보이다.


* 풀백업이든 증분 백업이든 최종 위치를 작성 후에 진행해야지만, 증분백업을 제대로 진행할 수 있다.


* innobackupex --incremental --user=DB유저ID --password=DB유저 패스워드 --socket=/tmp/mysql_8003.sock  /home/backup/game/ --incremental-basedir=/home/backup/game/2016-09-12_10-59-37/


* incremental 백업을 완료 하게 되면 xtrabackup_checkpoints 파일이 생기는데 해당 내용을 확인해 보면 아래와 같다

backup_type = incremental
from_lsn = 1601102
to_lsn = 1601102
last_lsn = 1601102
compact = 0
recover_binlog_info = 0

* 혹시라도 lsn 을 알고 있다면, 해당 번호가지고 수동으로 incremental 를 진행할 수 있다.

(시작되는 번호를 명시하면 자동으로 마지막까지 진행 한다.)

innobackupex --incremental --user=DB유저ID --password=DB유저 패스워드 --socket=/tmp/mysql_8003.sock  /home/backup/game/ --incremental-lsn=1600627

[root@DBTEST02:/home/backup/game/2016-09-12_11-26-40]# ll
total 108
-rw-r----- 1 root root   418 Sep 12 11:26 backup-my.cnf
-rw-r----- 1 root root 81920 Sep 12 11:26 ibdata1.delta
-rw-r----- 1 root root    44 Sep 12 11:26 ibdata1.meta
-rw-r----- 1 root root    23 Sep 12 11:26 xtrabackup_binlog_info
-rw-r----- 1 root root   117 Sep 12 11:26 xtrabackup_checkpoints
-rw-r----- 1 root root   563 Sep 12 11:26 xtrabackup_info
-rw-r----- 1 root root  2560 Sep 12 11:26 xtrabackup_logfile
[root@DBTEST02:/home/backup/game/2016-09-12_11-26-40]# vi xtrabackup_checkpoints

backup_type = incremental
from_lsn = 1600627
to_lsn = 1601102
last_lsn = 1601102
compact = 0
recover_binlog_info = 0

* backup_type = incremental 이라고 적혀 있으며 lsn 관련된 내용을 보면 위치를 확인할 수 있다.
* 반드시 백업이 완료 된 후 


* incremetal 실행 결과 xtrabackup_checkpoints  파일을 확인해 보는 습관이 필요할 듯 싶다.






해당 문서는 아래 블로그를 참고로 하였습니다.


http://ani2life.com/wp/?p=1290


http://mysqldba.tistory.com/240


반응형
반응형

현재까지 우리회사 DB는 순전히 python으로 coldbackup 으로 일일 백업 및 주간 백업은 Coldbackup & MySQLDump 를 이용한 schema 백업을 해왔다.

이것이 가능한 이유는 매주 한번씩 정기점검을 진행하고, Slave 로 일일 백업 및 복구하여 조회용으로 했기에 가능했다.


하지만 5.6.32로 버전 업 및 InnoDB로 업그레이드 예정이라 이제는 다른 식으로 백업을 진행하는 것이 좋지 않을까 고민해 보다,

XtraBackup 에 대해 알게되어 테스트해 본다.


해당 Percona XtraBackup 의 경우 무료다.

아래는 Percona에서 제공하는 MySQL backup 툴과 자신들의 XtraBackup 의 차이점에 대해서 나열한 것이다. (내가 무료란것만 살짝 강조를...ㅋㅋ)

무료 및 Open Source 이며, MySQL 및 MariaDB 까지 지원되는 것이 강점이며, Linux 만 가능한게 단점이 아닐까 싶다.ㅠ


https://www.percona.com/doc/percona-xtrabackup/2.4/intro.html

MySQL Backup Tool Feature Comparison

FeaturesPercona XtraBackupMySQL Enterprise backup
LicenseGPLProprietary
PriceFreeIncluded in subscription at $5000 per Server
Streaming and encryption formatsOpen sourceProprietary
Supported MySQL flavorsMySQL, Percona Server, MariaDB,Percona XtraDB Cluster, MariaDB Galera ClusterMySQL
Supported operating systemsLinuxLinux, Solaris, Windows, OSX, FreeBSD.
Non-blocking InnoDB backups [1]YesYes
Blocking MyISAM backupsYesYes
Incremental backupsYesYes
Full compressed backupsYesYes
Incremental compressed backupsYes 
Fast incremental backups [2]Yes 
Incremental backups with archived logs feature in Percona ServerYes 
Incremental backups with REDO log only Yes
Backup locks [8]Yes 
Encrypted backupsYesYes [3]
Streaming backupsYesYes
Parallel local backupsYesYes
Parallel compressionYesYes
Parallel encryptionYesYes
Parallel apply-logYes 
Parallel copy-back Yes
Partial backupsYesYes
Partial backups of individual partitionsYes 
Throttling [4]YesYes
Backup image validation Yes
Point-in-time recovery supportYesYes
Safe slave backupsYes 
Compact backups [5]Yes 
Buffer pool state backupsYes 
Individual tables exportYesYes [6]
Individual partitions exportYes 
Restoring tables to a different server [7]YesYes
Data & index file statisticsYes 
InnoDB secondary indexes defragmentationYes 
rsync support to minimize lock timeYes 
Improved FTWRL handlingYes 
Backup history tableYesYes
Backup progress table Yes
Offline backups Yes
Backup to tape media managers Yes
Cloud backups support Amazon S3
External graphical user interfaces to backup/recoveryZmanda Recovery Manager for MySQLMySQL Workbench, MySQL Enterprise Monitor


사실 설치를 진행하면서 yum으로 설치는 하지 말자라고 생각 했지만...

결국은 패키지 의존성 체크 및 key 관련되서 애를 먹어 그냥 yum 으로 설치 하였다.




설치 방법은 아래 참고하면 된다.

https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html


나는 아래에서 rpm 다운을 받은 뒤 진행했다.

https://www.percona.com/downloads/XtraBackup/


Yum으로 설치


$ yum localinstall percona-xtrabackup-24*


* key 관련하여 에러메시지는


1.  vi /etc/yum.repos.d/percona-release.repo


2.  맨 하단에 아래 추가
############################################
# XTRABACKUP #
############################################

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
gpgcheck = 1

3. 해당 파일 확인 /etc/pki/rpm-gpg/RPM-GPG-KEY-Percona
없으면, 생성후 아래 내용 추가 (참조 : https://www.percona.com/downloads/percona-release/RPM-GPG-KEY-percona )

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.9 (GNU/Linux)

mQGiBEsm3aERBACyB1E9ixebIMRGtmD45c6c/wi2IVIa6O3G1f6cyHH4ump6ejOi
AX63hhEs4MUCGO7KnON1hpjuNN7MQZtGTJC0iX97X2Mk+IwB1KmBYN9sS/OqhA5C
itj2RAkug4PFHR9dy21v0flj66KjBS3GpuOadpcrZ/k0g7Zi6t7kDWV0hwCgxCa2
f/ESC2MN3q3j9hfMTBhhDCsD/3+iOxtDAUlPMIH50MdK5yqagdj8V/sxaHJ5u/zw
YQunRlhB9f9QUFfhfnjRn8wjeYasMARDctCde5nbx3Pc+nRIXoB4D1Z1ZxRzR/lb
7S4i8KRr9xhommFnDv/egkx+7X1aFp1f2wN2DQ4ecGF4EAAVHwFz8H4eQgsbLsa6
7DV3BACj1cBwCf8tckWsvFtQfCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg
kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce
rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN
eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE
ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79
Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5
Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm
lHkwGqEqfbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72
O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG
chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt
caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ
b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFqFTqA0UW2gWQ
cKFN4Q6NPV6IW0rV61ONLUc0VFXvYDtwsRbUmUYkB/L/R9fHj4lRUDbGEQrLCoE+
/HyYvr2rxP94PT6Bkjk/aiCCPAKZRj5CFUKRpShfDIiow9qxtqv7yVd514Qqmjb4
eEihtcjltGAoS54+6C3lbjrHUQhLwPGqlAh8uZKzfSZq0C06kTxiEqsG6VDDYWy6
L7qaMwOqWdQtdekKiCk8w/FoovsMYED2qlWEt0i52G+0CjoRFx2zNsN3v4dWiIhk
ZSL00Mx+g3NA7pQ1Yo5Vhok034mP8L2fBLhhWaK3LG63jYvd0HLkUFhNG+xjkpeI
SQQYEQIACQUCSybdoQIbDAAKCRAcTL3NzS79KlacAJ0aAkBQapIaHNvmAhtVjLPN
wke4ZgCePe3sPPF49lBal7QaYPdjqapa1SQ=
=qcCk
-----END PGP PUBLIC KEY BLOCK-----



* 이마저도 안된다면 과감하게 yum update 후 진행을 해 보는 것도 좋은 방법이다.



$ yum update

-- 꽤나 시간이 오래 걸려도 나름 이후에는 한방에 되는 경우도 있다.


$ yum list | grep percona

...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.2-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.2-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...

$ yum install percona-xtrabackup


참고 https://www.percona.com/doc/percona-xtrabackup/2.2/installation/yum_repo.html

반응형
반응형

[출처] http://www.mimul.com/pebble/default/2013/12/07/1386399914136.html



대용량 테이블을 운용하는 경우 도움이 될만한 아티클 "Ten ways to improve the performance of large tables in MySQL"이 있어, 이 블로그에 정리해 봅니다.
아래 가지 10가지 방법을 잘 고민해 보면 좋은 도움을 받을 수 있을 것 같습니다.

오늘은 성능 문제를 일으키는 원인이 되는 대용량 테이블의 성능을 개선하는 방법을 살펴본다. 여기의 조언들은 일부는 많은 테이블을 가진 큰 데이터베이스에 적용할 수 있지만 대부분의 경우는 개별적으로 특별히 큰 테이블이 더 큰 문제라는 것이다.

테이블의 내용 변경의 속도는 테이블 사이즈가 커질수록 줄어드는 것을 일반적으로 알려진 사실이다. 아래는 B+Tree 인덱스의 성능을 시계열로 보여주는 그래프이다.



위의 그래프는 MySQL@Facebook에서 포스팅한 것이다. 이것은 insert buffer를 해제한 상태(물론 추천 하지는 않고, 데모 목적)에서 테이블에 10억 행을 insert했을 때의 성능 저하를 나타낸 것이다. 이 벤치 마크는 iibench도구를 사용했고, TokuTek에서 디자인한 것이다.

그래서 우리는 이러한 성능 저하가 인덱스 구조에 의한 것이라고 생각했지만, 그래도 우리는 이 곡선을 급격하게 내려가지 않고, 옆으로 길게 늘어지도록 할 수 있는 방법을 모색해 봐야 한다.

대용량 테이블의 성능에 영향을 줄일 수 있는 10가지 방법

1. MyISAM 대신에 InnoDB를 사용하자. MyISAM은 테이블의 마지막에 insert하는 경우에는 속도가 빠르지만, 테이블 잠금(update 및 delete에 제한 되지만)이 있고, 데이터를 디스크에서 읽기와 쓰기를 할 때 경합 때문에 키 버퍼를 보호하기 위해 싱글락을 사용한다. 또한 후술하겠지만, 체인지 버퍼 기능을 가지고 있지 않다.

2. InnoDB는 유니크하지 않은 보조 인덱스의 빌딩을 지연시키는 체인지 버퍼 기능(이전에 insert buffer라고 불리던 기능)을 가지고 있다. 이에 대한 자세한 것은 Facebook의 노트에 기술되어 있다. 이것은 위의 그래프에는 보여지지 않지만, insert의 성능을 상당히 빠르게하는 것으로, 기본적으로 활성화되어 있다. 이 기능은 MySQL 5.5에서 좋게 개선 되었기 때문에, 만약 업그레이드 하지 않는 경우에는 즉시하는 것이 좋다.

3. 파티셔닝은 인덱스의 크기를 작게하여 테이블 자체를 효율적으로 작게 나눌 수 있게 된다. 또한, MySQL 5.7.2 DMR에서 상당히 개선된 내부적인 인덱스 잠금(index->lock) 경합(contention)도 줄여 준다.

4. InnoDB의 압축 기능을 사용하자. 몇몇 부하 종류의(특별히 많은 char/varchar/text형 컬럼이있는 경우) 압축 기능은 데이터를 압축해 성능 저하의 곡선을 완만하게 해준다. 또한, 일반적으로 용량이 작은 SSD를 사용해도 된다. InnoDB의 압축 기능은 Facebook에서 제공한 여러가지 패치 덕택에 MySQL 5.6에서는 크게 개선 되었다.

5. 정렬후 대용량의 데이터를 테이블에 로드해라. 정렬된 데이터를 인서트하는 것은, 페이지 분할(메모리 상에 없는 테이블에서 성능은 악화되는)이 작게 될 것이고, 대용량 데이터의 로드는 테이블의 용량과는 특별히 관계가 없지만, redo 로그의 압축 부하를 줄여주는데 도움을 준다.

6. 테이블에서 불필요한 인덱스를 지우자. 체인지 버퍼 기능을 비활성화시키는 UNIQUE 키를 특히 주의하자. 제약 조건을 사용할 이유가 없는 경우, UNIQUE 키를 사용하지 않고 일반적인 INDEX를 사용하자.

7. 5, 6에서 관련된 PRIMARY KEY의 종류도 중요하다. 성능 저하를 빠르게 만들어버리는 GUID와 같은 데이터 타입보다, INT나 BIGINT를 사용하자. PRIMERY KEY가 없는 것도 성능에 부정적인 영향을 준다.

8. 새 테이블에 대용량 데이터를 로드할 경우 PRIMARY KEY가 아닌 인덱스는 나중에 만들자. 모든 데이터가 로드된 후 인덱스를 만든다면, InnoDB는 pre-sort와 및 대용량 로드 프로세스(빠르고 인덱스가 좀 더 콤팩트한 인덱스를 만드는)를 적용 할 수 있게 된다. 이 최적화는 MySQL 5.5에서 이루어졌다.

9. 메모리가 많으면 많을수록 도움을 받을 수 있다. 최근의 메모리의 실제 가격을 비교해 보면 새로운 데이터 베이스 서버에 너무 적은 메모리를 적용하는 것을 자주 볼 수 있다. 간단한 조언을 해 보면, SHOW ENGINE INNODB STATUS의 결과에서 BUFFER POOL AND MEMORY의 reads/s의 보여주고(읽고 있음을 나타냄), Free buffers(이것도 BUFFER POOL AND MEMORY 아래에 있다)의 수가 0이면 메모리를 더 늘리면 혜택이 얻을 수 있다.(innodb_buffer_pool_size를 잘 최적화했다는 가정하에. 이 문서를 참고).

10. 메모리 뿐만 아니라, SSD도 도움이 된다. 그래프의 곡선이 하향이 되는 이유는 테이블이 커져서 일어나는 IO 속성 때문이다. 하드 디스크가 초당 200 오퍼레이션(IOPS)을 수행하는데 반해, 일반적인 SSD는 20000 IOPS 이상 수행이 가능하다.

용어 정의

  • Change Buffer: MySQL 5.5이전에는 insert buffer라고 했으며, 이름 그대로 INSERT의 성능을 향상시키기 위한 버퍼다. insert buffer는 새로운 행을 삽입하는 INSERT에 대해서만 유효했는데, Change Buffer는 UPDATE 및 DELETE도 좋은 효율성을 보여준다.


반응형
반응형

Stored Procedure ....SP라고도 무르는 것 같은데......

Anyway...장단점에 대해서 정리해 본다.


Stored Procedure 는 Oracle DBA로 재직하면서 끊임없이 나와 마주했던 친구다.

내가 처음부터 끝까지 다 작성한 것은 많지 않지만..문제 발생 시 개발자와 같이 확인하고..Invaild로 상태가 바뀌었는지 체크하고

바뀌었으면 왜 바꼈는지...누가 해당 부분을 수정했는지 확인했다.


그리고 때로는 튜닝 요청관련해서 몇백줄 몇천줄 나의 능력을 뛰어넘는 놈을 이겨볼 꺼라고 아둥바둥 거렸다.


각설하고 MySQL에서는 Stored Procedure 라고 명명 한다.


장점


- DB 보안 향상

  -> 자체적인 보안 설정 기능을 가지고 있으며, Stored program 단위로 실행 권한을 부여할 수 있다.

  -> 세밀한 권한 제어가 가능

  -> SQL 인젝션과 같은 기본적인 보안 사고는 피할 수 있다.

  -> SQL 의 문법적인 취약점을 이용한 해킹이 어렵다


- 기능의 추상화

  ex) 여러 테이블에 걸쳐 유일한 일련번호를 발급하되, 일련번호에 자체적인 헤더 값과 시간 정보를 덧붙여서 생성

  -> 이러한 요구사항을 해결하기 위해서는 단순 Table 시퀀스(auto increment)를 사용하지 못하는데 Stored procedure 이용하면 해결가능


- 네트워크 소요 시간 절감

  -> 하나하나의 쿼리가 아주 가볍고 빠르게 처리될 수 있다면 네트워크를 경유하는 데 걸리는 시간이 문제가 될 수 있음

   (0.01만에 완료되는 쿼리가 0.1초의 네트워크 시간과  동일한 쿼리가 다량의 건수로 진행되는 경우 문제 소지)


- 절차적 기능 구현

  -> SQL 문장에 IF / While 과 같은 제어문장 사용 가능

    ( 어플리케이션 소스코드를 줄여줄 수 있음)


- 개발 업무의 구분

  -> 스토어드 프로그램을 만들어 API 처럼 제공하여 업무 구분


단점


- 낮은 처리 성능

  -> 다른 DBMS에 비해 Stored Procedure 프로그램은 성능이나 최적화가 부족하여 수행 능력이 떨어짐(오라클 pl/sql보다 2배 떨어짐)

  -> 문자열이나 숫자 연산을 위해 사용하기에는 나쁜 선택

  -> 하지만 한 번에 많은 쿼리를 실행해야 할 때 효율적임


- Application 코드의 조각화

 ??????이해가 되지 않기에 패스..


출처 : 개발자와 DBA를 위한 MySQL 서적


- 나의 생각 : 나는 Stored procedure 를 많이 만들지 않았지만 분명 여러가지로 유용한 부분이다.

Oracle로 비유하자면 많은 쿼리들이 Library cache에 등록이 되고 공유가 된다. 이 때 대,소문자 구분 및 바인드변수 미사용 등으로 동일한 쿼리이지만 전혀 다른 쿼리로 인식하여 실행계획이 공유되지 않는 상황이 발생된다. 이말은 즉슨, 동일한 쿼리로 인식하지 않기에 매번 하드파싱이 일어나 조금이라도 부하를 주게 된다. MySQL로 돌아와서 비슷한 것이라고 생각된다. MySQL에서 Stored procedure를 이용하게 되면 많이 사용되는 쿼리일 수록 부하는 줄게 될 것이고 성능적으로 향상될 것이다.

물론, 개발자들이 쿼리 규칙을 잘 지킨다면 걱정없겠지만...그게 아니라면 SP를 사용하는 것이 좋다고 생각든다.


몇주전에 어느 담당자가 SP는 MySQL 에 부하가 되기 때문에 사용을 지양 한다고 했었다.

나는 그 말에 동의 하지 않는다....

SP를 잘 사용한다면 많은 이득을 볼 수 있을 것이라고....


어디까지나 나의 생각일 뿐이다. 허접한...ㅠ

반응형
반응형

Warning: Using a password on the command line interface can be insecure.

출처 : http://xinet.kr/tc/341


확인 필 : http://myblog.opendocs.co.kr/archives/1591


이렇게 해도 난 뜨는것 같은데...ㅠㅠ

다시 해보기로....



Using a password on the command line interface can be insecure

 mysql 5.6 버전으로 올라가면서 패스우드 정보를 쉘 상태에서 입력하게 되면 위와 같이 에러 메세지가 발생된다 
(실제 에러 메세지가 아닌 경고 메세지이다 )

그럼 이 메세지를 나오지 않게 구성하려면 어떻게 해야 하나?

mysql 5.6 버전에 새로 생긴 mysql_config_editor 툴을 이용하여 로그인 파일을 저장해 놓고 해당 파일을 옵션으로 지정하여 사용

1. 파일 생성하기

[root@localhost ~]# /usr/local/mysql/bin/mysql_config_editor set --login-path=xinet --host=localhost --user=xinet --password
Enter password: (암호입력)

2. 암호화 파일 확인
 암호화 파일은 /root 폴더에 존재한다. 파일 이름은 .mylogin.cnf

이 파일은 암호화 되어 있어서 그냥은 볼수 없다 

그러면 등록된 user가 어느 유저인지 확인하는 명령어가 따로 있다

[root@localhost ~]# /usr/local/mysql/bin/mysql_config_editor print --all
[xinet]
user = xinet
password = *****
host = localhost


3. 접속하기

[root@localhost ~]# /usr/local/mysql/bin/mysql --login-path=/xinet -e "\s"
--------------
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.6.22, for Linux (x86_64) using  EditLine wrapper
Connection id:          1004
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.22-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 1 hour 38 min 28 sec
Threads: 125  Questions: 2906400  Slow queries: 0  Opens: 1532  Flush tables: 1  Open tables: 112  Queries per second avg: 491.943


이렇게 확인할수 있다 

4. 로그인 정보 특정 유저 삭제하기

[root@localhost ~]# /usr/local/mysql/bin/mysql_config_editor remove --login-path=xinet


이제 응용해서 만들어보면 되겠다. 


반응형
반응형
MySQL Replication(복제)

MySQL Replication(복제)  개나 2 이상의 MySQL database server(slave) 하나의 MySQL database server(master) 부터 데이터를 복제해   있는 기능을 제공한다.

MySQL Replication 비동기 방식으로 처리된다 slave master로부터 데이터를 받아 복제하기 위해 항상 master 연결되어 있을 필요가 없다.

 

MySQL Replication Binary logging mechanism 사용하여 이뤄진다.

Master 서버는(MySQL 인스턴스) binary log 변경된 데이터 정보를 기록하며  log slave 읽어서 실행함으로써 복제가 된다.

 

Master에서 binary logging 활성화되면 Master 모든 데이터 구문이 bindary log 저장되며 slave bindary log 모든 내용을 복사해서 읽어온다.

따라서 slave log 파일내의 position 유지할 필요가 있다그래야 로그파일 전체를 처음

부터 읽지 않고 효과적으로 로그 파일을 운영할  있다.

여기서 position 로그파일내 위치를 의미하며 어느 부분부터 읽겠다는 것을 의미 한다.

 

Configuration 따라 다음과 같은 단위로 복제가 이뤄질  있다.

l  all database

l  selected database

l  selected tables within a database

 

 

 

Replication 구성  – Appendix A.


 

Replication Configuration

 

[ Master Configuration ]

1.  Replication User 생성
slave
 master 접속하여 데이터를 복제하기 위한 MySQL 계정이 필요하다. root 사용해도 상관 없지만 slave Replication 설정을 하면(slave configureation 참조) 계정 정보가 암호화되지 않은 텍스트 형태로 slave 서버의 master.info(mysql\data) 파일에 기록이 되기 때문에 보안상 root 기타 계정을 사용하는 것을 권하지 않는다따라서 복제를 위한 계정을 하나 생성한다.
 계정은 단지 REPLICATION SLAVE Privilege 있으면 되므로 다음과 같이 계정을 생성한다.(REPLICATION SLAVE Privilege 있으면 된다는 의미는 INSERT/UPDATE 등과 같은 Privilege 필요 없다는 의미이다따라서 복제 계정은 mysql query실행을  없다.)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';

è  repl 계정이며 slavepass 계정의 비밀번호 이다.

è  %대신 IP주소를 넣으면  IP로부터 접속하는 slave 대해서만 접속을 허용하겠다는 의미(그냥 % 사용하자..!)

n  ) ….. ON *.* TO 'repl'@'1.1.1.2' IDENTIFIED BY 'slavepass';

 

2.  Configuration 설정(my.ini 또는 my.cfg)

[mysqld]

log-bin=mysql-bin

server-id=1

è  server-id 1~(2^32)-1내의 숫자중 아무것이나 설정해도 된다.

è  log-bin=mysql-bin는 바이너리 로그파일의 생성경로이며 log-bin만 기입시 기본 디렉토리에 생성된다.

3.  MySQL 데몬 재시작

 

4.  Master 정보 보기

mysql> FLSUSH TABLES WITH READ LOCCK;

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |       98   |                 |                     |

+------------------+----------+--------------+------------------+

è  File : 로그 파일을 의미한다.

è  Position : 로그 파일내 읽을 위치

è  Binlog_Do_DB : binary log 파일(변경된 이벤트 정보가 쌓이는 파일)

è  Binlog_Ignore_DB : 복제 제외 정보

n  Binlog_Do_DB Binlog_Ignore_DB Slave 시작하기 전까지는 나타나지 않는다.


 

[ Slave Configuration ]

1.  Configuration 설정

[mysqld]

server-id=2

replicate-do-db=’database name’


# master-host=xxx.xxx.xxx.xxx  --> 마스터의 IP

 master-user=xxxx  --> 리플리케이션용 ID

 master-password=xxxx  --> 패스워드

 master-port=3306  --> 접속할 포트번호 (일반적인 포트 3306)

è  slave server-id 정의한다 1~(2^32)-1내의 숫자중 아무것이나 설정해도 되나 Master와는 다르게 한다.

è  replicate-do-db: 복제할 데이터베이스를 의미한다.

n  2 이상의 데이터 베이스 복제를 원하면 replicate-do-db  추가한다.

 

2.  database dump

복제할 데이터베이스를 master로부터 dump하여 넣는다.

 

3.  CHANGE MASTER TO

Master 연결하기 위한 정보를 다음과 같이 설정한다.

mysql> CHANGE MASTER TO

MASTER_HOST='Master server host name or Master server IP',

MASTER_USER='replication user',

MASTER_PASSWORD='replication password',

MASTER_LOG_FILE='Log File name',

MASTER_LOG_POS=position;

è  MASTER_HOST: Master 서버의 정보를 입력한다.

è  MASTER_USER: replication 위해 생성한 계정 ID

è  MASTER_PASSWORD: replication 위해 생성한 계정 비밀번호

è  MASTER_LOG_FILE: SHOW MASTER STATUS에서 보이는 로그 파일 

è  MASTER_LOG_POS: SHOW MASTER STATUS에서 보이는 position

n  SHOW MASTER STATUS master에서 실행해야 한다.(master 설정 참고)

 

4.  MySQL 데몬 재시작

 

 Slave 실행이 되면(MySQL 데몬 시작 또는 slave start) master 접속하기 위한 정보를 master.info(mysql\data)에서 읽어 온다만일 master.info 아무런 정보가 없으면 my.ini 참고하여 master.info 연결정보를 기록한다.

여기서 주의할 점은 이미 master.info 정보가 있으면 my.ini 참조하지 않으므로 my.ini정보를 수정해도 master 연결시 반영되지 않는다.

그러나 CHANGE MASTER TO 이용하면 master.info 바로 변경한다.

따라서 master 연결정보는 my.ini 설정하는  보다는 CHANGE MASTER TO 이용하여 설정하는게 낫다.

 

다음과 같은 option CHANGE MASTER TO에서 사용된다.

master-host

master-user

master-password

master-port

master-connect-retry

master-ssl

master-ssl-ca

master-ssl-capath

master-ssl-cert

master-ssl-cipher

master-ssl-key

 

 

 

[양방향 동기화 처리]

Master서버에서 Slave 구현하고자 한다면 다음과 같은 방법으로 처리

1. 현재 Slave서버에 replication 계정 생성

2. 현재 Slave my.ini 변경

log-bin=mysql-bin 추가

3. 현재 SLAVE 서버 데몬 재시작

4. 현재 Master my.ini 변경

replicate-do-db=’database name’추가

4. 현재 Master에서 CHANGE MASTER TO 실행

5. 현재 Master 서버 데몬 재시작

 

 


 

Replication Monitoring

//ON Master

mysql> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 11

User: repl

Host: 192.168.1.22:3556

db: NULL

Command: Binlog Dump

Time: 21960

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Slave 192.168.1.22 repl계정으로 thread11 연결되어 있음을 보여준다.

 

 

//On Slave

mysql> show processlist\G

*************************** 1. row ***************************

     Id: 1

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 23049

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 2

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 4294967289

  State: Has read all relay log; waiting for the slave I/O thread to update it

   Info: NULL

Id 1: Master 서버와 통신하기 위한 I/O Thread

Id 2: update 내용을 처리하기 위한 SQL Thread

 2개의 Thread 오류가 발생하면 안된다.

 

 

mysql> show slave status\G;

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.14

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000004

        Read_Master_Log_Pos: 9187563

             Relay_Log_File: shin-relay-bin.000013

              Relay_Log_Pos: 9187700

      Relay_Master_Log_File: mysql-bin.000004

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: ipm3

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 0

                 Last_Error:

               Skip_Counter: 0

        Exec_Master_Log_Pos: 9187563

            Relay_Log_Space: 9187700

            Until_Condition: None

             Until_Log_File:

              Until_Log_Pos: 0

         Master_SSL_Allowed: No

         Master_SSL_CA_File:

         Master_SSL_CA_Path:

            Master_SSL_Cert:

          Master_SSL_Cipher:

             Master_SSL_Key:

      Seconds_Behind_Master: 0

Slave_IO_State: 현재 Slave 상태를 나타낸다.(Appendix B 참고)

Slave_IO_Running: I/O Thread 상태

Slave_SQL_Running: SQL Thread 상태

Last Error: 최근에 발생한 오류정상인 경우  값은 없다.

Seconds_BeHind_Master:  값이 크면 클수록 Master로부터 복제할  없는 데이터가 많음을 나타낸다.

 

 

mysql> stop slave

mysql> start slave

MySQL 데몬(서비스)  시작하면 slave 자동으로 시작된다.(my.ini 옵션 skip-slave-start 있으면 자동 시작 안한다.)



[기타 알아두어야 할 내용]

1. 안정성을 위해서 두대의 DB서버의 버전을 동일하게 맞추어 주는것이 좋다.

2. 버전이 다른경우 높은 버전은 Slave 만 가능하다.

3. 서버는 Master 를 먼저 시작한 후 Slave를 시작시킨다.

4. Master의 status 에 지정된 File 이외의 로그파일은 삭제해도 무방하다.

5. Master의 status의 Position과 Slave status의 Read_Master_Log_Pos는 동일해야 한다.

6. 서버환경, 계정이 바뀐후에는 데이터 디렉토리안의 master.info를 변경하거나 제거한 후 재시작을 해야한다.

7. Master의 Replication로그나 Slave의 relay-bin로그는 vi로는 읽을수 없으며 아래와 같은 방법으로 변환후에 읽을 수 있다.


# mysqlbinlog KRDAC1FLD001-relay-bin.000002 > log.sql

8. Insert 이후 Slave 동기화 도중 데이터 조회가 이루어 져야 하는 상황에는 해당 경우만 Master DB에서 보게 하거나 process 종료 후 sleep 타임을 잠시주어 해결하면 된다



1. 구성도



MySQL 리플리케이션은 이중화의 역할 보다는 부하 분산의 역할을 한다고 볼 수 있다. 이 구성은 데이터베이스의 데이터를 갱신(입력/수정/삭제 등) 및 조회(검색 등)하는 비율에 따라 도입 유무를 판단 할 수 있다. 물론, MySQL 리플리케이션 도입에 가장 이상적인 서비스는 대형 포털 사이트와 같은 조회형 서비스에 적합하다.


2. 슬레이브 서버 별로 데이터 분산



MySQL 리플리케이션에서 슬레이브 서버마다 데이터를 동일하게 가져갈 수 있지만 이처럼 각각의 데이타를 가지고 있을 수 있다. 이와 같이 데이터를 나눠 주는 이유는 부하 분산의 차원이다.


3. 이중마스터 서버



MySQL 리플리케이션에 이중 마스터 서버 구성으로 하는 이유는 슬레이브 서버로 인한 마스터의 서버의 부하를 분산하는 차원이다. 여기서 마스터2 서버는 각 슬레이브에 대해서 마스터 서버의 역할을 수행하긴 하나 마스터1 서버에 대해서는 슬레이블 서버의 역할을 하고 있다.


4. 장애 대처



MySQL 정상적인 서비스가 진행 중이다. 평시에는 슬레이브 서버 중 하나를 백업용으로 활용하는 것도 좋다.


5. 장애복구



MySQL 리플리케이션 서비스 중에 마스터 서버에 장애가 발생하였다면, 슬레이브 서버 중 하나를 마스터 서버로 전환을 하여 서비스를 정상적으로 복구 할 수 있다. 추후 장애가난 마스터 서버가 정상화 되면 슬레이브 서버 중 하나로 역할을 수행하게 만든다.



출처 : http://hanaduri.egloos.com/2389708



반응형
반응형

점검하면서 쿼리를 작성 중 재밌는 일이 생겨서 공유해 본다.


select gender from employees group by gender;

select distinct(gender) from employees;


둘 다 gender의 상태(??갑자기 설명이 안되네;;;) 를 확인하기 위해서 동료가 group by로 작성하는 것을 보았다.


순간...왜 저렇게 해야되는 걸까? 간단하게 distinct 를 하면 되지 않느냐로 내가 질문하면서 궁금해서 찾아보게 되었다.


난 성능적인 차이가 더 궁금해서 찾아본 결과 다음과 같이 확인할 수 있었다.


동일한 로직을 이용하기에 성능의 차이는 없을 거지만,

group by는 정렬를 하기에 distinct가 조금 더 성능적으로 빠르다.


라는 결과를 얻을 수 있었다.

하지만 이거는 어디까지나 사람들의 의견만 보았다.


그래서 간단하게 테스트를 아래와 같이 진행 하였다.

아직 MySQL 의 쿼리 튜닝할 정도의 실력이 아니기에..간단하게 explain 으로 확인 하였다.



보는바와 같이 동일한 건수를 참조하며, 마지막에 Using filesort 라는 것을 확인할 수 있다.


Using filesort


쿼리에 ORDER BY가 포함되어있는 경우 DBMS 내부적으로 sort를 한다.(MySQL의 경우 퀵소트 알고리즘을 사용)

소트를 처리하기 위해서 메모리공간을 사용하며, 메모리가 부족할 경우 임시파일을 사용한다.

(임시파일은 HDD등의 저장장치에 생성되며 PC에서 가장 느린 성능을 보여주는 부분이다. 

-> 결과적으로 EXPLAIN시 Extra필드에 Using filesort가 표시된다면 이는 파일을 사용할 가능성이 크다는 것이며, 

가장 느린성능을 보여줄것이다.)


하지만 인덱스 순으로 행을 fetch할수 있으면 Filesort는 필요없게된다. 

이런경우 당연히 Extra필드에도 Using Filesort가 표시되지 않는다.

ORDER BY 구문에 인덱스가 사용되도록 쿼리나 테이블을 튜닝하자.


여기까지는 하나의 테이블에서 ORDER BY를 사용한 경우이다.


2개 이상의 테이블을 JOIN해야 하며 결과는 정렬되어 있어야 하는경우엔 어떻게 될까?

이경우엔 3가지 유형이 있다.


1. 첫 테이블을 인덱스를 이용하여 정렬하고, JOIN하는 경우

2. 첫 테이블을 FileSort해서 Join하는 경우 -> Using filesort

3. JOIN후 Filesort하는 경우(JOIN한 결과는 인덱스가 없으므로 JOIN후 Sort할때는 항상 Filesort이다.) -> Using Temporary, Using filesort

 

뒤로 갈수록 처리가 무거워 지므로 1,2번의 실행계획이 잡히도록 유도하는 것이 좋다.

1번을 유도하는 방법 -> WHERE절의 검색조건과 ORDER BY절의 소트조건을 하나의 테이블에 집중시킨다.

출처 : http://blog.daum.net/billbo/11


비록 적은 중복값에 대해서는 차이가 없을지 모르겠으나 그 값이 많아 진다면 group by로 해서 확인하는 것은 부하를 줄 수도 있을 것 같다.


자세한 Filesort 내용은 아래의 위치에서 확인해 보자

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01_1&m_no=22505&cat1=827&cat2=963&cat3=980&lang=k


sort를 하는 부분은 tmep를 사용하기에 가급적 sort할 필요가 없다면 distinct 를 사용해서 확인하는 것이 좋을 듯 싶다.


오랜만에 아키텍처까지 한번 더 볼 수 있는 좋은 기회였다.


반응형
반응형

Bin Log가 쌓이게 되면 디스크가 증가하기에 이에 대해서 방안을 파이썬으로 지우는 스크립트를 분석하면서

이와 관련된 블로그 찾아서 포워딩해 본다


우리 회사에서는 아래와 같은 쿼리를 사용한다


PURGE MASTER LOGS TO '%s'


%s 에는 binlog의 번호를 확인한다.(파일에서 ls 로 확인)


하지만 서버에서도 확인이 가능하다.


show master logs;


알아서 정렬되어 나오며, 특정 번호를 선정하면 그 하위 번호까지 모두 삭제가 된다.


ex) mysql> purge master logs to 'mysql-bin.001000';

지정된 바이너리 로그 이하의 파일이 삭제됨.
mysql-bin.000001~0000999 까지 삭제됨.


참조 : http://www.ischo.net/mysql/2875


  MySQL Binary Log 는 add, delete, insert, update 등의 query 가 저장되어 있는 파일로서 MySQL 을  설치하게 되면 기본적으로 MySQL Binary Log 가 생성됩니다.
 Binary Log 를 쌓지 않아도 되는 MySQL 구동 환경에서는 Binary Log 를 삭제하므로, 디스크 공간
 여유 공간을 확보할 수 있는데, MySQL Binary Log 를 지우는 방법에 대해서 알아보겠습니다.

1. MySQL Replication 환경에서 지우기

  - MySQL Replication MASTER 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

  - MySQL Replication SLAVE 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

2. MySQL Binary Log sequence number 또는 특정 일자로 지우기

  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS TO 'mysql-bin.000015';
  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';

3. mysqladmin flush-logs 명령어를 통해서 MySQL Binary Log 지우기

   shell> mysqladmin -u root -p flush-logs

4. MySQL Binary Log 생성을 방지하는 방법

  /etc/my.cnf 파일에서 아래 라인을 주석 처리
  log-bin

5. MySQL Binary Log 를 특정 1주일까지만 생성 및 보관하기
  /etc/my.cnf 파일에서 아래 라인을 추가

  expire_logs_days = 7


출처 : http://faq.hostway.co.kr/Linux_DB/1307

반응형
반응형
업무를 하고 중간에 내가 하고 싶었던 일을 하다 보니 한번에 모든 것이 정리되는 것이 아니라

띄엄띄엄 정리를 하게 된다.


그러다 보니 어제는 분명히 완벽히 되던 것이 오늘 진행하다 보면 진행이 안되는 경우가 생긴다


어제까지 완벽히 Install 이 진행 되었었는데, MySQL 에 로그인까지 되는 것을 확인 했다.


하지만 다음날 DB를 내렸다가 다시 올리게 되면 로그인은 커녕 MySQL 이 시작도 안되는 현상이 발생 되었다.



맨 처음에 언급했듯이 해당 서버는 많으 사람들이 이용하는 개발 DB이다.

물론 개발자들을 위해서 멀티로 DB를 설치해 주고 또한 나도 여기서 필요시에 다른 버전을 설치한다.


그러다보니 3.x 버전부터 5.0x 버전 지금의 5.6.31까지 몇개의 MySQL 엔진이 설치되어 있다.


당연히 my.cnf 도 몇개가 있다.


참조 : http://jmkjb.tistory.com/entry/MySQL5621SetupSourceCompile


여기서 보면 service를 등록하는 부분이 있다.


나 또한 등록하여 진행을 하는데,

- cp /usr/local/rmysql/support-files/mysql.server /etc/init.d/mysqld


- vi /etc/init.d/mysqld

아래 내용수정

basedir=/usr/local/rmysql

datadir=/usr/local/rmysql/DB/data


- chkconfig mysqld on

- chkconfig --list mysqld

 

6. MySQL 서비스 시작


- service mysqld start


여기까지 따라한 후 service 를 시작하면 다음과 같은 에러를 발견할 수 있다.



ㅎㅎㅎㅎㅎㅎ여기에 보면 이상한 숫자들과 경로들을 볼 수 있다.

이 부분들은 내가 mysqld 파일을 디버깅하면서 적어 놓은 것들이다....ㅠㅠ


다음은 실행하면서 떨어진 devDB.err 이다



전의 포스팅한 내용에는 분명 rmy.cnf 에는 해당 skip-locking 이라는 설정이 전혀 있지 않다.


상사의 말 한마디에 my.cnf (3버전에서 쓰는 것) 을 확인해 보았다.



ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

나랑 장난치냐 지금 너!!!!!!!

ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ


 vi /etc/init.d/mysqld


그래......여기를 수정하면 가능하겠다 싶어 수정하였다.


 echo $echo_n "Starting MySQL"

    if test -x $bindir/mysqld_safe

    then

      # Give extra arguments to mysqld with the my.cnf file. This script

      # may be overwritten at next upgrade.

      $bindir/mysqld_safe --defaults-file=/etc/rmy.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?


빨간색으로 수정해 주었다.

이것을 처음부터 알았다면 고생 안했을 일을....


친구야...우리 앞으로 잘 지내자....ㅠ


나처럼 my.cnf 위치를 수정해서 쓰는 분들은 꼭 확인해 보세요....


이로써 나는 또 초급이란 것을 티내고 있다.....


이제는 3버전에서 5.6으로 Migration 하는 부분을 올릴 예정이다.

테스트는 금방 할 것 같지만...테스트 데이터는 3버전에서 사용하는 것들이라 많은 부분을 수정해 줘야 할 듯 싶다.


Oracle에서 심심하면 했던 Migration.....새삼 그립다....ㅎㅎ

여기서도 패러럴이 먹히면 좋겠지만.....

반응형

+ Recent posts