Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D2FDE63281A for ; Thu, 14 Jan 2010 16:49:19 -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 71337-09 for ; Thu, 14 Jan 2010 20:49:09 +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 425B56325F4 for ; Thu, 14 Jan 2010 16:49:07 -0400 (AST) Received: by ewy5 with SMTP id 5so15478185ewy.34 for ; Thu, 14 Jan 2010 12:49:06 -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=UaIkLAvyZ/oPxmnsXSlEa+cBgnVf6ry6w2r5YYFyV2c=; b=U8FZip8HkVWFgoNKK+jUgB5F2kdrFmFNVB/KCtIPGK//TxDfaOMCttfDu+sFDa7QPo FFdhYBPdZhoYBhl5Gm9qBuwYsfk/TfS8ONRbXqvJUcNz1OeK9rzOjWNizvnOd6dWtcC1 b+Wv/m8lnkeRuipLs6yVMGeqwU3F25uyPF2cs= 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=drr0cqpsRZsDbBushUusFhMJNDbnCDeL6xUVz6V014QbVorr9KsvIsfUTdXFE253ze ol8QUqCSqnhOpeSp/VbRH3f97QV6jqM+4BEj7eXlHyquGv4wKFPQKDRaPiqCdUImQ+5v 8I8AZwiOakVazE1ksGdzGuW/TY3kKD/AVT7qQ= MIME-Version: 1.0 Received: by 10.213.100.131 with SMTP id y3mr1291929ebn.91.1263502145049; Thu, 14 Jan 2010 12:49:05 -0800 (PST) In-Reply-To: References: <4B4D5698.8040808@emolecules.com> <4B4D61DA.7080008@postnewspapers.com.au> <603c8f071001130823l69804fe3m5c6fbb081c55c265@mail.gmail.com> <4B4E14EB.7080008@2ndquadrant.com> Date: Thu, 14 Jan 2010 17:49:04 -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=001636c5a49a875be6047d260442 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.19 tagged_above=-10 required=5 tests=BAYES_50=0.001, FH_DATE_PAST_20XX=3.188, HTML_MESSAGE=0.001 X-Spam-Level: *** X-Archive-Number: 201001/308 X-Sequence-Number: 37188 --001636c5a49a875be6047d260442 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Regarding the hardware the system is running on: It's an HP Proliant DL-180 G5 server. Here are the specs... our actual configuration only has one CPU, and 16G of RAM. The model of the 2 disks I will post later today, when I get to the server. I was with many things, sorry. http://h18000.www1.hp.com/products/quickspecs/12903_na/12903_na.HTML http://h18004.www1.hp.com/products/quickspecs/DS_00126/DS_00126.pdf *At A Glance *The HP ProLiant DL180 G5 is a low cost high capacity storage optimized 2-way server that delivers on a history of design excellence and 2U density for a variety of rack deployments and applications. - Processors: - Supports up to two Quad-Core Intel=AE Xeon=AE processors: 5400 sequ= ence with 12MB Level 2 cache - Intel=AE 5100 Chipset - Memory: - Up to 32 GB of memory supported by six (6) PC2-5300 (667 MHz) DDR2 memory slots - Internal Drive Support: - Supports up to twelve via CTO with controller or up to eight via BT= O with the addition of a controller: - Hot Plug Serial ATA (SATA) 3.5"hard drives; or - Hot Plug Serial Attached SCSI (SAS) 3.5"hard drives *NOTE:* 4 hard drives are supported standard via BTO. 8 hard drive support requires the addition of a Smart Array or HBA controller. Hot Plug and SAS functionality require the addition of a Smart Array or HBA controller. 12 hard drive support available via CTO only and requires a SAS controller that supports expanders. - Internal storage capacity: - SATA Models: Up to 12.0TB (12 x 1TB Hot Plug 3.5" hard drives) - SAS Model: Up to 12.0TB (12 x 1TB Hot Plug 3.5" hard drives) - Network Controller: - One integrated NC105i PCI-e Gigabit NIC (embedded) (Wake on LAN and PXE capable) - Storage Controllers: - HP Embedded SATA RAID Controller (up to 4 hard drive support on standard BTO models) *NOTE:* Transfer rate 1.5 Gb/s SATA - Expansion Slots: - One available Low Profile x8 PCI-Express slot using a Low profile Riser. - Two Full Height/ Full Length Riser options - Option1: 2 full-length/full-height PCI-Express x8 connector slot= s (x4 electrical - Standard) - Option2: full-length/full-height riser with 2 PCI-X Slots(Optional) - Infrastructure Management: - Optional HP Lights Out 100c Remote Management card with Virtual KVM and Virtual Media support (includes IPMI2.0 and SMASH support) - USB Ports: - Seven USB ports (2) front, (4) rear, (1) internal - Optical Drive: - Support for one: - Optional Multi-bay DVD - Optional Floppy (USB only, USB key) - Power Supply: - 750W Power Supply (Optional Redundancy Hot Plug, Autoswitching) CSC= I 2007/8 - 1200W High Efficiency Power Supply (Optional Redundancy Hot Plug, Autoswitching) (Optional) CSCI 2007/8 - *NOTE:* Climate Savers Computing Initiative, 2007-2008 Compliant - Form Factor: - 2U rack models Regarding the SATA RAID controller, on the other spec pages it says that fo= r the 8 disks model (ours), it comes with a Smart Array E200. I will try to check out if we are using the original, since I recall hearing something about that our disks were SAS (Serial Attached SCSI), and I don't know if i= t is possible to connect those disks to embedded Smart Array E200 controller. Would it be possible? On Wed, Jan 13, 2010 at 4:13 PM, Eduardo Piombino wrote= : > 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 y= ou > 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. Crea= te >> a wide table and insert a few million records into it with generate_seri= es >> 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 wh= at >> changes when the troublesome ALTER TABLE shows up. >> >> >> -- >> Greg Smith 2ndQuadrant Baltimore, MD >> PostgreSQL Training, Services and Support >> greg@2ndQuadrant.com www.2ndQuadrant.com >> >> > --001636c5a49a875be6047d260442 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Regarding the hardware the system is running on:

