Random thoughts: Random thoughts and wafflings of an engineer

The next step in my project is probably the most simple - getting hold of the software.

Go to MySQL's website and select to Download MySQL cluster. I chose to do the tar version for Solaris 10 SPARC 64bit, but there is also a package format installer.

Create a mysql user and group on each of the nodes - this user will own the MySQL software.

# mkdir /export/home
# groupadd -g 101 mysql
# useradd -g mysql -u 1001 -s /bin/ksh -d /export/home/mysql -m mysql
# gunzip mysql-cluster-gpl-6.3.20-solaris10-sparc-64bit.tar.gz
# tar xf mysql-cluster-gpl-6.3.20-solaris10-sparc-64bit.tar

Then you copy the directory to /usr/local/bin and create a symbolic link to it

# mkdir /usr/local/bin

# mv mysql-cluster-gpl-6.3.20-solaris10-sparc-64bit /usr/local/bin
# cd /usr/local/bin
# ln -s mysql-cluster-gpl-6.3.20-solaris10-sparc-64bit mysql

You don't actually need the full software directory on every node, but to allow me to repurpose nodes as necessary I'm taking the full software

Change the software owner and group on the MySQL software to help secure the installation

# cd /usr/local/bin
# chown -R mysql mysql
# chgrp -R mysql mysql

Well, that's the software installed on the nodes. As part of this process I noticed that the disk in use for pup3's root partition is heading off to the great computer scrapyard in the sky so this node may no longer be part of the configuration.

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# 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. 
Coming soon on this blog... installing the software!
 
 

As I'm a goal oriented person, I tend to take certifications for new products that I'm having to use for work. The certification process gives me a learning structure that forces me not to skip over the things that are less interesting, and hopefully gives me a more rounded knowledge base.

I've recently (3 hours ago ;-) ) completed the MySQL 5.0 DBA certification. To use a footballing cliché it was very definitely a game of two halves. The first exam I found very tricky. Perhaps this was because I still had my Oracle head on, and the concept of multiple pluggable storage engines wasn't sticking, but I really struggled to find the correct answers. The second exam I found much more understandable, lots of questions about EXPLAIN.  Both of the MySQL exams are multiple choice format - however, there did seem to be a lot of 'Select all that are correct' questions.

I used this book http://www.mysql.com/certification/studyguides/study-guide-5.0.html to help me along with the incredibly detailed MySQL reference manual.

Sometimes to speed up the setup of identical disk systems you might decide to use SSCS rather than the Common Array Manager GUI to configure them. 

All syntax mentioned here can be found in http://dlc.sun.com/pdf/820-4192-12/820-4192-12.pdf 

First login to your CAMS server using sccs

root@c14-48 # sscs login -h c14-48 -u root

This only tells you if the login has failed, if you don't get a response, everything is ok and you're now connected to your CAMS server until it times out.

Select the profile for the storage you are setting up. You can get a list of profiles on your array (esal-2540-2 in my example) using

root@c14-48 # sscs list -a esal-2540-2 profile
Profile: Oracle_OLTP_HA
Profile: Oracle_DSS
Profile: Oracle_9_VxFS_HA
Profile: Sun_SAM-FS
Profile: High_Performance_Computing
Profile: Oracle_9_VxFS
Profile: Oracle_10_ASM_VxFS_HA
Profile: Random_1
Profile: Sequential
Profile: Sun_ZFS
Profile: Sybase_OLTP_HA
Profile: Sybase_DSS
Profile: Oracle_8_VxFS
Profile: Mail_Spooling
Profile: Microsoft_NTFS_HA
Profile: Microsoft_Exchange
Profile: Sybase_OLTP
Profile: Oracle_OLTP
Profile: VxFS
Profile: Default
Profile: NFS_Mirroring
Profile: NFS_Striping
Profile: Microsoft_NTFS
Profile: High_Capacity_Computing

You can get more detail on a profile using

 root@c14-48 #  sscs list -a esal-2540-2 profile Oracle_9_VxFS_HA

 Profile: Oracle_9_VxFS_HA
Profile In Use: no
Factory Profile: yes
Description:
Oracle 9 over VxFS (High Availability)
 RAID Level:  1 
 Segment Size: 128 KB 
 Read Ahead: on
 Optimal Number of Drives:  variable 
 Disk Type: SAS
 Dedicated Hot Spare: no

The next step is to create my pool 'bt-poc'

The syntax for the command is

sscs create -a <array name> -p <profile name> pool <pool name> 

root@c14-48 #   sscs create -a  esal-2540-2 -p Oracle_9_VxFS_HA pool bt-poc

Logically, at this point you want to create a logical disk, however you can't create one directly but you can create one implicitly as part of the volume creation

sscs create -a <array name> -p <pool name> -s <size>

-n <number of disks in vdisk> volume <volume name> 

root@c14-48 # sscs create -a  esal-2540-2 -p bt-poc -s 15gb -n 6 volume vol1 

Check the name of you new virtual disk using

root@c14-48 # sscs list -a esal-2540-2 vdisk
Virtual Disk: 1

root@c14-48 #  sscs list -a esal-2540-2 vdisk 1
Virtual Disk: 1
  Status:               Optimal
  State:                Ready
  Number of Disks:      6
  RAID Level:           1
  Total Capacity:       836.690 GB
  Configured Capacity:  15.000 GB
  Available Capacity:   821.690 GB
  Array Name:           esal-2540-2
  Array Type:           2540
  Disk Type:            SAS
  Maximal Volume Size:  821.690 GB
  Associated Disks:
    Disk:               t85d01
    Disk:               t85d02
    Disk:               t85d03
    Disk:               t85d04
    Disk:               t85d05
    Disk:               t85d06
  Associated Volumes:
    Volume:             vol1

Since I need another 4 identical volumes on this virtual disk I can be lazy just script up the creation. 

root@c14-48 # for i in 2 3 4 5
do
sscs create -a esal-2540-2 -p bt-poc -s 15gb -v 1 volume vol${i}
done

The sccs commands are asynchronous - they return before the action is completed, so long running tasks like creating a RAID5 volume will still be running while you create your volumes on it.  

Can delete any mix ups simply

root@c14-48 # sscs delete -a  esal-2540-2 volume vol3

At this point, you can either map your volumes to the default storage domain, and all hosts connected to the storage will be able to see all the volumes, or you can do LUN mapping and limit which hosts can see which volumes.

Map to the default storage domain

for i in 1 2 3 4 5
do
	sscs map -a esal-2540-2 volume vol${i}
done

Create host based mappings

Create your hosts

root@c14-48 # sscs create -a esal-2540-2 host dingo 

root@c14-48 # sscs create -a esal-2540-2 host chief 

Create the initators that map to the World Wide Number (WWN) for the Host Bus Adaptor (HBA) of each machine.

First find your WWN - you can do this either by looking on the storage switch if you have one, or on the hosts that will be accessing the storage.

Looking on the host you issue the command fcinfo hba-port, and look for the HBA port WWN associated with the correct  fibre channel devices. I've highlighted the entries in red for clarity.

dingo # fcinfo hba-port
HBA Port WWN: 21000003ba9b3679
        OS Device Name: /dev/cfg/c1
        Manufacturer: QLogic Corp.
        Model: 2200
        Firmware Version: 2.01.145
        FCode/BIOS Version: ISP2200 FC-AL Host Adapter Driver: 1.15 04/03/22
        Type: L-port
        State: online
        Supported Speeds: 1Gb
        Current Speed: 1Gb
        Node WWN: 20000003ba9b3679
HBA Port WWN: 210000e08b09965e
        OS Device Name: /dev/cfg/c3
        Manufacturer: QLogic Corp.
        Model: 375-3108-xx
        Firmware Version: 3.03.27
        FCode/BIOS Version:  fcode: 1.13;
        Type: N-port
        State: online
        Supported Speeds: 1Gb 2Gb
        Current Speed: 2Gb
        Node WWN: 200000e08b09965e
HBA Port WWN: 210100e08b29965e
        OS Device Name: /dev/cfg/c4
        Manufacturer: QLogic Corp.
        Model: 375-3108-xx
        Firmware Version: 3.03.27
        FCode/BIOS Version:  fcode: 1.13;
        Type: N-port
        State: online
        Supported Speeds: 1Gb 2Gb
        Current Speed: 2Gb
        Node WWN: 200100e08b29965e

root@c14-48 # sscs create -a esal-2540-2 -w 210000e08b09965e -h dingo initiator dingo-1 

root@c14-48 # sscs create -a esal-2540-2 -w 210000e08b29965e -h dingo initiator dingo-2 

root@c14-48 # sscs map -a esal-2540-2 -v vol1,vol3 host dingo

And there you have it - you've formatted and mapped your volumes without needing the web interface.