Re: a heavy duty operation on an "unused" table kills my server

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® Xeon® processors: 5400 sequence with 12MB Level 2 cache
  • Intel® 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 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 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 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 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 it 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 <drakorg@gmail.com> 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 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 <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.  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