I have a website that gets 100,000 HITS a day. I love the cache features in ADOdb, it stores the resultsets in files and categorize them by cache lifetime and query which produced them. (This is how I THINK it works :)
Anyways, I can cache my website every hour, and thus amazingly decrease all the MySQL calls, which will be redirected to the filesystem, where the cache is stored.
Anyways. How come it wasn't included in ADOdb-Lite? CacheExecute, CacheGetOne, and all those good stuff? :(
If you are experiencing that much load then enabling ADOdb's Caching is probably what is causing all of your problems. ADOdb's Cache software is incredibly slow and should only be used when the database server it is connecting to is very, VERY slow or heavily overloaded.
You said you had the website and the database server on the same server box. ADOdb's Cache routines take MANY TIMES more CPU cycles than the straight mysql queries to the database. By using ADOdb's Cache you are actually putting a much greater load on the server. You can reduce this load somewhat by using Zend Optimizer, MMCache or eAccelerator but even then using ADOdb's Cache will be slower than not using it. The ADOdb Cache should only be used if the database server is running on a server with a very, very SLOW processor (IE less than 500mhz) and the database tables being cached have hundreds of thousands or millions of records. Then it can be useful but only if the cached record sets have a few hundred records or less in them.
If you want to really speed things up and lower your CPU load you would be better off enabling the Internal Intelligent Query Cache that Mysql provides. This Query Cache is disabled by default and you need to enable it in the my.cnf and restart Mysql. The variable you need to change is query_cache_size. This is normally set to 0. This is the number of bytes you want to allocate for the Query Cache. The average system should set this to 16 meg but yours should probably use 32 meg or more. This is an INTELLIGENT query cache unlike the one used in ADOdb. All table queries and their results are stored in memory. If any table is changed with an update, delete or anything else then the cached queries are flushed from memory. If the cache becomes full then the queries that haven't been used for the longest time are flushed. It is many, MANY times faster than any method written in PHP.
We enabled this on one of our servers that has 4 websites on it and the database load dropped by over 60%. If we used a software cache like the one from ADOdb the database server load would have dropped a LITTLE but the httpd load for PHP would have greatly increased.
The ADOdb Cache shouldn't be used except in EXTREME cases where the database server is incredibly slow or overloaded to the point where it takes seconds to get your data.
This is one reason why we haven't created a caching module for ADOdb Lite based off the one used in ADOdb. We will eventually create one but it will NEVER be as fast as using normal ADOdb function calls except in extreme circumstances. Even the author of ADOdb doesn't recommend caching be used for everything. :)