It's an HP Pro= liant DL-180 G5 server.

Here are the specs... our actual configurati= on only has one CPU, and 16G of RAM.
The model of the 2 disks I will pos= t later today, when I get to the server.
I was with many things, sorry.

http://h18000.www1.hp.com/produ= cts/quickspecs/12903_na/12903_na.HTML
http://h18004.www1.hp.com= /products/quickspecs/DS_00126/DS_00126.pdf

At=20 A Glance =20 =20
The HP ProLiant=20 DL180 G5 is a low cost high capacity storage optimized 2-way server t= hat=20 delivers on a history of design excellence and 2U density for a varie= ty=20 of rack deployments and applications.=20
  • Processors= :=20
    • Supports up to two Quad-Core Intel=AE Xeon=AE processors:= =20 5400 sequence with 12MB Level 2 cache
    • Intel=AE 5100 C= hipset
  • Memory:=20
    • Up to 32 GB of memory supported by six (6) PC2-5300 (667 = MHz)=20 DDR2 memory slots
  • Internal Drive Support:=20
    • Supports up to twelve via CTO with controller or up to ei= ght via=20 BTO with the addition of a controller:=20
      • Hot Plug Serial ATA (SATA) 3.5"hard drives; or
      • Hot Plug Serial Attached SCSI (SAS) 3.5"hard drives
        NOTE: 4 hard dri= ves are=20 supported standard via BTO. 8 hard drive support requires= the=20 addition of a Smart Array or HBA controller. Hot Plug and= SAS=20 functionality require the addition of a Smart Array or HB= A controller.=20 12 hard drive support available via CTO only and requires= a=20 SAS controller that supports expanders.
    • Internal storage capacity:=20
      • SATA Models: Up to 12.0TB (12 x 1TB Hot Plug 3.5"= ; hard=20 drives)
      • SAS Model: Up to 12.0TB (12 x 1TB Hot Plu= g 3.5" hard=20 drives)
  • Network Controller:=20
    • One integrated NC105i PCI-e Gigabit NIC (embedded) (Wake = on LAN=20 and PXE capable)
  • Storage Controllers:=20
    • HP Embedded SATA RAID Controller (up to 4 hard drive supp= ort on=20 standard BTO models)
      NOTE: Transfer rate = 1.5 Gb/s=20 SATA
  • Expansion Slots:=20
    • One available Low Profile x8 PCI-Express slot using a Low= profile=20 Riser.
    • Two Full Height/ Full Length Riser options=20
      • Option1: 2 full-length/full-height PCI-Express x8 con= nector=20 slots (x4 electrical - Standard)
      • Option2: full-le= ngth/full-height riser with 2 PCI-X Slots(Optional)
  • Infrastructure Management:= =20
    • Optional HP Lights Out 100c Remote Management card with V= irtual=20 KVM and Virtual Media support (includes IPMI2.0 and SMASH sup= port)
  • USB Ports:=20
    • Seven USB ports (2) front, (4) rear, (1) internal
    • Optical Drive:=20
      • Support for one:=20
        • Optional Multi-bay DVD
        • Optional Floppy (USB = only, USB key)
    • Power Supply:=20
      • 750W Power Supply (Optional Redundancy Hot Plug, Autoswit= ching)=20 CSCI 2007/8
      • 1200W High Efficiency Power Supply (Optio= nal Redundancy Hot Plug,=20 Autoswitching) (Optional) CSCI 2007/8=20
        • NOTE: Climate Save= rs Computing=20 Initiative, 2007-2008 Compliant
    • Form Factor:=20
      • 2U rack models

    Regarding the SATA RAID controller, on the other spec= pages it says that for the 8 disks model (ours), it comes with a Smart Arr= ay E200. I will try to check out if we are using the original, since I reca= ll hearing something about that our disks were SAS (Serial Attached SCSI), = and I don't know if it is possible to connect those disks to embedded S= mart Array E200 controller. Would it be possible?

    On Wed, Jan 13, 2010 at 4:13 PM, Eduardo Pio= mbino <drakorg@gm= ail.com> wrote:
    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@2ndquadrant.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



    --001636c5a49a875be6047d260442--