pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Axel Waggershauser <awagger@web.de>
To: pgsql-performance@postgresql.org
Subject: Re: Low throughput of binary inserts from windows to linux
Date: Sat, 9 Dec 2006 02:24:13 +0100
Message-ID: <5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com> (raw )
In-Reply-To: <5e66c6e90612080417r23ecaea4p210905f868c26c7f@mail.gmail.com >
References: <5e66c6e90612080417r23ecaea4p210905f868c26c7f@mail.gmail.com >
Hi,
I have written my own 'large object'-like feature using the following table:
----
CREATE TABLE blob
(
id bigint NOT NULL,
pageno integer NOT NULL,
data bytea,
CONSTRAINT blob_pkey PRIMARY KEY (id, pageno)
)
WITHOUT OIDS;
ALTER TABLE blob ALTER COLUMN data SET STORAGE EXTERNAL;
CREATE SEQUENCE seq_key_blob;
----
One blob consist of many rows, each containing one 'page'. I insert pages with
PQexecPrepared with the format set to binary. This works quite well for the
following setups:
client -> server
-----------------
linux -> linux
linux -> windows
windows -> windows
but pretty bad (meaning about 10 times slower) for this setup
windows -> linux
The used postgresql versions are 8.1.5 for both operating system. A (sort of)
minimal code sample exposing this problem may be found appended to this e-mail.
Any ideas?
Thanks,
Axel
Attachments:
my_lo.c (text/x-csrc, 4.3K) download
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#ifdef WIN32
#include <io.h>
#include <winsock2.h>
#else
#include <unistd.h>
#endif
#include "libpq-fe.h"
#include "libpq/libpq-fs.h"
#define BUFSIZE (1024*64)
static void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
#define CHECK_PG_NO_RES(...) \
do{ \
PGresult* res = __VA_ARGS__; \
if( !res || (PQresultStatus(res) != PGRES_TUPLES_OK \
&& PQresultStatus(res) != PGRES_COMMAND_OK) ) \
{ \
printf("%s failed: %s", #__VA_ARGS__, PQerrorMessage(conn)); \
exit_nicely( conn ); \
} \
PQclear( res ); \
} while(0)
#define CHECK_PGRES(...) \
do { \
res = __VA_ARGS__; \
if( !res || (PQresultStatus(res) != PGRES_TUPLES_OK \
&& PQresultStatus(res) != PGRES_COMMAND_OK) ) \
{ \
printf("%s failed: %s", #__VA_ARGS__, PQerrorMessage(conn)); \
exit_nicely( conn ); \
} \
} while(0)
/*
* upload 10 MB dummy data, return blob-id
*/
static int upload(PGconn *conn)
{
char buf[BUFSIZE];
int id;
int id_n; // network byteorder of id
int pageno;
int pageno_n;
int N = 10*1024*1024/BUFSIZE;
const char* values[] = { (const char*)&id_n, (const char*)&pageno_n, buf };
const int formats[] = {1, 1, 1};
const int lengths[] = {sizeof(int), sizeof(int), BUFSIZE};
/*
* get (free) blob id
*/
PGresult* res;
CHECK_PGRES( PQexec( conn, "select nextval('seq_key_blob')" ) );
id = atoi( PQgetvalue(res, 0, 0) );
PQclear( res );
id_n = htonl(id);
/*
* prepare insert statement
*/
CHECK_PG_NO_RES( PQprepare( conn, "", "insert into blob (id, pageno, data) "
"values ($1::int4, $2::int4, $3::bytea);",
0, 0 ) );
printf("inserting %d pages (each %d kB): ", N, BUFSIZE/1024);
/*
* upload pages
*/
for( pageno = 0; pageno < N; ++pageno )
{
pageno_n = htonl(pageno);
CHECK_PG_NO_RES(
PQexecPrepared( conn, "", 3, values, lengths, formats, 1 ) );
printf(".");
fflush(stdout);
}
printf("\n");
return id;
}
/*
* download blob with given id
*/
static void download(PGconn *conn, int id)
{
char buf[BUFSIZE];
int id_n = htonl(id); // network byteorder of id
int pageno;
int pageno_n;
const char* values[] = { (const char*)&id_n, (const char*)&pageno_n };
const int lengths[] = {sizeof(int), sizeof(int)};
const int formats[] = {1, 1};
/*
* determine size of blob
*/
PGresult* res;
CHECK_PGRES( PQexecParams( conn, "select count(id) from blob where id = $1::int4;",
1, 0 /*type*/, values, lengths, formats, 0 ) );
int pages = atoi( PQgetvalue(res, 0, 0) );
PQclear( res );
printf("selecting %d pages (each %d kB): ", pages, BUFSIZE/1024);
/*
* prepare select statement
*/
CHECK_PG_NO_RES( PQprepare( conn, "", "select data from blob "
"where id = $1::int4 and pageno = $2::int4;",
0, 0 ) );
/*
* download pages
*/
for( pageno = 0; pageno < pages; ++pageno )
{
pageno_n = htonl(pageno);
CHECK_PGRES(
PQexecPrepared( conn, "", 2, values, lengths, formats, 1 ) );
/*
* we are not interested in the actual data in this demo
* n = PQgetlength( res, 0, 0 );
* v = PQgetvalue(res, 0, 0);
*/
PQclear( res );
printf(".");
fflush(stdout);
}
printf("\n");
return;
}
int main(int argc, char **argv)
{
int id;
PGconn *conn;
PGresult *res;
if (argc < 5)
{
fprintf(stderr, "Usage: %s host database user password\n", argv[0]);
exit(1);
}
/*
* set up the connection
*/
conn = PQsetdbLogin(argv[1], "5432", NULL, NULL, argv[2], argv[3], argv[4]);
/* check to see that the backend connection was successfully made */
if (PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed: %s",
PQerrorMessage(conn));
exit_nicely(conn);
}
id = upload( conn );
download( conn, id );
PQfinish(conn);
return 0;
}
view thread (13+ messages) latest in thread
Message-ID: <5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com>
Permalink: ../5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com/
Also on: postgresql.org/message-id/5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: awagger@web.de
Subject: Re: Low throughput of binary inserts from windows to linux
In-Reply-To: <5e66c6e90612081724m57293ffdy21d6b45697e69bd4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox