Dear All I had gone through this article but not I could not locate file called '/proc/user_beancounters' on my Centos 6.2, is there any standard calculation inorder to set my.cnf based on avaiable ram to increase performance of query.
If there's any method, then say I got 8GB RAM, then what will be the configuration of my.cnf for both MyISAM and Innodb for the best performance using available RAM ?
I had to look up /proc/user_beancounters, and it seems to be an extension to Linux implemented for OpenVz. I doubt it would be present in plain CentOS Linux.
First of all, I recommend you don't use MyISAM. It is not an ACID storage engine, and it uses table-locking. The only benefit of MyISAM that it may store data in less disk space than InnoDB (depending on your table structure).
There is no magic tuning value that makes all workloads optimal. If there were, they wouldn't be tunable options, they'd just be pre-set at the right values.
It's typical that you have more data than can fit in available RAM, so the compromise is to set the innodb_buffer_pool_size as high as you can spare, given other uses of RAM on your server. For example if you have Apache or a Java app or memcached or other users of RAM, make sure they have what they need.
The queries you need to run against your data is an important consideration, too. If you have lots of queries that scan all the way through your tables, then it's hard to keep them in the buffer pool. But typical OLTP workloads settle on "hot spots" and query 20% of the data 80% of the time. Caching helps a lot here, because the most frequently-requested data gets cached.
It's harder to optimize for OLAP workloads, where your queries may scan across a great deal of your database, and your database is larger than available RAM.
So basic guidelines are:
innodb_buffer_pool_size. People often guess at 80% of available RAM. Keep in mind that the buffer pool uses about +10% over the value you set.