Thread: Reading data in bulk - help?

Reading data in bulk - help?

From
Chris Huston
Date:
I've got an application that needs to chunk through ~2GB of data. The
data is ~7000 different sets of 300 records each. I put all of the data
into a postgres database but that doesn't look like its going to work
because of how the data lives on the disk.

When the app runs on a 500 Mhz G4 the CPU is 30% idle... the processing
application eating about 50%, postgres taking about 10%. I don't know
how to tell for sure but it looks like postgres is blocking on disk i/o.

For a serial scan of the postgres table (e.g. "select * from
datatable"), "iostat" reports 128K per transfer, ~140 tps and between
14 and 20 MB/s from disk0 - with postgres taking more than 90% CPU.

If I then run a loop asking for only the 300 records at a time (e.g.
"select from datatable where group_id='123'"), iostat reports 8k per
transfer, ~200 tps, less than 1MB/s throughput and postgres taking ~10%
CPU. (There is an index defined for group_id and EXPLAIN says it's
being used.)

So I'm guessing that postgres is jumping all over the disk and my app
is just waiting on data. Is there a way to fix this? Or should I move
to a scientific data file format like NCSA's HDF?

I need to push new values into each of the 7000 datasets once or twice
a day and then read-process the entire data set as many times as I can
in a 12 hour period - nearly every day of the year. Currently there is
only single table but I had planned to add several others.

Thanks,
- Chris


Re: Reading data in bulk - help?

From
Josh Berkus
Date:
Chris,

> I've got an application that needs to chunk through ~2GB of data. The
> data is ~7000 different sets of 300 records each. I put all of the data
> into a postgres database but that doesn't look like its going to work
> because of how the data lives on the disk.

Your problem is curable through 4 steps:

1) adjust your postgresql.conf to appropriate levels for memory usage.

2) if those sets of 300 are blocks in some contiguous order, then cluster them
to force their physical ordering on disk to be the same order you want to
read them in.   This will require you to re-cluster whenever you change a
significant number of records, but from the sound of it that happens in
batches.

3) Get better disks, preferrably a RAID array, or just very fast scsi if the
database is small.    If you're budget-constrained, Linux software raid (or
BSD raid) on IDE disks is cheap.  What kind of RAID depends on what else
you'll be doing with the app; RAID 5 is better for read-only access, RAID 1+0
is better for read-write.

4) Make sure that you aren't dumping the data to the same disk postgreSQL
lives on!   Preferably, make sure that your swap partition is on a different
disk/array from postgresql.   If the computing app is complex and requires
disk reads aside from postgres data, you should make sure that it lives on
yet another disk.   Or you can simplify this with a good, really large
multi-channel RAID array.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Re: Reading data in bulk - help?

From
Chris Huston
Date:
Thanks Josh that helped. I had gone looking for some kind of cluster
option but was looking under create database, create index and
initlocation - didn't see the CLUSTER index ON table.

I ran the CLUSTER which took about 2 1/2 hours to complete. That
improved the query performance about 6x - which is great - but is still
taking 26 minutes to do what a serial read does in about 2 1/2 minutes.

At this point I'm ok because each fetch is taking around 200
milliseconds from call to the time the data is ready. The processing
takes 300-600ms per batch. I've got the fetch and the processing
running in separate threads so even if postgres was running faster it
wouldn't help this implementation.

However, "iostat" is still reporting average size per transfer of about
10kB and total thru-put of about 1MB/s. The transfers per second went
from >200/s to about 80/s. It still seams like it ought to be a faster.

The system is currently running on a single processor 500Mhz G4. We're
likely to move to a two processor 2Ghz G5 in the next few months. Then
each block may take only a 30-60 milliseconds to complete and their can
be two concurrent blocks processing at once.

Sometime before then I need to figure out how to cut the fetch times
from the now 200ms to something like 10ms. There are currently
1,628,800 records in the single data table representing 6817 groups.
Each group has 2 to 284 records - with 79% having the max 284 (max
grows by 1 every day - although the value may change throughout the
day). Each record is maybe 1 or 2k so ideally each batch/group should
require 284-568k - at 10MB/s - that'd be

