Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Testing MariaDB ColumnStore on Windows Using Hyper-V

DZone's Guide to

Testing MariaDB ColumnStore on Windows Using Hyper-V

Learn about Hyper-V, which makes it possible to run and test multiple types of MariaDB ColumnStore deployments on a single Windows machine.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

Hyper-V is the native virtualization technology contained within Windows 10 Professional (and Windows Server and Windows 8 Pro). Using Hyper-V to create one or more virtual machines running Linux, it is possible to run and test multiple types of MariaDB ColumnStore deployments on a single Windows machine.

Networking

Hyper-V provides very flexible and powerful software defined networking. However, some of this power is not available within the graphical Hyper-V Manager application and requires the use of PowerShell commands. The following procedure will enable a functional network that offers to each Linux VM:

  • A static IP address.
  • The ability to reach external resources such as package repositories.
  • Optionally, mapping a network port on the Windows host to a port on a VM instance.

Start an instance of PowerShell with administrator privileges (right-click > Run as administrator) and run the following three commands:

New-VMSwitch –SwitchName “NATSwitch” –SwitchType Internal 
New-NetIPAddress –IPAddress 172.21.21.1 -PrefixLength 24 -InterfaceAlias "vEthernet (NATSwitch)" 
New-NetNat –Name MyNATnetwork –InternalIPInterfaceAddressPrefix 172.21.21.0/24 

The commands perform the following:

  1. Creates a new internal virtual switch named NATSwitch.
  2. Configures a gateway address of 172.21.21.1.
  3. Configures a Network Address Translation (NAT) rule to allow the VMs running on the 172.21.21 subnet to communicate outbound to the Windows host network (and thus the internet).

Virtual Machine Setup

Once the virtual switch network has been created, create as many VM instances as you need. For running ColumnStore, I recommend creating these with:

  • 4Gb memory (2Gb is the recommended lower limit per ColumnStore host if you have less physical memory).
  • 40GB disk (more or less depending on how much data you will use for testing, 10GB could be good for very small test data sets).
  • As many vCPUs as you have CPU cores.
  • Select NATSwitch as the network virtual switch.
  • A nongraphical Linux ISO installation image, for example, CentOS 7 Minimal.
  • Disable Secure Boot checkbox in Security settings, as this is generally not compatible with downloaded Linux ISO images.

Start and connect to the VM from the Hyper-V application to install the Linux distribution on the VM. During the install setup or post-installation, configure a static IP for the host on the subnet 172.21.21. To configure this post installation, edit /etc/sysconfig/network-scripts/ifcfg-eth0 as root to be something like:

TYPE="Ethernet" 
BOOTPROTO="static" 
UUID="be5574cf-1728-4532-8495-727e1eb157b3" 
DEVICE="eth0" ONBOOT="yes" 
IPADDR=172.21.21.2 
NETMASK=255.255.255.0 
GATEWAY=172.21.21.1 
DNS1=8.8.8.8 

Notes:

  • IPADDR is 172.21.21.2, this should be unique within the 172.21.21 subnet for each new virtual machine.
  • GATEWAY is set to 172.21.21.1 for all hosts.
  • DNS1 is set to 8.8.8.8 to use Google DNS (this may be changed to your preferred DNS host).

In addition, you can configure the/etc/hosts file on each Linux VM to have hostnames to simplify access between servers, for example:

127.0.0.1 	localhost localhost.localdomain
::1 		localhost localhost.localdomain
172.21.21.2 centos1 
172.21.21.3 centos2
172.21.21.4 centos3 

On the Windows host, the same can be done to ease access by opening an editor with administrator privileges and editing the file C:\Windows\System32\drivers\etc\hosts:

172.21.21.2 centos1 
172.21.21.3 centos2 
172.21.21.4 centos3 

Using the Linux VMs

You can access each VM through the Hyper-V manager. However, cut and paste doesn't work well between Windows and Linux VMs, so I recommend using the SSH client putty in conjunction with mtputty, which provides a multiple tab view on top of putty.

Sometimes, logging in to use SSH access to the VMs can be slow. The following edits to /etc/ssh/sshd_config will resolve this:

GSSAPIAuthentication no 
UseDNS no 

After this restart SSHD — systemctl restart sshd on CentOS 7.

Optionally, an additional Hyper-V NAT rule can be configured to enable port forwarding from the Windows host to a given VM port. Assuming 172.21.21.2 is a VM running a ColumnStore UM module, the following PowerShell command will allow SQL clients to connect to the Windows host IP address to issue queries to the ColumnStore instance running on that VM:

Add-NetNatStaticMapping -NatName “MyNATnetwork” -Protocol TCP -ExternalIPAddress 0.0.0.0 -InternalIPAddress 172.21.21.2 -InternalPort 3306 -ExternalPort 3306

Credit goes to Thomas Maurer's blog for the Hyper-V scripts.

Other Options

This deployment setup enables local development testing of a multi node cluster of MariaDB ColumnStore. However, other options exist:

  1. Utilize another Virtualization technology such as VirtualBox. This will also allow you to run multiple VMs.
  2. Use Docker for Windows (Windows 10 Pro) or Docker Toolkit (other Windows versions) and utilize our reference Dockerfile for single server deployment. Some changes are being worked on in our upcoming 1.1 release with a goal of making it easier to support containers for multi server deployment.
  3. Use Bash for Windows (requires Windows 10 Creator's Edition) and follow the Ubuntu 16 installation instructions. This is limited to single server and a bash prompt must be running for Linux processes to remain running.

If you have a Mac, options #1 and #2 are the best approaches.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,mariadb ,hyper-v ,columnstore ,database testing

Published at DZone with permission of David Thompson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}