Tinkering with MySQL Cluster – Part 1 – Preparation

I’m trying to get familiar with MySQL Cluster – though usually most people who work with me use Oracle, I guess the difference in the license cost is making people consider MySQL for their implementations. The concepts are quite different to Oracle RAC, MySQL is based on a ‘shared nothing’ architecture so there aren’t the same requirements for shared disk storage.

Concepts

There are several cluster node types in MySQL

  • Data Nodes – these store and process the data. These ideally with have the most memory and CPU power, as they hold the data in memory, also all data nodes should be the same configuration.
  • Management Nodes – these control the cluster by passing configuration parameters to the data nodes and API nodes. The management software do not require large amounts of memory or CPU.
  • API Nodes – These are nodes which access the MySQL Cluster Data. API nodes include SQL Nodes and nodes running the NDB Native backup and restore (ndb_restore). SQL Nodes run a mysqld process to access the cluster data.

How you split these nodes up amongst the hardware really depends on what you have available. Though you can run multiple data nodes on a single host this is not recommended for resiliency and data availability. However, as the Management node has such a small footprint, you can run a copy of it on each of the SQL nodes if required. The speed of network between the nodes is important, fast and low latency is preferred. As there is no encryption on the cluster traffic, a separate network for the cluster traffic should be used.

Simple Architecture

My Configuration

Sadly I do not have access to 6 separate machines to carry out the configuration. This means that I’m going to make some compromises that are not acceptable in a production environment. I have access to a single v890 for this training exercise, 8 x UltraSPARC IV CPU with 64Gb RAM (very lucky!). To simulate the separate physical nodes I’m going to create Solaris Containers.

Hostname IP-Address Mount Point

CPU Assignment

pup1 10.132.1.185 /pup1 3,19
pup2 10.132.1.186 /pup2 4,20
pup3 10.132.1.187 /pup3 5,21
pup4 10.132.1.188 /pup4 6,22
pup5 10.132.1.189 /pup5 7,23

The CPU assignment isto keep the zones within a single CPU by assigning both cores. You can verify this with the ouput of psrinfo.

# psrinfo -pv

