I have 4 sites that all use 1 database. But 2 weeks ago mysql started to use up to %25 of 2GB RAM. And also queries started to run slowly. So after a few hours server dies as there become lots of unresponded apache threads. I have indexes etc. for the tables. I cant find why it uses so much memory and responds so slowly. The tables have 200K~ rows. I need to fix it because they will have 1000K soon. I'll be happy if you can help me to fix that problem. Thanks a lot for your replies...
noticed yesterday that mysql used a lot of resources, like more than 34.000 (!!!) handles (skype and explorer have ~600, average is ~300 for "normal applications"). seems like it only happens on the newer mysql version, one of the old 4.x mysql servers dosnt has this problem.
Have you tried enabling mysql query cache? It might help you in stopping the excessive use of resources by mysql.
Are you pegging any tables or anything like that? Running lots of transactional code? I've managed 230,000,000 rows in a SQL Server (msft) with 4 GB of ram. You should be okay in that regard with good code and a healthy server.