Hi, I have a server doing 26,000 queries a second so thought it was about time to enable caching. Did some research with MySQL 4 and got it to work. After running it for a while it turned out that more than 70% of queries were coming out of cache (straight out of memory & not hitting the disk (disk has always been the bottleneck)) which is nice!
I used the following entries in my.cnf set-variable = query_cache_type=1 set-variable = query_cache_size=64M And it works! Hope this helps. Cheers, Andrew sql,query -----Original Message----- From: Blaster [mailto:[EMAIL PROTECTED]] Sent: 10 January 2003 17:23 To: [EMAIL PROTECTED] Subject: Enabling Cache feature in MySQL 4 Hello I've been running MySQL 3.23.49 as my DB for my webpage since up till now. The number of visitors have been increasing, and the poor box is currently running at 100% load, 24/7, which can't be healthy, and the page is getting slower to load. I've checked all my queries and tried to optimized them as much as possible, but I think the box is the real limiter, it hosts both Apache Webserver and MySQL with 400 Mhz of CPU speed and 192 MB of SDRAM -- not much, I know =) So, I visited the chat the otherday, and they suggested upgrading to MySQL 4 and enable the Caching feature, and after reading the stuff about it, I was really happy since my page is built in a way that queries are called often, but usually the same query, in short, the cache should be an optimal solution. Happy as I was, I pulled out a test box, installed Linux Debian on it, downloaded MySQL 4 on it, everything worked smooth, now I wanted to enable the cache feature, but it didn't let me! I want the change to be permanent, so I looked up the doc pages at mysql.com, and found info about the /etc/my.cnf file, which I created and put in: [mysqld] set-variable = query_cache_size = 67108864 The MySQL deamon was restarted, but when I checked the run-time variables with SHOW STATUS, query_cache_size was still set to 0? I even tried rebooting the entire machine after the changes to my.cnf, but it wouldn't work -- as if MySQL ignored my file? Now, I was thinking, either MySQL reads the setting from somewhere else in my system, but since I cannot find a "config file" variable in SHOW STATUS, I cannot find out from where? Another possibility is that it does read it, but for some reason ignores it, or even worse, reads a 2nd config file and the setting is overriden? Now, this was quite odd I thought, so I tried to set the cache manually with a query, "SET GLOBAL query_cache_size=67108864" and that worked like a charm, it even updated "query_cache_type" to 1, which, according to the manuals mean ON. Now, I was wondering if any of you have a hint on what might be wrong? Also, as a little sidenote, I'm kinda wondering, what is a _decent_ size for the Cache buffer? is 64 Mb enough? I'm getting an upgraded server aswell, it will be 800 Mhz and have 256 MB of ram, this box will be 100% MySQL and I'll leave the Webserver on the old 400 Mhz. Any other hints on general optimizing is appreciated! Sorry if this post got too long, this is my first time posting on this list. Take it easy on my poor english skills =) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
