Skip site navigation (1) Skip section navigation (2)

Re: Best COPY Performance

From: "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com>
To: "Luke Lonergan" <llonergan(at)greenplum(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Best COPY Performance
Date: 2006-10-31 20:11:00
Message-ID: ce4072df0610311211i3944344dx69a4be966cb548c0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
> > And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks:
>
> So, if we divide 32,000 MB by the real time, we get:
> /data (data):
> 89 MB/s write
> 38 MB/s read
... snip ...
> The read speed on your /data volume is awful to the point where you should
> consider it broken and find a fix.  A quick comparison: the same number on a
> 16 drive internal SATA array with 7200 RPM disks gets 950 MB/s read, about
> 25 times faster for about 1/4 the price.

I managed to get approval to shut down the Oracle instance and reran
the dd's on the SAN (/data) and came up with about 60MB/s write (I had
missed the 'sync' in the previous runs) and about 58 MB/s read, still
no comparison on your SATA arrary.  Any recommendations on what to
look at to find a fix?  One thing which I never mentioned was that I
am using ext3 mounted with noatime,data=writeback.

An interesting note (at least to me) is the inverse relationship
between free memory and bo when writing with dd, i.e:

$ vmstat 5
r b   swpd   free   buff   cache si  so bi     bo   in  cs us sy id wa
0 3 244664 320688 23588 15383120  0   0  0     28 1145 197  0  1 74 25
2 6 244664 349488 22276 15204980  0   0  0     24 1137 188  0  1 75 25
2 6 244664  28264 23024 15526552  0   0  0  65102 1152 335  0 12 60 28
2 4 244664  28968 23588 15383120  0   0  1 384386 1134 372  0 19 34 47
1 5 244664  28840 23768 15215728  0   0  1 438482 1144 494  0 24 33 43
0 5 247256  41320 20144 15212788  0 524  0  57062 1142 388  0  6 43 51
1 6 247256  29096 19588 15226788  0   0  5  60999 1140 391  0 15 42 43

Is this because of the kernel attempting to cache the file in memory?

In response to

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2006-10-31 20:16:24
Subject: Re: Best COPY Performance
Previous:From: Andreas KostyrkaDate: 2006-10-31 15:03:55
Subject: Re: Index ignored on column containing mostly 0 values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group