Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

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 superuser.com May 27 '11 at 10:06

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

add comment

2 Answers 2

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

http://www.serveradminblog.com/2011/03/tuning-mysql-performance-howto-part-1/

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 mysqlperformanceblog.com, 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
add comment

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
add comment

Your Answer

 
discard

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.