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 to Speed up Slow Server 2 Server Parallel Network Query 192.168.0.3 192.168.0.4 --------- --------- |Auxiliary| | Base | | Server | | Server | | | | | --------- --------- | | | | | | --------------------- Gigabit Ethernet LAN Aux1: 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=800M Base: --key_buffer_size=1200M @inter_table_buffer_size=131072 (128K) @inter_server_socket_size=131072 (128K) @inter_server_socket_timeout=10 (default, in seconds) @inter_server_socket_retry=39 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) t4 same as t1 but with a different random set and a key (idn) t5 same as t1 but with a different random set and a key (idn) All tables are replicated at the auxiliary server. #1) 5 MyISAM table benchmark SELECT count(*) FROM t1,t2,t3,t4,t5 WHERE t1.rnd = t2.idn and t2.rnd = t3.idn and t3.rnd = t4.idn and t4.rnd = t5.idn and t5.grp >= 0; submitted to the base server Execution plan for Non-Parallel 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 | 1 | | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.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.3:3307:63306 | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | Processed in parallel | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 1 | Using where; Processed in parallel | +----+-------------+-------+------+---------------+------+---------+--------------------+---------+-----------------------------------------------+ ------------------------------------------------------------------------- | Cache | Non-Parallel Query | Parallel Network Query | Speed Improvement | | State | | | | |-------|--------------------|------------------------|-------------------| | Cold | 47.52 sec | 18.54 sec | 2.6 x | |-------|--------------------|------------------------|-------------------| | Warm | 10.90 sec | 4.65 sec | 2.3 x | ------------------------------------------------------------------------- 3 Server Parallel Network Query 192.168.0.2 192.168.0.3 192.168.0.4 --------- --------- --------- |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 @inter_table_buffer_size=131072 (128K) @inter_server_socket_size=131072 (128K) @inter_server_socket_timeout=10 (default, in seconds) @inter_server_socket_retry=39 t6 same as t1 but with a different random set and a key (idn) t7 same as t1 but with a different random set and a key (idn) All tables are replicated at the auxiliary servers. #2) 7 MyISAM table benchmark SELECT count(*) FROM t1,t2,t3,t4,t5,t6,t7 WHERE t1.rnd = t2.idn and t2.rnd = t3.idn and t3.rnd = t4.idn and t4.rnd = t5.idn and t5.rnd = t6.idn and t6.rnd = t7.idn and t7.grp >= 0; submitted to the base server Execution plan for Non-Parallel 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 | 1 | | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 1 | | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | | | 1 | SIMPLE | t7 | ref | idn | idn | 4 | my_database.t6.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.2:3307:63306 | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.rnd | 1 | Processed in parallel @192.168.0.2:3307:63306 | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | Processed in parallel @192.168.0.2:3307:63306 | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | Processed in parallel | | 1 | SIMPLE | t7 | ref | idn | idn | 4 | my_database.t6.rnd | 1 | Using where; Processed in parallel | +----+-------------+-------+------+---------------+------+---------+--------------------+---------+-----------------------------------------------+ ------------------------------------------------------------------------- | Cache | Non-Parallel Query | Parallel Network Query | Speed Improvement | | State | | | | |-------|--------------------|------------------------|-------------------| | Cold | 1 min 23.30 sec | 20.51 sec | 4.1 x | |-------|--------------------|------------------------|-------------------| | Warm | 17.21 sec | 5.03 sec | 3.4 x | ------------------------------------------------------------------------- 4 Server Parallel Network Query 192.168.0.1 192.168.0.2 192.168.0.3 192.168.0.4 --------- --------- --------- --------- |Auxiliary| |Auxiliary| |Auxiliary| | Base | | Server | | Server | | Server | | Server | | 1 | | 2 | | 3 | | | --------- --------- --------- --------- | | | | | | | | | | | | ----------------------------------------------------------------- Gigabit Ethernet LAN Aux1: AMD FX-4130 (4 cores) with 8GB of memory on Centos 6.2-64bit Aux2: AMD FX-4130 (4 cores) with 8GB of memory on Centos 6.2-64bit Aux3: 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=1600M Aux3: --key_buffer_size=800M Base: --key_buffer_size=1200M @inter_table_buffer_size=524288 (512K) @inter_server_socket_size=524288 (512K) @inter_server_socket_timeout=20 (seconds) @inter_server_socket_retry=1000 t8 same as t1 but with a different random set and a key (idn) t9 same as t1 but with a different random set and a key (idn) t10 same as t1 but with a different random set and a key (idn) t11 same as t1 but with a different random set and a key (idn) t12 same as t1 but with a different random set and a key (idn) t13 same as t1 but with a different random set and a key (idn) All tables are replicated at the auxiliary servers. #3) 13 MyISAM table benchmark SELECT count(*) FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13 WHERE t1.rnd = t2.idn and t2.rnd = t3.idn and t3.rnd = t4.idn and t4.rnd = t5.idn and t5.rnd = t6.idn and t6.rnd = t7.idn and t7.rnd = t8.idn and t8.rnd = t9.idn and t9.rnd = t10.idn and t10.rnd = t11.idn and t11.rnd = t12.idn and t12.rnd = t13.idn and t13.grp >= 0; submitted to the base server Execution plan for Non-Parallel 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 | 1 | | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 1 | | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | | | 1 | SIMPLE | t7 | ref | idn | idn | 4 | my_database.t6.rnd | 1 | | | 1 | SIMPLE | t8 | ref | idn | idn | 4 | my_database.t7.rnd | 1 | | | 1 | SIMPLE | t9 | ref | idn | idn | 4 | my_database.t8.rnd | 1 | | | 1 | SIMPLE | t10 | ref | idn | idn | 4 | my_database.t9.rnd | 1 | | | 1 | SIMPLE | t11 | ref | idn | idn | 4 | my_database.t10.rnd | 1 | | | 1 | SIMPLE | t12 | ref | idn | idn | 4 | my_database.t11.rnd | 1 | | | 1 | SIMPLE | t13 | ref | idn | idn | 4 | my_database.t12.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 | 1 | Processed in parallel @192.168.0.1:3307:63306 | | 1 | SIMPLE | t3 | ref | idn | idn | 4 | my_database.t2.rnd | 1 | Processed in parallel @192.168.0.1:3307:63306 | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.rnd | 1 | Processed in parallel @192.168.0.1:3307:63306 | | 1 | SIMPLE | t5 | ref | idn | idn | 4 | my_database.t4.rnd | 1 | Processed in parallel @192.168.0.2:3307:63306 | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.rnd | 1 | Processed in parallel @192.168.0.2:3307:63306 | | 1 | SIMPLE | t7 | ref | idn | idn | 4 | my_database.t6.rnd | 1 | Processed in parallel @192.168.0.2:3307:63306 | | 1 | SIMPLE | t8 | ref | idn | idn | 4 | my_database.t7.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t9 | ref | idn | idn | 4 | my_database.t8.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t10 | ref | idn | idn | 4 | my_database.t9.rnd | 1 | Processed in parallel @192.168.0.3:3307:63306 | | 1 | SIMPLE | t11 | ref | idn | idn | 4 | my_database.t10.rnd | 1 | Processed in parallel | | 1 | SIMPLE | t12 | ref | idn | idn | 4 | my_database.t11.rnd | 1 | Processed in parallel | | 1 | SIMPLE | t13 | ref | idn | idn | 4 | my_database.t12.rnd | 1 | Using where; Processed in parallel | +----+-------------+-------+------+---------------+------+---------+---------------------+---------+------------------------------------------------+ ------------------------------------------------------------------------- | Cache | Non-Parallel Query | Parallel Network Query | Speed Improvement | | State | | | | |-------|--------------------|------------------------|-------------------| | Cold | 3 min 11.87 sec | 35.82 sec | 5.4 x | |-------|--------------------|------------------------|-------------------| | Warm | 47.04 sec | 6.65 sec | 7.1 x | ------------------------------------------------------------------------- Tables used in the benchmarks are available as create table sql scripts in the tar file. $ wget http://www.paralleluniverse-inc.com/pnq_sss.tar pnq_sss_t1_dump.sql pnq_sss_t2_dump.sql pnq_sss_t3_dump.sql pnq_sss_t4_dump.sql pnq_sss_t5_dump.sql pnq_sss_t6_dump.sql pnq_sss_t7_dump.sql pnq_sss_t8_dump.sql pnq_sss_t9_dump.sql pnq_sss_t10_dump.sql pnq_sss_t11_dump.sql pnq_sss_t12_dump.sql pnq_sss_t13_dump.sql