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 Local Area Network (LAN) 192.168.0.1 192.168.0.2 192.168.0.3 --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | | Server | | Server | | 1 | | 2 | | | --------- --------- --------- | | | | | | | | | ------------------------------------------- Gigabit Ethernet LAN Aux1: AMD FX-4130 (4 cores) with 8GB of memory on Centos 6.2-64bit Aux2: AMD Phenom-9850 (4 cores) with 4GB of memory on Centos 6.2-64bit Base: Intel Core i7 CPU 920 (4 cores) with 6GB of memory on Fedora 18-64bit MyISAM (--innodb_buffer_pool_size=0) Aux1: --key_buffer_size=1600M Aux2: --key_buffer_size=800M Base: --key_buffer_size=1200M t1 ( `region` char(1) DEFAULT NULL, (A thru F and repeats) `idn` int(11) DEFAULT NULL, (0 thru 999,999) `rnd` int(11) DEFAULT NULL, (0 thru 999,999 randomly distributed) `grp` int(11) DEFAULT NULL (0 thru 99 in steps of 3, modulo 100) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 (1 million records) +--------+-----+--------+-----+ | region | idn | rnd | grp | +--------+-----+--------+-----+ | A | 0 | 561574 | 0 | | B | 1 | 198852 | 3 | | C | 2 | 245509 | 6 | | D | 3 | 834233 | 9 | | E | 4 | 974219 | 12 | | F | 5 | 230856 | 15 | | A | 6 | 965313 | 18 | | B | 7 | 911917 | 21 | | C | 8 | 242133 | 24 | | D | 9 | 880533 | 27 | . . . | B | 999997 | 571972 | 91 | | C | 999998 | 130371 | 94 | | D | 999999 | 411491 | 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 | 1000000 | | | 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 | 1000000 | Processed in parallel @192.168.0.1:3307:63306 | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.rnd | 12 | Processed in parallel @192.168.0.2: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 | 7 min 44 sec | 8.24 sec | 56 x | |-------------|----------------------------|------------------------|-------------------| | Warm | 7 min 39 sec | 4.19 sec | 110 x | --------------------------------------------------------------------------------------- InnoDB Aux1: --innodb_buffer_pool_size=6G Aux2: --innodb_buffer_pool_size=3G Base: --innodb_buffer_pool_size=4G 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 | 1000000 | | | 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 | 1000000 | Processed in parallel @192.168.0.1:3307:63306 | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 12 | Processed in parallel @192.168.0.2: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 | 8 min 27 sec | 16.78 sec | 30 x | |-------------|----------------------------|------------------------|-------------------| | Warm | 8 min 10 sec | 3.59 sec | 136 x | --------------------------------------------------------------------------------------- Tables used in the benchmarks are available as create table sql scripts in the tar file. $ wget http://www.paralleluniverse-inc.com/pnq_lan.tar pnq_lan_t1_dump.sql pnq_lan_t2_dump.sql pnq_lan_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)