Re: [PERFORM] SQL Query Performance - what gives?

[Ivan Voras]

Karl Denninger wrote:


The bitmask allows the setting of multiple permissions but the table
definition doesn't have to change (well, so long as the bits fit into a
word!)  Finally, this is a message forum - the actual code itself is
template-driven and the bitmask permission structure is ALL OVER the
templates; getting that out of there would be a really nasty rewrite,
not to mention breaking the user (non-developer, but owner)
extensibility of the current structure.

Is there a way to TELL the planner how to deal with this, even if it
makes the SQL non-portable or is a hack on the source mandatory?


You could maybe create function indexes for common bitmap operations; 
for example if it's common to check a single bit you could create 32 
indexes, on (field & 1), (field & 2), (field & 4), etc. You could also 
maybe extend this so if you need to query multiple bits you decompose 
them into individual single-bit queries, e.g. instead of (field & 3) you 
do ((field & 1) and (field & 2)).


I suppose there will be a break-even point in complexity before which 
the above approach will be very slow but after it it should scale better 
then the alternative.



--
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] Weird index or sort behaviour

[Matthew Wakeling]

On Tue, 18 Aug 2009, Tom Lane wrote:

->  Index Scan using locationbin8000__subjectobjectbin on 
locationbin8000 l1
  (cost=0.00..71635.23 rows=657430 width=20)
  (actual time=0.056..170.857 rows=664588 loops=1)
  Index Cond: (subjecttype = 'GeneFlankingRegion'::text)
->  Index Scan using locationbin8000__subjectobjectbin on 
locationbin8000 l2
  (cost=0.00..71635.23 rows=657430 width=20)
  (actual time=0.020..9594.466 rows=38231659 loops=1)
  Index Cond: (l2.subjecttype = 'GeneFlankingRegion'::text)



 ... So on average, we will be rewinding by 57 rows each time.


As indeed is reflected in those actual rowcounts.  (The estimated
counts and costs don't include re-fetching, but the actuals do.)

Even more interesting, the actual runtime is about 56x different too,
which implies that Matthew's re-fetches are not noticeably cheaper than
the original fetches.  I'd be surprised if that were true in an
indexscan pulling from disk (you'd expect recently-touched rows to stay
cached for awhile).  But it could easily be true if the whole table were
cached already.  Matthew, how big is this table compared to your RAM?
Were you testing a case in which it'd be in cache?


Oh, definitely. I have run this test so many times, it's all going to be 
in the cache. Luckily, that's what we are looking at as a normal situation 
in production. Also, since the table is clustered on that index, I would 
expect the performance when it is out of cache to be fairly snappy anyway.


For reference, the table is 350 MB, the index is 238 MB, and the RAM in 
the machine is 4GB (although it's my desktop so it'll have all sorts of 
other rubbish using that up). Our servers have 16GB to 32GB of RAM, so no 
problem there.


Matthew

--
I'm always interested when [cold callers] try to flog conservatories.
Anyone who can actually attach a conservatory to a fourth floor flat
stands a marginally better than average chance of winning my custom.
(Seen on Usenet)

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] PG 8.3 and server load

[Phoenix Kiula]

I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
PG version is 8.3.7, compiled as 64bit.
The memory is 8GB.
It's a 2 x Dual Core Intel 5310.
Hard disks are Raid 1, SCSI 15 rpm.

The server is running just one website. So there's Apache 2.2.11,
MySQL (for some small tasks, almost negligible).

And then there's PG, which in the "top" command shows up as the main beast.

My server load is going to 64, 63, 65, and so on.

Where should I start debugging? What should I see? TOP command does
not yield anything meaningful. I mean, even if it shows that postgres
user for "postmaster" and nobody user for "httpd" (apache) are the
main resource hogs, what should I start with in terms of debugging?

-- 
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] PG 8.3 and server load

[Ivan Voras]

Phoenix Kiula wrote:

I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
PG version is 8.3.7, compiled as 64bit.
The memory is 8GB.
It's a 2 x Dual Core Intel 5310.
Hard disks are Raid 1, SCSI 15 rpm.

The server is running just one website. So there's Apache 2.2.11,
MySQL (for some small tasks, almost negligible).

And then there's PG, which in the "top" command shows up as the main beast.

My server load is going to 64, 63, 65, and so on.

Where should I start debugging? What should I see? TOP command does
not yield anything meaningful. I mean, even if it shows that postgres
user for "postmaster" and nobody user for "httpd" (apache) are the
main resource hogs, what should I start with in terms of debugging?


If postgres or apache are the reason for the high load, it means you 
have lots of simultaneous users hitting either server.


The only thing you can do (except of course denying service to the 
users) is investigate which requests / queries take the most time and 
optimize them.


