'MySQL Cluster'에 해당되는 글 2건

Adding MySQL Cluster Data Nodes Online

• MySQL 클러스터 온라인 데이터 노드 추가가 가능하도록 지원을 시작하고 있다.
• MySQL Cluster Data Nodes Online 추가가 가능한 버전은 MySQL Cluster NDB 6.4.0 (This capability is available in MySQL Cluster NDB 7.0 (beginning with MySQL Cluster NDB 6.4.0) and later MySQL Cluster release series.) 이상부터 가능한 것으로 확인되며 공식문서를 참고하여 문서를 정리하였습니다.

     

Step 1: Update configuration file.

the cluster uses a config.ini file

[ndbd default]

…..

[ndbd]

….

Id = 7

HostName = *.*.*.4 (TEST IP)

datadir=/usr/local/mysql-cluster/ndbd-data

ArbitrationRank=3

Id = 8

HostName = *.*.*.5 (TEST IP)

datadir=/usr/local/mysql-cluster/ndbd-data

ArbitrationRank=4

Step 2: Restart the management server

1) Stop the management server using the management client STOP command, as shown here:

ndb_mgm> 10 STOP

Node 10 has shut down.

Disconnecting to allow Management Server to shutdown

shell>

2) Because shutting down the management server causes the management client to terminate, you must start the management server from the system shell.

shell> ndb_mgmd -f config.ini --reload

2008-12-08 17:29:23 [MgmSrvr] INFO -- NDB Cluster Management Server. 5.1.73-ndb-7.1.36

2008-12-08 17:29:23 [MgmSrvr] INFO -- Reading cluster configuration from 'config.ini'

3) status check

ndb_mgm> show

Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=3 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0)

id=4 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0, Master)

id=7 (not connected, accepting connect from 10.0.0.4)

id=8 (not connected, accepting connect from 10.0.0.5)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @*.*.*.3 (mysql-5.1.34 ndb-6.3.25)

[mysqld(API)] 2 node(s)

id=5 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25)

id=6 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25)

Step 3: Perform a rolling restart of the existing data nodes.

ndb_mgm> 1 RESTART

Node 1: Node shutdown initiated

Node 1: Node shutdown completed, restarting, no start.

Node 1 is being restarted

ndb_mgm> Node 1: Start initiated (version 7.1.36)

Node 1: Started (version 7.1.36)

ndb_mgm> 2 RESTART

Node 2: Node shutdown initiated

Node 2: Node shutdown completed, restarting, no start.

Node 2 is being restarted

ndb_mgm> Node 2: Start initiated (version 7.1.36)

ndb_mgm> Node 2: Started (version 7.1.36)

Step 4: Perform a rolling restart of all cluster API nodes.

shell> mysqladmin -uroot -ppassword shutdown

081208 20:19:56 mysqld_safe mysqld from pid file

/usr/local/mysql/var/tonfisk.pid ended

shell> mysqld_safe --ndbcluster --ndb-connectstring=*.*.*.3 &

081208 20:20:06 mysqld_safe Logging to '/usr/local/mysql/var/tonfisk.err'.

081208 20:20:06 mysqld_safe Starting mysqld daemon with databases

from /usr/local/mysql/var

Step 5: Perform an initial start of the new data nodes.

shell> ndbd -c 10.0.0.3 --initial

ndb_mgm> show

Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=3 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0)

id=4 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0, Master)

id=7 @*.*.*.7 (mysql-5.1.34 ndb-6.3.25, no nodegroup)

id=8 @*.*.*.8 (mysql-5.1.34 ndb-6.3.25, no nodegroup)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @*.*.*.3 (mysql-5.1.34 ndb-6.3.25)

[mysqld(API)] 2 node(s)

id=5 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25)

id=6 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25)

Step 6: Create a new node group

ndb_mgm> CREATE NODEGROUP 7,8

Nodegroup 1 created

ndb_mgm> show

Cluster Configuration

---------------------

[ndbd(NDB)] 2 node(s)

id=3 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0)

id=4 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25, Nodegroup: 0, Master)

id=7 @*.*.*.7 (mysql-5.1.34 ndb-6.3.25, 1)

id=8 @*.*.*.8 (mysql-5.1.34 ndb-6.3.25, 1)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @*.*.*.3 (mysql-5.1.34 ndb-6.3.25)

[mysqld(API)] 2 node(s)

id=5 @*.*.*.1 (mysql-5.1.34 ndb-6.3.25)

id=6 @*.*.*.2 (mysql-5.1.34 ndb-6.3.25)

Step 7: Redistribute cluster data.

