Setting up lightweight validationQuery for MySQL

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

 

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Fisheye および Crucible は除く

問題

To avoid the impact of creating a new database connection for each database access request made by Application (JIRA, Confluence, Stash), a pool of pre-established database connections is maintained. As part of this we need to make sure that connection inside the pool are validated. This needs to be done in order to prevent connections in pool to be broken, which may occur after database server reboots or a network failure. There is a lightweight validation query which can be used with MySQLConnector/J

The following appears in the tcpdump

17:36:54.292582 IP localhost.59437 > localhost.mysql: Flags [P.], seq 1272:1285, ack 846, win 522, options [nop,nop,TS val 311544791 ecr 311523797], length 13
......y.	....select 1

診断

環境

  • Database MySQL, JDBC Mysql Connector/J >= 5.1.3

原因

Using connection pool without validation query may lead to broken connection and application will not be able to survive connection closures. Using usual validation query like 'select 1' will create higher load on MySQL server as it needs to parse request. 

ソリューション

Enable lightweight validation-query in the application database settings (please check relevant documentation for that).

To use this feature, specify a validation query in your connection pool that starts with /* ping */. Note that the syntax must be exactly as specified.

Configuration example for JIRA:

dbconfig.xml
    <validation-query>/* ping */ select 1</validation-query>    
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
    <validation-query-timeout>3</validation-query-timeout>

関連コンテンツ

データベース接続のチューニング

http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html

Last modified on Mar 30, 2016

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.