[PERFORM] [PERFORMANCE] work_mem vs temp files issue

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 low

[PERFORM] [PERFORMANCE] how to set wal_buffers

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. T

Re: [PERFORM] Query tuning

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/

Re: [PERFORM] Query tuning

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 o

Re: [PERFORM] Query tuning

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 )

Re: [PERFORM] Query tuning

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

Re: [PERFORM] Query tuning

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

[PERFORM] Query tuning

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?

Re: [PERFORM] PG 8.3 and server load

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 avera

Re: [PERFORM] PG 8.3 and server load

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 subscrip

Re: [PERFORM] PG 8.3 and server load

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

Re: [PERFORM] PG 8.3 and server load

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:

Re: [PERFORM] PG 8.3 and server load

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 slee

Re: [PERFORM] PG 8.3 and server load

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

Re: [PERFORM] PG 8.3 and server load

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'l

Re: [PERFORM] PG 8.3 and server load

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 ma

Re: [PERFORM] PG 8.3 and server load

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,

Re: [PERFORM] PG 8.3 and server load

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 --sys

Re: [PERFORM] PG 8.3 and server load

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

Re: [PERFORM] PG 8.3 and server load

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

Re: [PERFORM] PG 8.3 and server load

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

Re: [PERFORM] PG 8.3 and server load

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

[PERFORM] PG 8.3 and server load

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 negligib

Re: [PERFORM] Weird index or sort behaviour

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 Co

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

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