process stuck in proxysql when stress testing with apachbench

[xman13a]

I'm doing stress testing on my website through proxysql with Percona XtrDB Cluster (3 nodes). But If I connected my website to the Percona XtrDB Cluter directly. Everything worked fine. I found a strange problem that when I did stress testing on proxysql, there were always several processes stuck in proxysql . I could use " show processlist" to see the stuck processes like the following:

mysql> show processlist; +-----------+---------------+--------------------+-----------+---------+---------+------+ | SessionID | user | db | hostgroup | command | time_ms | info | +-----------+---------------+--------------------+-----------+---------+---------+------+ | 21012 | drupal_master | drupal8_db | -1 | Sleep | 1462397 | NULL | | 21098 | web_R | information_schema | -1 | Sleep | 1463013 | NULL | | 24528 | drupal_master | drupal8_db | -1 | Sleep | 833890 | NULL | | 24610 | web_R | information_schema | -1 | Sleep | 833398 | NULL | | 26429 | drupal_master | drupal8_db | -1 | Sleep | 795097 | NULL | | 26511 | web_R | information_schema | -1 | Sleep | 794665 | NULL | | 27185 | drupal_master | drupal8_db | -1 | Sleep | 791391 | NULL | | 27255 | web_R | information_schema | -1 | Sleep | 791398 | NULL | | 27524 | drupal_master | drupal8_db | -1 | Sleep | 764359 | NULL | | 27584 | web_R | information_schema | -1 | Sleep | 764247 | NULL | | 27717 | drupal_master | drupal8_db | -1 | Sleep | 763547 | NULL | | 27779 | web_R | information_schema | -1 | Sleep | 764289 | NULL | | 27859 | drupal_master | drupal8_db | -1 | Sleep | 762568 | NULL | | 27921 | web_R | information_schema | -1 | Sleep | 762965 | NULL | | 28828 | drupal_master | drupal8_db | -1 | Sleep | 757692 | NULL | | 28907 | web_R | information_schema | -1 | Sleep | 757523 | NULL | | 29296 | drupal_master | drupal8_db | -1 | Sleep | 755712 | NULL | | 29366 | web_R | information_schema | -1 | Sleep | 757070 | NULL | | 29392 | drupal_master | drupal8_db | -1 | Sleep | 755093 | NULL | | 29458 | web_R | information_schema | -1 | Sleep | 756701 | NULL | | 30411 | drupal_master | drupal8_db | -1 | Sleep | 722259 | NULL | | 30493 | web_R | information_schema | -1 | Sleep | 721999 | NULL | | 31602 | drupal_master | drupal8_db | -1 | Sleep | 563889 | NULL | | 31620 | drupal_master | drupal8_db | -1 | Sleep | 565449 | NULL | | 31668 | web_R | information_schema | -1 | Sleep | 564048 | NULL | | 31673 | web_R | information_schema | -1 | Sleep | 564127 | NULL | | 32336 | drupal_master | drupal8_db | -1 | Sleep | 561533 | NULL | | 32418 | web_R | information_schema | -1 | Sleep | 559816 | NULL | +-----------+---------------+--------------------+-----------+---------+---------+------+ 28 rows in set (0.01 sec)

The hostgroup have always been "-1". and I got a error message at my client side when I execute appachbench (ab) like the following:

ab -v 5 -s 2000 -n 1000 -c 50 "http://192.168.5.20/system/search?lang=en&family=storage:6&filter=cputype&filtervalue=3&cputype=3&qatest=1"

WARNING: Response code not 2xx (504) LOG: header received: HTTP/1.1 504 Gateway Time-out Server: nginx/1.10.2 Date: Sun, 25 Aug 2019 12:50:09 GMT Content-Type: text/html Content-Length: 183 Connection: close

504 Gateway Time-out

---

nginx/1.10.2

Server Software: nginx/1.10.2 Server Hostname: 192.168.5.20 Server Port: 80 Document Length: 103956 bytes

Concurrency Level: 50 Time taken for tests: 68.989 seconds Complete requests: 1000 Failed requests: 3 (Connect: 0, Receive: 0, Length: 3, Exceptions: 0) Write errors: 0 Non-2xx responses: 3 Total transferred: 104007063 bytes HTML transferred: 103644681 bytes Requests per second: 14.50 [#/sec] (mean) Time per request: 3449.430 [ms] (mean) Time per request: 68.989 [ms] (mean, across all concurrent requests) Transfer rate: 1472.26 [Kbytes/sec] received

Connection Times (ms) min mean[+/-sd] median max Connect: 0 5 63.3 1 1006 Processing: 247 658 3261.0 455 60063 Waiting: 241 648 3261.4 448 60062 Total: 248 663 3261.3 457 60065

Percentage of the requests served within a certain time (ms) 50% 457 66% 483 75% 503 80% 516 90% 559 95% 671 98% 1177 99% 1476 100% 60065 (longest request)

Is It a bug? I don't know. I like proxysql ,so I hope I can fix it by some magic parameters. but I can't find any parameter of proxysql to resolve the issue.

My OS : CentOS Linux release 7.6.1810 (Core) My proxysql: proxysql2-2.0.6-1.1.el7.x86_64 My PXC :Percona XtraDB Cluster (GPL), Release rel29, Revision 03540a3, WSREP version 31.37, wsrep_31.37 my proxysql.cnf

datadir="/var/lib/proxysql" errorlog="/var/lib/proxysql/proxysql.log"

admin_variables= { admin_credentials="admin:s2k8cwtv;proxysql:s2k8cwtv" mysql_ifaces="0.0.0.0:6032" }

mysql_variables= { threads=8 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:3306" default_schema="information_schema" wait_timeout=1800000 stacksize=1048576 server_version="5.7.26" connect_timeout_server=3000 monitor_username="proxysql_monitor" monitor_password="12345" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 }

defines all the MySQL servers

mysql_servers = (

)

defines all the MySQL users

mysql_users: ( { username = "proxysql_user" password = "proxysql" default_hostgroup = 10 active = 1 } )

insert into mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,9999,1,1,0,100); INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES (10,'192.168.5.181',3306,100,500); INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES (10,'192.168.5.182',3306,100,500); INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES (10,'192.168.5.183',3306,100,500); insert into mysql_query_rules (rule_id,proxy_port,destination_hostgroup,active,retries,match_digest,apply,cache_ttl) values(1,3306,10,1,3,'^SELECT.FOR UPDATE',1,NULL); insert into mysql_query_rules (rule_id,proxy_port,destination_hostgroup,active,retries,match_digest,apply,cache_ttl) values(2,3306,20,1,3,'^SELECT.',1,120000);

[xman13a]

My issue is the same as https://github.com/sysown/proxysql/issues/2048

I use Drupal and php-fpm

[renecannao]

To further debugging this issue, we need a reproducible test case. Thanks