P A R A L L E L U N I V E R S E Server 5.5-3.1 Benchmarks - Parallel Network Query over Wide Area Network (WAN) TOKYO SAO PAULO N. VIRGINIA 54.199.**.*** 54.232.***.*** 50.19.**.** --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | | Server | | Server | | 1 | | 2 | | | --------- --------- --------- | | | | | | | | | ------------------------------------------- Internet (WAN) Servers: Amazon AWS EC2 m1.xlarge instance with 15GB of memory on Amazon Linux 2012.09 MyISAM (--innodb_buffer_pool_size=0) --key_buffer_size=3G t1 ( `region` char(1) DEFAULT NULL, (A thru F and repeats) `idn` int(11) DEFAULT NULL, (0 thru 9,999) `rnd` int(11) DEFAULT NULL, (0 thru 9,999 randomly distributed) `grp` int(11) DEFAULT NULL (0 thru 99 in steps of 3, modulo 100) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 (10 thousand records) +--------+-----+------+-----+ | region | idn | rnd | grp | +--------+-----+------+-----+ | A | 0 | 7141 | 0 | | B | 1 | 1956 | 3 | | C | 2 | 1254 | 6 | | D | 3 | 5150 | 9 | | E | 4 | 5090 | 12 | | F | 5 | 6371 | 15 | | A | 6 | 7598 | 18 | | B | 7 | 7337 | 21 | | C | 8 | 3816 | 24 | | D | 9 | 9694 | 27 | . . . | B | 9997 | 2743 | 91 | | C | 9998 | 2517 | 94 | | D | 9999 | 894 | 97 | +--------+------+------+-----+ t2 same as t1 but with a different random set and a key (idn) t3 same as t1 but with a different random set and a key (idn) Tables (Link via Federated table) Aux1 Aux2 Base t1 (local) t1 -> Aux1:t1 t1 -> Aux1:t1 t2 -> Aux2:t2 t2 (local) t2 -> Aux2:t2 t3 -> Base:t3 t3 -> Base:t3 t3 (local) #1) 3 MyISAM table benchmark SELECT count(*) FROM t1,t2,t3 WHERE t1.rnd = t2.idn and t2.rnd = t3.idn and t3.grp >= 0; submitted to the base server Execution plan for Non-Parallel Network Query +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.rnd | 12 | | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ Execution plan for Parallel Network Query +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10000 | Processed in parallel @54.199.**.***:3307:63306 | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.rnd | 12 | Processed in parallel @54.232.***.***:3307:63306 | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | Using where; Processed in parallel | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ --------------------------------------------------------------------------------------- | Cache State | Non-Parallel Network Query | Parallel Network Query | Speed Improvement | |-------------|----------------------------|------------------------|-------------------| | Cold | 25 min 38 sec | 15.94 sec | 96 x | |-------------|----------------------------|------------------------|-------------------| | Warm | 23 min 32 sec | 11.74 sec | 120 x | --------------------------------------------------------------------------------------- InnoDB --innodb_buffer_pool_size=10G t4 InnoDB table, contents same as t1 t5 InnoDB table, contents same as t2 t6 InnoDB table, contents same as t3 Tables (Link via Federated table) Aux1 Aux2 Base t4 (local) t4 -> Aux1:t4 t4 -> Aux1:t4 t5 -> Aux2:t5 t5 (local) t5 -> Aux2:t5 t6 -> Base:t6 t6 -> Base:t6 t6 (local) #2) 3 InnoDB table benchmark SELECT count(*) FROM t4,t5,t6 WHERE t4.rnd = t5.idn and t5.rnd = t6.idn and t6.grp >= 0; submitted to the base server Execution plan for Non-Parallel Network Query +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ | 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 10000 | | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 12 | | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+-------------+ Execution plan for Parallel Network Query +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ | 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 10000 | Processed in parallel @54.199.**.***:3307:63306 | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 12 | Processed in parallel @54.232.***.***:3307:63306 | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | Using where; Processed in parallel | +----+-------------+-------+------+---------------+------+---------+--------------------+-------+--------------------------------------------------+ --------------------------------------------------------------------------------------- | Cache State | Non-Parallel Network Query | Parallel Network Query | Speed Improvement | |-------------|----------------------------|------------------------|-------------------| | Cold | 21 min 46 sec | 15.16 sec | 86 x | |-------------|----------------------------|------------------------|-------------------| | Warm | 21 min 43 sec | 11.63 sec | 112 x | --------------------------------------------------------------------------------------- Tables used in the benchmarks are available as create table sql scripts in the tar file. $ wget http://www.paralleluniverse-inc.com/pnq_wan.tar pnq_wan_t1_dump.sql pnq_wan_t2_dump.sql pnq_wan_t3_dump.sql t4: copy t1 and alter storage engine t5: copy t2 and alter storage engine (add key) t6: copy t3 and alter storage engine (add key)