RELATED QUESTION: How now do I speed up the following query: "select
distinct group_id from datatable"? Which results in a sequential scan
of the db. Why doesn't it use the group_id index? I only do this once
per run so it's not as critical as the fetch speed which is done 6817
times.

Thanks for the help!
- Chris

On Tuesday, Sep 9, 2003, at 18:11 America/Denver, Josh Berkus wrote:

> Chris,
>
>> I've got an application that needs to chunk through ~2GB of data. The
>> data is ~7000 different sets of 300 records each. I put all of the
>> data
>> into a postgres database but that doesn't look like its going to work
>> because of how the data lives on the disk.
>
> Your problem is curable through 4 steps:
>
> 1) adjust your postgresql.conf to appropriate levels for memory usage.
>
> 2) if those sets of 300 are blocks in some contiguous order, then
> cluster them
> to force their physical ordering on disk to be the same order you want
> to
> read them in.   This will require you to re-cluster whenever you
> change a
> significant number of records, but from the sound of it that happens in
> batches.
>
> 3) Get better disks, preferrably a RAID array, or just very fast scsi
> if the
> database is small.    If you're budget-constrained, Linux software
> raid (or
> BSD raid) on IDE disks is cheap.  What kind of RAID depends on what
> else
> you'll be doing with the app; RAID 5 is better for read-only access,
> RAID 1+0
> is better for read-write.
>
> 4) Make sure that you aren't dumping the data to the same disk
> postgreSQL
> lives on!   Preferably, make sure that your swap partition is on a
> different
> disk/array from postgresql.   If the computing app is complex and
> requires
> disk reads aside from postgres data, you should make sure that it
> lives on
> yet another disk.   Or you can simplify this with a good, really large
> multi-channel RAID array.
>
> --
> -Josh Berkus
>  Aglio Database Solutions
>  San Francisco
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly


Re: Reading data in bulk - help?

From
Dennis Bjorklund
Date:
On Wed, 10 Sep 2003, Chris Huston wrote:

> Sometime before then I need to figure out how to cut the fetch times
> from the now 200ms to something like 10ms.

You didn't say anything about Joshs first point of adjusting
postgresql.conf to match your machine. Settings like effective_cache_size
you almost always want to increase from the default setting, also shared
memory.

--
/Dennis


Re: Reading data in bulk - help?

From
"Magnus Naeslund(w)"
Date:
Chris Huston said:
> Thanks Josh that helped. I had gone looking for some kind of cluster
> option but was looking under create database, create index and
> initlocation - didn't see the CLUSTER index ON table.
>
> I ran the CLUSTER which took about 2 1/2 hours to complete. That
> improved the query performance about 6x - which is great - but is still
> taking 26 minutes to do what a serial read does in about 2 1/2 minutes.
>
> At this point I'm ok because each fetch is taking around 200
> milliseconds from call to the time the data is ready. The processing
> takes 300-600ms per batch. I've got the fetch and the processing
> running in separate threads so even if postgres was running faster it
> wouldn't help this implementation.
>
> However, "iostat" is still reporting average size per transfer of about
> 10kB and total thru-put of about 1MB/s. The transfers per second went
> from >200/s to about 80/s. It still seams like it ought to be a faster.
>
> The system is currently running on a single processor 500Mhz G4. We're
> likely to move to a two processor 2Ghz G5 in the next few months. Then
> each block may take only a 30-60 milliseconds to complete and their can
> be two concurrent blocks processing at once.
>
> Sometime before then I need to figure out how to cut the fetch times
> from the now 200ms to something like 10ms. There are currently
> 1,628,800 records in the single data table representing 6817 groups.
> Each group has 2 to 284 records - with 79% having the max 284 (max
> grows by 1 every day - although the value may change throughout the
> day). Each record is maybe 1 or 2k so ideally each batch/group should
> require 284-568k - at 10MB/s - that'd be
>
> RELATED QUESTION: How now do I speed up the following query: "select
> distinct group_id from datatable"? Which results in a sequential scan
> of the db. Why doesn't it use the group_id index? I only do this once
> per run so it's not as critical as the fetch speed which is done 6817
> times.
>
> Thanks for the help!
> - Chris
>

