Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0AE716325F4 for ; Thu, 14 Jan 2010 17:01:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 74115-06 for ; Thu, 14 Jan 2010 21:01:32 +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 6622C6325DE for ; Thu, 14 Jan 2010 17:01:35 -0400 (AST) Received: by ewy5 with SMTP id 5so15493747ewy.34 for ; Thu, 14 Jan 2010 13:01:33 -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=t9zagvz1qotjkf0an2cSF5gSoG7LSImFS6YJi9FsxLc=; b=ULuNyQGIsM/n2ioQwa1gYYhGQJovSVxIdSBbcL9w1re3FRzTLsu4O3Rw1FQuYJa6KY AWFI5rzNKfbo15YFfdRKhq+4RVHZMP/bDkb7AfTV8ScK9/ayZyau8dNs6VacDehgufgs tDwm56UPrIsb/AR7aO2E5FgTheyO58J24gEnA= 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=F5k58/6hQHX3xOD0cAQNuD77zBD5KqCEzixcMprdq+/hIE9dmG+xtH2QF2NvP1O8Vc og9tmB1Ql8/HHq9IvkPujCyW8szVEccthMfUiZ7t5BEqGWPp47f0GYwbwrEvx4/yGuh2 JXX3HSXaKp+CpNfA2EBOOTAKmgkR7hmu0x2g0= MIME-Version: 1.0 Received: by 10.213.110.4 with SMTP id l4mr3090953ebp.81.1263502893403; Thu, 14 Jan 2010 13:01:33 -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 18:01:33 -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=001517447898224dce047d26312e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.298 tagged_above=-10 required=5 tests=AWL=-1.300, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/309 X-Sequence-Number: 37189 --001517447898224dce047d26312e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Regarding the EA-200 card, here are the specs. It seems it has support for SAS disks, so it is most probably that we are using the embedded/default controller. http://h18000.www1.hp.com/products/quickspecs/12460_div/12460_div.html http://h18000.www1.hp.com/products/quickspecs/12460_div/12460_div.pdf *Key Features * - Seamless upgrades from past generations and upgrades to next generatio= n HP high performance and high capacity Serial Attached SCSI Smart Array controllers. - 3G SAS technology delivers high performance and data bandwidth up to 300 MB\s per physical link and contains full compatibility with 1.5G SAT= A technology. - x4 2.5G PCI Express host interface technology delivers high performanc= e and data bandwidth up to 2 GB/s maximum bandwidth. - Addition of the battery backed cache upgrade enables BBWC, RAID 5, Capacity Expansion, RAID migration, and Stripe Size Migration. - Mix-and-match SAS and SATA hard drives, lets you deploy drive technology as needed to fit your computing environment. - Support for up to 2 TB in a single logical drive. - Software consistency among all Smart Array family products: Array Configuration Utility (ACU), Option ROM Configuration for Arrays (ORCA), Systems Insight Manager, Array Diagnostic Utility (ADU) and SmartStart. = Some of these features are not available with ProLiant 100 series platforms. - The SA-E200 controller supports up to 8 drives. The SA-E200i supports 2-8 drives depending on the server implementation. *Performance* HP's High Performance Architecture sets new boundaries of industry performance expectations! - 3Gb/s SAS (300MB/s bandwidth per physical link) - x8 3Gb/s SAS physical links (compatible with 1.5G SATA) - 64 MB or 128 MB DDR1-266 battery-backed cache provides up to 4.2 GB/s maximum bandwidth. - x4 2.5G PCI Express host interface provides 2 GB/s maximum bandwidth. - MIPS 32-bit Processor - Read ahead caching - Write-back caching (with battery-backed write cache upgrade) *Capacity * Given the increasing need for high performance and rapid capacity expansion= , the SA-E200 offers: - Up to 6TB of total storage with 6 x 1TB SATA MDL hard drives (3.5") *NOTE:* Support for greater than 2TB in a single logical drive. - Up to 2.4TB of total storage with 8 x 300GB SFF SAS hard drives On Thu, Jan 14, 2010 at 5:49 PM, Eduardo Piombino wrote= : > 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 serve= r. > 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 densi= ty > for a variety of rack deployments and applications. > > - Processors: > - Supports up to two Quad-Core Intel=AE Xeon=AE processors: 5400 > sequence with 12MB Level 2 cache > - Intel=AE 5100 Chipset > - Memory: > - Up to 32 GB of memory supported by six (6) PC2-5300 (667 MHz) DDR= 2 > memory slots > - Internal Drive Support: > - Supports up to twelve via CTO with controller or up to eight via > BTO 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 cont= roller. Hot > Plug and SAS functionality require the addition of a Smart Array= or HBA > controller. 12 hard drive support available via CTO only and req= uires 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 > slots (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) > CSCI 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 > for the 8 disks model (ours), it comes with a Smart Array E200. I will tr= y > to check out if we are using the original, since I recall hearing somethi= ng > about that our disks were SAS (Serial Attached SCSI), and I don't know if= it > is possible to connect those disks to embedded Smart Array E200 controlle= r. > Would it be possible? > > > On Wed, Jan 13, 2010 at 4:13 PM, Eduardo Piombino wrot= e: > >> 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 w= hat >>> changes when the troublesome ALTER TABLE shows up. >>> >>> >>> -- >>> Greg Smith 2ndQuadrant Baltimore, MD >>> PostgreSQL Training, Services and Support >>> greg@2ndQuadrant.com www.2ndQuadrant.com >>> >>> >> > --001517447898224dce047d26312e Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Regarding the EA-200 card, here are the specs.
It seems it has support f= or SAS disks, so it is most probably that we are using the embedded/default= controller.

