My server memory just reached 85% and my sites keep crashing because their are too many MySQL connections. I was told that query caching can lower the memory usage by MySQL but when I search for tutorials how to do this I find nothing useful for starting out. I just sent the query_cache_type to DEMAND and so that the query caching only effects my sites and not the ones owned my the person I share my server with. But I need to know some things. When I do a mysql query and demand that is it cached do I only do that for the first time? Or do I just have all my querys use the QUERY CACHE command and it won't overwrite the previous cache? Also when do the cached results update themselves. I am thinking I want them to overwrite the old cached ones every 6-12 hours.
Whenever you demand the cache, it will serve the data from cache otherwise it will fetch the data from table. Cache is updated for a table when that table is changed (INSERT, UPDATE or DELETE operation on that table). Did you properly INDEX your tables or use EXPLAIN to debug the performance of your queries? Two must see links http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
thanks. I added a primary key to all my tables. My server admin just came on and advised me to go with APC instead of query caching based on his previous experiences with it and my type of site.
Adding only primary keys may not be useful, you also need to create indexes on the columns on which you JOIN tables. How much traffic do you have on your site?
the problem has been fixed. My server was under attack by someone and I added APC caching. I will look into indexes also but I never don't ever use the JOIN command when calling the DB. Thanks
Just to take a sidestep from the querycache answers, but do you actually know if those connections are doing anything. Because chances are that you are running out of connections due to one of two issues. First, either your DB is not responsive enough so the load builds up, in that case query_cache might help, but first check the type of queries you are doing and how heavy they are, Mysql for example has a slowlog that you can consult to help find unresponsive queries. Second if you are using persistent connections, and have a large number of spare http daemons, an/or multipe code bases then you will find that persistent connections well they persist a long time In that case a connection pool might be more efficient.