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 -- Mark Callaghan [email protected] _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
