WARNING: read through the code and understand what the scripts do before
         running them. I AM NOT RESPONSIBLE FOR ANY DAMAGE OR COMPROMISED
         SECURITY WHICH COULD ARISE FROM USING THE SCRIPTS IN THIS REPO.
         USE AT YOUR OWN RISK.


Install requirements
    sudo pip install -r requirements.txt


Setup AWS credentials needed for EC2 access:
    Create ~/.boto:
        [Credentials]
        aws_access_key_id = YOURACCESSKEY
        aws_secret_access_key = YOURSECRETKEY


Edit the fabfile.py for your VPC:
    Change:
        env.ec2_key_pair_name
        env.ec2_vpc_id
        env.ec2_region
        env.ec2_instances, particularly, set the subnet attributes to your own


General notes:
    Everything is done with a VPC. Ideally, we'll need a public and a private
    subnet, though the scripts can be trivially modified to work with just a
    single public subnet if desired. If you don't have a VPC yet, use the
    "VPC Wizard" in the AWS console to create one first. While you can use the
    wizard to setup a VPC with both a public and a private subnet, the wizard
    will also try to create a NAT device, too. Since we can reuse other
    instances for NAT, it's easiest to just let the VPC Wizard create a VPC
    with a public subnet and then create a private subnet afterwards.

    Inspect the 'fab configure_vpn_server' task for exact details on the
    assumed network topology.

    Most commands are distribution agnostic, though some are rhel-specific. We
    test with Amazon Linux AMI, but it should work with Redhat proper and
    CentOS. Some modifications to the fabfile will be needed if using a
    debian-based distro.

    Instances are tagged in EC2 with the role they fulfill for our cluster.
    This makes it easy to select a set of instances and run some fabric task
    against all nodes in a particular role. The tags take the form:
        mysql-cluster-benchmark-$ROLE=1


Setup VPN for easy access into the VPC:
    # Default VPC configuration is very restrictive. This works out well since
    # MySQL Cluster doesn't have any security features. At the same time, it
    # makes it hard for even us to access the machines.
    #
    # To solve, we will create an IPsec VPN with strongswan. Once everything is
    # configured, you will be able to ssh to the machines in your VPC.
    #
    # A security group will be created specifically for the vpn instance. The
    # security group allows only VPN and SSH traffic though. Since we will
    # assign a public IP to this instance, we apply a strict ingress policy.
    #
    # Unfortunately, the internet gateways don't do NAT. To get around this,
    # we'll let the VPN server do NAT so that the MySQL cluster can at least
    # talk to the outside world (for doing things like downloading packages).
    #
    # Of course, if you have other means of connecting to the instances via
    # their private IPs, you can skip this step.

    fab create_vpn_security_group:vpn
    fab launch_instances:vpn
    # Wait for the instance to finish initializing (give it a minute or two).
    fab configure_vpn_server


Setup internal yum repo:
    # Using an internal yum repo makes installation of the MySQL cluster
    # packages easier and reduces amount of external billable traffic
    # originating from the VPC.
    #
    # This makes it possible to also completely isolate the private subnet from
    # the outside world. If desired, you can skip configuring NAT for the
    # private subnet.

    fab configure_yum_repo


Setup cluster:
    # The cluster will be created within a single placement group for maximum
    # performance; particularly, if you specify 10 gigabit instances, then all
    # nodes in the cluster are guaranteed to have full bisection bandwidth of
    # 10 gbps. Furthermore, we default to using AMIs configured for SR-IOV
    # enhanced networking to achieve low latency despite running in a
    # virtualized environment.
    #
    # For simplicity, we create a security group which allows all inbound
    # traffic to reach the instances. Since we are running in a VPC and do not
    # assign public IPs to any of the instances, inbound traffic can only come
    # from within the VPC, which we trust.

    fab create_open_security_group:mysql-cluster
    fab allow_source_traffic:sg=vpn-sg-id,src=mysql-cluster-sg-id
    fab create_placement_group:mysql-cluster
    fab launch_instances:utility-boxes
    fab launch_instances:data-nodes
    fab install_mysql_cluster


Setup load generators:
    fab launch_instances:load-generators
    fab install_load_generators


Sync configs, clear old data, and restart all nodes before benchmarking:
    fab prepare_mysql_cluster_for_benchmarking


