{"action":"created","issue":{"url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/264","repository_url":"https://api.github.com/repos/mysql-net/MySqlConnector","labels_url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/264/labels{/name}","comments_url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/264/comments","events_url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/264/events","html_url":"https://github.com/mysql-net/MySqlConnector/pull/264","id":228922747,"number":264,"title":"Reset connection asynchronously on return to pool","user":{"login":"bgrainger","id":188129,"avatar_url":"https://avatars1.githubusercontent.com/u/188129?v=3","gravatar_id":"","url":"https://api.github.com/users/bgrainger","html_url":"https://github.com/bgrainger","followers_url":"https://api.github.com/users/bgrainger/followers","following_url":"https://api.github.com/users/bgrainger/following{/other_user}","gists_url":"https://api.github.com/users/bgrainger/gists{/gist_id}","starred_url":"https://api.github.com/users/bgrainger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bgrainger/subscriptions","organizations_url":"https://api.github.com/users/bgrainger/orgs","repos_url":"https://api.github.com/users/bgrainger/repos","events_url":"https://api.github.com/users/bgrainger/events{/privacy}","received_events_url":"https://api.github.com/users/bgrainger/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2017-05-16T05:55:45Z","updated_at":"2017-05-16T13:58:25Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/mysql-net/MySqlConnector/pulls/264","html_url":"https://github.com/mysql-net/MySqlConnector/pull/264","diff_url":"https://github.com/mysql-net/MySqlConnector/pull/264.diff","patch_url":"https://github.com/mysql-net/MySqlConnector/pull/264.patch"},"body":"This PR implements asynchronous resetting of the connection when it's returned to the pool. This implements #178 and obsoletes PR #179.\r\n\r\n## Important Note\r\n\r\nThe major side-effect of this PR (apart from increased performance) is that failures in `ResetConnectionAsync` will be silently ignored. This will suppress #169 and #208; the only side-effect will (ironically) be decreased performance if connections are getting destroyed when they're closed, instead of returned to the pool. I can't think of a good way of logging/diagnosing this (unless there's a logging/tracing framework in `netstandard1.3`?). (We could add a static `MySqlConnection.OnError` event handler or similar that clients could attach a logging callback to, but it would be nice to have something less manual and non-standard.)\r\n\r\n## Benchmarks\r\n\r\n### Single Threaded\r\n\r\nThe primary speed up is in single-threaded (low-concurrency) operation. Here are results from \"Benchmark\" when connecting to a LAN DB server 0.4ms away:\r\n\r\n#### Before\r\n\r\nMethod | Mean | StdDev | StdErr | Min | Q1 | Median | Q3 | Max | Op/s | Allocated |\r\n---------------------- |------------ |----------- |---------- |------------ |------------ |------------ |------------ |------------ |-------- |---------- |\r\nOpenFromPoolNewAsync | 800.6765 us | 21.0694 us | 5.4401 us | 762.2547 us | 782.9775 us | 799.6714 us | 810.2822 us | 836.4873 us | 1248.94 | 16.97 kB |\r\nOpenFromPoolNewSync | 722.4660 us | 24.6377 us | 6.3614 us | 671.8728 us | 706.7121 us | 724.8340 us | 736.4763 us | 765.1436 us | 1384.15 | 4.45 kB |\r\n\r\n#### After\r\n\r\nMethod | Mean | StdErr | StdDev | Min | Q1 | Median | Q3 | Max | Op/s | Allocated |\r\n---------------------- |------------ |---------- |----------- |------------ |------------ |------------ |------------ |------------ |-------- |---------- |\r\nOpenFromPoolNewAsync | 346.8272 us | 3.7775 us | 14.1340 us | 328.4828 us | 338.2204 us | 345.1184 us | 349.9636 us | 382.3111 us | 2883.28 | 16.9 kB |\r\nOpenFromPoolNewSync | 308.6513 us | 3.0141 us | 11.6738 us | 296.0878 us | 299.0418 us | 302.9528 us | 317.7077 us | 332.5841 us | 3239.9 | 12.24 kB |\r\n\r\n### Concurrent\r\n\r\nI ran the `MySqlConnector.Performance` tests under two configurations: a maximum pool size of 500 (so blocking on the connection pool should happen) and a maximum pool size of 2000 (should be no blocking). All tests were run against a local `mysqld` server. The command was `.\\stress.ps1 N 30s async`.\r\n\r\n##### Summary\r\n\r\nWhen | MaxPoolSize | Requests | Mean | 50th | 95th | 99th | Max\r\n--- | --- | --- | --- | --- | --- | --- | ---\r\nBefore | 500 | 1000 | 1.380799ms | 1.0042ms | 2.7962ms | 2.9843ms | 27.9794ms\r\nAfter | 500 | 1000 | 1.106233ms | 997.7µs | 2.5788ms | 2.9801ms | 32.0018ms\r\nBefore | 500 | 2000 | 2.763622ms | 1.0009ms | 6.8465ms | 32.5073ms | 132.5081ms\r\nAfter | 500 | 2000 | 2.050698ms | 999.9µs | 5.919ms | 18.5076ms | 87.9365ms\r\nBefore | 500 | 3000 | 4.332318ms | 1.0009ms | 17.4993ms | 58.9991ms | 206.0083ms\r\nAfter | 500 | 3000 | 2.534541ms | 1.0003ms | 7.0002ms | 20.0056ms | 122.0119ms\r\nBefore | 2000 | 1000 | 1.371715ms | 1.0038ms | 2.785ms | 2.9723ms | 44.5096ms\r\nAfter | 2000 | 1000 | 1.143765ms | 998.9µs | 2.6757ms | 2.9806ms | 52.8817ms\r\nBefore | 2000 | 2000 | 2.781073ms | 1.0012ms | 6.5657ms | 31.0009ms | 189.8881ms\r\nAfter | 2000 | 2000 | 2.131828ms | 1.0002ms | 5.9785ms | 20.0009ms | 134.6819ms\r\nBefore | 2000 | 3000 | 4.856396ms | 1.0014ms | 23.0035ms | 60.502ms | 162.5065ms\r\nAfter | 2000 | 3000 | 3.554666ms | 1.0012ms | 11.9998ms | 41.0099ms | 137.0207ms\r\n\r\n#### Before\r\n\r\n##### Maximum Pool Size = 500\r\n\r\n```\r\nRate = 1000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            30000, 1000.03\r\nDuration      [total, attack, wait]    30.0017319s, 29.999s, 2.7319ms\r\nLatencies     [mean, 50, 95, 99, max]  1.380799ms, 1.0042ms, 2.7962ms, 2.9843ms, 27.9794ms\r\nBytes In      [total, mean]            346308890, 11543.63\r\nBytes Out     [total, mean]            11122500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:30000\r\nError Set:\r\n\r\nRate = 2000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            60000, 2000.03\r\nDuration      [total, attack, wait]    30.0024226s, 29.9995s, 2.9226ms\r\nLatencies     [mean, 50, 95, 99, max]  2.763622ms, 1.0009ms, 6.8465ms, 32.5073ms, 132.5081ms\r\nBytes In      [total, mean]            693267568, 11554.46\r\nBytes Out     [total, mean]            22245000, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:60000\r\nError Set:\r\n\r\nRate = 3000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            90000, 3000.00\r\nDuration      [total, attack, wait]    30.001002s, 30.0000023s, 999.7µs\r\nLatencies     [mean, 50, 95, 99, max]  4.332318ms, 1.0009ms, 17.4993ms, 58.9991ms, 206.0083ms\r\nBytes In      [total, mean]            1040017500, 11555.75\r\nBytes Out     [total, mean]            33367500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:90000\r\nError Set:\r\n```\r\n\r\n##### Maximum Pool Size = 2000\r\n\r\n```\r\nRate = 1000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            30000, 1000.00\r\nDuration      [total, attack, wait]    29.9999892s, 29.9999892s, 0s\r\nLatencies     [mean, 50, 95, 99, max]  1.371715ms, 1.0038ms, 2.785ms, 2.9723ms, 44.5096ms\r\nBytes In      [total, mean]            346595080, 11553.17\r\nBytes Out     [total, mean]            11122500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:30000\r\nError Set:\r\n\r\nRate = 2000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            60000, 2000.03\r\nDuration      [total, attack, wait]    30.0041588s, 29.9995s, 4.6588ms\r\nLatencies     [mean, 50, 95, 99, max]  2.781073ms, 1.0012ms, 6.5657ms, 31.0009ms, 189.8881ms\r\nBytes In      [total, mean]            693345000, 11555.75\r\nBytes Out     [total, mean]            22245000, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:60000\r\nError Set:\r\n\r\nRate = 3000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            90000, 3000.01\r\nDuration      [total, attack, wait]    30.0048949s, 29.9998982s, 4.9967ms\r\nLatencies     [mean, 50, 95, 99, max]  4.856396ms, 1.0014ms, 23.0035ms, 60.502ms, 162.5065ms\r\nBytes In      [total, mean]            1040017500, 11555.75\r\nBytes Out     [total, mean]            33367500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:90000\r\nError Set:\r\n```\r\n\r\n#### After\r\n\r\n##### Maximum Pool Size = 500\r\n\r\n```\r\nRate = 1000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            30000, 1000.01\r\nDuration      [total, attack, wait]    29.9995624s, 29.9995624s, 0s\r\nLatencies     [mean, 50, 95, 99, max]  1.106233ms, 997.7µs, 2.5788ms, 2.9801ms, 32.0018ms\r\nBytes In      [total, mean]            346307402, 11543.58\r\nBytes Out     [total, mean]            11122500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:30000\r\nError Set:\r\n\r\nRate = 2000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            60000, 2000.00\r\nDuration      [total, attack, wait]    30.0019398s, 29.9999408s, 1.999ms\r\nLatencies     [mean, 50, 95, 99, max]  2.050698ms, 999.9µs, 5.919ms, 18.5076ms, 87.9365ms\r\nBytes In      [total, mean]            693267566, 11554.46\r\nBytes Out     [total, mean]            22245000, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:60000\r\nError Set:\r\n\r\nRate = 3000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            90000, 3000.04\r\nDuration      [total, attack, wait]    30.001378s, 29.999636667s, 1.741333ms\r\nLatencies     [mean, 50, 95, 99, max]  2.534541ms, 1.0003ms, 7.0002ms, 20.0056ms, 122.0119ms\r\nBytes In      [total, mean]            1040017500, 11555.75\r\nBytes Out     [total, mean]            33367500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:90000\r\nError Set:\r\n```\r\n\r\n##### Maximum Pool Size = 2000\r\n\r\n```\r\nRate = 1000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            30000, 1000.01\r\nDuration      [total, attack, wait]    29.9998073s, 29.9998073s, 0s\r\nLatencies     [mean, 50, 95, 99, max]  1.143765ms, 998.9µs, 2.6757ms, 2.9806ms, 52.8817ms\r\nBytes In      [total, mean]            346304431, 11543.48\r\nBytes Out     [total, mean]            11122500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:30000\r\nError Set:\r\n\r\nRate = 2000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            60000, 1999.95\r\nDuration      [total, attack, wait]    30.0007196s, 30.0007196s, 0s\r\nLatencies     [mean, 50, 95, 99, max]  2.131828ms, 1.0002ms, 5.9785ms, 20.0009ms, 134.6819ms\r\nBytes In      [total, mean]            693267576, 11554.46\r\nBytes Out     [total, mean]            22245000, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:60000\r\nError Set:\r\n\r\nRate = 3000 RPS\r\nDuration = 30s\r\nTargets = targets-async.txt\r\nRequests      [total, rate]            90000, 3000.01\r\nDuration      [total, attack, wait]    30.0033768s, 29.9998718s, 3.505ms\r\nLatencies     [mean, 50, 95, 99, max]  3.554666ms, 1.0012ms, 11.9998ms, 41.0099ms, 137.0207ms\r\nBytes In      [total, mean]            1040017500, 11555.75\r\nBytes Out     [total, mean]            33367500, 370.75\r\nSuccess       [ratio]                  100.00%\r\nStatus Codes  [code:count]             200:90000\r\nError Set:\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/comments/301790863","html_url":"https://github.com/mysql-net/MySqlConnector/pull/264#issuecomment-301790863","issue_url":"https://api.github.com/repos/mysql-net/MySqlConnector/issues/264","id":301790863,"user":{"login":"bgrainger","id":188129,"avatar_url":"https://avatars1.githubusercontent.com/u/188129?v=3","gravatar_id":"","url":"https://api.github.com/users/bgrainger","html_url":"https://github.com/bgrainger","followers_url":"https://api.github.com/users/bgrainger/followers","following_url":"https://api.github.com/users/bgrainger/following{/other_user}","gists_url":"https://api.github.com/users/bgrainger/gists{/gist_id}","starred_url":"https://api.github.com/users/bgrainger/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bgrainger/subscriptions","organizations_url":"https://api.github.com/users/bgrainger/orgs","repos_url":"https://api.github.com/users/bgrainger/repos","events_url":"https://api.github.com/users/bgrainger/events{/privacy}","received_events_url":"https://api.github.com/users/bgrainger/received_events","type":"User","site_admin":false},"created_at":"2017-05-16T13:58:25Z","updated_at":"2017-05-16T13:58:25Z","body":"Sigh, I assume this is the \"classic\" problem of async I/O (or any queued threadpool work) being backlogged when the threadpool is flooded with blocking requests.\r\n\r\nI'm going to close this for now; it might be something to revisit in `netstandard2.0` when we could use `Thread` to create a dedicated connection resetting thread. In the meantime, it could be worth tackling #258 and remove `TryPingAsync` to try to reduce connection opening latency."}}
{"actor":{"display_login":"bgrainger"}}