Re: COPY fast parse patch

Here is the patch I was talking about in my message to the "NOLOGGING
option, or ?" thread. I would like to indicate some important points about
it:

1) The patch includes 2 parallel parsing code paths. One is the regular COPY
path that we all know, and the other is the improved one that I wrote. This
is only temporary, as there is a lot of code duplication, but I left it as
such for several purposes:

- The improved path for now supports only ASCII delimited text format with a
condition that client and server encodings are identical. If this is not the
case when running COPY the old path will take place. In other words, under
no condition a "not supported" error will be raised.

- Having both code paths allows for easy performance comparison between the
two. To run the regular COPY parsing call CopyFrom() from DoCopy() and to
run the improved parsing COPY call FastCopyFrom() from DoCopy(). Right now
FastCopyFrom() will be called automatically if all conditions explained in
previous point are met, but it's easy to change (need to re-compile).

* NOTE: the function names Fast*() as ugly as they are, are there to
emphasize the differences between the old and the improved path (i.e:
CopyReadLine() vs. FastReadLine()... ). They are not intended to stay this
way. This is not elegant (yet)!

2) There are some utilities such as bytebuffer and strchrlen that are at the
bottom of the file. This is probably not the right home for them, but for
now to simplify things they are included in copy.c

3) EOL is assumed NL. I raised a point about EOL's in COPY in my previous
thread, and it explains it.

4) Performance numbers could be viewed at
http://lists.pgfoundry.org/pipermail/bizgres-general/2005-May/000135.html
Some numbers include:
8.7MB/sec -> 11.8MB/sec on 15 column (Mixed) table.
12.1MB/sec -> 21MB/sec on 1 column (TEXT) table.

5) Data integrity and escaping improvements. Treats all characters as data
(unless it's an escaped delim or EOL) and therefore data
integrity is preserved. However, some people that already got
used to the postgres COPY escaping way may want to keep it. They could do so
by still using the old COPY.

As a part of submitting this patch I also presented an argument for a use of
a LOAD DATA command (in the NOLOGGING option thread). The points I made
there are closely related to this message. There may be a valid argument
that most of the points I raised could be implemented in the COPY code
instead of a LOAD DATA command, but that requires a great deal of
flexibility to add features and adding them to the COPY syntax. But that may
not be a good idea for some and will also be problematic for backwards
compatiability.

Thx,
Alon.

Alon Goldshuv wrote:
> 5) Data integrity and escaping improvements. Treats all characters as data
> (unless it's an escaped delim or EOL) and therefore data
> integrity is preserved. However, some people that already got
> used to the postgres COPY escaping way may want to keep it. They could do so
> by still using the old COPY.

OK, if the COPY file contains:

\n\||\r\\a|\N

and the delimiter is '|', what does that represent? What if there were
a literal '\' followed by a '|' in the data table being dumped?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2005-06-01 at 16:34 -0700, Alon Goldshuv wrote:
> 1) The patch includes 2 parallel parsing code paths. One is the regular COPY
> path that we all know, and the other is the improved one that I wrote. This
> is only temporary, as there is a lot of code duplication

Right; I really dislike the idea of having two separate code paths for
COPY. When you say this approach is "temporary", are you suggesting that
you intend to reimplement your changes as improvements/replacements of
the existing COPY code path rather than as a parallel code path?

> As a part of submitting this patch I also presented an argument for a use of
> a LOAD DATA command (in the NOLOGGING option thread). The points I made
> there are closely related to this message. There may be a valid argument
> that most of the points I raised could be implemented in the COPY code
> instead of a LOAD DATA command

I'm definitely not keen to see a new "LOAD DATA" command.

> But that may not be a good idea for some and will also be problematic
> for backwards compatiability.

In what way would the performance improvements to COPY be backward
incompatible with the existing COPY behaviour?

-Neil

