Bug #74224 On Windows, thread pool plugin + Zone Alarm firewall is unacceptably slow
Submitted: 4 Oct 2014 13:04 Modified: 5 Oct 2014 9:33
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S2 (Serious)
Version:5.6.20 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[4 Oct 2014 13:04] Shane Bester 
Description:
I'm filing this as an informational bug for now.

Queries per second with Thread pool plugin severely degrades when more than one thread is running on CPU. 

Here are query times from my system that is affected:

THere are 8 logical processors on this system (4c, ht).  If I throttle mysqld.exe to use a single core, the real time for 6 threads is 2mins.

threads, real time (TP),  real time (no TP)
1,       0m13.017s,       0m12.020s         
2,       0m48.021s,       0m15.017s
3,       2m15.093s,       0m21.019s
4,       3m15.139s,       0m31.022s
5,       4m27.218s,       0m48.038s
6,       5m45.284s,       0m59.040s

How to repeat:
Benchmark how long it takes to execute 100K simple queries like "do 1;" with/without thread pool enabled,  and at concurrency of 1, 2, 3, 4, 5, 6 threads.
[4 Oct 2014 13:23] MySQL Verification Team 
xperf, wpa showing a likely cause of the problem

Attachment: bug74224_stack_trace_windows7_5.6.21_thread_pool.jpg (image/jpeg, text), 356.56 KiB.
[4 Oct 2014 13:29] MySQL Verification Team 
Okay, so I uninstalled ZoneAlarm and the shocking performance is fixed.
Now with shared memory connections I can 100K QPS with TP enabled, but 300K QPS with TP disabled.  So, there is still a room for improvement.  I'll profile the kernel again.
[4 Oct 2014 17:06] MySQL Verification Team 
On 5.6.21, a third of time is spent in WSAPoll().  Much tcp/ip driver activity even when using shared memory connections.

On testing 5.7.6 now with 127.0.0.1 tcp/ip connection, we have
also about a third of time in WSAPoll.

Profiling using:
xperf -on SysProf -f shd.etl -Pids 2700 -stackwalk profile  -BufferSize 1024 -MaxBuffers 1024 -MaxFile 1024 -FileMode Circular

<run tests>

xperf -d Xperftp1.etl
wpa Xperftp1.etl