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:
Category:MySQL Server: Performance Schema Severity:S5 (Performance)
Version:5.6 / 5.7 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[13 Aug 2013 19:42] Dimitri Kravtchuk
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:
[14 Aug 2013 9:33] Dimitri Kravtchuk
Verified, reproduced.
[25 Sep 2013 17:03] Mark Callaghan
More results on PS and connection create performance:
[7 Oct 2013 18:18] Paul Dubois
Noted in 5.6.15, 5.7.3 changelogs.

Performance Schema instrumentation overhead was reduced
for frequent connect/disconnect operations.
[4 Dec 2013 11:48] Laurynas Biveinis
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
  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.