pgtop (http://pgfoundry.org/projects/pgtop/) might help you see what is 
your database doing. You will also probably need to use something like 
pqa (http://pqa.projects.postgresql.org/) to find top running queries.


Unfortunately, if you cannot significantly optimize your queries, there 
is not much else you can do with the hardware you have.



--
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] PG 8.3 and server load

[Guillaume Cottenceau]

Ivan Voras  writes:

> pgtop (http://pgfoundry.org/projects/pgtop/) might help you see what
> is your database doing.

A simpler (but most probably less powerful) method would be to
activate "stats_command_string = on" in the server configuration,
then issue that query to view the currently running queries:

SELECT procpid, datname, current_query, query_start FROM pg_stat_activity WHERE 
current_query <> ''

That may also be interesting.

-- 
Guillaume Cottenceau

-- 
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] PG 8.3 and server load

[Andy Colson]

Phoenix Kiula wrote:

I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
PG version is 8.3.7, compiled as 64bit.
The memory is 8GB.
It's a 2 x Dual Core Intel 5310.
Hard disks are Raid 1, SCSI 15 rpm.

The server is running just one website. So there's Apache 2.2.11,
MySQL (for some small tasks, almost negligible).

And then there's PG, which in the "top" command shows up as the main beast.

My server load is going to 64, 63, 65, and so on.

Where should I start debugging? What should I see? TOP command does
not yield anything meaningful. I mean, even if it shows that postgres
user for "postmaster" and nobody user for "httpd" (apache) are the
main resource hogs, what should I start with in terms of debugging?



1) check if you are using swap space.  Use free and make sure swap/used 
is a small number.  Check vmstat and see if swpd is moving up and down. 
 (Posting a handful of lines from vmstat might help us).


2) check 'ps ax|grep postgres' and make sure nothing says "idle in 
transaction"


3) I had a web box where the number of apache clients was set very high, 
and the box was brought to its knees by the sheer number of connections. 
 check "ps ax|grep http|wc --lines" and make sure its not too big. 
(perhaps less than 100)


-Andy

--
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] PG 8.3 and server load

[Karl Denninger]

Andy Colson wrote:
> Phoenix Kiula wrote:
>> I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
>> PG version is 8.3.7, compiled as 64bit.
>> The memory is 8GB.
>> It's a 2 x Dual Core Intel 5310.
>> Hard disks are Raid 1, SCSI 15 rpm.
>>
>> The server is running just one website. So there's Apache 2.2.11,
>> MySQL (for some small tasks, almost negligible).
>>
>> And then there's PG, which in the "top" command shows up as the main
>> beast.
>>
>> My server load is going to 64, 63, 65, and so on.
>>
>> Where should I start debugging? What should I see? TOP command does
>> not yield anything meaningful. I mean, even if it shows that postgres
>> user for "postmaster" and nobody user for "httpd" (apache) are the
>> main resource hogs, what should I start with in terms of debugging?
>>
>
> 1) check if you are using swap space.  Use free and make sure
> swap/used is a small number.  Check vmstat and see if swpd is moving
> up and down.  (Posting a handful of lines from vmstat might help us).
>
> 2) check 'ps ax|grep postgres' and make sure nothing says "idle in
> transaction"
>
> 3) I had a web box where the number of apache clients was set very
> high, and the box was brought to its knees by the sheer number of
> connections.  check "ps ax|grep http|wc --lines" and make sure its not
> too big. (perhaps less than 100)
>
> -Andy
>
I will observe that in some benchmark tests I've done on my application
(a VERY heavy Postgres user) CentOS was RADICALLY inferior in terms of
carrying capacity and performance to FreeBSD on the same hardware.

I have no idea why - you wouldn't expect this sort of result, but it is
what it is.  The test platform in my case was a Core i7 box (8 cores
SMP) with 6GB of memory running 64-bit code across the board.  Disks
were on a 3Ware coprocessor board.

I was quite surprised by this given that in general CentOS seems to be
comparable for base Apache (web service) use to FreeBSD, but due to this
recommend strongly in favor of FreeBSD for applications where web
service + PostgreSQL are the intended application mix.

-- Karl
begin:vcard
fn:Karl Denninger
n:Denninger;Karl
org:Cuda Systems LLC
adr;dom:;;314 Olde Post Road;Niceville;FL;32578
email;internet:k...@denninger.net
tel;work:850-376-9364
tel;fax:850-897-9364
x-mozilla-html:TRUE
url:http://market-ticker.org
version:2.1
end:vcard


-- 
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] PG 8.3 and server load

[Andy Colson]

Phoenix Kiula wrote:

Thanks, but swap is not changing, there is no idle transaction, and
number of connections are 28/29.

Here are some command line stamps...any other ideas?



[MYSITE] ~ > date && vmstat
Wed Aug 19 10:00:37 CDT 2009
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 3  1  20920  25736  60172 75949880074   1530 3 10  5 74 12

[MYSITE] ~ > date && vmstat
Wed Aug 19 10:00:40 CDT 2009
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 0  1  20920  34696  60124 75939960074   1530 3 10  5 74 12

[MYSITE] ~ > ps ax|grep postgres
25302 ?Ss 0:00 postgres: logger process
25352 ?Ss 0:07 postgres: writer process
25353 ?Ss 4:21 postgres: stats collector process
23483 ?Ds 0:00 postgres: snipurl_snipurl snipurl
127.0.0.1(51622) UPDATE
23485 pts/12   S+ 0:00 grep postgres

