date:20151023

Re: [PERFORM] Recursive query performance issue

2015-10-23 Thread Pavel Stehule

Hi

this extremely high load looks like different issue - maybe spinlock issue
or virtual memory issue.

Probably you need some low level debug tools like perf or dtrace :(

http://www.postgresql.org/message-id/20131206095629.gi7...@awork2.anarazel.de

Has you last PostgreSQL upgrade?

result of "perf top" when this issue is active is really requested.

Regards

Pavel

2015-10-23 19:45 GMT+02:00 Jamie Koceniak :

> Hi,
>
> We just had the performance problem again today.
> Here is some of the top output. Unfortunately, we don't have perf top
> installed.
>
> top - 16:22:16 up 29 days, 13:00,  2 users,  load average: 164.63, 158.62,
> 148.52
> Tasks: 1369 total, 181 running, 1188 sleeping,   0 stopped,   0 zombie
> %Cpu(s):  6.2 us,  0.7 sy,  0.0 ni, 93.1 id,  0.0 wa,  0.0 hi,  0.0 si,
> 0.0 st
> MiB Mem:   2068265 total,   433141 used,  1635124 free,  586 buffers
> MiB Swap: 7812 total,0 used, 7812 free,   412641 cached
>
>PID USER  PR  NI  VIRT  RES  SHR S  %CPU %MEMTIME+  COMMAND
>  81745 postgres  20   0 65.7g  51m  34m R   101  0.0   0:09.20 postgres:
> user1 db 0.0.0.2(52307) SELECT
>  81782 postgres  20   0 65.7g  51m  34m R   101  0.0   0:08.50 postgres:
> user1 db 0.0.0.3(44630) SELECT
>  81797 postgres  20   0 65.7g  51m  34m R   101  0.0   0:08.03 postgres:
> user1 db 0.0.0.6(60752) SELECT
>  67103 postgres  20   0 65.7g  81m  56m R97  0.0   2:01.89 postgres:
> user1 db 0.0.0.4(46337) SELECT
>  82527 postgres  20   0 65.7g  25m  20m R93  0.0   0:02.35 postgres:
> user1 db 0.0.0.2(52490) SELECT
>  82559 postgres  20   0 65.7g  25m  20m R93  0.0   0:02.17 postgres:
> user1 db 0.0.0.2(52496) SELECT
>  82728 postgres  20   0 65.7g  80m  76m R93  0.0   0:00.60 postgres:
> user1 db 0.0.0.6(60957) SELECT
>  65588 postgres  20   0 65.7g  76m  56m R89  0.0   2:12.27 postgres:
> user1 db 0.0.0.6(57195) SELECT
>  80594 postgres  20   0 65.7g  34m  28m R89  0.0   0:22.81 postgres:
> user1 db 0.0.0.2(52071) SELECT
>  25176 postgres  20   0 65.7g  74m  57m R85  0.0   7:24.42 postgres:
> user1 db 0.0.0.2(39410) SELECT
>  82182 postgres  20   0 65.7g 513m 502m R85  0.0   0:04.85 postgres:
> user1 db 0.0.0.4(49789) SELECT
>  82034 postgres  20   0 65.7g 523m 510m R81  0.0   0:05.79 postgres:
> user1 db 0.0.0.3(44683) SELECT
>  82439 postgres  20   0 65.7g 262m 258m R81  0.0   0:02.64 postgres:
> user1 db 0.0.0.6(60887) SELECT
>  82624 postgres  20   0 65.7g 148m 143m R81  0.0   0:01.20 postgres:
> user1 db 0.0.0.4(49888) SELECT
>  82637 postgres  20   0 65.7g 139m 134m R81  0.0   0:01.17 postgres:
> user1 db 0.0.0.3(44805) SELECT
>  82669 postgres  20   0 65.7g 119m 114m R81  0.0   0:00.97 postgres:
> user1 db 0.0.0.6(60939) SELECT
>  82723 postgres  20   0 65.7g  79m  75m R81  0.0   0:00.56 postgres:
> user1 db 0.0.0.4(49907) SELECT
>  29160 postgres  20   0 65.7g  79m  54m R77  0.0   6:52.13 postgres:
> user1 db 0.0.0.6(48802) SELECT
>  51095 postgres  20   0 65.7g  81m  57m R77  0.0   4:01.51 postgres:
> user1 db 0.0.0.4(42914) SELECT
>  81833 postgres  20   0 65.7g 528m 515m R77  0.0   0:07.23 postgres:
> user1 db 0.0.0.3(44644) SELECT
>  81978 postgres  20   0 65.7g 528m 515m R77  0.0   0:06.05 postgres:
> user1 db 0.0.0.2(52364) SELECT
>  82099 postgres  20   0 65.7g 523m 510m R77  0.0   0:05.18 postgres:
> user1 db 0.0.0.3(44692) SELECT
>  82111 postgres  20   0 65.7g 523m 510m R77  0.0   0:05.14 postgres:
> user1 db 0.0.0.4(49773) SELECT
>  82242 postgres  20   0 65.7g 433m 429m R77  0.0   0:04.27 postgres:
> user1 db 0.0.0.2(52428) SELECT
>  82292 postgres  20   0 65.7g 407m 402m R77  0.0   0:04.10 postgres:
> user1 db 0.0.0.2(52440) SELECT
>  82408 postgres  20   0 65.7g 292m 288m R77  0.0   0:02.98 postgres:
> user1 db 0.0.0.4(49835) SELECT
>  82542 postgres  20   0 65.7g 207m 202m R77  0.0   0:01.98 postgres:
> user1 db 0.0.0.4(49868) SELECT
>  63638 postgres  20   0 65.7g  80m  56m R73  0.0   2:30.10 postgres:
> user1 db 0.0.0.2(48699) SELECT
>  71572 postgres  20   0 65.7g  80m  56m R73  0.0   1:31.13 postgres:
> user1 db 0.0.0.2(50223) SELECT
>  80580 postgres  20   0 65.7g  34m  28m R73  0.0   0:22.93 postgres:
> user1 db 0.0.0.2(52065) SELECT
>  81650 postgres  20   0 65.8g 622m 555m R73  0.0   0:08.84 postgres:
> user1 db 0.0.0.2(52290) SELECT
>  81728 postgres  20   0 65.7g 523m 510m R73  0.0   0:08.28 postgres:
> user1 db 0.0.0.4(49684) SELECT
>  81942 postgres  20   0 65.7g 528m 515m R73  0.0   0:06.46 postgres:
> user1 db 0.0.0.2(52355) SELECT
>  81958 postgres  20   0 65.7g 528m 514m R73  0.0   0:06.48 postgres:
> user1 db 0.0.0.4(49744) SELECT
>  81980 postgres  20   0 65.7g 528m 515m R73  0.0   0:06.02 postgres:
> user1 db 0.0.0.3(44671) SELECT
>  82007 postgres  20   0 65.7g 523m 510m R73  0.0   0:06.27 postgres:
> user1 db 0.0.0.3(44676) SELECT
>  82374 postgres  20   0 65.7g 367m 362m R73  0.0

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread Jeff Janes

On Fri, Oct 23, 2015 at 7:29 AM, David Osborne  wrote:

> Hi,
>
> Wondering if anyone could suggest how we could improve the performance of
> this type of query?
> The intensive part is the summing of integer arrays as far as I can see.
>

Postgres does not ship with any 'sum' function which takes array arguments.

> select sum('{1,2,3,4,5,6}'::int[]);

ERROR:  function sum(integer[]) does not exist

Are you using a user defined function?  If so, how did you define it?

Cheers,

Jeff

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread David Osborne

Ah yes sorry:

I think these cover it...

CREATE AGGREGATE sum (
  sfunc = array_add,
  basetype = INTEGER[],
  stype = INTEGER[],
  initcond = '{}'
   );

CREATE OR REPLACE FUNCTION array_add(int[],int[]) RETURNS int[] AS $$
   -- Add two arrays.
   select
  ARRAY (
 SELECT coalesce($1[i],0) + coalesce($2[i],0)
 FROM (
select generate_series(least(array_lower($1, 1),array_lower($2,
1)), greatest(array_upper($1, 1),array_upper($2, 1)), 1) AS i
 ) sub
   GROUP BY i
   ORDER BY i
   );
$$ LANGUAGE sql STRICT IMMUTABLE;




On 23 October 2015 at 17:15, Jeff Janes  wrote:

> On Fri, Oct 23, 2015 at 7:29 AM, David Osborne  wrote:
>
>
>> Hi,
>>
>> Wondering if anyone could suggest how we could improve the performance of
>> this type of query?
>> The intensive part is the summing of integer arrays as far as I can see.
>>
>
>
> Postgres does not ship with any 'sum' function which takes array arguments.
>
> > select sum('{1,2,3,4,5,6}'::int[]);
>
> ERROR:  function sum(integer[]) does not exist
>
> Are you using a user defined function?  If so, how did you define it?
>
> Cheers,
>
> Jeff
>

Re: [PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread Merlin Moncure

On Friday, October 23, 2015, David Osborne  wrote:

> Hi,
>
> Wondering if anyone could suggest how we could improve the performance of
> this type of query?
> The intensive part is the summing of integer arrays as far as I can see.
> We're thinking there's not much we can do to improve performance apart
> from throw more CPU at it... would love to be proven wrong though!
>
>
> *Query:*
>
>   explain (analyse,buffers)
>   select
>   sum(s2.array_a),sum(s2.array_b)
>   from mytable s1 left join mytable s2
>   on s1.code=s2.code and s1.buyer=s2.seller and s2.seller='XX'
>   where s1.buyer='XX'
>   group by s1.buyer,s1.code
> ;
>
>
> *Depesz Explain Link:*
>
> http://explain.depesz.com/s/m3XP
>
>
>  QUERY PLAN
>
>
> 
>  GroupAggregate  (cost=275573.49..336223.36 rows=2547 width=524) (actual
> time=1059.340..22946.772 rows=22730 loops=1)
>Buffers: shared hit=113596 read=1020 dirtied=15
>->  Merge Left Join  (cost=275573.49..278850.09 rows=113560 width=524)
> (actual time=1058.773..1728.186 rows=240979 loops=1)
>  Merge Cond: ((s1.code)::text = (s2.code)::text)
>  Join Filter: (s1.buyer = (s2.seller)::bpchar)
>  Buffers: shared hit=113596 read=1020 dirtied=15
>  ->  Index Only Scan using mytable_buyer_idx on mytable s1
>  (cost=0.42..1226.06 rows=25465 width=12) (actual time=0.015..35.790
> rows=22730 loops=1)
>Index Cond: (buyer = 'XX'::bpchar)
>Heap Fetches: 3739
>Buffers: shared hit=16805 dirtied=1
>  ->  Sort  (cost=275573.07..275818.33 rows=98106 width=525)
> (actual time=1058.736..1141.560 rows=231662 loops=1)
>Sort Key: s2.code
>Sort Method: quicksort  Memory: 241426kB
>Buffers: shared hit=96791 read=1020 dirtied=14
>->  Bitmap Heap Scan on mytable s2
>  (cost=12256.28..267439.07 rows=98106 width=525) (actual
> time=60.330..325.730 rows=231662 loops=1)
>  Recheck Cond: ((seller)::text = 'XX'::text)
>  Filter: ((seller)::bpchar = 'XX'::bpchar)
>  Buffers: shared hit=96791 read=1020 dirtied=14
>  ->  Bitmap Index Scan on mytable_seller_idx
>  (cost=0.00..12231.75 rows=254844 width=0) (actual time=40.474..40.474
> rows=233244 loops=1)
>Index Cond: ((seller)::text = 'XX'::text)
>Buffers: shared hit=30 read=1020
>  Total runtime: 22968.292 ms
> (22 rows)
>
>
>
> *Table size:*
>
> => select count(*) from mytable;
>  count
> 
>  602669
> (1 row)
>
>
> *Array types:*
>
> # select array_a,array_b from mytable limit 1;
>   array_a  | array_b
> ---+---
>  {0,0,0,0,0,0,0,0,0,0,0,0} | {0,0,0,0,0,0,0,0,0,0,0,0}
>
>
> *Example schema:*
>
> # \d mytable
> Table "public.mytable"
>   Column   | Type  |   Modifiers
> ---+---+
>  buyer | character(2)  | not null
>  code  | character varying(20) | not null
>  seller| character varying(50) |
>  array_a   | integer[] |
>  array_b   | integer[] |
> Indexes:
> "mytable_buyer_code_idx" UNIQUE, btree (buyer, code) CLUSTER
> "mytable_buyer_idx" btree (buyer)
> "mytable_code_idx" btree (code)
> "mytable_seller_idx" btree (seller)
>
>
> *Version:*
>
> > SELECT version() ;
>version
>
>
> --
>  PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3
> 20120306 (Red Hat 4.6.3-2), 64-bit
> (1 row)
>
> This is running on an AWS RDS instance.
>
> Thanks for any pointers
> --
> David
>

What's physical memory and setting of work_mem?

merlin

[PERFORM] GroupAggregate and Integer Arrays

2015-10-23 Thread David Osborne

Hi,

Wondering if anyone could suggest how we could improve the performance of
this type of query?
The intensive part is the summing of integer arrays as far as I can see.
We're thinking there's not much we can do to improve performance apart from
throw more CPU at it... would love to be proven wrong though!


*Query:*

  explain (analyse,buffers)
  select
  sum(s2.array_a),sum(s2.array_b)
  from mytable s1 left join mytable s2
  on s1.code=s2.code and s1.buyer=s2.seller and s2.seller='XX'
  where s1.buyer='XX'
  group by s1.buyer,s1.code
;


*Depesz Explain Link:*

http://explain.depesz.com/s/m3XP


   QUERY PLAN


 GroupAggregate  (cost=275573.49..336223.36 rows=2547 width=524) (actual
time=1059.340..22946.772 rows=22730 loops=1)
   Buffers: shared hit=113596 read=1020 dirtied=15
   ->  Merge Left Join  (cost=275573.49..278850.09 rows=113560 width=524)
(actual time=1058.773..1728.186 rows=240979 loops=1)
 Merge Cond: ((s1.code)::text = (s2.code)::text)
 Join Filter: (s1.buyer = (s2.seller)::bpchar)
 Buffers: shared hit=113596 read=1020 dirtied=15
 ->  Index Only Scan using mytable_buyer_idx on mytable s1
 (cost=0.42..1226.06 rows=25465 width=12) (actual time=0.015..35.790
rows=22730 loops=1)
   Index Cond: (buyer = 'XX'::bpchar)
   Heap Fetches: 3739
   Buffers: shared hit=16805 dirtied=1
 ->  Sort  (cost=275573.07..275818.33 rows=98106 width=525) (actual
time=1058.736..1141.560 rows=231662 loops=1)
   Sort Key: s2.code
   Sort Method: quicksort  Memory: 241426kB
   Buffers: shared hit=96791 read=1020 dirtied=14
   ->  Bitmap Heap Scan on mytable s2
 (cost=12256.28..267439.07 rows=98106 width=525) (actual
time=60.330..325.730 rows=231662 loops=1)
 Recheck Cond: ((seller)::text = 'XX'::text)
 Filter: ((seller)::bpchar = 'XX'::bpchar)
 Buffers: shared hit=96791 read=1020 dirtied=14
 ->  Bitmap Index Scan on mytable_seller_idx
 (cost=0.00..12231.75 rows=254844 width=0) (actual time=40.474..40.474
rows=233244 loops=1)
   Index Cond: ((seller)::text = 'XX'::text)
   Buffers: shared hit=30 read=1020
 Total runtime: 22968.292 ms
(22 rows)



*Table size:*

=> select count(*) from mytable;
 count

 602669
(1 row)


*Array types:*

# select array_a,array_b from mytable limit 1;
  array_a  | array_b
---+---
 {0,0,0,0,0,0,0,0,0,0,0,0} | {0,0,0,0,0,0,0,0,0,0,0,0}


*Example schema:*

# \d mytable
Table "public.mytable"
  Column   | Type  |   Modifiers
---+---+
 buyer | character(2)  | not null
 code  | character varying(20) | not null
 seller| character varying(50) |
 array_a   | integer[] |
 array_b   | integer[] |
Indexes:
"mytable_buyer_code_idx" UNIQUE, btree (buyer, code) CLUSTER
"mytable_buyer_idx" btree (buyer)
"mytable_code_idx" btree (code)
"mytable_seller_idx" btree (seller)


*Version:*

> SELECT version() ;
   version

--
 PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.3
20120306 (Red Hat 4.6.3-2), 64-bit
(1 row)

This is running on an AWS RDS instance.

Thanks for any pointers
-- 
David

Re: [PERFORM] Recursive query performance issue

Re: [PERFORM] GroupAggregate and Integer Arrays

Re: [PERFORM] GroupAggregate and Integer Arrays

Re: [PERFORM] GroupAggregate and Integer Arrays

[PERFORM] GroupAggregate and Integer Arrays

5 matches

Site Navigation

Mail list logo

Footer information