1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

The best MySql Cluster Setup I have benchmarked to date

Discussion in 'Databases' started by blockdos, Nov 1, 2012.

  1. #1
    Lately, I have been working trying out different mysql clustering environments. There always seems to be a drawback of the traditional methods. Either one is good for write, but not so much for read or vice versa. Also I have never found a stable multi master setup that performs well. Then I found Galera and installed it using this very handy tool at http://severalnines.com/ . I proceeded to bench it and perform redundancy tests, It did great on every level It is a multi-master setup that provides maximum performance doing reads and writes. As far as the comparisons and explanations, I really cannot do it any better then they do at severalnines, go I would advise anyone looking for the best cluster software to check it out. It also includes config tools for all other cluster setups as well. It literally saved me hours of work using the config tool.


    For now we have 4 nodes (Vmware vms). 3 serve as master nodes, and one as cluster controller/load balancer. With our tests it performed higher on read and write then all other clustering methods.

    We are using public port ips as some remote sites will have to use this so I have not tested against lan latency.

    Any other galera users here? And if anyone wants to compare benches, that would be nice.
     
    blockdos, Nov 1, 2012 IP
  2. blockdos

    blockdos Active Member

    Messages:
    96
    Likes Received:
    0
    Best Answers:
    3
    Trophy Points:
    71
    #2
    Sysbench results, and with anything hardware matters most but I was real pleased with this on 3 vps - OLTP test statistics:
    queries performed:
    read: 1401876
    write: 500598
    other: 200211
    total: 2102685
    transactions: 100000 (630.41 per sec.)
    deadlocks: 134 (0.84 per sec.)
    read/write requests: 1902474 (11993.37 per sec.)
    other operations: 200211 (1262.15 per sec.)

    Test execution summary:
    total time: 158.6272s
    total number of events: 100000
    total time taken by event execution: 2537.2173
    per-request statistics:
    min: 12.37ms
    avg: 25.37ms
    max: 153.99ms
    approx. 95 percentile: 33.48ms

    Threads fairness:
    events (avg/stddev): 6250.0000/527.56
    execution time (avg/stddev): 158.5761/0.01
     
    blockdos, Nov 1, 2012 IP
  3. abhirampathak3

    abhirampathak3 Peon

    Messages:
    44
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    For anyone familiar with using FKs with InnoDB, a lot of this will be very similar – that’s intentional in the design of this Cluster feature. Throughout this section we will use the tables shown in the above figure.

    First of all, create the tables and add some data:

    mysql> CREATE DATABASE clusterdb;USE clusterdb;
    mysql> CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY, country VARCHAR(30))
    ENGINE=ndb;
    mysql> INSERT INTO counties VALUES ('Berkshire','England'),('Shropshire','England'),
    ('Oxfordshire','England'),('Buckinghamshire','England');
    mysql> CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county VARCHAR(30),
    INDEX county_county_index (county), CONSTRAINT county_town FOREIGN KEY (county)
    REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE=ndb;
    mysql> INSERT INTO towns VALUES ('Maidenhead','Berkshire'),('Reading','Berkshire'),
    ('Shrewsbury','Shropshire'),('Oxford','Oxfordshire');
    mysql> SELECT * FROM counties;SELECT * FROM towns;
    +-----------------+---------+
    | county | country |
    +-----------------+---------+
    | Shropshire | England |
    | Buckinghamshire | England |
    | Berkshire | England |
    | Oxfordshire | England |
    +-----------------+---------+
    4 rows in set (0.00 sec)

    +------------+-------------+
    | town | county |
    +------------+-------------+
    | Reading | Berkshire |
    | Shrewsbury | Shropshire |
    | Maidenhead | Berkshire |
    | Oxford | Oxfordshire |
    +------------+-------------+
    4 rows in set (0.00 sec)
     
    abhirampathak3, Nov 19, 2012 IP