Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 52ECD659F4C for ; Wed, 13 Jan 2010 15:26:50 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21265-02-3 for ; Wed, 13 Jan 2010 19:26:39 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f213.google.com (mail-ew0-f213.google.com [209.85.219.213]) by mail.postgresql.org (Postfix) with ESMTP id 83499658E88 for ; Wed, 13 Jan 2010 15:13:22 -0400 (AST) Received: by ewy5 with SMTP id 5so13897563ewy.34 for ; Wed, 13 Jan 2010 11:13:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=fUVuOa4ZX3V9CcT0gcaO9YBsE5RUHaRBLXqd6pwCSGA=; b=nY0BuK9mXdldzz5D22Qlfj+IvSP20Gi4vfd11Wfx2AnZmqVz/nw09Iqd2i94sswZKR CH75+ZXOJUND61d/C9AcxE2xu6dd+hFs/4TAtU7O8dNTn4FXDEC9IwxaeBJ66UmKvlGm fu/UV5zsI9NiQp6RGvdMFb885Jod6nvPwcBUo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=ct3mDaRd+xPmW+IA23CgJaOP4YCE36Thpkx0pcYiNIWXOOdUrDMzuYRV6DLsfJEQd4 8HlX8oBE7/bLC+iQLKbvsmMvOAbaC5yf6KyCe6PSneWSgvU8tRgzU6DJ5a/HLrGW03V9 L4THbGpDdcKZ0O4Xk1HzdDwtWTRncyXgL5l3E= MIME-Version: 1.0 Received: by 10.213.24.1 with SMTP id t1mr1858338ebb.22.1263410000348; Wed, 13 Jan 2010 11:13:20 -0800 (PST) In-Reply-To: <4B4E14EB.7080008@2ndquadrant.com> References: <4B4D5698.8040808@emolecules.com> <4B4D61DA.7080008@postnewspapers.com.au> <603c8f071001130823l69804fe3m5c6fbb081c55c265@mail.gmail.com> <4B4E14EB.7080008@2ndquadrant.com> Date: Wed, 13 Jan 2010 16:13:20 -0300 Message-ID: Subject: Re: a heavy duty operation on an "unused" table kills my server From: Eduardo Piombino To: Greg Smith Cc: Robert Haas , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0ce0d5d846cdb3047d10900e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.59 tagged_above=-10 required=5 tests=BAYES_00=-2.599, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/262 X-Sequence-Number: 37142 --000e0ce0d5d846cdb3047d10900e Content-Type: text/plain; charset=ISO-8859-1 Greg, I will post more detailed data as soon as I'm able to gather it. I was trying out if the cancellation of the ALTER cmd worked ok, I might give the ALTER another try, and see how much CPU, RAM and IO usage gets involved. I will be doing this monitoring with the process explorer from sysinternals, but I don't know how I can make it to log the results. Do you know any tool that you have used that can help me generate this evidence? I will google a little as soon as possible. On Wed, Jan 13, 2010 at 3:46 PM, Greg Smith wrote: > Robert Haas wrote: > >> I'm kind of surprised that there are disk I/O subsystems that are so >> bad that a single thread doing non-stop I/O can take down the whole >> server. Is that normal? Does it happen on non-Windows operating >> systems? What kind of hardware should I not buy to make sure this >> doesn't happen to me? >> >> > You can kill any hardware on any OS with the right abusive client. Create > a wide table and insert a few million records into it with generate_series > one day and watch what it does to queries trying to run in parallel with > that. > > I think the missing step here to nail down exactly what's happening on > Eduardo's system is that he should open up some of the Windows system > monitoring tools, look at both disk I/O and CPU usage, and then watch what > changes when the troublesome ALTER TABLE shows up. > > > -- > Greg Smith 2ndQuadrant Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.com > > --000e0ce0d5d846cdb3047d10900e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Greg, I will post more detailed data as soon as I'm able to gather it.<= br>
I was trying out if the cancellation of the ALTER cmd worked ok, I m= ight give the ALTER another try, and see how much CPU, RAM and IO usage get= s involved. I will be doing this monitoring with the process explorer from = sysinternals, but I don't know how I can make it to log the results. Do= you know any tool that you have used that can help me generate this eviden= ce? I will google a little as soon as possible.


On Wed, Jan 13, 2010 at 3:46 PM, Greg Sm= ith <greg@2ndq= uadrant.com> wrote:
Robert Haas wrote:
I'm kind of surprised that there are disk I/O subsystems that are so bad that a single thread doing non-stop I/O can take down the whole
server. =A0Is that normal? =A0Does it happen on non-Windows operating
systems? =A0What kind of hardware should I not buy to make sure this
doesn't happen to me?
=A0
You can kill any hardware on any OS with the right abusive client. =A0Creat= e a wide table and insert a few million records into it with generate_serie= s one day and watch what it does to queries trying to run in parallel with = that.

I think the missing step here to nail down exactly what's happening on = Eduardo's system is that he should open up some of the Windows system m= onitoring tools, look at both disk I/O and CPU usage, and then watch what c= hanges when the troublesome ALTER TABLE shows up.


--
Greg Smith =A0 =A02ndQuadrant =A0 Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com =A0www.2ndQuadrant.com


--000e0ce0d5d846cdb3047d10900e--