Test bandwidth between datanodes:
    fab install_iperf

    On datanode1: iperf --server
    On datanode2: iperf --client=DATANODE_1_IP --interval=5 --time=120 --print_mss --dual


Utils:
    fab spot_prices
    fab list_instances


MySQL Cluster on OSX:
    The MySQL Cluster download page has a dmg which contains all the necessary
    binaries, libraries, and header files. This worked surprisingly well for
    me on OSX 10.9.4.

    Header files (using version 7.3.6):
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/include/storage/ndb/ndbapi

    Run management node on mac:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/ndb_mgmd -f /etc/mysql/config.ini --initial --configdir=/etc/mysql

    Run datanode:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/ndbmtd -c localhost:1186

    Setup mysqld:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/scripts/mysql_install_db --no-defaults --datadir=/mysql/mysqld_data/ --basedir=/usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64

    Run mysqld:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/mysqld --defaults-file=/etc/mysql/my.cnf --user=root &

    Connect to mysqld:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/mysql -h 127.0.0.1 -u root

    Stop mysqld:
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/mysqladmin -u root -h 127.0.0.1 shutdown 

    List out all nodes in the mysql cluster (note: clients count against 255 node limit):
        /usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/bin/ndb_mgm -e show

    Compile ndb-load:
        g++ -O3 -std=c++11 -I/usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/include/storage/ndb -I/usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/include/storage/ndb/ndbapi -L/usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/lib -lmysqlclient_r -lpthread -lm -ldl -lndbclient benchmarks/src/ndb-load.cpp -o benchmarks/src/ndb-load

    Run ndb-load:
        export DYLD_LIBRARY_PATH=/usr/local/mysql-cluster-gpl-7.3.6-osx10.7-x86_64/lib
        ./benchmarks/src/ndb-load


ClusterJ performance:
    Unscientific benchmarks show that ClusterJ is about 3x slower than directly using ndbapi via C++. It seems that ClusterJ creates a lot of garbage; the JVM initiates garbage collections very frequently. But even so, best case query times are still 2x slower than using ndbapi directly.

    Single threaded querying of 1 account with 3000 records (all records returned):
                    Average query time      Network Utilization
        ndbapi      5.9345ms                40MB/s
        ClusterJ    17.041ms                12MB/s


Interesting notes about MySQL Cluster:
    "The memory space defined by DataMemory is also used to store ordered
    indexes, which use about 10 bytes per record. Each table row is represented
    in the ordered index. A common error among users is to assume that all
    indexes are stored in the memory allocated by IndexMemory, but this is not
    the case: Only primary key and unique hash indexes use this memory; ordered
    indexes use the memory allocated by DataMemory."


