On Wed, 2 Jun 2010, Jori Jovanovich wrote:
(2) Making the query faster by making the string match LESS specific (odd,
seems like it should be MORE)
No, that's the way round it should be. The LIMIT changes it all. Consider
if you have a huge table, and half of the entries match your WHERE
On Wed, Jun 2, 2010 at 7:30 PM, Craig James craig_ja...@emolecules.com wrote:
I'm testing/tuning a new midsize server and ran into an inexplicable
problem. With an RAID10 drive, when I move the WAL to a separate RAID1
drive, TPS drops from over 1200 to less than 90! I've checked everything
hi,
I'm sorry for not posting this first.
The server is the following and is being used exclusively for this
PostgreSQL instance:
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.2.real (GCC)
4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64-bit
Amazon EC2 Large Instance, 7.5GB memory, 64-bit
Craig James wrote:
I'm testing/tuning a new midsize server and ran into an inexplicable
problem. With an RAID10 drive, when I move the WAL to a separate
RAID1 drive, TPS drops from over 1200 to less than 90!
Normally 100 TPS means that the write cache on the WAL drive volume is
disabled (or
On 6/2/10 4:40 PM, Mark Kirkwood wrote:
On 03/06/10 11:30, Craig James wrote:
I'm testing/tuning a new midsize server and ran into an inexplicable
problem. With an RAID10 drive, when I move the WAL to a separate RAID1
drive, TPS drops from over 1200 to less than 90! I've checked
everything and
On Thu, 3 Jun 2010, Craig James wrote:
Also, are barriers *on* on the RAID1 mount and off on the RAID10 one?
It was the barriers. barrier=1 isn't just a bad idea on ext4, it's a
disaster.
This worries me a little. Does your array have a battery-backed cache? If
so, then it should be fast
Matthew Wakeling matt...@flymine.org wrote:
On Thu, 3 Jun 2010, Craig James wrote:
Also, are barriers *on* on the RAID1 mount and off on the RAID10
one?
It was the barriers. barrier=1 isn't just a bad idea on ext4,
it's a disaster.
This worries me a little. Does your array have a
I cant seem to pinpoint why this query is slow . No full table scans
are being done. The hash join is taking maximum time. The table
dev4_act_action has only 3 rows.
box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
1G work_mem
20G effective_cache
random_page_cost=1
Kevin Grittner wrote:
I've seen this, too (with xfs). Our RAID controller, in spite of
having BBU cache configured for writeback, waits for actual
persistence on disk for write barriers (unlike for fsync). This
does strike me as surprising to the point of bordering on qualifying
as a bug.
Craig James wrote:
This is really hard to believe, because the bonnie++ numbers and dd(1)
numbers look good (see my original post). But it's totally
repeatable. It must be some really unfortunate just missed the next
sector going by the write head problem.
Commit performance is a separate
Greg Smith g...@2ndquadrant.com wrote:
Kevin Grittner wrote:
I've seen this, too (with xfs). Our RAID controller, in spite of
having BBU cache configured for writeback, waits for actual
persistence on disk for write barriers (unlike for fsync). This
does strike me as surprising to the point
On 6/3/2010 12:47 PM, Anj Adu wrote:
I cant seem to pinpoint why this query is slow . No full table scans
are being done. The hash join is taking maximum time. The table
dev4_act_action has only 3 rows.
box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
1G work_mem
20G
On Thu, Jun 3, 2010 at 12:40 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Yeah, I read that long ago and I've disabled write barriers because
of it; however, it still seems wrong that the RAID controller
insists on flushing to the drives in write-back mode. Here are my
reasons for
Scott Marlowe scott.marl...@gmail.com wrote:
I think it's a case of the quickest, simplest answer to semi-new
tech. Not sure what to do with barriers? Just flush the whole
cache.
I'm guessing that this will get optimized in the future.
Let's hope so.
That reminds me, the write
Scott Marlowe wrote:
I think it's a case of the quickest, simplest answer to semi-new tech.
Not sure what to do with barriers? Just flush the whole cache.
Well, that really is the only useful thing you can do with regular SATA
drives; the ATA command set isn't any finer grained than that
On Thu, Jun 3, 2010 at 1:31 PM, Greg Smith g...@2ndquadrant.com wrote:
Scott Marlowe wrote:
I think it's a case of the quickest, simplest answer to semi-new tech.
Not sure what to do with barriers? Just flush the whole cache.
Well, that really is the only useful thing you can do with
Greg Smith g...@2ndquadrant.com wrote:
I think this can only be resolved usefully for all of us at the
RAID firmware level. If the controller had some logic that said
it's OK to not flush the cache when that call comes in if my
battery is working fine, that would make this whole problem go
On Thu, Jun 3, 2010 at 11:19 AM, Torsten Zühlsdorff
f...@meisterderspiele.de wrote:
Scott Marlowe schrieb:
On Tue, Jun 1, 2010 at 9:03 AM, Torsten Zühlsdorff
f...@meisterderspiele.de wrote:
Hello,
i have a set of unique data which about 150.000.000 rows. Regullary i get
a
list of data,
Link to plan
http://explain.depesz.com/s/kHa
On Thu, Jun 3, 2010 at 11:43 AM, Andy Colson a...@squeakycode.net wrote:
On 6/3/2010 12:47 PM, Anj Adu wrote:
I cant seem to pinpoint why this query is slow . No full table scans
are being done. The hash join is taking maximum time. The table
2010/6/1 Torsten Zühlsdorff f...@meisterderspiele.de:
Hello,
i have a set of unique data which about 150.000.000 rows. Regullary i get a
list of data, which contains multiple times of rows than the already stored
one. Often around 2.000.000.000 rows. Within this rows are many duplicates
and
I am reposting as my original query was mangled
The link to the explain plan is here as it does not paste well into
the email body.
http://explain.depesz.com/s/kHa
The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K
single raid-10 array
1G work_mem
default_statistics_target=1000
Hi
I am performing a DB insertion and update for 3000+ records and while doing so
i get CPU utilization
to 100% with 67% of CPU used by postgres
I have also done optimization on queries too...
Is there any way to optimized the CPU utilization for postgres
I am currently using postgres
22 matches
Mail list logo
