连接池溢出以及大量查询系统表的问题

​ 最近一个月已经出了三次宕机事故了
第一次因为程序有漏洞,一个搜索的程序,一旦缓存失效,会全表扫描数据库并排序.
由于没有做好并发控制,一旦缓存失效,会造成大量的全表扫描

第二次因为程序设计有漏洞。每个用户的空间,都有访问次数,每个PV都会在数据库产生一个update +1 的SQL.
这个设计的雷,埋了很长时间了,大家心里都清楚.就是没有动力去改.
平时这个漏洞也有,被大量访问,会造成几分钟不能响应,但是卡一会儿就好了.
直到这次,海量的update,直接把所有的连接都堵了.
甚至DBA帐号在第一时间都登录不了..

第三次也是相似的经历,海量的连接直接扑上来,数据库资源瞬间耗尽,回天乏力.


这三次事故有一个共同点,就是来势猛
（除了第一次是因为程序的bug,另外两次也不排除是人为攻击.因为基础架构设计确实有些问题.）
从接到邮件报警,登录服务器,第一时间看到的现象就是
CPU使用率从正常的15%飙升到90%以上.甚至100%.
几千个客户端连接,已经达到了数据库配置的最大连接数.

让我感到慌张的有几点
1.大量的客户端连接达到了数据库配置的最大连接数,所以第一时间,我可能登录不了数据库了.
2.好不容易登录了数据库,非常卡顿.
3.查看正在执行的查询,一屏一屏的SQL,好几千个.引起问题的SQL,往往不是执行最多的那个.要在这种慌乱的情绪中,找到问题SQL,就有点难度了.

总结这三次事故,我发现每次都伴随着海量的连接.但是应用配置了连接池,怎么会动辄产生好几千个数据库连接呢?
回想之前我们反映的问题,Oracle监听器日志显示,每小时大致有12w连接.甚至有ORA_3136 WARNING__inbound_connection_timed_out的报警.但是一直没有给我们反馈.

我猜测是因为程序的连接池出现了泄露。
因为我们的代码构成很复杂.从早期Struts+JDBC+DBCP1.4连接池到后来的Spring+MyBatis+Resin JNDI数据源,都出现了连接溢出的问题.
我当时猜测不是连接池的问题,就是驱动程序bug的问题.
当然经过排查,确实是连接池的问题.有溢出.

我将连接池更换为c3p0 v0.9.5.1之后,大量连接Oracle的问题已经解决.终于监听器日志不再刷了.

隔天,AWR检查,发现一个SQL在4个小时内居然执行196w次.占用数据库负载3%左右.
SELECT NULL AS table_cat,
       o.owner AS table_schem,
       o.object_name AS table_name,
       o.object_type AS table_type,
       NULL AS remarks
  FROM all_objects o
  WHERE o.owner LIKE :1 
    AND o.object_name LIKE :2 
    AND o.object_type IN ('xxx', 'TABLE')
  ORDER BY table_type, table_schem, table_name

刚开始以为是系统被SQL注入了..
因为之前正在排查硬解析过高的问题
后来发现是JDBC Metadata的调用.
但是这个调用的频率,明显异常.
最后发现,原来是我使用c3p0连接池的时候,还需要配置preferredTestQuery参数.
否则,默认测试连接的行为,就是DatabaseMetaData.getTables()这种操作.
preferredTestQuery设置为"select 1 from dual" 问题解决.

原文如下:
http://www.mchange.com/projects/c3p0/#configuring_connection_testing

Configuring Connection Testing

c3p0 can be configured to test the Connections that it pools in a variety of ways, to minimize the likelihood that your application will see broken or "stale" Connections. Pooled Connections can go bad for a variety of reasons -- some JDBC drivers intentionally "time-out" long-lasting database Connections; back-end databases or networks sometimes go down "stranding" pooled Connections; and Connections can simply become corrupted over time and use due to resource leaks, driver bugs, or other causes.

c3p0 provides users a great deal of flexibility in testing Connections, via the following configuration parameters:

• automaticTestTable
• connectionTesterClassName
• idleConnectionTestPeriod
• preferredTestQuery
• testConnectionOnCheckin
• testConnectionOnCheckout

idleConnectionTestPeriod, testConnectionOnCheckout, and testConnectionOnCheckin control when Connections will be tested. automaticTestTable, connectionTesterClassName, and preferredTestQuery control how they will be tested.

When configuring Connection testing, first try to minimize the cost of each test. If you are using a JDBC driver that you are certain supports the new(ish) jdbc4 API — and if you are using c3p0-0.9.5 or higher! — let your driver handle this for you. jdbc4 Connections include a method called isValid() that should be implemented as a fast, reliable Connection test. By default, c3p0 will use that method if it is present.

However, if your driver does not support this new-ish API, c3p0's default behavior is to test Connections by calling the getTables() method on a Connection's associated DatabaseMetaData object. This has the advantage of being very robust and working with any database, regardless of the database schema. However, a call to DatabaseMetaData.getTables() is often much slower than a simple database query, and using this test may significantly impair your pool's performance.

