P A R A L L E L U N I V E R S E Server 5.5-2 Benchmarks Server #1: AMD Phenom processor (4 cores) with 4GB of memory on Centos 6.2-64bit OS. Server #2: Intel Dual Xeon processors (2x6 cores) with 24GB 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) (Server #2: --innodb_buffer_pool_size=16G) 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=InnoDB 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) #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 | 1000544 | | | 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 | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ Server #1 Server #2 ----------------------------- ----------------------------- | Non-parallel | Parallel | | Non-parallel | Parallel | |--------------|--------------| |--------------|--------------| | 7.5 sec | 4.4 sec | | 7.3 sec | 3.8 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 | 1000544 | | | 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 | +----+-------------+-------+------+---------------+---------+---------+--------------------------+---------+-------------+ Server #1 Server #2 ----------------------------- ----------------------------- | Non-parallel | Parallel* | | Non-parallel | Parallel | |--------------|--------------| |--------------|--------------| | 19.2 sec | 10.7 sec | | 18.0 sec | 6.9 sec | ----------------------------- ----------------------------- *Only last 6 tables are parallel processed MyISAM t9 same as t1 with ENGINE=MyISAM (1 million 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 Server #2 ----------------------------- ----------------------------- | Non-parallel | Parallel | | Non-parallel | Parallel | |--------------|--------------| |--------------|--------------| | 9.0 sec | 3.3 sec | | 12.3 sec | 4.5 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 Server #2 ----------------------------- ----------------------------- | Non-parallel | Parallel** | | Non-parallel | Parallel | |--------------|--------------| |--------------|--------------| | 23.3 sec | 7.7 sec | | 31.0 sec | 6.2 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/16_table_1m_rec_t1_dump.sql t1: alter storage engine 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) ================================================================== Additional Info ================================================================== Parallel Universe> show table status; +-------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | t1 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 0 | 11534336 | NULL | 2012-04-28 09:59:38 | NULL | NULL | latin1_swedish_ci | NULL | | | | t2 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-07-14 14:22:05 | NULL | NULL | latin1_swedish_ci | NULL | | | | t3 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-07-14 14:22:57 | NULL | NULL | latin1_swedish_ci | NULL | | | | t4 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-07-14 14:22:35 | NULL | NULL | latin1_swedish_ci | NULL | | | | t5 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-07-14 14:23:24 | NULL | NULL | latin1_swedish_ci | NULL | | | | t6 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-03-28 11:35:24 | NULL | NULL | latin1_swedish_ci | NULL | | | | t7 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-03-28 11:36:19 | NULL | NULL | latin1_swedish_ci | NULL | | | | t8 | InnoDB | 10 | Compact | 1000393 | 40 | 40435712 | 0 | 16302080 | 11534336 | NULL | 2012-03-28 11:35:34 | NULL | NULL | latin1_swedish_ci | NULL | | | | t9 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 1024 | 0 | NULL | 2012-08-27 12:27:03 | 2012-08-27 12:27:04 | NULL | latin1_swedish_ci | NULL | | | | t10 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-26 13:34:02 | 2012-03-26 13:34:03 | 2012-03-26 13:34:03 | latin1_swedish_ci | NULL | | | | t11 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-26 13:34:14 | 2012-03-26 13:34:15 | 2012-03-26 13:34:15 | latin1_swedish_ci | NULL | | | | t12 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-26 13:34:06 | 2012-03-26 13:34:06 | 2012-03-26 13:34:07 | latin1_swedish_ci | NULL | | | | t13 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-28 11:23:37 | 2012-03-28 11:23:37 | 2012-03-28 11:23:38 | latin1_swedish_ci | NULL | | | | t14 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-28 11:23:55 | 2012-03-28 11:23:55 | 2012-03-28 11:23:56 | latin1_swedish_ci | NULL | | | | t15 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-28 11:23:42 | 2012-03-28 11:23:42 | 2012-03-28 11:23:42 | latin1_swedish_ci | NULL | | | | t16 | MyISAM | 10 | Fixed | 1000000 | 14 | 14000000 | 3940649673949183 | 10194944 | 0 | NULL | 2012-03-28 11:24:01 | 2012-03-28 11:24:01 | 2012-03-28 11:24:02 | latin1_swedish_ci | NULL | | | +-------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 16 rows in set (1.07 sec) Parallel Universe> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+ 1 row in set (0.00 sec) Parallel Universe>