블로그 이미지
OSSW(Open Source System SoftWare

calendar

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            

Notice

2014.11.28 11:19 2. DBMS이야기/02. MySQL


MySQL 5.6 파티션 삭제시 Exchange Partition 기능 활용 방안




로그성 데이터를 파티션 테이블로 보관하는 경우가 많은데, 보관주기가 지난 파티션을 drop 하는 경우에 MySQL 에서는 전체 파티션에 테이블 락이 걸리는 문제가 있다. 

물론 파티션의 물리적인 파일과 내부 메타 데이터 갱신 정도만 발생하기에 테이블 락 유지 시간이 길지는 않지만, 서비스 성격에 따라 문제가 되는 경우도 있다.

 

MySQL 5.6 버전부터는 파티션 Exchange 기능이 지원되는데, 이 기능을 사용하면 테이블 락으로 인한 영향을 최소화하면서 파티션 삭제를 할 수 있다.

테스트를 위해 하루에 3200만건(테이블스페이스 파일 사이즈 10GB) 정도 데이터가 저장되는 생성되는 일자별 파티션 테이블을 생성해 보았다.


mysql> show create table ptest\G

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

       Table: ptest

Create Table: CREATE TABLE `ptest` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `regdt` date NOT NULL DEFAULT '0000-00-00',

  `pad` char(255) DEFAULT NULL,

  PRIMARY KEY (`id`,`regdt`)

) ENGINE=InnoDB AUTO_INCREMENT=167108865 DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE ( to_days (regdt) )

