To: "Merlin Moncure" <merlin.moncure@rcsonline.com>
Cc: "Qingqing Zhou" <zhouqq@cs.toronto.edu>,
	pgsql-performance@postgresql.org, "Marc Cousin" <mcousin@sigma.fr>
Subject: Re: insert performance for win32 
In-reply-to: <6EE64EF3AB31D5448D0007DD34EEB3417DD79B@Herge.rcsinc.local> 
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD79B@Herge.rcsinc.local>
Comments: In-reply-to "Merlin Moncure" <merlin.moncure@rcsonline.com>
	message dated "Thu, 03 Nov 2005 13:03:19 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <7853.1131062168.0@sss.pgh.pa.us>
Date: Thu, 03 Nov 2005 18:56:59 -0500
Message-ID: <7860.1131062219@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <7853.1131062168.1@sss.pgh.pa.us>

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> ok, I generated a test case which was 250k inserts to simple two column
> table all in single transaction.  Every 50k inserts, time is recorded
> via timeofday().  

You mean something like the attached?

> Running from remote, Time progression is:
> First  50k: 20 sec
> Second    : 29 sec
> [...]
> final:    : 66 sec

On Unix I get a dead flat line (within measurement noise), both local
loopback and across my LAN.

after 50000 30.20 sec
after 100000 31.67 sec
after 150000 30.98 sec
after 200000 29.64 sec
after 250000 29.83 sec

"top" shows nearly constant CPU usage over the run, too.  With a local
connection it's pretty well pegged, with LAN connection the server's
about 20% idle and the client about 90% (client machine is much faster
than server which may affect this, but I'm too lazy to try it in the
other direction).

I think it's highly likely that you are looking at some strange behavior
of the Windows TCP stack.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: application/octet-stream
Content-ID: <7853.1131062168.2@sss.pgh.pa.us>
Content-Description: timeit.c

/*
 * testlibpq.c
 *
 *		Test the C version of libpq, the PostgreSQL frontend library.
 */
#include <stdio.h>
#include <stdlib.h>
#include <sys/time.h>

#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}

static void
do_cmd(PGconn *conn, const char *cmd)
{
	PGresult   *res;

	res = PQexec(conn, cmd);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "%s failed: %s", cmd, PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}
	PQclear(res);
}

static const char *
rusage_show(const struct timeval *tvold, const struct timeval *tvnew)
{
	static char result[100];
	struct timeval tv = *tvnew;

	if (tv.tv_usec < tvold->tv_usec)
	{
		tv.tv_sec--;
		tv.tv_usec += 1000000;
	}

	sprintf(result,
			 "%d.%02d sec",
			 (int) (tv.tv_sec - tvold->tv_sec),
			 (int) (tv.tv_usec - tvold->tv_usec) / 10000);

	return result;
}

int
main(int argc, char **argv)
{
	const char *conninfo;
	PGconn	   *conn;
	char		cmdbuf[1024];
	struct timeval tv;
	struct timeval tvold;
	int			i;

	/*
	 * If the user supplies a parameter on the command line, use it as the
	 * conninfo string; otherwise default to setting dbname=postgres and using
	 * environment variables or defaults for all other connection parameters.
	 */
	if (argc > 1)
		conninfo = argv[1];
	else
		conninfo = "dbname = postgres";

	/* Make a connection to the database */
	conn = PQconnectdb(conninfo);

	/* 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);
	}

	/* Create working table */
	do_cmd(conn, "CREATE TABLE foo (f1 int, f2 int);");

	/* Start a transaction block */
	do_cmd(conn, "BEGIN");

	gettimeofday(&tvold, NULL);

	for (i = 1; i <= 250000; i++)
	{
		sprintf(cmdbuf, "INSERT INTO foo VALUES(%d,%d);", i, i);
		do_cmd(conn, cmdbuf);

		if (i % 50000 == 0)
		{
			gettimeofday(&tv, NULL);
			printf("after %d %s\n", i, rusage_show(&tvold, &tv));
			tvold = tv;
		}
	}

	/* end the transaction */
	do_cmd(conn, "END");

	/* drop table */
	do_cmd(conn, "DROP TABLE foo;");

	/* close the connection to the database and cleanup */
	PQfinish(conn);

	return 0;
}

------- =_aaaaaaaaaa0--