php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #58700 Reading a BLOB is very slow
Submitted: 2009-05-19 15:21 UTC Modified: 2009-05-28 16:28 UTC
From: adam at morrison-ind dot com Assigned:
Status: Open Package: PDO_INFORMIX (PECL)
PHP Version: 5.2.9 OS: LINUX
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2009-05-19 15:21 UTC] adam at morrison-ind dot com
Description:
------------
Selecting a BLOB from the database is VERY slow.  Performing the same select with the old ifx_ API or using a JDBC app very quick. PDO and ifx_ compared on 5.0.4 since ifx_ API isn't enabled on current PHP build.

Reproduce code:
---------------
<?php
  class Test {
    public $db = null;
    public function connect($autoCommit=true) {
      $this->db = new PDO("informix:database=miecr;server=BARNET;",
                          "adam",
                          "**********");
      return $this->db;
    }
    public function runTest() {
      $this->connect();
      $stmt = $this->db->prepare( 'select wds_blob_id,wds_blob from wds_store where wds_blob_id = 11392' );
      $stmt->bindColumn( 'WDS_BLOB_ID' , $id );
      $stmt->bindColumn( 'WDS_BLOB' , $blob , PDO::PARAM_LOB );
      $rs = $stmt->execute();
      while ($stmt->fetch(PDO::FETCH_BOUND)) {
        var_dump( $id );
        var_dump( $blob );
        $fp = fopen( dirname(__FILE__) . "/large_blob_out.dat" , "wb" );
        echo "datalength: " . stream_copy_to_stream( $blob , $fp ) . "\n";
        system( "diff large_blob.dat large_blob_out.dat" );
      }
      print "done\n";
    }
  }
  $testcase = new Test();
  $testcase->runTest();
?>

Expected result:
----------------
Would load a 3.5Mb BLOB in less than 42 seconds!  Graphing the communication between the web server and the DB servers shows that throughput rapidly falls off.

Actual result:
--------------
yannigan-red:~ # time php -f blobPerfTest.php 
string(5) "11392"
resource(4) of type (stream)
datalength: 3591751
done

real	0m46.065s
user	0m0.044s
sys	0m0.416s


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-05-27 10:22 UTC] adam at morrison-ind dot com
If I profile the run (with strace) the PDO test spends the vast majority of its time in recv(). Looking at the communication using tcpdump/wireshark the performance rapidly drops off and a massive number of packets is exchanged between the client and the server. I've posted 
the Bytes/Second graph at <https://www.mormail.com/OGo12905012.2009051914.TransferRate.png>  I 
found an issue that sounds similair, but using VB, at 
<http://www.dbtalk.net/comp-databases-informix/blob-performance-36360.html>
 [2009-05-28 05:24 UTC] abhargav at in dot ibm dot com
Hi,

I have increased the buffer size. Please apply the following patch in v1.2.6 of PDO_INFORMIX (file php_pdo_informix_int.h) and then check the performance of the system and also let me know the outcome of it.

143c143
< #define LOB_BUFFER_SIZE 8192
---
> #define LOB_BUFFER_SIZE 32768

Regards,
Ambrish Bhargava
 [2009-05-28 16:28 UTC] adam at morrison-ind dot com
Increasing 
#define LOB_BUFFER_SIZE 8192
to
#define LOB_BUFFER_SIZE 32768
has no effect at all.  I had also previously tried a value of 65535, setting the LOB_SIZE=-1 in the connection string, and setting LOB_SIZE=-1 in the environment.  Sadly, none of these yielded a performance impact.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sun Jun 16 16:01:28 2019 UTC