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

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 generation
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 SATA
technology.
- x4 2.5G PCI Express host interface technology delivers high performance
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 <drakorg(at)gmail(dot)com> 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 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(at)gmail(dot)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(at)2ndquadrant(dot)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(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>>>
>>>
>>
>