Christian Welzel wrote:
> Hi there
> i set up php+db2+zend+apache2 on my linux server (debian/etch amd) and
> have great problems with the performance of queries. I currently have
> 50 empty tables and 2 filled ones (one with 11 rows, the other with
> 60) if i issue a simple "select * from table1" (11 results), its
> takes up to 6 seconds to get the result. Other dynamic pages, but
> without db access return in 200ms.
>
> Does anybody know if this is typical for an php-app with express-c ?
> Where can i start to tune the system?
>
> (Server is an AMD Athlon 64 X2 6000+, Dual Core with 6gb RAM)
Hmm ... that's too slow to be caused by a lack of statistics on the
tables (given the tiny size of the tables). I'd hazard a guess that
your PHP app is the only thing connecting to the database? i.e. there
are no other connections to the database at all? I'd also guess that
you're not using connection pooling / persistent connections?
If so, you might be running into the "activation" penalty: when a DB2
database has no connections, it's "inactive". When a connection is made
to an inactive database several (expensive) things happen: buffer pools
are allocated, logs are formatted (if necessary), etc. etc. When that
connnection is closed, assuming there are no other connections to the
database, it becomes inactive again.
Hence, if you're not using persistent connections, and there aren't
lots of requests you'll be activating and deactivating the database all
the time (with horrid performance penalties). There's a couple of ways
around this:
1) Use the "ACTIVATE DB dbname" command. This opens a "fake" permanent
connection to the database to keep it in the active state. Subsequent
connections will be made much faster. Note that you'll need to use
"DEACTIVATE DB dbname" before performing any offline tasks (e.g. an
offline backup).
2) A better idea is to change the application to use persistent
connections / connection pooling. However, before doing that I'd use
ACTIVATE DB just to see if this is actually what the problem is.
Cheers,
Dave.
|
