Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm facing a real problem with MySQL instance for a service online. We use hibernate and c3p0 pool with these settings:


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

Full my.cnf

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

cat /proc/cpuinfo

*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.

share|improve this question

migrated from May 27 '11 at 10:06

This question came from our site for computer enthusiasts and power users.

Mysql tuning can be achieved with several scripts..

Mysqltuner, mytop, mysqlreport, tuning-primer... Those scripts will give you the detailed informations about your server...

For examples check the next link

share|improve this answer
thank you for your contribution, i'm reading – black sensei May 27 '11 at 11:22
Those are useful scripts to pinpoint problem areas. Just make sure you always understand the suggestions before you implement them. For example, it's easy to make MySQL use more memory than your server has available, making it unstable. – Martijn Heemels May 28 '11 at 0:33
Another useful site is, which has many articles on tuning MySQL for various workloads. – Martijn Heemels May 28 '11 at 0:33
thank you for all your contribution.will closely look at those. – black sensei May 28 '11 at 8:07

Optimization is a never-ending quest, but here are a few things that may help:

  1. Increase max_connect_errors ( I use 10, but you could try more)
  2. Increase table_open_cache: 2048 is what I use, but that depends on the number of tables in your app.
  3. Use all ip addresses in your grant statements, and disable DNS: [mysqld] skip-name-reslove = 1
  4. Definitely increase query_cache_size (ie 64 MB)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.