Utilising ProxySQL for connection pooling in PHP

proxysql是一个非常强大的工具,具有扩展功能。本演示将演示如何使用proxysql获得功能(无缝数据库后端交换机)并纠正问题(应用程序缺少连接池)。本演示将是一个关于如何使用proxysql进行连接池、数据库故障转移和负载平衡的实际研究,即我们的(第三方)PHP应用程序和我们的主MySQL集群之间的通信。此外,我们将使用Percona监视和管理(PMM)显示监视和统计。

展开查看详情

1. Utilising ProxySQL for connection pooling in PHP Tibor Korocz Architect Webinar 14.08.2018 1 © 2016 Percona

2.Who is using PHP? 2 © 2016 Percona

3.What is our Problem? Our setup and how we broke it... 3 © 2016 Percona

4.The Environment • ~12k hardware clients • ~4k “software” clients (users) • ~6k qps • ~3k prepared statement calls/sec • ~500 transactions/sec • ~400 tcp open/sec • 3:rd party application, written in PHP, running on seven web servers
 4 © 2018 Percona

5.TCP States 5 © 2018 Percona

6.TCP States ● After Closing they stay in TIME_WAIT state. ● TIME_WAIT timeout is 1-2 minutes (the connections stay in that state.) ● cat /proc/sys/net/ipv4/ip_local_port_range - 32768 60999 ○ That is around ~ 28231 local ports. It sounds a lot ● Let’s say every connections stay there 1 minute. ○ 28231 / 60 ~ 470 , you will run out local ports if you open more than 470 connections per second. ● When you have 12k hardware clients sending data in every seconds that is not that lot. ○ PHP Warning: mysqli::mysqli(): (HY000/2002): Cannot assign requested address in / root/phptest/test_credentials.php on line 33 6 © 2018 Percona

7.The problem(s) ● No connection pooling in PHP, leading to ● Firewall port exhaustion ● Source port exhaustion on web-servers ● High number of tcp-connection requests ● High threadpool activity ● Ineffective use of prepared statements ● 1 prepare/bind/execute/close ● small number of unique statements 7 © 2018 Percona

8.The problem(s) ● “Closed source” - proprietary PHP ● Each page call creates one to many database sessions ● Needs to ‘co-exist’ with other critical application servers - no way of setting up an isolated environment for this application ● Business demands 24/7 uptime ● Hardware clients sends status updates only once - no resends 8 © 2018 Percona

9.How can we montior that? 9 © 2016 Percona

10.We can use PMM ● The metrics are collected. ● But they are not visualized. ● We need a custom dashboards for TCP connections. ○ rate(node_netstat_Tcp_ActiveOpens{instance=~"$host"}[$interval]) or irate(node_netstat_Tcp_ActiveOpens{instance=~"$host"}[5m]) 10 © 2018 Percona

11.We can use PMM 11 © 2018 Percona

12.What is connection pooling? 12 © 2016 Percona

13.Connection Pooling ● Opening and closing a connection for each request is costly and wastes resources. ● Connection pooling is a collection of the connections. ● The application can reuse the same connection again. ● This reduces the overhead associated with connecting to the database to service individual requests. 13 © 2018 Percona

14.Connection Pooling 14 © 2018 Percona

15.Connection Pooling ● Java has connection pooling. ● Python has connection pooling. ● But PHP does not have a proper connection pooling. ○ Without connection pooling the chance to get contentions issues is significantly higher. It does not matter what kind of application do you use. 15 © 2018 Percona

16.Possible solutions 16 © 2016 Percona

17.Possible Solutions ● rewrite PHP/port application ○ supplier unwilling to take the development cost ○ in-house development team unwilling to take maintenance responsibility ○ operations team unwilling to maintain a separate “branch” for patching ● ip-tables ○ NAT complexifies the environment ○ requires different configuration across portals ○ adds to firewall strain ● Tune kernel parameters ● Unix Sockets 17 © 2018 Percona

18.Possible Solutions - Unix Sockets ● Unix domain socket or IPC socket (inter-process communication socket). ● Exchanging data between processes executing on the same host operating system. ● Rather than using an underlying network protocol, all communication occurs entirely within the operating system kernel. ● Uses the file system as their address name space. ● Two processes can communicate by opening the same socket. 18 © 2018 Percona

19.How ProxySQL can help us? 19 © 2016 Percona

20.ProxySQL Setup ● Running locally on all application server. ● Listening on socket at /tmp/proxysql.sock ● The application connects through socket to ProxySQL. 20 © 2018 Percona

21.Relevant features ● seamless integration ○ reconfigure all portals to connect to unix-socket ○ proxySQL “proxying” connections from unix-socket to backend DB ● connection pooling ○ application closes connection to proxySQL, not to DB ○ proxySQL reuses db-connections for the next connect ● prepared statement reuse ○ proxySQL filters out “statement close”/”prepare statement” when possible 21 © 2018 Percona

22.The new environment ● ~12k hardware clients ● ~4k “software” clients (users) ● ~6k qps ● ~3k prepared statement calls/sec ○ ~3k “execute statement”/sec ○ ~50 “prepare statement”/sec ● ~500 transactions/sec ● ~10 tcp open/sec ● 3:rd party application, written in PHP, running on seven web servers, with locally installed proxySQL ● 1 central proxySQL for backend switch 22 © 2018 Percona

23.Demo Time 23 © 2016 Percona

24.The test environment 24 © 2018 Percona

25.Q&A 25 © 2016 Percona

26.Thanks for your attention! 26 © 2016 Percona

27.Champions of Unbiased Open Source DATABASE Database Performance Matters Database Solutions PERFORMANCE MATTERS 27