[MYSITE] ~ > date && vmstat
Wed Aug 19 10:00:55 CDT 2009
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 0  0  20920  49464  60272 75977480074   1530 3 10  5 74 12

[MYSITE] ~ > ps ax|grep http|wc --lines
28

[MYSITE] ~ > ps ax|grep http|wc --lines
29

[MYSITE] ~ > ps ax|grep postgres
25302 ?Ss 0:00 postgres: logger process
25352 ?Ss 0:07 postgres: writer process
25353 ?Ss 4:21 postgres: stats collector process
24718 pts/12   S+ 0:00 grep postgres

[MYSITE] ~ > date && vmstat
Wed Aug 19 10:01:23 CDT 2009
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 0  0  20920 106376  59220 75310160074   1530 3 10  5 74 12




On Wed, Aug 19, 2009 at 10:01 PM, Andy Colson wrote:

Phoenix Kiula wrote:

I'm on a CentOS 5 OS 64 bit, latest kernel and all of that.
PG version is 8.3.7, compiled as 64bit.
The memory is 8GB.
It's a 2 x Dual Core Intel 5310.
Hard disks are Raid 1, SCSI 15 rpm.

The server is running just one website. So there's Apache 2.2.11,
MySQL (for some small tasks, almost negligible).

And then there's PG, which in the "top" command shows up as the main
beast.

My server load is going to 64, 63, 65, and so on.

Where should I start debugging? What should I see? TOP command does
not yield anything meaningful. I mean, even if it shows that postgres
user for "postmaster" and nobody user for "httpd" (apache) are the
main resource hogs, what should I start with in terms of debugging?


1) check if you are using swap space.  Use free and make sure swap/used is a
small number.  Check vmstat and see if swpd is moving up and down.  (Posting
a handful of lines from vmstat might help us).

2) check 'ps ax|grep postgres' and make sure nothing says "idle in
transaction"

3) I had a web box where the number of apache clients was set very high, and
the box was brought to its knees by the sheer number of connections.  check
"ps ax|grep http|wc --lines" and make sure its not too big. (perhaps less
than 100)

-Andy




the first line of vmstat is an average since bootup.  Kinda useless. 
run it as:  'vmstat 4'


it will print a line every 4 seconds, which will be a summary of 
everything that happened in the last 4 seconds.


since boot, you've written out an average of 153 blocks (the bo column). 
 Thats very small, so your not io bound.


but... you have average 74% idle cpu.  So your not cpu bound either?

Ahh?  I'm not sure what that means.  Maybe I'm reading something wrong?

-Andy

--
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] PG 8.3 and server load

[Phoenix Kiula]

On Wed, Aug 19, 2009 at 11:25 PM, Andy Colson wrote:

.


>
> the first line of vmstat is an average since bootup.  Kinda useless. run it
> as:  'vmstat 4'
>
> it will print a line every 4 seconds, which will be a summary of everything
> that happened in the last 4 seconds.
>
> since boot, you've written out an average of 153 blocks (the bo column).
>  Thats very small, so your not io bound.
>
> but... you have average 74% idle cpu.  So your not cpu bound either?
>
> Ahh?  I'm not sure what that means.  Maybe I'm reading something wrong?
>
> -Andy
>




~ > vmstat 4
procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 0  2  16128  35056  62800 76974280074   1530 3 10  5 74 12
 0  0  16128  38256  62836 769817200   166   219 1386  1440  7  4 85  4
 0  1  16128  34704  62872 769891600   119   314 1441  1589  7  4 85  5
 0  0  16128  29544  62912 769939600   142   144 1443  1418  6  3 88  2
 7  1  16128  26784  62832 769219600   343   241 1492  1671  8  5 83  4
 0  0  16128  32840  62880 769318800   253   215 1459  1511  7  4 85  4
 0  0  16128  30112  62940 769390800   187   216 1395  1282  6  3 87  4

-- 
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] PG 8.3 and server load

[Kevin Grittner]

Andy Colson  wrote:
> Phoenix Kiula wrote:
 
 It's a 2 x Dual Core Intel 5310.
 
> you have average 74% idle cpu.  So your not cpu bound either?
 
Or one CPU is pegged and the other three are idle
 
-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] PG 8.3 and server load

[Andy Colson]

Kevin Grittner wrote:

Andy Colson  wrote:

Phoenix Kiula wrote:
 

It's a 2 x Dual Core Intel 5310.
 

you have average 74% idle cpu.  So your not cpu bound either?
 
Or one CPU is pegged and the other three are idle
 
-Kevin


Ahh, yeah...

Phoenix:  run top again, and hit the '1' key.  It'll show you stats for 
each cpu.  Is one pegged and the others idle?



do a 'cat /proc/cpuinfo' and make sure your os is seeing all your cpus.

-Andy

--
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] PG 8.3 and server load

[Matthew Wakeling]

On Wed, 19 Aug 2009, Phoenix Kiula wrote:

