ProxySQL 2.1.0 is here introducing a plethora of new features, upgraded dependencies, bug fixes and more statistics than ever!
At the forefront of this release we have the all new built in Prometheus exporter.
There is no longer a need to deploy external scripts or additional tools to export your Prometheus stats from ProxySQL, all that is needed is to enable the exporter and you can start scraping ProxySQL for Prometheus metrics. The built in Prometheus Exporter exposes internal ProxySQL statistics about connection pooling, internal variables, runtime status and more.
Although the exporter can be freely integrated with any tooling you may have available we’re also throwing in our own recommended ProxySQL GPLv3 Grafana Dashboards filled with all the most useful metrics to ensure your ProxySQL fleet stays in top form and allowing you to pick up on problems quickly as well as a Dockerized monitoring stack to get up and running quickly.
For this first release two key dashboards will be available, namely “ProxySQL Fleet Overview” and “ProxySQL Host Statistics”.
The first serves as a more high level view of all deployed ProxySQL instances while the latter allows you to drill down on a per host basic and even view statistics at the hostgroup or backend server level depending on the graph being viewed and the granularity of the underlying metrics.
For a general overview of all the ProxySQL nodes within your fleet review your “ProxySQL Fleet Overview” to understand the fleet’s “aggregated database workload”. Quickly understand the capacity of your ProxySQL deployment by reviewing your cluster-wide statistics and gain crucial data related to the performance and efficiency of ProxySQL by monitoring statistics such as the Multiplexing Efficiency Ratio, Active Transactions and Backend vs. Frontend Connections.
All stats can be filtered up to the instance level if you wish to only view a specific node or even a subset of nodes and cluster-wide hostgroup stats can be easily aggregated to get quick statistics on the total usage of a backend instance by all of the connected ProxySQL instances! When you are interested in getting more details on a specific host its time to drop into the “ProxySQL Host Statistics” dashboard.
The ProxySQL Host Statistics dashboard provides highly detailed “per host” statistics covering many areas including connection pooling, command types, prepared statements, query cache, routing, clustering and much much more!!!
The Prometheus exporter itself exports 126 different metric types in this release and we plan to add a lot more in the future! The Grafana dashboards make use of a large part of these metrics already and further enhancements are also planned for the existing dashboards as well as new dashboards providing backend server insights as well for a holistic view of your database infrastructure!
Moving on to other improvements, we’ve upgraded core dependencies to include all the latest security patches, features and performance improvements; specifically:
- MariaDB Connector C upgraded to 3.1.9
- RE2 upgraded to 2020-07
- PCRE upgraded to 8.44
- OpenSSL upgraded to 1.1.1g
- SQLite upgraded to 3.19.2
On the performance side of things we’ve implemented several important internal memory optimizations as well as a lot of code refactoring to enhance various areas of ProxySQL’s implementation as well as to improve performance at scale by reducing overhead. This also includes enhancements such as automatic vacuum of the internal stats and newly implemented “digest_text” hash table to improve memory usage on backends with a high schema count and similar query patterns across all. Digests for queries with unbounded length IN (?, ?, ?…) clauses are also now grouped – this greatly reduces the query digest footprint for workloads where the same query executes for various different IN (?, ?, ?…) sets.
Owing to an initial need for easier management of ProxySQL in Kubernetes we’ve increased the scope of data which is replicated within a ProxySQL Cluster. The improved ProxySQL Cluster dataset coverage includes all mysql variables, admin variables and native replication hostgroups (AWS, Galera, Group Replication). With the addition of these enhancements, a new cluster member can be initialised with a near empty configuration file and pull all of its configuration from the cluster easing the deployment of stateless containers.
By extending the scope of ProxySQL Cluster data to include the admin-xxx variables, ProxySQL now has to transfer more sensitive data, specifically passwords which form part of the admin variables. In order to safeguard this data transfer we also implemented SSL for inter-cluster communication so you can deploy ProxySQL on your application servers or a central layer without being concerned about unencrypted credentials and other ProxySQL Cluster data floating around your network.
The “mysql_query_rules” and “mysql_users” have also been extended to include a JSON attributescolumn which can be leveraged to implement a variety of new features as well as ease integration of ProxySQL with other 3rd party tools. Speaking of which, the SQLite plugin implementation has also been refactored and is now a replaceable module which can be extended to introduce features such as encryption at rest for ProxySQL’s data.
The 2.1 release is now available on AMD64 and ARMv8 64-bit with packages available for CentOS/RHEL, Debian and Ubuntu in the official ProxySQL repositories and the Github release page.
A special thanks goes out to all the people that report bugs and submit PRs: this makes each version of ProxySQL better than the previous. Please report any bugs or feature requests on the project’s Github issue tracker.
If you have any questions please do not hesitate to contact us. Our performance and scalability experts are readily available to evaluate the overall state of your database infrastructure and help to build a robust and reliable MySQL HA architecture. We also offer consulting, long term support and training for ProxySQL.