How are you fetching the data?
If you are using cursors, be sure to fetch a substatial bit at a time so
that youre not punished by latency.
I got a big speedup when i changed my original clueless code to fetch 64
rows in a go instead of only one.

Magnus



Re: Reading data in bulk - help?

From
Josh Berkus
Date:
Chris,

> The system is currently running on a single processor 500Mhz G4. We're
> likely to move to a two processor 2Ghz G5 in the next few months. Then
> each block may take only a 30-60 milliseconds to complete and their can
> be two concurrent blocks processing at once.

What about explaining your disk setup?   Or mentioning postgresql.conf?   For
somebody who wants help, you're ignoring a lot of advice and questions.

Personally, I'm not going to be of any further help until you report back on
the other 3 of 4 options.

> RELATED QUESTION: How now do I speed up the following query: "select
> distinct group_id from datatable"? Which results in a sequential scan
> of the db. Why doesn't it use the group_id index? I only do this once
> per run so it's not as critical as the fetch speed which is done 6817
> times.

Because it can't until PostgreSQL 7.4, which has hash aggregates.    Up to
7.3, we have to use seq scans for all group bys.  I'd suggest that you keep a
table of group_ids, instead.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Reading data in bulk - help?

From
Chris Huston
Date:
On Wednesday, Sep 10, 2003, at 11:16 America/Denver, Josh Berkus wrote:

> What about explaining your disk setup?   Or mentioning
> postgresql.conf?   For
> somebody who wants help, you're ignoring a lot of advice and questions.
>
> Personally, I'm not going to be of any further help until you report
> back on
> the other 3 of 4 options.

EEEK! Peace. Sorry I didn't include that info in the response.

1) Memory - clumsily adjusted shared_buffer - tried three values: 64,
128, 256 with no discernible change in performance. Also adjusted,
clumsily, effective_cache_size to 1000, 2000, 4000 - with no
discernible change in performance. I looked at the Admin manual and
googled around for how to set these values and I confess I'm clueless
here. I have no idea how many kernel disk page buffers are used nor do
I understand what the "shared memory buffers" are used for (although
the postgresql.conf file hints that it's for communication between
multiple connections). Any advice or pointers to articles/docs is
appreciated.

2) Clustering - tried it - definite improvement - thanks for the tip

3) RAID - haven't tried it - but I'm guessing that the speed
improvement from a RAID 5 may be on the order of 10x - which I can
likely get from using something like HDF. Since the data is unlikely to
grow beyond 10-20gig, a fast drive and firewire ought to give me the
performance I need. I know experimentally that the current machine can
sustain a 20MB/s transfer rate which is 20-30x the speed of these
queries. (If there's any concern about my enthusiasm for postgres - no
worries - I've been very happy with it on several projects - it might
not be the right tool for this kind of job - but I haven't come to that
conclusion yet.)

4) I'd previously commented out the output/writing steps from the app -
to isolate read performance.

