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(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
>>
>>
>