The simplest way to speed up Connection testing under a JDBC 3 driver (or a pre-0.9.5 version of c3p0) is to define a test query with the preferredTestQuery parameter. Be careful, however. Setting preferredTestQuery will lead to errors as Connection tests fail if the query target table does not exist in your database prior to initialization of your DataSource. Depending on your database and JDBC driver, a table-independent query like SELECT 1 may (or may not) be sufficient to verify the Connection. If a table-independent query is not sufficient, instead of preferredTestQuery, you can set the parameter automaticTestTable. Using the name you provide, c3p0 will create an empty table, and make a simple query against it to test the database.

The most reliable time to test Connections is on check-out. But this is also the most costly choice from a client-performance perspective. Most applications should work quite reliably using a combination of idleConnectionTestPeriod and testConnectionOnCheckin. Both the idle test and the check-in test are performed asynchronously, which can lead to better performance, both perceived and actual.

For some applications, high performance is more important than the risk of an occasional database exception. In its default configuration, c3p0 does no Connection testing at all. Setting a fairly long idleConnectionTestPeriod, and not testing on checkout and check-in at all is an excellent, high-performance approach.

It is possible to customize how c3p0's DefaultConnectionTester tests when no preferredTestQuery or automaticTestTable are available. Please see Configuring DefaultConnectionTester.isValidTimeout and Configuring DefaultConnectionTester.QuerylessTestRunner.

Advanced users may define any kind of Connection testing they wish, by implementing a ConnectionTester and supplying the fully qualified name of the class as connectionTesterClassName. If you'd like your custom ConnectionTesters to honor and support thepreferredTestQuery and automaticTestTable parameters, implement UnifiedConnectionTester, most conveniently by extending AbstractConnectionTester. See the api docs for more information.

If you know you want to use the jdbc4 Connection.isValid() method, but you want to set a timeout, consider writing a trivial extension of IsValidConnectionTester.

package com.mchange.v2.c3p0.example; import com.mchange.v2.c3p0.util.IsValidOnlyConnectionTester; public final class IsValidOnlyConnectionTester30 extends IsValidOnlyConnectionTester { protected int getIsValidTimeout() { return 30; } }

See? These really are trivial to write.

Simple advice on Connection testing

If you don't know what to do, try this:

If you know your driver supports the JDBC 4 Connection.isValid(...) method and you are using c3p0-0.9.5 or above, don't set a preferredTestQuery. If your driver does not support this method (or if you are not sure), try SELECT 1 for your preferredTestQuery, if you are running MySQL or Postgres. For other databases, look for suggestions here. Leave automatedTestTable undefined.

Begin by setting testConnectionOnCheckout to true and get your application to run correctly and stably. If you are happy with your application's performance, you can stop here! This is the simplest, most reliable form of Connection-testing, but it does have a client-visible performance cost.

If you'd like to improve performance by eliminating Connection testing from clients' code path:

1. Set testConnectionOnCheckout to false

2. Set testConnectionOnCheckin to true

3. Set idleConnectionTestPeriod to 30, fire up you application and observe. This is a pretty robust setting, all Connections will tested on check-in and every 30 seconds thereafter while in the pool. Your application should experience broken or stale Connections only very rarely, and the pool should recover from a database shutdown and restart quickly. But there is some overhead associated with all that Connection testing.

4. If database restarts will be rare so quick recovery is not an issue, consider reducing the frequency of tests by idleConnectionTestPeriod to, say, 300, and see whether clients are troubled by stale or broken Connections. If not, stick with 300, or try an even bigger number. Consider settingtestConnectionOnCheckin back to false to avoid unnecessary tests on checkin. Alternatively, if your application does encounter bad Connections, consider reducing idleConnectionTestPeriod and set testConnectionOnCheckin back to true. There are no correct or incorrect values for these parameters: you are trading off overhead for reliability in deciding how frequently to test. The exact numbers are not so critical. It's usually easy to find configurations that perform well. It's rarely worth spending time in pursuit of "optimal" values here.

So, when should you stick with simple and reliable (Step 2 above), and when is it worth going for better performance (Step 3)? In general, it depends on how much work clients typically do with Connections once they check them out. If clients usually make complex queries and/or perform multiple operations, adding the extra cost of one fast test per checkout will not much affect performance. But if your application typically checks out a Connection and performs one simple query with it, throwing in an additional test can really slow things down.

That's nice in theory, but often people don't really have a good sense of how much work clients perform on average. The best thing to do is usually to try Step 3, see if it helps (however you measure performance), see if it hurts (is your application troubled by broken Connections? does it recover from database restarts well enough?), and then decide. You can always go back to simple, slow, and robust. Just set testConnectionOnCheckout to true, testConnectionOnCheckin to false, and set idleConnectionTestPeriod to 0.

But do, always, be sure that your tests themselves are performant, either because your JDBC driver supports Connection.isValid(...) or because you have set an efficient preferredTestQuery !!!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1815054/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1815054/