On Wednesday, Sep 10, 2003, at 05:47 America/Denver, Magnus Naeslund(w)
wrote:
>
> How are you fetching the data?
> If you are using cursors, be sure to fetch a substatial bit at a time
> so
> that youre not punished by latency.
> I got a big speedup when i changed my original clueless code to fetch
> 64
> rows in a go instead of only one.
That's an excellent question... I hadn't thought about it. I'm using a
JDBC connection... I have no idea (yet) how the results are moving
between postgres and the client app. I'm testing once with the app and
the DB on the same machine (to remove network latency) and once with
db/app on separate machines. However, I wonder if postgres is blocking
on network io (even if it's the loopback interface) and not on disk?!

I'll definitely look into it. Maybe I'll try a loop in psql and see
what the performance looks like. Thanks Magnus.

On Wednesday, Sep 10, 2003, at 07:05 America/Denver, Sean McCorkle
wrote:

>     I ended up solving the problem by going "retro" and using the
>     quasi-database functions of unix and flat files:  grep, sort,
>     uniq and awk.
That's an cool KISS approach. If I end up moving out of postgres I'll
speed test this approach against HDF. Thanks.


This is a very helpful list,
- Chris


Re: Reading data in bulk - help?

From
William Yu
Date:
> 1) Memory - clumsily adjusted shared_buffer - tried three values: 64,
> 128, 256 with no discernible change in performance. Also adjusted,
> clumsily, effective_cache_size to 1000, 2000, 4000 - with no discernible
> change in performance. I looked at the Admin manual and googled around
> for how to set these values and I confess I'm clueless here. I have no
> idea how many kernel disk page buffers are used nor do I understand what
> the "shared memory buffers" are used for (although the postgresql.conf
> file hints that it's for communication between multiple connections).
> Any advice or pointers to articles/docs is appreciated.

The standard procedure is 1/4 of your memory for shared_buffers. Easiest
way to calculate would be ###MB / 32 * 1000. E.g. if you have 256MB of
memory, your shared_buffers should be 256 / 32 * 1000 = 8000.

The remaining memory you have leftover should be "marked" as OS cache
via the effective_cache_size setting. I usually just multiply the
shared_buffers value by 3 on systems with a lot of memory. With less
memory, OS/Postgres/etc takes up a larger percentage of memory so values
of 2 or 2.5 would be more accurate.


Re: Reading data in bulk - help?

From
Josh Berkus
Date:
Chris,

> 1) Memory - clumsily adjusted shared_buffer - tried three values: 64,
> 128, 256 with no discernible change in performance. Also adjusted,
> clumsily, effective_cache_size to 1000, 2000, 4000 - with no
> discernible change in performance. I looked at the Admin manual and
> googled around for how to set these values and I confess I'm clueless
> here. I have no idea how many kernel disk page buffers are used nor do
> I understand what the "shared memory buffers" are used for (although
> the postgresql.conf file hints that it's for communication between
> multiple connections). Any advice or pointers to articles/docs is
> appreciated.

You want values *much* higher than that.   How much RAM do you have?  See:
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

For example, if you have 512mb RAM, I'd crank up the shared buffers to 8000.
the sort_mem to 8mb, and the effective_cache_size to 24,000.

> 3) RAID - haven't tried it - but I'm guessing that the speed
> improvement from a RAID 5 may be on the order of 10x

Probably not ... more like 1.5x - 2.0x, but that's still a significant help,
yes?  Also, the advantage will get better the more your data grows.

>  - which I can
> likely get from using something like HDF.

HDF sucks for I/O speed.    XServe will become a much more significant option
in the market when Apple can bring themselves to abandon HDF, and adopt XFS
or something.  This is part of your problem.

> Since the data is unlikely to
> grow beyond 10-20gig, a fast drive and firewire ought to give me the
> performance I need.

Not sure about that.   Is Firewire really faster for I/O than modern SCSI or
233mhz ATA?    I don't do much Mac anymore, but I'd the impression that
Firewire was mainly for peripherals ....

What is important for your app in terms of speed is to get the data coming
from multiple drives over multiple channels.   Were it a PC, I'd recommend a
motherboard with 4 IDE channels or Serial ATA, and spreading the data over 4
drives via RAID 0 or RAID 5, and adding dual processors.  Then you could use
multiple postgres connections to read different parts of the table
simultaneously.

>  I know experimentally that the current machine can
> sustain a 20MB/s transfer rate which is 20-30x the speed of these
> queries.

That is interesting.   Adjust your PostgreSQL.conf and see what results you
get.   It's possible that PostgreSQL is convinced that you have little or no
RAM because of your .conf settings, and is swapping stuff to temp file on
disk.

> 4) I'd previously commented out the output/writing steps from the app -
> to isolate read performance.

OK.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: Reading data in bulk - help?

From
"Christopher Kings-Lynne"
Date:
> You want values *much* higher than that.   How much RAM do you have?  See:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

Now THAT is a remarkable document!  I vote for putting that information into
the PostgreSQL documentation tree.

Chris