Ubuntu – How to adjust MySQL performance

connectionMySQLperformance-tuningUbuntu

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.

Best Answer

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/

Related Topic