(PARTITION p20140420 VALUES LESS THAN (735709) ENGINE = InnoDB,

 PARTITION p20140421 VALUES LESS THAN (735710) ENGINE = InnoDB,

 PARTITION p20140422 VALUES LESS THAN (735711) ENGINE = InnoDB,

 PARTITION p20140423 VALUES LESS THAN (735712) ENGINE = InnoDB,

 PARTITION pMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

 

mysql> select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME='ptest';      

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

| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |

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

| ptest      | p20140420      |   32934515 |

| ptest      | p20140421      |   32939678 |

| ptest      | p20140422      |   32935000 |

| ptest      | p20140423      |   32935471 |

| ptest      | pMAXVALUE      |          0 |

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

5 rows in set (0.01 sec)

 



MySQL 데이터 디렉토리에서 파일을 조회해 보면  파티션별로 10GB 정도의 테이블 스페이스가 생성된 것을 확인할  있다.

-rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:03 ptest#P#p20140420.ibd

-rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:08 ptest#P#p20140421.ibd

-rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:34 ptest#P#p20140422.ibd

-rw-rw---- 1 seuis398 seuis398 9.9G 2014-04-25 22:39 ptest#P#p20140423.ibd

-rw-rw---- 1 seuis398 seuis398  96K 2014-04-25 22:39 ptest#P#pMAXVALUE.ibd

 



이 상태에서, 파티션 DROP 방식으로 2014-04-20일의 파티션을 삭제했을 때, 해당 파티션의 테이블 스페이스(10GB) 삭제 작업과 테이블의 메타 정보 갱신에 대략 0.6초 정도 소요되는 것을 확인할 수 있었다. 

10GB 정도의 파일을 삭제하는 작업은 ext4 파일 시스템에서는 크게 문제가 되지 않는 작업이라 다행이 0.6초만에 처리가 되긴 했지만, 만약 ext3 파일 시스템을 사용하고 있었거나 월별 파티셔닝 등으로 파티션의 사이즈가 더 컸다면 파티션 삭제에 소요되는 시간은 더 늘어나게 되고, 이때는 락으로 인한 문제가 발생하게 된다.

mysql> alter table ptest drop partition p20140420;

Query OK, 0 rows affected (0.59 sec)

Records: 0  Duplicates: 0  Warnings: 0 

 



MySQL 5.6 버전에서는 파티션 Exchange 기능을 사용해서 락으로 인한 영향을 최소화하는 것이 가능하다.

// 테스트 테이블의 파티션 1개와 동일한 구조의 테이블을 생성

mysql> create table empty_ptest like ptest; 

Query OK, 0 rows affected (0.07 sec) 

 

mysql> alter table empty_ptest remove partitioning;   

Query OK, 0 rows affected (0.17 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

// 위에서 생성한 빈 테이블과 삭제한 파티션(20140421)을 Exchange

// 잠시 테이블 락이 걸리겠지만 물리적인 파일 삭제가 발생하지 않으므로 파티션 사이즈에 관계없이 소요시간이 매우 짧다. 

mysql> alter table ptest exchange partition p20140421 with table empty_ptest;   

Query OK, 0 rows affected (0.04 sec)

 

mysql>  select TABLE_NAME, PARTITION_NAME, TABLE_ROWS from information_schema.PARTITIONS where TABLE_NAME='ptest';

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

| TABLE_NAME | PARTITION_NAME | TABLE_ROWS |

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

| ptest      | p20140421      |          0 |

| ptest      | p20140422      |   32209112 |

| ptest      | p20140423      |   32184620 |

| ptest      | pMAXVALUE      |          0 |

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

4 rows in set (0.00 sec)

 

// 불필요한 데이터 일괄 삭제

// 파티션은 이미 빈 껍데기만 남은 상태이므로 삭제 작업이 매우 빠르다.

mysql> alter table ptest drop partition p20140421;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> drop table empty_ptest;

Query OK, 0 rows affected (0.58 sec) 

 



Posted By 김득은






자료출처   :   http://seuis398.blog.me/70039224614    


posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.28 11:00 2. DBMS이야기/02. MySQL

MySQL 5.6 Parallel Replication (slave_parallel_workers)



MySQL Replication 구조는 Master에서 아무리 많은 쿼리를 처리하더라도 Slave가 Master의 처리량을 따라갈 수 없는 근본적인 문제를 가지고 있다.

이는 Master에서는 다량의 세션에서 동시에 쿼리를 수행하지만 Slave는 항상 Single Thread로 복제를 처리하기 때문인데, MySQL 5.6 버전에서는 이런 부분들을 조금 완화할 수 있는 parallel slave 기능이 도입되었다.

 

MySQL 5.6의 parallel slave는 database 단위로 별도의 worker thread가 생성되고, 기존 복제 처리를 하던 SQL Thread가 작업 관리 역할을 하면서 각 worker thread에게 작업을 할당해 주는 방식으로 처리된다.







mysql> set global slave_parallel_workers=4;


mysql> stop slave ; start slave;

...

mysql> show processlist;

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

| Id | User        | Host | db   | Command | Time | State                                                                       | Info             |

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

| 31 | system user |      | NULL | Connect | 6500 | Waiting for master to send event                                            | NULL             |

| 32 | system user |      | NULL | Connect | 5548 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |

| 33 | system user |      | NULL | Connect | 5592 | Waiting for an event from Coordinator                                       | NULL             |

| 34 | system user |      | NULL | Connect | 5592 | Waiting for an event from Coordinator                                       | NULL             |

| 35 | system user |      | NULL | Connect | 5594 | Waiting for an event from Coordinator                                       | NULL             |

| 36 | system user |      | NULL | Connect | 5587 | Waiting for an event from Coordinator                                       | NULL             |

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

 

mysql> show processlist;

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

| Id | User        | Host | db      | Command | Time | State                                            | Info                                                                                                 |

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

| 31 | system user |      | NULL    | Connect |  831 | Waiting for master to send event                 | NULL                                                                                                 |

| 32 | system user |      | NULL    | Connect |    0 | Waiting for Slave Workers to free pending events | NULL                                                                                                 |

| 33 | system user |      | sbtest1 | Connect |   31 | update                                           | INSERT INTO sbtest10(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'), |

| 34 | system user |      | sbtest4 | Connect |   30 | update                                           | INSERT INTO sbtest4(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'),( |

| 35 | system user |      | sbtest3 | Connect |   32 | update                                           | INSERT INTO sbtest4(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'),( |

| 36 | system user |      | sbtest2 | Connect |   32 | update                                           | INSERT INTO sbtest4(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'),( |

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





실제 어느 정도의 효과가 있는지 아래와 같은 시나리오로 테스트를 해보았다.

▶ sysbench로 테스트 database를 8개 생성 (sbtest1~8)

 sysbench oltp 테스트 부하를 sbtest1~8에 동시에 인입 =>  Master db 기준으로 초당 40,000~45,000 정도의 쿼리가 처리됨

▶ Slave db에서 slave_parallel_workers 파라미터를  0(기존방식), 2, 4, 8로 증가하면서 복제 처리량 측정 

 

테스트 결과 병렬 처리를 하지 않았을 때에 비해서 database 개수만큼 병렬 처리를 했을 때 거의 6배 정도의 복제 처리량이 증가하는 것을 확인할 수 있었다.

물론 부하모델에 따라서 수치는 차이가 나겠고, Master에 비해서는 여전히 느리게 복제가 반영되겠지만, 충분히 메리트 있는 기능으로 보인다.









다만 5.6 버전 기준으로 이 기능을 사용하기 위해서는 몇가지 유의해야 될 부분들이 있다.

(1) 당연한 얘기겠지만 database 단위로 worker thread가 생성되기 때문에, database가 1개인 경우에는 이득이 없다.

(2) worker thread는 트랜잭션을 고려하지 않고 반영을 하기 때문에 각 database가 독립적인 구조가 아니라 트랜잭션으로 묶여서 사용되는 환경에서는 Master-Slave 간 데이터 정합성을 보장할 수 없다.

(3) database 단위로 복제를 처리하지만, 한쪽 database에서 지연이 발생하면 전체 database에도 복제 지연이 발생하게 된다.  

     즉, 병렬 처리의 효과를 극대화하기 위해서는 각 database에 인입되는 부하가 균일해야 하며, 짧은 쿼리들 위주이어야 한다.

     (SQL Thread가 특정 worker thread에게 작업 할당을 해야 되는데, 해당 thread가 이전 작업을 완료하지 못했으면 SQL Thread는 대기)

(4) 5.6.16 버전까지는 특정 상황에서 parallel slave 기능을 사용했을 때 memory leak이 발생할 수 있다. (5.6.17에서 패치됨)

     http://bugs.mysql.com/bug.php?id=71197





※ 특정 database에 lock을 걸어서 의도적으로 복제 적용하지 못하도록 처리했을 때의 나머지 worker thread가 대기하는 상태


mysql> lock table sbtest1.sbtest write;


Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

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

| Id | User        | Host            | db      | Command | Time | State                                            | Info                                                                                                 |

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

| 31 | system user |                 | NULL    | Connect |  853 | Waiting for master to send event                 | NULL                                                                                                 |

| 32 | system user |                 | NULL    | Connect |    0 | Waiting for Slave Workers to free pending events | NULL                                                                                                 |

| 33 | system user |                 | sbtest1 | Connect |   37 | Waiting for table metadata lock                  | INSERT INTO sbtest(k, c, pad) VALUES (0,' ','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt'),(0 |

| 34 | system user |                 | NULL    | Connect |   32 | Waiting for an event from Coordinator            | NULL                                                                                                 |

| 35 | system user |                 | NULL    | Connect |   32 | Waiting for an event from Coordinator            | NULL                                                                                                 |

| 36 | system user |                 | NULL    | Connect |   32 | Waiting for an event from Coordinator            | NULL                                                                                                 |

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

 




Posted By 김득은






자료 출처  :  http://seuis398.blog.me/70039224614



posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.27 22:30 2. DBMS이야기/02. MySQL

 

 

 

MySQL filesort 알고리즘

 

 

ORDER BY/GROUP BY 처리에 인덱스를 사용하지 못하는 경우, MySQL은 Filesort 알고리즘을 통해 데이터를 정렬한다.
Filesort 알고리즘을 사용하게 되면 쿼리 실행플랜 Extra 필드에 Using filesort 구문이 나오게 되며, 이런 경우 대체로 쿼리 처리 비용이 증가하게 된다.

Filesort 동작 방식과 Filesort와 관련된 Variable, Status 항목을 정리해 보면 아래와 같다.

 

 

 

 

 

(1) 데이터 Block에 대한 Scan 및 WHERE Filter 처리후 조건에 맞는 row를 Sort Buffer에 저장
     정렬 조건 컬럼 값과 데이터 포지션 정보를 Buffer에 저장하며, Clustered Index 구조를 사용하는 InnoDB의 경우 PK값이 Buffer에 함께 저장됨

     (sort_buffer_size 환경 변수로 지정)

(2) Sort Buffer가 가득차면, Quick Sort 처리하여 결과를 별도의 임시 파일에 저장하며, 위 과정을 조건에 부합하는 모든 row를 다 읽을때까지 반복
      (임시 파일은 실제 물리적인 파일이 아님)

(3) 생성된 임시 파일들을 Merge
      Merge 횟수는 sort_merge_pass 상태 변수에 카운트 돠며, sort_buffer_size가 작은 경우, sort_merge_pass 상태 변수 값이 커짐

(4) Merge 완료된 결과는, 정렬 조건 순서대로 데이터 포지션 정보를 가지고 있음
      결과 파일 순서대로 데이터 Block을 읽는 경우 많은 Random I/O를 유발되므로, 
      별도의 버퍼(read_rnd_buffer 환경 변수)에서 결과 파일의 위치 정보를 정렬하여 Random I/O를 Sequential I/O로 유도

이 Filesort 알고리즘은 데이터 Block에 대한 Access가 중복으로 발생한다는 단점이 있다.
이 문제를 해결하기 위해 MySQL 4.1 이후부터는 개선된 Filesort 알고리즘을 추가로 사용한다.
Sort Buffer에서 정렬 수행시 데이터 포지션 뿐만 아니라, SELECT 쿼리가 요청한 컬럼의 내용도 같이 처리를 하는 것이다.
Sort Merge가 완료되면 추가적인 데이터 Access 없이 Result Set을 생성할 수 있다.

일반적인 경우에는 개선된 Filesort 알고리즘이 더 빠르지만,
추가 처리되는 컬럼의 크기가 길면, Sort Buffer의 활용도가 떨어지고 Sort Merge의 부담이 발생하여 오히려 기존 Filesort 알고리즘에 비해 더 느려지게 된다.
그래서 아래 조건을 만족하는 경우에만 개선된 Filesort 알고리즘이 사용되고, 그렇지 않으면 원래 Filesort 알고리즘이 그대로 사용된다.
  - SELECT 쿼리가 요청한 컬럼이 TEXT / BLOB이 아닐 것
  - SELECT 쿼리가 요청한 컬럼의 사이즈 합이 max_length_for_sort_data 환경 변수 값 이하 (기본값 1KB)

[결론]
가급적 Filesort를 피하는 것이 좋으나, 불가피하게 Filesort를 해야 하는 경우 불필요하게 SELECT 하는 컬럼을 줄이거나 불필요한 BLOB/TEXT 사용을 자제하면 성능 향상을 얻을 수 있다.

[유의사항]
sort_buffer_size가 불필요하게 큰 경우에는 오히려 성능 저하 발생 (default 값을 사용해도 무방)

 

 

 

Posted by  김득은

 

자료 출처 : http://seuis398.blog.me/70039224614

 

 

 

posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 17:19 2. DBMS이야기/02. MySQL
사용 사례: 실제 벤치마킹 시나리오와 Live 쿼리 캡처링.
지금까지 우리의 예에서, 우리는 원래 직원 데이터베이스에 대해 쿼리를 실행하고있다. 즉, 당신이 원하는하지 않을 확실히 뭔가 DBA가 있습니다. 그리고 그것을위한 좋은 이유가있다. 당신은 당신의 프로덕션 데이터베이스를로드 추가하지 않고 삭제, 업데이트 또는 생산 테이블에 데이터를 삽입 할 수있는 테스트 쿼리를 실행하지 않습니다.

우리는 어떻게 프로덕션 데이터베이스의 백업을 만드는 방법을 보여 그것은 테스트 환경에 복사합니다. 이 예에서는 동일한 서버에,하지만 당신은 이상적으로는 동일한 하드웨어 용량을 별도의 서버에 복사합니다.

더 중요한 것은, 우리는 어떻게 쿼리가 프로덕션 데이터베이스에서 살고 기록하고 테스트 스크립트에 추가하는 방법을 보여 드리겠습니다. 즉, 프로덕션 데이터베이스에서 쿼리를 얻을 수 있지만, 테스트 데이터베이스에 대한 테스트를 실행하는 것입니다.
다음과 같은 일반적인 단계는, 당신은 어떤 mysqlslap 테스트에 사용할 수 있습니다 :

1. 테스트 환경을 프로덕션 데이터베이스를 복사합니다.
2. 구성 MySQL은 기록하고 프로덕션 데이터베이스의 모든 연결 요청 및 쿼리를 촬영합니다.
3. 테스트하려고하는 유스 케이스를 시뮬레이션합니다. 당신이 쇼핑 카트를 실행하는 경우 예를 들어, 당신은 당신의 응용 프로그램에서 모든 적절한 데이터베이스 쿼리를 실행하기 위해 뭔가를 구매해야합니다.
4. 쿼리 로깅을 끕니다.
5. 쿼리 로그를 확인하고 테스트 할 쿼리의 목록을 확인합니다.
6. 테스트 할 각 쿼리에 대한 테스트 파일을 만듭니다.
7. 테스트를 실행합니다.
8. 데이터베이스 성능을 향상시키기 위해 출력을 사용한다.

시작하기 위해,이 직원 데이터베이스의 백업을 만들 수 있습니다. 우리는 백업을위한 별도의 디렉토리를 생성합니다 :
sudo mkdir /mysqlslap_tutorial/mysqlbackup

cd /mysqlslap_tutorial/mysqlbackup
백업을 생성하고 새 디렉토리로 이동 :
sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql

sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/
MySQL의 테스트 서버로 이동합니다.employees_backup 데이터베이스를 만듭니다 :
CREATE DATABASE employees_backup;
당신이 테스트를 위해 별도의 서버를 사용하는 경우이 시점에서, 당신은 employeesbackup.sql 그것에를 통해 파일을 복사해야합니다. 메인 터미널 세션에서 employeesbackup 데이터베이스로 백업 데이터를 가져옵니다
sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql
생산 MySQL 데이터베이스 서버, MySQL은 일반 쿼리 로그를 활성화하고 그것의 파일 이름을 제공합니다.일반 쿼리 로그는 MySQL 데이터베이스 인스턴스에 대한 연결, 분리 및 쿼리 활동을 캡처합니다.
SET GLOBAL general_log=1, general_log_file='capture_queries.log';
이제 생산 MySQL 서버에 테스트 할 쿼리를 실행합니다. 이 예에서는 명령 줄에서 쿼리를 실행합니다. 그러나, 당신은 당신의 응용 프로그램 대신 직접 실행 한 쿼리를 생성 할 수 있습니다. 당신이 테스트하려는 느린 과정 또는 웹 사이트 페이지가있는 경우에, 당신은 그 과정을 통해 실행하거나 지금 웹 페이지에 액세스해야합니다. 당신이 쇼핑 카트를 실행하는 경우 예를 들어, 데이터베이스 서버의 모든 해당 쿼리를 실행해야하는, 지금 체크 아웃 프로세스를 완료 할 수 있습니다.
이것은 우리가 생산 MySQL 서버에서 실행되는 쿼리입니다. 먼저 오른쪽 데이터베이스를 사용 :
USE employees;
SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date;
489903 rows in set (4.33 sec)
쿼리가 완료 될 때 우리는 일반적으로 로깅을 해제합니다 :

SET GLOBAL general_log=0;
당신이 로그인두면, 쿼리가 열심히 테스트 만들 수있는 로그에 추가 할 계속합니다. 그래서, 바로 시험을 마친 후에는 로그를 비활성화해야합니다. 의 로그 파일은 / var / lib 디렉토리 / mysql을 디렉토리에 생성 된 것을 확인하자 :
sudo ls -l /var/lib/mysql/capt*

-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log
우리의 MySQL의 test 디렉토리에이 파일을 복사 할 수 있습니다. 당신이 테스트를 위해 별도의 서버를 사용하는 경우, 해당 서버에 복사합니다.
sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/
이 로그 파일에있는 데이터의 꽤가 있어야합니다. 이 예제에서, 우리가 원하는 쿼리는 끝 부분에 있어야합니다.파일의 마지막 부분을 확인 :
sudo tail /mysqlslap_tutorial/capture_queries.log
         6294 Query show databases
         6294 Query show tables
         6294 Field List    departments 
         6294 Field List    dept_emp 
         6294 Field List    dept_manager 
         6294 Field List    employees 
         6294 Field List    salaries 
         6294 Field List    titles 
140930 15:34:52  6294 Query SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date
140930 15:35:06  6294 Query SET GLOBAL general_log=0
이 로그는 SQL 명령과 타임 스탬프를 보여줍니다.파일의 끝 부분에 SQL SELECT 문은 우리가 관심이있는 것입니다. 그것은 우리가 프로덕션 데이터베이스에서 실행 된 명령과 정확하게 일치해야합니다, 우리는 그것을 캡처 한 곳이기 때문이다.

이 예제에서, 우리는 이미 쿼리를 알고 있었다. 그러나 프로덕션 환경에서는이 방법을 사용하면 반드시 서버에서 실행되고 있는지에 대해 알고하지 않을 수 있습니다 쿼리를 찾는 데 매우 유용 할 수 있습니다.

당신이 실행 또는 로그인 할 때 다른 쿼리를 발생하는 경우,이 파일이 완전히 다른 모양 있습니다.실제 시나리오에서이 파일은 모든 다른 연결에서 오는 항목의 수백이 쇄도 할 수있다. 당신의 목표는 병목 현상을 일으키는 쿼리 또는 쿼리를 찾을 수 있습니다. 당신은 텍스트 쿼리를 포함 모든 라인의 목록을 만들어 시작할 수 있습니다. 그런 다음 쿼리를 테스트하는 동안 데이터베이스에서 실행 된 정확히의 목록을해야합니다.

테스트 할 각 쿼리를 들어,는 .sql 확장자로 끝나는 파일에 복사합니다.
sudo vi /mysqlslap_tutorial/capture_queries.sql
내용은 줄 바꿈없이 끝에 세미콜론없이 테스트 할 MySQL의 쿼리해야합니다 :
SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date
다음, 쿼리 결과가 캐시되지 않습니다 있는지 확인하십시오. 다시 테스트 MySQL의 세션으로 이동합니다. 다음 명령을 실행합니다 :
RESET QUERY CACHE;
이제 스크립트 파일로 mysqlslap 유틸리티를 실행하는 시간이다. 당신이 --query 매개 변수에 올바른 스크립트 파일 이름을 사용하십시오. 우리는 단지 10 개의 동시 연결을 사용하여 테스트를 두 번 반복합니다. 테스트 서버에서이 프로그램을 실행 :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose
Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1
그렇다면 우리는이 벤치 마크를 향상시킬 수 있습니까?

당신은 쿼리가 무엇을하고 있는지 평가하기 위해 MySQL의 쿼리 친숙의 일정 금액을해야합니다.

다시 쿼리에서 보면, 우리는 여러 테이블에서의 조인이 수를하고있어 볼 수 있습니다. 쿼리는 직원의 작업 기록을 보여주고 그렇게함으로써, 그것은 EMPNO 필드가 다른 테이블을 조인합니다. 또한 접합 용 DEPTNO 필드를 사용하고, 그러나 약간 부서 레코드가 있기 때문에, 우리는 이것을 무시할 것이다.데이터베이스 많은 empno에 엔트리가 존재하기 때문에,이 EMPNO 필드 인덱스를 작성하는 쿼리를 개선 할 수 있다고 가정하는 것이 논리적이다.

당신이 서버를 (즉 mysqlslap 함께 도움이 부분입니다!) 과세되는 쿼리를 찾았하면 약간의 연습으로, 당신은 MySQL의 지식과 데이터베이스를 기반으로 쿼리에 대한 평가를 할 수있을 것입니다.

다음으로, 당신은 당신의 데이터베이스 또는에서 실행되고있는 쿼리를 개선하기 위해 시도 할 수 있습니다.

우리의 경우, 이제 우리는 위에서 언급 한 인덱스를 추가 할 수 있습니다. 우리는 EMPNO에 세 개의 인덱스를 생성합니다. 하나의 인덱스가 직원 테이블의 EMPNO 필드에 생성되며 다른 인덱스는 deptemp 테이블의 EMPNO 필드에 생성되며, 마지막 하나는 제목 테이블의 EMP_NO 필드에 생성됩니다.

의는 우리의 테스트 MySQL의 세션으로 이동하여 다음 명령을 실행하자
USE employees_backup;

CREATE INDEX employees_empno ON employees(emp_no);

CREATE INDEX dept_emp_empno ON dept_emp(emp_no);

CREATE INDEX titles_empno ON titles(emp_no);
우리는 동일한 매개 변수 mysqlslap을 실행할 경우 다시 테스트 서버에 우리의 주요 터미널 창에오고, 우리는 벤치 마크의 차이를 볼 수 있습니다 :
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose
Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1


앞서서 mysqlslpa을 이용하는 방법, 쿼리 성능을 측정하는 방법 및 실제 사용 사례도 설명하였습니다.

결론입니다.

Troubleshooting - mysqlslap Doesn't Show Output

당신이 테스트 명령을 실행하고 출력을 얻을 수없는 경우,이 서버 자원이 끝나가 될 수있는 좋은 지표이다.

증상은 벤치 마크의 출력 부족, 또는 에러 등을 포함 할 수있다 mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query.


당신은 --concurrency 또는 --iterations 매개 변수에 적은 수 테스트를 다시 시도 할 수 있습니다. 또는, 당신은 당신의 테스트 서버 환경을 업그레이드 시도 할 수 있습니다.


이 데이터베이스 서버의 용량의 외부 한계를 찾을 수있는 좋은 방법이 될 수 있습니다.


결론적으로,

mysqlslap 사용하기 쉽고 간단하고 가벼운 도구이며 그것은 MySQL 데이터베이스 엔진이 기본적으로 통합되어 있습니다. 이 버전 5.1.4에서의 MySQL의 모든 버전에 사용할 수 있습니다.


이 튜토리얼에서 우리는 다양한 옵션 mysqlslap 사용하는 방법을 보았다 및 샘플 데이터베이스와 주위했다. 당신도 그와 MySQL의 사이트와 연습에서 다른 샘플 데이터베이스를 다운로드 할 수 있습니다. 우리가 전에 언급 한 바와 같이, 프로덕션 데이터베이스 서버에서 테스트를 실행하지 마십시오.


이 튜토리얼의 마지막 유스 케이스는 하나의 쿼리를하고있었습니다. 우리가 다소 세 테이블에 추가 인덱스를 추가하여 해당 쿼리의 성능을 개선하는 동안, 과정은 현실에서 그렇게 단순하지 않을 수 있습니다. 추가 여분 인덱스는 때때로 시스템 성능을 저하시킬 수 있으며, DBA는 종종 발생할 수있는 성능 저하에 대해 여분의 인덱스를 추가의 이점을 계량 할 필요가있다.


실제 테스트 시나리오는 더 복잡하지만 이것은 당신에게 테스트 및 데이터베이스 성능을 향상 시작하는 도구를 제공합니다.








BY LEE JI EUN





posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 17:16 2. DBMS이야기/02. MySQL

이번 글에서는 mysqlslap을 사용한 사례를 소개합니다.

사용 사례 : 자동 생성 된 SQL 및 데이터와 벤치마킹

우리는 mysqlslap의 자동 생성-SQL 기능을 사용하여 시작합니다. 우리는 자동 생성 된 SQL을 사용하는 경우, mysqlslap는 별도의 임시 데이터베이스를 작성합니다 - 적절라는 mysqlslap을. 이 데이터베이스는 하나의 정수 및 샘플 데이터로 채워 하나의 VARCHAR 타입 컬럼과의 간단한 테이블을해야합니다. 이 데이터베이스 서버의 전체 성능을 확인하는 빠르고 쉬운 방법이 될 수 있습니다.


우리는 자동으로 생성 된 SQL이 한 번 일을 하나의 클라이언트 연결을 테스트하여 시작합니다

sudo mysqlslap --user=sysadmin --password --host=localhost  --auto-generate-sql --verbose
Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

출력에 표시되는 mysqlslap 몇 벤치마킹 통계를보고합니다. 그것은 평균, 최소를보고하고 초 최대 IT는 쿼리를 실행했다. 또한,이 부하 시험에 사용 클라이언트 연결의 수가 하나라고 볼 수있다.


이제 50 개의 동시 연결을 시도하고, 자동 생성 된 쿼리 실행을 10 배를 가지고 :

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose
이것이 의미하는 것은 명령 오십 시뮬레이션 클라이언트 연결마다 동시에 동일한 테스트 쿼리를 발생하며,이 시험은 10 회 반복 될 것이다.

출력은 우리에게 부하 증가와 현저한 차이를 보여줍니다
Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0
필드 이제 클라이언트 당 질의 평균 수가 0 (50)의 값을 보이고있습니다 : 질의를 실행하는 클라이언트들의 개수가 어떻게 참고합니다.

자동 생성 된 SQL은 두 개의 필드와 간단한 테이블을 작성합니다. 대부분의 생산 환경에서 테이블 구조보다 훨씬 더 큰 것입니다. 우리는 테스트 테이블에 추가 필드를 추가하여이 에뮬레이션 mysqlslap 지시 할 수있다. 이를 위해, 우리는 두 개의 새로운 매개 변수를 사용할 수있다 : 숯 COLS---number과 --number-INT-COLS. 이들 파라미터는 VARCHAR의 번호를 지정하고 테스트 테이블에 추가 열 유형 값 int.
다음 예제에서, 우리는 5 숫자 열 20 문자 유형 열이있는 테이블에 대해 실행되는 자동 생성 SQL 쿼리와 함께 테스트. 우리는 또한 50 클라이언트 연결을 시뮬레이션하고 우리는 100 번 반복하는 테스트 :
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose
이 사람이 조금해야합니다. 테스트가 실행되는 동안, 우리는 우리가 MySQL의 세션 실행을 가지고 무슨 일이 일어나고 있는지 볼 수있는 다른 터미널 윈도우로 전환 할 수 있습니다. 시간이 초과하는 경우, 테스트가 완료하고 테스트 데이터베이스를 볼 수 없습니다 있습니다.
MySQL의 프롬프트에서 :
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| mysqlslap          |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)
당신은 당신이 원하는 경우 테스트 데이터베이스에서 테이블을 확인할 수 있습니다; 그것은 T1라고.

다시 다른 터미널 창을 확인합니다. 테스트가 완료되면, 당신은 성능이 더욱 증가 된 부하 둔화 것을 찾을 수 있습니다 :

Benchmark
        Average number of seconds to run all queries: 0.695 seconds
        Minimum number of seconds to run all queries: 0.627 seconds
        Maximum number of seconds to run all queries: 1.442 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0
다시 MySQL의 터미널 세션에 이동합니다. 우리는 mysqlslap가 버리는 데이터베이스 떨어졌다 볼 수 있습니다.MySQL의 프롬프트에서 :
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
사용 예제: Custom 쿼리들로 벤치마킹.
서버의 물리적 자원을 평가하는 경우 자동 생성 된 SQL이 좋다. 당신이 주어진 시스템이 취할 수있는 부하의 수준을 발견 할 때 매우 유용합니다.

특정 데이터베이스에 종속 응용 프로그램의 성능 문제를 해결하려는 경우 그러나, 당신은 실제 데이터에 실제 쿼리를 테스트 할 수 있습니다. 이러한 쿼리는 웹 또는 응용 프로그램 서버에서 오는 될 수 있습니다.

지금, 우리는 당신이 테스트하려는 특정 쿼리를 알고 있다고 가정합니다. 다음 절에서 우리는 당신에게 당신의 서버에서 실행중인 쿼리를 찾을 수있는 방법을 보여 드리죠.

우리는 인라인 쿼리를 시작합니다. 당신은 --query 옵션 mysqlslap하는 인라인 쿼리를 제공 할 수 있습니다.SQL 문은 그들에 줄 바꿈을 할 수 없습니다, 그들은 세미콜론으로 구분해야합니다 ().쿼리는 큰 따옴표로 묶어야합니다.
다음 코드에서 우리는 deptemp 테이블에 대해 간단한 쿼리를 실행하고 있습니다.`deptemp` 테이블 만 명 이상의 기록을 가지고있다. 우리가 --create-스키마 옵션을 사용하여 직원 데이터베이스를 지정하는 방법을 참고 :
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose
이것은 실행하는 데 시간이 걸릴 것입니다. 당신은 2 분 후이 같은 성능 벤치 마크를 받아야한다 :

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1
다음으로, 우리는 --query 매개 변수에 여러 SQL 문을 사용합니다. 다음의 예에서 우리는 세미콜론으로 각 쿼리를 종료한다. mysqlslap 우리가 --delimiter 옵션을 지정했기 때문에 우리가 별도의 SQL 명령의 번호를 사용하는 알고 :
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --iterations=10 --create-schema=employees --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" --delimiter=";" --verbose
이 테스트는 연결 수와 동일한 반복 동일한 번호를 사용한다. 그러나 성능은 여러 SELECT 문 (23.8 초 대 18.486 초 평균)에 대한 점진적으로 느립니다 :
Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 5
생산 SQL 문은 복잡 할 수 있습니다. 이 시험을 위해 그것을 입력하는 것보다 스크립트에 복잡한 SQL 문을 추가하는 것이 더 쉽습니다. 그래서, 우리는 스크립트 파일에서 쿼리를 읽을 mysqlslap을 지시 할 수 있습니다.

이를 설명하기 위해 SQL 명령에서 스크립트 파일을 만들 수 있습니다. 우리는 이러한 파일을 만들려면 아래의 코드를 사용할 수 있습니다 :
sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql

sudo cp ~/select_query.sql /mysqlslap_tutorial/
select_query.sql 파일은 이제 다섯 SELECT 문을 보유하고있다.

이 스크립트는 여러 쿼리를 가지고 있기 때문에, 우리는 새로운 테스트 개념을 도입 할 수 있습니다. mysqlslap는 쿼리를 병렬화 할 수 있습니다. 우리는 각각의 테스트 클라이언트 실행해야 할 쿼리 수를 지정하여이 작업을 수행 할 수 있습니다. mysqlslap는 --number - 중 - 쿼리 옵션을 사용하여이 작업을 수행합니다. 우리가 실행하는 50 개 1000 쿼리가 경우에 따라서, 각각의 클라이언트는 약 20 쿼리를 각각 실행합니다.

마지막으로, 우리는 또한 우리가 사용하는 컴퓨팅 자원의 표시를 줄 것이다 --debug-정보 스위치를 사용할 수 있습니다.

다음 코드에서, 우리는 우리가 방금 만든 스크립트 파일을 사용하는 mysqlslap을 요구하고있다. 우리는 또한 수 - 중 - 쿼리 매개 변수를 지정합니다. 이 과정을 두 번 반복됩니다 우리는 출력에서 디버깅 정보를 원하는 :
sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info
이 명령이 완료되면, 우리는 몇 가지 흥미로운 결과를 볼 수 있습니다 :
Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50


User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43
여기에 우리의 MySQL의 인스턴스의 모든 쿼리를 실행할 수있는 시간 (초) 평균 217 초 거의 사분입니다. 그는 확실히 우리의 가상 머신에 RAM 및 사용 가능한 CPU의 양에 의해 영향을받는 동안, 그것은 또한 두 번 반복 클라이언트 연결의 적당한 수의 쿼리의 많은 때문.

우리는 비 물리적 페이지 결함 다수 볼 수 있었다. 데이터가 메모리에있는 시스템이 가서 디스크에 스왑 파일에서 그것을 가져가 할 수없는 경우 페이지 오류 발생.출력은 CPU 관련 정보를 나타낸다. 이 경우에 우리는 컨텍스트 스위치의 다수를 참조.

BY LEE JI EUN








posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 17:04 2. DBMS이야기/02. MySQL

다음으로 설명할 내용은 2) Sample Database를 인스톨 하는 방법입니다.

다음으로, 우리는 테스트를 위해 샘플 데이터베이스를 설치해야합니다. 이 데이터베이스는 직원이라고하며 MySQL의 웹 사이트에서 무료로 액세스 할 수 있습니다. 데이터베이스는 또한 런치 패드에서 다운로드 할 수 있습니다.직원 데이터베이스는 패트릭 대원과 주세페 Maxia에 의해 개발되었다. 원래 데이터는 지멘스 기업 연구에 Fusheng 왕과 카를로 Zaniolo에 의해 만들어졌습니다.


이 큰 데이터 세트를 제공하기 때문에 우리는 직원 데이터베이스를 선택하고있다.데이터베이스 구조는 간단하다 : 그것은 단지 여섯 테이블을 가지고있다; 그러나 포함 된 데이터에 둘 이상의 300 직원 기록을 (급여 테이블 자체가 거의 3 만 행이 있음)이있다. 이것은 우리가보다 현실적인 생산 작업 부하를 에뮬레이트하는 데 도움이됩니다.


첫째, 우리는 / mysqlslap_tutorial 디렉토리에 있는지 확인 :

cd /mysqlslap_tutorial
직원 샘플 데이터베이스의 최신 버전을 다운로드 :
sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

우리는 압축 파일을 압축을 해제 할 수 있도록 bzip2에 도구를 설치합니다 :

sudo yum install bzip2
데이터베이스 아카이브의 압축을 풉니 다. 이 분 소요됩니다. 우리는 여기서 두 단계를하고 있습니다 :
sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

내용은 employees_db라는 별도, 새 디렉토리에 압축 해제합니다. 우리는 데이터베이스를 설치하는 쿼리를 실행하기 위해이 디렉토리로 이동해야합니다. 내용은 데이터베이스 구조를 생성합니다 README 문서, 변경 로그, 데이터 덤프, 다양한 SQL 쿼리 파일을 포함한다 :

cd employees_db
ls -l
-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

데이터를 MySQL로 연결하고 데이터베이스를 생성 할 employees.sql 스크립트를 실행하고로드하려면이 명령을 실행합니다 :


sudo mysql -h localhost -u sysadmin -p -t < employees.sql
프롬프트에서 이전 섹션에서 시스템 관리자 MySQL 사용자를 위해 만든 암호를 입력합니다.

이 과정의 출력은 다음과 같이 표시됩니다. 그것은 분 정도 걸릴 것 정도 실행하려면
+-----------------------------+
| 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 |
+------------------+

지금 당신은 MySQL의에 로그인하여 데이터가 성공적으로 가져 왔는지 확인하려면 몇 가지 기본 쿼리를 실행할 수 있습니다.

sudo mysql -h localhost -u sysadmin -p
시스템 관리자 MySQL 사용자의 암호를 입력합니다.

새로운 직원 데이터베이스에 대한 데이터베이스 목록을 확인합니다
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

직원 데이터베이스를 사용합니다 :

use employees;
show tables;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

당신이 원한다면, 당신은 각 테이블의 세부 정보를 확인할 수 있습니다. 우리는 titles 테이블에 대한 정보를 확인합니다 :

describe titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

항목 수를 확인합니다 :

mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
|   443308 |
+----------+
1 row in set (0.14 sec)

당신이 원하는 다른 어떤 데이터를 확인합니다. 우리는 지금 우리의 운영 체제 프롬프트로 돌아 갈 수 있습니다 :


quit;

3) mysqlslap을 활용하기!

우리는 지금 mysqlslap을 사용하여 시작할 수 있습니다. 명시 적으로 MySQL로 로그인 할 필요가 없습니다 있도록 mysqlslap는 정규 쉘 프롬프트에서 호출 할 수 있습니다. 이 튜토리얼에서는, 그러나, 우리는 우리의 리눅스 서버에 다른 터미널 연결을 열고 거기에 우리가 이전에 만든 시스템 관리자 사용자와의 새로운 MySQL의 세션을 시작, 그래서 우리는 더 쉽게 MySQL의에서 몇 가지를 확인하고 업데이트 할 수 있습니다. 그래서, 우리는 우리의 sudo는 사용자와의 열린 프롬프트 하나와 MySQL에 로그인 한 프롬프트를해야합니다.


우리가 테스트를 위해 특정 명령에 들어가기 전에, 당신은 가장 유용한 mysqlslap 옵션이 목록에서 살펴 봐야 할 수 있습니다. 이것은 나중에 자신의 mysqlslap 명령을 설계하는 데 도움이 될 수 있습니다.

OptionWhat it means
--userMySQL username to connect to the database server
--passwordPassword for the user account. It's best to leave it blank in command line
--hostMySQL database server name
--portPort number for connecting to MySQL if the default is not used
--concurrencyThe number of simultaneous client connections mysqlslap will emulate
--iterationsThe number of times the test query will be run
--create-schemaThe database where the query will be run
--queryThe query to execute. This can either be a SQL query string or a path to a SQL script file
--createThe query to create a table. Again, this can be a query string or a path to a SQL file
--delimiterThe delimiter used to separate multiple SQL statements
--engineThe MySQL database engine to use (e.g., InnoDB)
--auto-generate-sqlLets MySQL perform load testing with its own auto-generated SQL command


BY LEE JI EUN




























posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 16:58 2. DBMS이야기/02. MySQL

MySQL은 버전 5.1.4부터 주변에있어 쉽고 간단한 진단 도구로 mysqlslap와 함께 제공됩니다. 그것은 DBA와 개발자가 데이터베이스 서버로드 테스트 할 수 있는 벤치마킹 도구입니다.


mysqlslap 동시에 데이터베이스 서버를 타격하는 다수의 클라이언트 연결을 에뮬레이트 할 수있습니다. 부하 테스트 매개 변수를 완벽하게 구성 할 수있는 다른 테스트 실행의 결과는 미세 조정 데이터베이스 설계 나 하드웨어 자원을 사용할 수 있습니다.


이 튜토리얼에서 우리는 몇 가지 기본적인 쿼리와 MySQL 데이터베이스로드 테스트 및 벤치마킹이 그 쿼리 우리에게 미세 조정을 할 수있는 방법을 볼 수 mysqlslap를 사용하는 방법을 배우게됩니다. 몇 가지 기본적인 시위 후, 우리는, 우리가 테스트를 위해 기존 데이터베이스의 사본을 작성 상당히 현실적인 테스트 시나리오를 실행하는 로그에서 쿼리를 주울 및 스크립트의 테스트를 실행합니다.


이 튜토리얼에 표시된 명령, 패키지 및 파일은 개념이 다른 배포판에 대해 동일하게 유지를 CentOS 7에서 테스트되었습니다.


"서버 사이즈를 어떻게 설정해야 할까"

특정 데이터베이스 서버를 벤치마킹에 관심이 있다면, 당신은 같은 사양 및 설치 데이터베이스의 정확한 사본이있는 서버에서 테스트해야합니다.


당신이 학습 목적으로이 튜토리얼을 통해 실행하고있는 모든 명령을 실행하려면, 우리는 적어도 2GB의 물방울을 추천합니다. 이 튜토리얼의 명령이 서버 세금을 부과하기위한 것입니다, 당신은 작은 서버에서 시간 초과 것을 알 수 있습니다.


이 튜토리얼의 샘플 출력은 교육에 대한 예제를 최적화하기 위해 다양한 방법으로 제작 하였습니다.


1) MySQL 커뮤니티 서버에 테스트 시스템으로 인스톨링.

우리는 테스트 데이터베이스에서 MySQL 커뮤니티 서버의 신선한 복사본을 설치하여 시작합니다. 당신은 프로덕션 데이터베이스 서버에서이 튜토리얼에서 어떤 명령이나 쿼리를 실행하면 안됩니다.

이 테스트는 테스트 서버를 강조하기위한 것입니다 및 프로덕션 서버에서 지연 또는 중단의 원인이 될 수 있습니다. 이 튜토리얼은 다음과 같은 환경에서 테스트되었다 :

  • CentOS 7
  • sudo user 에 의해 실행
  • 2 GB Droplet 권장: 이 튜토리얼에 표시된 벤치 마크 결과는 교육 목적으로 생산 된 것을 명심하고 특정 디지털 오션 벤치 마크를 반영하지 않습니다

첫째, 우리는이 튜토리얼에 관련된 모든 파일을 보관하는 디렉토리를 작성합니다. 이 단정하게 유지하는 데 도움이됩니다. 이 디렉토리로 이동합니다


sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial
다음으로, 우리는 MySQL의 커뮤니티 릴리스 얌 저장소를 다운로드합니다. 우리가 다운로드하는 저장소를 CentOS 7 작동 레드햇 엔터프라이즈 리눅스 (7)입니다 :
sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
다음으로는 저장소를 설치하는 명령 -Uvh RPM을 실행할 수 있습니다 :


sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm
저장소가 /etc/yum.repos.d 폴더의 내용을보고 설치되어 있는지 확인합니다 :


sudo ls -l /etc/yum.repos.d
출력은 다음과 같아야합니다 :
-rw-r--r--. 1 root root 1612 Jul  4 21:00 CentOS-Base.repo
-rw-r--r--. 1 root root  640 Jul  4 21:00 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1331 Jul  4 21:00 CentOS-Sources.repo
-rw-r--r--. 1 root root  156 Jul  4 21:00 CentOS-Vault.repo
-rw-r--r--. 1 root root 1209 Jan 29  2014 mysql-community.repo
-rw-r--r--. 1 root root 1060 Jan 29  2014 mysql-community-source.repo

우리는 또한 올바른 MySQL의 릴리스 설치에 사용할 수 있는지 확인할 수 있습니다 :

sudo yum repolist enabled | grep mysql

우리의 경우, MySQL은 5.6 커뮤니티 서버는 우리가 원하는 것입니다 :


mysql-connectors-community/x86_64       MySQL Connectors Community           10
mysql-tools-community/x86_64            MySQL Tools Community                 6
mysql56-community/x86_64                MySQL 5.6 Community Server           64

MySQL의 커뮤니티 서버를 설치합니다 :

sudo yum install mysql-community-server
프로세스가 완료되면, 이제 intalled 구성 요소를 확인하자 :
sudo yum list installed | grep mysql
목록은 다음과 같이 보일 것입니다 :
mysql-community-client.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-common.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-libs.x86_64        5.6.20-4.el7      @mysql56-community
mysql-community-release.noarch     el7-5             installed
mysql-community-server.x86_64      5.6.20-4.el7      @mysql56-community

다음으로 우리는 MySQL의 데몬이 실행 때 자동으로 서버가 부팅을 시작하고 있는지 확인해야합니다.mysqld를 데몬의 상태를 확인합니다.


sudo systemctl status mysqld.service
중지되어 있다면,이 출력을 보여줍니다 :


mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
   Active: inactive (dead)

서비스를 시작합니다 :


sudo systemctl start mysqld.service
이 부팅시 자동으로 시작되도록 구성되어 있는지 확인합니다 :
sudo systemctl enable mysqld.service
마지막으로, 우리는 MySQL을 확보해야한다 :
sudo mysql_secure_installation
이 일련의 프롬프트를 가져올 것이다. 우리는 당신이 빨간색으로 입력해야 답변을 아래의 지시를 보여줄 것이다. 초기에, 그래서 그냥 Enter를 누르십시오, MySQL의 루트 사용자에 대한 암호가 없습니다.

프롬프트에서 당신은 당신이 자신을 선택해야 새로운 보안 루트 암호를 제공해야합니다. 당신은, 권한 테이블을 다시로드, 등, 익명의 데이터베이스 사용자 계정을 제거 원격 루트 로그인을 사용하지 y로 대답합니다 :
...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

우리는 지금 데이터베이스에 연결하고 있는지 모든 것이 작동을 할 수 있습니다 :

sudo mysql -h localhost -u root -p
당신이 프롬프트에서 설정 한 MySQL의 root 암호를 입력합니다. 다음과 같은 출력을 볼 수있을 것이다 :


Enter password:
Welcome to the MySQL monitor....

mysql>

mysql> promp에서 모든 데이터베이스를 보려면 명령을 입력합니다 :

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

마지막으로, 이제 시스템 관리자라는 사용자 계정을 만들 수 있습니다. 이 계정은 MySQL을 대신 루트 사용자로 로그인하는 데 사용됩니다. 이 사용자에 대한 자신의 암호 mypassword 교체해야합니다. 우리는 또한이 계정에 모든 권한을 부여합니다.MySQL의 프롬프트에서 다음 명령을 입력 :

create user sysadmin identified by 'mypassword';

Output:

Query OK, 0 rows affected (0.00 sec)

Grant the privileges:

grant all on *.* to sysadmin;

Output:

Query OK, 0 rows affected (0.01 sec)
지금 다시 운영 체제 프롬프트에 갑시다 :


quit;
Bye


BY LEE JI EUN



posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 16:43 2. DBMS이야기/02. MySQL

이는 상기 기술과 백업 데이터 MySQL의 복제를 사용하는 것이 가능하다.


복제는 다른 (마스터 마스터)에 서버 중 하나에 대한 변경 사항을 다른 서버 (마스터 - 슬레이브)에 하나의 서버에서 데이터를 미러링 또는 미러링의 과정이다.


복제 데이터 미러링 수 있지만, 그것은 당신이 특정 시점을 저장하려고 할 때 겪고있다. 그것은 항상 동적 시스템의 변경을 복제되기 때문이다.


이 문제를 방지하려면 :


1) 일시적으로 복제 하지 않음

2) 일시적으로 백업 시스템은 읽기 전용 확인


Disabling Replication Temporarily



당신은 실행하여 일시적으로 슬레이브 복제를 사용하지 않도록 설정할 수 있습니다 :

mysqladmin -u user_name -p stop-slave
완전히, 말하자면, 복제를 중지하지만, 일시에두고하지 않는 또 다른 옵션을 입력하여 수행 할 수 있습니다 :


mysql -u user_name -p -e 'STOP SLAVE SQL_THREAD;'
복제가 정지 된 후에는 백업 상기 방법 중 하나를 사용하여 할 수있다. 이 슬레이브를 백업하는 동안 온라인 마스터 MySQL 데이터베이스를 유지할 수 있습니다.

이 작업이 완료되면, 입력하여 복제를 다시 시작합니다


mysqladmin -u user_name -p start-slave

Making the Backup Machine Read-Only Temporarily




또한 데이터가 읽기 전용 일시적하여 서버 내 데이터의 일관성을 보장 할 수 있습니다.


당신은 마스터 또는 슬레이브 시스템 중 하나에서 다음 단계를 수행 할 수 있습니다.


우선, 데이터를 조작하기에 충분한 권한의 MySQL에 로그인 :

mysql -u root -p 
다음으로는 디스크에 캐시 된 모든 변경 사항을 작성하고 읽기 전용-입력하여 시스템을 설정할 수 있습니다 :
FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

이제, mysqldump는을 사용하여 백업을 수행합니다.

백업이 완료되면, 입력하여 원래의 작동 상태로 시스템을 반환 :

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

A Note About Techniques That Are No Longer Recommended

mysqlhotcopy

MySQL은 신속하게 "mysqlhotcopy를"라는 데이터베이스를 백업하는 펄 스크립트를 포함하고 있습니다. 이 도구는 로컬 시스템에 신속하게 데이터베이스 백업에 이용 될 수 있지만, 우리는 그것을 추천 방지하게 한계가있다.


그것은 단지 "의 MyISAM"와 "아카이브"스토리지 엔진을 사용하여 저장된 데이터에 대해 작동하기 때문에 우리가 여기 mysqlhotcopy를의 사용을 포함하지 않습니다 가장 중요한 이유입니다.


대부분의 사용자가 MySQL을 5.5에서 시작하여 자신의 데이터베이스에 저장 엔진을 변경하지 않으며, 디폴트 스토리지 엔진은 "InnoDB는"이다. 이런 유형의 데이터베이스는 mysqlhotcopy를 사용하여 백업을 할 수 없다.


이 스크립트의 또 다른 한계는 단지 데이터베이스 저장 보관되어 동일한 머신에서 수행 될 수 있다는 것이다. 이것은 어떤 상황에 큰 제한이 될 수있는 원격 시스템에서 실행중인 백업을 방지 할 수 있습니다.

Copying Table Files



때때로 제안하는 다른 방법은 단순히 MySQL이에 데이터를 저장 테이블 파일을 복사한다.


이 접근법은 "mysqlhotcopy를"같은 이유 중 하나로 고통입니다.


이 파일에 데이터를 저장하는 스토리지 엔진이 기술을 사용하는 것이 합리적이지만, InnoDB에, 새로운 기본 스토리지 엔진은, 이러한 방식으로 백업 할 수 없습니다.

Conclusion

MySQL의에서 백업을 수행하는 여러 가지 방법이 있습니다. 모두 자신의 장점과 약점을 가지고 있지만, 일부는 다른 사람보다 구현하기가 훨씬 더 쉽고 광범위하게 유용하다.

당신이 선택하여 배포 할 백업 방식은 개인의 필요와 자원뿐만 아니라 프로덕션 환경에 크게 좌우 될 것이다. 당신이 어떤 결정 방법, 당신은 프로세스가 제대로 작동하는지 확신 할 수 있도록 백업 및 복원 연습 데이터의 유효성을 검사해야합니다.


by 윤성용

posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 16:37 2. DBMS이야기/02. MySQL

What is MySQL?



 MySQL을 액세스하고 데이터를 조작하기 위해 SQL 질의의 언어를 사용하는 인기있는 데이터베이스 관리 솔루션이다. 그것은 쉽게 웹 사이트 또는 애플리케이션으로부터 데이터를 관리하는데 사용될 수있다.


백업은 어떤 종류의 데이터와 중요하며, 데이터베이스에 대해 얘기 할 때 특히 적합하다. MySQL은 우리가이 문서에서 설명하는 몇 가지 다른 방법으로 백업 할 수 있습니다.


이 튜토리얼을 위해, 우리는 설치 MySQL의 5.5 우분투 12.04 VPS를 사용하는 것입니다. 대부분의 현대적인 분포와 MySQL의 최신 버전은 비슷한 방식으로 작동한다.


How to Backup a MySQL Database with mysqldump

MySQL과 백업하는 가장 일반적인 방법 중 하나는 "mysqldump를"이라는 명령을 사용하는 것이다.


Backing Up

여기 mysqldump를을 사용하여 데이터베이스를 내보내는 방법에 대한 기사가있다. 명령의 기본 구문은 다음과 같습니다

mysqldump -u username -p database_to_backup > backup_name.sql

Restoring



mysqldump를 사용하여 만든 데이터베이스 덤프를 복원하려면, 당신은 단순히 다시 MySQL의에 파일을 리디렉션해야합니다.

우리는 가져온 데이터를 수용 할 수있는 빈 데이터베이스를 작성해야합니다. 첫째, 입력하여 MySQL의에 로그인 :

mysql -u username -p
데이터 덤프 및 MySQL의 프롬프트 중 다음 출구에서 모든 데이터를 보유 할 새 데이터베이스를 만듭니다 :
CREATE DATABASE database_name;
exit

다음으로, 우리는 다음과 같은 명령을 실행하여 새로 만든 데이터베이스로 덤프 파일을 재 지정할 수 있습니다 :

mysql -u username -p database_name < backup_name.sql
귀하의 정보는 현재 사용자가 만든 데이터베이스에 복원해야합니다.


How to Backup a MySQL Table to a Text File

당신은 MySQL의에서 SELECT 문을 사용하여 직접 텍스트 파일로 테이블의 데이터를 저장할 수 있습니다.

이 작업에 대한 일반 구문은 다음과 같습니다
SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;
이 작업은 MySQL 서버의 파일에 테이블 데이터를 저장합니다. 이미 선택한 이름을 가진 파일이 존재하는 경우에 실패합니다.


How to Backup MySQL Information using automysqlbackup

우분투 저장소에서 사용할 수 있습니다 "automysqlbackup"라는 유틸리티 프로그램이있다.

이 유틸리티는 정기적으로 자동 백업을 수행하도록 예약 할 수 있습니다.

이 프로그램을 설치하려면 터미널에 다음을 입력합니다 :
sudo apt-get install automysqlbackup
입력하여 명령을 실행합니다 :
sudo automysqlbackup
automysqlbackup의 주요 구성 파일 "을 / etc / 기본 / automysqlbackup"에 있습니다. 관리자 권한을 엽니다.
sudo nano /etc/default/automysqlbackup
이 파일은 기본적으로 "/etc/mysql/debian.cnf"에있는 MySQL의 파일에 의해 많은 변수를 할당하는 것을 볼 수 있습니다. 이것은 유지 보수 로그인 정보를 포함합니다.

이 파일에서, 그것은 백업해야하는 사용자, 암호 및 데이터베이스를 읽습니다.

백업의 기본 위치는 "의 / var / lib 디렉토리 / automysqlbackup"입니다. 백업의 구조를보기 위해이 디렉토리를 검색 :
ls /var/lib/automysqlbackup
daily  monthly weekly
우리가 매일 디렉토리에 보면있는 명령이 실행 된 때부터 gzip으로 압축 된 SQL 덤프는 내부에, 우리는 각각의 데이터베이스에 대한 하위 디렉토리를 볼 수 있습니다
ls -R /var/lib/automysqlbackup/dailey
.:
database_name  information_schema  performance_schema

./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz

./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz

./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz

Ubuntu는 매일 그것을 실행이 프로그램 크론 스크립트를 설치합니다. 이는 해당 디렉토리에 파일을 구성합니다.




by 윤성용













































posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 16:30 2. DBMS이야기/02. MySQL

MySQL 데이터베이스를위한 마스터 - 슬레이브 복제의 이점에 상세히 설명 할 필요가 없을 만큼, 그것은 단순히 좋은 생각입니다;

앞으로 이야기 할 내용은 데이터베이스의 성능에 영향을주지 않고 슬레이브에서 백업을 실행할 수있는 기능입니다.


마스터 - 슬레이브 복제에 대부분의 튜토리얼은 초기 설정시 일관된 사본을 달성하기 위해 읽기 잠금을 사용합니다.

우리의 사용자가 밤의 모든 시간에 카드와 선물의 수천을 보내고, 다운 타임없이 마이그레이션을 수행 할 수있는 방법을 찾고 싶었습니다.


먼저, [mysqld를] 섹션에 다음 행을 추가하여 마스터의 /etc/mysql/my.cnf를 구성해야합니다 :


server-id=1 binlog-format = mixed log-bin=mysql-bin datadir=/var/lib/mysql innodb_flush_log_at_trx_commit=1 sync_binlog=1


마스터 MySQL 서버를 다시 시작하고 슬레이브 서버가 마스터에 연결하는 데 사용할 복제 사용자를 생성 :


CREATE USER replicant@<<slave-server-ip>>; GRANT REPLICATION SLAVE ON *.* TO replicant@<<slave-server-ip>> IDENTIFIED BY '<<choose-a-good-password>>';


다음으로, 바이너리 로그 위치와 백업 파일을 만들 수 있습니다. 그것은 당신의 데이터베이스 서버의 성능에 영향을 미칠 것입니다,하지만 테이블을 잠글 것입니다 :


mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql


이제, MASTER_LOG_FILE 과 MASTER_LOG_POS파일의 헤드 검사 값을 적어야합니다. 나중에 필요합니다.


head dump.sql -n80 | grep "MASTER_LOG_POS"


파일의 크기가 크기 때문에 slave로 파일을 전송하기 전에 gzip으로 압축을 할 것입니다. 선택사항 입니다.


gzip ~/dump.sql


이제 우리는 (당신이 먼저 gzip을 gz 인 비트를 제거하지 않은 경우) 우리의 슬레이브 서버에 덤프 파일을 전송해야합니다 :


scp ~/dump.sql.gz mysql-user@<<slave-server-ip>>:~/


그 실행 중에, 당신은 당신의 노예 서버에 로그인하고 다음 줄을 추가하도록 /etc/mysql/my.cnf 파일을 편집해야합니다 :


server-id = 101 binlog-format = mixed log_bin = mysql-bin relay-log = mysql-relay-bin log-slave-updates = 1 read-only = 1


MySQL의 슬래 이브를 다시 시작한 다음 덤프 파일을 가져옵니다


gunzip ~/dump.sql.gz mysql -u root -p < ~/dump.sql


당신의 슬레이브 서버에 MySQL의 콘솔에 로그인하고 설정하고 복제를 시작하려면 다음 명령을 실행합니다.


CHANGE MASTER TO MASTER_HOST='<<master-server-ip>>',MASTER_USER='replicant',MASTER_PASSWORD='<<slave-server-password>>', MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>; START SLAVE;


당신의 슬래 이브의 진행 상황을 확인하려면 :


SHOW SLAVE STATUS \G


모든 것이 잘된 경우, Last_Error가 비어있을 것이고, Slave_IO_State는 "이벤트를 보낼 수있는 마스터를 기다리는 중"을 보고합니다. 그 뒤에 나타나는 Seconds_Behind_Master를 찾습니다. 위의 모든 작업을 수행하는 나에게 몇 시간이 걸렸지 만 슬레이브는 몇 분 만에 실행되었습니다.


그리고 지금 당신은 다운 타임을 경험하지 않고 새로 생성한 MySQL을 슬래 이브 서버를 사용할 수 있습니다



by 윤성용


posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 16:18 2. DBMS이야기/02. MySQL

MySQL에서 Master-Master Replication 셋팅 방법을 전 내용과 이어서 설명드립니다.


Step 2 - Install and Configure MySQL on Server D

우리는 우리가 우리가 다음 명령을 수행 할 수있는 설치해야합니다 서버 C. 먼저 다음에 동일한 단계를 반복해야합니다 :
sudo apt-get install mysql-server mysql-client
두 개의 패키지가 제대로 설치되면, 우리는 우리가 우리가 /etc/mysql/my.cnf 파일을 편집하여 시작합니다 서버 C. 구성으로 거의 같은 방식으로 구성해야합니다.
sudo nano /etc/mysql/my.cnf 
우리가 이전 변경으로 우리는 구성 파일에 같은 네 줄을 변경해야합니다.
우리가 변경해야하는 기본값 내용은 다음과 같습니다.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1
우리는 아래의 라인에 맞게 네 가지 라인을 변경해야합니다. 서버 C는 달리, 서버 D에 대한 서버 ID가 1로 설정 될 수 없음을 양해 해 주시기 바랍니다.
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
# bind-address            = 127.0.0.1
저장하고 해당 파일을 종료 한 후에, 당신은 MySQL의를 다시 시작해야합니다 :
sudo service mysql restart
그것은 MySQL의 쉘에 가서 좀 더 많은 구성 옵션을 설정하는 시간입니다.
mysql -u root -p 
첫째, 단지 서버 C에, 우리는 복제에 대한 책임을 질 것입니다 의사 사용자를 생성 할 것입니다. 당신이 사용하고자하는 암호로 "password"를 교체합니다.

create user 'replicator'@'%' identified by 'password'; 
다음으로, 우리는 우리가 우리의 VPS에 복제하고자하는 데이터베이스를 작성해야합니다.
create database example; 
그리고 우리는 그것을 복제하는 우리의 새로 만든 '복제'사용자 권한을 부여해야합니다.
grant replication slave on *.* to 'replicator'@'%'; 
다음 단계는 우리가 이전의 메모를했다 정보를 복용하고 우리의 MySQL 인스턴스에 적용이 포함됩니다. 이 복제를 시작 할 수 있습니다. 다음은 MySQL의 쉘에서 입력해야합니다 :

slave stop; 
CHANGE MASTER TO MASTER_HOST = '3.3.3.3', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107; 
slave start; 
당신은 당신이 복제에 대해 선택한 암호 '암호'를 교체해야합니다. MASTER_LOG_FILE과 MASTER_LOG_POS에 대한 귀하의 값은 위의 것보다 다를 수 있습니다. "SHOW 마스터 상태가"서버 C.에 반환 당신은 값을 복사해야합니다.
완료하기 전에 우리가 마지막으로 할 일은 MySQL의 마스터 - 마스터 복제 (서버 C로 서버 D에서) 다른 방향으로 복제하는 마스터 로그 파일과 위치를 기록하는 것입니다 .
우리는 다음을 입력하여 해당 작업을 수행 할 수 있습니다 :
SHOW MASTER STATUS; 
출력은 다음과 비슷하게 보일 것입니다:
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
우리는 서버 C에 그를 입력해야합니다으로 양방향 복제를 완료하려면, 파일 및 위치를 기록해 둡니다.
다음 단계는 그 작업을 수행하는 방법을 설명합니다.

Step 3 - Completing Replication on Server C

돌아 가기 서버 C에, 우리는 명령 줄에서 복제 구성을 완료해야합니다. 이 명령을 실행하면 서버 D. 모든 데이터를 복제합니다


slave stop; 
CHANGE MASTER TO MASTER_HOST = '4.4.4.4', MASTER_USER = 'replicator', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 107; 
slave start; 
당신의 가치는 위의 것과 다를 수 있다는 것을 명심하십시오. 또한 복제 사용자를 설정할 때 만든 암호로 MASTER_PASSWORD의 값을 교체하십시오.
Query OK, 0 rows affected (0.01 sec)
마지막으로 할 일은 복제가 모두 VPS에 노력하고 테스트하는 것입니다. 마지막 단계는이 구성을 테스트 할 수있는 쉬운 방법을 설명합니다.

Step 4 - Testing Master-Master Replication

모든 구성이 설정했는지 이제, 우리는 지금 테스트하는 것입니다.
이를 위해, 우리는 서버 C에 예 데이터베이스에 테이블을 생성하고이 나타나 있는지 확인하기 위해 서버 D에 확인할 것입니다.
그런 다음, 우리는 서버 D에서 삭제하고 더 이상 서버 C.에 게재하는지 확인려고합니다.
이제 우리는 서버간에 복제 될 데이터베이스를 작성해야합니다. 우리는 MySQL의 쉘에서 다음을 입력하여 해당 작업을 수행 할 수 있습니다 :
create database example; 
이 작업이 일단,의 서버 C에 더미 테이블을 만들 수 있습니다 :
create table example.dummy (`id` varchar(10)); 
우리는 지금 우리의 테이블이 존재하는지 확인하기 위해 서버 D를 확인할 것입니다.

show tables in example; 
+-------------------+
| Tables_in_example |
+-------------------+
| dummy             |
+-------------------+
1 row in set (0.00 sec)
할 수있는 마지막 테스트는 또한 서버 C.에서 삭제해야 서버 D.에서 우리의 더미 테이블을 삭제하는 것입니다

우리는 서버 D에서 다음을 입력하여이 작업을 수행 할 수 있습니다 :
DROP TABLE dummy; 
이를 확인하려면, 서버 C에있는 "Show 테이블"명령을 실행하면 어떤 테이블을 표시하지 않습니다 :

Empty set (0.00 sec)

by 윤성용









posted by DB,MW,OS OSSW(Open Source System SoftWare
2014.11.19 15:56 2. DBMS이야기/02. MySQL

MySQL의 복제는 MySQL 데이터베이스에 저장 한 데이터 세트가 두 번째 서버로 라이브 복사되는 과정입니다.

"마스터 - 슬레이브"복제라고하는이 구성은 일반적인 설정입니다.

마스터 마스터 복제는 데이터가 다른 하나에 두 서버에서 복사 할 수 있기 때문에 우리의 설정은,보다 더 좋을 것입니다.

이 미묘하지만 중요한 차이는 우리가 MySQL을 읽거나 하나를 서버에서 쓰기를 수행 할 수 있습니다.

이 구성은 리던던시를 추가하고, 데이터 액세스를 처리 할 때 효율을 증가시킵니다.


이 문서의 예는, 두 VPS를 기반으로 서버 C 및 서버 D에 지정됩니다.


Server C: 3.3.3.3

Server D: 4.4.4.4

Step 1 - Install and Configure MySQL on Server C

우리가해야 할 첫 번째 일은 우리의 서버에 MySQL의 서버와 MySQL 클라이언트 패키지를 설치하는 것입니다. 우리는 다음을 입력하여 해당 작업을 수행 할 수 있습니다 :
sudo apt-get install mysql-server mysql-client
기본적으로 MySQL의 프로세스는 로컬 호스트 (127.0.0.1)에 연결을 허용합니다.
  이 기본 동작을 변경하고 복제가 제대로 작동하는 데 필요한 몇 가지 다른 설정을 변경하려면, 우리는 서버 C.에 /etc/mysql/my.cnf 편집해야 현재 다음으로 설정됩니다. 우리가 변경해야 할 네 가지 라인 :
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
bind-address            = 127.0.0.1

그 라인의 첫 번째 고유 우리 복제 구성에서, 우리의 특정 서버를 식별하는 것입니다.

우리는 이전에 "#"을 제거하여, 그 행의 주석을 해제해야합니다. 두 번째 줄은 MySQL 데이터베이스를 변경하거나 테이블이 로그인 할 수있는 파일을 나타냅니다.


세 번째 라인은 우리가 우리의 서버간에 복제 할 데이터베이스를 나타냅니다.

당신이 원하는만큼이 라인에 많은 데이터베이스를 추가 할 수 있습니다.

이 기사는 단순성의 목적을 위해 "예"라는 이름의 단일 데이터베이스를 사용합니다.

그리고 마지막 줄 (127.0.0.1에서 수신하지 않음으로써) 인터넷 연결을 허용하기 위해 서버를 알려줍니다.

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = example
# bind-address            = 127.0.0.1

이제 우리는 MySQL의를 다시 시작해야합니다 :

sudo service mysql restart
우리는 다음 우리의 MySQL 인스턴스 내에서 일부 명령 줄 설정을 변경해야합니다. 돌아 가기 우리의 쉘에서, 우리는 다음을 입력하여 우리의 루트 MySQL 사용자를 얻을 수 있습니다 :
mysql -u root -p 
이 명령은 그 루트 MySQL 사용자를 묻는 메시지를 표시하니, 암호를 주의하시기 바랍니다.
당신은 MySQL의 쉘에 로그인되어 있는지 확인하기위해 프롬프트는 mysql> 과 같아야합니다.
mysql> 
우리가 로그인하면, 우리는 몇 가지 명령을 실행해야합니다.
우리는 우리의 두 VPS 사이에 데이터를 복제에 사용되는 가상 사용자를 작성해야합니다.
이 문서의 예제에서는이 사용자 "복제"를 이름을 가정합니다.
복제를 위해 사용하고자하는 암호로 "password"를 교체합니다.
create user 'replicator'@'%' identified by 'password'; 
다음으로, 우리는 우리의 MySQL의 데이터를 복제하려면이 사용자 권한을 부여해야합니다

grant replication slave on *.* to 'replicator'@'%'; 
복제에 대한 권한은, 불행하게도, 데이터베이스 당 기준으로 부여 할 수 없습니다. 우리 사용자는 우리가 설정 파일에 지시하는 데이터베이스들을 복제합니다.
초기 서버 C 구성의 최종 단계를 들어, 우리는 현재의 MySQL의 인스턴스에 대한 정보를 얻을 필요로 우리가 나중에 서버 D.에 제공 할 것입니다 
다음 명령 출력됩니다 우리가 주목해야 할 중요한 정보가됩니다, :
show master status; 
출력은 다음과 비슷하게 보는 것, 중요한 두 가지 정보를해야합니다 :
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
우리는 다음 단계에서 사용되는 파일과 위치의 설명을 작성해야합니다.








by 윤성용






posted by DB,MW,OS OSSW(Open Source System SoftWare
prev 1 2 3 4 next