I'm facing a real problem with MySQL instance for a service online. We use hibernate and c3p0 pool with these settings:
c3p0.max_size=200 c3p0.min_size=5 c3p0.timeout=60000 c3p0.max_statements=20 c3p0.idle_test_period=300 c3p0.acquire_increment=3 c3p0.unreturnedConnectionTimeout=70000
I've noticed that th service(web site) was down as in it's refusing connection. I log in the MySQL console and
show processlist showed at list 30 sleeping(command) connections to the concerned database. And that makes me worry. We were expecting 200 connections everytime and enabled the MySQL max connection to be 300. Here is the my. Cnf
key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 max_connections = 300 #table_cache = 64 #thread_concurrency = 10
I did a small reseach and I see that the table_cache size has to be updated accordingly. The machine is a quad core machine running ubuntu 9.10 server 4gb ram
*Question 1: * How to force the c3p0 to release idle connections.
*Question 2: * Which tache_cache and all other parameters I need to fix to allow the server to use its full potential and not refusing connections
*Question 3: * After I restarted the Tomcat the MySQL connections dropped to 4. I need to fix this a soon as possible and I need your experience for that. But I would like to acquire that experience too for the time to come. What possible book should you recommend from performance tweaking for MySQL to clustering.