How to adjust MySQL performance

Question

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.

Answer 1

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/

Answer 2

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)