Home / server / Questions / 184066
Accepted
Zach Bonham
Asked: 2010-09-24 10:35:57 +0800 CST

Proxy for database server/connection?

  • 772

I am coming from an application development perspective (my day job) and not an infrastructure, or operations, perspective, so if this is a completely inane question, please let me know!

We have proxies in front of our application servers and this has typically served us well. This (hardware/software) proxy provides an abstracted endpoint (in addition to other services, such as load balancing/distribution) so that our clients always know how to connect.

Does anyone have any experience putting a proxy between application servers and the the database?

Seems like someone has got to be doing this, especially in hosting provider, or cloud space, but when I've talked to our (enterprise) infrastructure teams they just say 'no' though I'm still struggling to understand why.

I don't understand if due to the extra layer of abstraction in front of the database this causes functional or performance issues, or just plain makes it harder to trouble shoot when things go wrong.

Can anyone share thoughts, experiences, or resources in this area? I would greatly appreciate it!

Thanks!

Z

proxy database

3 Answers

  1. Main problems:

    • Database connections are way more persistent between application servers and backends - so load balancing is hard through them.

    • Proxies mus tspeak the protocol. There are tons of HTTP proxies there. Try getting one for for example the SQL Server protocol. No need, no market - no product.

    • Finally, though what SQL Servers are, caching is not possible. Not without a protocol to invalidate results etc., and that is not part of any SQL implementation. No "if modified since" tag. Ergo, you would either gain nothing or deal with outdated data - both not acceptable on that layer.

    • 4
  2. Best Answer

    I also like TomTom's DNS idea. Short TTLs and this is easier than you're making it to be.

    But you can certainly run a proxy in front of your databases. SQLProxy and HAProxy can do this, for example. But again, if your concern is not load balancing or separating reads and writes, why add this extra level of latency (to and from the proxy) when you could just do it with a CNAME -- and I think that the argument is the one your Ops/Systems guys will consider.

    • 3

  3. I realize this is an old question, but at this time there are at least a couple of decent database proxy products to choose from. That is, if you're using MariaDB or MySQL:

    • MaxScale, claims to be the world's most advanced database proxy
    • ProxySQL, which has many of the features found in MaxScale

    I only have experience using MaxScale, which I feel I can recommend at this point. MaxScale works really well e.g. with Galera clusters. It's taken a little while to mature, perhaps because it's a relatively new concept developed from scratch, and it's solving some non-trivial problems.

    It should be noted that MaxScale has a special non-open source licence - BSL. (ProxySQL on the other hand is licenced under GPL.)

    I could also mention HAProxy which is not a database-aware proxy, but has nevertheless been widely used successfully as a database proxy both for MariaDB/MySQL and PostgreSQL.

    • 2