I'm trying to workout a managed backup scheme on a MySQL production database with XtraBackup. According to our DBA, XtraBackup doesn't lock the database, but issues a series of SHOW TABLE STATUS commands and then works on the file system level.
Still, just as XtraBackup runs my logs start to blow up with connection pool errors: Error getting database connection:[http-nio-8080-exec-5] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:100; busy:100; idle:0; lastwait:10000]. There's some suggestion that turning off 'innodb-stats-on-metadata' might help so we're trying that. At the same time I want to confirm my connection pool settings are good: <Resource name="jdbc/mydatabase_master" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" testWhileIdle="true" testOnBorrow="true" testOnReturn="false" validationQuery="SELECT 1" validationInterval="30000" timeBetweenEvictionRunsMillis="30000" maxActive="100" minIdle="10" maxWait="10000" initialSize="10" removeAbandonedTimeout="60" removeAbandoned="true" minEvictableIdleTimeMillis="30000" jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState; org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer" username="db_user" password="mypassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://my.ip:3306/my_database" /> For everything besides the backup, this configuration works very well. For most of our traffic, the connection pool size hovers around the initialSize. It seems like setting the maxActive higher will just spawn new connections in the pool that are hanging. Anything strike you as..."too tight" for lack of a better description, that would force the pool to continuously add connections when the backup runs? Anything else seem like it could be tweaked in relation to XtraBackup? TIA, John