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.
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# pooladmsystem 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.
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 bootConnect 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 pup2Cloning the Zone
Once you have one zone configured, you can quickly create the others. Stop the configured zone
global# zoneadm -z pup2 haltCopy 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.confBoot and login to your new zone. You will need to configure the hostname and locale information.
# zoneadm -z pup5 boot # zlogin -C pup5Repeat this procedure for the rest of the zones.
Coming soon on this blog... installing the software!