The physical processor has 2 virtual processors (0 16)

 UltraSPARC-IV+ (portid 0 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (1 17)

 UltraSPARC-IV+ (portid 1 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (2 18)

 UltraSPARC-IV+ (portid 2 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (3 19)

 UltraSPARC-IV+ (portid 3 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (4 20)

 UltraSPARC-IV+ (portid 4 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (5 21)

 UltraSPARC-IV+ (portid 5 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (6 22)

 UltraSPARC-IV+ (portid 6 impl 0x19 ver 0x22 clock 1500 MHz)

The physical processor has 2 virtual processors (7 23)

 UltraSPARC-IV+ (portid 7 impl 0x19 ver 0x22 clock 1500 MHz)

Setting up the Resource Pools

Enable the resource pools feature, using the pooladm(1M) command.

global# pooladm -e

The use of the “global#” prompt throughout this example indicates that a command is run in the global zone.

Save the current configuration to a file by using the pooladm command.

global# pooladm -s

See if any pools already exist on the system, using the pooladm(1M) command.

global# pooladm

Since there are no existing pools configured, there is only an entry for “pool pool_default”

Create the processor sets required. The configuration specifies a minimum of 2 CPUs and a maximum of 2 CPUs for each of the sets.

global# poolcfg -c 'create pset pup1-pset (uint pset.min=2;uint pset.max=2)' global# poolcfg -c 'create pset pup2-pset (uint pset.min=2;uint pset.max=2)' global# poolcfg -c 'create pset pup3-pset (uint pset.min=2;uint pset.max=2)' global# poolcfg -c 'create pset pup4-pset (uint pset.min=2;uint pset.max=2)' global# poolcfg -c 'create pset pup5-pset (uint pset.min=2;uint pset.max=2)' global# poolcfg -c 'transfer to pset pup5-pset (cpu 7;cpu 23)' global# poolcfg -c 'transfer to pset pup4-pset (cpu 6;cpu 22)' global# poolcfg -c 'transfer to pset pup3-pset (cpu 5;cpu 21)' global# poolcfg -c 'transfer to pset pup2-pset (cpu 4;cpu 20)' global# poolcfg -c 'transfer to pset pup1-pset (cpu 3;cpu 19)'

Create the resource pools for the processor sets

global# poolcfg -c 'create pool pup1-pool' global# poolcfg -c 'associate pool pup1-pool (pset pup1-pset)' global# poolcfg -c 'create pool pup2-pool' global# poolcfg -c 'associate pool pup2-pool (pset pup2-pset)' global# poolcfg -c 'create pool pup3-pool' global# poolcfg -c 'associate pool pup3-pool (pset pup3-pset)' global# poolcfg -c 'create pool pup4-pool' global# poolcfg -c 'associate pool pup4-pool (pset pup4-pset)' global# poolcfg -c 'create pool pup5-pool' global# poolcfg -c 'associate pool pup5-pool (pset pup5-pset)'

Activate the configuration

global# pooladm -c

You can view the configuration using the pooladm command<

global# pooladm

system default

       string  system.comment

       int     system.version 1

       boolean system.bind-default true

       string  system.poold.objectives wt-load


       pool pup5-pool

               int     pool.sys_id 5

               boolean pool.active true

               boolean pool.default false

               int     pool.importance 1

               string  pool.comment

       pset    pup5-pset


       pool pup4-pool

               int     pool.sys_id 4

               boolean pool.active true

               boolean pool.default false

               int     pool.importance 1

               string  pool.comment

               pset    pup4-pset


       pool pup1-pool

               int     pool.sys_id 1

               boolean pool.active true

               boolean pool.default false

               int     pool.importance 1

               string  pool.comment

               pset    pup1-pset


       pool pool_default

               int     pool.sys_id 0

               boolean pool.active true

               boolean pool.default true

               int     pool.importance 1

               string  pool.comment

               pset    pset_default


       pool pup3-pool

               int     pool.sys_id 3

               boolean pool.active true

               boolean pool.default false

               int     pool.importance 1

               string  pool.comment

               pset    pup3-pset


       pool pup2-pool

               int     pool.sys_id 2

               boolean pool.active true

               boolean pool.default false

               int     pool.importance 1

               string  pool.comment

               pset    pup2-pset


       pset pup5-pset

               int     pset.sys_id 5

               boolean pset.default false

               uint    pset.min 2

               uint    pset.max 2

               string  pset.units population

               uint    pset.load 45

               uint    pset.size 2

               string  pset.comment


               cpu

                       int     cpu.sys_id 23

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 7

                       string  cpu.comment

                       string  cpu.status on-line


       pset pup4-pset

               int     pset.sys_id 4

               boolean pset.default false

               uint    pset.min 2

               uint    pset.max 2

               string  pset.units population

               uint    pset.load 43

               uint    pset.size 2

               string  pset.comment


               cpu

                       int     cpu.sys_id 22

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 6

                       string  cpu.comment

                       string  cpu.status on-line


       pset pup1-pset

               int     pset.sys_id 1

               boolean pset.default false

               uint    pset.min 2

               uint    pset.max 2

               string  pset.units population

               uint    pset.load 45

               uint    pset.size 2

               string  pset.comment


               cpu

                       int     cpu.sys_id 19

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 3

                       string  cpu.comment

                       string  cpu.status on-line


       pset pup3-pset

               int     pset.sys_id 3

               boolean pset.default false

               uint    pset.min 2

               uint    pset.max 2

               string  pset.units population

               uint    pset.load 0

               uint    pset.size 2

               string  pset.comment


               cpu

                       int     cpu.sys_id 21

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 5

                       string  cpu.comment

                       string  cpu.status on-line


       pset pup2-pset

               int     pset.sys_id 2

               boolean pset.default false

               uint    pset.min 2

               uint    pset.max 2

               string  pset.units population

               uint    pset.load 47

               uint    pset.size 2

               string  pset.comment


               cpu

                       int     cpu.sys_id 20

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 4

                       string  cpu.comment

                       string  cpu.status on-line


       pset pset_default

               int     pset.sys_id -1

               boolean pset.default true

               uint    pset.min 1

               uint    pset.max 65536

               string  pset.units population

               uint    pset.load 39

               uint    pset.size 6

               string  pset.comment


               cpu

                       int     cpu.sys_id 17

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 16

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 18

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 1

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 0

                       string  cpu.comment

                       string  cpu.status on-line


               cpu

                       int     cpu.sys_id 2

                       string  cpu.comment

                       string  cpu.status on-line


Creating the Zones

You need a directory to hold the zone’s root filesystem. On my machine, they are fully separated disks, mounted as /pup[1…x]. The filesystem mountpoint with the permission 700 and owned by root. Within the filesystem you need to create a second directory called ‘local’ this will be used to hold the /usr/local directory structure. Usually within zones, /usr is a read only filesystem shared from the global zone, and this will prevent the creation of a /usr/local structure on the zone.

Create the first zone

global# zonecfg -z pup2

pup2: No such zone configured

Use 'create' to begin configuring a new zone.

zonecfg:pup2> create

zonecfg:pup2> set zonepath=/pup2

zonecfg:pup2> add net

zonecfg:pup2:net> set address=10.132.1.186

zonecfg:pup2:net> set physical=ce0

zonecfg:pup2:net> end

zonecfg:pup2> set pool=pup2-pool

zonecfg:pup2> add fs

zonecfg:pup2:fs> set dir=/usr/local

zonecfg:pup2:fs> set special=/pup2/local

zonecfg:pup2:fs> set type=lofs

zonecfg:pup2:fs> set options=[rw,nodevices]

zonecfg:pup2:fs> end

zonecfg:pup2> verify

zonecfg:pup2> commit

global #zoneadm -z pup2 install

Preparing to install zone email-zone
Creating list of files to copy from the global zone.
[Some output was omitted here for brevity]
Zone pup2 is initialized.

Boot the zone

global# zoneadm -z pup2 boot

Connect to the Console for the zone. There will be an initial delay while the SMF services are updated. Then you will be prompted to set basic information such as Locale, Hostname for the zone. To exit the console use the ~. keysequence.

global# zlogin -C pup2

Cloning the Zone

Once you have one zone configured, you can quickly create the others. Stop the configured zone

global# zoneadm -z pup2 halt

Copy the zone configuration from pup2

global# zonecfg -z pup2 export > /tmp/pup5.cfg

Edit the file /tmp/pup5.cfg and change the IP Address, disk paths and devices to match pup5. Use this file to create the pup5 zone.

global# zonecfg -z pup5 -f /tmp/pup5.cfg

Clone pup5 from pup2

global# zoneadm -z pup5 clone pup2

Cloning zonepath /pup2...

grep: can't open /a/etc/dumpadm.conf

Boot and login to your new zone. You will need to configure the hostname and locale information.

# zoneadm -z pup5 boot

# zlogin -C pup5

Repeat this procedure for the rest of the zones.

Advertisements

One thought on “Tinkering with MySQL Cluster – Part 1 – Preparation

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s