~ > vmstat 4
procs ---memory-- ---swap-- -io --system-- cpu
r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
0  2  16128  35056  62800 76974280074   1530 3 10  5 74 12
0  0  16128  38256  62836 769817200   166   219 1386  1440  7  4 85  4
0  1  16128  34704  62872 769891600   119   314 1441  1589  7  4 85  5
0  0  16128  29544  62912 769939600   142   144 1443  1418  6  3 88  2
7  1  16128  26784  62832 769219600   343   241 1492  1671  8  5 83  4
0  0  16128  32840  62880 769318800   253   215 1459  1511  7  4 85  4
0  0  16128  30112  62940 769390800   187   216 1395  1282  6  3 87  4


As far as I can see from this, your machine isn't very busy at all.


[MYSITE] ~ > ps ax|grep postgres
25302 ?Ss 0:00 postgres: logger process
25352 ?Ss 0:07 postgres: writer process
25353 ?Ss 4:21 postgres: stats collector process
24718 pts/12   S+ 0:00 grep postgres


Moreover, Postgres isn't doing anything either.

So, what is the problem that you are seeing? What do you want to change?

Matthew

--
Surely the value of C++ is zero, but C's value is now 1?
 -- map36, commenting on the "No, C++ isn't equal to D. 'C' is undeclared
 [...] C++ should really be called 1" response to "C++ -- shouldn't it
 be called D?"

--
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] PG 8.3 and server load

[Phoenix Kiula]

On Wed, Aug 19, 2009 at 11:37 PM, Andy Colson wrote:

>
> Phoenix:  run top again, and hit the '1' key.  It'll show you stats for
each
> cpu.  Is one pegged and the others idle?
>


top - 10:38:53 up 29 days, 5 min,  1 user,  load average: 64.99, 65.17,
65.06
Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie
Cpu0  : 17.7% us,  7.7% sy,  0.0% ni, 74.0% id,  0.7% wa,  0.0% hi,  0.0% si
Cpu1  :  6.3% us,  5.6% sy,  0.0% ni, 84.4% id,  3.6% wa,  0.0% hi,  0.0% si
Cpu2  :  5.6% us,  5.9% sy,  0.0% ni, 86.8% id,  1.7% wa,  0.0% hi,  0.0% si
Cpu3  :  5.6% us,  4.0% sy,  0.0% ni, 74.2% id, 16.2% wa,  0.0% hi,  0.0% si
Mem:   8310256k total,  8277416k used,32840k free,61944k buffers
Swap:  2096440k total,16128k used,  2080312k free,  7664224k cached

 PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND

9922 nobody15   0 49024  16m 7408 S  3.0  0.2   0:00.52 httpd

9630 nobody15   0 49020  16m 7420 S  2.3  0.2   0:00.60 httpd

9848 nobody16   0 48992  16m 7372 S  2.3  0.2   0:00.51 httpd

10995 nobody15   0 49024  16m 7304 S  2.3  0.2   0:00.35 httpd

11031 nobody15   0 48860  16m 7104 S  2.3  0.2   0:00.34 httpd

6701 nobody15   0 49028  17m 7576 S  2.0  0.2   0:01.50 httpd

10996 nobody15   0 48992  16m 7328 S  2.0  0.2   0:00.31 httpd

12232 nobody15   0 48860  16m 7004 S  1.7  0.2   0:00.05 httpd

9876 nobody15   0 48992  16m 7400 S  1.3  0.2   0:00.73 httpd

12231 nobody15   0 48860  16m 6932 S  1.3  0.2   0:00.04 httpd

12233 nobody16   0 48860  16m 6960 S  1.3  0.2   0:00.04 httpd

20315 postgres  19   0  325m 9732 9380 S  1.0  0.1   0:10.39 postmaster

31573 nobody15   0 49024  17m 7664 S  1.0  0.2   0:03.14 httpd

7954 nobody15   0 49032  16m 7400 S  1.0  0.2   0:01.14 httpd

9918 nobody15   0 48956  16m 7344 S  1.0  0.2   0:00.44 httpd

12298 nobody16   0 48860  16m 6780 S  1.0  0.2   0:00.03 httpd

6479 nobody16   0 49040  16m 7412 S  0.7  0.2   0:01.20 httpd

7950 nobody15   0 49020  16m 7388 S  0.7  0.2   0:00.83 httpd

7951 nobody15   0 49032  16m 7384 S  0.7  0.2   0:01.03 httpd

9875 nobody15   0 48948  16m 7096 S  0.7  0.2   0:00.51 httpd

9916 nobody16   0 48860  16m 7124 S  0.7  0.2   0:00.59 httpd

10969 nobody15   0 49036  16m 7380 S  0.7  0.2   0:00.29 httpd

11752 root  16   0  3620 1288  772 R  0.7  0.0   0:00.14 top

12309 nobody16   0 48860  16m 6844 S  0.7  0.2   0:00.02 httpd

20676 mysql 15   0  182m  20m 2916 S  0.3  0.3   0:00.95 mysqld

20811 root  21   0 47920  14m 5872 S  0.3  0.2   0:00.71 httpd

7952 nobody15   0 49024  16m 7524 S  0.3  0.2   0:00.96 httpd

