Tom Lane wrote:
Greg Stark [EMAIL PROTECTED] writes:
In any case the issue with the IDE protocol is that fundamentally you
can only have a single command pending. SCSI can have many commands
pending.
That's the bottom line: the SCSI protocol was designed (twenty years ago!)
to allow the
Kevin Brown [EMAIL PROTECTED] writes:
My question is: why does this (physical I/O scheduling) seem to matter
so much?
Before you flame me for asking a terribly idiotic question, let me
provide some context.
The operating system maintains a (sometimes large) buffer cache, with
each
Richard Huxton dev@archonet.com writes:
In the first, we match outer.clientnum to inner.clientnum, in the second
it's ?column10? - are you sure the query was identical in each case.
I'm guessing the unidentified column in query 2 is the reason for the
sort a couple of lines below it, which
Greg Stark wrote:
I think you're being misled by analyzing the write case.
Consider the read case. When a user process requests a block and
that read makes its way down to the driver level, the driver can't
just put it aside and wait until it's convenient. It has to go ahead
and issue the
Hi,
Just wondering... Is Postgresql able to use data present within indexes
without looking up the table data?
To be more explicit, let's say I have table with two fields a and b. If I
have an index on (a,b) and I do a request like SELECT b FROM table WHERE
a=x, will Postgresql use only the
I am new to cross references between tables, and I am trying to
understand how they impact performance. From reading the documentation I
was under the impression that deffering foreign keys would yield about
the same performance as dropping them before a copy, and adding them
after. However, I
I have done a vacuum and a vacuum analyze.
I can try again for kicks, but it is not in production so no new records are
added and vacuum analyze is ran after any mods to the indexes.
I am still pursuing Dell on why the monster box is so much slower then the
desktop as well.
Joel Fradkin
To be more explicit, let's say I have table with two fields a and b. If
I have an index on (a,b) and I do a request like SELECT b FROM table
WHERE a=x, will Postgresql use only the index, or will it need to also
read the table page for that (those) row(s)?
It must read the table because of
I am new to cross references between tables, and I am trying to
understand how they impact performance. From reading the documentation I
was under the impression that deffering foreign keys would yield about
the same performance as dropping them before a copy, and adding them
after. However, I
On 4/14/05, Joel Fradkin [EMAIL PROTECTED] wrote:
I have done a vacuum and a vacuum analyze.
I can try again for kicks, but it is not in production so no new records are
added and vacuum analyze is ran after any mods to the indexes.
I am still pursuing Dell on why the monster box is so much
Hello Chris,
Thanks for your answers.
Christopher Kings-Lynne wrote:
Deferring makes no difference to FK checking speed...
But why then is the speed acceptable if I copy and then manually add the
FK? Is the check done by the FK so much different from when it is done
automatically using an active
Deferring makes no difference to FK checking speed...
But why then is the speed acceptable if I copy and then manually add the
FK? Is the check done by the FK so much different from when it is done
automatically using an active deffered FK?
Yeah I think it uses a different query formulation...
Kevin Brown [EMAIL PROTECTED] writes:
I really don't see how this is any different between a system that has
tagged queueing to the disks and one that doesn't. The only
difference is where the queueing happens. In the case of SCSI, the
queueing happens on the disks (or at least on the
Josh from commandprompt.com had me alter the config to have
default_statistics_target = 250
Is this somehow related to what your asking me to do?
I did do an analyze, but have only ran the viw a few times.
Joel Fradkin
-Original Message-
From: Dawid Kuroczko [mailto:[EMAIL PROTECTED]
On Thu, 14 Apr 2005, Richard van den Berg wrote:
Hello Chris,
Thanks for your answers.
Christopher Kings-Lynne wrote:
Deferring makes no difference to FK checking speed...
But why then is the speed acceptable if I copy and then manually add the
FK? Is the check done by the FK so much
while you weren't looking, Kevin Brown wrote:
[reordering bursty reads]
In other words, it's a corner case that I strongly suspect
isn't typical in situations where SCSI has historically made a big
difference.
[...]
But I rather doubt that has to be a huge penalty, if any. When a
process
Imagine a system in furious activity with two (2) process regularly occuring
Process One: Long read (or write). Takes 20ms to do seek, latency, and
stream off. Runs over and over.
Process Two: Single block read ( or write ). Typical database row access.
Christopher Kings-Lynne wrote:
But why then is the speed acceptable if I copy and then manually add
the FK? Is the check done by the FK so much different from when it is
done automatically using an active deffered FK?
Yeah I think it uses a different query formulation... Actually I only
assume
sorry, don't remember whether it's SCSI or SATA II, but IIRC
the Areca controllers are just stellar for things.
If you do get SATA for db stuff..especially multiuser...i still
haven't seen anything to indicate an across-the-board primacy
for SATA over SCSI. I'd go w/SCSI, or if SATA for $$$
Christopher Kings-Lynne wrote:
No it certainly won't warn you. You have _avoided_ the check entirely.
That's why I was warning you...
I figured as much when I realized it was just a simple table update. I
was thinking more of a DB2 style set integrity command.
If you wanted to be really
My problem with this really is that in my database it is hard to predict
which inserts will be huge (and thus need FKs dissabled), so I would
have to code it around all inserts. Instead I can code my own integirty
logic and avoid using FKs all together.
Just drop the fk and re-add it, until
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
No it certainly won't warn you. You have _avoided_ the check entirely.
That's why I was warning you...
If you wanted to be really careful, you could:
Probably the better bet is to drop and re-add the FK constraint.
Hi.
Our professor told us the following story: Oracle. A client issued a
selective delete statement on a big table. After two days he lost
patience and pulled the plug. Unfortunately while starting up, oracle
had to restore all the deleted rows, which took it another two days. He
reasoned that
On 4/14/05, Tom Lane [EMAIL PROTECTED] wrote:
That's basically what it comes down to: SCSI lets the disk drive itself
do the low-level I/O scheduling whereas the ATA spec prevents the drive
from doing so (unless it cheats, ie, caches writes). Also, in SCSI it's
possible for the drive to
On 4/14/05, Joel Fradkin [EMAIL PROTECTED] wrote:
Josh from commandprompt.com had me alter the config to have
default_statistics_target = 250
Is this somehow related to what your asking me to do?
I did do an analyze, but have only ran the viw a few times.
well, he did suggest the right
Dawid Kuroczko [EMAIL PROTECTED] writes:
Basically it tells postgres how many values should it keep for
statistics per column. The config default_statistics_target
is the default (= used when creating table) and ALTER... is
a way to change it later.
Not quite. default_statistics_target is
Greg,
I posted this link under a different thread (the $7k server thread). It is
a very good read on why SCSI is better for servers than ATA. I didn't note
bias, though it is from a drive manufacturer. YMMV. There is an
interesting, though dated appendix on different manufacturers' drive
On Thu, 14 Apr 2005, Tom Lane wrote:
Stephan Szabo [EMAIL PROTECTED] writes:
... At some point, if we can work out how to do all the semantics
properly, it'd probably be possible to replace the insert type check with
a per-statement check which would be somewhere in between. That requires
About the foreign key performance:
Maybe foreign key checks could be delayed into the COMMIT phase.
In that position, you could check, that there are lots of foreign key
checks
for each foreign key pending, and do the foreign key check for an area
or for the whole table, if it is faster.
I have
Markus Bertheau [EMAIL PROTECTED] writes:
Now if you pull the plug after 2, at startup, pg will go through the
in-progress txns and mark them as aborted. That's all the recovery in
this case. All rows are still there. O(1).
Right. (Actually it's O(checkpoint interval), because we have to make
I have read a large chunk of this, and I would highly recommend it to
anyone who has been participating in the drive discussions. It is
most informative!!
Alex Turner
netEconomist
On 4/14/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Greg,
I posted this link under a different thread (the
I have put together a little head to head performance of a 15k SCSI,
10k SCSI 10K SATA w/TCQ, 10K SATA wo/TCQ and 7.2K SATA drive
comparison at storage review
I have a table A with an int column ID that references table B column
ID. Table B has about 150k rows, and has an index on B.ID. When trying
to copy 1 million rows into A, I get the following \timings:
You're using 7.4.5. It's possible that you have a type mismatch in your
foreign keys which
I'm not subscribed to performance at this time. I reviewed the
thread and owe everything I know about this to Wei Hong whose
brilliance exceeds all others :) All misinterpretations are
mine alone.
I have not reviewed hellerstein's papers posted by neil, but I
will.
My understanding of this
Nice research Alex.
Your data strongly support the information in the paper. Your SCSI drives
blew away the others in all of the server benchmarks. They're only
marginally better in desktop use.
I do find it somewhat amazing that a 15K SCSI 320 drive isn't going to help
me play Unreal
Matthew Nuzum [EMAIL PROTECTED] writes:
drive A has capacity C and spins at 15K rpms, and
drive B has capacity 2 x C and spins at 10K rpms and
all other features are the same, the price is the same and C is enough
disk space which would you choose?
In this case you always choose the 15k RPM
Kevin Brown [EMAIL PROTECTED] writes:
Greg Stark wrote:
I think you're being misled by analyzing the write case.
Consider the read case. When a user process requests a block and
that read makes its way down to the driver level, the driver can't
just put it aside and wait until
Markus Bertheau [EMAIL PROTECTED] writes:
How does oracle do that? Has all this something to do with mvcc? Why
does it take oracle so long to recover?
Postgres does pessimistic MVCC where it keeps the old versions where they
are in the table. Only after it's committed can they be cleaned up
Matthew Nuzum [EMAIL PROTECTED] writes:
So if you all were going to choose between two hard drives where:
drive A has capacity C and spins at 15K rpms, and
drive B has capacity 2 x C and spins at 10K rpms and
all other features are the same, the price is the same and C is enough
disk space
Our vendor is trying to sell us on an Intel SRCS16 SATA raid controller instead of the 3ware one.
Well I have never even heard of it. 3ware is the defacto authority of
reasonable SATA RAID. If you were to
go with a different brand I would go with LSI. The LSI 150-6 is a nice
card with a
I did as described to alter table and did not see any difference in speed.
I am trying to undo the symbolic link to the data array and set it up on
raid 5 disks in the machine just to test if there is an issue with the
config of the raid 10 array or a problem with the controller.
I am kinda lame
On Thu, Apr 14, 2005 at 10:39:03AM -0700, elein wrote:
All functions could have a cost associated with them, set by the writer of
the function in order for the planner to reorder function calls.
The stonebraker airplane level example was:
select ... from ... where f(id) = 3 and
Steve Poe wrote:
If SATA drives don't have the ability to replace SCSI for a multi-user
I don't think it is a matter of not having the ability. SATA all in all
is fine as long as
it is battery backed. It isn't as high performing as SCSI but who says
it has to be?
There are plenty of companies
Well so far I have 1.5 hours with commandpromt.com and 8 + hours with Dell
and have not seen any changes in the speed of my query.
I did move the data base to the raid 5 drives and did see a 1 second
improvement from 13 secs to 12 secs (keep in mind it runs in 6 on the
optiplex).
The dell guy
-Original Message-
From: Joel Fradkin [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 14, 2005 11:39 AM
To: 'Tom Lane'; 'Dawid Kuroczko'
Cc: 'PERFORM'
Subject: Re: [PERFORM] speed of querry?
I did as described to alter table and did not see any
difference in speed. I am trying
Looking at the numbers, the raptor with TCQ enabled was close or beat
the Atlas III 10k drive on most benchmarks.
Naturaly a 15k drive is going to be faster in many areas, but it is
also much more expensive. It was only 44% better on the server tests
than the raptor with TCQ, but it costs nearly
Alex Turner wrote:
Looking at the numbers, the raptor with TCQ enabled was close or beat
the Atlas III 10k drive on most benchmarks.
Naturaly a 15k drive is going to be faster in many areas, but it is
also much more expensive. It was only 44% better on the server tests
than the raptor with TCQ,
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
I really don't see how this is any different between a system that has
tagged queueing to the disks and one that doesn't. The only
difference is where the queueing happens. In the case of SCSI, the
queueing happens on the disks (or
3ware claim that their 'software' implemented command queueing
performs at 95% effectiveness compared to the hardware queueing on a
SCSI drive, so I would say that they agree with you.
I'm still learning, but as I read it, the bits are split across the
platters and there is only 'one' head, but
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
The reason this is so much more of a win than it was when ATA was
designed is that in modern drives the kernel has very little clue about
the physical geometry of the disk. Variable-size tracks, bad-block
sparing, and stuff like that make
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
The reason this is so much more of a win than it was when ATA was
designed is that in modern drives the kernel has very little clue about
the physical geometry of the disk. Variable-size tracks, bad-block
sparing,
Kevin Brown [EMAIL PROTECTED] writes:
In the case of pure random reads, you'll end up having to wait an
average of half of a rotation before beginning the read.
You're assuming the conclusion. The above is true if the disk is handed
one request at a time by a kernel that doesn't have any
52 matches
Mail list logo
