Friday Mar 14, 2014

Performance improvement for OCI_RETURN_LOBS in PHP OCI8 2.0.8

Reducing "round trips" to the database server is important for performance and ultimately for system scalability. A server round-trip is defined as the trip from PHP to the database server and back to PHP.

Reducing round trips makes everything more efficient: PHP is faster, there is less network overhead, the database doesn't have to do a context switch or do any processing for you. Applications have some control over round trips, for example by effectively using prefetching or by using an appropriate execute mode to minimize unneccessary rollbacks at the end of a script.

The bug filer of Bug 66875 noticed that PHP OCI8's implementation itself could reduce round trips if a particular LOB column meta data value was cached for the duration of a query instead of being re-requested for each row of the query result set.

So, now with OCI8 2.0.8, you should see a performance increase if you are doing multi-row queries involving LOB columns returned as OCI_RETURN_LOBS:

$s = oci_parse($c, "select mylobcol from mylobtab");
oci_execute($s);
while (($row = oci_fetch_array($s, OCI_ASSOC+OCI_RETURN_LOBS)) !== false) {
    echo $row['MYLOBCOL'], "\n";
}

The bug filer tested the patch and added his performance improvement benchmark results to the bug report. The benefit in your environment will vary greatly with the network setup, schema, and how many LOB columns are queried. Test it out and let me know how the new version helps you.

There is no immediate change for LOBs fetched with OCI-Lob::read() and OCI-Lob::load(). This would require a more complex patch than I want to apply at this time. Queries that don't use LOBs are not affected in any way by the patch.

OCI8 2.0 is included in the forthcoming PHP 5.6 code base. For PHP 5.2 to PHP 5.5 you can install it from PECL. PHP 5.5 RPMs with PHP OCI8 2.0.8 are available from oss.oracle.com.

Finally, if your LOB queries return multiple rows, you might also like this tip to reduce PHP memory usage.

Saturday Sep 21, 2013

Come and Join us in San Francisco this Week

Come and Join us in San Francisco this Week

It's the week of the huge Oracle OpenWorld, JavaOne and MySQL Connect conferences in San Francisco. The week will be a blast with so many things happening.

The easiest way to find sessions is to browse the Content Catalog

Followers of this blog might want to think about these sessions:

There are lots of interesting language talks at JavaOne worth considering - search the Content Catalog.

Finally, drop by the Oracle Database "Database Access APIs" demo pod 3591 in the Moscone South exhibition hall and say Hi.

Tuesday Mar 13, 2012

PHP-FPM FastCGI Process Manager with Apache 2

I've published some vanilla PHP 5.4.0 RPMs to make new feature testing easier.

Along with all the PHP 5.4 goodies, the php-fpm "FastCGI Process Manager" is available for the first time on oss.oracle.com. Php-fpm is an alternative FastCGI interface to PHP with various extra features such as load dependent spawning of processes. (For other features, see php-fpm.org). Php-fpm has been getting more and more traction in the PHP community and the EXPERIMENTAL flag was removed in PHP 5.4. You might want to test it out.

To use php-fpm with the default Apache web server, first install Oracle Linux 5.8 (64bit) using Oracle's free, public yum repository public-yum.oracle.com.

Install Apache, if not already installed:

  yum install httpd

Download and install the PHP 5.4 RPMs from oss.oracle.com/projects/php:

  rpm -i php54-common-5.4.0-1.el5.x86_64.rpm php54-fpm-5.4.0-1.el5.x86_64.rpm

Other extensions can also be installed, depending on the functionality you want to test.

Download and build FastCGI for Apache:

  wget http://www.fastcgi.com/dist/mod_fastcgi-current.tar.gz
  tar -zxf mod_fastcgi-current.tar.gz
  cd mod_fastcgi-2.4.6
  cp Makefile.AP2 Makefile
  make top_dir=/usr/lib64/httpd

Install FastCGI as root:

  make top_dir=/usr/lib64/httpd install

Edit /etc/httpd/conf/httpd.conf and comment out any existing references to PHP you might previously have added for testing:

# LoadModule php5_module        modules/libphp5.so
# AddType application/x-httpd-php .php

Add the php-fpm configuration to httpd.conf:

  LoadModule fastcgi_module modules/mod_fastcgi.so

  <IfModule mod_fastcgi.c>  
    FastCGIExternalServer /usr/sbin/php-fpm -host 127.0.0.1:9000
    AddHandler php-fastcgi .php  

    #<LocationMatch "/status">
    #  SetHandler php-fastcgi-virt
    #  Action php-fastcgi-virt /usr/sbin/php-fpm.fcgi virtual
    #</LocationMatch>

    Action php-fastcgi /usr/sbin/php-fpm.fcgi  
    ScriptAlias /usr/sbin/php-fpm.fcgi /usr/sbin/php-fpm  

    <Directory /usr/sbin>  
      Options ExecCGI FollowSymLinks  
      SetHandler fastcgi-script  
      Order allow,deny  
      Allow from all  
    </Directory>  
  </IfModule> 

Start php-fpm and Apache:

  service php-fpm start
  service httpd start

Test it out with your favorite script or create a file pi.php:

  <?php
    phpinfo();
  ?>

Save it in /var/www/html/pi.php or in $HOME/public_html/pi.php, if you have configured UserDir in httpd.conf.

In a browser load http://localhost/pi.php or http://localhost/~yourname/pi.php.

This will show the PHP configuration values.

To test php-fpm's built-in statistics, edit httpd.conf and uncomment the four lines of the LocationMatch section:

  <LocationMatch "/status">
    SetHandler php-fastcgi-virt
    Action php-fastcgi-virt /usr/sbin/php-fpm.fcgi virtual
  </LocationMatch>

Edit /etc/php-fpm.conf and uncomment the line:

  pm.status_path = /status

Restart php-fpm and Apache:

  service php-fpm restart
  service httpd restart

Run some load on the system:

  ab -c 10 -t 60 http://localhost/pi.php  

Now http://localhost/status gives you the status of the server:

  pool:                 www
  process manager:      dynamic
  start time:           13/Mar/2012:14:25:53 -0700
  start since:          26
  accepted conn:        50001
  listen queue:         0
  max listen queue:     6
  listen queue len:     128
  idle processes:       2
  active processes:     1
  total processes:      3
  max active processes: 5
  max children reached: 1

The php-fpm.conf file documents other output formats for the statistics. It also shows the extensive functionality available with php-fpm.

Documentation on php-fpm is not ideal, but you can see some at http://php.net/manual/en/install.fpm.php. The php-fpm.org site has more, including a forum and wiki.

Php-fpm is commonly used in conjunction with the nginx webserver. For this, you will need to compile nginx yourself. Because php-fpm is installed and managed separately from the webserver, the php-fpm RPM will still be usable. Now that Apache 2.4 has been released with claims of improved performance, it will be interesting to see if web server popularity swings back towards Apache.

Finally, remember that the PHP RPMs on oss.oracle.com are for testing purposes only. They are not supported.

Update: In the Underground PHP & Oracle Manual, I simplified the httpd.conf options to this (note PHP was installed in a different location):

<IfModule mod_fastcgi.c>
    FastCGIExternalServer /opt/php544/sbin/php-fpm -host 127.0.0.1:9000
    AddHandler php-fastcgi .php
    Action php-fastcgi /mycgi
    ScriptAlias /mycgi /opt/php544/sbin/php-fpm
</IfModule>
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today