ndb_mgm> all report mem

  Node 2: Data usage is 5%(176 32K pages of total 3200)

  Node 2: Index usage is 0%(76 8K pages of total 12832)

  Node 3: Data usage is 5%(176 32K pages of total 3200)

  Node 3: Index usage is 0%(76 8K pages of total 12832)

Node 7: Data usage is 0%(0 32K pages of total 192000)

Node 7: Index usage is 0%(0 8K pages of total 192032)

Node 8: Data usage is 0%(0 32K pages of total 192000)

Node 8: Index usage is 0%(0 8K pages of total 192032)

shell> ndb_desc -c *.*.*.3 -d n ips -p

- ips --

Version: 1

Fragment type: 9

K Value: 6

Min load factor: 78

Max load factor: 80

...

-- Per partition info --

Partition Row count Commit count Frag fixed memory Frag varsized memory

0 26086 26086 1572864 557056

1 26329 26329 1605632 557056

1) Table Redistribute

- 대상 테이블명 확인

SELECT TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE = 'NDBCLUSTER';

- 테이블 복제

mysql> ALTER ONLINE TABLE [DB명].[테이블명] REORGANIZE PARTITION;

- Redistribute 확인

shell> ndb_desc -d [DB명] [테이블명] -c *.*.*.3:port -pn

shell> ndb_desc -c *.*.*.10 -d n ips -p

-- ips --

Version: 16777217

Fragment type: 9

K Value: 6

Min load factor: 78

Max load factor: 80

…

-- Per partition info --

Partition Row count Commit count Frag fixed memory Frag varsized memory

0 12981 52296 1572864 557056

1 13236 52515 1605632 557056

2 13105 13105 819200 294912

3 13093 13093 819200 294912

ndb_mgm> all report mem

Node 2: Data usage is 5%(176 32K pages of total 3200)

Node 2: Index usage is 0%(76 8K pages of total 12832)

Node 3: Data usage is 5%(176 32K pages of total 3200)

Node 3: Index usage is 0%(76 8K pages of total 12832)

Node 7: Data usage is 2%(80 32K pages of total 3200)

Node 7: Index usage is 0%(51 8K pages of total 12832)

Node 8: Data usage is 2%(80 32K pages of total 3200)

  Node 8: Index usage is 0%(50 8K pages of total 12832)

[참고 사이트]

원본 위치 <https://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-online-add-node.html>

MySQL Monitor Tool Install

• MySQL Cluster에 따른 node를 많이 생성(global Service의 경우에 3000개까지 구성하는 것을 본적이 있다.)하게 되면 관리적인 측면의 고민하게 된다.MySQL의 경우에 위 MySQL Monitor Tool을 통하여 통합 관리가 가능하도록 지원하고 있다.

  

• 해당 Tool에서는 DB 상태 확인부터 성능에 대한 확인까지 다양한 정보를 확인할 수 있는데 Database Activity의 기능을 통하여 부하테스트를 진행할 때 성능 분석 및 개선을 진행한 적이 있었는데 오라클의 ASH(Active Session History)와 비슷한 기능으로 유용하게 사용했던 기억이 있다.

     

• Manager와 Agent를 설치하여 IP 설정만 하면 어렵지 않게 구성할 수 있다. (아래 내역을 참고하여 진행)

0. MySQL 사용자 계정 생성

create user 'TEST'@'%' identified by 'TEST';

1. Monitor Manager Install

--------------------

test-mysql-manager

*.*.*.10

================================================================

root@mysql-manager:/mnt/TEST# ./mysqlmonitor-2.3.12.2175-linux-x86_64-installer.bin

Message from syslogd@ at Mon Jan 21 09:55:15 2013 …

2. Monitor Agent 1 Install

-------------------

test-mysql1

*.*.*.1

================================================================

root@mysql1:/mnt/TEST# ./mysqlmonitoragent-2.3.12.2174-linux-glibc2.3-x86-64bit-installer.bin --mode text

Language Selection

Please select the installation language

[1] English - English

[2] Japanese - ?¥本訿

Please choose an option [1] : 1

----------------------------------------------------------------------------

Welcome to the MySQL Enterprise Monitor Agent Setup Wizard.

 ----------------------------------------------------------------------------

 ….

3. Monitor Agent 2 Install

-------------------

TEST-mysql2

*.*.*.2

================================================================

root@mysql2:/mnt/TEST# ./mysqlmonitoragent-2.3.12.2174-linux-glibc2.3-x86-64bit-installer.bin --mode text

Language Selection

Please select the installation language

[1] English - English

[2] Japanese - ?¥本訿

Please choose an option [1] : 1

----------------------------------------------------------------------------

Welcome to the MySQL Enterprise Monitor Agent Setup Wizard.

----------------------------------------------------------------------------

Installation directory

…

[참고 사이트]

원본 위치 <http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-server-install-generic.html>