11036 nobody15   0 48992  16m 7320 S  0.3  0.2   0:00.36 httpd

12230 nobody15   0 48860  16m 6956 S  0.3  0.2   0:00.01 httpd

12297 nobody16   0 48860  16m 6932 S  0.3  0.2   0:00.01 httpd

12299 nobody16   0 48992  16m 7120 S  0.3  0.2   0:00.01 httpd

12301 nobody20   0 48860  16m 6816 S  0.3  0.2   0:00.01 httpd

12307 nobody15   0 48860  16m 6880 S  0.3  0.2   0:00.01 httpd




> do a 'cat /proc/cpuinfo' and make sure your os is seeing all your cpus.
>



I guess it's using all 4?

Re: [PERFORM] PG 8.3 and server load

[Andy Colson]

Phoenix Kiula wrote:
On Wed, Aug 19, 2009 at 11:37 PM, Andy Colson> wrote:


 >
 > Phoenix:  run top again, and hit the '1' key.  It'll show you stats 
for each

 > cpu.  Is one pegged and the others idle?
 >


top - 10:38:53 up 29 days, 5 min,  1 user,  load average: 64.99, 65.17, 
65.06

Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie
Cpu0  : 17.7% us,  7.7% sy,  0.0% ni, 74.0% id,  0.7% wa,  0.0% hi,  0.0% si
Cpu1  :  6.3% us,  5.6% sy,  0.0% ni, 84.4% id,  3.6% wa,  0.0% hi,  0.0% si
Cpu2  :  5.6% us,  5.9% sy,  0.0% ni, 86.8% id,  1.7% wa,  0.0% hi,  0.0% si
Cpu3  :  5.6% us,  4.0% sy,  0.0% ni, 74.2% id, 16.2% wa,  0.0% hi,  0.0% si
Mem:   8310256k total,  8277416k used,32840k free,61944k buffers
Swap:  2096440k total,16128k used,  2080312k free,  7664224k cached

 PID USER  PR  NI  VIRT  RES  SHR S %CPU %MEMTIME+  COMMAND 
   
9922 nobody15   0 49024  16m 7408 S  3.0  0.2   0:00.52 httpd   
   
9630 nobody15   0 49020  16m 7420 S  2.3  0.2   0:00.60 httpd   
   
9848 nobody16   0 48992  16m 7372 S  2.3  0.2   0:00.51 httpd   
   
10995 nobody15   0 49024  16m 7304 S  2.3  0.2   0:00.35 httpd   
   
11031 nobody15   0 48860  16m 7104 S  2.3  0.2   0:00.34 httpd   
   
6701 nobody15   0 49028  17m 7576 S  2.0  0.2   0:01.50 httpd   
   
10996 nobody15   0 48992  16m 7328 S  2.0  0.2   0:00.31 httpd   
   
12232 nobody15   0 48860  16m 7004 S  1.7  0.2   0:00.05 httpd   
   
9876 nobody15   0 48992  16m 7400 S  1.3  0.2   0:00.73 httpd   
   
12231 nobody15   0 48860  16m 6932 S  1.3  0.2   0:00.04 httpd   
   
12233 nobody16   0 48860  16m 6960 S  1.3  0.2   0:00.04 httpd   
   
20315 postgres  19   0  325m 9732 9380 S  1.0  0.1   0:10.39 postmaster 
 
31573 nobody15   0 49024  17m 7664 S  1.0  0.2   0:03.14 httpd   
   
7954 nobody15   0 49032  16m 7400 S  1.0  0.2   0:01.14 httpd   
   
9918 nobody15   0 48956  16m 7344 S  1.0  0.2   0:00.44 httpd   
   
12298 nobody16   0 48860  16m 6780 S  1.0  0.2   0:00.03 httpd   
   
6479 nobody16   0 49040  16m 7412 S  0.7  0.2   0:01.20 httpd   
   
7950 nobody15   0 49020  16m 7388 S  0.7  0.2   0:00.83 httpd   
   
7951 nobody15   0 49032  16m 7384 S  0.7  0.2   0:01.03 httpd   
   
9875 nobody15   0 48948  16m 7096 S  0.7  0.2   0:00.51 httpd   
   
9916 nobody16   0 48860  16m 7124 S  0.7  0.2   0:00.59 httpd   
   
10969 nobody15   0 49036  16m 7380 S  0.7  0.2   0:00.29 httpd   
   
11752 root  16   0  3620 1288  772 R  0.7  0.0   0:00.14 top 
   
12309 nobody16   0 48860  16m 6844 S  0.7  0.2   0:00.02 httpd   
   
20676 mysql 15   0  182m  20m 2916 S  0.3  0.3   0:00.95 mysqld 
 
20811 root  21   0 47920  14m 5872 S  0.3  0.2   0:00.71 httpd   
   
7952 nobody15   0 49024  16m 7524 S  0.3  0.2   0:00.96 httpd   
   
11036 nobody15   0 48992  16m 7320 S  0.3  0.2   0:00.36 httpd   
 

Re: [PERFORM] PG 8.3 and server load

[Tom Lane]

