X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7400DD1C94D for ; Wed, 6 Aug 2003 17:17:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 49844-07 for ; Wed, 6 Aug 2003 14:15:45 -0300 (ADT) Received: from mailgate.sri.com (mailgate.SRI.COM [128.18.243.11]) by svr1.postgresql.org (Postfix) with SMTP id 0AF44D1C941 for ; Wed, 6 Aug 2003 14:16:55 -0300 (ADT) Received: (qmail 2833 invoked from network); 6 Aug 2003 17:16:53 -0000 Received: from localhost (HELO mailgate.SRI.COM) (127.0.0.1) by mailgate.sri.com with SMTP; 6 Aug 2003 17:16:53 -0000 Received: from quarter.csl.sri.com ([130.107.1.30]) by mailgate.SRI.COM (SAVSMTP 3.1.0.29) with SMTP id M2003080610165202287 for ; Wed, 06 Aug 2003 10:16:53 -0700 Received: from [130.107.14.36] (abyss.csl.sri.com [130.107.14.36]) by quarter.csl.sri.com (8.12.9/8.12.9) with ESMTP id h76HGqdX026767 for ; Wed, 6 Aug 2003 10:16:52 -0700 Mime-Version: 1.0 Message-Id: Date: Wed, 6 Aug 2003 10:17:12 -0700 To: pgsql-interfaces@postgresql.org From: John Khouri Subject: Disk usage problem with BLOBs using libpq Content-Type: multipart/alternative; boundary="============_-1151932262==_ma============" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/26 X-Sequence-Number: 4380 --============_-1151932262==_ma============ Content-Type: text/plain; charset="us-ascii" ; format="flowed" Hi, I'm experiencing a disk usage "lossage" when rewriting large objects (BLOBs) multiple times. I'm using the libpq libraries, posgresql 7.3.2 on FreeBSD-4.5 (default settings on installation), but I don't believe the version or OS are factors here. In the test case below, a 5k-byte BLOB is created once and rewritten n-times. Given these multiple same-size rewrites, I would expect the disk usage to increase only slightly due to (hopefully small) overhead needed for the transaction. However I am seeing a substantial increase in disk usage that is linear with respect to the number of rewrites (in /usr/local/pgsql/data/base/16976): - 10000 rewrites increases the disk space by 11.5 Mbytes - 50000 rewrites increases the disk space by 57.0 Mbytes and so on Is this a postgres "bug" or is there a way to minimize this "lost" disk space by adjusting a configuration setting or manipulating the transaction? (Note: in our actual application with multiple BLOBS summing 40 Mbytes, the resulting disk space consumed in ...pgsql/data/base... is over 4 Gbytes). Any help is appreciated. Thanks, JK #include #include "libpq-fe.h" #include "libpq/libpq-fs.h" #define BLOB_SIZE 5000 static void writeBlob (PGconn* conn, Oid lobjId) { char buf[BLOB_SIZE]; int lobj_fd; int count; int status; lobj_fd = lo_open (conn, lobjId, INV_WRITE); count = lo_write (conn, lobj_fd, buf, BLOB_SIZE); if (count < BLOB_SIZE) fprintf (stderr, "error while writing large object\n"); status = lo_close (conn, lobj_fd); if (status != 0) fprintf (stderr, "lo_close error %d\n", status); } int main (int argc, char **argv) { Oid lobjId; PGconn* conn; PGresult* res; int num_rewrites; int j; conn = PQconnectdb ("dbname='test_db' user='postgres'"); if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "Connection to database 'test_db' failed.\n"); fprintf(stderr, "%s", PQerrorMessage(conn)); return (1); } num_rewrites = atoi (argv[1]); res = PQexec (conn, "begin"); PQclear(res); /* create initial blob */ lobjId = lo_creat (conn, INV_READ | INV_WRITE); if (lobjId == 0) fprintf (stderr, "error creating large object\n"); writeBlob (conn, lobjId); res = PQexec (conn, "end"); PQclear (res); /* rewrite blob "num_rewrites" times */ for (j = 0; j < num_rewrites; j++) { res = PQexec (conn, "begin"); PQclear(res); writeBlob (conn, lobjId); res = PQexec (conn, "end"); PQclear (res); } PQfinish (conn); return (0); } --============_-1151932262==_ma============ Content-Type: text/html; charset="us-ascii" Disk usage problem with BLOBs using libpq
Hi,

I'm experiencing a disk usage "lossage" when rewriting large objects (BLOBs) multiple times.  I'm using the libpq libraries, posgresql 7.3.2 on FreeBSD-4.5 (default settings on installation), but I don't believe the version or OS are factors here.

In the test case below, a 5k-byte BLOB is created once and rewritten n-times.  Given these multiple same-size rewrites, I would expect the disk usage to increase only slightly due to (hopefully small) overhead needed for the transaction.  However I am seeing a substantial increase in disk usage that is linear with respect to the number of rewrites (in /usr/local/pgsql/data/base/16976):

- 10000 rewrites increases the disk space by 11.5 Mbytes
- 50000 rewrites increases the disk space by 57.0 Mbytes
  and so on

Is this a postgres "bug" or is there a way to minimize this "lost" disk space by adjusting a configuration setting or manipulating the transaction?  (Note: in our actual application with multiple BLOBS summing 40 Mbytes, the resulting disk space consumed in ...pgsql/data/base... is over 4 Gbytes).

Any help is appreciated.

Thanks,
JK




#include <stdio.h>
#include "libpq-fe.h"
#include "libpq/libpq-fs.h"
                    
#define BLOB_SIZE 5000
static void writeBlob (PGconn* conn, Oid lobjId)
{
  char buf[BLOB_SIZE];
  int  lobj_fd;
  int  count;
  int  status;

  lobj_fd = lo_open (conn, lobjId, INV_WRITE);
                    
  count = lo_write (conn, lobj_fd, buf, BLOB_SIZE);
  if (count < BLOB_SIZE)
    fprintf (stderr, "error while writing large object\n");
                    
  status = lo_close (conn, lobj_fd);
  if (status != 0)
    fprintf (stderr, "lo_close error %d\n", status);
}


int main (int argc, char **argv)
{
  Oid         lobjId;
  PGconn*     conn;
  PGresult*   res;
  int         num_rewrites;
  int         j;
                    
  conn = PQconnectdb ("dbname='test_db' user='postgres'");
                    
  if (PQstatus(conn) == CONNECTION_BAD) {
    fprintf(stderr, "Connection to database 'test_db' failed.\n");
    fprintf(stderr, "%s", PQerrorMessage(conn));
    return (1);
  }
                    
  num_rewrites = atoi (argv[1]);

  res = PQexec (conn, "begin");
  PQclear(res);
            
  /* create initial blob */

  lobjId = lo_creat (conn, INV_READ | INV_WRITE);

  if (lobjId == 0)
    fprintf (stderr, "error creating large object\n");
                    
  writeBlob (conn, lobjId);

  res = PQexec (conn, "end");
  PQclear (res);
 
  /* rewrite blob "num_rewrites" times */

  for (j = 0; j < num_rewrites; j++) {
      res = PQexec (conn, "begin");
      PQclear(res);
      writeBlob (conn, lobjId);              
      res = PQexec (conn, "end");
      PQclear (res);
  }
 
  PQfinish (conn);
  return (0);
}
--============_-1151932262==_ma============--