Re: COPY locking

Tom Lane wrote:
>
> John Coers <coers@intrinsity.com> writes:
> > I've attached a little facsinating truss output.
>
> You probably shouldn't have sent 250k of trace to the whole mailing
> list.  But it *is* fascinating.
I thought about that right as I hit "Send."  Apologies.  At least it wasn't a Meg. :D


>
> I tried to duplicate the result locally with no success.  For a
> 10000-row COPY FROM STDIN (using the tenk1 table from the regression
> database, which has several indexes), my trace contains:
I don't think the semops occur when only 1 client is performing a COPY,
so it makes sense you got different results.

I am doing this with a C executable.  The code is pretty ugly because I've been trying
all sorts of different (mostly stupid) things to see what affected performance.  Here
is an outline:

1) Try to connect.  If there are already max_connections connected, I wait a second (or
a small rand() number of seconds) and try again.
2) do an insert of 10 rows into the 'tests' table and hold onto the OID from each insert
3) query the 'monlibs' table to see if the db knows the code I am about to send it.
If it doesn't, then I COPY that (small) amount of data to table 'mongrids' and insert a
signature of 3 strings into 'monlibs'.

Here is the part that beats on the server:
4) I then COPY upto 7500 rows of data of 3 Ints for each of the 10 test oids to the 'moncoverage' table.
The number of rows is weighted oddly and averages 16000 rows total, but could obviously go up to 75000.

There are 150 clients doing this every 5-15 minutes depending on how many rows of data are generated.
AT some point I have 250-300 machines doing it.



>
>         lseek:  14060
>         read:   4261
>         write:  4967
>         recv:   43
>         other:  170
>
> with a large part of the "other" being process startup/shutdown
> overhead.  There are no semops or setitimers at all in my trace.
> There do seem to be some excess lseeks, but I don't see lots and
> lots of seeks with no read or write as you show.
>
> Could we see the schema for the table you are using?
> ("pg_dump -s -t tablename dbname" is the best way to show it.)
>
Attached.  It is less than 250k :D

I have tried the Exclusive Lock idea you sent earlier and it seems to
help significantly.  I plan on trying that in combination with 8 dbs
on two disks and have each client randomly pick one.  Is there a query
to see whether a table is locked?


--
John Coers            Intrinsity, Inc.
coers@intrinsity.com  Austin, TexasCREATE TABLE "tests" (
    "name" character varying(80),
    "majver" int4,
    "minver" int4,
    "status" character(8),
    "failtype" character(30),
    "cycles" int4,
    "genseed" int8,
    "simseed" int8,
    "username" character(12),
    "hostname" character(12),
    "started" timestamp,
    "ended" timestamp,
    "generator" character(20),
    "genmajver" int4,
    "genminver" int4,
    "geniss" character(20),
    "issmajver" int4,
    "issminver" int4,
    "blmajver" int4,
    "blminver" int4
);
CREATE TABLE "monlibs" (
    "source" character varying(80),
    "parser" character varying(80),
    "blanket" character varying(80)
);
CREATE TABLE "moncoverage" (
    "library" oid,
    "gridpoint" int4,
    "test" oid
);
CREATE TABLE "monhits" (
    "library" oid,
    "gridpoint" int4,
    "count" int4
);
CREATE TABLE "mongrids" (
    "library" oid,
    "dimension" int4,
    "monstring" character varying(1000)
);