Thread: Reading data in bulk - help?
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
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
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
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
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
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
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
> 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.
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
> 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