You have 90,000 tables in your DB server. Increasing RAM certainly helps you to overcome from this situation. Using Disks, swapping and open/close tables are negative points for DB performance. When you see this issue in your DB server, please execute
show open tables; statement and list down the tables. You can repeat it multiple times and compare the list to find out which tables are repeatedly open. After this, you can analyze the reasons behind this.
Only increasing hardware is not necessary, its full utilization is also an important factor which is totally depends upon the software and its configuration. Regarding hardware, you have never mentioned configuration of your Database server. Regarding software, you are using almost updated version of MySQL. Now, you have to consider on tuning the MySQL configuration parameters. Before starting parameters tweaking of your MySQL, Please refer below URL:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html. Almost every parameters are important while some of them are very important for performance improvement. To know the values of all parameters, please execute
SHOW VARIABLES; statement. Below are the few important variables:
In the same way, you must know the Global status variables of MySQL. Please refer URL:
http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html. To check the values of these variable value, please run
show global status; statement. Also, note that values of status variable vary time to time. You must note down the value of some status variables during peak time and analyze how much it is different than usual hour. Few of them are below:
There are multiple factors behind this likewise
Your hardware resources - RAM, CPU, Disks
Database engine of your tables - MyISAM, InnoDB
How much Index created in your tables?
You have to find out how many tables are using InnoDB engine and how much are using MyISAM. In the same ratio, you should divide the RAM. MySQL uses different-different parameters for both of the engines. These variables are prefix with Myisam and innodb respectively.
Disk I/O should be reduced as much as we can. By better allocation of RAM resources, we can do this. It may also be possible that Queries involved in the application are not utilizing indexes which tends to full table scan. By creating indexes in proper way, designing the database schema and write proper queries are very helpful in the improvement of system performance.
Hope above information would help...