Tomcat Connection Pool Problems with XtraBackup

[John Smith <to...@gmail.com>]

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

Re: Tomcat Connection Pool Problems with XtraBackup

[John Smith <to...@gmail.com>]

On Sun, Oct 12, 2014 at 5:56 AM, Stefan Mayr <st...@mayr-stefan.de> wrote:

> Hi John,
>
> Am 11.10.2014 23:30, schrieb John Smith:
>
>> 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.
>>
>> ...
>> Anything else seem like it could be tweaked in relation to XtraBackup?
>>
>> TIA,
>> John
>>
>>
> XtraBackup can operate without locks if your database contains only InnoDB
> tables. For other storage engines like MyISAM it still uses locks. A
> problem we seen is high IO load during XtraBackup. Your DBA could check
> iostats while backup is in progress. If this makes your database server
> unresponsive he could try to throttle the backup:
> http://www.percona.com/doc/percona-xtrabackup/2.2/
> innobackupex/throttling_ibk.html
>
> -Stefan
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Stefan,

Thanks, that seems to be the problem. We have all InnoDB, and the backup
runs without locks, but the it runs under fairly heavy IO load. It seems
like turning 'innodb-stats-on-metadata' to OFF lessened the strain that the
SHOW STATUS queries from XtraBackup caused. Bumping up maxActive allowed
the pool to handle the slowdown of application queries during the backup.
This has worked so far but if it repeats I'll look into the throttling.
That was my first sense, that it wasn't locking, just pushing too hard on
the DB.

Very helpful. Thanks.

-John

Re: Tomcat Connection Pool Problems with XtraBackup

[Stefan Mayr <st...@mayr-stefan.de>]

Hi John,

Am 11.10.2014 23:30, schrieb John Smith:
> 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.
>
> ...
> Anything else seem like it could be tweaked in relation to XtraBackup?
>
> TIA,
> John
>

XtraBackup can operate without locks if your database contains only 
InnoDB tables. For other storage engines like MyISAM it still uses 
locks. A problem we seen is high IO load during XtraBackup. Your DBA 
could check iostats while backup is in progress. If this makes your 
database server unresponsive he could try to throttle the backup: 
http://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/throttling_ibk.html

-Stefan

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org