JK,
On 11/25/21 04:23, jkla...@iki.fi wrote:
On Wednesday, Nov 24, 2021 at 7:37 PM, Christopher Schultz
<ch...@christopherschultz.net (mailto:ch...@christopherschultz.net)> wrote:
(on the significance of DBCP overall)
It's essentially "failing faster" or, IMO, "failing safer."
All right, I think I see it now. A very good explanation, thank you.
If I were you, I'd examine the reason behind your decision to use
ProxySQL in the first place. Maybe it makes sense to use it, but maybe
it makes sense to use what you already have, and not introduce a new
component into the mix.
>
I just realized that an earlier reply of mine to Olaf Kock bounced
for some reason and never made it to the list; it had the reasoning
behind this. Here it is again:
Reason number 1: the read-write split.
The MySQL database has a LOT of legacy baggage in the form of ancient
MyISAM tables, which are really prone to table-level locks.
(Upgrading the tables to InnoDB to enable row-level locking is a
long-term goal, but it has been tried before unsuccessfully, so it'll
take much more time to do it right.) We're seeing these locks and
resulting expensive outages with certain patterns of simultaneous
read/write loads. We've created a replica server to which we'll be
able to point nearly all of the read traffic, which should basically
instantly solve this problem.
This won't work with writes + reads that are in the same transaction. If
you know it's safe to issue a read separately from a write, you can use
a separate connection (pool) which queries a read-only replica. Is the
proxy smart enough to know when it's okay to issue a read to a replica
instead of a primary?
Reason number 2: better query statistics and the capability to act
on them without downtime
At this point you'll likely not be surprised to hear that the Java
application produces practically no useful log output. ProxySQL will
help us to identify query-level bottlenecks, and in some cases we'll be
able to use its other functionalities (e.g. query caching or query
rewriting, and the ability to activate them on the fly) to work around
problematic queries without having to touch Java code at all.
The slow query log and audit plug-in can do this as well. I'm not saying
the proxy isn't helpful for this purpose, just that other tools do exist
without introducing a new moving part in a production deployment.
In both cases, the Java application is "the right place" to fix these
issues (although the read-write split is a good idea for our
environment in any case). But as complicated as the ProxySQL
undertaking is, fixing the decades-old, poorly architected,
monolithic legacy Java application with library versions EOL'd over a
decade ago and tons of SQL queries that rely on old MySQL misfeatures
would be far more time-consuming and sometimes difficult enough to
the point of infeasibility.
Understood. Technical dept is a real thing. You do ultimately have to
pay it off, because it grows all the time.
Anyhow, thank you for your answers. They have been extremely helpful
and will give us confidence to move forward without worrying too much
about the two pools coexisting.
I hope everything goes well for you.
-chris
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
