| Bug #92360 | MySQL 8.0 Sysbench Benchmark (Point Selects) TPS/QPS was 500k,less than expected | ||
|---|---|---|---|
| Submitted: | 11 Sep 2018 8:58 | Modified: | 26 Oct 2018 13:23 |
| Reporter: | Lee Jay | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S5 (Performance) |
| Version: | 8.0 | OS: | Debian (Linux fdc-aa02-crdb.i.nease.net 4.9.0-8-amd64 #1 SMP Debian 4.9.110-3+deb9u4 (2018-08-21) x86_64 GNU) |
| Assigned to: | CPU Architecture: | Other (Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz) | |
| Tags: | MySQL 8.0, qps, Sysbench Benchmark, TPS | ||
[11 Sep 2018 8:58]
Lee Jay
[12 Sep 2018 3:47]
MOUSTAFA AHMED
Where is my.cnf for 5.7? Pt-config-diff Will give better and more beneficial comparison than raw variables..
[12 Sep 2018 9:40]
Lee Jay
The following file is my.cnf for mysql5.7 [client] port = 3306 socket = /home/mysql5.7.13_data/mysql5.sock [mysql] default-character-set = latin1 no-auto-rehash [mysqld] port=3306 server_id=5713 max_connections=3000 datadir=/home/mysql5.7.13_data/data tmpdir=/home/mysql5.7.13_data/tmp socket = /home/mysql5.7.13_data/mysql5.sock log-error = /home/mysql5.7.13_data/mysqld8.log pid-file = /home/mysql5.7.13_data/mysqld8.pid log_bin=/home/mysql5.7.13_data/binlog/gas3dp-bin log-bin-index=/home/mysql5.7.13_data/binlog/mysql-bin.index relay-log=/home/mysql5.7.13_data/binlog/localhost-relay-bin default_authentication_plugin=mysql_native_password ######## the following configuration is according to gas3 dp ##### default_storage_engine=Innodb default_tmp_storage_engine=Innodb core_file general_log=OFF slow_query_log=ON long_query_time=2 sync_binlog=1 log_slave_updates=ON slave_net_timeout=3600 binlog_format=ROW binlog_row_image=FULL relay_log_recovery=1 master_info_repository=TABLE relay_log_info_repository=TABLE slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 innodb_io_capacity=2000 innodb_io_capacity_max=20000 innodb_write_io_threads=16 #query_cache_size=0 key_buffer_size=128M myisam_mmap_size=12M tmp_table_size=3M sort_buffer_size=3M max_heap_table_size=3M join_buffer_size=3M bulk_insert_buffer_size=3M explicit_defaults_for_timestamp=ON #innodb innodb_data_home_dir=/home/mysql5.7.13_data/innodb #innodb_file_format=Barracuda #innodb_file_format_max=Barracuda innodb_file_per_table=ON innodb_buffer_pool_size=48G innodb_sort_buffer_size=48M innodb_log_group_home_dir=/home/mysql5.7.13_data/innodb innodb_log_files_in_group=3 innodb_log_file_size=1024M innodb_log_buffer_size=128M innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT innodb_print_all_deadlocks=ON wait_timeout=604800 interactive_timeout=604800 sql_mode="" log_timestamps=system secure_file_priv=''
[12 Sep 2018 9:42]
Lee Jay
The configuration for mysql5.7 is almost the same as that of mysql8.0
[12 Sep 2018 9:57]
Lee Jay
MySQL 5.7 and MySQL 8.0 share the same configuration, for example: default_storage_engine=Innodb default_tmp_storage_engine=Innodb sync_binlog=1 innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_size=48G innodb_sort_buffer_size=48M innodb_io_capacity=2000 innodb_io_capacity_max=20000
[17 Sep 2018 12:52]
Dimitri KRAVTCHUK
Hi Lee, may you, please, replay your test locally on your server and using IP port (e.g. 127.0.0.1 as hostname and same port number as you used initially) -- will you then still observe the same difference or not ?.. (I'm trying to reproduce the issue you're reporting) Rgds, -Dimitri
[21 Sep 2018 1:58]
Lee Jay
hi,Dimitri I have replayed the test locally on my server using IP 127.0.0.1 and same port number as used initially， but still get similar or little worse result than before. I guess it's because both test client and test server are on the same machine. And what about your reproducing conclusion? looking forwarding to your reply. Rgds, -Lee
[21 Sep 2018 10:10]
Dimitri KRAVTCHUK
Hi Lee, to be honest, I'm really surprised by your results.. My system is pretty similar to yours : Intel(R) Xeon(R) CPU E5-2699 v4 @ 2.20GHz just that I have 44cores-HT and you mentioning 40cores-HT, so the difference in 4cores should not be a big deal to make any big difference in QPS results.. However, in my case I'm getting over 1M QPS, while you're only staying around 500K QPS, and it's specially on point-selects where queries should just "fly".. My results you can see from the following post : - http://dimitrik.free.fr/blog/posts/mysql-performance-80-ga-ip-port-vs-unix-socket-impact.h... (see graphs on 44cores-HT Broadwell) is your system really 40cores ? what is your "lscpu" output ? then, regarding config file -- I've tried to summarize all the settings you're using into the following : -------------------------------------------------------------- [mysqld] server_id=229 max_connections=3000 log_bin=log-bin log-bin-index=mysql-bin.index relay-log=localhost-relay-bin default_authentication_plugin=mysql_native_password default_storage_engine=Innodb default_tmp_storage_engine=Innodb core_file general_log=OFF slow_query_log=ON long_query_time=2 sync_binlog=1 log_slave_updates=ON slave_net_timeout=3600 binlog_format=ROW binlog_row_image=FULL relay_log_recovery=1 master_info_repository=TABLE relay_log_info_repository=TABLE slave_parallel_type=LOGICAL_CLOCK slave_parallel_workers=8 innodb_io_capacity=2000 innodb_io_capacity_max=20000 innodb_write_io_threads=16 #query_cache_size=0 key_buffer_size=128M myisam_mmap_size=12M tmp_table_size=3M sort_buffer_size=3M max_heap_table_size=3M join_buffer_size=3M bulk_insert_buffer_size=3M explicit_defaults_for_timestamp=ON innodb_file_per_table=ON innodb_buffer_pool_size=48G innodb_sort_buffer_size=48M innodb_log_files_in_group=3 innodb_log_file_size=1024M innodb_log_buffer_size=128M innodb_flush_log_at_trx_commit=1 innodb_flush_method=O_DIRECT innodb_print_all_deadlocks=ON wait_timeout=604800 interactive_timeout=604800 sql_mode="" log_timestamps=system secure_file_priv='' -------------------------------------------------------------- is it ok for you ? with this config I'm still getting over 1M QPS on MySQL 8.0 (and the same with MySQL 5.7 as well). so, right now no idea why on your side you're observing such different results.. Rgds, -Dimitri
[25 Sep 2018 12:54]
Lee Jay
hi Dimitri, first of all, thank you very much for your reply. yes, I'm still confused why I get such different results on my side and now trying to figure it out. the following is information about the innodb status and process list while testing with the same config. -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1. "lscpu" output - 40 cpus Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 40 On-line CPU(s) list: 0-39 Thread(s) per core: 2 Core(s) per socket: 10 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 79 Model name: Intel(R) Xeon(R) CPU E5-2630 v4 @ 2.20GHz Stepping: 1 CPU MHz: 1199.000 CPU max MHz: 3100.0000 CPU min MHz: 1200.0000 BogoMIPS: 4401.29 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 25600K NUMA node0 CPU(s): 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38 NUMA node1 CPU(s): 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2.prepare data and run the sysbench test prepare: ~/sysbench$ /home/mysql/sysbench/bin/sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3309 --mysql-user=mysql --mysql-password=mysql --mysql-db=dbtest --time=120 --report-interval=1 --threads=128 /home/mysql/sysbench/share/sysbench/oltp_point_select.lua --auto-inc=on --tables=8 --table-size=10000000 prepare run: ~/sysbench$ /home/mysql/sysbench/bin/sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3309 --mysql-user=mysql --mysql-password=mysql --mysql-db=dbtest --time=120 --report-interval=1 --threads=128 /home/mysql/sysbench/share/sysbench/oltp_point_select.lua --auto-inc=on --tables=8 --table-size=10000000 run cleanup: ~/sysbench$ /home/mysql/sysbench/bin/sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3309 --mysql-user=mysql --mysql-password=mysql --mysql-db=dbtest --time=120 --report-interval=1 --threads=128 /home/mysql/sysbench/share/sysbench/oltp_point_select.lua --auto-inc=on --tables=8 --table-size=10000000 cleanup and the sysbench report is like: thds: 128 tps: 480011.29 qps: 480011.29 (r/w/o: 480011.29/0.00/0.00) lat (ms,95%): 0.42 err/s: 0.00 reconn/s: 0.00
[25 Sep 2018 12:57]
Lee Jay
show full processlist and innodb status
Attachment: innodb_status.txt (text/plain), 27.20 KiB.
[25 Sep 2018 13:00]
Lee Jay
I wonder if there is/are anything wrong, needed to be modified or added. expecting your reply. yours, -Lee
[25 Sep 2018 13:43]
Dimitri KRAVTCHUK
Hi Lee, seems like these details are explaining everything : first of all, your system has 20cores-HT (and not 40cores-HT as initially mentioned) -- from "lscpu" output you can see that you have 2CPU sockets on your system, each with 10cores-HT (HT = hyper-threading (e.g. 2 threads per core)) -- so, yes, you have 40 vcpu, but only 20 real cores. This is explaining why you're reaching only 500K QPS and not 1M QPS.. then, the next issue is in your sysbench command line -- you're missing a very important option here : "--rand-type=uniform" by default sysbench will use "special" instead, which will make the whole test case unfair (well, of course all depends what is the goal of the test ;-)) -- but with "special" random access your user threads will mostly hit the same rows (it'll be similar like if you test a very small data size, so you'll be reading then often even not from RAM, but from CPU caches..) -- as such a situation is not common at all for database workloads, using "uniform" access will be much more fair here.. Try to replay with --rand-type=uniform now and let me know what kind of results you're observing. Generally there should be no or very small difference between 5.7 and 8.0 in this kind of workload. NOTE : be sure also to test both MySQL versions in the same conditions (e.g. if you use latin1 in 5.7, then use also latin1 charset for 8.0 as well, etc.) Rgds, -Dimitri
[26 Sep 2018 8:53]
Lee Jay
hi Dimitri, You help me a lot to understand the meaning of the "lscpu" output. Now I got it. And it does explain why I'm reaching only 500K QPS rather than 1M QPS. Then, with --rand-type=uniform added and using latin1 both in 5.7 and 8.0, I replay the test and the outcome is as following: --------------------------------------------------------------------------------------------------------------------------- workload : Sysbench point select data volume : 8 tables of 10M rows each encoding : latin1 user load levels : 1, 2, 4, .. 1024 engines : MySQL 8.0, MySQL 5.7 replay result(TPS or QPS): thread num MySQL5.7 MySQL8.0 1 8062.13 7472.38 2 16337.55 14784.83 4 32773.59 31014.67 8 70026.82 65768.99 16 158027.49 151900.25 32 315657.33 297423.32 64 472389.23 424431.45 128 557826.72 510477.62 256 550488.11 501599.16 512 531786.18 482864.66 1024 485846.34 451044.52 --------------------------------------------------------------------------------------------------------------------------- mysql5.7 reaches 557k QPS while 8.0 reaches 510k. There is small difference between 5.7 and 8.0 in this kind of workload just as you mentioned above (and why ?). as mentioned in this link (https://www.mysql.com/why-mysql/benchmarks/) , mysql 8.0 has a large gain over MySQL 5.7 on the top. So it seeems that I would have drawn a similar conclusion but it doesn't happen in my case. And your result in this post (http://dimitrik.free.fr/blog/posts/mysql-performance-21m-qps-on-80rc.html) is similar like mine. while talking about mysql 8.0 , everyone around me insists that it must have a large gain over MySQL 5.7. But my test result above prove not. What about your viewpoint and how should it be made clear ? Rgds, -Lee
[3 Oct 2018 20:09]
Dimitri KRAVTCHUK
Hi Lee, I think the main confusion is coming from the benchmark result published on mysql.com -- if you'll look on the title, it's mentioning the results was obtained on IO-bound workload (not in-memory which you're trying to reproduce), and all the details about this result you can find from here : - http://dimitrik.free.fr/blog/posts/mysql-performance-1m-iobound-qps-with-80-ga-on-intel-op... you will need to use a very fast and low latency storage (like Intel Optaine NVMe) -- this benchmark result was here to show our progress on IO-bound workloads as well (not only in-memory ;-)) However, yes, there is a "regression" now in MySQL 8.0 -vs- 5.7 on in-memory point-select workload, around 10% -- I can confirm it now. Indeed, this was not expected, but unfortunately coming with any SW code which is constantly growing with more and more features. Seems like our main issue is related to instructions which are less and less matching CPU cache.. From the other side, the obtained results on point-selects are pretty high (and these are SQL queries (!), not API key/value ;-)) -- so, once we fixed scalability issues here since 5.7, this is no more on high priority (but definitively part of other investigations which we're doing to improve the code for overall efficiency).. while regarding the max QPS results I'm obtaining on 44cores-HT Broadwell (like yours) are : 1.184M for MySQL 8.0 and 1.313M for MySQL 5.7 however on 48cores-HT Skylake it's : 1.969M for MySQL 8.0 and 1.888M for MySQL 5.7 which is at least giving expectations that with newer CPUs the things may still go better ;-)) Thank you for reporting the issue ! -- stay tuned.. Rgds, -Dimitri
[8 Oct 2018 7:31]
victor zheng
hi,hi,Dimitri.
I am Lee's colleuge.We do the test work together.
the result of Lee's result is as belows:
users MySQL8.0.3 MySQL8.0.11 MySQL5.7.12
1 7741.56 8969.97 8039.47
2 15031.38 18482.32 16039.74
4 31432.79 36849.98 32008.82
8 65832.87 72674.37 70298.56
16 155385.12 130536.82 159168.68
32 296262.23 207442.07 317195.47
64 437290.15 287950.62 472959.41
128 513392.78 369015.35 542102.88
256 505372.06 427250.67 548766.58
512 495553.15 407806.51 538539.79
1024 464846.53 379987.09 476207.88
Here is my confusion.
2;
Our environment: debian 8
software:
uname -a
Linux fdc-aa07-crdb.i.nease.net 3.16.0-4-amd64 #1 SMP Debian 3.16.43-2+deb8u2 (2017-06-26) x86_64 GNU/Linux
I install this mysql version from source :
(mysql-8.0.12.tar.gz) MD5: 569b6037bad533ad84c2b8d29534a68f
3. test command
here is the test command
SYSBENCH="/usr/local/bin/sysbench"
SCRIPT_DIR="/usr/local/share/sysbench"
ACTION=${1}
NUM=${2-100000}
TYPE=${4-"oltp_insert.lua"}
THREAD=${3-64}
export LD_LIBRARY_PATH=/home/zzhn1689/lib
cd ${SCRIPT_DIR}
${SYSBENCH} ${SCRIPT_DIR}/${TYPE} \
--mysql-host=... \
--mysql-port=3307 \
--mysql-user=sbtest \
--mysql-password='...' \
--mysql-db=sbtest \
--db-driver=mysql \
--tables=10 \
--table-size=${NUM} \
--report-interval=10 \
--threads=${THREAD} \
--time=300 \
${ACTION}
#prepare/run/cleanup
no --rand-type=uniform config to test both mysql 5.7 and mysql 8.0.12
4. test result
128 threads, point select case.
5.7: 52w QPS
8.0: 22w QPS
5. INFO
5.1 show engine innodb status
I observe the show engine innodb status of 5.7 and 8.0.
mysql 8.0
show engine innodb status:
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2140534
OS WAIT ARRAY INFO: signal count 286212
RW-shared spins 100042, rounds 195304, OS waits 95428
RW-excl spins 5388, rounds 229920, OS waits 11913
RW-sx spins 1765374, rounds 52326396, OS waits 1712379
Spin rounds per wait: 1.95 RW-shared, 42.67 RW-excl, 29.64 RW-sx
------------------------
mysql 5.7
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1353186
OS WAIT ARRAY INFO: signal count 2374142
RW-shared spins 0, rounds 1598484, OS waits 204490
RW-excl spins 0, rounds 102635128, OS waits 974146
RW-sx spins 2, rounds 60, OS waits 2
Spin rounds per wait: 1598484.00 RW-shared, 102635128.00 RW-excl, 30.00 RW-sx
******** line********
compared to mysql 5.7: spins alomst equals to 0, but 8.0 is pretty high.
I don't know does it related to the test result?
*****line****
mysql 8.0
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35181821952
Dictionary memory allocated 629045
Buffer pool size 2097152
Free buffers 1155124
Database pages 942028
Old database pages 347413
Modified db pages 227842
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 28214, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 589719, created 352309, written 982255
0.00 reads/s, 2994.75 creates/s, 1031.34 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 942028, unzip_LRU len: 0
I/O sum[0]:cur[37968], unzip sum[0]:cur[0]
mysql 5.7
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35181821952
Dictionary memory allocated 723397
Buffer pool size 2096896
Free buffers 1445133
Database pages 611898
Old database pages 226032
Modified db pages 0
Pending reads 1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 600704, created 11193, written 11392
2959.52 reads/s, 0.00 creates/s, 0.52 writes/s
Buffer pool hit rate 998 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 611898, unzip_LRU len: 0
I/O sum[0]:cur[14664], unzip sum[0]:cur[0]
buffer pool read is zero in mysql 8.0
5.2 cpu system and user space
when running the test, cpu is alomst full used.
but mysql 5.7: 90% cpu=70% user space+20% system space
mysql 8.0: 90% cpu=70% system space+20% user space
I think mysql 8.0 is stuck in mutex competition?
This is my guess. And I use perf to see what cpu is doing.
mysql 8.0 cpu:
47.55% _raw_spin_lock
6. use another version mysql 8.0
We install another version. It seems the performance increases with the same config and test command.
(mysql-server_8.0.11-1debian9_amd64.deb-bundle.tar) MD5: 14dfc45e1f8bf09ff45ffda4178efe70)
[8 Oct 2018 9:08]
Dimitri KRAVTCHUK
Hi Victor, there is definitively something going strange in your tests, which I cannot reproduce, so probably related to the server in use or something else.. from what I see the most negative difference is coming since 32-64 concurrent users, and was it "uniform" or "special" --rand-type ? well, to avoid any ambiguity, may we simplify the whole test to the following : * test : point_selects * data volume : 8 tables of 10000000 rows each (10M) * rand-type : special, uniform (to test both) * concurrent users : 64 * connection : local (host=127.0.0.1, IP port) * MySQL versions : 5.7.12, 8.0.3, 8.0.11 (as you have them all) could you replay, please, these 6 tests and during each test copy the output of "perf top -z" and paste here -- let's hope it'll point on something.. I suppose the server remains the same 20cores-HT as before ? Rgds, -Dimitri
[12 Oct 2018 8:29]
victor zheng
test result of MYSQL 8.0.12
64 thread, mysql 8.0.12 , --rand-type=uniform
```
22.54% [kernel] [k] native_queued_spin_lock_slowpath
1.07% [kernel] [k] entry_SYSCALL_64
1.02% [kernel] [k] syscall_return_via_sysret
0.84% [kernel] [k] futex_wait_setup
0.78% mysqld [.] btr_search_guess_on_hash
0.71% [kernel] [k] _raw_spin_lock
0.70% libpthread-2.24.so [.] __pthread_rwlock_wrlock
0.62% libpthread-2.24.so [.] __pthread_rwlock_wrlock_slow
0.56% mysqld [.] row_search_mvcc
0.47% [kernel] [k] idle_cpu
0.46% mysqld [.] JOIN::optimize
0.45% [kernel] [k] ixgbe_poll
0.45% [kernel] [k] __schedule
0.44% [kernel] [k] futex_wake
0.42% libpthread-2.24.so [.] __pthread_m
```
--rand-type=special \
```
24.29% [kernel] [k] native_queued_spin_lock_slowpath
1.05% [kernel] [k] syscall_return_via_sysret
1.05% [kernel] [k] entry_SYSCALL_64
0.88% [kernel] [k] futex_wait_setup
0.69% libpthread-2.24.so [.] __pthread_rwlock_wrlock
0.68% [kernel] [k] _raw_spin_lock
0.63% mysqld [.] btr_search_guess_on_hash
0.62% libpthread-2.24.so [.] __pthread_rwlock_wrlock_slow
0.56% mysqld [.] row_search_mvcc
0.46% [kernel] [k] futex_wake
0.45% [kernel] [k] __schedule
0.45% mysqld [.] JOIN::optimize
0.45% [kernel] [k] idle_cpu
0.44% libpthread-2.24.so [.] __pthread_mu
```
[12 Oct 2018 9:00]
victor zheng
8.0.2 test
64 threads , random type
```
2.16% mysqld [.] btr_search_guess_on_hash(dict_index_t*, btr_se
1.14% mysqld [.] create_metadata_lock(void*, MDL_key const*, in
1.03% mysqld [.] open_table(THD*, TABLE_LIST*, Open_table_conte
0.99% [kernel] [k] ixgbe_poll
0.93% mysqld [.] row_search_mvcc(unsigned char*, page_cur_mode_
0.78% [kernel] [k] irq_entries_start
0.77% mysqld [.] JOIN::optimize()
0.71% libpthread-2.19.so [.] pthread_mutex_lock
0.70% libpthread-2.19.so [.] __pthread_mutex_unlock_usercnt
0.70% mysqld [.] dispatch_command(THD*, COM_DATA const*, enum_s
0.69% [kernel] [k] _raw_spin_lock
0.68% mysqld [.] buf_block_from_ahi(unsigned char const*)
0.64% libc-2.19.so [.] malloc
0.60% [kernel] [k] tcp_ack
0.60% mysqld [.] pfs_start_stage_v1
0.58% mysqld [.] pfs_end_statement_v1
0.58% [kernel] [k] tcp_packet
0.55% [kernel] [k] ixgbe_xmit_frame_ring
0.52% mysqld [.] JOIN::make_join_plan()
0.52% [kernel] [k] ixgbe_clean_rx_irq
0.52% [kernel] [k] __switch_to
0.51% mysqld [.] alloc_root
0.50% [kernel] [k] __nf_conntrack_find_get
0.50% libc-2.19.so [.] memset
0.49% mysqld [.] MDL_context::try_acquire_lock_impl(MDL_request
0.49% mysqld [.] btr_cur_search_to_nth_level(dict_index_t*, uns
0.47% [kernel] [k] ipt_do_table
0.46% mysqld [.] buf_page_get_known_nowait(unsigned long, buf_b
0.45% mysqld [.] check_trx_exists(THD*)
0.43% [kernel] [k] nf_iterate
0.42% mysqld [.] pfs_set_thread_info_v1
Press '?' for help on key bindings
```
special type, 64 threads
```
2.05% mysqld [.] btr_search_guess_on_hash(dict_index_t*, btr_se
1.14% mysqld [.] create_metadata_lock(void*, MDL_key const*, in
1.08% mysqld [.] open_table(THD*, TABLE_LIST*, Open_table_conte
1.00% [kernel] [k] ixgbe_poll
0.94% mysqld [.] row_search_mvcc(unsigned char*, page_cur_mode_
0.77% [kernel] [k] irq_entries_start
0.77% mysqld [.] JOIN::optimize()
0.72% libpthread-2.19.so [.] __pthread_mutex_unlock_usercnt
0.70% libpthread-2.19.so [.] pthread_mutex_lock
0.69% mysqld [.] dispatch_command(THD*, COM_DATA const*, enum_s
0.68% [kernel] [k] _raw_spin_lock
0.65% libc-2.19.so [.] malloc
0.64% mysqld [.] buf_block_from_ahi(unsigned char const*)
0.61% [kernel] [k] tcp_ack
0.60% mysqld [.] pfs_start_stage_v1
0.59% [kernel] [k] tcp_packet
0.59% mysqld [.] pfs_end_statement_v1
0.55% [kernel] [k] ixgbe_xmit_frame_ring
0.54% mysqld [.] JOIN::make_join_plan()
0.53% mysqld [.] alloc_root
0.52% [kernel] [k] ixgbe_clean_rx_irq
0.51% [kernel] [k] __nf_conntrack_find_get
0.50% [kernel] [k] __switch_to
```
[12 Oct 2018 9:37]
victor zheng
5.7.22 uniform type ,64 threads, 42w qps ``` Samples: 655K of event 'cycles', Event count (approx.): 293490569841 1.87% mysqld [.] btr_search_guess_on_hash(dict_index_t*, btr_search_t*, 1.11% mysqld [.] row_search_mvcc(unsigned char*, page_cur_mode_t, row_pr 0.99% [kernel] [k] ixgbe_poll 0.82% libpthread-2.19.so [.] __pthread_mutex_unlock_usercnt 0.78% [kernel] [k] irq_entries_start 0.70% mysqld [.] dispatch_command(THD*, COM_DATA const*, enum_server_com 0.68% [kernel] [k] _raw_spin_lock 0.62% mysqld [.] open_table(THD*, TABLE_LIST*, Open_table_context*) 0.61% mysqld [.] rec_get_offsets_func(unsigned char const*, dict_index_t 0.61% [kernel] [k] tcp_ack 0.61% libpthread-2.19.so [.] pthread_mutex_lock 0.60% mysqld [.] JOIN::optimize() 0.60% [kernel] [k] tcp_packet 0.57% mysqld [.] btr_cur_search_to_nth_level(dict_index_t*, unsigned lon 0.57% [kernel] [k] ixgbe_clean_rx_irq 0.56% mysqld [.] buf_block_from_ahi(unsigned char const*) 0.56% mysqld [.] JOIN::make_join_plan() 0.54% mysqld [.] PolicyMutex<TTASEventMutex<GenericPolicy> >::enter(unsi 0.54% [kernel] [k] ixgbe_xmit_frame_ring 0.53% libpthread-2.19.so [.] pthread_getspecific 0.53% mysqld [.] pfs_start_stage_v1 Press '?' for help on key bindings ``` special type, ``` 1.80% mysqld [.] btr_search_guess_on_hash(dict_index_t*, btr_search 1.16% mysqld [.] row_search_mvcc(unsigned char*, page_cur_mode_t, r 1.00% [kernel] [k] ixgbe_poll 0.83% libpthread-2.19.so [.] __pthread_mutex_unlock_usercnt 0.79% [kernel] [k] irq_entries_start 0.73% mysqld [.] dispatch_command(THD*, COM_DATA const*, enum_serve 0.70% [kernel] [k] _raw_spin_lock 0.63% libpthread-2.19.so [.] pthread_mutex_lock 0.63% mysqld [.] open_table(THD*, TABLE_LIST*, Open_table_context*) 0.61% [kernel] [k] tcp_packet 0.61% [kernel] [k] tcp_ack 0.59% mysqld [.] JOIN::optimize() 0.57% mysqld [.] btr_cur_search_to_nth_level(dict_index_t*, unsigne 0.57% [kernel] [k] ixgbe_clean_rx_irq 0.56% mysqld [.] JOIN::make_join_plan() 0.55% libpthread-2.19.so [.] pthread_getspecific 0.55% mysqld [.] buf_block_from_ahi(unsigned char const*) 0.54% mysqld [.] pfs_start_stage_v1 0.54% [kernel] [k] ixgbe_xmit_frame_ring ```
[12 Oct 2018 9:38]
victor zheng
mysql 8.0.12 : 20w qps 8.0.3: 40w qps 5.7.22 : 42w qps
[12 Oct 2018 19:33]
Dimitri KRAVTCHUK
Hi Victor, thanks a lot for all these collected data ! as you can see from the "perf" output from MySQL 8.0.12 tests, there is a lock contention reported which has nothing to do in such kind of workload : 22.54% [kernel] [k] native_queued_spin_lock_slowpath before to go in "perf" details to see from where this contention is going, may you, please, just replay the same 8.0.12 "uniform" test with the my.conf file from here : - http://dimitrik.free.fr/blog/posts/mysql-performance-21m-qps-on-80rc.html and collect the "perf top -z" output as well, this will at least confirm there is nothing came wrong with config settings and the problem is coming from something else.. Rgds, -Dimitri
[14 Oct 2018 9:05]
victor zheng
mysql 8.0.12 uniform type, 64 thread, point-select case perf result: ``` 1.79% mysqld [.] rec_get_offsets_func 1.54% mysqld [.] page_cur_search_with_match 1.32% mysqld [.] cmp_dtuple_rec_with_match_low 1.11% mysqld [.] buf_page_get_gen 1.10% mysqld [.] btr_cur_search_to_nth_level 0.90% mysqld [.] row_search_mvcc 0.82% [kernel] [k] ixgbe_poll 0.81% [kernel] [k] entry_SYSCALL_64 0.79% [kernel] [k] tcp_packet 0.76% mysqld [.] open_table 0.74% mysqld [.] JOIN::optimize 0.73% mysqld [.] dispatch_command 0.72% libpthread-2.24.so [.] pthread_mutex_lock 0.72% [kernel] [k] syscall_return_via_sysret 0.70% [kernel] [k] irq_entries_start 0.63% mysqld [.] JOIN::make_join_plan 0.60% mysqld [.] rec_init_offsets 0.59% [kernel] [k] _raw_spin_lock 0.56% libpthread-2.24.so [.] __pthread_mutex_unlock_usercnt 0.56% [kernel] [k] ixgbe_clean_rx_irq 0.55% [kernel] [k] tcp_ack 0.54% mysqld [.] PolicyMutex<TTASEventMutex<GenericPolicy> > 0.52% mysqld [.] 0x0000000001d3c18d 0.50% mysqld [.] mysql_execute_command 0.47% libc-2.24.so [.] malloc 0.45% [kernel] [k] ixgbe_xmit_frame_ring 0.44% [kernel] [k] idle_cpu 0.43% [kernel] [k] nf_conntrack_in 0.43% [kernel] [k] tcp_recvmsg ``` test result: 35 w qps
[15 Oct 2018 16:35]
Dimitri KRAVTCHUK
Hi Victor, thanks for sharing this ! as you can see, the observed contention is gone, and your QPS result is moved from 20w to 35w. Now, what was the reason of contention -- I think you can find it by comparing your config file -vs- mine (at least we know now it's directly related to your config -- however, curiously I was unable to reproduce your issue even using your config, except if you used something yet more different which was not not mentioned here).. And, indeed, I'm also curious to know what exactly is that impacting in your case ? Rgds, -Dimitri
[16 Oct 2018 9:37]
victor zheng
hi,Dimitri.here is my system version
```
Linux fdc-aa02-crdb.i.nease.net 4.9.0-8-amd64 #1 SMP Debian 4.9.110-3+deb9u4 (2018-08-21) x86_64 GNU/Linux
```
Is there anything wrong with this?
And I can not understand mysql 5.7 working with the same config is OK. But only mysql 8.0.12 works wrong.
main difference between the configs ars as below shows:
```
innodb_doublewrite=0,1
innodb_flush_method=O_DIRECT_NO_FSYNC, O_DIRECT
innodb_adaptive_hash_index=0, ON
innodb_buffer_pool_instances=8,16
innodb_max_purge_lag_delay=300000,0
innodb_read_io_threads=4,16
```
[16 Oct 2018 13:48]
Dimitri KRAVTCHUK
Hi Victor, may you, please, change these 6 options you're mentioning within my config (according your setting) and replay the same point-select test using this modified config ? my feeling here is that you'll still obtain "good" results even with this modified conf file, and if so -- then the problem is related to other config options which you did not list here.. (except if there is a bug or another issue) Rgds, -Dimitri
[16 Oct 2018 14:29]
victor zheng
After many test, I found the key config affecting the result ssl=0 or ssh =1 ssl=0--> 35w qps ssl=1--> 20w qps and lock contention
[16 Oct 2018 14:59]
Dimitri KRAVTCHUK
Great ! ;-)) so, you should probably then change the topic of this bug to "regression on SSL" ? (or file another bug related to SSL) -- however, probably makes sense first to check which SSL library your binaries are using ? same as MySQL 5.7 ? -- and if different, then retry with the same libs first to be sure about the bug.. regarding "general" point-select performance (with ssl=0, etc.) -- I can confirm 10% regression on the HW I'm having around and where I can reproduce this. We're aware about, while from the other side we have a solution for MySQL 8.0 to bypass this issue and do even much better than 5.7 is doing here ;-)) -- when and how it'll be available I cannot say right now.. Rgds, -Dimitri
[17 Oct 2018 6:02]
Harin Vadodaria
Hi Victor, Lee, 1. Are all test performed against community editions of MySQL server? 2. In my.cnf details, I do not see anything specific to --ssl-* oprions. Am i correct in assuming that you are not setting --ssl-ca/-ssl-cert/--ssl-key variables explicitly while starting MySQL server? 3. Can you please check respective data directory for 5.7/8.0.3/8.0.12 and see if ca.pem, server-cert.pem and server-key.pem files are present. Best Regards, - Harin
[17 Oct 2018 8:18]
victor zheng
Hi Victor, Lee, 1. Are all test performed against community editions of MySQL server? yes community editions~ 2. In my.cnf details, I do not see anything specific to --ssl-* oprions. Am i correct in assuming that you are not setting --ssl-ca/-ssl-cert/--ssl-key variables explicitly while starting MySQL server? yes. not setting these variables explicityly 3. Can you please check respective data directory for 5.7/8.0.3/8.0.12 and see if ca.pem, server-cert.pem and server-key.pem files are present. for 8.0.12： the pem files are preset for 5.7: no such files Best Regards, - Harin
[18 Oct 2018 5:14]
Harin Vadodaria
Hi Victor, Thank you for the information. With 8.0.11, community edition started using OpenSSL. See: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html#mysqld-8-0-11-security Side effect of this change is that TLS certificates are auto generated at startup and MySQL server will support encrypted connections. This is why you find auto generated certificates in data directory. Comparing this to MySQL 5.7 and MySQL 8.0.3, community edition for these versions use yaSSL. In this case, certificate generation is not done during database initialization. User has to run mysql_ssl_rsa_setup manually and use generated certificates. Hence, the difference you are observing may be because of changes in defaults and perhaps not because of SSL regression. Can you please try following for 5.7/8.0.3: 1. Use OpenSSL based binaries 2. Run test with --ssl=0/--ssl=1 Please compare the results with those you found for 8.0.11. Thank you, - Harin
[26 Oct 2018 13:23]
MySQL Verification Team
Since all details of the discrepancy are explained, I am closing this bug ......
[30 Nov 2020 10:33]
chao wang
set slow_query_log = 0 to turn off slow_query_log. Then you can get more than 150M qps for Sysbench Benchmark (Point Selects). Is there something wrong for the module of slow_query_log? At 34s I set slow_query_log = 1,then the qps is form 160M to 50M. [ 10s ] thds: 512 tps: 1650241.84 qps: 1650241.84 (r/w/o: 1650241.84/0.00/0.00) lat (ms,95%): 1.03 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 512 tps: 1648605.66 qps: 1648605.56 (r/w/o: 1648605.56/0.00/0.00) lat (ms,95%): 1.03 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 512 tps: 1661280.12 qps: 1661280.02 (r/w/o: 1661280.02/0.00/0.00) lat (ms,95%): 1.03 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 512 tps: 1496199.78 qps: 1496199.98 (r/w/o: 1496199.98/0.00/0.00) lat (ms,95%): 1.25 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 512 tps: 553805.47 qps: 553805.37 (r/w/o: 553805.37/0.00/0.00) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 512 tps: 549367.49 qps: 549367.49 (r/w/o: 549367.49/0.00/0.00) lat (ms,95%): 1.79 err/s: 0.00 reconn/s: 0.00
