My short answer is "gee, I don't know", but since no one else has answered yet, I'll throw some thoughts out:
1. Have you looked into MySQL replication? You might be able to run duplicates of the database locally on your webservers. (Note: I've heard that MySQL replication is flaky, but then I'm a postgresql snob -- nearly everything related to MySQL seems a little flaky to me.)
2. Have you looked for MySQL administration tricks to speed up the database? Some years back, Tim Perdue tried to benchmark MySQL against PostgreSQL and came to the conclusion that despite it's reputation MySQL wasn't particularly fast. The response of the MySQL camp was that he just didn't know what he was doing, the database wasn't tuned right, etc. This would seem to indicate there are some tuning secrets that the insiders know, so what are they?
3. Maybe you need to think about how you're using Class::DBI. I've been looking at it recently, and my first take is that it makes OOP persistance easy to do at the expense of crippling the database access. It used to be considered a rank newbie mistake to do multiple selects instead of one that uses a table join, but Class::DBI encourages you to do just that. You might want to look into writing some custom queries to dodge around Class::DBI bottlenecks.
4. Your ideas about using Cache::Cache or Class::DBI::Cacheable also sound reasonable, but I don't know what to say about your update problem. The kind of thing I usually see done is have the cache expire after a certain fixed period of time, but if you can't live with the data being stale for any substantial length of time that obviously doesn't work for you.

Hopefully someone else will have something more definite for you (though I predict you're going to hear some advice about doing profiling to make sure you know where the bottlenecks are).

Maybe you need to think about how you're using Class::DBI. I've been looking at it recently, and my first take is that it makes OOP persistance easy to do at the expense of crippling the database access. It used to be considered a rank newbie mistake to do multiple selects instead of one that uses a table join, but Class::DBI encourages you to do just that. You might want to look into writing some custom queries to dodge around Class::DBI bottlenecks.

I see that dragonchild has already written extensively about this problem:

OO concepts and relational databases.

He doesn't name Class::DBI until late in the article, but Class::DBI definitely falls into the class of things under discussion.

One solution, assuming you are already caching connections (i.e. mod_perl or some such), is to use the temporary, in-memory tables in SQL::Statement (available to any of the DBDs which subcalss it - AnyData, CSV, File, DBM, etc.). Basically you can then query the cache with the same SQL you use to query the full db and you can switch back and forth between them. Pair Networks, our kind host, uses this kind of caching for some of their databases and Dan Wright, their lead software developer will be talking about that and AnyData at the upcoming YAPC in Toronto. As for updating, the in-memory tables are updateable, so you can go in either direction (refresh the cahce from the db or refresh the db from the cache).

Just a couple of thoughts/suggestions:

• Have you benchmarked your application? You describe it as 'sloooow' but do you *know* why it's slow or are you just assuming Class::DBI?
  I'm not denying that Class::DBI will be slower than using straight DBI but in most cases this doesn't matter. And where it does matter, you can step outside of C::D and just use DBI and SQL. Also, there are many things that if done poorly with C::D can really cause performance hits (like sorting a result set in perl and not in your SQL). This is where profiling and benchmarking can really help. You would know exactly why it's 'sloooow'.
• Are you using a custom db_Main()? It is sometimes usefult to override this method but you must be very careful since it's called *alot*. You must be careful that speed is always considered when changing it.
HTH

I'm not denying that Class::DBI will be slower than using straight DBI but in most cases this doesn't matter.

Hm... "most cases". What kind of "most cases" are you thinking of? Have you used Class::DBI on a heavily loaded web site, for example?

And where it does matter, you can step outside of C::D and just use DBI and SQL.

Do you have any suggestions on how you would find out when it matters? Any hints on profiling a Class::DBI app?

Hm... "most cases". What kind of "most cases" are you thinking of? Have you used Class::DBI on a heavily loaded web site, for example?

"most cases" == most websites. Most websites are not heavily loaded. But even on heavily loaded websites, it's still usually a minority of the code that gets hit the most.

Do you have any suggestions on how you would find out when it matters? Any hints on profiling a Class::DBI app?

Find the most visited portions of the application (that's what server logs are for) and then profile it just like you would any normal web application. Like this for instance.

I think you need to profile the whole system first to find out exactly what is causing the slowness. It's hard to recommend a solution without knowing for sure what the problem is.

Just wanted to make sure you've seen Comparison of different PERL caching modules, which indicates BerkeleyDB may be significantly faster than Cache::FileCache (of Cache::Cache fame).

The main downside is your cache is not cross-platform or in my case even cross-perlversion. But for a cache this should be no problem.

MySQL replication has worked well for me. i.e. use the replicated version of the database rather than the overloaded primary version.
MySQL 4.x offers query caching and that has worked *really* well for me on a site with few updates and lots of reads.
Update:Gah! Just noticed your paragraph about not changing MySQL version - which renders my comment above pointless.

OK, thanks all for your answers. Profiling/looking at performance is indeed something that had to be done in this case, and I know of several places where I can improve my code, but not without a lot of effort, and ugly hacks. Right now my code is quite tidy, and has a nice layering (class-dbi modules/API so other users can use my database too without a lot of hassle, and a cgi-layer that uses template toolkit to generate HTML), and I'd like to keep it maintainable like that.

I've profiled some, a typical result of dprofpp -r is below. Seems my main problem is the SQL part of this, so either I have to be smarter in my queries (or smarter in how I let Class::DBI make queries) or ... cache?!

Total Elapsed Time = 19.73262 Seconds
         Real Time = 19.73262 Seconds
Exclusive Times
%Time ExclSec CumulS #Calls sec/call Csec/c  Name
 17.9   3.532  3.532   2933   0.0012 0.0012  DBI::st::execute
 15.4   3.041  3.041 119885   0.0000 0.0000  Class::Accessor::get
 13.2   2.610 26.949 126519   0.0000 0.0002  Class::Accessor::__ANON__
 8.38   1.654  1.721  49101   0.0000 0.0000  Class::Data::Inheritable:
+:__ANON__
 8.18   1.614  5.405   8107   0.0002 0.0007  Class::DBI::_init
 7.31   1.443  6.437 106803   0.0000 0.0001  Class::DBI::Column::name_
+lc
 6.80   1.342  1.342   2931   0.0005 0.0005  DBI::db::ping
 6.37   1.256  1.667  13440   0.0001 0.0001  Class::DBI::_attribute_st
+ore
 6.05   1.193  2.621   2890   0.0004 0.0009  Class::DBI::transform_sql
 5.78   1.140  7.540 106149   0.0000 0.0001  Class::DBI::Column::__ANO
+N__
 5.38   1.061  9.354  16177   0.0001 0.0006  Class::DBI::Relationship:
+:HasA::__
                                             ANON__
 4.49   0.886 24.001   2730   0.0003 0.0088  Class::DBI::_flesh
 4.41   0.871  1.783  16893   0.0001 0.0001  Class::DBI::_attrs
 3.04   0.599  4.327   2932   0.0002 0.0015  DBIx::ContextualFetch::st
+::_untain
                                             t_execute
 3.01   0.593 26.163   5980   0.0001 0.0044  Class::DBI::get
 2.94   0.581  1.187  19511   0.0000 0.0001  Class::DBI::ColumnGrouper
+::group_c
                                             ols
 2.69   0.530  1.367  14215   0.0000 0.0001  Class::DBI::_attribute_ex
+ists
 2.64   0.520  1.085  16533   0.0000 0.0001  Class::DBI::ColumnGrouper
+::primary
 2.37   0.467  0.467  10984   0.0000 0.0000  Class::DBI::Column::group
+s
 2.36   0.466  4.993   5822   0.0001 0.0009  Ima::DBI::__ANON__
 2.27   0.447 10.095   5472   0.0001 0.0018  Class::Trigger::call_trig
+ger
 2.09   0.412  2.102  16345   0.0000 0.0001  Class::DBI::primary_colum
+n
 1.89   0.372  1.013   5460   0.0001 0.0002  Class::DBI::ColumnGrouper
+::_uniq
 1.76   0.347  1.108   5527   0.0001 0.0002  Class::DBI::meta_info
 1.62   0.320  0.319     11   0.0291 0.0290  Template::Parser::BEGIN
 1.56   0.308  0.308  12454   0.0000 0.0000  DBI::common::FETCH
 1.51   0.298  4.476   2730   0.0001 0.0016  DBIx::ContextualFetch::st
+::select_
                                             row
 1.37   0.270  1.193   6075   0.0000 0.0002  Class::DBI::_find_columns
 1.27   0.250  0.310   3070   0.0001 0.0001  DBIx::ContextualFetch::st
+::fetch
 1.23   0.242  0.730    103   0.0023 0.0071  base::import
[download]