Tomcat 5 Database Pooling with MySQL
Does anyone know what the *hell* I'm doing wrong?
+-----+----------------+-----------+------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+----------------+-----------+------------------+---------+------+-------+------------------+
| 396 | russell_dbuser | www | russell_rbeattie | Sleep | 7029 | | |
| 398 | russell_dbuser | www | mobitopia | Sleep | 5468 | | |
| 399 | russell_dbuser | www | russell_rbeattie | Sleep | 5859 | | |
| 401 | russell_dbuser | www | russell_rbeattie | Sleep | 6764 | | |
| 402 | russell_dbuser | www | russell_rbeattie | Sleep | 5627 | | |
| 409 | russell_dbuser | www | russell_rbeattie | Sleep | 5607 | | |
| 410 | russell_dbuser | www | russell_rbeattie | Sleep | 5670 | | |
| 411 | russell_dbuser | www | russell_rbeattie | Sleep | 2135 | | |
| 412 | russell_dbuser | www | mobitopia | Sleep | 4712 | | |
| 413 | russell_dbuser | www | russell_rbeattie | Sleep | 1037 | | |
| 421 | russell_dbuser | www | russell_rbeattie | Sleep | 5010 | | |
| 423 | russell_dbuser | www | mobitopia | Sleep | 2083 | | |
| 424 | russell_dbuser | www | russell_rbeattie | Sleep | 2946 | | |
| 425 | russell_dbuser | www | russell_rbeattie | Sleep | 1348 | | |
| 426 | russell_dbuser | www | mobitopia | Sleep | 2644 | | |
| 434 | russell_dbuser | www | mobitopia | Sleep | 784 | | |
| 441 | russell_dbuser | www | russell_rbeattie | Sleep | 1740 | | |
| 453 | russell_dbuser | www | russell_rbeattie | Sleep | 1084 | | |
| 462 | russell_dbuser | www | russell_rbeattie | Sleep | 948 | | |
| 463 | russell_dbuser | www | russell_rbeattie | Sleep | 509 | | |
| 473 | russell_dbuser | www | russell_rbeattie | Sleep | 793 | | |
| 477 | russell_dbuser | www | mobitopia | Sleep | 785 | | |
| 478 | russell_dbuser | www | mobitopia | Sleep | 784 | | |
| 479 | russell_dbuser | www | mobitopia | Sleep | 727 | | |
| 480 | russell_dbuser | www | mobitopia | Sleep | 7 | | |
| 481 | russell_dbuser | www | russell_rbeattie | Sleep | 500 | | |
| 490 | russell_dbuser | www | russell_rbeattie | Sleep | 426 | | |
| 491 | russell_dbuser | www | russell_rbeattie | Sleep | 455 | | |
| 508 | russell_dbuser | www | russell_rbeattie | Sleep | 379 | | |
| 510 | russell_dbuser | www | russell_rbeattie | Sleep | 193 | | |
| 511 | russell_dbuser | www | russell_rbeattie | Sleep | 241 | | |
| 519 | russell_dbuser | www | russell_rbeattie | Sleep | 235 | | |
| 521 | russell_dbuser | www | russell_rbeattie | Sleep | 181 | | |
| 522 | russell_dbuser | www | russell_rbeattie | Sleep | 185 | | |
| 523 | russell_dbuser | www | russell_rbeattie | Sleep | 122 | | |
| 524 | russell_dbuser | www | russell_rbeattie | Sleep | 186 | | |
| 525 | russell_dbuser | www | russell_rbeattie | Sleep | 164 | | |
| 540 | russell_dbuser | www | russell_rbeattie | Sleep | 103 | | |
| 541 | russell_dbuser | www | russell_rbeattie | Sleep | 107 | | |
| 542 | russell_dbuser | www | russell_rbeattie | Sleep | 121 | | |
| 543 | russell_dbuser | www | russell_rbeattie | Sleep | 116 | | |
| 545 | russell_dbuser | www | russell_rbeattie | Sleep | 4 | | |
| 546 | russell_dbuser | www | russell_rbeattie | Sleep | 54 | | |
| 552 | root | localhost | | Query | 0 | | show processlist |
+-----+----------------+-----------+------------------+---------+------+-------+------------------+
There should be at most 20 connections and unused connections are supposed to go away after 10 seconds...
Any help would be appreciated on either side of the app to db equasion.
:-)
-Russ
The next day: Woohooo! So I never realized that you could use the MySQL JDBC driver pool in Tomcat! I was using the DBCP stuff (ugh). Using the MysqlConnectionPoolDataSource is exactly what I was doing in OrionServer and it worked great. Paul left me a comment with a link to a Google Groups thread on the subject (look in the comments below) and it spelled it out for me. This is pretty great - I just switched over to it and it looks like it's working much better, though under load it still freaks out. Here's what I have in my server.xml. (I know I don't need the url and the database properties, but I'm not sure which it's going to pay attention to.). Any help refining this would be awesome. Thanks PAUL!
<Resource name="jdbc/RussellBeattieDS" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" auth="Container"/>
<ResourceParams name="jdbc/RussellBeattieDS">
<parameter>
<name>factory</name>
<value>com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory</value>
</parameter>
<parameter>
<name>port</name>
<value>3306</value>
</parameter>
<parameter>
<name>user</name>
<value>user</value>
</parameter>
<parameter>
<name>password</name>
<value>password</value>
</parameter>
<parameter>
<name>serverName</name>
<value>localhost</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>russelldb</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/russelldb?autoReconnect=true&useUnicode=true&characterEncoding=UTF8</value>
</parameter>
<parameter>
<name>autoReconnect</name>
<value>true</value>
</parameter>
<parameter>
<name>useUnicode</name>
<value>true</value>
</parameter>
<parameter>
<name>characterEncoding</name>
<value>UTF8</value>
</parameter>
<parameter>
<name>max-connections</name>
<value>50</value>
</parameter>
<parameter>
<name>min-connections</name>
<value>2</value>
</parameter>
<parameter>
<name>inactivity-timeout</name>
<value>30</value>
</parameter>
<parameter>
<name>wait-timeout</name>
<value>30</value>
</parameter>
</ResourceParams>