Phoenix Kiula  writes:
> top - 10:38:53 up 29 days, 5 min,  1 user,  load average: 64.99, 65.17,
> 65.06
> Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie
> Cpu0  : 17.7% us,  7.7% sy,  0.0% ni, 74.0% id,  0.7% wa,  0.0% hi,  0.0% si
> Cpu1  :  6.3% us,  5.6% sy,  0.0% ni, 84.4% id,  3.6% wa,  0.0% hi,  0.0% si
> Cpu2  :  5.6% us,  5.9% sy,  0.0% ni, 86.8% id,  1.7% wa,  0.0% hi,  0.0% si
> Cpu3  :  5.6% us,  4.0% sy,  0.0% ni, 74.2% id, 16.2% wa,  0.0% hi,  0.0% si
> Mem:   8310256k total,  8277416k used,32840k free,61944k buffers
> Swap:  2096440k total,16128k used,  2080312k free,  7664224k cached

It sure looks from here like your box is not under any particular
stress.  The only thing that suggests a problem is the high load
average, but since that doesn't agree with any other measurements,
I'm inclined to think that the load average is simply wrong.
Do you have any actual evidence of a problem (like slow response)?

(I've seen load averages that had nothing to do with observable
reality on other Unixes, though not before on RHEL.)

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

Re: [PERFORM] PG 8.3 and server load

[Guillaume Cottenceau]

Phoenix Kiula  writes:

> Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie

The stopped and zombie processes look odd. Any reason for these?

-- 
Guillaume Cottenceau

-- 
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] PG 8.3 and server load

[Scott Marlowe]

On Wed, Aug 19, 2009 at 9:40 AM, Phoenix Kiula wrote:
> On Wed, Aug 19, 2009 at 11:37 PM, Andy Colson wrote:
>
>>
>> Phoenix:  run top again, and hit the '1' key.  It'll show you stats for
>> each
>> cpu.  Is one pegged and the others idle?
>
> top - 10:38:53 up 29 days, 5 min,  1 user,  load average: 64.99, 65.17,
> 65.06
> Tasks: 568 total,   1 running, 537 sleeping,   6 stopped,  24 zombie
> Cpu0  : 17.7% us,  7.7% sy,  0.0% ni, 74.0% id,  0.7% wa,  0.0% hi,  0.0% si
> Cpu1  :  6.3% us,  5.6% sy,  0.0% ni, 84.4% id,  3.6% wa,  0.0% hi,  0.0% si
> Cpu2  :  5.6% us,  5.9% sy,  0.0% ni, 86.8% id,  1.7% wa,  0.0% hi,  0.0% si
> Cpu3  :  5.6% us,  4.0% sy,  0.0% ni, 74.2% id, 16.2% wa,  0.0% hi,  0.0% si
> Mem:   8310256k total,  8277416k used,    32840k free,    61944k buffers
> Swap:  2096440k total,    16128k used,  2080312k free,  7664224k cached
>

OK, nothing looks odd except, as pointed out, the stopped, zombie and
high load.  The actual amount of stuff running is minimal.

I'm wondering if you've got something causing apache children to crash
and go zombie.  What parts of this setup are compiled by hand?  Are
you sure that you don't have something like apache compiled against
one version of zlib and php-mysql against another?  Not that exact
problem, but it's one of many ways to make a crash prone apache.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Query tuning

[Kevin Kempter]

Hi all;

we've been fighting this query for a few days now. we bumped up the statistict 
target for the a.id , c.url_hits_id and the b.id columns below to 250 and ran 
an analyze on the relevant tables.  we killed it after 8hrs.  

Note the url_hits table has > 1.4billion rows

Any suggestions?



$ psql -ef expl.sql pwreport
explain 

select  

a.id,   

ident_id,   

time,   

customer_name,  

extract('day' from timezone(e.name, to_timestamp(a.time))) as day,  

category_id 

from

pwreport.url_hits a left outer join 

pwreport.url_hits_category_jt c on (a.id = c.url_hits_id),  

pwreport.ident b,   

pwreport.timezone e 

where   

a.ident_id = b.id   

and b.timezone_id = e.id

and time >= extract ('epoch' from timestamp '2009-08-12')   

and time < extract ('epoch' from timestamp '2009-08-13' )   

and direction = 'REQUEST'   

;

 QUERY 
PLAN
 

 Merge Right Join  (cost=47528508.61..180424544.59 rows=10409251 width=53)
   Merge Cond: (c.url_hits_id = a.id)
   ->  Index Scan using mt_url_hits_category_jt_url_hits_id_index on 
