P A R A L L E L U N I V E R S E Server 5.5-2 Benchmarks (16 Table Join) Amazon Web Services Cluster Compute Eight Extra Large Instance Server: 2 Intel Xeon E5-2670 processors (2x8 cores) with 60.5 GB of memory and 3370 GB of instance storage (cc2.8xlarge) running Cluster Compute Amazon Linux 64 bit OS. (Using warm cache: 2nd and subsequent runs where tables have already been loaded into the memory.) MyISAM t1 ( `region` char(1) DEFAULT NULL, (A thru F and repeats) `idn` int(11) DEFAULT NULL, (0 thru 999,999) `rev_idn` int(11) DEFAULT NULL, (999,999 thru 0) `grp` int(11) DEFAULT NULL (0 thru 99 in steps of 3, modulo 100) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 (1 million records) +--------+------+---------+------+ | region | idn | rev_idn | grp | +--------+------+---------+------+ | A | 0 | 999999 | 0 | | B | 1 | 999998 | 3 | | C | 2 | 999997 | 6 | | D | 3 | 999996 | 9 | | E | 4 | 999995 | 12 | | F | 5 | 999994 | 15 | | A | 6 | 999993 | 18 | | B | 7 | 999992 | 21 | | C | 8 | 999991 | 24 | . . . | C | 32 | 999967 | 96 | | D | 33 | 999966 | 99 | | E | 34 | 999965 | 2 | | F | 35 | 999964 | 5 | | A | 36 | 999963 | 8 | | B | 37 | 999962 | 11 | . . . | B | 999997 | 2 | 91 | | C | 999998 | 1 | 94 | | D | 999999 | 0 | 97 | +--------+--------+---------+------+ t2 same as t1 with a key (idn) t3 same as t1 with a key (rev_idn) t4 same as t1 with a key (idn) t5 same as t1 with a key (rev_idn) t6 same as t1 with a key (idn) t7 same as t1 with a key (rev_idn) t8 same as t1 with a key (idn) t9 same as t1 with a key (rev_idn) t10 same as t1 with a key (idn) t11 same as t1 with a key (rev_idn) t12 same as t1 with a key (idn) t13 same as t1 with a key (rev_idn) t14 same as t1 with a key (idn) t15 same as t1 with a key (rev_idn) t16 same as t1 with a key (idn) 16 MyISAM Table Join Benchmark SELECT straight_join count(*) FROM t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16 WHERE t1.idn=t2.idn and t2.rev_idn=t3.rev_idn and t3.idn=t4.idn and t4.rev_idn=t5.rev_idn and t5.idn=t6.idn and t6.rev_idn=t7.rev_idn and t7.idn=t8.idn and t8.rev_idn=t9.rev_idn and t9.idn=t10.idn and t10.rev_idn=t11.rev_idn and t11.idn=t12.idn and t12.rev_idn=t13.rev_idn and t13.idn=t14.idn and t14.rev_idn=t15.rev_idn and t15.idn=t16.idn and t16.grp >= 0; Straight join is used to reduce query optimization time. Execution plan +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ | 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 | 5 | my_database.t1.idn | 1 | Using where | | 1 | SIMPLE | t3 | ref | rev_idn | rev_idn | 5 | my_database.t2.rev_idn | 1 | Using where | | 1 | SIMPLE | t4 | ref | idn | idn | 5 | my_database.t3.idn | 1 | Using where | | 1 | SIMPLE | t5 | ref | rev_idn | rev_idn | 5 | my_database.t4.rev_idn | 1 | Using where | | 1 | SIMPLE | t6 | ref | idn | idn | 5 | my_database.t5.idn | 1 | Using where | | 1 | SIMPLE | t7 | ref | rev_idn | rev_idn | 5 | my_database.t6.rev_idn | 1 | Using where | | 1 | SIMPLE | t8 | ref | idn | idn | 5 | my_database.t7.idn | 1 | Using where | | 1 | SIMPLE | t9 | ref | rev_idn | rev_idn | 5 | my_database.t8.rev_idn | 1 | Using where | | 1 | SIMPLE | t10 | ref | idn | idn | 5 | my_database.t9.idn | 1 | Using where | | 1 | SIMPLE | t11 | ref | rev_idn | rev_idn | 5 | my_database.t10.rev_idn | 1 | Using where | | 1 | SIMPLE | t12 | ref | idn | idn | 5 | my_database.t11.idn | 1 | Using where | | 1 | SIMPLE | t13 | ref | rev_idn | rev_idn | 5 | my_database.t12.rev_idn | 1 | Using where | | 1 | SIMPLE | t14 | ref | idn | idn | 5 | my_database.t13.idn | 1 | Using where | | 1 | SIMPLE | t15 | ref | rev_idn | rev_idn | 5 | my_database.t14.rev_idn | 1 | Using where | | 1 | SIMPLE | t16 | ref | idn | idn | 5 | my_database.t15.idn | 1 | Using where | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ ----------------------------- | Non-parallel | Parallel | |--------------|--------------| | 38.6 sec | 6.8 sec | ----------------------------- InnoDB (--innodb_buffer_pool_size=40G) t17 same as t1 with ENGINE=InnoDB (1 million records) same records as t1 t18 same as t17 with a key (idn) t19 same as t17 with a key (rev_idn) t20 same as t17 with a key (idn) t21 same as t17 with a key (rev_idn) t22 same as t17 with a key (idn) t23 same as t17 with a key (rev_idn) t24 same as t17 with a key (idn) t25 same as t17 with a key (rev_idn) t26 same as t17 with a key (idn) t27 same as t17 with a key (rev_idn) t28 same as t17 with a key (idn) t29 same as t17 with a key (rev_idn) t30 same as t17 with a key (idn) t31 same as t17 with a key (rev_idn) t32 same as t17 with a key (idn) 16 InnoDB Table Join Benchmark SELECT straight_join count(*) FROM t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32 WHERE t17.idn=t18.idn and t18.rev_idn=t19.rev_idn and t19.idn=t20.idn and t20.rev_idn=t21.rev_idn and t21.idn=t22.idn and t22.rev_idn=t23.rev_idn and t23.idn=t24.idn and t24.rev_idn=t25.rev_idn and t25.idn=t26.idn and t26.rev_idn=t27.rev_idn and t27.idn=t28.idn and t28.rev_idn=t29.rev_idn and t29.idn=t30.idn and t30.rev_idn=t31.rev_idn and t31.idn=t32.idn and t32.grp >= 0; Straight join is used to reduce query optimization time. Execution plan same as MyISAM ----------------------------- | Non-parallel | Parallel | |--------------|--------------| | 37.2 sec | 15.1 sec | ----------------------------- Tables used in the benchmarks are available as create table sql scripts. $ wget http://www.paralleluniverse-inc.com/16_table_1m_rec_t1_dump.sql t2: copy t1 (add key idn) t3: copy t1 (add key rev_idn) t4: copy t1 (add key idn) t5: copy t1 (add key rev_idn) t6: copy t1 (add key idn) t7: copy t1 (add key rev_idn) t8: copy t1 (add key idn) t9: copy t1 (add key rev_idn) t10: copy t1 (add key idn) t11: copy t1 (add key rev_idn) t12: copy t1 (add key idn) t13: copy t1 (add key rev_idn) t14: copy t1 (add key idn) t15: copy t1 (add key rev_idn) t16: copy t1 (add key idn) t17: copy t1 and alter storage engine t18: copy t1 and alter storage engine (add key idn) t19: copy t1 and alter storage engine (add key rev_idn) t20: copy t1 and alter storage engine (add key idn) t21: copy t1 and alter storage engine (add key rev_idn) t22: copy t1 and alter storage engine (add key idn) t23: copy t1 and alter storage engine (add key rev_idn) t24: copy t1 and alter storage engine (add key idn) t25: copy t1 and alter storage engine (add key rev_idn) t26: copy t1 and alter storage engine (add key idn) t27: copy t1 and alter storage engine (add key rev_idn) t28: copy t1 and alter storage engine (add key idn) t29: copy t1 and alter storage engine (add key rev_idn) t30: copy t1 and alter storage engine (add key idn) t31: copy t1 and alter storage engine (add key rev_idn) t32: copy t1 and alter storage engine (add key idn)