> 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. 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. 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. 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. - JK
