Thread: Very poor performance loading 100M of sql data using copy
Hi all:
We are loading in a number (100+) of sql files that are about 100M in
size. It takes about three hours to load the file. There is very
little load on the database other than the copy from operations.
We are running postgresql-8.1.3 under Centos 4 on a RAID 1/0 array
with 4 disks (so we have only one spindle). The partitions are set up
in an LVM and iostat 5 shows (for one report):
avg-cpu: %user %nice %sys %iowait %idle
1.70 0.00 0.80 51.40 46.10
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 179.20 1472.00 2561.60 7360 12808
sda1 0.00 0.00 0.00 0 0
sda2 385.20 1462.40 2561.60 7312 12808
dm-0 0.80 0.00 6.40 0 32
dm-1 0.00 0.00 0.00 0 0
dm-2 0.00 0.00 0.00 0 0
dm-3 0.00 0.00 0.00 0 0
dm-4 4.40 0.00 35.20 0 176
dm-5 0.00 0.00 0.00 0 0
dm-6 380.00 1462.40 2520.00 7312 12600
dm-6 is where the data files reside and dm-4 is where the WAL archives
are kept. Note all the DM's are on the same RAID 0 device /dev/sda2.
A sample psql command file to load the data is:
BEGIN;
COPY peers (observe_start, observe_end, geo_scope, geo_value,
peer_a, peer_b) FROM stdin WITH NULL AS '';
(data here)
3 more copy commands to different tables w/ data
COMMIT;
The primary keys for the tables being loaded are composite keys using
4-7 columns, so that may be part of the issue.
From postgres.conf
shared_buffers = 3000
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
max_locks_per_transaction).
work_mem = 2048 # min 64, size in KB
maintenance_work_mem = 65536 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
The prior settings for work_mem/maintenance_work_mem were the
defaults:
#work_mem = 1024 # min 64, size in KB
#maintenance_work_mem = 16384 # min 1024, size in KB
I also took a look at disk-io hit rates:
# select * from pg_statio_user_tables;
relid | schema | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit |
-------+--------+--------------+----------------+---------------+---------------+--------------+
17282 | public | providers | 179485097 | 78832253 | 835008 | 196903582 |
17264 | public | events | 0 | 0 | | |
17262 | public | days | 495 | 219 | 478 | 16 |
17276 | public | peers | 147435004 | 114304828 | 1188908 | 295569499 |
17288 | public | rankings | 564638938 | 345456664 | 275607291 | 1341727605 |
17270 | public | market_share | 131932 | 90048 | 5408 | 182100 |
market_share did have one tidx_blks_read reported, but all the other
fields (toast_blks_read, toast_blks_hit, tidx_blks_read,
tidx_blks_hit) were empty for all rows.
This looks like we have whole indexes in memory except for the days
table, which has a low update rate, so I am not worried about that.
However for the heap_blks_read and heap_blks_hit we get a different
story:
relname | hit_percent
--------------+-----------
providers | 43.92
days | 44.24
peers | 77.52
rankings | 61.18
market_share | 68.25
so we see a 43 % hit ratio for providers to 77% hit ratio for
peers. Not horrible hit rates given that we are more data warehousing
than OLTP, but I am not sure what effect increasing these (by
increasing shared_buffers I think) will have on the COPY operation. I
would suspect none.
To try to solve this speed issue:
I checked the logs and was seeing a few
2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds apart)
of these, so I changed:
checkpoint_segments = 30
checkpoint_warning = 150
in postgres.conf and reloaded postgres. I have only seen one of these
log messages in the past week.
I have turned of autovacuum.
I have increased the maintenance_work_mem as mentioned
above. (Although I didn't expect it to do anything unless we
drop/recreate indexes).
I have increased work_mem as mentioned above.
The only things I can think of is increasing shared memory, or
dropping indexes.
I don't see any indication in the docs that increasing shared memory
would help speed up a copy operation.
The only indexes we have to drop are the ones on the primary keys
(there is one non-primary key index in the database as well).
Can you drop an index on the primary key for a table and add it back
later? Am I correct in saying: the primary key index is what enforces
the unique constraint in the table? If the index is dropped and
non-unique primary key data has been added, what happens when you
re-add the index?
Does anybody have any things to check/ideas on why loading a 100Mb sql
file using psql would take 3 hours?
Thanks in advance for any ideas.
--
-- rouilj
John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111
John Rouillard wrote: > We are running postgresql-8.1.3 under Centos 4 You should upgrade, at least to the latest minor release of the 8.1 series (8.1.11), as there has been a bunch of important bug and security fixes. Or even better, upgrade to 8.3, which has reduced the storage size of especially variable length datatypes like text/char/varchar in particular. As your COPY is I/O bound, reducing storage size will translate directly to improved performance. > dm-6 is where the data files reside and dm-4 is where the WAL archives > are kept. Note all the DM's are on the same RAID 0 device /dev/sda2. Another reason to upgrade to 8.3: if you CREATE or TRUNCATE the table in the same transaction as you COPY into it, you can avoid WAL logging of the loaded data, which will in the best case double your performance as your WAL is on the same physical drives as the data files. > The only indexes we have to drop are the ones on the primary keys > (there is one non-primary key index in the database as well). > > Can you drop an index on the primary key for a table and add it back > later? Am I correct in saying: the primary key index is what enforces > the unique constraint in the table? If the index is dropped and > non-unique primary key data has been added, what happens when you > re-add the index? Yes, the index is what enforces the uniqueness. You can drop the primary key constraint, and add it back after the load with ALTER TABLE. If the load introduces any non-unique primary keys, adding the primary key constraint will give you an error and fail. Dropping and recreating the indexes is certainly worth trying. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Mon, Apr 28, 2008 at 06:53:09PM +0100, Heikki Linnakangas wrote:
> John Rouillard wrote:
> >We are running postgresql-8.1.3 under Centos 4
> You should upgrade, at least to the latest minor release of the 8.1
> series (8.1.11), as there has been a bunch of important bug and security
> fixes. Or even better, upgrade to 8.3, which has reduced the storage
> size of especially variable length datatypes like text/char/varchar in
> particular. As your COPY is I/O bound, reducing storage size will
> translate directly to improved performance.
Yup. Just saw that suggestion in an unrelated email.
> >dm-6 is where the data files reside and dm-4 is where the WAL archives
> >are kept. Note all the DM's are on the same RAID 0 device /dev/sda2.
>
> Another reason to upgrade to 8.3: if you CREATE or TRUNCATE the table in
> the same transaction as you COPY into it, you can avoid WAL logging of
> the loaded data, which will in the best case double your performance as
> your WAL is on the same physical drives as the data files.
We can't do this as we are backfilling a couple of months of data into
tables with existing data.
> >The only indexes we have to drop are the ones on the primary keys
> >(there is one non-primary key index in the database as well).
> >
> >Can you drop an index on the primary key for a table and add it back
> >later? Am I correct in saying: the primary key index is what enforces
> >the unique constraint in the table? If the index is dropped and
> >non-unique primary key data has been added, what happens when you
> >re-add the index?
>
> Yes, the index is what enforces the uniqueness. You can drop the primary
> key constraint, and add it back after the load with ALTER TABLE. If the
> load introduces any non-unique primary keys, adding the primary key
> constraint will give you an error and fail.
That's the part I am worried about. I guess using psql to delete the
problem row then re-adding the index will work.
> Dropping and recreating the indexes is certainly worth trying.
Thanks for the info.
--
-- rouilj
John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111
On Mon, 28 Apr 2008, John Rouillard wrote: > 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds apart) > so I changed: > checkpoint_segments = 30 > checkpoint_warning = 150 That's good, but you might go higher than 30 for a bulk loading operation like this, particularly on 8.1 where checkpoints are no fun. Using 100 is not unreasonable. > shared_buffers = 3000 > I don't see any indication in the docs that increasing shared memory > would help speed up a copy operation. The index blocks use buffer space, and what ends up happening if there's not enough memory is they are written out more than they need to be (and with your I/O hardware you need to avoid writes unless absolutely necessary). Theoretically the OS is caching around that situation but better to avoid it. You didn't say how much RAM you have, but you should start by a factor of 10 increase to 30,000 and see if that helps; if so, try making it large enough to use 1/4 of total server memory. 3000 is only giving the server 24MB of RAM to work with, and it's unfair to expect it to work well in that situation. While not relevant to this exercise you'll need to set effective_cache_size to a useful value one day as well. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
John Rouillard wrote: > We can't do this as we are backfilling a couple of months of data > into tables with existing data. Is this a one off data loading of historic data or an ongoing thing? >>> The only indexes we have to drop are the ones on the primary keys >>> (there is one non-primary key index in the database as well). If this amount of data importing is ongoing then one thought I would try is partitioning (this could be worthwhile anyway with the amount of data you appear to have). Create an inherited table for the month being imported, load the data into it, then add the check constraints, indexes, and modify the rules/triggers to handle the inserts to the parent table. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Tue, Apr 29, 2008 at 05:19:59AM +0930, Shane Ambler wrote:
> John Rouillard wrote:
>
> >We can't do this as we are backfilling a couple of months of data
> >into tables with existing data.
>
> Is this a one off data loading of historic data or an ongoing thing?
Yes it's a one off bulk data load of many days of data. The daily
loads will also take 3 hour's but that is ok since we only do those
once a day so we have 21 hours of slack in the schedule 8-).
> >>>The only indexes we have to drop are the ones on the primary keys
> >>> (there is one non-primary key index in the database as well).
>
> If this amount of data importing is ongoing then one thought I would try
> is partitioning (this could be worthwhile anyway with the amount of data
> you appear to have).
> Create an inherited table for the month being imported, load the data
> into it, then add the check constraints, indexes, and modify the
> rules/triggers to handle the inserts to the parent table.
Hmm, interesting idea, worth considering if we have to do this again
(I hope not).
Thaks for the reply.
--
-- rouilj
John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111
On Mon, Apr 28, 2008 at 02:16:02PM -0400, Greg Smith wrote:
> On Mon, 28 Apr 2008, John Rouillard wrote:
>
> > 2008-04-21 11:36:43 UTC @(2761)i: LOG: checkpoints ... (27 seconds
> > apart)
> > so I changed:
> > checkpoint_segments = 30
> > checkpoint_warning = 150
>
> That's good, but you might go higher than 30 for a bulk loading operation
> like this, particularly on 8.1 where checkpoints are no fun. Using 100 is
> not unreasonable.
Ok. I can do that. I chose 30 to make the WAL logs span the 5 minute
checkpoint_timeout = 300
so that the 30 segments wouldn't wrap over before the 5 minute
checkpoint that usually occurs. Maybe I should increase both the
timeout and the segments?
> >shared_buffers = 3000
> >I don't see any indication in the docs that increasing shared memory
> >would help speed up a copy operation.
>
> The index blocks use buffer space, and what ends up happening if there's
> not enough memory is they are written out more than they need to be (and
> with your I/O hardware you need to avoid writes unless absolutely
> necessary).
I forgot to mention the raid 1/0 is on a 3ware 9550SX-4LP raid card
setup as raid 1/0. The write cache is on and autoverify is turned off.
> Theoretically the OS is caching around that situation but
> better to avoid it.
The system is using 6-8MB of memory for cache.
> You didn't say how much RAM you have,
16GB total, but 8GB or so is taken up with other processes.
> but you should
> start by a factor of 10 increase to 30,000 and see if that helps; if so,
> try making it large enough to use 1/4 of total server memory. 3000 is
> only giving the server 24MB of RAM to work with, and it's unfair to expect
> it to work well in that situation.
So swap the memory usage from the OS cache to the postgresql process.
Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a
better setting. So I'll try 300000 to start (1/8 of memory) and see
what it does to the other processes on the box.
> While not relevant to this exercise you'll need to set
> effective_cache_size to a useful value one day as well.
This is a very lightly loaded database, a few queries/hour usually
scattered across the data set, so hopefully that won't be much of an
issue.
--
-- rouilj
John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111
On Tue, 29 Apr 2008, John Rouillard wrote: > So swap the memory usage from the OS cache to the postgresql process. > Using 1/4 as a guideline it sounds like 600,000 (approx 4GB) is a > better setting. So I'll try 300000 to start (1/8 of memory) and see > what it does to the other processes on the box. That is potentially a good setting. Just be warned that when you do hit a checkpoint with a high setting here, you can end up with a lot of data in memory that needs to be written out, and under 8.2 that can cause an ugly spike in disk writes. The reason I usually threw out 30,000 as a suggested starting figure is that most caching disk controllers can buffer at least 256MB of writes to keep that situation from getting too bad. Try it out and see what happens, just be warned that's the possible downside of setting shared_buffers too high and therefore you might want to ease into that more gradually (particularly if this system is shared with other apps). x -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD