API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 6th, 2017
500
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.95 KB | None | 0 0
raw download clone embed print report
  1. TESTED ON PERRO UNDER STEPHANE USER :
  2.  
  3. processor : 3
  4. model name : Genuine Intel(R) CPU 3.20GHz
  5.  
  6. CREATE TABLE `login` (
  7. `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  8. `user_name` varchar(50) DEFAULT NULL,
  9. `user_email` varchar(255) DEFAULT NULL,
  10. `created` datetime DEFAULT NULL,
  11. PRIMARY KEY (`user_id`)
  12. ) ENGINE=MyISAM AUTO_INCREMENT=1000000 DEFAULT CHARSET=latin1
  13.  
  14.  
  15. ./my sqlslap -ustephane test -p --create-schema=test --query="HANDLER login READ \`PRIMARY\`=(10000)" --concurrency=4 --number-of-queries=10000000
  16.  
  17. init_connect='use test;HANDLER login OPEN;'
  18.  
  19.  
  20. REFERENCE ON MYSQL 5.5 SELECT * from login where user_id=1000
  21.  
  22.  
  23. mysql [localhost] {msandbox} (performance_schema) > SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
  24. +--------------------------------------------+------------+
  25. | EVENT_NAME | COUNT_STAR |
  26. +--------------------------------------------+------------+
  27. | wait/synch/mutex/sql/THD::LOCK_thd_data | 4951736 |
  28. | wait/synch/mutex/innodb/kernel_mutex | 3008929 |
  29. | wait/synch/rwlock/sql/MDL_lock::rwlock | 3008373 |
  30. | wait/synch/mutex/sql/LOCK_open | 2006001 |
  31. | wait/synch/mutex/mysys/THR_LOCK::mutex | 2005879 |
  32. | wait/synch/mutex/sql/MDL_map::mutex | 1003036 |
  33. | wait/synch/rwlock/sql/LOCK_grant | 1002829 |
  34. | wait/synch/rwlock/innodb/btr_search_latch | 1002769 |
  35. | wait/synch/mutex/innodb/mutex_list_mutex | 16569 |
  36. | wait/synch/mutex/innodb/rw_lock_list_mutex | 16465 |
  37. +--------------------------------------------+------------+
  38.  
  39. | Bytes_received | 648475 |
  40. | Com_select | 15080 |
  41. | Handler_commit | 15081 |
  42. | Handler_read_key | 30160 |
  43. | Innodb_buffer_pool_read_requests | 30181 |
  44. | Innodb_rows_read | 14979 |
  45.  
  46.  
  47.  
  48. mysql [localhost] {msandbox} (test) > Benchmark
  49. Average number of seconds to run all queries: 323.958 seconds
  50. Minimum number of seconds to run all queries: 323.958 seconds
  51. Maximum number of seconds to run all queries: 323.958 seconds
  52. Number of clients running queries: 8
  53. Average number of queries per client: 1250000
  54.  
  55. 30864 qps
  56.  
  57. MariaDB 5.2.3 MyISAM
  58.  
  59. query_cache_type=0
  60. innodb_buffer_pool_size=250M
  61. key_cache_segments=64
  62. key_buffer_size=256M
  63.  
  64.  
  65. +--------------------+----------+
  66. | Status | Duration |
  67. +--------------------+----------+
  68. | starting | 0.000057 |
  69. | System lock | 0.000008 |
  70. | Table lock | 0.000045 |
  71. | query end | 0.000006 |
  72. | freeing items | 0.000013 |
  73. | logging slow query | 0.000006 |
  74. | cleaning up | 0.000005 |
  75. +--------------------+----------+
  76.  
  77.  
  78. stephane@perro:~/sandboxes/msb_5_2_3> time ./my sqlslap -ustephane test -p --create-schema=test --query="HANDLER login READ \`PRIMARY\`=(10000)" --concurrency=8 --number-of-queries=10000000
  79. Enter password:
  80.  
  81. Benchmark
  82. Average number of seconds to run all queries: 326.757 seconds
  83. Minimum number of seconds to run all queries: 326.757 seconds
  84. Maximum number of seconds to run all queries: 326.757 seconds
  85. Number of clients running queries: 8
  86. Average number of queries per client: 1250000
  87.  
  88.  
  89. real 5m27.802s
  90. user 2m55.871s
  91. sys 3m2.723s
  92.  
  93.  
  94.  
  95. +---------------------------------------+------------+
  96. | Variable_name | Value |
  97. +---------------------------------------+------------+
  98. | Bytes_sent | 9408004 |
  99. | Com_ha_read | 30717 |
  100. | Handler_read_key | 30718 |
  101. | Key_read_requests | 123062 |
  102. | Queries | 30719 |
  103. | Questions | 30719 |
  104. | Table_locks_immediate | 30775 |
  105.  
  106. +---------------------------------------+------------+
  107. | Variable_name | Value |
  108. +---------------------------------------+------------+
  109. | Bytes_sent | 9410758 |
  110. | Com_ha_read | 30728 |
  111. | Handler_read_key | 30725 |
  112. | Key_read_requests | 122895 |
  113.  
  114.  
  115. procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
  116. r b swpd free buff cache si so bi bo in cs us sy id wa
  117. 9 0 120 628480 83936 1010752 0 0 1 117 1 3 3 0 96 1
  118. 10 0 120 628480 83936 1010784 0 0 0 0 1109 123640 65 35 0 0
  119. 9 0 120 628480 83936 1010784 0 0 0 26 1137 123198 65 35 0 0
  120. 10 0 120 628464 83936 1010784 0 0 0 0 1050 123453 64 36 0 0
  121. 8 0 120 628464 83936 1010784 0 0 0 0 1061 123534 66 34 0 0
  122. 9 0 120 628464 83936 1010784 0 0 0 76 1088 123548 63 37 0 0
  123. 8 0 120 628464 83936 1010784 0 0 0 0 1053 122171 64 36 0 0
  124.  
  125. Oprofile :
  126. 566426 23.4969 /no-vmlinux /home/stephane/5.2.3/bin/mysqld /no-vmlinux
  127. 353985 14.6842 /no-vmlinux /home/stephane/5.2.3/bin/mysqlslap /no-vmlinux
  128. 131994 5.4755 /lib64/libc-2.9.so /home/stephane/5.2.3/bin/mysqld /lib64/libc-2.9.so
  129. 126725 5.2569 /lib64/libc-2.9.so /home/stephane/5.2.3/bin/mysqlslap /lib64/libc-2.9.so
  130. 78529 3.2576 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld MYSQLparse(void*)
  131. 52751 2.1883 /lib64/libpthread-2.9.so /home/stephane/5.2.3/bin/mysqld pthread_mutex_lock
  132. 38664 1.6039 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld MYSQLlex(void*, void*)
  133. 36848 1.5286 /usr/bin/oprofiled /usr/bin/oprofiled /usr/bin/oprofiled
  134. 32702 1.3566 /no-vmlinux /no-vmlinux /no-vmlinux
  135. 26797 1.1116 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld Protocol::net_store_data(unsigned char const*, unsigned long, charset_info_st const*, charset_info_st const*)
  136. 26756 1.1099 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld find_key_block
  137. 22769 0.9445 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld bmove512
  138. 22696 0.9415 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld mysql_ha_read(THD*, TABLE_LIST*, enum_ha_read_modes, char*, List<Item>*, ha_rkey_function, Item*, unsigned long long, unsigned long long)
  139. 19845 0.8232 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld dispatch_command(enum_server_command, THD*, char*, unsigned int)
  140. 19521 0.8098 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld Protocol::send_fields(List<Item>*, unsigned int)
  141. 19283 0.7999 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld copy_and_convert(char*, unsigned int, charset_info_st const*, char const*, unsigned int, charset_info_st const*, unsigned int*)
  142. 18953 0.7862 /lib64/libpthread-2.9.so /home/stephane/5.2.3/bin/mysqld __pthread_mutex_unlock_usercnt
  143. 18761 0.7783 /home/stephane/5.2.3/bin/mysqlslap /home/stephane/5.2.3/bin/mysqlslap cli_read_rows
  144. 18646 0.7735 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld ha_key_cmp
  145. 18421 0.7642 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld simple_key_cache_read
  146. 16992 0.7049 /home/stephane/5.2.3/bin/mysqlslap /home/stephane/5.2.3/bin/mysqlslap my_real_read
  147. 14467 0.6001 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld insert_fields(THD*, Name_resolution_context*, char const*, char const*, List_iterator<Item>*, bool)
  148. 12763 0.5294 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld alloc_root
  149. 12575 0.5216 /home/stephane/5.2.3/bin/mysqlslap /home/stephane/5.2.3/bin/mysqlslap alloc_root
  150. 12215 0.5067 /home/stephane/5.2.3/bin/mysqld /home/stephane/5.2.3/bin/mysqld _mi_bin_search
  151. 11190 0.4642 /home/stephane/5.2.3/bin/mysqlslap /home/stephane/5.2.3/bin/mysqlslap vio_read_buff
  152. 11121 0.4613 /lib64/libpthread-2.9.so /home/stephane/5.2.3/bin/mysqld pthread_getspecific
  153.  
  154.  
  155. MariaDB 5.2.3 XtraDB
  156.  
  157. mysql [localhost] {stephane} (test) > show profile for query 1;
  158. +--------------------+----------+
  159. | Status | Duration |
  160. +--------------------+----------+
  161. | starting | 0.000052 |
  162. | System lock | 0.000007 |
  163. | Table lock | 0.000073 |
  164. | query end | 0.000006 |
  165. | freeing items | 0.000017 |
  166. | logging slow query | 0.000005 |
  167. | cleaning up | 0.000005 |
  168. +--------------------+----------+
  169. 7 rows in set (0.01 sec)
  170.  
  171.  
  172. | Handler_read_key | 30770 |
  173.  
  174.  
  175.  
  176. procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
  177. 21:24 svaroqui r b swpd free buff cache si so bi bo in cs us sy id wa
  178. 21:24 svaroqui 7 0 120 17128 379060 1281564 0 0 1 119 4 4 3 0 96 1
  179. 21:24 svaroqui 2 4 0 120 17000 379060 1281600 0 0 0 0 1044 125528 63 36 1 0
  180. 21:24 svaroqui 4 0 120 17000 379060 1281600 0 0 0 22 1046 125350 62 38 1 0
  181.  
  182.  
  183.  
  184.  
  185. MySQL 5.5 - MyISAM -READ \`PRIMARY\`=(10000)
  186.  
  187.  
  188.  
  189. mysql [localhost] {root} (performance_schema) > SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
  190. +------------------------------------------------------+------------+
  191. | EVENT_NAME | COUNT_STAR |
  192. +------------------------------------------------------+------------+
  193. | wait/synch/mutex/mysys/KEY_CACHE::cache_lock | 58256213 |
  194. | wait/synch/mutex/sql/THD::LOCK_thd_data | 33575372 |
  195. | wait/io/file/myisam/dfile | 21408329 |
  196. | wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock | 14564146 |
  197. | wait/synch/mutex/mysys/THR_LOCK::mutex | 14564136 |
  198. | wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock | 6988215 |
  199. | wait/synch/mutex/innodb/mutex_list_mutex | 8359 |
  200. | wait/synch/mutex/innodb/rw_lock_list_mutex | 8272 |
  201. | wait/synch/mutex/innodb/buf_pool_mutex | 1449 |
  202. | wait/synch/mutex/innodb/log_sys_mutex | 1432 |
  203. +------------------------------------------------------+------------+
  204. 10 rows in set (0.00 sec)
  205.  
  206. INNODB - HANDLER login READ \`PRIMARY\`=(10000)
  207. best concurency at 6
  208.  
  209. | Handler_read_key | 63002 |
  210.  
  211.  
  212.  
  213. mysql [localhost] {msandbox} (performance_schema) > SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;
  214. +-----------------------------------------------+------------+
  215. | EVENT_NAME | COUNT_STAR |
  216. +-----------------------------------------------+------------+
  217. | wait/synch/mutex/sql/THD::LOCK_thd_data | 11442504 |
  218. | wait/synch/mutex/innodb/kernel_mutex | 6971775 |
  219. | wait/synch/mutex/mysys/THR_LOCK::mutex | 4647757 |
  220. | wait/synch/rwlock/innodb/btr_search_latch | 2323685 |
  221. | wait/synch/mutex/innodb/mutex_list_mutex | 16560 |
  222. | wait/synch/mutex/innodb/rw_lock_list_mutex | 16465 |
  223. | wait/synch/mutex/innodb/buf_pool_mutex | 12409 |
  224. | wait/synch/mutex/innodb/log_sys_mutex | 10099 |
  225. | wait/synch/mutex/innodb/log_flush_order_mutex | 9089 |
  226. | wait/synch/mutex/innodb/rseg_mutex | 2829 |
  227. +-----------------------------------------------+------------+
  228. +--------------------+----------+
  229. | Status | Duration |
  230. +--------------------+----------+
  231. | starting | 0.000045 |
  232. | System lock | 0.000072 |
  233. | query end | 0.000005 |
  234. | closing tables | 0.000004 |
  235. | freeing items | 0.000012 |
  236. | logging slow query | 0.000004 |
  237. | cleaning up | 0.000003 |
  238. +--------------------+----------+
  239.  
  240.  
  241.  
  242. MariaDB 5.3 MYISAM
  243.  
  244.  
  245. Variable_name | Value |
  246. +------------------------------------+-----------+
  247. | Bytes_sent | 9930181 |
  248. | Com_ha_read | 32431 |
  249. | Com_show_status | 1 |
  250. | Created_tmp_tables | 1 |
  251. | Handler_read_key | 32431 |
  252. | Handler_read_rnd_next | 258 |
  253. | Handler_write | 257 |
  254. | Key_read_requests | 129709 |
  255.  
  256. Benchmark
  257. Average number of seconds to run all queries: 318.000 seconds
  258. Minimum number of seconds to run all queries: 318.000 seconds
  259. Maximum number of seconds to run all queries: 318.000 seconds
  260. Number of clients running queries: 6
  261. Average number of queries per client: 1666666
  262.  
  263.  
  264. real 5m19.724s
  265. user 3m13.044s
  266. sys 3m1.775s
  267.  
  268. mysql [localhost] {stephane} (test) > show profile for query 1;
  269. +--------------------+----------+
  270. | Status | Duration |
  271. +--------------------+----------+
  272. | starting | 0.000043 |
  273. | System lock | 0.000006 |
  274. | Table lock | 0.000061 |
  275. | query end | 0.000005 |
  276. | freeing items | 0.000012 |
  277. | logging slow query | 0.000003 |
  278. | cleaning up | 0.000004 |
  279. +--------------------+----------+
  280.  
  281.  
  282. Handler socket on MariaDB 5.3 and 5.2
  283.  
  284.  
  285. home/stephane/5.3/sql/handler.h:1807: error: ‘virtual int handler::index_read_map(uchar*, const uchar*, key_part_map, ha_rkey_function)’ is protected
  286. database.cpp:688: error: within this context
  287. /home/stephane/5.3/sql/handler.h:1823: error: ‘virtual int handler::index_prev(uchar*)’ is protected
  288. database.cpp:693: error: within this context
  289. /home/stephane/5.3/sql/handler.h:1821: error: ‘virtual int handler::index_next(uchar*)’ is protected
  290. database.cpp:697: error: within this context
  291. /home/stephane/5.3/sql/handler.h:1829: error: ‘virtual int handler::index_next_same(uchar*, const uchar*, uint)’ is protected
  292. database.cpp:700: error: within this context
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!