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

Eduardo Piombino <drakorg@xxxxxxxxx> · Thu, 14 Jan 2010 17:49:04 -0300

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@xxxxxxxxx> 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx  www.2ndQuadrant.com