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 lo
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
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
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 ha
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 d
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, bu
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
I would ask this on the jdbc mailling list. They might know.
---
anon permutation wrote:
>
> Hi,
>
> I have just upgraded our db from 7.4.2 to 8.0.1 and we are doing some
> testing. For some reason, we have discovered t
Just to clarify these are tests from http://www.storagereview.com, not
my own. I guess they couldn't get number for those parts. I think
everyone understands that a 0ms seek time impossible, and indicates a
missing data point.
Thanks,
Alex Turner
netEconomist
On 4/14/05, Dave Held <[EMAIL PROT
> -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
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 ran
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 ru
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 expen
> -Original Message-
> From: Mohan, Ross [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 14, 2005 1:30 PM
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to improve db performance with $7K?
>
> Greg Stark wrote:
> >
> > Kevin Brown <[EMAIL PROTECTED]> writes:
> >
>
If SATA drives don't have the ability to replace SCSI for a multi-user
Postgres apps, but you needed to save on cost (ALWAYS an issue),
could/would you implement SATA for your logs (pg_xlog) and keep the rest
on SCSI?
Steve Poe
Mohan, Ross wrote:
I've been doing some reading up on this, trying t
The real question is whether you choose the single 15kRPM drive or
additional
drives at 10kRPM... Additional spindles would give a much bigger
And the bonus question.
Expensive fast drives as a RAID for everything, or for the same price
many more slower drives (even SATA) so you can put the
I've been doing some reading up on this, trying to keep up here,
and have found out that (experts, just yawn and cover your ears)
1) some SATA drives (just type II, I think?) have a "Phase Zero"
implementation of Tagged Command Queueing (the special sauce
for SCSI).
2) This SATA "TCQ" is
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 a
> -Original Message-
> From: Greg Stark [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 14, 2005 12:55 PM
> To: [EMAIL PROTECTED]
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] How to improve db performance with $7K?
>
> "Matthew Nuzum" <[EMAIL PROTECTED]> writes:
>
> >
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 batt
"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
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
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
"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 1
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 Tournamen
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 issue
> -Original Message-
> From: Alex Turner [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 14, 2005 12:14 PM
> To: [EMAIL PROTECTED]
> Cc: Greg Stark; pgsql-performance@postgresql.org;
> [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Intel SRCS16 SATA raid?
>
>
> I have put together a little
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 pr
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
http://www.storagereview.com/php/benchmark/compare_rtg_2001.php?typeID=10&testbedID=3&osID=4&raidconfigID=1&numDrives=1&devID_0=232&devID_1
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 (t
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
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 h
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
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
chara
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
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 righ
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 t
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 one
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.
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 post
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 care
Thanks for the pointer. I got this from the archives:
update pg_class set reltriggers=0 where relname = 'YOUR_TABLE_NAME';
to enable them after you are done, do
update pg_class set reltriggers = count(*) from pg_trigger where
pg_class.oid=tgrelid and relname='YOUR_TABLE_NA
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 $$$ reaso
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
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.
O
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
> access to the affected rows inside
Our vendor is trying to sell us on an Intel SRCS16 SATA raid controller
instead of the 3ware one.
Poking around it seems this does come with Linux drivers and there is a
battery backup option. So it doesn't seem to be completely insane.
Anyone have any experience with these controllers?
I'm als
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
> pro
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
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]
S
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 c
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...
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
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
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 cann
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 vis
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
Wazagua
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 cann
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
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 issu
60 matches
Mail list logo
