commit c2b1020576b4a44484f54b77131c6a76009cb6bb Author: Vasile Paraschiv Date: Wed Apr 27 17:29:32 2016 -0700 cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DWITH_EMBEDDED_SERVER=OFF -DENABLED_LOCAL_INFILE=1 -DENABLE_DTRACE=0 -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/fb56 [openxs@fc23 fb56]$ cat /home/openxs/fb56.cnf [mysqld] rocksdb default-storage-engine=rocksdb skip-innodb default-tmp-storage-engine=MyISAM log-bin binlog-format=ROW mysql> explain select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category; +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ | 1 | SIMPLE | incident | index | PRIMARY,incident_category | incident_category | 123 | NULL | 8192 | Using index | | 1 | SIMPLE | task | eq_ref | PRIMARY | PRIMARY | 96 | test.incident.sys_id | 1 | Using index | +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ 2 rows in set (0.00 sec) mysql> explain select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category; +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ | 1 | SIMPLE | task | index | PRIMARY | PRIMARY | 96 | NULL | 8292 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | incident | eq_ref | PRIMARY,incident_category | PRIMARY | 96 | test.task.sys_id | 1 | NULL | +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql> show create table task\G *************************** 1. row *************************** Table: task Create Table: CREATE TABLE `task` ( `sys_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT '', `u_root_cause` char(32) COLLATE utf8_bin DEFAULT NULL, `u_business_impact_description` mediumtext COLLATE utf8_bin, `u_business_impact_category` mediumtext COLLATE utf8_bin, PRIMARY KEY (`sys_id`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec) mysql> show create table incident\G *************************** 1. row *************************** Table: incident Create Table: CREATE TABLE `incident` ( `sys_id` char(32) COLLATE utf8_bin NOT NULL DEFAULT '', `category` varchar(40) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`sys_id`), KEY `incident_category` (`category`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec) [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 60.447 seconds Minimum number of seconds to run all queries: 60.053 seconds Maximum number of seconds to run all queries: 60.740 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 39.267 seconds Minimum number of seconds to run all queries: 39.010 seconds Maximum number of seconds to run all queries: 39.581 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 24.262 seconds Minimum number of seconds to run all queries: 24.122 seconds Maximum number of seconds to run all queries: 24.341 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 24.782 seconds Minimum number of seconds to run all queries: 24.663 seconds Maximum number of seconds to run all queries: 24.960 seconds Number of clients running queries: 8 Average number of queries per client: 125 ----- [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 58.083 seconds Minimum number of seconds to run all queries: 57.750 seconds Maximum number of seconds to run all queries: 58.697 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 37.274 seconds Minimum number of seconds to run all queries: 37.068 seconds Maximum number of seconds to run all queries: 37.440 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 23.056 seconds Minimum number of seconds to run all queries: 22.948 seconds Maximum number of seconds to run all queries: 23.221 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 23.495 seconds Minimum number of seconds to run all queries: 23.349 seconds Maximum number of seconds to run all queries: 23.637 seconds Number of clients running queries: 8 Average number of queries per client: 125 ----- InnoDB --- ???? --- [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.794 seconds Minimum number of seconds to run all queries: 11.928 seconds Maximum number of seconds to run all queries: 14.742 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.142 seconds Minimum number of seconds to run all queries: 11.808 seconds Maximum number of seconds to run all queries: 12.680 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 13.019 seconds Minimum number of seconds to run all queries: 11.609 seconds Maximum number of seconds to run all queries: 14.233 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.791 seconds Minimum number of seconds to run all queries: 11.360 seconds Maximum number of seconds to run all queries: 13.279 seconds Number of clients running queries: 32 Average number of queries per client: 31 Other plan: [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 35.475 seconds Minimum number of seconds to run all queries: 34.463 seconds Maximum number of seconds to run all queries: 36.999 seconds Number of clients running queries: 4 Average number of queries per client: 250 ... [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 34.939 seconds Minimum number of seconds to run all queries: 33.971 seconds Maximum number of seconds to run all queries: 37.151 seconds Number of clients running queries: 16 Average number of queries per client: 62 2016-10-12T11:50:47.540101Z 0 [Note] InnoDB: PUNCH HOLE support available 2016-10-12T11:50:47.540151Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-10-12T11:50:47.540159Z 0 [Note] InnoDB: Uses event mutexes 2016-10-12T11:50:47.540166Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2016-10-12T11:50:47.540174Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-10-12T11:50:47.540182Z 0 [Note] InnoDB: Using Linux native AIO 2016-10-12T11:50:47.540492Z 0 [Note] InnoDB: Number of pools: 1 2016-10-12T11:50:47.540633Z 0 [Note] InnoDB: Not using CPU crc32 instructions ... 2016-10-12T12:13:43.715794Z 650 [Note] Aborted connection 650 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets) 2016-10-12T12:13:43.756518Z 651 [Note] Aborted connection 651 to db: 'test' user: 'root' host: 'localhost' (Got an error writing communication packets) Was like this on the same box with 5.6.9: [openxs@chief 5.6]$ bin/mysqlslap -uroot --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --iterations=10 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 18.372 seconds Minimum number of seconds to run all queries: 18.108 seconds Maximum number of seconds to run all queries: 18.847 seconds Number of clients running queries: 4 Average number of queries per client: 250 Platform: [openxs@fc23 5.7]$ pt-summary # Percona Toolkit System Summary Report ###################### Date | 2016-10-12 12:20:26 UTC (local TZ: EEST +0300) Hostname | fc23 Uptime | 5:54, 2 users, load average: 7.71, 10.38, 8.82 Platform | Linux Release | Fedora release 23 (Twenty Three) Kernel | 4.7.6-100.fc23.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.22 SELinux | Enforcing Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 1, cores = 4, virtual = 4, hyperthreading = no Speeds | 2x2003.000, 2x2499.000 Models | 4xIntel(R) Core(TM)2 Quad CPU Q8300 @ 2.50GHz Caches | 4x2048 KB # Memory ##################################################### Total | 7.8G Free | 1.6G Used | physical = 1.6G, swap allocated = 0.0, swap used = 0.0, virtual = 1.6G Shared | 186.8M Buffers | 4.5G Caches | 5.7G Dirty | 1624 kB UsedRSS | 2.9G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 ----- Recent: commit 2306b6e503a1e35b843e28ed00e2c867feb2bae7 Author: Santosh Praneeth Banda Date: Tue Oct 11 10:19:33 2016 -0700 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 61.552 seconds Minimum number of seconds to run all queries: 61.035 seconds Maximum number of seconds to run all queries: 62.869 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 34.910 seconds Minimum number of seconds to run all queries: 34.525 seconds Maximum number of seconds to run all queries: 35.618 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.791 seconds Minimum number of seconds to run all queries: 19.211 seconds Maximum number of seconds to run all queries: 20.163 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 18.805 seconds Minimum number of seconds to run all queries: 18.645 seconds Maximum number of seconds to run all queries: 19.084 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 18.218 seconds Minimum number of seconds to run all queries: 18.091 seconds Maximum number of seconds to run all queries: 18.343 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 17.832 seconds Minimum number of seconds to run all queries: 17.780 seconds Maximum number of seconds to run all queries: 17.883 seconds Number of clients running queries: 32 Average number of queries per client: 31 [openxs@fc23 fb56]$ bin/mysql -uroot test -e 'explain select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ | 1 | SIMPLE | incident | index | PRIMARY,incident_category | incident_category | 123 | NULL | 8192 | Using index | | 1 | SIMPLE | task | eq_ref | PRIMARY | PRIMARY | 96 | test.incident.sys_id | 1 | Using index | +----+-------------+----------+--------+---------------------------+-------------------+---------+----------------------+------+-------------+ Now different plan: [openxs@fc23 fb56]$ bin/mysql -uroot test -e 'explain select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ | 1 | SIMPLE | task | index | PRIMARY | PRIMARY | 96 | NULL | 8292 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | incident | eq_ref | PRIMARY,incident_category | PRIMARY | 96 | test.task.sys_id | 1 | NULL | +----+-------------+----------+--------+---------------------------+---------+---------+------------------+------+----------------------------------------------+ [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 58.319 seconds Minimum number of seconds to run all queries: 57.961 seconds Maximum number of seconds to run all queries: 58.542 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 32.219 seconds Minimum number of seconds to run all queries: 32.059 seconds Maximum number of seconds to run all queries: 32.395 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 18.684 seconds Minimum number of seconds to run all queries: 17.953 seconds Maximum number of seconds to run all queries: 19.222 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 17.335 seconds Minimum number of seconds to run all queries: 17.141 seconds Maximum number of seconds to run all queries: 17.501 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 16.650 seconds Minimum number of seconds to run all queries: 16.599 seconds Maximum number of seconds to run all queries: 16.707 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 16.630 seconds Minimum number of seconds to run all queries: 16.417 seconds Maximum number of seconds to run all queries: 16.704 seconds Number of clients running queries: 32 Average number of queries per client: 31 ----- perf 68079 MyRocks inner join: 5.04% my-oneconnectio libpthread-2.24.so [.] pthread_mutex_lock 3.96% my-oneconnectio libpthread-2.24.so [.] pthread_mutex_unlock 3.91% my-oneconnectio libc-2.24.so [.] __memcpy_ssse3 3.64% my-oneconnectio libc-2.24.so [.] __memcmp_sse4_1 3.45% my-oneconnectio mysqld [.] rocksdb::Version::Get 2.90% my-oneconnectio mysqld [.] rocksdb::LRUCacheShard::Lo okup 2.42% my-oneconnectio mysqld [.] myrocks::Rdb_pk_comparator ::Compare 2.33% my-oneconnectio mysqld [.] rocksdb::BlockIter::Binary Seek 2.29% my-oneconnectio mysqld [.] rocksdb::StatisticsImpl::r ecordTick 1.97% my-oneconnectio mysqld [.] my_strnxfrm_unicode 1.65% my-oneconnectio mysqld [.] rocksdb::InternalKeyCompar ator::Compare 1.46% my-oneconnectio mysqld [.] rocksdb::HistogramStat::Ad d 1.40% my-oneconnectio mysqld [.] rocksdb::BlockIter::Seek 68079 MyRocks straight join: 7.42% my-oneconnectio libc-2.24.so [.] __memcpy_ssse3 6.41% my-oneconnectio libc-2.24.so [.] __memcmp_sse4_1 4.50% my-oneconnectio mysqld [.] rocksdb::BlockIter::Seek 2.78% my-oneconnectio mysqld [.] rocksdb::StatisticsImpl::r ecordTick 2.77% my-oneconnectio mysqld [.] rocksdb::BlockIter::Binary Seek 2.62% my-oneconnectio mysqld [.] rocksdb::InternalKeyCompar ator::Compare 2.56% my-oneconnectio mysqld [.] myrocks::Rdb_pk_comparator ::Compare 2.19% my-oneconnectio mysqld [.] rocksdb::BlockBasedTable:: Get 2.11% my-oneconnectio mysqld [.] rocksdb::DBIter::Next 1.81% my-oneconnectio mysqld [.] rocksdb::Version::Get 1.63% my-oneconnectio mysqld [.] join_read_key 1.63% my-oneconnectio mysqld [.] evaluate_join_record 1.63% my-oneconnectio mysqld [.] myrocks::Rdb_key_def::unpa ck_record 1.49% my-oneconnectio mysqld [.] myrocks::ha_rocksdb::index_read_map_impl === Raw testing results as of 2017-01-30 === [openxs@fc23 mysql-5.6]$ git log -1 commit 6b4ba98698182868800f31e0d303f1e16026efdf Author: Alex Yang Date: Sat Jan 28 13:51:47 2017 -0800 [openxs@fc23 mysql-server]$ git log -1 commit 23032807537d8dd8ee4ec1c4d40f0633cd4e12f9 Author: Balasubramanian Kandasamy Date: Mon Nov 28 16:48:20 2016 +0530 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 68.740 seconds Minimum number of seconds to run all queries: 67.913 seconds Maximum number of seconds to run all queries: 70.768 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 37.944 seconds Minimum number of seconds to run all queries: 37.433 seconds Maximum number of seconds to run all queries: 38.855 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 20.658 seconds Minimum number of seconds to run all queries: 20.062 seconds Maximum number of seconds to run all queries: 21.961 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.922 seconds Minimum number of seconds to run all queries: 19.691 seconds Maximum number of seconds to run all queries: 20.508 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=10 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.716 seconds Minimum number of seconds to run all queries: 19.554 seconds Maximum number of seconds to run all queries: 20.229 seconds Number of clients running queries: 10 Average number of queries per client: 100 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.559 seconds Minimum number of seconds to run all queries: 19.382 seconds Maximum number of seconds to run all queries: 20.031 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.637 seconds Minimum number of seconds to run all queries: 19.524 seconds Maximum number of seconds to run all queries: 19.885 seconds Number of clients running queries: 32 Average number of queries per client: 31 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=64 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 19.092 seconds Minimum number of seconds to run all queries: 19.074 seconds Maximum number of seconds to run all queries: 19.131 seconds Number of clients running queries: 64 Average number of queries per client: 15 --- [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=64 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.242 seconds Minimum number of seconds to run all queries: 15.201 seconds Maximum number of seconds to run all queries: 15.272 seconds Number of clients running queries: 64 Average number of queries per client: 15 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.696 seconds Minimum number of seconds to run all queries: 15.625 seconds Maximum number of seconds to run all queries: 15.840 seconds Number of clients running queries: 32 Average number of queries per client: 31 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.598 seconds Minimum number of seconds to run all queries: 15.562 seconds Maximum number of seconds to run all queries: 15.647 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.698 seconds Minimum number of seconds to run all queries: 15.536 seconds Maximum number of seconds to run all queries: 16.111 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.812 seconds Minimum number of seconds to run all queries: 15.542 seconds Maximum number of seconds to run all queries: 16.175 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=10 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.726 seconds Minimum number of seconds to run all queries: 15.663 seconds Maximum number of seconds to run all queries: 15.828 seconds Number of clients running queries: 10 Average number of queries per client: 100 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=10 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.744 seconds Minimum number of seconds to run all queries: 15.640 seconds Maximum number of seconds to run all queries: 15.896 seconds Number of clients running queries: 10 Average number of queries per client: 100 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.751 seconds Minimum number of seconds to run all queries: 15.671 seconds Maximum number of seconds to run all queries: 15.852 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.772 seconds Minimum number of seconds to run all queries: 15.675 seconds Maximum number of seconds to run all queries: 15.887 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.731 seconds Minimum number of seconds to run all queries: 15.674 seconds Maximum number of seconds to run all queries: 15.847 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.767 seconds Minimum number of seconds to run all queries: 15.682 seconds Maximum number of seconds to run all queries: 15.941 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 31.148 seconds Minimum number of seconds to run all queries: 31.003 seconds Maximum number of seconds to run all queries: 31.356 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 62.033 seconds Minimum number of seconds to run all queries: 61.387 seconds Maximum number of seconds to run all queries: 62.883 seconds Number of clients running queries: 1 Average number of queries per client: 1000 ---------------------------- perf record -a at concurrency 8: straight_join: [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 16.006 seconds Minimum number of seconds to run all queries: 15.935 seconds Maximum number of seconds to run all queries: 16.098 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 ~]$ perf --version perf version 4.9.6.200.fc25.x86_64.g51a0 [openxs@fc23 ~]$ sudo perf record -a [sudo] password for openxs: ^C[ perf record: Woken up 335 times to write data ] [ perf record: Captured and wrote 84.913 MB perf.data (1825548 samples) ] [openxs@fc23 ~]$ sudo perf report --stdio | more Failed to open /tmp/perf-5089.map, continuing without symbols # To display the perf.data header info, please use --header/--header-only option s. # # # Total Lost Samples: 0 # # Samples: 1M of event 'cycles:p' # Event count (approx.): 62748127726390 # # Overhead Command Shared Object Symbol # ........ ............... ................................ ................. ................................................................................ 5.32% my-oneconnectio libc-2.24.so [.] __memcmp_sse4 _1 4.68% my-oneconnectio libc-2.24.so [.] __memcpy_ssse 3 4.04% my-oneconnectio mysqld [.] rocksdb::Bloc kIter::Seek 3.33% my-oneconnectio mysqld [.] my_strnxfrm_u nicode 3.11% my-oneconnectio mysqld [.] rocksdb::Bloc kBasedTable::Get 2.96% my-oneconnectio mysqld [.] myrocks::Rdb_ pk_comparator::Compare 2.54% my-oneconnectio mysqld [.] rocksdb::Bloc kIter::BinarySeek 2.14% my-oneconnectio mysqld [.] rocksdb::Inte rnalKeyComparator::Compare 2.00% my-oneconnectio mysqld [.] rocksdb::Stat isticsImpl::recordTick 1.99% my-oneconnectio mysqld [.] rocksdb::Merg ingIterator::Next 1.95% my-oneconnectio mysqld [.] rocksdb::Hist ogramStat::Add 1.51% my-oneconnectio mysqld [.] join_read_key 1.51% my-oneconnectio mysqld [.] myrocks::rdb_ unpack_utf8_str 1.41% my-oneconnectio mysqld [.] myrocks::Rdb_ key_def::unpack_record 1.36% my-oneconnectio mysqld [.] sub_select 1.32% my-oneconnectio mysqld [.] my_uni_utf8 1.29% my-oneconnectio mysqld [.] rocksdb::Vers ion::Get 1.22% my-oneconnectio libc-2.24.so [.] _int_malloc 1.22% my-oneconnectio mysqld [.] rocksdb::Tabl eCache::Get 1.22% my-oneconnectio mysqld [.] rocksdb::Bloc kIter::Next 1.21% my-oneconnectio mysqld [.] rocksdb::Thre adLocalPtr::Get 1.18% my-oneconnectio mysqld [.] rocksdb::DBIt er::FindNextUserEntryInternal 1.18% my-oneconnectio mysqld [.] rocksdb::(ano nymous namespace)::FilePicker::GetNextFile 1.12% my-oneconnectio libc-2.24.so [.] malloc 1.07% my-oneconnectio mysqld [.] evaluate_join _record 1.07% my-oneconnectio mysqld [.] myrocks::Rdb_ key_def::pack_index_tuple 1.01% my-oneconnectio mysqld [.] key_restore inner join: [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 20.059 seconds Minimum number of seconds to run all queries: 20.038 seconds Maximum number of seconds to run all queries: 20.104 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 ~]$ sudo perf record -a ^C[ perf record: Woken up 296 times to write data ] [ perf record: Captured and wrote 75.245 MB perf.data (1612597 samples) ] [openxs@fc23 ~]$ sudo perf report --stdio | more /usr/lib64/dri/i965_dri.so was updated (is prelink enabled?). Restart the long running apps that use it! no symbols found in /usr/lib64/sa/sadc, maybe install a debug package? no symbols found in /usr/bin/cat, maybe install a debug package? Failed to open /tmp/perf-5089.map, continuing without symbols # To display the perf.data header info, please use --header/--header-only option s. # # # Total Lost Samples: 0 # # Samples: 1M of event 'cycles:p' # Event count (approx.): 162704428520300 # # Overhead Command Shared Object Symbol # ........ ............... ............................. .................... ................................................................................ ................................................................................ ................................................................................ .............................. # 5.90% my-oneconnectio libc-2.24.so [.] __memcpy_ssse3 4.38% my-oneconnectio libpthread-2.24.so [.] pthread_mutex_lo ck 3.69% my-oneconnectio libc-2.24.so [.] __memcmp_sse4_1 3.58% my-oneconnectio mysqld [.] rocksdb::BlockIt er::Seek 2.53% my-oneconnectio libpthread-2.24.so [.] pthread_mutex_un lock 2.47% my-oneconnectio mysqld [.] rocksdb::Statist icsImpl::recordTick 2.35% my-oneconnectio mysqld [.] rocksdb::BlockBa sedTable::Get 2.28% my-oneconnectio mysqld [.] my_strnxfrm_unic ode 2.08% my-oneconnectio mysqld [.] rocksdb::BlockIt er::BinarySeek 1.84% my-oneconnectio mysqld [.] rocksdb::Histogr amStat::Add 1.84% my-oneconnectio mysqld [.] rocksdb::Version ::Get 1.71% my-oneconnectio mysqld [.] myrocks::rdb_unp ack_binary_or_utf8_varchar_space_pad 1.69% my-oneconnectio mysqld [.] rocksdb::LRUCach eShard::Lookup 1.67% my-oneconnectio mysqld [.] my_uni_utf8 1.61% my-oneconnectio mysqld [.] rocksdb::Interna lKeyComparator::Compare 1.52% my-oneconnectio mysqld [.] myrocks::Rdb_pk_ comparator::Compare 1.47% my-oneconnectio mysqld [.] rocksdb::(anonym ous namespace)::FilePicker::GetNextFile 1.23% my-oneconnectio mysqld [.] rocksdb::BlockIt er::Next 1.15% my-oneconnectio mysqld [.] rocksdb::Merging Iterator::Next 1.10% my-oneconnectio mysqld [.] join_read_key 1.09% my-oneconnectio mysqld [.] rocksdb::Block:: NewIterator 1.04% my-oneconnectio mysqld [.] rocksdb::TableCa che::Get ... Now, hierarchical profiles: [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 15.897 seconds Minimum number of seconds to run all queries: 15.789 seconds Maximum number of seconds to run all queries: 16.009 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 ~]$ sudo perf record -ag ^C[ perf record: Woken up 565 times to write data ] [ perf record: Captured and wrote 142.410 MB perf.data (644176 samples) ] -g, --call-graph= Display call chains using type, min percent threshold, print limit, call order, sort key, optional branch and value. Note that ordering of parameters is not fixed so any parement can be given in an arbitraty order. One exception is the print_limit which should be preceded by threshold. print_type can be either: - flat: single column, linear exposure of call chains. - graph: use a graph tree, displaying absolute overhead rates. (default) - fractal: like graph, but displays relative rates. Each branch of the tree is considered as a new profiled object. - folded: call chains are displayed in a line, separated by semicolons - none: disable call chain display. [openxs@fc23 ~]$ sudo perf report -g flat --stdio | more # To display the perf.data header info, please use --header/--header-only options. # # # Total Lost Samples: 0 # # Samples: 644K of event 'cycles:p' # Event count (approx.): 192722642138071 # # Children Self Command Shared Object Symbol # ........ ........ ............... ................................ ..................................................................................... .............................................................................................................................................................. ............................................... # 95.87% 0.00% my-oneconnectio mysqld [.] handle_one_connection 13.27% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get rocksdb::TableCache::Get rocksdb::BlockBasedTable::Get rocksdb::BlockIter::Seek 6.66% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get rocksdb::TableCache::Get rocksdb::BlockBasedTable::Get rocksdb::BlockIter::Seek rocksdb::BlockIter::BinarySeek 31.54% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get rocksdb::TableCache::Get rocksdb::BlockBasedTable::Get 40.20% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get ... [openxs@fc23 fb56]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 20.601 seconds Minimum number of seconds to run all queries: 20.552 seconds Maximum number of seconds to run all queries: 20.653 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 ~]$ sudo perf record -ag ^C[ perf record: Woken up 722 times to write data ] [ perf record: Captured and wrote 181.521 MB perf.data (815206 samples) ] [openxs@fc23 ~]$ sudo perf report --stdio > rocksdb_68079_hier_inner.txt [openxs@fc23 ~]$ sudo perf report -g flat --stdio > rocksdb_68079_flat_inner.txt 36.93% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get rocksdb::TableCache::Get rocksdb::BlockBasedTable::Get 45.87% handle_one_connection do_handle_one_connection dispatch_command mysql_parse mysql_execute_command execute_sqlcom_select handle_select mysql_select JOIN::exec sub_select evaluate_join_record sub_select join_read_key myrocks::ha_rocksdb::index_read_map_impl myrocks::ha_rocksdb::get_row_by_rowid myrocks::Rdb_transaction_impl::get rocksdb::TransactionBaseImpl::Get rocksdb::WriteBatchWithIndex::GetFromBatchAndDB rocksdb::DBImpl::Get rocksdb::DBImpl::GetImpl rocksdb::Version::Get -G flat attempt: # (Cannot load tips.txt file, please install perf!) [openxs@fc23 ~]$ tar -tzvf rocksdb_profiling.tgz -rw-rw-r-- openxs/openxs 1367769 2017-01-31 11:45 rocksdb_68079_flat_inner.txt -rw-rw-r-- openxs/openxs 1007164 2017-01-31 11:38 rocksdb_68079_flat_straight.txt -rw-rw-r-- openxs/openxs 1364563 2017-01-31 11:44 rocksdb_68079_hier_inner.txt -rw-rw-r-- openxs/openxs 1062789 2017-01-31 11:31 rocksdb_68079_hier_straight.txt --- Now, InnoDB from MySQL 5.7.17 on the same hardware: 2017-01-31T09:55:07.527057Z mysqld_safe Logging to './data/fc23.err'. 2017-01-31T09:55:07.572733Z mysqld_safe Starting mysqld daemon with databases from ./data 2017-01-31T09:55:08.331095Z mysqld_safe mysqld from pid file ./data/fc23.pid ended [openxs@fc23 5.7]$ bin/mysqld_safe --no-defaults --basedir=/home/openxs/dbs/5.7 --datadir=/home/openxs/dbs/5.7/data & [2]+ Done wget -c https://bugs.mysql.com/file.php?id=19619 [openxs@fc23 5.7]$ mv file.php\?id\=19619 test.sql.gz [openxs@fc23 5.7]$ gzip -d test.sql.gz [openxs@fc23 5.7]$ ls -l test.sql -rw-rw-r--. 1 openxs openxs 657443 Jan 31 11:59 test.sql [openxs@fc23 5.7]$ bin/mysql -uroot test < test.sql For reference, 5.6.9-rc before the fix (from https://bugs.mysql.com/bug.php?id=68079): [openxs@chief 5.6]$ bin/mysqlslap -uroot --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --iterations=10 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 18.372 seconds Minimum number of seconds to run all queries: 18.108 seconds Maximum number of seconds to run all queries: 18.847 seconds Number of clients running queries: 4 Average number of queries per client: 250 Now, testing: [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 33.520 seconds Minimum number of seconds to run all queries: 33.265 seconds Maximum number of seconds to run all queries: 34.136 seconds Number of clients running queries: 1 Average number of queries per client: 1000 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 23.398 seconds Minimum number of seconds to run all queries: 23.257 seconds Maximum number of seconds to run all queries: 23.572 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.337 seconds Minimum number of seconds to run all queries: 12.254 seconds Maximum number of seconds to run all queries: 12.452 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.300 seconds Minimum number of seconds to run all queries: 12.246 seconds Maximum number of seconds to run all queries: 12.345 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=10 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.340 seconds Minimum number of seconds to run all queries: 12.264 seconds Maximum number of seconds to run all queries: 12.545 seconds Number of clients running queries: 10 Average number of queries per client: 100 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.218 seconds Minimum number of seconds to run all queries: 12.167 seconds Maximum number of seconds to run all queries: 12.252 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 12.240 seconds Minimum number of seconds to run all queries: 12.194 seconds Maximum number of seconds to run all queries: 12.279 seconds Number of clients running queries: 32 Average number of queries per client: 31 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=64 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task inner join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.879 seconds Minimum number of seconds to run all queries: 11.849 seconds Maximum number of seconds to run all queries: 11.912 seconds Number of clients running queries: 64 Average number of queries per client: 15 Now, straight_join case: [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=64 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.223 seconds Minimum number of seconds to run all queries: 11.162 seconds Maximum number of seconds to run all queries: 11.274 seconds Number of clients running queries: 64 Average number of queries per client: 15 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=32 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.855 seconds Minimum number of seconds to run all queries: 11.543 seconds Maximum number of seconds to run all queries: 13.731 seconds Number of clients running queries: 32 Average number of queries per client: 31 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=16 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.593 seconds Minimum number of seconds to run all queries: 11.555 seconds Maximum number of seconds to run all queries: 11.621 seconds Number of clients running queries: 16 Average number of queries per client: 62 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=10 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.761 seconds Minimum number of seconds to run all queries: 11.692 seconds Maximum number of seconds to run all queries: 11.823 seconds Number of clients running queries: 10 Average number of queries per client: 100 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=8 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.711 seconds Minimum number of seconds to run all queries: 11.642 seconds Maximum number of seconds to run all queries: 11.813 seconds Number of clients running queries: 8 Average number of queries per client: 125 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=4 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 11.740 seconds Minimum number of seconds to run all queries: 11.597 seconds Maximum number of seconds to run all queries: 11.976 seconds Number of clients running queries: 4 Average number of queries per client: 250 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=2 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 22.521 seconds Minimum number of seconds to run all queries: 22.324 seconds Maximum number of seconds to run all queries: 23.097 seconds Number of clients running queries: 2 Average number of queries per client: 500 [openxs@fc23 5.7]$ bin/mysqlslap -uroot --iterations=10 --concurrency=1 --create-schema=test --no-drop --number-of-queries=1000 --query='select count(*), category from task straight_join incident on task.sys_id=incident.sys_id group by incident.category' Benchmark Average number of seconds to run all queries: 34.411 seconds Minimum number of seconds to run all queries: 33.912 seconds Maximum number of seconds to run all queries: 36.466 seconds Number of clients running queries: 1 Average number of queries per client: 1000