What's new in ProxySQL 2.0? (Updated to version 2.0.9)

This presentation was given by Rene Cannao, ProxySQL author and CEO, at Open Source Database Best Practices event in London. The event took place on December 16, 2019.

The talk abstract is as follows:

"ProxySQL, the high performance, high availability, protocol-aware proxy for MySQL is now GA in version 2.0.8.

In this talk we'll discuss ProxySQL's new features from the very recent 2.0 releases, including the improved support for AWS Aurora and Galera Cluster. Most importantly, we'll tackle the practical side of things and explain MySQL users how the newly developed ProxySQL features can help them with their daily tasks and workloads".

Transcript

1. Whats new in ProxySQL 2.0 by Rene Cannao
2. 2 ProxySQL LLC We provide services to help build, support as well as improve the performance & reliability of your Cloud-Based or On-Premise MySQL infrastructure: • ProxySQL Development • Remote DBRE Consulting • ProxySQL Support Services
3. 3 Companies using ProxySQL
4. 4 We’re hiring!!! • Experience coding in C/C++? • MySQL Database Administration / Development? • DevOps / Automation? • Working remotely?
5. 5 Speaker Rene Cannao • Founder of ProxySQL LLC • Author of ProxySQL • MySQL DBA
6. 6 Talk Overview • Supported OSes & Packaging • Query Cache Tunables • GTID Causal Reads • Native Galera Support • Amazon Aurora Features • LDAP Integration • SSL, Audit Log & Security • JSON Support • Performance Enhancements • MySQL 8
7. Supported OSes and Packaging What has changed and what is on the roadmap?
8. 8 - OS Support Support has been discontinued for EoL OSes: • RHEL / CentOS 5 • Debian 7 • Ubuntu 12
9. 9 + OS Support Support has been extended to include: • Ubuntu 18 • Fedora 27 • Fedora 28
10. 10 Startup script changes ProxySQL now runs under systemd for: • Ubuntu 18 • Debian 9 • RHEL / CentOS 7 (* not yet available in package) ProxySQL also uses a dedicated “proxysql” user instead of “root” by default (all new package versions).
11. 11 Packaging Packaging via the Makefile is now done via docker-compose which needs to be installed before building. • Build images can be found on Docker Hub in the ProxySQL organization: https://hub.docker.com/u/proxysql/ • Additional ProxySQL images are available for Docker / Kubernetes and general container services: https://hub.docker.com/r/proxysql/proxysql
12. 12 Repository for ProxySQL 2.0 HTTP / HTTPS repository has been created for ProxySQL 2.0 to prevent automatic upgrades, new URL is: https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/ • APT repository • YUM repository
13. Query Cache Tunables Tweaking, tuning and defining the behaviour of ProxySQL 2.0
14. 14 Query Cache Improvements • Most importantly, a new command was added to flush the query cache: PROXYSQL FLUSH QUERY CACHE • Updating mysql_query_rules.cache_ttl now purges cache entries • Additionally, a new setting was added to avoid caching empty resultsets: mysql_query_rules.cache_empty_result
15. 15 Query Digest Tracking • A new variable mysql-query_digests_track_hostname has been introduced which when enables tracking of the source host which sends a query listed in the MySQL query digest stats table stats_mysql_query_digest
16. GTID Causal Reads Ensuring consistency by tracking GTIDs across a MySQL cluster
17. 17 GTID Consistency in ProxySQL ProxySQL can now be configured to enforce GTID consistency for reads on any hostgroup / replication hostgroup. The hostgroup will ensure that any subsequent DQL: • Will be routed only to hosts which have executed the previous transaction's GTID for the connection • There is always a node available to serve the DQL statement when the PRIMARY host is part of the hostgroup / READER
18. 18 How ProxySQL tracks GTIDs
19. 19 ProxySQL Binlog Reader • A lightweight process that runs on the MySQL server • Primary task is to provide GTID information about a MySQL server to all connected ProxySQL instances • Designed to be robust and efficient, keeping CPU / Network I/O to an absolute minimum and supporting hundreds of instances • Features an auto-restart mechanism in case of failure and a client side reconnect
20. 20 Where can I get Binlog Reader? • ProxySQL Binlog Reader is available in a separate repository • Currently only Debian / Ubuntu builds can be performed ◦ For example: make ubuntu18 • RHEL / CentOS is planned • Repository URL: https://github.com/sysown/proxysql_mysqlbinlog
21. Native Galera Support Built-in Galera Clustering configuration support
22. 22 What is Galera Replication? • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Multi-threaded Replication • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes
23. 23 Galera ProxySQL Configuration In ProxySQL 2.0 the concept of the regular replication hostgroup is extended, in addition to “reader_hostgroup” and “writer_hostgroup” we also have the following additional concepts and hostgroup types: • max_writers • writer_is_also_reader • max_transactions_behind • backup_writer_hostgroup • offline_hostgroup
24. 24 Galera Replication Hostgroups CREATE TABLE mysql_galera_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR
25. 25 Galera Replication Hostgroups writer_hostgroup - the hostgroup id that will contain writer nodes (RO=0) backup_writer_hostgroup - when multiple writers are active (RO=0 on more than 1x node) but max_writers is defined all nodes that exceed this value are moved to the backup writer group (standby nodes) reader_hostgroup - the hostgroup id that will contain reader nodes (RO=1) offline_hostgroup - when ProxySQL's monitoring determines a node is offline it will be moved to the offline_hostgroup
26. 26 Galera Replication Hostgroups active - ProxySQL monitors the active hostgroups and makes use only of active hostgroups. Facilitates standby configurations e.g. DC failover max_writers - limits the number of nodes allocated to the writer hostgroup writer_is_also_reader - determines if a node in the writer hostgroup will also belong to the reader hostgroup max_transactions_behind - determines the maximum number of writesets behind the node can fall before shunning the node to prevent stale reads (determined by querying the Galera’s `wsrep_local_recv_queue`).
27. 27 Galera Monitoring The check timeouts are controlled in the “global_variables” table’s “xxx_galera_healthcheck_xxx” variables: ProxySQL Admin> select * from global_variables where variable_name like '%monitor_galera%'; +-------------------------------------------+-------------------------+ | variable_name | variable_value | +----------------------------------------------------+----------------+ | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | +=---------------------------------------------------+----------------+
28. 28 Galera Monitoring In ProxySQL 2.0.4 we added a new variable: mysql-monitor_galera_healthcheck_max_timeout_count • The default value is 3. • The goal of this variable is to prevent flapping by specifying a threshold after which ProxySQL will reconfigure the MySQL servers and replication hostgroups in case a check temporarily fails.
29. Amazon Aurora Features Plug and play support for Amazon Aurora cluster
30. 30 Aurora AWS Integration • Get metrics and statuses from REPLICA_HOST_STATUS • Automatically detects writer/reader roles • Auto-discovery of new replicas • AZ awareness • Replication lag monitoring with millisecond granularity
31. 31 Aurora AWS Integration • New configuration table CREATE TABLE mysql_aws_aurora_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, aurora_port INT NOT NUlL DEFAULT 3306, endpoint_address VARCHAR NOT NULL DEFAULT '', max_lag_ms INT NOT NULL CHECK (max_lag_ms>= 10 AND max_lag_ms <= 600000) DEFAULT 600000, check_interval_ms INT NOT NULL CHECK (check_interval_ms >= 100 AND check_interval_ms <= 600000) DEFAULT 1000, check_timeout_ms INT NOT NULL CHECK (check_timeout_ms >= 80 AND check_timeout_ms <= 3000) DEFAULT 800, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0, new_reader_weight INT CHECK (new_reader_weight >= 0 AND new_reader_weight <=10000000) NOT NULL DEFAULT 1, comment VARCHAR, UNIQUE (reader_hostgroup))
32. 32 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_log mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_log\G *************************** 1. row *************************** table: mysql_server_aws_aurora_log Create Table: CREATE TABLE mysql_server_aws_aurora_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, success_time_us INT DEFAULT 0, error VARCHAR, SERVER_ID VARCHAR NOT NULL DEFAULT '', SESSION_ID VARCHAR, LAST_UPDATE_TIMESTAMP VARCHAR, replica_lag_in_milliseconds INT NOT NULL DEFAULT 0, CPU INT NOT NULL DEFAULT 0, PRIMARY KEY (hostname, port, time_start_us, SERVER_ID))
33. 33 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_check_status mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_check_status\G *************************** 1. row *************************** table: mysql_server_aws_aurora_check_status Create Table: CREATE TABLE mysql_server_aws_aurora_check_status ( writer_hostgroup INT NOT NULL, hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, last_checked_at VARCHAR, checks_tot INT NOT NULL DEFAULT 0, checks_ok INT NOT NULL DEFAULT 0, last_error VARCHAR, PRIMARY KEY (writer_hostgroup, hostname, port))
34. 34 Aurora AWS Monitor • New monitor table mysql_server_aws_aurora_failovers mysql> SHOW CREATE TABLE monitor.mysql_server_aws_aurora_failovers\G *************************** 1. row *************************** table: mysql_server_aws_aurora_failovers Create Table: CREATE TABLE mysql_server_aws_aurora_failovers ( writer_hostgroup INT NOT NULL, hostname VARCHAR NOT NULL, inserted_at VARCHAR NOT NULL)
35. LDAP Integration Enterprise grade centralised ProxySQL authentication management
36. 36 LDAP implementation • client connects to ProxySQL using clear text password • if ProxySQL doesn't know the username specified by the client, it will connect to an LDAP server using the same credentials used by the client • if the connection to LDAP server succeeds, ProxySQL knows that the credentials are correct • ProxySQL will retrieve from LDAP all the attributes for the specified user using filter memberOf • because a single user could be part of multiple groups, the list of attributes matching filter memberOf will be used to find the best match (higher priority) to determine which mapping backend user needs to be used to connect to MySQL • ProxySQL connects to MySQL using mapped credentials, or original credentials if no mapped credentials are found
37. 37 LDAP implementation • Available as a loadable plugin • When loaded, it creates new configuration variables • LOAD/SAVE LDAP VARIABLES TO/FROM RUNTIME/MEMORY/DISK Admin> SHOW LDAP VARIABLES; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | ldap-bind_dn_prefix | | | ldap-bind_dn_suffix | | | ldap-cache_ttl_sec | 30000 | | ldap-connect_timeout_ms | 1000 | | ldap-root_dn | | | ldap-uri | | | ldap-verbosity | 0 | +-------------------------+-------+
38. SSL, Audit Log & Security New and improved ProxySQL 2.0 security features
39. 39 SSL Support in ProxySQL 2.0 Frontend SSL support is available in ProxySQL 2.0 • ProxySQL 2.0 includes the mariadb-connector-3.0.2 and supports SSLv2, SSLv3, TLSv1.0, TLSv1.1 and TLSv1.2 Additionally, Backend SSL support is extended to include the following in addition to SSL/TLSv1.0 which was supported in ProxySQL 1.4: • TLSv1.1 and TLSv1.2
40. 40 Frontend SSL To enable frontend SSL in ProxySQL 2.0 enable the global variable “mysql-have_ssl=true” • ProxySQL will automatically generate (if not present) the following files in “/var/lib/proxysql/”: ◦ “proxysql-ca.pem” ◦ “proxysql-cert.pem” ◦ “proxysql-key.pem”
41. 41 Frontend SSL CIPHERs The MySQL Compliant CIPHERs are supported: DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA256 DHE-RSA-AES256-SHA DHE-RSA-CAMELLIA256-SHA AES256-GCM-SHA384 AES256-SHA256 AES256-SHA CAMELLIA256-SHA DHE-RSA-AES128-GCM-SHA256 DHE-RSA-AES128-SHA256 DHE-RSA-AES128-SHA DHE-RSA-SEED-SHA DHE-RSA-CAMELLIA128-SHA AES128-GCM-SHA256 AES128-SHA256 AES128-SHA SEED-SHA CAMELLIA128-SHA DES-CBC3-SHA
42. 42 Verifying Frontend SSL To check the SSL CIPHER between the MySQL client and ProxySQL run “\s” and look at the SSL field: mysql -e 'status' | egrep 'SSL:|Connection:' SSL: Cipher in use is DHE-RSA-AES256-SHA Connection: 127.0.0.1 via TCP/IP
43. 43 Verifying Frontend SSL To verify SSL is working as expected connect to ProxySQL via a mysql-client and check the status of “Ssl_cipher”, this shows the SSL CIPHER between ProxySQL and MySQL: ProxySQL Admin> SHOW SESSION STATUS LIKE "Ssl_cipher” +---------------+----------------------+ | Variable_name | Value | +---------------+----------------------+ | Ssl_cipher | ECDHE-RSA-AES256-SHA | +---------------+----------------------+
44. 44 Verifying Frontend SSL SSL can also be verified from the new stats_mysql_processlist.extended_info using JSON . More details later (we can create an example after describing JSON).
45. 45 QPS Impact of SSL
46. 46 Latency Impact of SSL
47. 47 Audit Log Overview ProxySQL 2.0.5 introduces the Audit Log • This feature allows to track certain connection activities. • The Audit Log is disabled by default, to enable define the `mysql-auditlog_filename` • The supported file format is JSON
48. 48 Audit Log Events (MySQL) The following events are logged for the MySQL Module: • Successful authentication • Failed authentication • Graceful disconnect • Closed connection • Change of schema (COM_INIT_DB)
49. 49 Audit Log Events (Admin) The following events are logged for the Admin Module: • Successful authentication • Failed authentication • Graceful disconnect • Closed connection
50. 50 Audit Log Events (Future) Further extensions are expected in the future, specifically: • Support for change of user (COM_CHANGE_USER) on MySQL Module • Support for events on SQLite3 Server Module • Support for events on ClickHouse Module
51. 51 Audit Log Variables • `mysql-auditlog_filename` : the base name of the audit log where audit events are logged. The filename of the log file will be the base name followed by an 8 digit progressive number ◦ The default value is an empty string (``). • `mysql-auditlog_filesize` : the maximum file size of the audit log before the file is rotated ◦ The default value is `104857600` (100MB).
52. 52 Audit Log Format (1/2) • `client_addr` : address (IP:port) of the client connecting to ProxySQL • `proxy_addr` : address (IP:port) of the bind interface where ProxySQL was listening (available only for MySQL module) • `event` : event type (detailed in a later slide) • `time` : human readable time of when the even happened, with milliseconds granularity • `timestamp` : epoch time in milliseconds • `ssl` : boolean value that specify if SSL is being used or not
53. 53 Audit Log Format (2/2) • `schemaname`: the current schema for successful and established connections • `username`: client's username • `thread_id`: the thread_id (session ID) assigned to the client • `creation_time` : when the session was created, information available only when the session is closed • `duration` : time in milliseconds since the session was created, information available only when the session is closed • `extra_info` : attribute that provides additional information. Currently only used to describe in which part of the code the session is closed.
54. 54 Audit Log Event Types (MySQL) • `MySQL_Client_Connect_OK` : successful connection to MySQL Module • `MySQL_Client_Connect_ERR` : failed connection to MySQL Module • `MySQL_Client_Close` : MySQL Session being closed • `MySQL_Client_Quit` : client sending an explicit `COM_QUIT` to MySQL Module • `MySQL_Client_Init_DB` : client sending an explicit `COM_INIT_DB` to MySQL Module
55. 55 Audit Log Event Types (Admin) • `Admin_Connect_OK` : successful connection to Admin Module • `Admin_Connect_ERR` : failed connection to Admin Module • `Admin_Close` : Admin Session being closed • `Admin_Quit` : client sending an explicit `COM_QUIT` to Admin Module
56. 56 Audit Log Examples (admin) {"client_addr": "127.0.0.1:39954", "event": "Admin_Connect_ERR", "schemaname": "", "ssl": false, "thread_id": 2, "time": "2019-05-20 18:48:47.631", "timestamp": 1558342127631, "username": "admin"} A client (`"client_addr":"127.0.0.1:39954"`) with (`"thread_id":2`) failed to connect to Admin (`"event":"Admin_Connect_ERR"`), without using SSL (`"ssl":false`) using username "admin" (`"username":"admin"`).
57. 57 Audit Log Examples (admin) {"client_addr": "127.0.0.1:43266", "event": "Admin_Connect_OK", "schemaname": "main", "ssl": false, "thread_id": 3, "time": "2019-05-20 19:16:53.313", "timestamp": 1558343813313, "username": "admin"} A successful login on Admin module (`"event":"Admin_Connect_OK"`) from `"client_addr":"127.0.0.1:43266"` , without SSL (`"ssl":false`) with username `admin`.
58. 58 Audit Log Examples (admin) {"client_addr": "127.0.0.1:43266", "event": "Admin_Quit", "schemaname": "main", "ssl": false, "thread_id": 3, "time": "2019-05-20 19:16:56.513", "timestamp": 1558343816513, "username": "admin"} The client listed above explicitly sends a `COM_QUIT` command
59. 59 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "event": "MySQL_Client_Connect_OK", "proxy_addr": "0.0.0.0:6033", "schemaname": "mysql", "ssl":false, "thread_id": 4, "time": "2019-05-20 19:20:26.668", "timestamp": 1558344026668, "username": "sbtest"} A client (`"client_addr":"127.0.0.1:40822"`) successfully connected to MySQL module (`"event":"MySQL_Client_Connect_OK"`) on a given bind interface (`"proxy_addr":"0.0.0.0:6033"`) , without SSL (`"ssl":false`).
60. 60 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "event": "MySQL_Client_Init_DB", "proxy_addr": "0.0.0.0:6033", "schemaname": "sbtest", "ssl": false, "thread_id": 4, "time": "2019-05-20 19:20:29.902", "timestamp": 1558344029902, "username": "sbtest"} A few seconds after, the same client issue a `COM_INIT_DB` (`"event":"MySQL_Client_Init_DB"`), switching schemaname (`"schemaname":"sbtest"`) .
61. 61 Audit Log Examples (mysql) {"client_addr": "127.0.0.1:40822", "creation_time": "2019-05-20 19:20:26.668", "duration": "9091.966ms", "event": "MySQL_Client_Close", "extra_info": "MySQL_Thread.cpp:3733:process_all_sessions()", "proxy_addr": "0.0.0.0:6033", "schemaname":"sbtest", "ssl": false, "thread_id": 4, "time": "2019-05-20 19:20:35.760","timestamp":1558344035760,"username":"sbtes t"}
62. JSON Support Exporting ProxySQL data & statistics in JSON
63. 63 JSON Data in ProxySQL Currently, JSON format is supported in the following ProxySQL Admin tables: • stats_mysql_processlist • stats_mysql_free_connections JSON within ProxySQL Admin can also be parsed using the standard “json_extract” function.
64. 64 Proc. List Stats - extended_info ProxySQL Admin> select extended_info from stats_mysql_processlist WHERE ThreadID = 0\G *************************** 1. row *************************** extended_info: {"autocommit":true,"autocommit_on_hostgroup":-1,"backends":[{"conn":{"MultiplexDisabled":false,"autocommi t":true,"init_connect":"","init_connect_sent":true,"last_set_autocommit":-1,"mysql":{"affected_rows":1844 6744073709551615,"charset":8,"db":"test","host":"127.0.0.1","host_info":"127.0.0.1 via TCP/IP","insert_id":0,"net":{"fd":28,"last_errno":0,"max_packet_size":1073741824,"sqlstate":"00000"},"opt ions":{"charset_name":"utf8","use_ssl":0},"port":13308,"server_status":16386,"server_version":"5.7.24-log ","unix_socket":"","user":"root"},"no_backslash_escapes":false,"ps":{"backend_stmt_to_global_ids":[],"glo bal_stmt_to_backend_ids":[]},"sql_log_bin":1,"sql_mode":"","status":{"found_rows":false,"get_lock":false, "lock_tables":false,"no_multiplex":false,"temporary_table":false,"user_variable":false},"time_zone":"SYST EM"},"hostgroup_id":11}],"client":{"client_addr":{"address":"127.0.0.1","port":37810},"encrypted":false," proxy_addr":{"address":"0.0.0.0","port":6033},"stream":{"bytes_recv":111,"bytes_sent":89,"pkts_recv":2,"p kts_sent":2},"userinfo":{"password":"*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B","username":"root"}},"conn ":{"autocommit":true,"charset":8,"no_backslash_escapes":false,"ps":{"client_stmt_to_global_ids":[]},"sql_ log_bin":1,"sql_mode":"","status":{"compression":false,"transaction":false},"time_zone":"SYSTEM"},"curren t_hostgroup":11,"default_hostgroup":10,"default_schema":"","last_HG_affected_rows":-1,"last_insert_id":0, "thread_session_id":5,"transaction_persistent":true}
65. 65 KEEP CALM AND LOVE JSON
66. 66 Dissecting extended_info... "autocommit": true, "autocommit_on_hostgroup": -1, "backends": [...], "client": {...}, "conn": {...}, "current_hostgroup": 11, "default_hostgroup": 10, "default_schema": "", "last_HG_affected_rows": -1, "last_insert_id": 0, "thread_session_id": 5, "transaction_persistent": true
67. 67 Backends Element "backends": [{ "conn": { "MultiplexDisabled": false, "autocommit": true, "init_connect": "", "init_connect_sent": true, "last_set_autocommit": -1, "mysql": {...}, "no_backslash_escapes": false, "ps": {"backend_stmt_to_global_ids": [], "global_stmt_to_backend_ids": []}, "sql_log_bin": 1, "sql_mode": "", "status": {...}, "time_zone": "SYSTEM" }, "hostgroup_id": 11 }]
68. 68 Backends “conn”.“mysql” "mysql": { "affected_rows": 18446744073709552000, "charset": 8, "db": "test", "host": "127.0.0.1", "host_info": "127.0.0.1 via TCP/IP", "insert_id": 0, "net": {"fd": 28, "last_errno": 0, "max_packet_size": 1073741824, "sqlstate": "00000"}, "options": {"charset_name": "utf8", "use_ssl": 0}, "port": 13308, "server_status": 16386, "server_version": "5.7.24-log", "unix_socket": "", "user": "root" }
69. 69 Backends “conn”.”status” "status": { "found_rows": false, "get_lock": false, "lock_tables": false, "no_multiplex": false, "temporary_table": false, "user_variable": false },
70. 70 Client Element "client": { "client_addr": {"address": "127.0.0.1","port": 37810}, "encrypted": false, "proxy_addr": {"address": "0.0.0.0","port": 6033}, "stream": {"bytes_recv": 111, "bytes_sent": 89, "pkts_recv": 2, "pkts_sent": 2}, "userinfo": {"password": "XXXX", "username": "root"} }
71. 71 Conn Element "conn": { "autocommit": true, "charset": 8, "no_backslash_escapes": false, "ps": {"client_stmt_to_global_ids": []}, "sql_log_bin": 1, "sql_mode": "", "status": {"compression": false, "transaction": false}, "time_zone": "SYSTEM" }
72. 72 Free Conn. Stats - mysql_info Admin> SELECT mysql_info FROM stats_mysql_free_connections LIMIT 1\G ****************** 1. row ****************** mysql_info: {"affected_rows":18446744073709551615,"charset":8,"db":"t est","host":"127.0.0.1","host_info":"127.0.0.1 via TCP/IP","insert_id":0,"net":{"fd":36,"last_errno":0,"max_ packet_size":1073741824,"sqlstate":"00000"},"options":{"c harset_name":"utf8","use_ssl":0},"port":13307,"server_sta tus":2,"server_version":"5.7.24-log","unix_socket":"","us er":"root"}
73. 73 Using JSON_EXTRACT ProxySQL Admin> SELECT fd, hostgroup hg, srv_host, srv_port, JSON_EXTRACT(mysql_info,'$.server_version') sv , statistics FROM stats_mysql_free_connections ORDER BY JSON_EXTRACT(statistics,'$.questions') LIMIT 1\G fd: 30 hg: 11 srv_host: 127.0.0.1 srv_port: 13307 sv: 5.7.24-log Statistics: {"bytes_recv": 408, "bytes_sent": 551, "myconnpoll_get": 69, "myconnpoll_put": 69, "questions": 68}
74. Performance Enhancements What’s been done to make ProxySQL 2.0 faster than ever before?
75. 75 Upgraded Dependencies Several of the core dependencies have been upgraded, including: • MariaDB Connector C baseline upgraded to 3.0.2 • jemalloc upgraded to 5.2.0 • SQLite upgraded to 3.19.2 • libconfig upgraded to 1.7.2
76. 76 Performance Improvements • Improved the performance of MySQL_Thread::refresh_variables() • Several optimizations in the code that generate a variety of stats tables • Multiple improvements in the Monitor module • Traffic handled when fast_forward=1 is now considerably faster • Added support for mysql-keep_multiplexing_variables to define additional MySQL system vars to keep multiplexing enabled
77. 77 Memory Optimizations • Automatic VACUUM of stats schema • When the new global variable mysql-query_digests_normalize_digest_text is enabled, queries statistics store digest_text on a different hash table. This drastically reduces memory usage on setups with many schemas but similar queries patterns • Improved memory usage in jemalloc 5.2.0
78. Support for MySQL 8
79. 79 MySQL 8 • Auth Plugin a. sha256_password b. caching_sha2_password
80. Q&A Any questions?