Re: [PERFORM] SCSI vs SATA
On Thu, Apr 05, 2007 at 11:19:04PM -0400, Ron wrote: Both statements are the literal truth. Repeating something over and over again doesn't make it truth. The OP asked for statistical evidence (presumably real-world field evidence) to support that assertion. Thus far, all the publicly available evidence does not show a significant difference between SATA and SCSI reliability in the field. Mike Stone ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SCSI vs SATA
On Fri, Apr 06, 2007 at 02:00:15AM -0400, Tom Lane wrote: It seems hard to believe that the vendors themselves wouldn't burn in the drives for half a day, if that's all it takes to eliminate a large fraction of infant mortality. The savings in return processing and customer goodwill would surely justify the electricity they'd use. Wouldn't help if the reason for the infant mortality is bad handling between the factory and the rack. One thing that I did question in the CMU study was the lack of infant mortality--I've definately observed it, but it might just be that my UPS guy is clumsier than theirs. Mike Stone ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] SCSI vs SATA
I read them as soon as they were available. Then I shrugged and noted YMMV to myself. 1= Those studies are valid for =those= users under =those= users' circumstances in =those= users' environments. How well do those circumstances and environments mimic anyone else's? I don't know since the studies did not document said in enough detail (and it would be nigh unto impossible to do so) for me to compare mine to theirs. I =do= know that neither Google's nor a university's nor an ISP's nor a HPC supercomputing facility's NOC are particularly similar to say a financial institution's or a health care organization's NOC. ...and they better not be. Ditto the personnel's behavior working them. You yourself have said the environmental factors make a big difference. I agree. I submit that therefore differences in the environmental factors are just as significant. 2= I'll bet all the money in your pockets vs all the money in my pockets that people are going to leap at the chance to use these studies as yet another excuse to pinch IT spending further. In the process they are consciously or unconsciously going to imitate some or all of the environments that were used in those studies. Which IMHO is exactly wrong for most mission critical functions in most non-university organizations. While we can't all pamper our HDs to the extent that Richard Troy's organization can, frankly that is much closer to the way things should be done for most organizations. Ditto Greg Smith's =very= good habit: "I scan all my drives for reallocated sectors, and the minute there's a single one I get e-mailed about it and get all the data off that drive pronto. This has saved me from a complete failure that happened within the next day on multiple occasions." Amen. I'll make the additional bet that no matter what they say neither Google nor the CMU places had to deal with setting up and running environments where the consequences of data loss or data corruption are as serious as they are for most mission critical business applications. =Especially= DBMSs in such organizations. If anyone tried to convince me to run a mission critical or production DBMS in a business the way Google runs their HW, I'd be applying the clue-by-four liberally in "boot to the head" fashion until either they got just how wrong they were or they convinced me they were too stupid to learn. A which point they are never touching my machines. 3= From the CMU paper: "We also find evidence, based on records of disk replacements in the field, that failure rate is not constant with age, and that, rather than a significant infant mortality effect, we see a significant early onset of wear-out degradation. That is, replacement rates in our data grew constantly with age, an effect often assumed not to set in until after a nominal lifetime of 5 years." "In our data sets, the replacement rates of SATA disks are not worse than the replacement rates of SCSI or FC disks. =This may indicate that disk independent factors, such as operating conditions, usage and environmental factors, affect replacement=." (emphasis mine) If you look at the organizations in these two studies, you will note that one thing they all have in common is that they are organizations that tend to push the environmental and usage envelopes. Especially with regards to anything involving spending money. (Google is an extreme even in that group). What these studies say clearly to me is that it is possible to be penny-wise and pound-foolish with regards to IT spending... ...and that these organizations have a tendency to be so. Not a surprise to anyone who's worked in those environments I'm sure. The last thing the IT industry needs is for everyone to copy these organization's IT behavior! 4= Tom Lane is of course correct that vendors burn in their HDs enough before selling them to get past most infant mortality. Then any time any HD is shipped between organizations, it is usually burned in again to detect and possibly deal with issues caused by shipping. That's enough to see to it that the end operating environment is not going to see a bath tub curve failure rate. Then environmental, usage, and maintenance factors further distort both the shape and size of the statistical failure curve. 5= The major conclusion of the CMU paper is !NOT! that we should buy the cheapest HDs we can because HD quality doesn't make a difference. The important conclusion is that a very large segment of the industry operates their equipment significantly enough outside manufacturer's specifications that we need a new error rate model for end use. I agree. Regardless of what Seagate et al can do in their QA labs, we need reliability numbers that are actually valid ITRW of HD usage. The other take-away is that organizational policy and procedure with regards to HD maintenance and use in most organizations could use improving. I strongly agree with t
Re: [PERFORM] SCSI vs SATA
Tom Lane wrote: Greg Smith <[EMAIL PROTECTED]> writes: On Fri, 6 Apr 2007, Tom Lane wrote: It seems hard to believe that the vendors themselves wouldn't burn in the drives for half a day, if that's all it takes to eliminate a large fraction of infant mortality. I've read that much of the damage that causes hard drive infant mortality is related to shipping. Doh, of course. Maybe I'd better go to bed now... regards, tom lane You actually sleep? -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] SCSI vs SATA
At 07:38 AM 4/6/2007, Michael Stone wrote: On Thu, Apr 05, 2007 at 11:19:04PM -0400, Ron wrote: Both statements are the literal truth. Repeating something over and over again doesn't make it truth. The OP asked for statistical evidence (presumably real-world field evidence) to support that assertion. Thus far, all the publicly available evidence does not show a significant difference between SATA and SCSI reliability in the field. Not quite. Each of our professional experiences is +also+ statistical evidence. Even if it is a personally skewed sample. For instance, Your experience suggests that infant mortality is more real than the studies stated. Does that invalidate your experience? Of course not. Does that invalidate the studies? Equally clearly not. My experience supports the hypothesis that spending slightly more for quality and treating HDs better is worth it. Does that mean one of us is right and the other wrong? Nope. Just that =in my experience= it does make a difference. The OP asked for real world evidence. We're providing it; and across a wider range of use cases than the studies used. Cheers, Ron ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SCSI vs SATA
Michael Stone wrote: On Fri, Apr 06, 2007 at 02:00:15AM -0400, Tom Lane wrote: It seems hard to believe that the vendors themselves wouldn't burn in the drives for half a day, if that's all it takes to eliminate a large fraction of infant mortality. The savings in return processing and customer goodwill would surely justify the electricity they'd use. Wouldn't help if the reason for the infant mortality is bad handling between the factory and the rack. One thing that I did question in the CMU study was the lack of infant mortality--I've definately observed it, but it might just be that my UPS guy is clumsier than theirs. Good point. Folks must realize that carriers handle computer hardware the same way they handle a box of marshmallows or ball bearings.. A box is a box is a box. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] SCSI vs SATA
On Fri, Apr 06, 2007 at 08:49:08AM -0400, Ron wrote:
Not quite. Each of our professional experiences is +also+
statistical evidence. Even if it is a personally skewed sample.
I'm not sure that word means what you think it means. I think the one
you're looking for is "anecdotal".
My experience supports the hypothesis that spending slightly more for
quality and treating HDs better is worth it.
Does that mean one of us is right and the other wrong? Nope. Just
that =in my experience= it does make a difference.
Well, without real numbers to back it up, it doesn't mean much in the
face of studies that include real numbers. Humans are, in general,
exceptionally lousy at assessing probabilities. There's a very real
tendency to exaggerate evidence that supports our preconceptions and
discount evidence that contradicts them. Maybe you're immune to that.
Personally, I tend to simply assume that anecdotal evidence isn't very
useful. This is why having some large scale independent studies is
valuable. The manufacturer's studies are obviously biased, and it's good
to have some basis for decision making other than "logic" (the classic
"proof by 'it stands to reason'"), marketing, or "I paid more for it" ("so
it's better whether it's better or not").
Mike Stone
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PERFORM] Premature view materialization in 8.2?
On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> writes: > On 4/5/07, Tom Lane <[EMAIL PROTECTED]> wrote: >>> Is this a regression, or a "feature" of 8.2? >> >> Hard to say without EXPLAIN ANALYZE output to compare. > To my eye they are identical other than the speed but perhaps I am > missing something. Yeah, it sure is the same plan, and 8.2 seems to be a tad faster right up to the hash join on user_id. Is user_id a textual datatype? I'm wondering if the 8.2 installation is using a different locale --- the speed of simple string comparisons can be horrifically worse in some locales compared to others. user_id is an int; they are both C locale. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] High Load on Postgres 7.4.16 Server
Hey Guys thanks for the input. I do have some more questions. I am looking a doing some additional tuning on the system. The first think I am looking at is the OS tuning. What kernel parameters would I look at setting on a RHEL 4 box? I have set the SHMMAX and SHMALL to 1GB. What other tuning options should I look into setting? Am I correct to assume to whatever I set the shared memory too that I can't set the total of all postgres buffers to larger than the shared memory. I am still trying to learn about properly tuning an OS and PostgreSQL system correctly. I would be interested in hearing about what other people on the list have there kernel tuned too. Best Regards John Allgood -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Cramer Sent: Thursday, April 05, 2007 4:27 PM To: John Allgood Cc: 'Jeff Frost'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server The problem with this is that it doesn't leverage shared buffers and kernel buffers well. Anyways, my bet is that your SAN isn't performing as you expect on the new hardware. Dave On 5-Apr-07, at 4:13 PM, John Allgood wrote: > We run multiple postmasters because we can shutdown one postmaster/ > database > without affecting the other postmasters/databases. Each database is a > division in our company. If we had everything under one postmaster if > something happened to the one the whole company would be down. > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Dave > Cramer > Sent: Thursday, April 05, 2007 4:01 PM > To: John Allgood > Cc: 'Jeff Frost'; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server > > > On 5-Apr-07, at 3:33 PM, John Allgood wrote: > >> The hard thing about running multiple postmasters is that you have >> to tune >> each one separate. Most of the databases I have limited the max- >> connections >> to 30-50 depending on the database. What would reasonable values for >> effective_cache_size and random_page_cost. I think I have these >> default. >> Also what about kernel buffers on RHEL4. >> > random_page_cost should be left alone > > Why do you run multiple postmasters ? I don't think this is not the > most efficient way to utilize your hardware. > > Dave > >> Thanks >> >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of Jeff >> Frost >> Sent: Thursday, April 05, 2007 3:24 PM >> To: John Allgood >> Cc: pgsql-performance@postgresql.org >> Subject: Re: [PERFORM] High Load on Postgres 7.4.16 Server >> >> On Thu, 5 Apr 2007, John Allgood wrote: >> >>> Hello All >>> >>> I sent this message to the admin list and it never got through so I >>> am trying the performance list. >>> We moved our application to a new machine last night. It is a Dell >>> PowerEdge 6950 2X Dual Core. AMD Opteron 8214 2.2Ghz. 8GB Memory. >>> The >>> machine is running Redhat AS 4 Upd 4 and Redhat Cluster Suite. The >>> SAN is >> an >>> EMC SAS connected via fibre. We are using Postgres 7.4.16. We have >> recently >>> had some major hardware issues and replaced the hardware with >>> brand new >> Dell >>> equipment. We expected a major performance increase over the >>> previous >> being >>> the old equipment was nearly three years old >>> I will try and explain how things are configured. We have 10 >>> separate postmasters running 5 on each node. Each of the >>> postmasters is a >>> single instance of each database. Each database is separated by >>> division >> and >>> also we have them separate so we can restart an postmaster with >>> needing to >>> restart all databases My largest database is about 7 GB. And the >>> others >> run >>> anywhere from 100MB - 1.8GB. >>> The other configuration was RHEL3 and Postgres 7.4.13 and Redhat >>> Cluster Suite. The application seemed to run much faster on the >>> older >>> equipment. >>> My thoughts on the issues are that I could be something with the OS >>> tuning. Here is what my kernel.shmmax, kernel.shmall = >>> 1073741824. Is >> there >>> something else that I could tune in the OS. My max_connections=35 >>> and >> shared >>> buffers=8192 for my largest database. >> >> John, >> >> Was the SAN connected to the previous machine or is it also a new >> addition >> with the Dell hardware? We had a fairly recent post regarding a >> similar >> upgrade in which the SAN ended up being the problem, so the first >> thing I >> would do is test the SAN with bonnie-++ and/or move your >> application to use >> a >> local disk and test again. With 8GB of RAM, I'd probably set the >> shared_buffers to at least 5...If I remember correctly, this >> was the >> most >> you could set it to on 7.4.x and continue benefitting from it. I'd >> strongly >> >> encourage you to upgrade to at least 8.1.8 (and possibly 8.2.3) if >> you can, >> as >> it has much better shared memory manage
Re: [PERFORM] SCSI vs SATA
On Thu, 2007-04-05 at 23:37, Greg Smith wrote: > On Thu, 5 Apr 2007, Scott Marlowe wrote: > > > On Thu, 2007-04-05 at 14:30, James Mansion wrote: > >> Can you cite any statistical evidence for this? > > Logic? > > OK, everyone who hasn't already needs to read the Google and CMU papers. > I'll even provide links for you: > > http://www.cs.cmu.edu/~bianca/fast07.pdf > http://labs.google.com/papers/disk_failures.pdf > > There are several things their data suggests that are completely at odds > with the lore suggested by traditional logic-based thinking in this area. > Section 3.4 of Google's paper basically disproves that "mechanical devices > have decreasing MTBF when run in hotter environments" applies to hard > drives in the normal range they're operated in. On the google: The google study ONLY looked at consumer grade drives. It did not compare them to server class drives. This is only true when the temperature is fairly low. Note that the drive temperatures in the google study are <=55C. If the drive temp is below 55C, then the environment, by extension, must be lower than that by some fair bit, likely 10-15C, since the drive is a heat source, and the environment the heat sink. So, the environment here is likely in the 35C range. Most server drives are rated for 55-60C environmental temperature operation, which means the drive would be even hotter. As for the CMU study: It didn't expressly compare server to consumer grade hard drives. Remember, there are server class SATA drives, and there were (once upon a time) consumer class SCSI drives. If they had separated out the drives by server / consumer grade I think the study would have been more interesting. But we just don't know from that study. Personal Experience: In my last job we had three very large storage arrays (big black refrigerator looking boxes, you know the kind.) Each one had somewhere in the range of 150 or so drives in it. The first two we purchased were based on 9Gig server class SCSI drives. The third, and newer one, was based on commodity IDE drives. I'm not sure of the size, but I believe they were somewhere around 20Gigs or so. So, this was 5 or so years ago, not recently. We had a cooling failure in our hosting center, and the internal temperature of the data center rose to about 110F to 120F (43C to 48C). We ran at that temperature for about 12 hours, before we got a refrigerator on a flatbed brought in (btw, I highly recommend Aggreko if you need large scale portable air conditioners or generators) to cool things down. In the months that followed the drives in the IDE based storage array failed by the dozens. We eventually replaced ALL the drives in that storage array because of the failure rate. The SCSI based arrays had a few extra drives fail than usual, but nothing too shocking. Now, maybe now Seagate et. al. are making their consumer grade drives from yesterday's server grade technology, but 5 or 6 years ago that was not the case from what I saw. > Your comments about > server hard drives being rated to higher temperatures is helpful, but > conclusions drawn from just thinking about something I don't trust when > they conflict with statistics to the contrary. Actually, as I looked up some more data on this, I found it interesting that 5 to 10 years ago, consumer grade drives were rated for 35C environments, while today consumer grade drives seem to be rated to 55C or 60C. Same as server drives were 5 to 10 years ago. I do think that server grade drive tech has been migrating into the consumer realm over time. I can imagine that today's high performance game / home systems with their heat generating video cards and tendency towards RAID1 / RAID0 drive setups are pushing the drive manufacturers to improve reliability of consumer disk drives. > The main thing I wish they'd published is breaking some of the statistics > down by drive manufacturer. For example, they suggest a significant > number of drive failures were not predicted by SMART. I've seen plenty of > drives where the SMART reporting was spotty at best (yes, I'm talking > about you, Maxtor) and wouldn't be surprised that they were quiet right up > to their bitter (and frequent) end. I'm not sure how that factor may have > skewed this particular bit of data. I too have pretty much given up on Maxtor drives and things like SMART or sleep mode, or just plain working properly. In recent months, we had an AC unit fail here at work, and we have two drive manufacturers for our servers. Manufacturer F and S. The drives from F failed at a much higher rate, and developed lots and lots of bad sectors, the drives from manufacturer S, OTOH, have not had an increased failure rate. While both manufacturers claim that their drives can survive in an environment of 55/60C, I'm pretty sure one of them was lying. We are silently replacing the failed drives with drives from manufacturer S. Based on experience I think that on averag
[PERFORM] more on high load on postgres 7.4.16
We are trying to attack this problem from multiple avenues, thus I'm starting a separate thread. This is with regard to the problem posted via thread: http://archives.postgresql.org/pgsql-performance/2007-04/msg00120.php One thing we are seeing with this move to the new hardware (and rhas 4) is database connection processes that are left over by users who have exited the application. I've attached to these processes via gdb and find they all have the same backtrace. Any insights into what might be causing this issue would be appreciated. Understand, we did not have this problem on the previous hardware running on rhes 3. Here is the backtrace: #0 0x00ba47a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0019f1de in __lll_mutex_lock_wait () from /lib/tls/libpthread.so.0 #2 0x0019ca7a in _L_mutex_lock_23 () from /lib/tls/libpthread.so.0 #3 0xbfed9438 in ?? () #4 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from /lib/tls/libc.so.6 #5 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from /lib/tls/libc.so.6 #6 0x0015243f in critSec::~critSec () from /usr/local/pcm170/libdalkutil.so #7 0x003a48b8 in Comp_ZipFiles () from /usr/local/pcm170/libcompress.so #8 0x00bec527 in exit () from /lib/tls/libc.so.6 #9 0x0816a52f in proc_exit () -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] more on high load on postgres 7.4.16
Geoffrey wrote: > We are trying to attack this problem from multiple avenues, thus I'm > starting a separate thread. This is with regard to the problem posted > via thread: > > http://archives.postgresql.org/pgsql-performance/2007-04/msg00120.php > > One thing we are seeing with this move to the new hardware (and rhas 4) > is database connection processes that are left over by users who have > exited the application. I've attached to these processes via gdb and > find they all have the same backtrace. Any insights into what might be > causing this issue would be appreciated. Understand, we did not have > this problem on the previous hardware running on rhes 3. Here is the > backtrace: > > #0 0x00ba47a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 > #1 0x0019f1de in __lll_mutex_lock_wait () from /lib/tls/libpthread.so.0 > #2 0x0019ca7a in _L_mutex_lock_23 () from /lib/tls/libpthread.so.0 > #3 0xbfed9438 in ?? () > #4 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from > /lib/tls/libc.so.6 > #5 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from > /lib/tls/libc.so.6 > #6 0x0015243f in critSec::~critSec () from > /usr/local/pcm170/libdalkutil.so > #7 0x003a48b8 in Comp_ZipFiles () from /usr/local/pcm170/libcompress.so /usr/local on RHEL should only contain software installed directly from source - what exactly is pcm170/libdalkutil ? beside that - is pg actually compiled with debugging symbols on that platform ? Stefan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] SCSI vs SATA
At 09:23 AM 4/6/2007, Michael Stone wrote:
On Fri, Apr 06, 2007 at 08:49:08AM -0400, Ron wrote:
Not quite. Each of our professional
experiences is +also+ statistical
evidence. Even if it is a personally skewed sample.
I'm not sure that word means what you think it
means. I think the one you're looking for is "anecdotal".
OK, let's kill this one as well. Personal
experience as related by non professionals is
often based on casual observation and of questionable quality or veracity.
It therefore is deservedly called "anecdotal".
Professionals giving evidence in their
professional capacity within their field of
expertise are under an obligation to tell the
truth, the whole truth, and nothing but the truth
to the best of their knowledge and
ability. Whether you are in court and sworn in or not.
Even if it's "just" to a mailing list ;-)
From dictionary.com
an·ec·dot·al:
1.pertaining to, resembling, or containing
anecdotes: an anecdotal history of jazz.
2.(of the treatment of subject matter in
representational art) pertaining to the
relationship of figures or to the arrangement of
elements in a scene so as to emphasize the story
content of a subject. Compare narrative (def. 6).
3.based on personal observation, case study
reports, or random investigations rather than
systematic scientific evaluation: anecdotal evidence.
+also an·ec·dot·ic (-d t' k) or an·ec·dot·i·cal
(- -k l) Of, characterized by, or full of anecdotes.
+Based on casual observations or indications
rather than rigorous or scientific analysis:
"There are anecdotal reports of children poisoned
by hot dogs roasted over a fire of the [oleander] stems" (C. Claiborne Ray).
While evidence given by professionals can't be as
rigorous as that of a double blind and controlled
study, there darn well better be nothing casual
or ill-considered about it. And it had better
!not! be anything "distorted or emphasized" just
for the sake of making the story better.
(Good Journalists deal with this one all the time.)
In short, professional advice and opinions are
supposed to be considerably more rigorous and
analytical than anything "anecdotal". The alternative is "malpractice".
My experience supports the hypothesis that
spending slightly more for quality and treating HDs better is worth it.
Does that mean one of us is right and the other
wrong? Nope. Just that =in my experience= it does make a difference.
Well, without real numbers to back it up, it
doesn't mean much in the face of studies that
include real numbers. Humans are, in general,
exceptionally lousy at assessing probabilities.
There's a very real tendency to exaggerate
evidence that supports our preconceptions and
discount evidence that contradicts them. Maybe you're immune to that.
Half agree. Half disagree.
Part of the definition of "professional" vs
"amateur" is an obligation to think and act
outside our personal "stuff" when acting in our professional capacity.
Whether numbers are explicitly involved or not.
I'm certainly not immune to personal bias. No
one is. But I have a professional obligation of
the highest order to do everything I can to make
sure I never think or act based on personal bias
when operating in my professional capacity. All professionals do.
Maybe you've found it harder to avoid personal
bias without sticking strictly to controlled
studies. I respect that. Unfortunately the RW
is too fast moving and too messy to wait for a
laboratory style study to be completed before we
are called on to make professional decisions on
most issues we face within our work
IME I have to serve my customers in a timely
fashion that for the most part prohibits me from
waiting for the perfect experiment's outcome.
Personally, I tend to simply assume that
anecdotal evidence isn't very useful.
Agreed. OTOH, there's not supposed to be
anything casual, ill-considered, or low quality
about professionals giving professional opinions within their
fields of expertise. Whether numbers are explicitly involved or not.
This is why having some large scale independent
studies is valuable. The manufacturer's studies
are obviously biased, and it's good to have some
basis for decision making other than "logic"
(the classic "proof by 'it stands to reason'"),
marketing, or "I paid more for it" ("so it's
better whether it's better or not").
No argument here. However, note that there is
often other bias present even in studies that strive to be objective.
I described the bias in the sample set of the CMU study in a previous post.
Cheers,
Ron Peacetree
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] more on high load on postgres 7.4.16
Stefan Kaltenbrunner wrote: Geoffrey wrote: We are trying to attack this problem from multiple avenues, thus I'm starting a separate thread. This is with regard to the problem posted via thread: http://archives.postgresql.org/pgsql-performance/2007-04/msg00120.php One thing we are seeing with this move to the new hardware (and rhas 4) is database connection processes that are left over by users who have exited the application. I've attached to these processes via gdb and find they all have the same backtrace. Any insights into what might be causing this issue would be appreciated. Understand, we did not have this problem on the previous hardware running on rhes 3. Here is the backtrace: #0 0x00ba47a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2 #1 0x0019f1de in __lll_mutex_lock_wait () from /lib/tls/libpthread.so.0 #2 0x0019ca7a in _L_mutex_lock_23 () from /lib/tls/libpthread.so.0 #3 0xbfed9438 in ?? () #4 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from /lib/tls/libc.so.6 #5 0x00c96a4e in pthread_cond_destroy@@GLIBC_2.3.2 () from /lib/tls/libc.so.6 #6 0x0015243f in critSec::~critSec () from /usr/local/pcm170/libdalkutil.so #7 0x003a48b8 in Comp_ZipFiles () from /usr/local/pcm170/libcompress.so /usr/local on RHEL should only contain software installed directly from source - what exactly is pcm170/libdalkutil ? It is a third party package that we have build into the backend. pcmiler. We do not have source to it though. beside that - is pg actually compiled with debugging symbols on that platform ? Not yet, I'm building it now, but I was hoping that the limited info above might get us some insights. I plan to try and recreate the problem and reproduce a more useful backtrace after rebuilding postgresql with debugging symbols. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Premature view materialization in 8.2?
"Jonathan Ellis" <[EMAIL PROTECTED]> writes: > On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Yeah, it sure is the same plan, and 8.2 seems to be a tad faster right >> up to the hash join on user_id. Is user_id a textual datatype? > user_id is an int; they are both C locale. Really!? So much for that theory. Is work_mem set similarly on both installations? The only other thing I can think is that you've exposed some unfortunate corner case in the hash join logic. Would you be willing to send me (off-list) the lists of user_ids being joined? That would be the clan_members.user_id column and the user_id column from the join of parties and clan_participants. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Premature view materialization in 8.2?
On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> writes: > On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> Yeah, it sure is the same plan, and 8.2 seems to be a tad faster right >> up to the hash join on user_id. Is user_id a textual datatype? > user_id is an int; they are both C locale. Really!? So much for that theory. Yeah, this db goes back to 7.0 so I've been careful to keep the locale set to C to avoid surprises. Is work_mem set similarly on both installations? work_mem is 8MB on 8.2; work_mem is 1MB and sort_mem is 8MB on 8.1. (there's no disk io going on with the 8.2 installation either, so it's not swapping or anything like that.) The only other thing I can think is that you've exposed some unfortunate corner case in the hash join logic. Would you be willing to send me (off-list) the lists of user_ids being joined? That would be the clan_members.user_id column and the user_id column from the join of parties and clan_participants. I can do that... you don't think the fact I mentioned, that redefining the view to leave out the expensive function fixes the problem, is relevant? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Premature view materialization in 8.2?
"Jonathan Ellis" <[EMAIL PROTECTED]> writes: > I can do that... you don't think the fact I mentioned, that > redefining the view to leave out the expensive function fixes the > problem, is relevant? Hm, I'd not have thought that an expensive function would get evaluated partway up the join tree, but maybe that's wrong. You never did show us the actual view definition ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Premature view materialization in 8.2?
On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Jonathan Ellis" <[EMAIL PROTECTED]> writes: > I can do that... you don't think the fact I mentioned, that > redefining the view to leave out the expensive function fixes the > problem, is relevant? Hm, I'd not have thought that an expensive function would get evaluated partway up the join tree, but maybe that's wrong. You never did show us the actual view definition ... It was in my original post unless it got clipped: The problem seems to be that clan_members_v contains a call to an expensive function: create or replace view clan_members_v as select cm.clan_id, cm.user_id, cp.party_id, cm.date_accepted, p.name as party_name, p_tp_total(p.id)::int as tp_total from clan_members cm, clan_participants cp, parties p where cm.user_id = p.user_id and p.id = cp.party_id ; p_tp_total takes around 50ms per row. If I create clan_members_v without the function call, the original query's speed goes to the 150ms range on 8.2 as well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] SCSI vs SATA
On Fri, Apr 06, 2007 at 12:41:25PM -0400, Ron wrote: 3.based on personal observation, case study reports, or random investigations rather than systematic scientific evaluation: anecdotal evidence. Here you even quote the appropriate definition before ignoring it. In short, professional advice and opinions are supposed to be considerably more rigorous and analytical than anything "anecdotal". The alternative is "malpractice". In any profession where malpractice is applicable, the profession opinion had better be backed up by research rather than anecdote. I'm not aware of any profession held to a "malpractice" standard which is based on personal observation and random investigation rather than formal methods. studies. I respect that. Unfortunately the RW is too fast moving and too messy to wait for a laboratory style study to be completed before we are called on to make professional decisions on most issues we face within our work IME I have to serve my customers in a timely fashion that for the most part prohibits me from waiting for the perfect experiment's outcome. Which is what distinguishes your field from a field such as engineering or medicine, and which is why waving the term "malpractice" around is just plain silly. And claiming to have to wait for perfection is a red herring. Did you record the numbers of disks involved (failed & nonfailed), the models, the environmental conditions, the poweron hours, etc.? That's what would distinguish anecdote from systematic study. Agreed. OTOH, there's not supposed to be anything casual, ill-considered, or low quality about professionals giving professional opinions within their fields of expertise. Whether numbers are explicitly involved or not. If I go to an engineer and ask him how to build a strong bridge and he responds with something like "Well, I always use steel bridges. I've driven by concrete bridges that were cracked and needed repairs, and I would never use a concrete bridge for a professional purpose." he'd lose his license. You'd expect the engineer to use, you know, numbers and stuff, not anecdotal observations of bridges. The professional opinion has to do with how to apply the numbers, not fundamentals like 100 year loads, material strength, etc. What you're arguing is that your personal observations are a perfectly good substitute for more rigorous study, and that's frankly ridiculous. In an immature field personal observations may be the best data available, but that's a weakness of the field rather than a desirable state. 200 years ago doctors operated the same way--I'm glad they abandoned that for a more rigorous approach. The interesting thing is, there was quite a disruption as quite a few of the more established doctors were really offended by the idea that their professional opinions would be replaced by standards of care based on large scale studies. Mike Stone ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Premature view materialization in 8.2?
"Jonathan Ellis" <[EMAIL PROTECTED]> writes: > It was in my original post unless it got clipped: Sorry, I had forgotten. > The problem seems to be that clan_members_v contains a call to an > expensive function: I'll bet that the function is marked VOLATILE. 8.2 is more conservative about optimizing away volatile functions than previous releases. If it has no side effects, mark it STABLE (or can it even be IMMUTABLE?). In some quick testing, I verified that 8.2 does evaluate the function at the join level corresponding to the view's join (and I think this is preventing it from considering other join orders, too). If you change the function's marking to be nonvolatile then the function disappears from the plan entirely, and also it seems to prefer joining "clans" sooner. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SCSI vs SATA
At 02:19 PM 4/6/2007, Michael Stone wrote: On Fri, Apr 06, 2007 at 12:41:25PM -0400, Ron wrote: 3.based on personal observation, case study reports, or random investigations rather than systematic scientific evaluation: anecdotal evidence. Here you even quote the appropriate definition before ignoring it. In short, professional advice and opinions are supposed to be considerably more rigorous and analytical than anything "anecdotal". The alternative is "malpractice". In any profession where malpractice is applicable, the profession opinion had better be backed up by research rather than anecdote. I'm not aware of any profession held to a "malpractice" standard which is based on personal observation and random investigation rather than formal methods. Talk to every Professional Engineer who's passed both rounds of the Professional Engineering Exams. While there's a significant improvement in quality when comparing a formal study to professional advice, there should be an equally large improvement when comparing professional advice to random anecdotal evidence. If there isn't, the professional isn't worth paying for. ...and you =can= be successfully sued for giving bad professional advice. studies. I respect that. Unfortunately the RW is too fast moving and too messy to wait for a laboratory style study to be completed before we are called on to make professional decisions on most issues we face within our work IME I have to serve my customers in a timely fashion that for the most part prohibits me from waiting for the perfect experiment's outcome. Which is what distinguishes your field from a field such as engineering or medicine, and which is why waving the term "malpractice" around is just plain silly. Ok, since you know I am an engineer that crossed a professional line in terms of insult. That finishes this conversation. ...and you know very well that the use of the term "malpractice" was not in the legal sense but in the strict dictionary sense: "mal, meaning bad" "practice, meaning "professional practice." ...and unless you've been an academic your entire career you know the time pressures of the RW of business. And claiming to have to wait for perfection is a red herring. Did you record the numbers of disks involved (failed & nonfailed), the models, the environmental conditions, the power on hours, etc.? That's what would distinguish anecdote from systematic study. Yes, as a matter of fact I =do= keep such maintenance records for operations centers I've been responsible for. Unfortunately, that is not nearly enough to qualify for being "objective". Especially since it is not often possible to keep accurate track of every one might want to. Even your incomplete list. Looks like you might not have ever =done= some of the studies you tout so much. Agreed. OTOH, there's not supposed to be anything casual, ill-considered, or low quality about professionals giving professional opinions within their fields of expertise. Whether numbers are explicitly involved or not. If I go to an engineer and ask him how to build a strong bridge and he responds with something like "Well, I always use steel bridges. I've driven by concrete bridges that were cracked and needed repairs, and I would never use a concrete bridge for a professional purpose." he'd lose his license. You'd expect the engineer to use, you know, numbers and stuff, not anecdotal observations of bridges. The professional opinion has to do with how to apply the numbers, not fundamentals like 100 year loads, material strength, etc. ..and I referenced this as the knowledge base a professional uses to render opinions and give advice. That's far better than anecdote, but far worse than specific study. The history of bridge building is in fact a perfect example for this phenomenon. There are a number of good books on this topic both specific to bridges and for other engineering projects that failed due to mistakes in extrapolation. What you're arguing is that your personal observations are a perfectly good substitute for more rigorous study, Of course I'm not! and IMHO you know I'm not. Insult number two. Go settle down. and that's frankly ridiculous. Of course it would be. The =point=, which you seem to just refuse to consider, is that there is a valid degree of evidence between "anecdote" and "data from proper objective study". There has to be for all sorts of reasons. As I'm sure you know, the world is not binary. In an immature field personal observations may be the best data available, but that's a weakness of the field rather than a desirable state. 200 years ago doctors operated the same way--I'm glad they abandoned that for a more rigorous approach. The interesting thing is, there was quite a disruption as quite a few of the more established doctors were really offended by the idea that their professional opinions would be replaced
Re: [PERFORM] Premature view materialization in 8.2?
On 4/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: > The problem seems to be that clan_members_v contains a call to an > expensive function: I'll bet that the function is marked VOLATILE. 8.2 is more conservative about optimizing away volatile functions than previous releases. If it has no side effects, mark it STABLE (or can it even be IMMUTABLE?). That's exactly right, it should have been STABLE. Thanks a lot for figuring that out for me! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] postgres 8.2 seems to prefer Seq Scan
One more anomaly between 7.4 and 8.2. DB dumped from 7.4 and loaded
onto 8.2, both have locale set to C. 8.2 seems to prefer Seq Scans
for the first query while the ordering in the second query seems to
perform worse on 8.2. I ran analyze. I've tried with the encoding
set to UTF-8 and SQL_ASCII; same numbers and plans. Any ideas how to
improve this?
Thanks,
Alex
postgres 7.4
EXPLAIN ANALYZE select pnum, event_pid, code_name, code_description,
code_mcam, event_date, effective_date, ref_country,
ref_country_legal_code, corresponding_pnum, withdrawal_date,
payment_date, extension_date, fee_payment_year, requester, free_form
from code inner join event on code_pid = code_pid_fk where pnum
='AB5819188';
QUERY PLAN
-
Nested Loop (cost=0.00..60.87 rows=19 width=231) (actual
time=0.065..0.065 rows=0 loops=1)
-> Index Scan using pnum_idx on event (cost=0.00..3.37 rows=19
width=172) (actual time=0.063..0.063 rows=0 loops=1)
Index Cond: ((pnum)::text = 'AB5819188'::text)
-> Index Scan using code_pkey on code (cost=0.00..3.01 rows=1
width=67) (never executed)
Index Cond: (code.code_pid = "outer".code_pid_fk)
Total runtime: 0.242 ms
(6 rows)
postgres 8.2
EXPLAIN ANALYZE select pnum, event_pid, code_name, code_description,
code_mcam, event_date, effective_date, ref_country,
ref_country_legal_code, corresponding_pnum, withdrawal_date,
payment_date, extension_date, fee_payment_year, requester, free_form
from code inner join event on code_pid = code_pid_fk where pnum
='AB5819188';
QUERY PLAN
--
Hash Join (cost=106.91..3283.46 rows=1779 width=230) (actual
time=10.383..10.390 rows=1 loops=1)
Hash Cond: (event.code_pid_fk = code.code_pid)
-> Index Scan using pnum_idx on event (cost=0.00..3147.63
rows=1779 width=171) (actual time=0.030..0.033 rows=1 loops=1)
Index Cond: ((pnum)::text = 'AB5819188'::text)
-> Hash (cost=70.85..70.85 rows=2885 width=67) (actual
time=10.329..10.329 rows=2885 loops=1)
-> Seq Scan on code (cost=0.00..70.85 rows=2885 width=67)
(actual time=0.013..4.805 rows=2885 loops=1)
Total runtime: 10.490 ms
(7 rows)
postgres 7.4
EXPLAIN ANALYZE select e.pnum, c.code_description, c.code_mcam,
e.event_pid from event e, code c where c.code_name =
e.ref_country_legal_code and c.code_country = e.ref_country and e.pnum
= 'AB5819188';
QUERY PLAN
--
Hash Join (cost=3.47..106.28 rows=1 width=73) (actual
time=7.795..7.795 rows=0 loops=1)
Hash Cond: ((("outer".code_name)::text =
("inner".ref_country_legal_code)::text) AND
(("outer".code_country)::text = ("inner".ref_country)::text))
-> Seq Scan on code c (cost=0.00..63.92 rows=2592 width=69)
(actual time=0.010..3.881 rows=2592 loops=1)
-> Hash (cost=3.37..3.37 rows=19 width=30) (actual
time=0.064..0.064 rows=0 loops=1)
-> Index Scan using pnum_idx on event e (cost=0.00..3.37
rows=19 width=30) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: ((pnum)::text = 'AB5819188'::text)
Total runtime: 7.947 ms
(7 rows)
postgres 8.2
EXPLAIN ANALYZE select e.pnum, c.code_description, c.code_mcam,
e.event_pid from event e, code c where c.code_name =
e.ref_country_legal_code and c.code_country = e.ref_country and e.pnum
= 'AB5819188';
QUERY PLAN
---
Hash Join (cost=114.12..3368.51 rows=1 width=73) (actual
time=10.900..10.900 rows=0 loops=1)
Hash Cond: (((e.ref_country_legal_code)::text =
(c.code_name)::text) AND ((e.ref_country)::text =
(c.code_country)::text))
-> Index Scan using pnum_idx on event e (cost=0.00..3147.63
rows=1779 width=30) (actual time=0.027..0.031 rows=1 loops=1)
Index Cond: ((pnum)::text = 'AB5819188'::text)
-> Hash (cost=70.85..70.85 rows=2885 width=69) (actual
time=10.838..10.838 rows=2885 loops=1)
-> Seq Scan on code c (cost=0.00..70.85 rows=2885 width=69)
(actual time=0.011..4.863 rows=2885 loops=1)
Total runtime: 11.018 ms
(7 rows)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] SCSI vs SATA
On Fri, 6 Apr 2007, Scott Marlowe wrote: Most server drives are rated for 55-60C environmental temperature operation, which means the drive would be even hotter. I chuckled when I dug into the details for the drives in my cheap PC; the consumer drives from Seagate: http://www.seagate.com/docs/pdf/datasheet/disc/ds_barracuda_7200_10.pdf are rated to a higher operating temperature than their enterprise drives: http://www.seagate.com/docs/pdf/datasheet/disc/ds_barracuda_es.pdf They actually have an interesting white paper on this subject. The factor they talk about that isn't addressed in the studies we've been discussing is the I/O workload of the drive: http://www.seagate.com/content/pdf/whitepaper/TP555_BarracudaES_Jun06.pdf What kind of sticks out when I compare all their data is that the chart in the white paper puts the failure rate (AFR) of their consumer drives at almost 0.6%, yet the specs on the consumer drive quote 0.34%. Going back to the original question here, though, the rates are all similar and small enough that I'd take many more drives over a small number of slightly more reliable ones any day. As long as you have a controller that can support multiple hot-spares you should be way ahead. I get more concerned about battery backup cache issues than this nowadays (been through too many extended power outages in the last few years). I do think that server grade drive tech has been migrating into the consumer realm over time. I can imagine that today's high performance game / home systems with their heat generating video cards and tendency towards RAID1 / RAID0 drive setups are pushing the drive manufacturers to improve reliability of consumer disk drives. The introduction of fluid dynamic motor bearings into the hard drive market over the last few years (ramping up around 2003) has very much transformed the nature of that very temperature sensitive mechanism. That's the cause of why a lot of rules of thumb from before that era don't apply as strongly to modern drives. Certainly that fact that today's consumer processors produce massively more heat than those of even a few years ago has contributed to drive manufacturers moving their specs upwards as well. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] SCSI vs SATA
On Fri, Apr 06, 2007 at 03:37:08PM -0400, Ron wrote: studies. I respect that. Unfortunately the RW is too fast moving and too messy to wait for a laboratory style study to be completed before we are called on to make professional decisions on most issues we face within our work IME I have to serve my customers in a timely fashion that for the most part prohibits me from waiting for the perfect experiment's outcome. Which is what distinguishes your field from a field such as engineering or medicine, and which is why waving the term "malpractice" around is just plain silly. Ok, since you know I am an engineer that crossed a professional line in terms of insult. That finishes this conversation. Actually, I don't know what you are. I obviously should have been more specific that the field I was refering to is computer systems integration, which isn't a licensed engineering profession in any jurisdiction that I'm aware of. ...and you know very well that the use of the term "malpractice" was not in the legal sense but in the strict dictionary sense: "mal, meaning bad" "practice, meaning "professional practice." That's the literal definition or etymology; the dictionary definition will generally include terms like "negligence", "established rules", etc., implying that there is an established, objective standard. I just don't think that hard disk choice (or anything else about designing a hardware & software system) can be argued to have an established standard best practice. Heck, you probably can't even say "I did that sucessfully last year, we can just implement the same solution" because in this industry you probably couldn't buy the same parts (exagerrating only somewhat). And claiming to have to wait for perfection is a red herring. Did you record the numbers of disks involved (failed & nonfailed), the models, the environmental conditions, the power on hours, etc.? That's what would distinguish anecdote from systematic study. Yes, as a matter of fact I =do= keep such maintenance records for operations centers I've been responsible for. Great! If you presented those numbers along with some context the data could be assessed to form some kind of rational conclusion. But to remind you of what you'd offered up to the time I suggested that you were offering anecdotal evidence in response to a request for statistical evidence: OTOH, I've spent my career being as careful as possible to as much as possible run HW within manufacturer's suggested specifications. I've been chided for it over the years... ...usually by folks who "save" money by buying commodity HDs for big RAID farms in NOCs or push their environmental envelope or push their usage envelope or ... ...and then act surprised when they have so much more down time and HW replacements than I do. All I can tell you is that I've gotten to eat my holiday dinner far more often than than my counterparts who push it in that fashion. I don't know how to describe that other than as anecdotal. You seem to be interpreting the term "anecdotal" as pejorative rather than descriptive. It's not anecdotal because I question your ability or any other such personal factor, it's anecdotal because if your answer to the question is "in my professional opinion, A" and someone else says "in my professional opinion, !A", we really haven't gotten any hard data to synthesize a rational opinion. Mike Stone ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] postgres 8.2 seems to prefer Seq Scan
On Fri, Apr 06, 2007 at 04:38:33PM -0400, Alex Deucher wrote: > One more anomaly between 7.4 and 8.2. DB dumped from 7.4 and loaded > onto 8.2, both have locale set to C. 8.2 seems to prefer Seq Scans > for the first query while the ordering in the second query seems to > perform worse on 8.2. I ran analyze. I've tried with the encoding > set to UTF-8 and SQL_ASCII; same numbers and plans. Any ideas how to > improve this? Are you sure the data sets are identical? The 7.4 query returned 0 rows; the 8.2 query returned 1 row. If you're running the same query against the same data in both versions then at least one of them appears to be returning the wrong result. Exactly which versions of 7.4 and 8.2 are you running? Have you analyzed all tables in both versions? The row count estimate in 7.4 is much closer to reality than in 8.2: 7.4 > -> Index Scan using pnum_idx on event (cost=0.00..3.37 rows=19 > width=172) (actual time=0.063..0.063 rows=0 loops=1) > Index Cond: ((pnum)::text = 'AB5819188'::text) 8.2 > -> Index Scan using pnum_idx on event (cost=0.00..3147.63 > rows=1779 width=171) (actual time=0.030..0.033 rows=1 loops=1) > Index Cond: ((pnum)::text = 'AB5819188'::text) If analyzing the event table doesn't improve the row count estimate then try increasing the statistics target for event.pnum and analyzing again. Example: ALTER TABLE event ALTER pnum SET STATISTICS 100; ANALYZE event; You can set the statistics target as high as 1000 to get more accurate results at the cost of longer ANALYZE times. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] postgres 8.2 seems to prefer Seq Scan
On 4/6/07, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Apr 06, 2007 at 04:38:33PM -0400, Alex Deucher wrote: > One more anomaly between 7.4 and 8.2. DB dumped from 7.4 and loaded > onto 8.2, both have locale set to C. 8.2 seems to prefer Seq Scans > for the first query while the ordering in the second query seems to > perform worse on 8.2. I ran analyze. I've tried with the encoding > set to UTF-8 and SQL_ASCII; same numbers and plans. Any ideas how to > improve this? Are you sure the data sets are identical? The 7.4 query returned 0 rows; the 8.2 query returned 1 row. If you're running the same query against the same data in both versions then at least one of them appears to be returning the wrong result. Exactly which versions of 7.4 and 8.2 are you running? They should be although it's possible one of my co-workers updated one of the DB's since I last dumped it, but should be a negligible amount of data. Not sure of the exact version of 7.4; psql just says: psql --version psql (PostgreSQL) 7.4 contains support for command-line editing 8.2 is 8.2.3 Have you analyzed all tables in both versions? The row count estimate in 7.4 is much closer to reality than in 8.2: Yes. 7.4 > -> Index Scan using pnum_idx on event (cost=0.00..3.37 rows=19 > width=172) (actual time=0.063..0.063 rows=0 loops=1) > Index Cond: ((pnum)::text = 'AB5819188'::text) 8.2 > -> Index Scan using pnum_idx on event (cost=0.00..3147.63 > rows=1779 width=171) (actual time=0.030..0.033 rows=1 loops=1) > Index Cond: ((pnum)::text = 'AB5819188'::text) If analyzing the event table doesn't improve the row count estimate then try increasing the statistics target for event.pnum and analyzing again. Example: ALTER TABLE event ALTER pnum SET STATISTICS 100; ANALYZE event; You can set the statistics target as high as 1000 to get more accurate results at the cost of longer ANALYZE times. Thanks! I'll give that a try and report back. Alex ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] SCSI vs SATA
On Fri, 6 Apr 2007, Scott Marlowe wrote: Based on experience I think that on average server drives are more reliable than consumer grade drives, and can take more punishment. this I am not sure about But, the variables of manufacturer, model, and the batch often make even more difference than grade. this I will agree with fully. David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SCSI vs SATA
On Fri, 6 Apr 2007, [EMAIL PROTECTED] wrote: On Fri, 6 Apr 2007, Scott Marlowe wrote: Based on experience I think that on average server drives are more reliable than consumer grade drives, and can take more punishment. this I am not sure about I think they should survey Tivo owners next time. Perfect stress-testing environment. Mine runs at over 50C most of the time, and it's writing 2 video streams 24/7. What more could you do to punish a drive? :) Charles David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SCSI vs SATA
On Fri, 6 Apr 2007, Charles Sprickman wrote: On Fri, 6 Apr 2007, [EMAIL PROTECTED] wrote: On Fri, 6 Apr 2007, Scott Marlowe wrote: > Based on experience I think that on average server drives are more > reliable than consumer grade drives, and can take more punishment. this I am not sure about I think they should survey Tivo owners next time. Perfect stress-testing environment. Mine runs at over 50C most of the time, and it's writing 2 video streams 24/7. What more could you do to punish a drive? :) and the drives that are in them are consumer IDE drives. I will admit that I've removed to cover from my tivo to allow it to run cooler, and I'm still on the origional drive + 100G drive I purchased way back when (7+ years ago) before I removed the cover I did have times when the tivo would die from the heat (Los Angeles area in the summer with no A/C) David Lang ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] SCSI vs SATA
* Charles Sprickman <[EMAIL PROTECTED]> [070407 00:49]: > On Fri, 6 Apr 2007, [EMAIL PROTECTED] wrote: > > >On Fri, 6 Apr 2007, Scott Marlowe wrote: > > > >>Based on experience I think that on average server drives are more > >>reliable than consumer grade drives, and can take more punishment. > > > >this I am not sure about > > I think they should survey Tivo owners next time. > > Perfect stress-testing environment. Mine runs at over 50C most of the time, > and it's writing 2 video streams 24/7. What more could you do to punish a > drive? :) Well, there is one thing, actually what my dreambox does ;) -) read/write 2 streams at the same time. (which means quite a bit of seeking under pressure) -) and even worse, standby and sleep states. And powering up the drive when needed. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SCSI vs SATA
Ron wrote: I read them as soon as they were available. Then I shrugged and noted YMMV to myself. 1= Those studies are valid for =those= users under =those= users' circumstances in =those= users' environments. How well do those circumstances and environments mimic anyone else's? Exactly, understanding whether the studies are applicable to you is the critical step - before acting on their conclusions! Thanks Ron, for the thoughtful analysis on this topic! Cheers Mark ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SCSI vs SATA
In summary, it seems one of these is true: 1. Drive manufacturers don't design server drives to be more reliable than consumer drive 2. Drive manufacturers _do_ design server drives to be more reliable than consumer drive, but the design doesn't yield significantly better reliability. 3. Server drives are significantly more reliable than consumer drives. --- Scott Marlowe wrote: > On Thu, 2007-04-05 at 23:37, Greg Smith wrote: > > On Thu, 5 Apr 2007, Scott Marlowe wrote: > > > > > On Thu, 2007-04-05 at 14:30, James Mansion wrote: > > >> Can you cite any statistical evidence for this? > > > Logic? > > > > OK, everyone who hasn't already needs to read the Google and CMU papers. > > I'll even provide links for you: > > > > http://www.cs.cmu.edu/~bianca/fast07.pdf > > http://labs.google.com/papers/disk_failures.pdf > > > > There are several things their data suggests that are completely at odds > > with the lore suggested by traditional logic-based thinking in this area. > > Section 3.4 of Google's paper basically disproves that "mechanical devices > > have decreasing MTBF when run in hotter environments" applies to hard > > drives in the normal range they're operated in. > > On the google: > > The google study ONLY looked at consumer grade drives. It did not > compare them to server class drives. > > This is only true when the temperature is fairly low. Note that the > drive temperatures in the google study are <=55C. If the drive temp is > below 55C, then the environment, by extension, must be lower than that > by some fair bit, likely 10-15C, since the drive is a heat source, and > the environment the heat sink. So, the environment here is likely in > the 35C range. > > Most server drives are rated for 55-60C environmental temperature > operation, which means the drive would be even hotter. > > As for the CMU study: > > It didn't expressly compare server to consumer grade hard drives. > Remember, there are server class SATA drives, and there were (once upon > a time) consumer class SCSI drives. If they had separated out the > drives by server / consumer grade I think the study would have been more > interesting. But we just don't know from that study. > > Personal Experience: > > In my last job we had three very large storage arrays (big black > refrigerator looking boxes, you know the kind.) Each one had somewhere > in the range of 150 or so drives in it. The first two we purchased were > based on 9Gig server class SCSI drives. The third, and newer one, was > based on commodity IDE drives. I'm not sure of the size, but I believe > they were somewhere around 20Gigs or so. So, this was 5 or so years > ago, not recently. > > We had a cooling failure in our hosting center, and the internal > temperature of the data center rose to about 110F to 120F (43C to 48C). > We ran at that temperature for about 12 hours, before we got a > refrigerator on a flatbed brought in (btw, I highly recommend Aggreko if > you need large scale portable air conditioners or generators) to cool > things down. > > In the months that followed the drives in the IDE based storage array > failed by the dozens. We eventually replaced ALL the drives in that > storage array because of the failure rate. The SCSI based arrays had a > few extra drives fail than usual, but nothing too shocking. > > Now, maybe now Seagate et. al. are making their consumer grade drives > from yesterday's server grade technology, but 5 or 6 years ago that was > not the case from what I saw. > > > Your comments about > > server hard drives being rated to higher temperatures is helpful, but > > conclusions drawn from just thinking about something I don't trust when > > they conflict with statistics to the contrary. > > Actually, as I looked up some more data on this, I found it interesting > that 5 to 10 years ago, consumer grade drives were rated for 35C > environments, while today consumer grade drives seem to be rated to 55C > or 60C. Same as server drives were 5 to 10 years ago. I do think that > server grade drive tech has been migrating into the consumer realm over > time. I can imagine that today's high performance game / home systems > with their heat generating video cards and tendency towards RAID1 / > RAID0 drive setups are pushing the drive manufacturers to improve > reliability of consumer disk drives. > > > The main thing I wish they'd published is breaking some of the statistics > > down by drive manufacturer. For example, they suggest a significant > > number of drive failures were not predicted by SMART. I've seen plenty of > > drives where the SMART reporting was spotty at best (yes, I'm talking > > about you, Maxtor) and wouldn't be surprised that they were quiet right up > > to their bitter (and frequent) end. I'm not sure how that factor may have > > skewed this particular bit of data. > >
