{"action":"created","issue":{"url":"https://api.github.com/repos/sysown/proxysql/issues/638","repository_url":"https://api.github.com/repos/sysown/proxysql","labels_url":"https://api.github.com/repos/sysown/proxysql/issues/638/labels{/name}","comments_url":"https://api.github.com/repos/sysown/proxysql/issues/638/comments","events_url":"https://api.github.com/repos/sysown/proxysql/issues/638/events","html_url":"https://github.com/sysown/proxysql/issues/638","id":168976877,"number":638,"title":"Failed to connect to backend server when the thead is above 1100","user":{"login":"govindarajan","id":5063694,"avatar_url":"https://avatars.githubusercontent.com/u/5063694?v=3","gravatar_id":"","url":"https://api.github.com/users/govindarajan","html_url":"https://github.com/govindarajan","followers_url":"https://api.github.com/users/govindarajan/followers","following_url":"https://api.github.com/users/govindarajan/following{/other_user}","gists_url":"https://api.github.com/users/govindarajan/gists{/gist_id}","starred_url":"https://api.github.com/users/govindarajan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/govindarajan/subscriptions","organizations_url":"https://api.github.com/users/govindarajan/orgs","repos_url":"https://api.github.com/users/govindarajan/repos","events_url":"https://api.github.com/users/govindarajan/events{/privacy}","received_events_url":"https://api.github.com/users/govindarajan/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":2,"created_at":"2016-08-02T20:11:08Z","updated_at":"2016-08-03T07:21:49Z","closed_at":null,"body":"Hi, \r\nI was doing some kind of load testing before we introduce proxy sql in to production env. When we test proxy sql with 1200 threads, it is started failing, It was not able to make connection properly. I have max connection for that user set as 10K and in that server configuration max-connection is set as 5K. Please find the sysbench log below.\r\n\r\n_sysbench 0.5:  multi-threaded system evaluation benchmark\r\n\r\nRunning the test with following options:\r\nNumber of threads: 1200\r\nReport intermediate results every 1 second(s)\r\nRandom number generator seed is 0 and will be ignored\r\n\r\n\r\nThreads started!\r\n\r\n[   1s] threads: 1200, tps: 0.00, reads: 9945.33, writes: 180.97, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\nALERT: mysql_drv_query() for query 'SELECT c FROM sbtest1 WHERE id=498669' failed: 1317 Query execution was interrupted\r\n[   2s] threads: 1200, tps: 606.99, reads: 4781.92, writes: 2697.95, response time: 1943.54ms (95%), errors: 0.00, reconnects:  0.00\r\n[   3s] threads: 1200, tps: 508.01, reads: 882.02, writes: 1581.04, response time: 2241.16ms (95%), errors: 0.00, reconnects:  0.00\r\n[   4s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   5s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   6s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   7s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   8s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   9s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  10s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 43 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n[  11s] threads: 1200, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n(last message repeated 4 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 7 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\n(last message repeated 2 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 13 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 6 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 3 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\n(last message repeated 1 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms\r\n(last message repeated 12 times)\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10001ms\r\nALERT: mysql_drv_query() for query 'BEGIN' failed: 1045 Max connect timeout reached while reaching hostgroup 0 after 10000ms_\r\n\r\nAny other configuration that i need to tweek?\r\nPlease note that when i test sysbench directly to mysql (without proxysql) It was able to pass the test. \r\nAm I doing anything wrong or is there any issue with my configuration/testing?"},"comment":{"url":"https://api.github.com/repos/sysown/proxysql/issues/comments/237162373","html_url":"https://github.com/sysown/proxysql/issues/638#issuecomment-237162373","issue_url":"https://api.github.com/repos/sysown/proxysql/issues/638","id":237162373,"user":{"login":"govindarajan","id":5063694,"avatar_url":"https://avatars.githubusercontent.com/u/5063694?v=3","gravatar_id":"","url":"https://api.github.com/users/govindarajan","html_url":"https://github.com/govindarajan","followers_url":"https://api.github.com/users/govindarajan/followers","following_url":"https://api.github.com/users/govindarajan/following{/other_user}","gists_url":"https://api.github.com/users/govindarajan/gists{/gist_id}","starred_url":"https://api.github.com/users/govindarajan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/govindarajan/subscriptions","organizations_url":"https://api.github.com/users/govindarajan/orgs","repos_url":"https://api.github.com/users/govindarajan/repos","events_url":"https://api.github.com/users/govindarajan/events{/privacy}","received_events_url":"https://api.github.com/users/govindarajan/received_events","type":"User","site_admin":false},"created_at":"2016-08-03T07:21:49Z","updated_at":"2016-08-03T07:21:49Z","body":"Hi, \r\nThanks for the reply.\r\n\r\nI have increased server_connect_timeout in ProxySQL. But no help. Still it was not able to make connections beyond 1200 threads. Also I have encountered one more issue while I was doing load test.\r\n\r\nWhen I kill the sysbench in the middle, proxysql is getting hung. It is not cleaning up the connection to the backend server. connection was still open to backend server and I am not able to connect to proxy sql now.\r\n\r\nPlease find the log below.\r\n\r\n**Started with 1150 threads:**\r\nsysbench 0.5:  multi-threaded system evaluation benchmark\r\n\r\nRunning the test with following options:\r\nNumber of threads: 1150\r\nReport intermediate results every 1 second(s)\r\nRandom number generator seed is 0 and will be ignored\r\n\r\n\r\nThreads started!\r\n\r\n[   1s] threads: 1150, tps: 0.00, reads: 10396.86, writes: 95.97, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   2s] threads: 1150, tps: 560.08, reads: 6487.92, writes: 2729.39, response time: 1948.20ms (95%), errors: 0.00, reconnects:  0.00\r\n[   3s] threads: 1150, tps: 534.00, reads: 8532.01, writes: 2012.00, response time: 2853.54ms (95%), errors: 0.00, reconnects:  0.00\r\nALERT: mysql_drv_query() for query 'SELECT c FROM sbtest1 WHERE id=503008' failed: 1317 Query execution was interrupted\r\nALERT: mysql_drv_query() for query 'SELECT c FROM sbtest1 WHERE id=498941' failed: 1317 Query execution was interrupted\r\n[   4s] threads: 1150, tps: 767.00, reads: 5485.99, writes: 3069.00, response time: 3579.28ms (95%), errors: 0.00, reconnects:  0.00\r\n[   5s] threads: 1150, tps: 384.99, reads: 542.99, writes: 1077.98, response time: 2949.95ms (95%), errors: 0.00, reconnects:  0.00\r\n[   6s] threads: 1150, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   7s] threads: 1150, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n**I pressed Ctrl+C after 7 seconds.**\r\n\r\n**Again I started with 1 thread:**\r\nsysbench 0.5:  multi-threaded system evaluation benchmark\r\n\r\nRunning the test with following options:\r\nNumber of threads: 1\r\nReport intermediate results every 1 second(s)\r\nRandom number generator seed is 0 and will be ignored\r\n\r\n\r\nThreads started!\r\n\r\n[   1s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   2s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   3s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   4s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   5s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   6s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   7s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   8s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[   9s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  10s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  11s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  12s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  13s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  14s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  15s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  16s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  17s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  18s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  19s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  20s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  21s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  22s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  23s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  24s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  25s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  26s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  27s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  28s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  29s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  30s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  31s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  32s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  33s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  34s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  35s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n[  36s] threads: 1, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00\r\n\r\nIt is not executing any queries. number of connection in the backend server is connstant.(1083 connections)\r\n\r\n$ps aux | grep proxy\r\nroot      8780  0.0  0.0  56036  3200 ?        S    01:22   0:00 proxysql -c /etc/proxysql.cnf -D /var/run/proxysql\r\nroot      8781  1.4  2.1 427876 82252 ?        Sl   01:22   9:53 proxysql -c /etc/proxysql.cnf -D /var/run/proxysql\r\n\r\nProcess is running. But it is not processing any more connection. I tried to connect using mysql client and that also waiting infinitely. Not getting connected to proxysql.\r\n"}}
{"actor":{"display_login":"govindarajan"}}