Neil Conway said:
> On Wed, 2005-06-01 at 16:34 -0700, Alon Goldshuv wrote:
>> 1) The patch includes 2 parallel parsing code paths. One is the
>> regular COPY path that we all know, and the other is the improved one
>> that I wrote. This is only temporary, as there is a lot of code
>> duplication
>
> Right; I really dislike the idea of having two separate code paths for
> COPY. When you say this approach is "temporary", are you suggesting
> that you intend to reimplement your changes as
> improvements/replacements of the existing COPY code path rather than as
> a parallel code path?
>

It's not an all or nothing deal. When we put in CSV handling, we introduced
two new routines for attribute input/output and otherwise used the rest of
the COPY code. When I did a fix for the multiline problem, it was originally
done with a separate read line function for CSV mode - Bruce didn't like
that so I merged it back into the existing code. In restrospect, given this
discussion, that might not have been an optimal choice. But the point is
that you can break out at several levels.

Incidentally, there might be a good case for allowing the user to set the
line end explicitly, but you can't just hardwire it - we will get massive
Windows breakage. What is more, in CSV mode line end sequences can occur
within logical lines. You need to take that into account. It's tricky and
easy to get badly wrong.

I will be the first to admit that there are probably some very good
possibilities for optimisation of this code. My impression though has been
that in almost all cases it's fast enough anyway. I know that on some very
modest hardware I have managed to load a 6m row TPC line-items table in just
a few minutes. Before we start getting too hung up, I'd be interested to
know just how much data people want to load and how fast they want it to be.
If people have massive data loads that take hours, days or weeks then it's
obviously worth improving if we can. I'm curious to know what size datasets
people are really handling this way.

cheers

andrew

Andrew,

> I will be the first to admit that there are probably some very good
> possibilities for optimisation of this code. My impression though has been
> that in almost all cases it's fast enough anyway. I know that on some very
> modest hardware I have managed to load a 6m row TPC line-items table in just
> a few minutes. Before we start getting too hung up, I'd be interested to
> know just how much data people want to load and how fast they want it to be.
> If people have massive data loads that take hours, days or weeks then it's
> obviously worth improving if we can. I'm curious to know what size datasets
> people are really handling this way.

x0+ GB files are common in data warehousing. The issue is often "can we
load our data within the time allotted for the batch window", usually a
matter of an hour or two.

Assuming that TPC lineitem is 140Bytes/row, 6M rows in 3 minutes is 4.7
MB/s. To load a 10GB file at that rate takes about 2/3 hour. If one were
to restore a 300GB database, it would take 18 hours. Maintenance operations
are impractical after a few hours, 18 is a non-starter.

In practice, we're usually replacing an Oracle system with PostgreSQL, and
the load speed difference between the two is currently embarrassing and
makes the work impractical.

- Luke

Luke Lonergan said:
> Andrew,
>
>> I will be the first to admit that there are probably some very good
>> possibilities for optimisation of this code. My impression though has
>> been that in almost all cases it's fast enough anyway. I know that on
>> some very modest hardware I have managed to load a 6m row TPC
>> line-items table in just a few minutes. Before we start getting too
>> hung up, I'd be interested to know just how much data people want to
>> load and how fast they want it to be. If people have massive data
>> loads that take hours, days or weeks then it's obviously worth
>> improving if we can. I'm curious to know what size datasets people are
>> really handling this way.
>
> x0+ GB files are common in data warehousing. The issue is often "can
> we load our data within the time allotted for the batch window",
> usually a matter of an hour or two.
>
> Assuming that TPC lineitem is 140Bytes/row, 6M rows in 3 minutes is 4.7
> MB/s. To load a 10GB file at that rate takes about 2/3 hour. If one
> were to restore a 300GB database, it would take 18 hours. Maintenance
> operations are impractical after a few hours, 18 is a non-starter.
>
> In practice, we're usually replacing an Oracle system with PostgreSQL,
> and the load speed difference between the two is currently embarrassing
> and makes the work impractical.
>

OK ... that seems fair enough. The next question is where the data being
loaded comes from? pg_dump? How does load speed compare with using COPY's
binary mode?

cheers

andrew

Andrew,

> OK ... that seems fair enough. The next question is where the data being
> loaded comes from? pg_dump? How does load speed compare with using COPY's
> binary mode?

Oddly, our tests in the past have shown that binary is actually slower.

Luke