From: Eduardo Piombino <drakorg@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: a heavy duty operation on an "unused" table kills my server
Date: Thu, 14 Jan 2010 17:49:04 -0300
Message-ID: <e24c1d9d1001141249k6e2adf36o1932eab9def85532@mail.gmail.com> (raw)
In-Reply-To: <e24c1d9d1001131113x69d2f10anbee2c87dbd9856c4@mail.gmail.com>
References: <e24c1d9d1001122059ic5d4721o98ccf85033b1fead@mail.gmail.com>
<4B4D5698.8040808@emolecules.com>
<e24c1d9d1001122147l1c0e2111kce25dc2c7f393087@mail.gmail.com>
<4B4D61DA.7080008@postnewspapers.com.au>
<e24c1d9d1001122303g1d4eea48h18bd73467d1fa8ec@mail.gmail.com>
<603c8f071001130823l69804fe3m5c6fbb081c55c265@mail.gmail.com>
<4B4E14EB.7080008@2ndquadrant.com>
<e24c1d9d1001131113x69d2f10anbee2c87dbd9856c4@mail.gmail.com>
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.HTMLhttp://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
>>
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: drakorg@gmail.com, greg@2ndquadrant.com, robertmhaas@gmail.com
Subject: Re: a heavy duty operation on an "unused" table kills my server
In-Reply-To: <e24c1d9d1001141249k6e2adf36o1932eab9def85532@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox