P A R A L L E L U N I V E R S E Server 5.5-2 Benchmarks (10,000 record tables) Server #1: AMD Phenom processor (4 cores) with 4GB of memory on Centos 6.2-64bit OS. (Using warm cache: 2nd and subsequent runs where tables have already been loaded into the memory.) Innodb (Server #1: --innodb_buffer_pool_size=2500M) t1 ( `region` char(1) DEFAULT NULL, (A thru F and repeats) `idn` int(6) DEFAULT NULL, (0 thru 9,999) `rev_idn` int(6) DEFAULT NULL, (9,999 thru 0) `grp` int(6) DEFAULT NULL (0 thru 99 in steps of 3, modulo 100) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 (10 thousand records) +--------+-----+---------+-----+ | region | idn | rev_idn | grp | +--------+-----+---------+-----+ | A | 0 | 9999 | 0 | | B | 1 | 9998 | 3 | | C | 2 | 9997 | 6 | | D | 3 | 9996 | 9 | | E | 4 | 9995 | 12 | | F | 5 | 9994 | 15 | | A | 6 | 9993 | 18 | | B | 7 | 9992 | 21 | | C | 8 | 9991 | 24 | . . . | C | 32 | 9967 | 96 | | D | 33 | 9966 | 99 | | E | 34 | 9965 | 2 | | F | 35 | 9964 | 5 | | A | 36 | 9963 | 8 | | B | 37 | 9962 | 11 | . . . | B | 9997 | 2 | 91 | | C | 9998 | 1 | 94 | | D | 9999 | 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) #1) 4 Innodb table benchmark SELECT count(*) FROM t1,t2,t3,t4 WHERE t1.idn=t2.idn and t2.rev_idn=t3.rev_idn and t3.idn=t4.idn and t4.grp >= 0; Execution plan +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9897 | | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.idn | 1 | | | 1 | SIMPLE | t3 | ref | rev_idn | rev_idn | 4 | my_database.t2.rev_idn | 1 | | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.idn | 1 | Using where | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ Server #1 ----------------------------- | Non-parallel | Parallel | |--------------|--------------| | 0.073 sec | 0.046 sec | ----------------------------- #2) 8 Innodb table benchmark SELECT count(*) FROM t1,t2,t3,t4,t5,t6,t7,t8 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.grp >= 0; Execution plan +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 9897 | | | 1 | SIMPLE | t2 | ref | idn | idn | 4 | my_database.t1.idn | 1 | | | 1 | SIMPLE | t3 | ref | rev_idn | rev_idn | 4 | my_database.t2.rev_idn | 1 | | | 1 | SIMPLE | t4 | ref | idn | idn | 4 | my_database.t3.idn | 1 | | | 1 | SIMPLE | t5 | ref | rev_idn | rev_idn | 4 | my_database.t4.rev_idn | 1 | | | 1 | SIMPLE | t6 | ref | idn | idn | 4 | my_database.t5.idn | 1 | | | 1 | SIMPLE | t7 | ref | rev_idn | rev_idn | 4 | my_database.t6.rev_idn | 1 | | | 1 | SIMPLE | t8 | ref | idn | idn | 4 | my_database.t7.idn | 1 | Using where | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ Server #1 ----------------------------- | Non-parallel | Parallel* | |--------------|--------------| | 0.171 sec | 0.099 sec | ----------------------------- *Only last 6 tables are parallel processed MyISAM t9 same as t1 with ENGINE=MyISAM (10 thousand records) same records as t1 t10 same as t9 with a key (idn) t11 same as t9 with a key (rev_idn) t12 same as t9 with a key (idn) t13 same as t9 with a key (rev_idn) t14 same as t9 with a key (idn) t15 same as t9 with a key (rev_idn) t16 same as t9 with a key (idn) #3) 4 MyISAM table benchmark SELECT count(*) FROM t9,t10,t11,t12 WHERE t9.idn=t10.idn and t10.rev_idn=t11.rev_idn and t11.idn=t12.idn and t12.grp >= 0; Execution plan same as #1) Server #1 ----------------------------- | Non-parallel | Parallel | |--------------|--------------| | 0.070 sec | 0.030 sec | ----------------------------- #4) 8 MyISAM table benchmark SELECT count(*) FROM t9,t10,t11,t12,t13,t14,t15,t16 WHERE 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; Execution plan same as #2) Server #1 ----------------------------- | Non-parallel | Parallel** | |--------------|--------------| | 0.177 sec | 0.063 sec | ----------------------------- **Only last 6 tables are parallel processed Tables used in the benchmarks are available as create table sql scripts. $ wget http://www.paralleluniverse-inc.com/bench10k.sql t1: copy bench10k 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 and alter storage engine t10: copy t9 (add key idn) t11: copy t9 (add key rev_idn) t12: copy t9 (add key idn) t13: copy t9 (add key rev_idn) t14: copy t9 (add key idn) t15: copy t9 (add key rev_idn) t16: copy t9 (add key idn)