PHP + Apache + DB2 slow odbc_connect
 

Hi,

I'm using a script with odbc_connect in order to connect to a remote DB2 instance. When the script is executed from command line everything works as expected, but when the same script is executed using Apache web server the call to odbc_connect is extremely slow.

I've compared the php.ini for CLI and for Apache and the configuration of memory and ODBC are the same in both files.

I'm using iSeries driver, PHP 5 and Apache 2.

Does anybody knows how to solve this annoying problem?

I'm assuming you're running on a Windows platform-- which OS version? And what versions of PHP and Apache are you using exactly-- what install packages? Did you match the compilers up between PHP and Apache? (VC6 vs. VC9) That will all have an impact.

I've tested this behavior in Ubuntu 12.10 and also in a Centos server.

Ubuntu configuration:
apache2 -V
Server version: Apache/2.2.22 (Ubuntu)
Server built: Nov 6 2012 20:27:22
Server's Module Magic Number: 20051115:30
Server loaded: APR 1.4.6, APR-Util 1.4.1
Compiled using: APR 1.4.6, APR-Util 1.4.1
Architecture: 32-bit
Server MPM: Prefork
threaded: no
forked: yes (variable process count)
Server compiled with....
-D APACHE_MPM_DIR="server/mpm/prefork"
-D APR_HAS_SENDFILE
-D APR_HAS_MMAP
-D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
-D APR_USE_SYSVSEM_SERIALIZE
-D APR_USE_PTHREAD_SERIALIZE
-D APR_HAS_OTHER_CHILD
-D AP_HAVE_RELIABLE_PIPED_LOGS
-D DYNAMIC_MODULE_LIMIT=128
-D HTTPD_ROOT="/etc/apache2"
-D SUEXEC_BIN="/usr/lib/apache2/suexec"
-D DEFAULT_PIDLOG="/var/run/apache2.pid"
-D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
-D DEFAULT_LOCKFILE="/var/run/apache2/accept.lock"
-D DEFAULT_ERRORLOG="logs/error_log"
-D AP_TYPES_CONFIG_FILE="mime.types"
-D SERVER_CONFIG_FILE="apache2.conf"

php -v
PHP 5.4.6-1ubuntu1 (cli) (built: Aug 22 2012 21:15:19)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies

Well shoot, there goes my theory. :(

Can you narrow down where the bottleneck is-- connecting? fetching? Any errors being reported in Apache error log?

The problem is with the function odbc_connect. Once the connection resource is available everything works fine.

Script execution time from command line (in milliseconds):
odbc_connect : 754 (0.7 second)
odbc_prepare : 104
odbc_execute : 80
odbc_fetch_object : 3
odbc_fetch_object : 0
odbc_fetch_object : 1
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 1
odbc_fetch_object : 0

Script execution time from Apache (in milliseconds):
odbc_connect : 127125 (127.125 seconds)
odbc_prepare : 94
odbc_execute : 96
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 1
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0
odbc_fetch_object : 0

Remember that both executions where done in the same server and the DB2 server is a remote server.

This reeks of communication. And what spot, not sure.
PDO appears to have generics compatible with DB2: http://ca2.php.net/manual/en/ref.pdo-odbc.php, so I'd suggest you establish the connection via PDO to the DB2 db, and see how that goes.
If its still slow via httpd, I'd suggest that its definitely communication. Make sure you are working through local (assuming local yeah?) connections and not attempting to go offsite. Check the iptables, firewall or whatever you are using to block to make sure that its not being bottlenecked there.

If it fixes the problem, I'd suggest an issue in the odbc package itself. In which case I'd suggest not to bother with the odbc. I don't believe that the package is deprecated, but it is getting rather antiquated.

Typically you will see better performance on direct CLI, but not like 1/150th of a difference. More like 20% savings being normal.