url_hits_category_jt c  (cost=0.00..122162596.63 rows=4189283233 width=8)
   ->  Sort  (cost=47528508.61..47536931.63 rows=3369210 width=49)
 Sort Key: a.id
 ->  Hash Join  (cost=2565.00..47163219.21 rows=3369210 width=49)
   Hash Cond: (b.timezone_id = e.id)
   ->  Hash Join  (cost=2553.49..47116881.07 rows=3369210 
width=37)
 Hash Cond: (a.ident_id = b.id)
 ->  Seq Scan on url_hits a  (cost=0.00..47051154.89 
rows=3369210 width=12)
   Filter: ((direction = 
'REQUEST'::proxy_direction_enum) AND (("time")::double precision >= 
1250035200::double precision) AND (("time")::double precision < 
1250121600::double precision))
 ->  Hash  (cost=2020.44..2020.44 rows=42644 width=29)
   ->  Seq Scan on ident b  (cost=0.00..2020.44 
rows=42644 width=29)
   ->  Hash  (cost=6.78..6.78 rows=378 width=20)
 ->  Seq Scan on timezone e  (cost=0.00..6.78 rows=378 
width=20)
(15 rows)


-- 
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] Query tuning

[Grzegorz Jaśkiewicz]

that seems to be the killer:

and time >= extract ('epoch' from timestamp '2009-08-12')
and time < extract ('epoch' from timestamp '2009-08-13' )

You probably need an index on time/epoch:

CREATE INDEX foo ON table(extract ('epoch' from timestamp time );

or something like that, vacuum analyze and retry.

-- 
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] Query tuning

[Scott Carey]

On 8/19/09 9:28 AM, "Kevin Kempter"  wrote:

> Hi all;
> 
> we've been fighting this query for a few days now. we bumped up the statistict
> target for the a.id , c.url_hits_id and the b.id columns below to 250 and ran
> an analyze on the relevant tables.  we killed it after 8hrs.
> 
> Note the url_hits table has > 1.4billion rows
> 
> Any suggestions?
> 

Have you tried setting work_mem higher for just this query?

The big estimated cost is the sequential scan on url_hits.  But in reality,
if the estimates are off the sort and index scan at the end might be your
bottleneck.  Larger work_mem might make it choose another plan there.

But if the true cost is the sequential scan on url_hits, then only an index
there will help.

> 
> 
> $ psql -ef expl.sql pwreport
> explain  
> select   
> a.id,
> ident_id,
> time,
> customer_name,   
> extract('day' from timezone(e.name, to_timestamp(a.time))) as day,
> category_id  
> from 
> pwreport.url_hits a left outer join
> pwreport.url_hits_category_jt c on (a.id = c.url_hits_id),
> pwreport.ident b,
> pwreport.timezone e
> where
> a.ident_id = b.id
> and b.timezone_id = e.id
> and time >= extract ('epoch' from timestamp '2009-08-12')
> and time < extract ('epoch' from timestamp '2009-08-13' )
> and direction = 'REQUEST'
> ;
>  
> QUERY
> PLAN 
> --
> --
> 
>  Merge Right Join  (cost=47528508.61..180424544.59 rows=10409251 width=53)
>Merge Cond: (c.url_hits_id = a.id)
>->  Index Scan using mt_url_hits_category_jt_url_hits_id_index on
> url_hits_category_jt c  (cost=0.00..122162596.63 rows=4189283233 width=8)
>->  Sort  (cost=47528508.61..47536931.63 rows=3369210 width=49)
>  Sort Key: a.id
>  ->  Hash Join  (cost=2565.00..47163219.21 rows=3369210 width=49)
>Hash Cond: (b.timezone_id = e.id)
>->  Hash Join  (cost=2553.49..47116881.07 rows=3369210
> width=37)
>  Hash Cond: (a.ident_id = b.id)
>  ->  Seq Scan on url_hits a  (cost=0.00..47051154.89
> rows=3369210 width=12)
>Filter: ((direction =
> 'REQUEST'::proxy_direction_enum) AND (("time")::double precision >=
> 1250035200::double precision) AND (("time")::double precision <
> 1250121600::double precision))
>  ->  Hash  (cost=2020.44..2020.44 rows=42644 width=29)
>->  Seq Scan on ident b  (cost=0.00..2020.44
> rows=42644 width=29)
>->  Hash  (cost=6.78..6.78 rows=378 width=20)
>  ->  Seq Scan on timezone e  (cost=0.00..6.78 rows=378
> width=20)
> (15 rows)
> 
> 
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 


-- 
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] Query tuning

[Nikolas Everett]

2009/8/19 Grzegorz Jaśkiewicz 

> that seems to be the killer:
>
> and time >= extract ('epoch' from timestamp '2009-08-12')
> and time < extract ('epoch' from timestamp '2009-08-13' )
>
> You probably need an index on time/epoch:
>
> CREATE INDEX foo ON table(extract ('epoch' from timestamp time );


It looks like those extracts just make constant integer times. You probably
just create an index on the time column.

Also, why not store times as timestamps?


>
>
> or something like that, vacuum analyze and retry.
>
> --
> 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] Query tuning

[Kevin Kempter]

On Wednesday 19 August 2009 11:17:26 Scott Carey wrote:
> On 8/19/09 9:28 AM, "Kevin Kempter"  wrote:
> > Hi all;
> >
> > we've been fighting this query for a few days now. we bumped up the
> > statistict target for the a.id , c.url_hits_id and the b.id columns below
> > to 250 and ran an analyze on the relevant tables.  we killed it after
> > 8hrs.
> >
> > Note the url_hits table has > 1.4billion rows
> >
> > Any suggestions?
>
> Have you tried setting work_mem higher for just this query?

