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: 2021-09-05 04:22 UTC
From: adam at morrison-ind dot com Assigned: cmb (profile)
Status: No Feedback Package: PDO_INFORMIX (PECL)
PHP Version: 5.2.9 OS: LINUX
Private report: No CVE-ID: None
 [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

Pull Requests

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.
 [2021-08-23 14:40 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2021-08-23 14:40 UTC] cmb@php.net
Is this still an issue with the latest PDO_INFORMIX (1.3.4)?
 [2021-09-05 04:22 UTC] pecl-dev at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Dec 27 00:01:30 2024 UTC