Re: [PERFORM] Best COPY 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? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
Worky (!), On 10/31/06 12:11 PM, Worky Workerson [EMAIL PROTECTED] wrote: 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. You can try setting the max readahead like this: /sbin/blockdev --setra 16384 /dev/sd[a-z] It will set the max readahead to 16MB for whatever devices are in /dev/sd[a-z] for the booted machine. You'd need to put the line(s) in /etc/rc.d/rc.local to have the setting persist on reboot. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Nope, nothing in the log. I have set: wal_buffers=128 checkpoint_segments=128 checkpoint_timeout=3000 which I thought was rather generous. Perhaps I should set it even higher for the loads? But depending on your shared_buffer and bgwriter settings (as well as how much WAL traffic you're generating, you could still end up with big slugs of work to be done when checkpoints happen. If you set checkpoint_warning to 3001, you'll see exactly when checkpoints are happening, so you can determine if that's an issue. I did that, and I get two log messages, seeing checkpoints happening at 316 and 147 seconds apart on my load of a 1.9 GB file. Is this an issue? Thanks! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
Maybe it is just the PK *build* that slows it down, but I just tried some small scale experiments on my MacBook Pro laptop (which has the same disk performance as your server) and I get only a 10-15% slowdown from having a PK on an integer column. The 10-15% slowdown was on 8.1.5 MPP, so it used both CPUs to build the index and load at about 15 MB/s. ...snip... What is your schema for the table? A single IP4 PK and 21 VARCHARs. It takes about 340 seconds to load a 1.9GB file with the PK index, and about 230 seconds without it (ALTER TABLE mytable DROP CONSTRAINT mytable_pkey), which is a pretty significant (~30%) savings. If I read the vmstat output correctly (i.e. the cpu us column), I'm still at 12% and thus still cpu-bound, except for when the checkpoint occurs, i.e (everything is chugging along similar to the first line, then stuff gets wonky): r b swpdfree buff cache si so bi bo in cs us sy id wa 2 0 279028 4620040 717940 9697664 0 0 0 19735 1242 7534 13 4 82 1 1 2 279028 4476120 718120 9776840 0 0 0 2225483 1354 5269 13 6 71 11 0 3 279028 4412928 718320 9866672 0 0 2 19746 1324 3978 10 2 69 18 1 1 279028 4334112 718528 9971456 0 0 0 20615 1311 5912 10 3 69 18 0 1 279028 4279904 718608 9995244 0 0 0 134946 1205 674 1 3 85 11 0 2 279028 4307344 718616 9995304 0 0 0 54 1132 247 0 1 77 22 1 0 279028 7411104 718768 6933860 0 0 09942 1148 3618 11 6 80 3 1 0 279028 7329312 718964 7015536 0 0 1 19766 1232 5108 13 2 84 1 Also, as a semi-side note, I only have a single checkpoint without the index, while I have 2 with the index. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
Checkpoints are not an issue here, the vmstat you included was on a 5 second interval, so the 'bursts' were bursting at a rate of 60MB/s. - Luke Msg is shrt cuz m on ma treo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 iirc, he is running quad opteron 885 (8 cores), so if my math is correct he can split up his process for an easy gain. Are you saying that I should be able to issue multiple COPY commands because my I/O wait is low? I was under the impression that I am I/O bound, so multiple simeoultaneous loads would have a detrimental effect ... The reason I asked how many CPUs was to make sense of the 12% usr CPU time in the above. That means you are CPU bound and are fully using one CPU. So you aren't being limited by the I/O in this case, it's the CPU. ... snip ... For now, you could simply split the file in two pieces and load two copies at once, then watch the same vmstat 1 for 10 seconds and look at your bo rate. Significantly higher on average, and a parallel loads were ~30% faster that a single with index builds (240s vs 340s) and about ~45% (150s vs 230s) without the PK index. I'll definitely look into the bizgres java loader. Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
On Tuesday 31 October 2006 21:11, Worky Workerson wrote: One thing which I never mentioned was that I am using ext3 mounted with noatime,data=writeback. You might also want to try with data=ordered. I have noticed that nowadays it seems to be a bit faster, but not much. I don't know why, maybe it has got more optimization efforts. Teemu ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Luke Lonergan Sent: Saturday, October 28, 2006 12:07 AM To: Worky Workerson; Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Best COPY Performance Worky, On 10/27/06 8:47 PM, Worky Workerson [EMAIL PROTECTED] wrote: Are you saying that I should be able to issue multiple COPY commands because my I/O wait is low? I was under the impression that I am I/O bound, so multiple simeoultaneous loads would have a detrimental effect ... ... I agree with Merlin that you can speed things up by breaking the file up. Alternately you can use the OSS Bizgres java loader, which lets you specify the number of I/O threads with the -n option on a single file. As a result of this thread, and b/c I've tried this in the past but never had much success at speeding the process up, I attempted just that here except via 2 psql CLI's with access to the local file. 1.1M rows of data varying in width from 40 to 200 characters COPY'd to a table with only one text column, no keys, indexes, c took about 15 seconds to load. ~73K rows/second. I broke that file into 2 files each of 550K rows and performed 2 simultaneous COPY's after dropping the table, recreating, issuing a sync on the system to be sure, c and nearly every time both COPY's finish in 12 seconds. About a 20% gain to ~91K rows/second. Admittedly, this was a pretty rough test but a 20% savings, if it can be put into production, is worth exploring for us. B/c I'll be asked, I did this on an idle, dual 3.06GHz Xeon with 6GB of memory, U320 SCSI internal drives and PostgreSQL 8.1.4. Greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
Greg, On 10/30/06 7:09 AM, Spiegelberg, Greg [EMAIL PROTECTED] wrote: I broke that file into 2 files each of 550K rows and performed 2 simultaneous COPY's after dropping the table, recreating, issuing a sync on the system to be sure, c and nearly every time both COPY's finish in 12 seconds. About a 20% gain to ~91K rows/second. Admittedly, this was a pretty rough test but a 20% savings, if it can be put into production, is worth exploring for us. Did you see whether you were I/O or CPU bound in your single threaded COPY? A 10 second vmstat 1 snapshot would tell you/us. With Mr. Workerson (:-) I'm thinking his benefit might be a lot better because the bottleneck is the CPU and it *may* be the time spent in the index building bits. We've found that there is an ultimate bottleneck at about 12-14MB/s despite having sequential write to disk speeds of 100s of MB/s. I forget what the latest bottleneck was. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
Luke Lonergan wrote: Greg, On 10/30/06 7:09 AM, Spiegelberg, Greg [EMAIL PROTECTED] wrote: I broke that file into 2 files each of 550K rows and performed 2 simultaneous COPY's after dropping the table, recreating, issuing a sync on the system to be sure, c and nearly every time both COPY's finish in 12 seconds. About a 20% gain to ~91K rows/second. Admittedly, this was a pretty rough test but a 20% savings, if it can be put into production, is worth exploring for us. Did you see whether you were I/O or CPU bound in your single threaded COPY? A 10 second vmstat 1 snapshot would tell you/us. With Mr. Workerson (:-) I'm thinking his benefit might be a lot better because the bottleneck is the CPU and it *may* be the time spent in the index building bits. We've found that there is an ultimate bottleneck at about 12-14MB/s despite having sequential write to disk speeds of 100s of MB/s. I forget what the latest bottleneck was. I have personally managed to load a bit less then 400k/s (5 int columns no indexes) - on very fast disk hardware - at that point postgresql is completely CPU bottlenecked (2,6Ghz Opteron). Using multiple processes to load the data will help to scale up to about 900k/s (4 processes on 4 cores). Stefan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
Stefan, On 10/30/06 8:57 AM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: We've found that there is an ultimate bottleneck at about 12-14MB/s despite having sequential write to disk speeds of 100s of MB/s. I forget what the latest bottleneck was. I have personally managed to load a bit less then 400k/s (5 int columns no indexes) - on very fast disk hardware - at that point postgresql is completely CPU bottlenecked (2,6Ghz Opteron). 400,000 rows/s x 4 bytes/column x 5 columns/row = 8MB/s Using multiple processes to load the data will help to scale up to about 900k/s (4 processes on 4 cores). 18MB/s? Have you done this? I've not seen this much of an improvement before by using multiple COPY processes to the same table. Another question: how to measure MB/s - based on the input text file? On the DBMS storage size? We usually consider the input text file in the calculation of COPY rate. - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
Luke Lonergan wrote: Stefan, On 10/30/06 8:57 AM, Stefan Kaltenbrunner [EMAIL PROTECTED] wrote: We've found that there is an ultimate bottleneck at about 12-14MB/s despite having sequential write to disk speeds of 100s of MB/s. I forget what the latest bottleneck was. I have personally managed to load a bit less then 400k/s (5 int columns no indexes) - on very fast disk hardware - at that point postgresql is completely CPU bottlenecked (2,6Ghz Opteron). 400,000 rows/s x 4 bytes/column x 5 columns/row = 8MB/s Using multiple processes to load the data will help to scale up to about 900k/s (4 processes on 4 cores). yes I did that about half a year ago as part of the CREATE INDEX on a 1,8B row table thread on -hackers that resulted in some some the sorting improvements in 8.2. I don't think there is much more possible in terms of import speed by using more cores (at least not when importing to the same table) - iirc I was at nearly 700k/s with two cores and 850k/s with 3 cores or such ... 18MB/s? Have you done this? I've not seen this much of an improvement before by using multiple COPY processes to the same table. Another question: how to measure MB/s - based on the input text file? On the DBMS storage size? We usually consider the input text file in the calculation of COPY rate. yeah that is a good questions (and part of the reason why I cited the rows/sec number btw.) Stefan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
Are you saying that I should be able to issue multiple COPY commands because my I/O wait is low? I was under the impression that I am I/O bound, so multiple simeoultaneous loads would have a detrimental effect ... The reason I asked how many CPUs was to make sense of the 12% usr CPU time in the above. That means you are CPU bound and are fully using one CPU. So you aren't being limited by the I/O in this case, it's the CPU. I agree with Merlin that you can speed things up by breaking the file up. Alternately you can use the OSS Bizgres java loader, which lets you specify the number of I/O threads with the -n option on a single file. Thanks, I'll try that on Monday. The other thing to wonder about though is why you are so CPU bound at 5 MB/s. What version of Postgres is this? I was wondering about that as well, and the only thing that I can think of is that its the PK btree index creation on the IP4. PG 8.1.3 x86_64. I installed it via a RH rpm for their Web Services Beta, or something like that. I know I'm a bit behind the times, but getting stuff in (and out) of my isolated lab is a bit of a pain. I'll compile up a 8.2 beta as well and see how that works out. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
Michael (aka Worky), On 10/28/06 5:03 AM, Michael Artz [EMAIL PROTECTED] wrote: PG 8.1.3 x86_64. I installed it via a RH rpm for their Web Services Beta, or something like that. I know I'm a bit behind the times, but getting stuff in (and out) of my isolated lab is a bit of a pain. I'll compile up a 8.2 beta as well and see how that works out. I think 8.1 and 8.2 should be the same in this regard. Maybe it is just the PK *build* that slows it down, but I just tried some small scale experiments on my MacBook Pro laptop (which has the same disk performance as your server) and I get only a 10-15% slowdown from having a PK on an integer column. The 10-15% slowdown was on 8.1.5 MPP, so it used both CPUs to build the index and load at about 15 MB/s. Note that the primary key is the first column. Table public.part Column | Type | Modifiers ---+---+--- p_partkey | integer | not null p_name| character varying(55) | not null p_mfgr| text | not null p_brand | text | not null p_type| character varying(25) | not null p_size| integer | not null p_container | text | not null p_retailprice | double precision | not null p_comment | character varying(23) | not null Indexes: part_pkey PRIMARY KEY, btree (p_partkey) What is your schema for the table? - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
I do have a dirty little secret, one which I wasn't completely aware of until a little while ago. Apparently, someone decided to install Oracle on the server, and use the SAN as the primary tablespace, so that might have something to do with the poor performance of the SAN. At least, I'm hoping that's what is involved. I'll be able to tell for sure when I can rerun the benchmarks on Monday without Oracle. Thank you all for all your help so far. I've learned a ton about Postgres (and life!) from both this thread and this list in general, and the support has been nothing less than spectacular. I'm hoping that the corporate Oracle machine won't shut down my pg projects. On total side note, if anyone knows how to best limit Oracle's impact on a system (i.e. memory usage, etc), I'd be interested. I hate shared DB servers. Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
On 10/27/06, Worky Workerson [EMAIL PROTECTED] wrote: I'm hoping that the corporate Oracle machine won't shut down my pg projects. On total side note, if anyone knows how to best limit Oracle's impact on a system (i.e. memory usage, etc), I'd be interested. rm -rf /usr/local/oracle? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
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'm hoping that the poor performance is a result of Oracle doing random reads while I try and do the sequential read. If not (I'll test on Monday), I'll start looking other places. Any immediate suggestions? Can you provide about 10 seconds worth of vmstat 1 while running your COPY so we can get a global view of the I/O and CPU? Here it is, taken from a spot about halfway through a 'cat file | psql' load, with the Oracle-is-installed-and-running caveat: r b swpd free buffcache si so bibo in cs us sy id wa 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
Worky (that your real name? :-) On 10/27/06 12:08 PM, Worky Workerson [EMAIL PROTECTED] wrote: Here it is, taken from a spot about halfway through a 'cat file | psql' load, with the Oracle-is-installed-and-running caveat: r b swpd free buffcache si so bibo in cs us sy id wa 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 It doesn't look like there's anything else running - the runnable r is about 1. Your bo blocks output rate is about 16MB/s, so divide by 3 and you're about in range with your 5MB/s COPY rate. The interesting thing is that the I/O wait is pretty low. How many CPUs on the machine? Can you send the result of cat /proc/cpuinfo? Is your cat file | psql being done on the DBMS server or is it on the network? - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
On 10/28/06, Luke Lonergan [EMAIL PROTECTED] wrote: Worky (that your real name? :-) On 10/27/06 12:08 PM, Worky Workerson [EMAIL PROTECTED] wrote: Here it is, taken from a spot about halfway through a 'cat file | psql' load, with the Oracle-is-installed-and-running caveat: r b swpd free buffcache si so bibo in cs us sy id wa 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 It doesn't look like there's anything else running - the runnable r is about 1. Your bo blocks output rate is about 16MB/s, so divide by 3 and you're about in range with your 5MB/s COPY rate. The interesting thing is that the I/O wait is pretty low. How many CPUs on the machine? Can you send the result of cat /proc/cpuinfo? Is your cat file | psql being done on the DBMS server or is it on the network? iirc, he is running quad opteron 885 (8 cores), so if my math is correct he can split up his process for an easy gain. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
Worky (that your real name? :-) Nope, its Mike. worky.workerson is just the email that I use for work :) How many CPUs on the machine? Can you send the result of cat /proc/cpuinfo? Not at work at the moment, however I do have quad dual-core opterons, like Merlin mentioned. Is your cat file | psql being done on the DBMS server or is it on the network? Everything is currently being done on the same DB server. The data that is being loaded is on the same 2-disk RAID10 as the OS and WAL. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
On 10/27/06, Merlin Moncure [EMAIL PROTECTED] wrote: r b swpd free buffcache si so bibo in cs us sy id wa 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 It doesn't look like there's anything else running - the runnable r is about 1. Your bo blocks output rate is about 16MB/s, so divide by 3 and you're about in range with your 5MB/s COPY rate. The interesting thing is that the I/O wait is pretty low. How many CPUs on the machine? Can you send the result of cat /proc/cpuinfo? iirc, he is running quad opteron 885 (8 cores), so if my math is correct he can split up his process for an easy gain. Are you saying that I should be able to issue multiple COPY commands because my I/O wait is low? I was under the impression that I am I/O bound, so multiple simeoultaneous loads would have a detrimental effect ... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
Worky, On 10/27/06 8:47 PM, Worky Workerson [EMAIL PROTECTED] wrote: 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 iirc, he is running quad opteron 885 (8 cores), so if my math is correct he can split up his process for an easy gain. Are you saying that I should be able to issue multiple COPY commands because my I/O wait is low? I was under the impression that I am I/O bound, so multiple simeoultaneous loads would have a detrimental effect ... The reason I asked how many CPUs was to make sense of the 12% usr CPU time in the above. That means you are CPU bound and are fully using one CPU. So you aren't being limited by the I/O in this case, it's the CPU. I agree with Merlin that you can speed things up by breaking the file up. Alternately you can use the OSS Bizgres java loader, which lets you specify the number of I/O threads with the -n option on a single file. OTOH, you should find that you will only double your COPY speed with this approach because your write speed as you previously found was limited to 30 MB/s. For now, you could simply split the file in two pieces and load two copies at once, then watch the same vmstat 1 for 10 seconds and look at your bo rate. If this does speed things up, you really should check out the Bizgres Java loader. The other thing to wonder about though is why you are so CPU bound at 5 MB/s. What version of Postgres is this? - Luke ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
On 23 Oct 2006, at 22:59, Jim C. Nasby wrote: http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. http://shootout.alioth.debian.org/gp4/benchmark.php? test=alllang=perllang2=gcc 100x doesn't totally impossible if that is even vaguely accurate and you happen to be using bits of Perl which are a lot slower than the C implementation would be... The slowest things appear to involve calling functions, all the slowest tests involve lots of function calls. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
On 10/25/06, Craig A. James [EMAIL PROTECTED] wrote: Jim C. Nasby wrote: Well, given that perl is using an entire CPU, it sounds like you should start looking either at ways to remove some of the overhead from perl, or to split that perl into multiple processes. I use Perl for big database copies (usually with some processing/transformation along the way) and I've never seen 100% CPU usage except for brief periods, even when copying BLOBS and such. My typical copy divides operations into blocks, for example doing I'm just doing CSV style transformations (and calling a lot of functions along the way), but the end result is a straight bulk load of data into a blank database. And we've established that Postgres can do *way* better than what I am seeing, so its not suprising that perl is using 100% of a CPU. However, I am still curious as to the rather slow COPYs from psql to local disks. Like I mentioned previously, I was only seeing about 5.7 MB/s (1.8 GB / 330 seconds), where it seemed like others were doing substantially better. What sorts of things should I look into? Thanks! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
Hi, Worky, Worky Workerson wrote: $ psql -c COPY my_table TO STDOUT my_data $ ls my_data 2018792 edgescape_pg_load $ time cat my_data | psql -c COPY mytable FROM STDIN real5m43.194s user0m35.412s sys 0m9.567s That's via PSQL, and you get about 5 MB/Sec. On a table with no indices, triggers and contstraints, we managed to COPY about 7-8 megabytes/second with psql over our 100 MBit network, so here the network was the bottleneck. hmm, this makes me think that either my PG config is really lacking, or that the SAN is badly misconfigured, as I would expect it to outperform a 100Mb network. As it is, with a straight pipe to psql COPY, I'm only working with a little over 5.5 MB/s. Could this be due to the primary key index updates? Yes, index updates cause both CPU load, and random disk access (which is slow by nature). HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
On 10/23/06, Worky Workerson [EMAIL PROTECTED] wrote: The disk load is where I start to get a little fuzzy, as I haven't played with iostat to figure what is normal. The local drives contain PG_DATA as well as all the log files, but there is a tablespace on the FibreChannel SAN that contains the destination table. The disk usage pattern that I see is that there is a ton of consistent activity on the local disk, with iostat reporting an average of 30K Blk_wrtn/s, which I assume is the log files. Every several seconds there is a massive burst of activity on the FC partition, to the tune of 250K Blk_wrtn/s. On a table with no indices, triggers and contstraints, we managed to COPY about 7-8 megabytes/second with psql over our 100 MBit network, so here the network was the bottleneck. I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Have you determined that pg is not swapping? try upping maintenance_work_mem. What exactly is your architecture? is your database server direct attached to the san? if so, 2gb/4gb fc? what san? have you bonnie++ the san? basically, you can measure iowait to see if pg is waiting on your disks. regarding perl, imo the language performance is really about which libraries you use. the language itself is plenty fast. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
Mr. Worky Workerson, On 10/25/06 5:03 AM, Worky Workerson [EMAIL PROTECTED] wrote: However, I am still curious as to the rather slow COPYs from psql to local disks. Like I mentioned previously, I was only seeing about 5.7 MB/s (1.8 GB / 330 seconds), where it seemed like others were doing substantially better. What sorts of things should I look into? It's probable that you have a really poor performing disk configuration. Judging from earlier results, you may only be getting 3 x 5.7 = 17 MB/s of write performance to your disks, which is about 1/4 of a single disk drive. Please run this test and report the time here: 1) Calculate the size of 2x memory in 8KB blocks: # of blocks = 250,000 x memory_in_GB Example: 250,000 x 16GB = 4,000,000 blocks 2) Benchmark the time taken to write 2x RAM sequentially to your disk: time bash -c dd if=/dev/zero of=bigfile bs=8k count=# of blocks sync 3) Benchmark the time taken to read same: time dd if=bigfile of=/dev/null bs=8k - Luke ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
On Tue, Oct 24, 2006 at 10:36:04PM -0700, Craig A. James wrote:
Jim C. Nasby wrote:
Well, given that perl is using an entire CPU, it sounds like you should
start looking either at ways to remove some of the overhead from perl,
or to split that perl into multiple processes.
I use Perl for big database copies (usually with some
processing/transformation along the way) and I've never seen 100% CPU usage
except for brief periods, even when copying BLOBS and such. My typical
copy divides operations into blocks, for example doing
N = 0
while (more rows to go) {
begin transaction
select ... where primary_key N order by primary_key limit 1000
while (fetch a row)
insert into ...
N = (highest value found in last block)
commit
}
Doing it like this in Perl should keep Postgres busy, with Perl using only
moderate resources. If you're seeing high Perl CPU usage, I'd look first
at the Perl code.
Wait... so you're using perl to copy data between two tables? And using
a cursor to boot? I can't think of any way that could be more
inefficient...
What's wrong with a plain old INSERT INTO ... SELECT? Or if you really
need to break it into multiple transaction blocks, at least don't
shuffle the data from the database into perl and then back into the
database; do an INSERT INTO ... SELECT with that same where clause.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
On Wed, Oct 25, 2006 at 08:03:38AM -0400, Worky Workerson wrote: I'm just doing CSV style transformations (and calling a lot of functions along the way), but the end result is a straight bulk load of data into a blank database. And we've established that Postgres can do *way* better than what I am seeing, so its not suprising that perl is using 100% of a CPU. If you're loading into an empty database, there's a number of tricks that will help you: Turn off fsync Add constraints and indexes *after* you've loaded the data (best to add as much of them as possible on a per-table basis right after the table is loaded so that it's hopefully still in cache) Crank up maintenance_work_mem, especially for tables that won't fit into cache anyway Bump up checkpoint segments and wal_buffers. Disable PITR Create a table and load it's data in a single transaction (8.2 will avoid writing any WAL data if you do this and PITR is turned off) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
On 10/25/06, Worky Workerson [EMAIL PROTECTED] wrote: I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Nope, nothing in the log. I have set: wal_buffers=128 checkpoint_segments=128 checkpoint_timeout=3000 which I thought was rather generous. Perhaps I should set it even higher for the loads? Have you determined that pg is not swapping? try upping maintenance_work_mem. maintenance_work_mem = 524288 ... should I increase it even more? Doesn't look like pg is swapping ... nah, you already addressed it. either pg is swapping or it isnt, and i'm guessing it isn't. I'm currently running bonnie++ with the defaults ... should I change the execution to better mimic Postgres' behavior? just post what you have... RHEL 4.3 x86_64 HP DL585, 4 Dual Core Opteron 885s 16 GB RAM 2x300GB 10K SCSI320, RAID10 HP MSA1000 SAN direct connected via single 2GB Fibre Channel Arbitrated Loop 10x300GB 10K SCSI320, RAID10 in theory, with 10 10k disks in raid 10, you should be able to keep your 2fc link saturated all the time unless your i/o is extremely random. random i/o is the wild card here, ideally you should see at least 2000 seeks in bonnie...lets see what comes up. hopefully, bonnie will report close to 200 mb/sec. in extreme sequential cases, the 2fc link should be a bottleneck if the raid controller is doing its job. if you are having cpu issues, try breaking your process down to at least 4 processes (you have quad dual core box after all)...thats a no brainer. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Nope, nothing in the log. I have set: wal_buffers=128 checkpoint_segments=128 checkpoint_timeout=3000 which I thought was rather generous. Perhaps I should set it even higher for the loads? Have you determined that pg is not swapping? try upping maintenance_work_mem. maintenance_work_mem = 524288 ... should I increase it even more? Doesn't look like pg is swapping ... What exactly is your architecture? is your database server direct attached to the san? if so, 2gb/4gb fc? what san? have you bonnie++ the san? basically, you can measure iowait to see if pg is waiting on your disks. I'm currently running bonnie++ with the defaults ... should I change the execution to better mimic Postgres' behavior? RHEL 4.3 x86_64 HP DL585, 4 Dual Core Opteron 885s 16 GB RAM 2x300GB 10K SCSI320, RAID10 HP MSA1000 SAN direct connected via single 2GB Fibre Channel Arbitrated Loop 10x300GB 10K SCSI320, RAID10 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
Merlin, On 10/25/06 8:38 AM, Merlin Moncure [EMAIL PROTECTED] wrote: in theory, with 10 10k disks in raid 10, you should be able to keep your 2fc link saturated all the time unless your i/o is extremely random. random i/o is the wild card here, ideally you should see at least 2000 seeks in bonnie...lets see what comes up. The 2000 seeks/sec are irrelevant to Postgres with one user doing COPY. Because the I/O is single threaded, you will get one disk worth of seeks for one user, roughly 150/second on a 10K RPM drive. I suspect the problem here is the sequential I/O rate - let's wait and see what the dd test results look like. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
Jim C. Nasby wrote: Wait... so you're using perl to copy data between two tables? And using a cursor to boot? I can't think of any way that could be more inefficient... What's wrong with a plain old INSERT INTO ... SELECT? Or if you really need to break it into multiple transaction blocks, at least don't shuffle the data from the database into perl and then back into the database; do an INSERT INTO ... SELECT with that same where clause. The data are on two different computers, and I do processing of the data as it passes through the application. Otherwise, the INSERT INTO ... SELECT is my first choice. Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Craig A. James Sent: Wednesday, October 25, 2006 12:52 PM To: Jim C. Nasby Cc: Worky Workerson; Merlin Moncure; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Best COPY Performance Jim C. Nasby wrote: Wait... so you're using perl to copy data between two tables? And using a cursor to boot? I can't think of any way that could be more inefficient... What's wrong with a plain old INSERT INTO ... SELECT? Or if you really need to break it into multiple transaction blocks, at least don't shuffle the data from the database into perl and then back into the database; do an INSERT INTO ... SELECT with that same where clause. The data are on two different computers, and I do processing of the data as it passes through the application. Otherwise, the INSERT INTO ... SELECT is my first choice. Would dblink() help in any way? Greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
Spiegelberg, Greg wrote: The data are on two different computers, and I do processing of the data as it passes through the application. Otherwise, the INSERT INTO ... SELECT is my first choice. Would dblink() help in any way? It might if perl wasn't so damned good at this. ;-) Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
Merlin/Luke: in theory, with 10 10k disks in raid 10, you should be able to keep your 2fc link saturated all the time unless your i/o is extremely random. random i/o is the wild card here, ideally you should see at least 2000 seeks in bonnie...lets see what comes up. I suspect the problem here is the sequential I/O rate - let's wait and see what the dd test results look like. Here are the tests that you suggested that I do, on both the local disks (WAL) and the SAN (tablespace). The random seeks seem to be far below what Merlin said was good, so I am a bit concerned. There is a bit of other activity on the box at the moment which is hard to stop, so that might have had an impact on the processing. Here is the bonnie++ output: Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP Local Disks31G 45119 85 56548 21 27527 8 35069 66 86506 13 499.6 1 SAN31G 53544 98 93385 35 18266 5 24970 47 57911 8 611.8 1 And here are the dd results for 16GB RAM, i.e. 4,000,000 8K blocks: # Local Disks $ time bash -c dd if=/dev/zero of=/home/myhome/bigfile bs=8k count=400 sync 400+0 records in 400+0 records out real10m0.382s user0m1.117s sys 2m45.681s $ time dd if=/home/myhome/bigfile of=/dev/null bs=8k count=400 400+0 records in 400+0 records out real6m22.904s user0m0.717s sys 0m53.766s # Fibre Channel SAN $ time bash -c dd if=/dev/zero of=/data/test/bigfile bs=8k count=400 sync 400+0 records in 400+0 records out real5m58.846s user 0m1.096s sys 2m18.026s $ time dd if=/data/test/bigfile of=/dev/null bs=8k count=400 400+0 records in 400+0 records out real14m9.560s user0m0.739s sys 0m53.806s ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
Hi, Craig, Craig A. James wrote: Would dblink() help in any way? It might if perl wasn't so damned good at this. ;-) You know that you can use Perl inside PostgreS via plperl? HTH, Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Best COPY Performance
Mr. Worky, On 10/25/06 11:26 AM, Worky Workerson [EMAIL PROTECTED] wrote: 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: /home (WAL): 53 MB/s write 84 MB/s read /data (data): 89 MB/s write 38 MB/s read The write and read speeds on /home look like a single disk drive, which is not good if you have more drives in a RAID. OTOH, it should be sufficient for WAL writing and you should think that the COPY speed won't be limited by WAL. 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. But again, this may not have anything to do with the speed of your COPY statements. Can you provide about 10 seconds worth of vmstat 1 while running your COPY so we can get a global view of the I/O and CPU? - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
On Wed, Oct 25, 2006 at 11:25:01AM -0400, Worky Workerson wrote: I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Nope, nothing in the log. I have set: wal_buffers=128 checkpoint_segments=128 checkpoint_timeout=3000 which I thought was rather generous. Perhaps I should set it even higher for the loads? But depending on your shared_buffer and bgwriter settings (as well as how much WAL traffic you're generating, you could still end up with big slugs of work to be done when checkpoints happen. If you set checkpoint_warning to 3001, you'll see exactly when checkpoints are happening, so you can determine if that's an issue. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
Markus, Could you COPY one of your tables out to disk via psql, and then COPY it back into the database, to reproduce this measurement with your real data? $ psql -c COPY my_table TO STDOUT my_data $ ls my_data 2018792 edgescape_pg_load $ time cat my_data | psql -c COPY mytable FROM STDIN real5m43.194s user0m35.412s sys 0m9.567s Also, how much is the disk load, and CPU usage? When I am loading via the perl (which I've established is a bottleneck), the one CPU core is at 99% for the perl and another is at 30% for a postmaster, vs about 90% for the postmaster when going through psql. The disk load is where I start to get a little fuzzy, as I haven't played with iostat to figure what is normal. The local drives contain PG_DATA as well as all the log files, but there is a tablespace on the FibreChannel SAN that contains the destination table. The disk usage pattern that I see is that there is a ton of consistent activity on the local disk, with iostat reporting an average of 30K Blk_wrtn/s, which I assume is the log files. Every several seconds there is a massive burst of activity on the FC partition, to the tune of 250K Blk_wrtn/s. On a table with no indices, triggers and contstraints, we managed to COPY about 7-8 megabytes/second with psql over our 100 MBit network, so here the network was the bottleneck. hmm, this makes me think that either my PG config is really lacking, or that the SAN is badly misconfigured, as I would expect it to outperform a 100Mb network. As it is, with a straight pipe to psql COPY, I'm only working with a little over 5.5 MB/s. Could this be due to the primary key index updates? Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
On Mon, Oct 23, 2006 at 03:37:47PM -0700, Craig A. James wrote: Jim C. Nasby wrote: http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. I use Perl extensively, and have never seen a performance problem. I suspect the perl-to-C 100x improvement was due to some other factor, like a slight change in the schema, indexes, or the fundamental way the client (C vs Perl) handled the data during the transformation, or just plain bad Perl code. Modern scripting languages like Perl and Python make programmers far, far more productive than the bad old days of C/C++. Don't shoot yourself in the foot by reverting to low-level languages like C/C++ until you've exhausted all other possibilities. I only use C/C++ for intricate scientific algorithms. In many cases, Perl is *faster* than C/C++ code that I write, because I can't take the time (for example) to write the high-performance string manipulation that have been fine-tuned and extensively optimized in Perl. Well, the code is all at http://cvs.distributed.net/viewcvs.cgi/stats-proc/hourly/ (see logmod directory and logmod_*.pl). There have been changes made to the C code since we changed over, but you can find the appropriate older versions in there. IIRC, nothing in the database changed when we went from perl to C (it's likely that was the *only* change that happened anywhere around that time). -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
On Tue, Oct 24, 2006 at 09:17:08AM -0400, Worky Workerson wrote: http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. Like Craig mentioned, I have never seen those sorts of improvements going from perl-C, and developer efficiency is primo for me. I've profiled most of the stuff, and have used XS modules and Inline::C on the appropriate, often used functions, but I still think that it comes down to my using CSV and Text::CSV_XS. Even though its XS, CSV is still a pain in the ass. Ultimately, you might be best of using triggers instead of rules for the partitioning since then you could use copy. Or go to raw insert commands that are wrapped in a transaction. Eh, I've put the partition loading logic in the loader, which seems to work out pretty well, especially since I keep things sorted and am the only one inserting into the DB and do so with bulk loads. But I'll keep this in mind for later use. Well, given that perl is using an entire CPU, it sounds like you should start looking either at ways to remove some of the overhead from perl, or to split that perl into multiple processes. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. Like Craig mentioned, I have never seen those sorts of improvements going from perl-C, and developer efficiency is primo for me. I've profiled most of the stuff, and have used XS modules and Inline::C on the appropriate, often used functions, but I still think that it comes down to my using CSV and Text::CSV_XS. Even though its XS, CSV is still a pain in the ass. Ultimately, you might be best of using triggers instead of rules for the partitioning since then you could use copy. Or go to raw insert commands that are wrapped in a transaction. Eh, I've put the partition loading logic in the loader, which seems to work out pretty well, especially since I keep things sorted and am the only one inserting into the DB and do so with bulk loads. But I'll keep this in mind for later use. Thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Best COPY Performance
Jim C. Nasby wrote:
Well, given that perl is using an entire CPU, it sounds like you should
start looking either at ways to remove some of the overhead from perl,
or to split that perl into multiple processes.
I use Perl for big database copies (usually with some processing/transformation
along the way) and I've never seen 100% CPU usage except for brief periods,
even when copying BLOBS and such. My typical copy divides operations into
blocks, for example doing
N = 0
while (more rows to go) {
begin transaction
select ... where primary_key N order by primary_key limit 1000
while (fetch a row)
insert into ...
N = (highest value found in last block)
commit
}
Doing it like this in Perl should keep Postgres busy, with Perl using only
moderate resources. If you're seeing high Perl CPU usage, I'd look first at
the Perl code.
Craig
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
Hi, Worky, Worky Workerson wrote: I am currently getting between 10K and 15K inserts/second. I ran trivial little insert into a table with a single integer row and came close to 250K inserts/second using psql's \copy, so I'm thinking that my code could be optimized a bit more, but wanted to check around to see if that was the case. Could you COPY one of your tables out to disk via psql, and then COPY it back into the database, to reproduce this measurement with your real data? Also, how much is the disk load, and CPU usage? As long as psql is factor 20 better than your perl script, I think that the perl interface is what should be optimized. On a table with no indices, triggers and contstraints, we managed to COPY about 7-8 megabytes/second with psql over our 100 MBit network, so here the network was the bottleneck. You should think about making your perl program writing the COPY statement as text, and piping it into psql. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Best COPY Performance
Markus, On 10/23/06 2:27 AM, Markus Schaber [EMAIL PROTECTED] wrote: On a table with no indices, triggers and contstraints, we managed to COPY about 7-8 megabytes/second with psql over our 100 MBit network, so here the network was the bottleneck. We routinely get 10-12MB/s on I/O hardware that can sustain a sequential write rate of 60+ MB/s with the WAL and data on the same disks. It depends on a few things you might not consider, including the number and type of columns in the table and the client and server encoding. The fastest results are with more columns in a table and when the client and server encoding are the same. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
I am most interested in loading two tables, one with about 21 (small) VARCHARs where each record is about 200 bytes, and another with 7 INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350 bytes. indexes/keys? more memory for sorting during index creation can have a dramatic affect on bulk insert performance. check for pg_tmp folders popping up during copy run. The only index on load is a single IP4 btree primary key, which I figure should function about like an INTEGER. for table light on indexes, 10-15k for copy is pretty poor. you can get pretty close to that with raw inserts on good hardware. I would suggest configuirng your perl script to read from stdin and write to stdout, and pipe it to psql using copy from stdin. then just benchmark your perl script redirecting output to a file. So simple and hadn't thought of that ... thanks. When I pre-create a COPY file, I can load it at about 45K inserts/sec (file was 1.8GB or 14.5 million records in 331 seconds), which looks like its about 5.5 MB/s. I'm loading from a local 15K SCSI320 RAID10 (which also contains the PG log files) to a 10K SCSI320 RAID10 on an FC SAN. Does this look more consistent with decent performance, or should I go looking into some hardware issues i.e. SAN configuration? I've currently got several hats including hardware/systems/security admin, as well as DBA and programmer, and my SAN setup skills could definitely use some more work. Hardware aside, my perl can definitely use some work, and it seems to be mostly the CSV stuff that I am using, mostly for convenience. I'll see if I can't redo some of that to eliminate some CSV processing, or, barring that, multithread the process to utilize more of the CPUs. Part of the reason that I hadn't used psql in the first place is that I'm loading the data into partitioned tables, and the loader keeps several COPY connections open at a time to load the data into the right table. I guess I could just as easily keep several psql pipes open, but it seemed cleaner to go through DBI. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Best COPY Performance
On Mon, Oct 23, 2006 at 11:10:19AM -0400, Worky Workerson wrote: I am most interested in loading two tables, one with about 21 (small) VARCHARs where each record is about 200 bytes, and another with 7 INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350 bytes. indexes/keys? more memory for sorting during index creation can have a dramatic affect on bulk insert performance. check for pg_tmp folders popping up during copy run. The only index on load is a single IP4 btree primary key, which I figure should function about like an INTEGER. for table light on indexes, 10-15k for copy is pretty poor. you can get pretty close to that with raw inserts on good hardware. I would suggest configuirng your perl script to read from stdin and write to stdout, and pipe it to psql using copy from stdin. then just benchmark your perl script redirecting output to a file. So simple and hadn't thought of that ... thanks. When I pre-create a COPY file, I can load it at about 45K inserts/sec (file was 1.8GB or 14.5 million records in 331 seconds), which looks like its about 5.5 MB/s. I'm loading from a local 15K SCSI320 RAID10 (which also contains the PG log files) to a 10K SCSI320 RAID10 on an FC SAN. Does this look more consistent with decent performance, or should I go looking into some hardware issues i.e. SAN configuration? I've currently got several hats including hardware/systems/security admin, as well as DBA and programmer, and my SAN setup skills could definitely use some more work. Hardware aside, my perl can definitely use some work, and it seems to be mostly the CSV stuff that I am using, mostly for convenience. I'll see if I can't redo some of that to eliminate some CSV processing, or, barring that, multithread the process to utilize more of the CPUs. Part of the reason that I hadn't used psql in the first place is that I'm loading the data into partitioned tables, and the loader keeps several COPY connections open at a time to load the data into the right table. I guess I could just as easily keep several psql pipes open, but it seemed cleaner to go through DBI. http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. Ultimately, you might be best of using triggers instead of rules for the partitioning since then you could use copy. Or go to raw insert commands that are wrapped in a transaction. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Best COPY Performance
Ultimately, you might be best of using triggers instead of rules for the partitioning since then you could use copy. Or go to raw insert commands that are wrapped in a transaction. My experience is that triggers are quite a bit faster than rules in any kind of partitioning that involves more than say 7 tables. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Best COPY Performance
Jim C. Nasby wrote: http://stats.distributed.net used to use a perl script to do some transformations before loading data into the database. IIRC, when we switched to using C we saw 100x improvement in speed, so I suspect that if you want performance perl isn't the way to go. I think you can compile perl into C, so maybe that would help some. I use Perl extensively, and have never seen a performance problem. I suspect the perl-to-C 100x improvement was due to some other factor, like a slight change in the schema, indexes, or the fundamental way the client (C vs Perl) handled the data during the transformation, or just plain bad Perl code. Modern scripting languages like Perl and Python make programmers far, far more productive than the bad old days of C/C++. Don't shoot yourself in the foot by reverting to low-level languages like C/C++ until you've exhausted all other possibilities. I only use C/C++ for intricate scientific algorithms. In many cases, Perl is *faster* than C/C++ code that I write, because I can't take the time (for example) to write the high-performance string manipulation that have been fine-tuned and extensively optimized in Perl. Craig ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Best COPY Performance
On 10/21/06, Worky Workerson [EMAIL PROTECTED] wrote: What is the best COPY performance that you have gotten on a normal table? I know that this is question is almost too general, but it might help me out a bit, or at least give me the right things to tweak. Perhaps the question can be rewritten as Where are the major bottlenecks in a COPY? or How can I compute the max theoretical COPY performance for my hardware?. The two subquestions that I have from this are: -Are my ETL scripts (perl) maximizing the database COPY speeds? -Can I tweak my DB further to eek out a bit more performance? I'm using perl to ETL a decent sized data set (10 million records) and then loading it through perl::DBI's copy. I am currently getting between 10K and 15K inserts/second. I've profiled the ETL scripts a bit and have performance-improved a lot of the code, but I'd like to determine whether it makes sense to try and further optimize my Perl or count it as done and look for improvements elsewhere. I ran trivial little insert into a table with a single integer row and came close to 250K inserts/second using psql's \copy, so I'm thinking that my code could be optimized a bit more, but wanted to check around to see if that was the case. I am most interested in loading two tables, one with about 21 (small) VARCHARs where each record is about 200 bytes, and another with 7 INTEGERs, 3 TIMESTAMPs, and 1 BYTEA where each record is about 350 bytes. indexes/keys? more memory for sorting during index creation can have a dramatic affect on bulk insert performance. check for pg_tmp folders popping up during copy run. I have implemented most of the various bits of PG config advice that I have seen, both here and with a some googling, such as: wal_buffers=128 checkpoint_segments=128 checkpoint_timeout=3000 Software: PG 8.1.3 on RHEL 4.3 x86_64 Hardware: Quad Dual-core Opteron, Fibre Channel SAN with 256M BBC for table light on indexes, 10-15k for copy is pretty poor. you can get pretty close to that with raw inserts on good hardware. I would suggest configuirng your perl script to read from stdin and write to stdout, and pipe it to psql using copy from stdin. then just benchmark your perl script redirecting output to a file. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
