Handling database connection pooling outside Java, without DBCP et al?
I've been tasked with the maintenance of a client's legacy Tomcat 8.0 application servers. The person who initially configured Tomcat on them is no longer with the company, and I've basically been thrown into the deep end with no prior Tomcat or Java knowledge. Their Java developers are also unfamiliar with the Tomcat setup. I've been reading through a bunch of documentation to try to make sense of it all, so excuse my extreme newbieness. The servers are running a single Java application under Tomcat that connects to a MySQL server. The data sources are configured in the META-INF/context.xml file of the Java application. It contains a bunch of Resource elements with the javax.sql.DataSource type. We're in the process of adopting ProxySQL in front of MySQL, to act as the connection pooler and for separating read and write traffic to different database instances. After this, we have no need for DBCP or any other Java-level pooling – in fact, having two levels of connection pooling would probably be detrimental to performance, and certainly to our ability to diagnose issues. Trouble is, based on my reading of the JNDI Resources HOW-TO, the Java EE specs require that the data source implementation features connection pooling, and Tomcat follows this. Accordingly, the Resource element features a bunch of pooling-related DBCP attributes. I haven't been able to figure out how to entirely disable DBCP so that ProxySQL could handle the pooling entirely. I can leave out the pooling-related Resource attributes, but that probably just causes a bunch of defaults to be used. I could maybe configure a "pool" of one connection on the Java side, but my intuition says that's a bad idea, and I'm not sure how to do it anyway. What seems the most realistic option is configuring the MySQL connection in some other, non-pooled fashion outside Tomcat, but I don't know how. With the last option, it would have to be something that requires minimal code changes in the Java application. Any guidance would be appreciated. - JK
Re: Handling database connection pooling outside Java, without DBCP et al?
> On Tuesday, Nov 23, 2021 at 4:20 PM, Christopher Schultz > mailto:ch...@christopherschultz.net)> wrote: > > ProxySQL is, mostly, a load-balancing and caching product. Sure, it can > provide connection-pooling, but that doesn't mean that you want your > application making 1000 simultaneous requests to the proxy. Could you expand on this? Connection pooling is a fundamental aspect of how ProxySQL operates, after all. I'm not familiar with how the DBCP connection pooling works, but what added value would it bring in this case? What does DBCP pooling at the application level achieve that ProxySQL wouldn't? In case it's relevant, we're planning on installing ProxySQL on the app servers themselves, an approach generally recommended by e.g. Percona. My main fear is that having two separate connection pools would make performance tuning at the pool level potentially nightmarish. We'd make a config change at the ProxySQL level, test it, and conclude that it didn't have a meaningful impact–when it actually might have, had there not been another, poorly understood (by us, that is) pool at play. DBCP has the potential to muddy the waters: even if it doesn't actively work against ProxySQL-level tuning, we can't really prove that it's not a contributing cause any time one of our ProxySQL config changes fails to improve performance. > > I haven't been able to figure out how to entirely disable DBCP so > > that ProxySQL could handle the pooling entirely. > You can't. You'd have to tear-out the use of the JNDI-provided > DataSource altogether which isn't worth your time. Elsewhere in this thread, Mark Thomas said this in response to Olaf Kock, referring to his idea of a custom "non-pooling connection pool": > > No need for this. > > > > > DBCP looks exactly like a database driver to the application. So, rather > > > > > > > > > than configuring DBCP as a wrapper for the database driver, you just > > > > > > > configure the database driver directly. > > > > > > > > > The short version is: > > - remove the pooling configuration from the resource definition > > > > > > > - change / set the factory attribute the the correct value for your > > > > > > > driver (probably com.mysql.cj.jdbc.MysqlDataSourceFactory) > > > > > Now, this probably shouldn't be taken as a recommendation or even a statement of practical feasibility. How do you view this option, assuming local ProxySQL installs on the app servers? You can still assume my complete ignorance about every element between the JNDI specs and the actual configuration statements in context.xml :-) - JK
Re: Handling database connection pooling outside Java, without DBCP et al?
> On Wednesday, Nov 24, 2021 at 7:37 PM, Christopher Schultz > 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
