Hi Mark!
Is there a reason you are using the pool_of_threads scheduler in
Drizzle? Is this a requirement on your end? We've found that the
pool_of_threads scheduler has serious performance problems and we
recommend using the default multi_thread scheduler. In addition, to get
an apples-to-apples comparison, I would advise using multi_thread, as
MySQL < 6.0 does not have a pool of threads scheduler anyway.
In addition, you will see serious performance bottlenecks if TCMalloc is
installed on your benchmark server. MySQL's memory allocation
procedures simply do not like TCMalloc.
Here is how we run our benchmarks internally:
innodb_buffer_pool_size= 128M
innodb_log_file_size= 64M
innodb_log_buffer_size= 8M
innodb_thread_concurrency= 0
innodb_additional_mem_pool_size= 16M
table_open_cache= 4096
table_definition_cache= 4096
When running against MySQL, we also add this:
character_set_server= utf8
I see from the results below that Drizzle, with the pool_of_threads, is
doing ~98K qps (16,000,000 / 162). I would expect us to exceed 100 qps
with the regular multi_thread scheduler in Drizzle, but I'll leave it to
you to do the benchmarks :)
Thanks!
Jay
MARK CALLAGHAN wrote:
I am moving a discussion from the facebook page to here as it is
drizzle specific.
I ran a load test, briefly described at
http://www.facebook.com/MySQLatFacebook#/note.php?note_id=142812280932,
to measure the overheads in MySQL by area (parser, table locking,
authorization, execution). Eventually, I tried the test on Drizzle.
For the non-Drizzle tests, about 66% of the overhead in mysqld is from
executing the query, about 10% from parsing and about 12% from
pre-execution (between parse and execution -- table locking, access
control).
Notes:
* the non-drizzle tests used mysqlslap and the drizzle test used drizzleslap
* the test input is a file with 1M identical select statements --
select pk from foo where pk = 1
* 5138-innodb-plugin uses the 1.0.4 innodb plugin which is faster than
the 1.0.3 innodb plugin used by drizzle
* 5138-innodb-builtin uses the non-plugin innodb which is probably not
faster than the 1.0.3 innodb plugin in drizzle
* I reran the tests to use utf8 (5138-innodb-plugin-utf8) to confirm
that the only cause for the performance difference is not utf8
* tests are run on an 8-core server with clients and server run on the same host
* tests for drizzle used the default value -- pool_of_threads_size=8
(pool8 below), but changing it to 16 (pool16 below) did not improve
performance much
seconds to complete test
by concurrency
1 2 4 8 16
60 66 64 68 139 5138-innodb-plugin-utf8
55 72 79 81 164 drizzle-innodb-utf8-pool8
62 65 73 80 162 drizzle-innodb-utf8-pool16
55 63 55 62 129 5138-innodb-plugin-latin1
49 61 57 64 130 5138-innodb-builtin-latin1
Top-N entries from oprofile for Drizzle:
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a
unit mask of 0x00 (Unhalted core cycles) count 50000
samples % image name app name symbol name
29261 7.9739 drizzled drizzled
DRIZZLEparse(void*)
10959 2.9864 drizzled drizzled
JOIN::optimize()
10508 2.8635 drizzled drizzled
lex_one_token(void*, void*)
9207 2.5090 drizzled drizzled
mysql_select(Session*, Item***, TableList*, unsigned int, List<Item>&,
Item*, unsigned int, order_st*, order_st*, Item*, unsigned long,
select_result*, Select_Lex_Unit*, Select_Lex*)
6466 1.7620 drizzled drizzled
row_search_for_mysql
4968 1.3538 drizzled drizzled alloc_root
4583 1.2489 drizzled drizzled
btr_search_guess_on_hash
4552 1.2405 drizzled drizzled
my_strnncoll_binary
4467 1.2173 libc-2.5.so drizzleslap memset
4387 1.1955 libc-2.5.so drizzled memcpy
4181 1.1394 libc-2.5.so drizzled malloc
3761 1.0249 libc-2.5.so drizzled _int_malloc
3521 0.9595 libc-2.5.so drizzled free
3426 0.9336 ld-2.5.so drizzle do_lookup_x
3324 0.9058 drizzled drizzled Item::Item()
3146 0.8573 libpthread-2.5.so drizzled
pthread_mutex_lock
And similar data for 5.1.38, innodb plugin and latin1
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a
unit mask of 0x00 (Unhalted core cycles) count 50000
samples % image name app name symbol name
32255 7.2674 mysqld mysqld
MYSQLparse(void*)
9406 2.1193 libc-2.5.so mysql strncmp
7827 1.7635 mysqld mysqld
JOIN::optimize()
7662 1.7263 mysqld mysqld
MYSQLlex(void*, void*)
6722 1.5145 libc-2.5.so mysqld memcpy
6443 1.4517 mysqld mysqld
make_join_statistics(JOIN*, TABLE_LIST*, Item*, st_dynamic_array*)
6270 1.4127 libc-2.5.so mysqlslap malloc
5906 1.3307 libpthread-2.5.so mysqld
pthread_mutex_lock
5618 1.2658 mysqld mysqld
row_search_for_mysql
5256 1.1842 libc-2.5.so mysqlslap _int_malloc
4694 1.0576 mysqld mysqld
get_hash_symbol(char const*, unsigned int, bool)
4452 1.0031 libc-2.5.so mysqld _int_malloc
4188 0.9436 libc-2.5.so mysqlslap memcpy
3814 0.8593 mysqld mysqld alloc_root
3749 0.8447 no-vmlinux no-vmlinux (no symbols)
3733 0.8411 ld-2.5.so mysql do_lookup_x
my.cnf variables:
innodb_buffer_pool_size=2000M
innodb_log_file_size=100M
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
max_connections=500
innodb_max_dirty_pages_pct=80
innodb_flush_log_at_trx_commit=2
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
