How much RAM can be allocated to an MySQL instance ? I know it is 80% of the physical memory available. Is there a limitation on MySql side, that can take only upto a cartain value ? Assuming i have a box with 64 GB of RAM. So can i allocate 51.2 GB to the MySql instance ?
There are many factors in determining the optimal amount of memory to be allocated, these include the size of your database, the number of operating system processes that will run on the server and the operating system limit on shared memory that can be allocated.
If your database is less than 51Gb then in all likelihood you can reduce the size of the memory allocated without any impact on performance. You need to look closely at the hit rate. The hit rate is a ratio of the number of requests sent to the server and the number of requests found already cached in memory. In a well tuned database you would expect a hit rate above 95% but you would need to look closely at the nature of the requests. Reading from disk is always going to be slower than accessing from memory.
You must remember that each process running on the operating system will also have need of memory. If there are going to be many processes running on the server and there is insufficient memory then it will start swapping out and then the server will start spending more time swapping sleeping processes out and in of memory than actually doing the work.
Finally the memory allocated is shared memory and is limited by the operating system. This is memory that many processes can access simultaneously. There is a limit on the amount of shared memory, on Linux/UNIX this is a OS parameter called shmmax.