http://h18000.www1.hp.com/products/quickspecs/1= 2460_div/12460_div.html
http://h18000.www1.hp.com/products/quickspecs/12460_div/12460_div.pd= f

Key Features

=20 =20
  • Seamless upgrades from past generations and upgrades to next = generation=20 HP high performance and high capacity Serial Attached SCSI Smart = Array=20 controllers.
  • 3G SAS technology delivers high performance= and data bandwidth up=20 to 300 MB\s per physical link and contains full compatibility wit= h 1.5G=20 SATA technology.
  • x4 2.5G PCI Express host interface tech= nology delivers high performance=20 and data bandwidth up to 2 GB/s maximum bandwidth.
  • Addit= ion of the battery backed cache upgrade enables BBWC, RAID 5,=20 Capacity Expansion, RAID migration, and Stripe Size Migration.
  • Mix-and-match SAS and SATA hard drives, lets you deploy drive techno= logy=20 as needed to fit your computing environment.
  • Support for= up to 2 TB in a single logical drive.
  • Software consistency among = all Smart Array family products: Array=20 Configuration Utility (ACU), Option ROM Configuration for Arrays = (ORCA),=20 Systems Insight Manager, Array Diagnostic Utility (ADU) and Smart= Start.=20 Some of these features are not available with ProLiant 100 series= platforms.=20
  • The SA-E200 controller supports up to 8 drives. The SA-E20= 0i supports=20 2-8 drives depending on the server implementation.

=
Performance
=20

HP's High Performance Architecture sets new boundaries of industry p= erformance=20 expectations!

  • 3Gb/s SAS (300MB/s bandwidth per physical link)
  • x8 3G= b/s SAS physical links (compatible with 1.5G SATA)
  • 64 MB or 128 MB = DDR1-266 battery-backed cache provides up to 4.2 GB/s=20 maximum bandwidth.
  • x4 2.5G PCI Express host interface pro= vides 2 GB/s maximum bandwidth.
  • MIPS 32-bit Processor
  • Read = ahead caching
  • Write-back caching (with battery-backed write cache u= pgrade)

Capacity
=20

Given the increasing need for high performance and rapid capacity expans= ion,=20 the SA-E200 offers:

  • Up to 6TB of total storage with 6 x 1TB SATA MDL hard drives = (3.5")=20
    NOTE: Support for greate= r than=20 2TB in a single logical drive.
  • Up to 2.4TB of tota= l storage with 8 x 300GB SFF SAS hard drives

On Thu, Jan 14, 2010 at 5:49 PM, Eduardo Piombino <drakorg@gmail.com> wrote:
Regarding the har= dware the system is running on:

It's an HP Proliant DL-180 G5 s= erver.

Here are the specs... our actual configuration only has one CPU, and 16= G of RAM.
The model of the 2 disks I will post later today, when I get t= o 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=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 drives 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 Savers 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@gmail.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




--001517447898224dce047d26312e--