| From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
|---|---|
| To: | Horst Dehmer <horst(dot)dehmer(at)gmail(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Insert performance for large transaction with multiple COPY FROM |
| Date: | 2013-01-12 01:17:40 |
| Message-ID: | CAMkU=1zx2MXdT+za1PokYc5SZEe4j=qaHG8GuG=13UOQ3oRScQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Friday, January 11, 2013, Horst Dehmer 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.
>
It sure sounds like you don't have enough RAM to hold the foreign-key table
data needed to check the constraints, so every insert needs one disk
revolution to fetch the data.
If you drop the indexes and constraints one at a time until it speeds up,
is there a certain one that is the culprit?
You can look in pg_statio_user_tables to see what tables and indexes have
high io being driven by the bulk loading.
Use "top" to see of the server is mostly IO bound or CPU bound.
Cheers,
Jeff
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Claudio Freire | 2013-01-12 01:17:59 | Re: Insert performance for large transaction with multiple COPY FROM |
| Previous Message | Horst Dehmer | 2013-01-11 23:55:36 | Insert performance for large transaction with multiple COPY FROM |