EC2 performance:
    r3.large instances have "moderate" networking performance; this means a cap
    of 500mbps.

    r3.8xlarge have 10 gigabit networking, which aws does, in fact, deliver. In
    this test, iperf was both sending and receiving traffic at nearly 10gbps!

        [ec2-user@ip-10-0-128-82 ~]$ iperf --client=10.0.128.35 --interval=5 --time=120 --print_mss --dualtest
        ------------------------------------------------------------
        Server listening on TCP port 5001
        TCP window size: 85.3 KByte (default)
        ------------------------------------------------------------
        ------------------------------------------------------------
        Client connecting to 10.0.128.35, TCP port 5001
        TCP window size: 1.23 MByte (default)
        ------------------------------------------------------------
        [  5] local 10.0.128.82 port 47348 connected with 10.0.128.35 port 5001
        [  4] local 10.0.128.82 port 5001 connected with 10.0.128.35 port 49350
        [ ID] Interval       Transfer     Bandwidth
        [  5]  0.0- 5.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4]  0.0- 5.0 sec  5.64 GBytes  9.69 Gbits/sec
        [  5]  5.0-10.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4]  5.0-10.0 sec  5.64 GBytes  9.69 Gbits/sec
        [  5] 10.0-15.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 10.0-15.0 sec  5.64 GBytes  9.69 Gbits/sec
        [  5] 15.0-20.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 15.0-20.0 sec  5.65 GBytes  9.70 Gbits/sec
        [  5] 20.0-25.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 20.0-25.0 sec  5.65 GBytes  9.71 Gbits/sec
        [  5] 25.0-30.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 25.0-30.0 sec  5.65 GBytes  9.71 Gbits/sec
        [  5] 30.0-35.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 30.0-35.0 sec  5.66 GBytes  9.72 Gbits/sec
        [  5] 35.0-40.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 35.0-40.0 sec  5.66 GBytes  9.73 Gbits/sec
        [  5] 40.0-45.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 40.0-45.0 sec  5.67 GBytes  9.73 Gbits/sec
        [  5] 45.0-50.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 45.0-50.0 sec  5.67 GBytes  9.74 Gbits/sec
        [  5] 50.0-55.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 50.0-55.0 sec  5.67 GBytes  9.75 Gbits/sec
        [  5] 55.0-60.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 55.0-60.0 sec  5.67 GBytes  9.74 Gbits/sec
        [  5] 60.0-65.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 60.0-65.0 sec  5.67 GBytes  9.74 Gbits/sec
        [  5] 65.0-70.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 65.0-70.0 sec  5.67 GBytes  9.74 Gbits/sec
        [  5] 70.0-75.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 70.0-75.0 sec  5.67 GBytes  9.73 Gbits/sec
        [  5] 75.0-80.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 75.0-80.0 sec  5.66 GBytes  9.73 Gbits/sec
        [  5] 80.0-85.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 80.0-85.0 sec  5.67 GBytes  9.73 Gbits/sec
        [  5] 85.0-90.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 85.0-90.0 sec  5.66 GBytes  9.72 Gbits/sec
        [  5] 90.0-95.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 90.0-95.0 sec  5.66 GBytes  9.72 Gbits/sec
        [  5] 95.0-100.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 95.0-100.0 sec  5.65 GBytes  9.71 Gbits/sec
        [  5] 100.0-105.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 100.0-105.0 sec  5.65 GBytes  9.71 Gbits/sec
        [  5] 105.0-110.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 105.0-110.0 sec  5.65 GBytes  9.70 Gbits/sec
        [  5] 110.0-115.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  4] 110.0-115.0 sec  5.64 GBytes  9.69 Gbits/sec
        [  5] 115.0-120.0 sec  5.71 GBytes  9.81 Gbits/sec
        [  5]  0.0-120.0 sec   137 GBytes  9.81 Gbits/sec
        [  5] MSS size 8949 bytes (MTU 8989 bytes, unknown interface)
        [  4] 115.0-120.0 sec  5.64 GBytes  9.68 Gbits/sec
        [  4]  0.0-120.0 sec   136 GBytes  9.72 Gbits/sec
        [  4] MSS size 8949 bytes (MTU 8989 bytes, unknown interface)


        [ec2-user@ip-10-0-128-82 ~]$ ping -c 120 -i0.2 -q 10.0.128.35
        PING 10.0.128.35 (10.0.128.35) 56(84) bytes of data.

        --- 10.0.128.35 ping statistics ---
        120 packets transmitted, 120 received, 0% packet loss, time 23799ms
        rtt min/avg/max/mdev = 0.069/0.076/0.118/0.014 ms


    pair of r3.2xlarge:

        [ec2-user@ip-10-0-128-158 ~]$ iperf --client=10.0.128.88 --interval=5 --time=120 --print_mss --dual
        ------------------------------------------------------------
        Server listening on TCP port 5001
        TCP window size: 85.3 KByte (default)
        ------------------------------------------------------------
        ------------------------------------------------------------
        Client connecting to 10.0.128.88, TCP port 5001
        TCP window size:  704 KByte (default)
        ------------------------------------------------------------
        [  5] local 10.0.128.158 port 60494 connected with 10.0.128.88 port 5001
        [  4] local 10.0.128.158 port 5001 connected with 10.0.128.88 port 37995
        [ ID] Interval       Transfer     Bandwidth
        [  5]  0.0- 5.0 sec   711 MBytes  1.19 Gbits/sec
        [  4]  0.0- 5.0 sec   700 MBytes  1.17 Gbits/sec
        [  4]  5.0-10.0 sec   597 MBytes  1.00 Gbits/sec
        [  5]  5.0-10.0 sec   586 MBytes   984 Mbits/sec
        [  5] 10.0-15.0 sec   591 MBytes   992 Mbits/sec
        [  4] 10.0-15.0 sec   590 MBytes   989 Mbits/sec
        [  5] 15.0-20.0 sec   592 MBytes   993 Mbits/sec
        [  4] 15.0-20.0 sec   589 MBytes   988 Mbits/sec
        [  4] 20.0-25.0 sec   589 MBytes   988 Mbits/sec
        [  5] 20.0-25.0 sec   590 MBytes   989 Mbits/sec
        [  5] 25.0-30.0 sec   590 MBytes   990 Mbits/sec
        [  4] 25.0-30.0 sec   591 MBytes   991 Mbits/sec
        [  5] 30.0-35.0 sec   584 MBytes   981 Mbits/sec
        [  4] 30.0-35.0 sec   590 MBytes   991 Mbits/sec
        [  5] 35.0-40.0 sec   592 MBytes   993 Mbits/sec
        [  4] 35.0-40.0 sec   590 MBytes   989 Mbits/sec
        [  5] 40.0-45.0 sec   593 MBytes   994 Mbits/sec
        [  4] 40.0-45.0 sec   588 MBytes   987 Mbits/sec
        [  4] 45.0-50.0 sec   588 MBytes   986 Mbits/sec
        [  5] 45.0-50.0 sec   589 MBytes   989 Mbits/sec
        [  4] 50.0-55.0 sec   593 MBytes   995 Mbits/sec
        [  5] 50.0-55.0 sec   585 MBytes   981 Mbits/sec
        [  5] 55.0-60.0 sec   592 MBytes   993 Mbits/sec
        [  4] 55.0-60.0 sec   590 MBytes   989 Mbits/sec
        [  4] 60.0-65.0 sec   590 MBytes   989 Mbits/sec
        [  5] 60.0-65.0 sec   588 MBytes   987 Mbits/sec
        [  4] 65.0-70.0 sec   556 MBytes   933 Mbits/sec
        [  5] 65.0-70.0 sec   595 MBytes   998 Mbits/sec
        [  4] 70.0-75.0 sec   623 MBytes  1.05 Gbits/sec
        [  5] 70.0-75.0 sec   587 MBytes   985 Mbits/sec
        [  5] 75.0-80.0 sec   591 MBytes   992 Mbits/sec
        [  4] 75.0-80.0 sec   587 MBytes   985 Mbits/sec
        [  4] 80.0-85.0 sec   586 MBytes   984 Mbits/sec
        [  5] 80.0-85.0 sec   590 MBytes   990 Mbits/sec
        [  5] 85.0-90.0 sec   589 MBytes   989 Mbits/sec
        [  4] 85.0-90.0 sec   591 MBytes   991 Mbits/sec
        [  5] 90.0-95.0 sec   589 MBytes   988 Mbits/sec
        [  4] 90.0-95.0 sec   594 MBytes   997 Mbits/sec
        [  4] 95.0-100.0 sec   580 MBytes   973 Mbits/sec
        [  5] 95.0-100.0 sec   591 MBytes   992 Mbits/sec
        [  5] 100.0-105.0 sec   588 MBytes   986 Mbits/sec
        [  4] 100.0-105.0 sec   599 MBytes  1.01 Gbits/sec
        [  5] 105.0-110.0 sec   591 MBytes   992 Mbits/sec
        [  4] 105.0-110.0 sec   590 MBytes   989 Mbits/sec
        [  5] 110.0-115.0 sec   590 MBytes   989 Mbits/sec
        [  4] 110.0-115.0 sec   588 MBytes   986 Mbits/sec
        [  4] 115.0-120.0 sec   589 MBytes   989 Mbits/sec
        [  5] 115.0-120.0 sec   590 MBytes   990 Mbits/sec
        [  5]  0.0-120.0 sec  13.9 GBytes   998 Mbits/sec
        [  5] MSS size 8949 bytes (MTU 8989 bytes, unknown interface)
        [  4]  0.0-120.0 sec  13.9 GBytes   997 Mbits/sec
        [  4] MSS size 8949 bytes (MTU 8989 bytes, unknown interface)


        [ec2-user@ip-10-0-128-158 ~]$ ping -c 120 -i0.2 -q 10.0.128.88
        PING 10.0.128.88 (10.0.128.88) 56(84) bytes of data.

        --- 10.0.128.88 ping statistics ---
        120 packets transmitted, 120 received, 0% packet loss, time 23798ms
        rtt min/avg/max/mdev = 0.101/0.111/0.152/0.012 ms