Part 73: Use ProxySQL to help your database

ProxySQL cover image

If you have a busy Zabbix system with lots of people using the web interface, perhaps Grafana with complex dashboards connecting to your Zabbix, and many other tasks directly communicating with the database, here's an idea: try out ProxySQL, a yet another nice open source product.

What does it do?

It sits between your application and your database. All you need to do is to point your applications -- such as Zabbix -- to ProxySQL, and it will transparently take care of everything. It will establish a connection pool to your database and reuse the open connections in smart way (read the webpage...), and allows you to go very granular with multiple database servers, query routing, much more not relevant for this blog post.

The connection pooling helps with the number of open connections: connections are immediately reused, so most likely you don't see nearly as many idle, sleeping connections consuming the resources of your database.

How much does it help?

In my humble What's up, home? environment with few browser sessions open to Zabbix and Grafana, and some scripts doing their business, this is what happened. The empty spots on graphs are when I was configuring ProxySQL, and the later spike is me going "What? Is this true?" and reverting to my old config just to see if the status would change back to old. Yes it did, so back to ProxySQL I went.

See how the number of threads connected went from 30-40 to around 5.

Threads connected

... and observe how much more effectively MySQL is now using cached threads.

Threads cached

 

How do I install it?

If it's not directly available with your usual <packagemanagername> install proxysql, then see from the official installation page.  After you get the package installed, you just need to do few things in the /etc/proxysql.cnf config file to get started. The config file is very self-explanatory, so I trust that you can follow the setup with these pseudo instructions.

  • Change server version from the default "5.7.30" or so to something more modern matching with your server version;: this value is what ProxySQL will tell to connecting clients
  • By default ProxySQL listens on port 6033 for incoming clients; change that and the possible NIC bindings to suit your needs
  • Define your actual MySQL server location and how to connect to it: the config file has several presets as commented blocks, just uncomment and modify for your needs
  • Define users that can connect, again there's several presets available

Next do the usual 

systemctl enable proxysql && systemctl start proxysql

Then, go to your app settings -- like Zabbix web interface settings /etc/zabbix/web/zabbix.conf.php or so -- and point it to your new ProxySQL address. 

You are done!

If you want to go deeper, ProxySQL can also be configured and its runtime statistics & variables seen using MySQL console like interface which by default is listening on port 6032.

There's also at least one Zabbix template for monitoring ProxySQL, but the one I found by quick search looks abandoned and I have no idea yet if it works.

Caveat emptor

Of course, if doing this somewhere else than a cozy home lab, be prepared to harden the setup and possibly adjust the max connections, connected clients, other workload related settings. But anyway, getting started with this thing is easy and can be beneficial -- or at least fun for a nerd like me.

 

 

 

 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
Buy me a coffee

Like these posts? Support the project and Buy me a coffee