SEO

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)

--
MfG, Christian Welzel aka Gawain@Regenbogen

GPG-Key: http://www.camlann.de/key.asc
Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

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.

Hi Dave,

> I'd hazard a guess that
> your PHP app is the only thing connecting to the database?

Yes, it is.

> I'd also guess that
> you're not using connection pooling / persistent connections?

Correctly. I dont know if the php-db2-driver supports this, but i
investigate this.

> 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.

I activated the database and now the requests are handled very
fast. Seems this solved my problem at a first step.
As we are in a very early state of development, this solution is
ok for me, but for later use i will look for some pooling code
as you suggested.

--
MfG, Christian Welzel aka Gawain@Regenbogen

GPG-Key: http://www.camlann.de/key.asc
Fingerprint: 4F50 19BF 3346 36A6 CFA9 DBDC C268 6D24 70A1 AD15

Christian Welzel wrote:

> Hi Dave,
>
> > I'd hazard a guess that
> > your PHP app is the only thing connecting to the database?
>
> Yes, it is.
>
> > I'd also guess that
> > you're not using connection pooling / persistent connections?
>
> Correctly. I dont know if the php-db2-driver supports this, but i
> investigate this.

I'm no PHP expert (I've never used it heavily), but it looks like
there's a db2_pconnect() function which might be handy:

http://php.net/manual/en/function.db2-pconnect.php

>
> > 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.
>
> I activated the database and now the requests are handled very
> fast. Seems this solved my problem at a first step.

Excellent, glad to hear it

> As we are in a very early state of development, this solution is
> ok for me, but for later use i will look for some pooling code
> as you suggested.

I'd recommend connection pooling / persistent connections as a long
term solution simply because if you have to reboot the server at some
point (or restart DB2, or whatever) the database will be inactive again
and performance will suffer until ACTIVATE DB is performed again. You
could always set up a little script to automatically activate the
database on reboot or something similar, but I feel persistent
connections are a "cleaner" solution.

Of course, in some cases ACTIVATE DB is the only (easy) solution. For
example, CGI applications which don't persist between requests can't
hold a persistent connection (things like SQLRelay are an alternative
solution, although rather more complex, in such circumstances).


Cheers,

Dave.