Re: [PERFORM] Proposal of tunable fix for scalability of 8.4
On Wed, 2009-03-11 at 22:20 -0400, Jignesh K. Shah wrote: A tunable does not impact existing behavior Why not put the tunable parameter into the patch and then show the test results with it in? If there is no overhead, we should then be able to see that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Proposal of tunable fix for scalability of 8.4
Simon Riggs wrote: On Wed, 2009-03-11 at 22:20 -0400, Jignesh K. Shah wrote: A tunable does not impact existing behavior Why not put the tunable parameter into the patch and then show the test results with it in? If there is no overhead, we should then be able to see that. Can do? Though will need quick primer on adding tunables. Is it on wiki.postgresql.org anywhere? -Jignesh -- Jignesh Shah http://blogs.sun.com/jkshah The New Sun Microsystems,Inc http://sun.com/postgresql -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Confused ! when insert with Preimary key, Freebsd 7.1 is slower thrice times then Debian5
Test hardware: MB:GA-EP43-DS3L, CPU :intel 7400 RAM:2G HD:320G sata debian5, freebsd7.1 are installed by default choice by cd-rom debian use XFS, Freebsd use UFS+softdateup all software installed by app_get or pkg_add, change NOTHING configure. at begening of every testing, use initdb create new data, don't edit default postgressql.conf this is table struct: \d user_account; Table public.user_account Column | Type | Modifiers ---+---+--- passport | character varying(32) | not null default ''::character(1) spassword | character(40) | not null default ''::character(1) guid | integer | not null default 0 ptype | integer | default 0 Indexes: user_account_pk_guid PRIMARY KEY, btree (guid) user_account_key_passport UNIQUE, btree (passport) use sql file to insert data, one insert sql include 1000 row. ua1m.sql include 1000 insert sql. so, there are all 1,000,000 row . usage : time psql node ua1m.sql === ONE test: create table an primary key and unique, insert date. debian : 3 min freebsd: 10 min debian win. faster thrice times then freebsd. === TWO test: step 1: create table without key and unique, after insert date, step 2: ALTER TABLEADD pk and unique insert alter table debian : 45s 26s freebsd: 18s 10s freebsd win. whe ONE test: I use iostat to observer IO. freebsd : at beginning, io is 20-30M , after 30s, io go down slowly, about 5M-7M debian always keep one level. freedb# iostat 2 tty ad1 cpu tin tout KB/t tps MB/s us ni sy in id 0 22 11.88 8 0.09 0 0 0 0 100 0 22 0.00 0 0.00 0 0 0 0 100 0 22 0.00 0 0.00 0 0 0 0 100 0 22 0.00 0 0.00 0 0 0 0 100 0 75 42.62 52 2.16 4 0 1 0 95 0 388 47.42 491 22.75 30 0 4 0 66 0 388 46.60 502 22.85 32 0 3 0 65 0 321 50.01 588 28.69 26 0 4 0 69 0 298 45.77 592 26.44 26 0 2 0 71 0 328 43.86 536 22.96 27 0 3 1 69 0 269 45.72 659 29.42 25 0 2 1 72 0 239 35.69 661 23.05 21 0 2 1 76 0 216 26.91 535 14.06 19 0 2 0 80 0 343 42.71 520 21.69 34 0 3 0 62 0 29 33.25 343 11.13 1 0 2 0 97 0 96 27.97 412 11.26 8 0 2 0 90 0 319 27.48 429 11.52 25 0 3 0 71 0 126 38.58 357 13.45 10 0 2 0 87 0 81 34.50 302 10.19 6 0 2 0 91 0 81 36.62 288 10.32 5 0 2 0 92 0 66 30.89 285 8.60 4 0 2 0 94 0 44 43.47 382 16.23 4 0 2 0 94 0 21 32.31 297 9.37 0 0 1 0 99 0 36 27.29 385 10.26 2 0 1 0 97 0 21 22.12 350 7.56 0 0 1 0 99 0 66 26.02 477 12.11 5 0 2 0 92 0 51 36.14 292 10.32 3 0 1 0 96 0 29 31.48 278 8.55 1 0 1 0 98 0 44 35.26 284 9.80 3 0 2 0 95 0 51 34.27 295 9.87 3 0 1 0 96 0 44 30.35 238 7.06 3 0 2 0 96 0 44 28.12 290 7.98 2 0 2 0 96 0 44 30.64 271 8.11 3 0 1 0 96 0 51 32.52 288 9.16 3 0 2 0 96 0 44 33.29 265 8.61 2 0 1 0 96 debian1:~# iostat -t 2 sda Linux 2.6.26-1-686 (debian1)03/16/2009 _i686_ Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 2.66 2.17 246.58 329693 37377968 sda 0.50 0.00 8.00 0 16 sda 0.00 0.00 0.00 0 0 sda 10.00 0.00 3117.00 0 6234 sda 49.50 0.00 25833.50 0 51667 sda 33.50 0.00 10091.00 0 20182 sda 51.50 0.00 31448.50 0 62897 sda 29.50 0.00 9900.00 0 19800 sda 51.00 0.00 32721.00 0 65442 sda 52.50 0.00 24387.00 0 48774 sda 69.50 0.00 25178.00 0 50356 sda 70.00 0.00 40984.50 0 81969 sda 28.50 0.00 9674.00 0 19348 sda 63.50 0.00 35200.50 0 70401 sda 64.00 0.00 25347.50 0 50695 sda 72.50 0.00 44291.50 0 88583 sda 33.00 0.00 8569.50 0 17139 sda 32.00 0.00 8192.00 0 16384 sda 52.00 0.00 24642.00 0 49284 sda 366.00 0.00 55748.50 0
[PERFORM] Extremely slow intarray index creation and inserts.
Short summary: * extremely slow intarray indexes with gist__int_ops * gist__intbig_ops seems much faster even with short arrays * gin seems much much faster for inserts and creation(and queries) I was debugging a system with a table that slowed to where individual inserts were taking well over a second on a practically idle system. Dropping an a gist index on an intarray made the problem go away. Timing the creation of gist indexes on this 8.3.6 system makes me think there's something broken with intarray's gist indexes. This table summarizes some of the times, shown more completely in a script below. = create gist index on 1 = 5 seconds create gist index on 2 = 32 seconds create gist index on 3 = 39 seconds create gist index on 4 = 102 seconds create gist index on 7 = I waited 10 minutes before giving up. create gin index on 4 = 0.7 seconds create gist index on 4 = 5 seconds using gist__intbig_ops create gin index on 7 = 1.0 seconds create gist index on 7 = 9 seconds using gist__intbig_ops == This surprised me for a number of reasons. The longest array in the table is 9 elements long, and most are 5 or 6 so I'd have thought the default ops would have been better than the big_ops. Secondly, I thought gin inserts were expected to be slower than gist, but I'm finding them much faster. Nothing seems particular strange about the data. A dump of an excerpt of the table can be found at http://0ape.com/tmp/int_array.dmp (Yes, the production table had other columns; but this column alone is enough to demonstrate the problem.) Any thoughts what I'm doing wrong? Ron psql output showing the timing follows. === vm=# create table tmp_intarray_test as select tag_id_array as my_int_array from taggings; SELECT vm=# create table tmp_intarray_test_1 as select * from tmp_intarray_test limit 1; SELECT vm=# create table tmp_intarray_test_2 as select * from tmp_intarray_test limit 2; SELECT vm=# create table tmp_intarray_test_3 as select * from tmp_intarray_test limit 3; SELECT vm=# create table tmp_intarray_test_4 as select * from tmp_intarray_test limit 4; SELECT vm=# \timing Timing is on. vm=# vm=# create index gist_1 using GIST(my_int_array) on tmp_intarray_test_1 using GIST (my_int_array); CREATE INDEX Time: 5760.050 ms vm=# create index gist_2 using GIST(my_int_array) on tmp_intarray_test_2 using GIST (my_int_array); CREATE INDEX Time: 32500.911 ms vm=# create index gist_3 using GIST(my_int_array) on tmp_intarray_test_3 using GIST (my_int_array); CREATE INDEX Time: 39284.031 ms vm=# create index gist_4 using GIST(my_int_array) on tmp_intarray_test_4 using GIST (my_int_array); CREATE INDEX Time: 102572.780 ms vm=# vm=# vm=# vm=# vm=# vm=# vm=# create index gin_4 on tmp_intarray_test_4 using GIN (my_int_array gin__int_ops); CREATE INDEX Time: 696.668 ms vm=# create index gist_big_4000 on tmp_intarray_test_4 using GIST (my_int_array gist__intbig_ops); CREATE INDEX Time: 5227.353 ms vm=# vm=# vm=# vm=# \d tmp_intarray_test Table public.tmp_intarray_test Column| Type| Modifiers --+---+--- my_int_array | integer[] | vm=# select max(array_dims(my_int_array)) from tmp_intarray_test_3; max --- [1:9] (1 row) Time: 119.607 ms vm=# vm=# vm=# select version(); version --- PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.3-1) 4.3.3 (1 row) Time: 12.169 ms vm=# create index gistbig7 on tmp_intarray_test using GIST (my_int_array gist__intbig_ops); CREATE INDEX Time: 9156.886 ms vm=# create index gin7 on tmp_intarray_test using GIN (my_int_array gin__int_ops); CREATE INDEX Time: 1060.752 ms vm=# create index gist7000 on tmp_intarray_test using GIST (my_int_array gist__int_ops); [ it just sits here for 10 minutes or more ] -- 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] Extremely slow intarray index creation and inserts.
Ron Mayer wrote: This table summarizes some of the times, shown more completely in a script below. = create gist index on 1 = 5 seconds create gist index on 2 = 32 seconds create gist index on 3 = 39 seconds create gist index on 4 = 102 seconds create gist index on 7 = I waited 10 minutes before giving up Finished after 34 minutes. vm=# create index gist7 on tmp_intarray_test using GIST (my_int_array gist__int_ops); CREATE INDEX Time: 2069836.856 ms Is that expected, or does it sound like a bug to take over half an hour to index 7 rows of mostly 5 and 6-element integer arrays? create gin index on 4 = 0.7 seconds create gist index on 4 = 5 seconds using gist__intbig_ops create gin index on 7 = 1.0 seconds create gist index on 7 = 9 seconds using gist__intbig_ops == This surprised me for a number of reasons. The longest array in the table is 9 elements long, and most are 5 or 6 so I'd have thought the default ops would have been better than the big_ops. Secondly, I thought gin inserts were expected to be slower than gist, but I'm finding them much faster. Nothing seems particular strange about the data. A dump of an excerpt of the table can be found at http://0ape.com/tmp/int_array.dmp (Yes, the production table had other columns; but this column alone is enough to demonstrate the problem.) Any thoughts what I'm doing wrong? Ron psql output showing the timing follows. === vm=# create table tmp_intarray_test as select tag_id_array as my_int_array from taggings; SELECT vm=# create table tmp_intarray_test_1 as select * from tmp_intarray_test limit 1; SELECT vm=# create table tmp_intarray_test_2 as select * from tmp_intarray_test limit 2; SELECT vm=# create table tmp_intarray_test_3 as select * from tmp_intarray_test limit 3; SELECT vm=# create table tmp_intarray_test_4 as select * from tmp_intarray_test limit 4; SELECT vm=# \timing Timing is on. vm=# vm=# create index gist_1 using GIST(my_int_array) on tmp_intarray_test_1 using GIST (my_int_array); CREATE INDEX Time: 5760.050 ms vm=# create index gist_2 using GIST(my_int_array) on tmp_intarray_test_2 using GIST (my_int_array); CREATE INDEX Time: 32500.911 ms vm=# create index gist_3 using GIST(my_int_array) on tmp_intarray_test_3 using GIST (my_int_array); CREATE INDEX Time: 39284.031 ms vm=# create index gist_4 using GIST(my_int_array) on tmp_intarray_test_4 using GIST (my_int_array); CREATE INDEX Time: 102572.780 ms vm=# vm=# vm=# vm=# vm=# vm=# vm=# create index gin_4 on tmp_intarray_test_4 using GIN (my_int_array gin__int_ops); CREATE INDEX Time: 696.668 ms vm=# create index gist_big_4000 on tmp_intarray_test_4 using GIST (my_int_array gist__intbig_ops); CREATE INDEX Time: 5227.353 ms vm=# vm=# vm=# vm=# \d tmp_intarray_test Table public.tmp_intarray_test Column| Type| Modifiers --+---+--- my_int_array | integer[] | vm=# select max(array_dims(my_int_array)) from tmp_intarray_test_3; max --- [1:9] (1 row) Time: 119.607 ms vm=# vm=# vm=# select version(); version --- PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.3-1) 4.3.3 (1 row) Time: 12.169 ms vm=# create index gistbig7 on tmp_intarray_test using GIST (my_int_array gist__intbig_ops); CREATE INDEX Time: 9156.886 ms vm=# create index gin7 on tmp_intarray_test using GIN (my_int_array gin__int_ops); CREATE INDEX Time: 1060.752 ms vm=# create index gist7000 on tmp_intarray_test using GIST (my_int_array gist__int_ops); [ it just sits here for 10 minutes or more ] -- 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] Proposal of tunable fix for scalability of 8.4
On 03/16/09 13:39, Simon Riggs wrote:
On Wed, 2009-03-11 at 22:20 -0400, Jignesh K. Shah wrote:
A tunable does not impact existing behavior
Why not put the tunable parameter into the patch and then show the test
results with it in? If there is no overhead, we should then be able to
see that.
I did a patch where I define lock_wakeup_algorithm with default value of
0, and range is 0 to 32
It basically handles three types of algorithms and 32 different
permutations, such that:
When lock_wakeup_algorithm is set to
0 = default logic of wakeup (only 1 exclusive or all
sequential shared)
1 = wake up all sequential exclusives or all sequential
shared
32= n =2 = wake up first n waiters irrespective of exclusive or
sequential
I did a quick test with patch. Unfortunately it improves my number even
with default setting 0 (not sure whether I should be pleased or sad -
Definitely no overhead infact seems to help performance a bit. NOTE:
Logic is same, implementation is slightly different for default set)
my Prepatch numbers typically peaked around 136,000 tpm
With the patch and settings:
lock_wakeup_algorithm=0
PEAK: 962: 512: Medium Throughput: 161121.000 Avg Medium Resp: 0.051
When lock_wakeup_algorithm=1
Then my PEAK increases to
PEAK 1560: 832: Medium Throughput: 176577.000 Avg Medium Resp: 0.086
(Couldn't recreate the 184K+ result.. need to check that)
I still havent tested for the rest 2-32 values but you get the point,
the patch is quite flexible with various types of permutations and no
overhead.
Do give it a try on your own setup and play with values and compare it
with your original builds.
Regards,
Jignesh
*** lwlock.cTue Mar 17 12:27:49 2009
--- lwlock.c.orig Wed Mar 11 12:48:27 2009
***
*** 87,93
static intlock_addin_request = 0;
static bool lock_addin_request_allowed = true;
- int LWLockWakeupAlgorithm;
#ifdef LWLOCK_STATS
static intcounts_for_pid = 0;
--- 87,92
***
*** 564,570
PGPROC *head;
PGPROC *proc;
int i;
- int runq;
PRINT_LWDEBUG(LWLockRelease, lockid, lock);
--- 563,568
***
*** 612,631
* as many waiters as want shared access.
*/
proc = head;
!if (LWLockWakeupAlgorithm || !proc-lwExclusive)
!{
! if (LWLockWakeupAlgorithm = 1)
! {
while (proc-lwWaitLink != NULL
! (proc-lwExclusive ==
proc-lwWaitLink-lwExclusive))
proc = proc-lwWaitLink;
- }
-else
- {
- runq= LWLockWakeupAlgorithm;
- while (proc-lwWaitLink != NULL --runq)
- proc = proc-lwWaitLink;
- }
}
/* proc is now the last PGPROC to be released */
lock-head = proc-lwWaitLink;
--- 610,620
* as many waiters as want shared access.
*/
proc = head;
! if (!proc-lwExclusive)
! {
while (proc-lwWaitLink != NULL
! !proc-lwWaitLink-lwExclusive)
proc = proc-lwWaitLink;
}
/* proc is now the last PGPROC to be released */
lock-head = proc-lwWaitLink;
*** lwlock.h.orig Tue Mar 17 14:27:10 2009
--- lwlock.hTue Mar 17 08:24:40 2009
***
*** 103,106
--- 103,107
extern void RequestAddinLWLocks(int n);
+ extern int LWLockWakeupAlgorithm;
#endif /* LWLOCK_H */
*** guc.c.orig Tue Mar 17 07:30:26 2009
--- guc.c Tue Mar 17 07:47:10 2009
***
*** 57,62
--- 57,63
#include postmaster/walwriter.h
#include regex/regex.h
#include storage/bufmgr.h
+ #include storage/lwlock.h
#include storage/fd.h
#include tcop/tcopprot.h
#include tsearch/ts_cache.h
***
*** 167,172
--- 168,174
static bool assign_maxconnections(int newval, bool doit, GucSource source);
static bool assign_autovacuum_max_workers(int newval, bool doit, GucSource
source);
static bool assign_effective_io_concurrency(int newval, bool doit, GucSource
source);
+ static bool assign_lock_wakeup_algorithm(int newval, bool doit, GucSource
source);
static const char *assign_pgstat_temp_directory(const char *newval, bool
doit, GucSource source);
static char
[PERFORM] parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
We have a few slow queries that use sequential scans on tables that have plenty of indexes (for other queries), on a box with a lot of RAM and 13 active cores (don't ask), so I was curious to find out how to put this environment to better use. The result is (maybe) interesting, esp. since PostgreSQL is getting better at executing many queries in parallel lately and we will have more than 16 cores in typical servers very soon. The simplified scenario was a query like select * from t where foo ~ 'bla'; on a table with approx. 9m rows, taking around 12 seconds in the best case. The table had a bigserial primary key eid with btree index, which seemed to be the most suitable starting point. The current value range of eid was partitioned into intervals of equal size and depending on the number of rows in these intervals, one or more of them were assigned to worker processes (this is done once per day, not before each query!): worker 1: select * from t where foo ~ 'bla' and (eid = 30 and eid 40) worker 2: select * from t where foo ~ 'bla' and (eid = 50 and eid 60 or eid = 110 and eid 120 ...) ... Instead of a sequential scan, a bunch of worker processes (implemented with Gearman/Perl) would then execute queries (one each) with a plan like: Bitmap Heap Scan on t ... Recheck Cond: eid = 30 and eid 40 .. Filter: foo ~ 'bla' Bitmap Index Scan on t_pkey ... This led to a speedup factor of 2 when the box was idle, i.e. the time to split the query, distribute the jobs to worker processes, execute the queries in parallel, collect results and send them back to the client was now ~6 seconds. Observations: - currently, the speedup is almost the same for anything between ~10 and 80+ workers (~12s down to ~7s on average, best run ~ 6s) - the effective processing speed of the workers varied greatly (fastest ~3x to ~10x the rows/second of the slowest - real time divided by rows to work on) - the fastest workers went as fast as the sequential scans (in rows per second) - sometimes, but not always (most likely they were actually running alone then for some reason) - in each new run, the workers finished in a completely different order (even though they had the same parts of the table to work on and thus identical queries) so perhaps the partitioning of the work load is quite good already and it's more of a scheduling issue (Gearman? Shared buffer contention?) - the Linux I/O scheduler had a visible effect, noop was better than deadline (others not tried yet) ~10%, but this is typical for random writes and RAID controllers that manage their writeback cache as they like (it's a wasted effort to reorder writes before hitting the RAID controller) - CLUSTER might help a lot (the workers should hit fewer pages and need fewer shared resources?) but I haven't tested it - our query performance is not limited by disk I/O (as is usually the case I guess), since we have most of the tables/indexes in RAM. Whether it scales as well (or better?) with a proper disk subsystem and less RAM, is unknown. I hope there is some room for improvement so these queries can execute faster in parallel for better scaling, these first results are quite encouraging. I'd love to put 32+ cores to use for single queries. Perhaps something like this could be built into PostgreSQL at some point? There's no complicated multithreading/locking involved and Postgres has enough statistics available to distribute work even better. It should be easy to implement this for any of the various connection pooling solutions also. Has anyone done similar work in the light of upcoming many-core CPUs/systems? Any better results than 2x improvement? Apologies if this is a well-known and widely used technique already. ;-) Marinos. PS. yes, for the example query we could use tsearch2 etc., but it has drawbacks in our specific case (indexing overhead, no real regexps possible) and it's only an example anyway ... -- 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] parallelizing slow queries for multiple cores (PostgreSQL + Gearman)
On Tue, 17 Mar 2009, Marinos Yannikos wrote: It should be easy to implement this for any of the various connection pooling solutions also. pgpool-II has something very similar in its Parallel Mode, and the example given at http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html looks just like your example. I wonder if anyone has tried using that feature but just pointing pgpool-II at the same database instance multiple times? -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Proposal of tunable fix for scalability of 8.4
On Tue, 2009-03-17 at 17:41 -0400, Jignesh K. Shah wrote: I did a quick test with patch. Unfortunately it improves my number even with default setting 0 (not sure whether I should be pleased or sad - Definitely no overhead infact seems to help performance a bit. NOTE: Logic is same, implementation is slightly different for default set) OK, I bite. 25% gain from doing nothing??? You're stretching my... err, credulity. I like the train of thought for setting 1 and it is worth investigating, but something feels wrong somewhere. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Proposal of tunable fix for scalability of 8.4
Simon Riggs wrote: On Tue, 2009-03-17 at 17:41 -0400, Jignesh K. Shah wrote: I did a quick test with patch. Unfortunately it improves my number even with default setting 0 (not sure whether I should be pleased or sad - Definitely no overhead infact seems to help performance a bit. NOTE: Logic is same, implementation is slightly different for default set) OK, I bite. 25% gain from doing nothing??? You're stretching my... err, credulity. I like the train of thought for setting 1 and it is worth investigating, but something feels wrong somewhere. Actually I think I am hurting my credibility here since I cannot explain the improvement with the patch but still using default logic (thought different way I compare sequential using fields from the previous proc structure instead of comparing with constant boolean) But the change was necessary to allow it to handle multiple algorithms and yet be sleek and not bloated. In next couple of weeks I plan to test the patch on a different x64 based system to do a sanity testing on lower number of cores and also try out other workloads ... Regards, Jignesh - 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] Proposal of tunable fix for scalability of 8.4
On Tue, 2009-03-17 at 19:54 -0400, Jignesh K. Shah wrote: Simon Riggs wrote: On Tue, 2009-03-17 at 17:41 -0400, Jignesh K. Shah wrote: I did a quick test with patch. Unfortunately it improves my number even with default setting 0 (not sure whether I should be pleased or sad - Definitely no overhead infact seems to help performance a bit. NOTE: Logic is same, implementation is slightly different for default set) OK, I bite. 25% gain from doing nothing??? You're stretching my... err, credulity. I like the train of thought for setting 1 and it is worth investigating, but something feels wrong somewhere. Actually I think I am hurting my credibility here since I cannot explain the improvement with the patch but still using default logic (thought different way I compare sequential using fields from the previous proc structure instead of comparing with constant boolean) But the change was necessary to allow it to handle multiple algorithms and yet be sleek and not bloated. In next couple of weeks I plan to test the patch on a different x64 based system to do a sanity testing on lower number of cores and also try out other workloads ... Good plan. I'm behind your ideas and will be happy to wait. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support - 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] Extremely slow intarray index creation and inserts.
Ron Mayer rm...@cheapcomplexdevices.com writes: vm=# create index gist7 on tmp_intarray_test using GIST (my_int_array gist__int_ops); CREATE INDEX Time: 2069836.856 ms Is that expected, or does it sound like a bug to take over half an hour to index 7 rows of mostly 5 and 6-element integer arrays? I poked at this example with oprofile. It's entirely CPU-bound AFAICT, and the CPU utilization is approximately 55% g_int_compress 35% memmove/memcpy (difficult to distinguish these) 1% pg_qsort 1% anything else Probably need to look at reducing the number of calls to g_int_compress ... it must be getting called a whole lot more than once per new index entry, and I wonder why that should need to be. regards, tom lane - Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
