Re: [CDBI] Make CDBI go fast

On 2/15/07, Michael G Schwern <[email protected]> wrote:
> They're having to write far too much SQL by hand to be performant.

We do that a lot too.

> MySQL means views are to be avoided (they're really inefficient), no custom types, no custom constraints, crappy foreign key constraints and crappy transactional isolation.  Can you tell I've been using Postgres lately?

I find the foreign key constraints and transaction isolation in MySQL
InnoDB to be quite good, except that you have to dig for the exact
error message when you hit a constraint.  By default, MySQL runs at a
safer isolation level than Postges.  We changed that from
"REPEATABLE-READ" to "READ-COMMITTED" because the former tends to be
confusing for web applications with persistent db connections.

> Its a large, normalized schema on the order of 100+ tables.  Its replicating existing business logic which cannot easily be changed so its a bit crazy in places.  Some tables have in the order of a million+ rows.

That shouldn't be a problem, except when you execute a query that
matches 500,000 rows and DBD::mysql tries to load them all into
memory.  That's when you need to set "mysql_use_result".

> Performance is generally good with most pages coming back in less than 4 seconds but some can take hundreds.

I doubt that the kind of changes you've talked about so far will make
the difference between 10 seconds and hundreds of seconds.  That kind
of change usually happens at the SQL/schema level.  It should be
pretty easy to find out though, by profiling.

One thing that I noticed when looking at how our CDBI app performs is
the way the database handles are always asked for with a method call,
even in places where passing them would be really easy.  Many tiny
methods in CDBI need a handle for something, and call db_Main, causing
a $dbh ping.  I haven't checked how these add up in the profiler
though.

- Perrin