I want to get your opinions on how to increase available/free memory and
performance on a heavy volume database server.
I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using
same MySQL version.
Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap
space) started to suffer from memory outages because of heavy load.
During day available free memory is changing from 200Mb to 5Mb and when
available memory reaches to 5Mb MySQL starts to give 'Too many
connections' messages. Db server is working with 45-70 query/second and
more than 25,712 connection per hour. There are active 10-13 threads
To increase available free memory I've did the following :
1- Optimized all tables
2- Removed unneccessary/old indexes
3- Moved unused databases to replication server
4- Increased key_buffer_size from 8Mb to 128Mb
5- Have increased max_connection from 100 to 150
6- Have increased thread_cache to 5
This changes helped a bit but still memory is a problem for MySQL. What
should I do to prevent 'too many connections' messages and have more
memory available on database servers? Should I remove more indexes from
tables? Should I increase key_buffer_size to 256Mb or more?
Key_buffer_size doesn't look like a problem since key efficiency looks
100% most of the time.
Thank you for your time