Re: Low throughput of binary inserts from windows to linux

["Axel Waggershauser" <awagger@xxxxxx>]

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

#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;
}