MySQL Bugs: #70018: PFS Overhead on frequent connect/disconnect

Bug #70018	PFS Overhead on frequent connect/disconnect
Submitted:	13 Aug 2013 19:42	Modified:	7 Oct 2013 18:18
Reporter:	Dimitri KRAVTCHUK	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S5 (Performance)
Version:	5.6 / 5.7	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
The issue was reported by Yoshinori: http://yoshinorimatsunobu.blogspot.com/2013/08/another-reason-to-disable-performance.html

He obtained on his test case 30% QPS regression when PFS was switched ON -vs- switched OFF.

I was able to reproduce the problem and observe a QPS regression on latest MySQL 5.6 and 5.7 versions. My first analyzes are confirming there is a bug, because the regression is happening even if none of instrumentation is enabled (means it's not related to instrumentation code path, but rather PFS activity on connection close)..

How to repeat:
The most simple is to reproduce the problem with Sysbench RO Point-Selects workload:

1. prepare a table with 1M or 10M rows

2. then start a point-select test with 64 concurrent users (for ex.):

sysbench --num-threads=64 --test=oltp --oltp-table-size=1000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_10M \
        --max-requests=0 --max-time=180 --mysql-socket=/path/to/mysql.sock \
        --mysql-user=root --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on --oltp-reconnect-mode=query \
        --oltp-connect-delay=0 run

Test it with performance_schema=OFF.

Then with performance_schema=ON and all PFS instrumentation disabled.

What is curious as well that with performance_schema=ON we're using much less CPU time (so, in fact the regression is not coming from a simply increased code path, but rather from some kind of wait events involved by PFS).. More CPU cores we have on a server - bigger is slow down: 14% on 12cores, 30% on 32cores.

Suggested fix:
none.

Verified, reproduced.

More results on PS and connection create performance:
http://mysqlha.blogspot.com/2013/09/concurrent-connection-create.html
http://mysqlha.blogspot.com/2013/09/single-threaded-connect-performance.html

Noted in 5.6.15, 5.7.3 changelogs.

Performance Schema instrumentation overhead was reduced
for frequent connect/disconnect operations.

5.6$ bzr log -r 5489
------------------------------------------------------------
revno: 5489
committer: Marc Alff <marc.alff@oracle.com>
branch nick: mysql-5.6-bug17310878
timestamp: Fri 2013-10-04 22:21:58 +0200
message:
  Bug#17310878 PFS OVERHEAD ON FREQUENT CONNECT/DISCONNECT
  
  This fix is a back port from 5.7 to 5.6
  
  This fix is a performance improvement.
  
  The issue was that the call to PSI_THREAD_CALL(delete_current_thread)(),
  which is executed each time a thread disconnects,
  was placed inside a critical section involving LOCK_thread_count.
  
  The fix is to perform the same call sooner,
  before entering the LOCK_thread_count critical section.
  
  Serializing all calls to pfs_delete_current_thread_v1() was the major
  cause of performance overhead, for frequent connect/disconnect.