Re: [PERFORM] Performance die when COPYing to table with bigint PK
Yes, you are right. Performance become even more awful. Can some techniques from pg_bulkload be implemented in postgres core? Current performance is not suitable for any enterprise-wide production system. 2011/8/5 Віталій Тимчишин : > > In my tests it greatly depends on if index writes are random or sequential. > My test time goes down from few hours to seconds if I add to the end of > index. > As for me, best comparision would be to make two equal int4 columns with > same data as in int8, two indexes, then perform the test. My bet it will be > slower than int8. > > Четвер, 4 серпня 2011 р. користувач Robert Ayrapetyan > написав: >> All you are saying disproves following: >> >> in experiment I replaces bigint index: >> >> CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix; >> >> with 4 (!) other indexes: >> If you look at the rest of my mail - you would notice 50 times difference in performance. What you would say? >>> >>> That accessing a page from RAM is more than 50 times as fast as a >>> random access of that page from disk. >>> >>> -Kevin >>> >> >> >> >> -- >> Ayrapetyan Robert, >> Comodo Anti-Malware Data Processing Analysis and Management System >> (CAMDPAMS) >> http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php >> > > -- > Best regards, > Vitalii Tymchyshyn > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
05.08.11 11:44, Robert Ayrapetyan написав(ла): Yes, you are right. Performance become even more awful. Can some techniques from pg_bulkload be implemented in postgres core? Current performance is not suitable for any enterprise-wide production system. BTW: I was thinking this morning about indexes. How about next feature: Implement new index type, that will have two "zones" - old & new. New zone is of fixed configurable size, say 100 pages (800 K). Any search goes into both zones. So, as soon as index is larger then 800K, the search must be done twice. As soon as new zone hit's it's size limit, part (may be only one?) of it's pages are merged with old zone. The merge is "rolling" - if last merge've stopped at "X" entry, next merge will start at entry right after X. As for me, this should greatly resolve large index insert problem: 1) Insert into new zone must be quick because it's small and hot in cache. 2) During merge writes will be grouped because items with near keys (for B-tree) or hashes (for hash index) will go to small subset of "old" zone pages. In future, merge can be also done by autovacuum in background. Yes, we get dual index search, but new zone will be hot, so this won't make it twice as costly. Best regards, Vitalii Tymchyshyn -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
In my tests it greatly depends on if index writes are random or sequential. My test time goes down from few hours to seconds if I add to the end of index. As for me, best comparision would be to make two equal int4 columns with same data as in int8, two indexes, then perform the test. My bet it will be slower than int8. Четвер, 4 серпня 2011 р. користувач Robert Ayrapetyan < robert.ayrapet...@comodo.com> написав: > All you are saying disproves following: > > in experiment I replaces bigint index: > > CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix; > > with 4 (!) other indexes: > >>> If you look at the rest of my mail - you would notice 50 times >>> difference in performance. >>> What you would say? >> >> That accessing a page from RAM is more than 50 times as fast as a >> random access of that page from disk. >> >> -Kevin >> > > > > -- > Ayrapetyan Robert, > Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) > http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php > -- Best regards, Vitalii Tymchyshyn
Re: [PERFORM] Performance die when COPYing to table with bigint PK
All you are saying disproves following: in experiment I replaces bigint index: CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix; with 4 (!) other indexes: CREATE INDEX ix_t2 ON test.t USING btree (ip) TABLESPACE tblsp_ix; CREATE INDEX ix_t3 ON test.t USING btree (id_small) TABLESPACE tblsp_ix; CREATE INDEX ix_t4 ON test.t USING btree (id_smalll) TABLESPACE tblsp_ix; CREATE INDEX ix_t5 ON test.t USING btree (ts) TABLESPACE tblsp_ix; which are definitely larger then one bigint index. 0.000u 0.005s 0:13.23 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.035s 0:05.08 0.5% 421+1114k 0+0io 0pf+0w COPY 10 0.000u 0.036s 0:19.28 0.1% 526+1393k 0+0io 0pf+0w COPY 10 0.000u 0.005s 0:05.56 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.006u 0.012s 0:05.57 0.1% 984+1820k 0+0io 0pf+0w COPY 10 0.007u 0.029s 0:05.20 0.3% 808+1746k 0+0io 0pf+0w COPY 10 0.005u 0.000s 0:05.35 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.011s 0:05.92 0.1% 316+836k 0+0io 0pf+0w COPY 10 0.000u 0.005s 0:12.08 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.029s 0:05.46 0.3% 808+2074k 0+0io 0pf+0w COPY 10 0.002u 0.002s 0:05.35 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.005s 0:06.52 0.0% 0+0k 0+0io 0pf+0w Insertions became slower 4-5 times, which is ok. Nothing is closer to even half of minute, while one bigint index constantly gives more then minute and even 2 for 100k records. On Thu, Aug 4, 2011 at 8:22 PM, Kevin Grittner wrote: > Robert Ayrapetyan wrote: > >> If you look at the rest of my mail - you would notice 50 times >> difference in performance. >> What you would say? > > That accessing a page from RAM is more than 50 times as fast as a > random access of that page from disk. > > -Kevin > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
If you look at the rest of my mail - you would notice 50 times difference in performance. What you would say? On Thu, Aug 4, 2011 at 7:11 PM, Vitalii Tymchyshyn wrote: > 04.08.11 18:59, Kevin Grittner написав(ла): >> >> Robert Ayrapetyan wrote: >>> >>> Kevin Grittner wrote: >> >>> [regarding tests which do show the problem] >>> tried same with 2 columns (bigint and int) - it didn't produced >>> such effect probably because data volume has critical effect. >> >> Based on what you're showing, this is almost certainly just a matter >> of pushing your volume of active data above the threshold of what >> your cache holds, forcing it to do disk access rather than RAM >> access for a significant portion of the reads. >> >> -Kevin > > Yep. Seems so. Plus famous "you'd better insert data, then create indexes". > On my database it takes twice the time for int8 then for int4 to insert > data. > Also it takes ~twice a time (2 hours) to add 200K of rows to 200M of rows > than to make an index over 200M of rows (1 hour). > > Best regards, Vitalii Tymchyshyn > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Hi. Timings for your test: foo=# create table bi (big bigint not null, medium int not null); CREATE TABLE foo=# insert into bi with x(n) as (select generate_series(1, 100)) foo-# select n + 50, n from x; INSERT 0 100 foo=# \timing on Timing is on. foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 211.205 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 2789.607 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 206.712 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 2959.679 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 594.584 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 3651.206 ms foo=# create unique index bi_medium on bi (medium); CREATE INDEX Time: 781.407 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 42.177 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 5671.883 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 139.418 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 5668.894 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 204.479 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 6530.010 ms foo=# drop index bi_medium; DROP INDEX Time: 212.038 ms foo=# create unique index bi_big on bi (big); CREATE INDEX Time: 650.492 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 39.818 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 8093.276 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 282.165 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 5988.694 ms foo=# truncate table bi; insert into bi with x(n) as (select TRUNCATE TABLE Time: 245.859 ms foo(# generate_series(1, 100)) select n + 50, n from x; INSERT 0 100 Time: 5702.236 ms foo=# \timing off Timing is off. Now please perform mine: CREATE TABLESPACE tblsp_ix LOCATION '/foo'; CREATE SCHEMA test; CREATE TABLE test.t ( id_big bigint, --PRIMARY KEY USING INDEX TABLESPACE tblsp_ix, ts timestamp NOT NULL, ip inet, id_medium integer NOT NULL, id_small smallint NOT NULL, id_smalll smallint NOT NULL ); CREATE INDEX ix_t ON test.t USING btree (ts, ip, id_medium, id_small) TABLESPACE tblsp_ix; gen_data.csh -cut here--- #!/bin/tcsh set f = $1 set lines_cnt = $2 rm ${f} set id_big = -2147483648 set time_t = 10 set ts = `date -r ${time_t}` set ip = "127.0.0.1" set id_medium = -2147483648 set id_small = 0 set echo_style = both while ( $lines_cnt > 0 ) echo "${id_big}\t${ts}\t${ip}\t${id_medium}\t${id_small}\t${id_small}" >> ${f} @ id_big = ${id_big} + 1 @ time_t = ${time_t} + 1 @ id_medium = ${id_medium} + 1 @ lines_cnt = ${lines_cnt} - 1 end exit 0 -cut here--- time ./gen_data.csh app.data 10 9.564u 2.487s 0:12.05 99.9% 420+1113k 0+51io 0pf+0w copy_data.csh -cut here--- #!/bin/tcsh set f = $1 set cnt = $2 while ( $cnt > 0 ) time psql -d foo -c "COPY test.t(id_big, ts, ip, id_medium, id_small, id_smalll) from '$f'" @ cnt = ${cnt} - 1 end exit 0 -cut here--- time copy_data.csh /aaa/app.data 100 ... 0.000u 0.027s 0:01.55 1.2% 474+1254k 0+0io 0pf+0w COPY 10 ... (~1-3 sec for every of 100 iterations with 3-4 spikes to 5 secs max) CREATE INDEX ix_t_big ON test.t USING btree (id_big) TABLESPACE tblsp_ix; time copy_data.csh /aaa/app.data 100 (the show begins from iteration # ~20): COPY 10 0.000u 0.005s 0:20.70 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.005s 0:06.50 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.037s 0:03.44 0.8% 704+514k 0+0io 0pf+0w COPY 10 0.007u 0.029s 0:04.55 0.4% 808+1746k 0+0io 0pf+0w COPY 10 0.005u 0.000s 0:03.60 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.005u 0.000s 0:02.55 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.037s 0:03.03 0.9% 469+197k 0+0io 0pf+0w COPY 10 0.000u 0.036s 0:03.85 0.7% 526+1393k 0+0io 0pf+0w COPY 10 0.005u 0.000s 0:06.66 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.037s 0:02.73 1.0% 526+1393k 0+0io 0pf+0w COPY 10 0.000u 0.005s 0:11.85 0.0% 0+0k 0+0io 0pf+0w COPY 10 0.000u 0.022s 0:02.56 0.7% 492+1238k 0+0io 0pf+0w COPY 10 0.007u 0.022s 0:02.46 0.8% 650+1328k 0+0io 0pf+0w COPY 10 0.006u 0.031s 0:04.71 0.6%
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Robert Ayrapetyan wrote: > If you look at the rest of my mail - you would notice 50 times > difference in performance. > What you would say? That accessing a page from RAM is more than 50 times as fast as a random access of that page from disk. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
04.08.11 18:59, Kevin Grittner написав(ла): Robert Ayrapetyan wrote: Kevin Grittner wrote: [regarding tests which do show the problem] tried same with 2 columns (bigint and int) - it didn't produced such effect probably because data volume has critical effect. Based on what you're showing, this is almost certainly just a matter of pushing your volume of active data above the threshold of what your cache holds, forcing it to do disk access rather than RAM access for a significant portion of the reads. -Kevin Yep. Seems so. Plus famous "you'd better insert data, then create indexes". On my database it takes twice the time for int8 then for int4 to insert data. Also it takes ~twice a time (2 hours) to add 200K of rows to 200M of rows than to make an index over 200M of rows (1 hour). Best regards, Vitalii Tymchyshyn -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Robert Ayrapetyan wrote: > Kevin Grittner wrote: >> What timings do you get for the insert statements if you run the >> following in your environment? >> Here's what I get: >> >> Time: 1629.141 ms >> Time: 1638.060 ms >> Time: 1711.833 ms >> >> Time: 4151.953 ms >> Time: 4602.679 ms >> Time: 5107.259 ms >> >> Time: 4654.060 ms >> Time: 5158.157 ms >> Time: 5101.110 ms > Timings for your test: > [no index] > Time: 2789.607 ms > Time: 2959.679 ms > Time: 3651.206 ms > [int index] > Time: 5671.883 ms > Time: 5668.894 ms > Time: 6530.010 ms > [bigint index] > Time: 8093.276 ms > Time: 5988.694 ms > Time: 5702.236 ms > [regarding tests which do show the problem] > tried same with 2 columns (bigint and int) - it didn't produced > such effect probably because data volume has critical effect. Based on what you're showing, this is almost certainly just a matter of pushing your volume of active data above the threshold of what your cache holds, forcing it to do disk access rather than RAM access for a significant portion of the reads. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Robert Ayrapetyan wrote: > So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS > + bigint column index > (some of these may be superfluous, but I have no resources to > check on different platforms with different filesystems). Linux 64 bit XFS bigint column index only shows a slightly longer run time for bigint versus int here. What timings do you get for the insert statements if you run the following in your environment? create table bi (big bigint not null, medium int not null); insert into bi with x(n) as (select generate_series(1, 100) select n + 50, n from x; \timing on truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; create unique index bi_medium on bi (medium); truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; drop index bi_medium; create unique index bi_big on bi (big); truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; truncate table bi; insert into bi with x(n) as (select generate_series(1, 100)) select n + 50, n from x; \timing off drop table bi; Here's what I get: Time: 1629.141 ms Time: 1638.060 ms Time: 1711.833 ms Time: 4151.953 ms Time: 4602.679 ms Time: 5107.259 ms Time: 4654.060 ms Time: 5158.157 ms Time: 5101.110 ms -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Seems this assumption is not right. Just created simple index on bigint column - situation with huge performance degradation repeated. Dropping this index solved COPY issues on the fly. So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS + bigint column index (some of these may be superfluous, but I have no resources to check on different platforms with different filesystems). On Mon, Aug 1, 2011 at 12:15 PM, Robert Ayrapetyan wrote: > Quite possible. > But anyway - I don't think performance degradation must be so huge in > case of using UNIQUE indexes. > > On Mon, Aug 1, 2011 at 12:06 PM, Vitalii Tymchyshyn wrote: >> 31.07.11 16:51, Robert Ayrapetyan написав(ла): >>> >>> Hello. >>> >>> I've found strange behavior of my pg installation (tested both 8.4 and >>> 9.0 - they behave same) on FreeBSD platform. >>> In short - when some table have PK on bigint field - COPY to that >>> table from file becomes slower and slower as table grows. When table >>> reaches ~5GB - COPY of 100k records may take up to 20 mins. I've >>> experimented with all params in configs, moved indexes to separate hdd >>> etc - nothing made any improvement. However, once I'm dropping 64 bit >>> PK - COPY of 100k records passes in seconds. Interesting thing - same >>> table has other indexes, including composite ones, but none of them >>> include bigint fields, that's why I reached decision that bug >>> connected with indexes on bigint fields only. >> >> I did see this behavior, but as for me it occurs for UNIQUE indexes only >> (including PK), not dependent on field type. >> You can check this by dropping PK and creating it as a regular non-unique >> index. >> >> Best regards, Vitalii Tymchyshyn >> > > > > -- > Ayrapetyan Robert, > Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) > http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
Quite possible. But anyway - I don't think performance degradation must be so huge in case of using UNIQUE indexes. On Mon, Aug 1, 2011 at 12:06 PM, Vitalii Tymchyshyn wrote: > 31.07.11 16:51, Robert Ayrapetyan написав(ла): >> >> Hello. >> >> I've found strange behavior of my pg installation (tested both 8.4 and >> 9.0 - they behave same) on FreeBSD platform. >> In short - when some table have PK on bigint field - COPY to that >> table from file becomes slower and slower as table grows. When table >> reaches ~5GB - COPY of 100k records may take up to 20 mins. I've >> experimented with all params in configs, moved indexes to separate hdd >> etc - nothing made any improvement. However, once I'm dropping 64 bit >> PK - COPY of 100k records passes in seconds. Interesting thing - same >> table has other indexes, including composite ones, but none of them >> include bigint fields, that's why I reached decision that bug >> connected with indexes on bigint fields only. > > I did see this behavior, but as for me it occurs for UNIQUE indexes only > (including PK), not dependent on field type. > You can check this by dropping PK and creating it as a regular non-unique > index. > > Best regards, Vitalii Tymchyshyn > -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
02.08.11 11:26, Robert Ayrapetyan написав(ла): Seems this assumption is not right. Just created simple index on bigint column - situation with huge performance degradation repeated. Dropping this index solved COPY issues on the fly. So I'm still convinced - this bug relates to FreeBSD 64-bit + UFS + bigint column index (some of these may be superfluous, but I have no resources to check on different platforms with different filesystems). Inteesting. We also have FreeBSDx64 on UFS and are using bigint (bigserial) keys. It seems I will need to perform more tests here because I do see similar problems. I for sure can do a copy of data with int4 keys and test the performance. BTW: The thing we are going to try on next upgrade is to change UFS block size from 16K to 8K. What problem I saw is that with default setting, UFS needs to read additional 8K when postgresql writes it's page (and for index random writes can be vital). Unfortunately, such a changes requires partition reformat and I can't afford it for now. Best regards, Vitalii Tymchyshyn -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
31.07.11 16:51, Robert Ayrapetyan написав(ла): Hello. I've found strange behavior of my pg installation (tested both 8.4 and 9.0 - they behave same) on FreeBSD platform. In short - when some table have PK on bigint field - COPY to that table from file becomes slower and slower as table grows. When table reaches ~5GB - COPY of 100k records may take up to 20 mins. I've experimented with all params in configs, moved indexes to separate hdd etc - nothing made any improvement. However, once I'm dropping 64 bit PK - COPY of 100k records passes in seconds. Interesting thing - same table has other indexes, including composite ones, but none of them include bigint fields, that's why I reached decision that bug connected with indexes on bigint fields only. I did see this behavior, but as for me it occurs for UNIQUE indexes only (including PK), not dependent on field type. You can check this by dropping PK and creating it as a regular non-unique index. Best regards, Vitalii Tymchyshyn -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance die when COPYing to table with bigint PK
On Sun, Jul 31, 2011 at 2:51 PM, Robert Ayrapetyan wrote: > I've found strange behavior of my pg installation (tested both 8.4 and > 9.0 - they behave same) on FreeBSD platform. > In short - when some table have PK on bigint field - COPY to that > table from file becomes slower and slower as table grows. When table > reaches ~5GB - COPY of 100k records may take up to 20 mins. I've > experimented with all params in configs, moved indexes to separate hdd > etc - nothing made any improvement. However, once I'm dropping 64 bit > PK - COPY of 100k records passes in seconds. Interesting thing - same > table has other indexes, including composite ones, but none of them > include bigint fields, that's why I reached decision that bug > connected with indexes on bigint fields only. > > In terms of IO picture is following: after copy started gstat shows > 100% load on index partition (as I mentioned above - I've tried > separate hdd to keep index tablespace), large queue (over 2k > elements), and constant slow write on speed of ~2MB\s. Hdd becomes > completely unresponsive, even ls on empty folder hangs for minute or > so. > > To avoid thoughts like "your hdd is slow, you haven't tuned > postgresql.conf etc" - all slowness dissapears with drop of bigint PK, > same time other indexes on same table remain alive. And yes - I've > tried drop PK \ recreate PK, vacuum full analyze and all other things > - nothing helped, only drop helps. > > Is this known and expected behavior? This is a duplicate post with one on BUGS, being discussed there. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance die when COPYing to table with bigint PK
Hello. I've found strange behavior of my pg installation (tested both 8.4 and 9.0 - they behave same) on FreeBSD platform. In short - when some table have PK on bigint field - COPY to that table from file becomes slower and slower as table grows. When table reaches ~5GB - COPY of 100k records may take up to 20 mins. I've experimented with all params in configs, moved indexes to separate hdd etc - nothing made any improvement. However, once I'm dropping 64 bit PK - COPY of 100k records passes in seconds. Interesting thing - same table has other indexes, including composite ones, but none of them include bigint fields, that's why I reached decision that bug connected with indexes on bigint fields only. In terms of IO picture is following: after copy started gstat shows 100% load on index partition (as I mentioned above - I've tried separate hdd to keep index tablespace), large queue (over 2k elements), and constant slow write on speed of ~2MB\s. Hdd becomes completely unresponsive, even ls on empty folder hangs for minute or so. To avoid thoughts like "your hdd is slow, you haven't tuned postgresql.conf etc" - all slowness dissapears with drop of bigint PK, same time other indexes on same table remain alive. And yes - I've tried drop PK \ recreate PK, vacuum full analyze and all other things - nothing helped, only drop helps. Is this known and expected behavior? -- Ayrapetyan Robert, Comodo Anti-Malware Data Processing Analysis and Management System (CAMDPAMS) http://repo-qa.camdpams.odessa.office.comodo.net/mediawiki/index.php -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance