Обсуждение: Insert performance for large transaction with multiple COPY FROM

Except - and that's the wall I'm hitting - for one table which yielded just 75 records/second.

The main 'problem' seem to be the FK constraints. Dropping just them restored insert performance for this table to 6k records/s.

On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
> Except - and that's the wall I'm hitting - for one table which yielded just
> 75 records/second.
> The main 'problem' seem to be the FK constraints. Dropping just them
> restored insert performance for this table to 6k records/s. The table in
> question has a composite PK (3 columns), 3 foreign keys and a bunch of
> indexes (see table obj_item_loc at the end of the mail). Compared to the
> other 32 tables nothing unusual.
> I'd gladly supply more information if necessary.
...
> CREATE TABLE obj_item_loc
> (
>   obj_item_id numeric(20,0) NOT NULL,
>   loc_id numeric(20,0) NOT NULL,
>   obj_item_loc_ix numeric(20,0) NOT NULL,

That sounds a lot like a missing index on the target relations (or
indices that are unusable).

Those numeric ids look really unusual. Why not bigint? It's close to
the same precision, but native, faster, more compact, and quite
unambiguous when indices are involved. If the types don't match on
both tables, it's quite likely indices won't be used when checking the
FK, and that spells trouble.

Yes, the ids is something I don't like either.
They carry additional semantics, which I cannot make go away.
How are chances char(20) is more time efficient than numeric(20)?
Disk space is no problem here.


On 12.01.2013, at 02:17, Claudio Freire <klaussfreire@gmail.com> wrote:

> On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
>> Except - and that's the wall I'm hitting - for one table which yielded just
>> 75 records/second.
>> The main 'problem' seem to be the FK constraints. Dropping just them
>> restored insert performance for this table to 6k records/s. The table in
>> question has a composite PK (3 columns), 3 foreign keys and a bunch of
>> indexes (see table obj_item_loc at the end of the mail). Compared to the
>> other 32 tables nothing unusual.
>> I'd gladly supply more information if necessary.
> ...
>> CREATE TABLE obj_item_loc
>> (
>>  obj_item_id numeric(20,0) NOT NULL,
>>  loc_id numeric(20,0) NOT NULL,
>>  obj_item_loc_ix numeric(20,0) NOT NULL,
>
> That sounds a lot like a missing index on the target relations (or
> indices that are unusable).
>
> Those numeric ids look really unusual. Why not bigint? It's close to
> the same precision, but native, faster, more compact, and quite
> unambiguous when indices are involved. If the types don't match on
> both tables, it's quite likely indices won't be used when checking the
> FK, and that spells trouble.

On Fri, Jan 11, 2013 at 5:17 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Fri, Jan 11, 2013 at 8:55 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
>> Except - and that's the wall I'm hitting - for one table which yielded just
>> 75 records/second.
>> The main 'problem' seem to be the FK constraints. Dropping just them
>> restored insert performance for this table to 6k records/s. The table in
>> question has a composite PK (3 columns), 3 foreign keys and a bunch of
>> indexes (see table obj_item_loc at the end of the mail). Compared to the
>> other 32 tables nothing unusual.
>> I'd gladly supply more information if necessary.
> ...
>> CREATE TABLE obj_item_loc
>> (
>>   obj_item_id numeric(20,0) NOT NULL,
>>   loc_id numeric(20,0) NOT NULL,
>>   obj_item_loc_ix numeric(20,0) NOT NULL,
>
> That sounds a lot like a missing index on the target relations (or
> indices that are unusable).
>
> Those numeric ids look really unusual. Why not bigint? It's close to
> the same precision, but native, faster, more compact, and quite
> unambiguous when indices are involved. If the types don't match on
> both tables, it's quite likely indices won't be used when checking the
> FK, and that spells trouble.

Will PG allow you to add a FK constraint where there is no usable
index on the referenced side?

I have failed to do so, but perhaps I am not being devious enough.

Cheers,

Jeff

On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
> Yes, the ids is something I don't like either.
> They carry additional semantics, which I cannot make go away.
> How are chances char(20) is more time efficient than numeric(20)?
> Disk space is no problem here.

What are the other tables like then?

The exact data types involved are at issue here, so it matters.

Jeff Janes <jeff.janes@gmail.com> writes:
> Will PG allow you to add a FK constraint where there is no usable
> index on the referenced side?

It will not, because the referenced side must have a unique constraint,
ie an index.

The standard performance gotcha here is not having an index on the
referencing side.  But that only hurts when doing UPDATEs/DELETEs of
referenced-side keys, which as far as I gathered was not the OP's
scenario.

            regards, tom lane

The types referenced by the foreign keys are the same Numeric(20).
Since the complete schema (of about 300 tables) is generated, I will just try char(20) instead of numeric(20) in the
nextdays to see if it makes any difference. Which I somehow doubt.  

But first I'm following the lead of the tables/indexes iostats given by Jeff.


obj_item_loc references the following three tables and there should be no surprises.

CREATE UNLOGGED TABLE loc
(
  loc_id numeric(20,0) NOT NULL,
...
  CONSTRAINT loc_pkey PRIMARY KEY (loc_id),
…
)

CREATE UNLOGGED TABLE obj_item
(
  obj_item_id numeric(20,0) NOT NULL,
...
  CONSTRAINT obj_item_pkey PRIMARY KEY (obj_item_id),
…
)

CREATE UNLOGGED TABLE rptd
(
  rptd_id numeric(20,0) NOT NULL,
...
  CONSTRAINT rptd_pkey PRIMARY KEY (rptd_id),
…
)


On 12.01.2013, at 23:18, Claudio Freire <klaussfreire@gmail.com> wrote:

> On Sat, Jan 12, 2013 at 5:16 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
>> Yes, the ids is something I don't like either.
>> They carry additional semantics, which I cannot make go away.
>> How are chances char(20) is more time efficient than numeric(20)?
>> Disk space is no problem here.
>
> What are the other tables like then?
>
> The exact data types involved are at issue here, so it matters.

On Sat, Jan 12, 2013 at 7:41 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:
> Since the complete schema (of about 300 tables) is generated, I will just try char(20) instead of numeric(20) in the
nextdays to see if it makes any difference. Which I somehow doubt. 

I think that might just make it worse.

Well, maybe the others were right, and it's just that you're hitting
the disk on that particular table.

That, or it's all those CHECK constraints. Have you tried removing the
CHECK constraints (they're a heapload of function calls)

On Tue, Jan 15, 2013 at 3:44 PM, Horst Dehmer <horst.dehmer@gmail.com> wrote:


> idx_4 together with a simple select in the tables on-insert trigger is
> slowing things down considerably.

So the theory is that the presence of idx_4 is causing the trigger to
pick a poor plan (i.e. one using idx_4) while its absence removes that
temptation?


> pg_statio comes up with same big numbers (reads = bad, hits = not so bad?):

True disk reads are much more expensive, but given how few reads you
have relative to hits, I now think that in aggregate the hits are more
of a concern than the reads are.  In other words, you seem to be CPU
bound, not IO bound.

Even more so I think (but not with much confidence) that most of your
"reads" are actually coming from the OS cache and not from the disk.
PG cannot distinguish true disk reads from OS cache reads.

When was the last time you reset the stats?  That is, are your
reported numbers accumulated over several loads, with some having idx4
and some not?

...
>
> Now I have some (more) questions:
>
> 1. How do I know which index (if any) is chosen for a select statement
> inside a trigger during a bulk load transaction? (or for that matter: a
> series of recursive plpgsql functions)

Informally, reset your database to the state it was in before the
load, analyze it, and do the explain again before you do the load.

More formally, use use auto_explain and set
auto_explain.log_nested_statements to true.  I haven't verified this
works with triggers, just going by the description I think it should.

> 2. The query planner depends on stats collected by auto-vacuum/vacuum
> analyze, right? Does stats collecting also happen during a lengthy
> transaction?

My understanding is that a transaction will not dump its stats until
the commit, so the auto analyze will not occur *due to* the lengthy
transaction until after it is over.  But if the table was already due
for analyze anyway due to previous or concurrent shorter transactions,
the analyze will happen.  However, the lengthy transaction might not
see the results of the analyze (I'm not clear on the transaction
snapshot semantics of the statistics tables) and even if it did see
them, it might just be using cached plans and so would not change the
plan in the middle.

> 3. Is it possible (or even advisable) to trigger vacuum analyze inside an
> ongoing transaction. Let's say load 10,000 rows of table A, analyze table A,
> insert the next 10,000 rows, analyze again, ...

You can't vacuum inside a transaction.  You can analyze, but I don't
know if it would be advisable.

Your use case is a little unusual.  If you are bulk loading into an
initially empty table, usually you would remove the trigger and add it
after the load (with some kind of bulk operation to make up for
whatever it was the trigger would have been doing).  On the other
hand, if you are bulk loading into a "live" table and so can't drop
the trigger, then the live table should have good-enough preexisting
statistics to make the trigger choose a good plan.

Cheers,

Jeff

Hey Jeff (and others)!

First of all: Thanks for your detailed explanations and guide lines.


On 17.01.2013, at 18:12, Jeff Janes <jeff.janes@gmail.com> wrote:

> So the theory is that the presence of idx_4 is causing the trigger to
> pick a poor plan (i.e. one using idx_4) while its absence removes that
> temptation?

Yes. And auto_explain confirms this for the first record (obj_item_loc_obj_item_id_idx = idx_4 from last my last mail):

2013-01-18 22:50:21 CET LOG:  duration: 0.021 ms  plan:
    Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id = NEW.obj_item_id AND obj_item_loc.loc_id =
NEW.loc_idAND obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix 
    Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  (cost=0.00..8.27 rows=1 width=382)
      Index Cond: (obj_item_id = $15)
      Filter: ((loc_id = $16) AND (obj_item_loc_ix = $17))
2013-01-18 22:50:21 CET CONTEXT:  SQL statement "SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id =
NEW.obj_item_idAND obj_item_loc.loc_id = NEW.loc_id AND obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix" 
    PL/pgSQL function obj_item_loc_before_insert() line 5 at SQL statement
    COPY obj_item_loc, line 1: "10903011224100007276    10903010224100015110    10900024100000029720    \N    \N    \N
 \N    \N    \N    \N    \N    \N    \N    \N    \N    \..." 

and for one of the last records:

2013-01-18 22:53:20 CET LOG:  duration: 16.088 ms  plan:
    Query Text: SELECT * FROM obj_item_loc WHERE obj_item_loc.obj_item_id = NEW.obj_item_id AND obj_item_loc.loc_id =
NEW.loc_idAND obj_item_loc.obj_item_loc_ix = NEW.obj_item_loc_ix 
    Index Scan using obj_item_loc_obj_item_id_idx on obj_item_loc  (cost=0.00..8.27 rows=1 width=382)
      Index Cond: (obj_item_id = $15)
      Filter: ((loc_id = $16) AND (obj_item_loc_ix = $17))

I see a linear increase of the duration from 0.0x ms to over 16 ms (apart from a few nasty outliers with about 22 ms).
Althougheven at the end there are still a few durations < 0.03 but mostly 15 ms and above. 

> True disk reads are much more expensive, but given how few reads you
> have relative to hits, I now think that in aggregate the hits are more
> of a concern than the reads are.  In other words, you seem to be CPU
> bound, not IO bound.

Yes, definitely CPU bound, as top shows 99+% CPU utilization.

> Even more so I think (but not with much confidence) that most of your
> "reads" are actually coming from the OS cache and not from the disk.
> PG cannot distinguish true disk reads from OS cache reads.
>
> When was the last time you reset the stats?  That is, are your
> reported numbers accumulated over several loads, with some having idx4
> and some not?

I set up a fresh database before each test run. So the stats should be clean.

> More formally, use use auto_explain and set
> auto_explain.log_nested_statements to true.  I haven't verified this
> works with triggers, just going by the description I think it should.

Nice tip! Works for triggers as well.

> Your use case is a little unusual.  If you are bulk loading into an
> initially empty table, usually you would remove the trigger and add it
> after the load (with some kind of bulk operation to make up for
> whatever it was the trigger would have been doing).  On the other
> hand, if you are bulk loading into a "live" table and so can't drop
> the trigger, then the live table should have good-enough preexisting
> statistics to make the trigger choose a good plan.

My case is indeed unusual as for the whole model of 276 tables there will never be an update nor a delete on any row.
The model is rather short-lived, from a few hours to a few months. COPY FROM/TO are the only ways to get data into the
databaseand back out. And in between there is lots of graph traversal and calculation of convex hulls. But the lengthy
transactionare by far not the common case. 

Having said that, I'm no longer sure if a RDBMS is the right tool for the backend. Maybe indexing and storing with a
plainfull text search engine is. Dunno... 

Thanks again!

--
Horst