Yes, we upped it to 500Meg


>
> The big estimated cost is the sequential scan on url_hits.  But in reality,
> if the estimates are off the sort and index scan at the end might be your
> bottleneck.  Larger work_mem might make it choose another plan there.
>
> But if the true cost is the sequential scan on url_hits, then only an index
> there will help.
>
> > $ psql -ef expl.sql pwreport
> > explain
> > select
> > a.id,
> > ident_id,
> > time,
> > customer_name,
> > extract('day' from timezone(e.name, to_timestamp(a.time))) as day,
> > category_id
> > from
> > pwreport.url_hits a left outer join
> > pwreport.url_hits_category_jt c on (a.id = c.url_hits_id),
> > pwreport.ident b,
> > pwreport.timezone e
> > where
> > a.ident_id = b.id
> > and b.timezone_id = e.id
> > and time >= extract ('epoch' from timestamp '2009-08-12')
> > and time < extract ('epoch' from timestamp '2009-08-13' )
> > and direction = 'REQUEST'
> > ;
> >
> > QUERY
> > PLAN
> > -
> >-
> > -
> >- 
> >  Merge Right Join  (cost=47528508.61..180424544.59 rows=10409251
> > width=53) Merge Cond: (c.url_hits_id = a.id)
> >->  Index Scan using mt_url_hits_category_jt_url_hits_id_index on
> > url_hits_category_jt c  (cost=0.00..122162596.63 rows=4189283233 width=8)
> >->  Sort  (cost=47528508.61..47536931.63 rows=3369210 width=49)
> >  Sort Key: a.id
> >  ->  Hash Join  (cost=2565.00..47163219.21 rows=3369210 width=49)
> >Hash Cond: (b.timezone_id = e.id)
> >->  Hash Join  (cost=2553.49..47116881.07 rows=3369210
> > width=37)
> >  Hash Cond: (a.ident_id = b.id)
> >  ->  Seq Scan on url_hits a  (cost=0.00..47051154.89
> > rows=3369210 width=12)
> >Filter: ((direction =
> > 'REQUEST'::proxy_direction_enum) AND (("time")::double precision >=
> > 1250035200::double precision) AND (("time")::double precision <
> > 1250121600::double precision))
> >  ->  Hash  (cost=2020.44..2020.44 rows=42644
> > width=29) ->  Seq Scan on ident b  (cost=0.00..2020.44 rows=42644
> > width=29)
> >->  Hash  (cost=6.78..6.78 rows=378 width=20)
> >  ->  Seq Scan on timezone e  (cost=0.00..6.78
> > rows=378 width=20)
> > (15 rows)
> >
> >
> > --
> > Sent via pgsql-performance mailing list
> > (pgsql-performance@postgresql.org) To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance


-- 
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] Query tuning

[Kevin Kempter]

On Wednesday 19 August 2009 11:31:30 Nikolas Everett wrote:
> 2009/8/19 Grzegorz Jaśkiewicz 
>
> > that seems to be the killer:
> >
> > and time >= extract ('epoch' from timestamp '2009-08-12')
> > and time < extract ('epoch' from timestamp '2009-08-13' )
> >
> > You probably need an index on time/epoch:
> >
> > CREATE INDEX foo ON table(extract ('epoch' from timestamp time );
>
> It looks like those extracts just make constant integer times. You probably
> just create an index on the time column.
>
> Also, why not store times as timestamps?
>
> > or something like that, vacuum analyze and retry.
> >
> > --
> > Sent via pgsql-performance mailing list
> > (pgsql-performance@postgresql.org) To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance


We do have an index on url_hits.time

not sure why timestamps were not used, I was not here for the design phase.


Thx




-- 
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] how to set wal_buffers

[Jaime Casanova]

Hi,

Our fine manual says:
"""
The amount of memory used in shared memory for WAL data. The default
is 64 kilobytes (64kB). The setting need only be large enough to hold
the amount of WAL data generated by one typical transaction, since the
data is written out to disk at every transaction commit. This
parameter can only be set at server start.
"""

but how can measure "one typical transaction"? i read in the archives
that the useful top for this parameter is 1MB is that an "oficial"
opinion?
while we are there is there any way to know how many transactions are
we processing per period of time?

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
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] work_mem vs temp files issue

[Jaime Casanova]

Hi,

AFAIUI, work_mem is used for some operations (sort, hash, etc) for
avoiding the use of temp files on disk...

In a client server i'm monitoring (pg 8.3.7, 32GB of ram) work_mem is
set to 8MB, however i'm seeing a lot of temp files (>3 in 4 hours)
with small sizes (ie: 2021520 obviously lower than 8MB). so, why?
maybe we use work_mem until we find isn't enough and we send just the
difference to a temp file?

i'm not thinking in raising work_mem until i understand this well,
what's the point if we still create temp files that could fit in
work_mem...

PS: i have max_connections to 1024, i know i need a pool but the app
is still opening persistent conecctions to the db, so is not like i
could raise work_mem just easy until the app gets fixed

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance