Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7DBD26342F8 for ; Wed, 13 Jan 2010 03:06:32 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 02776-02-7 for ; Wed, 13 Jan 2010 07:06:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f213.google.com (mail-ew0-f213.google.com [209.85.219.213]) by mail.postgresql.org (Postfix) with ESMTP id 1334A634421 for ; Wed, 13 Jan 2010 03:03:06 -0400 (AST) Received: by ewy5 with SMTP id 5so13063105ewy.34 for ; Tue, 12 Jan 2010 23:03:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:content-type; bh=NUeccWASJC4A0HijQADCUAJ+cwOaIG1kvrkm6inia2A=; b=e6KCB5DAAeWKfhsTeZXo+J48Lng3BxJip+aiKZUG6Z+UEFx3P8hi/8FTKvto/aE6oU ZGrydcFUxhUX74r5vw+ynki9edHD33FBueJqOVbNbyxzKak/JPat6rHBlgvaXvu5oTnA yLiqfHpt9q00AT7ZGDDT3xuhS5YnmMT8Tzihw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=cUxoakrQ5L6n5DYBDNfPAKwTwid2wp87lCSZkAe23Ubgg37jpZJNAYxE3gZJ5lSMU4 kHHQpvz61qs0vaaKV7XzMLgBD5aC6bto635clq7ZyLsLH+8av3E/Irm6FBMOIYs1KJ6j tE/WvcCPsvulRJmR7TRK0JVfSWDVsEMH3GY5k= MIME-Version: 1.0 Received: by 10.216.91.81 with SMTP id g59mr467017wef.128.1263366184815; Tue, 12 Jan 2010 23:03:04 -0800 (PST) In-Reply-To: <4B4D61DA.7080008@postnewspapers.com.au> References: <4B4D5698.8040808@emolecules.com> <4B4D61DA.7080008@postnewspapers.com.au> Date: Wed, 13 Jan 2010 04:03:04 -0300 Message-ID: Subject: Re: a heavy duty operation on an "unused" table kills my server From: Eduardo Piombino To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d9a027aacfe1047d065cfc X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201001/243 X-Sequence-Number: 37123 --0016e6d9a027aacfe1047d065cfc Content-Type: text/plain; charset=ISO-8859-1 Excellent, lots of useful information in your message. I will follow your advices, and keep you posted on any progress. I have yet to confirm you with some technical details of my setup, but I'm pretty sure you hit the nail in any case. One last question, this IO issue I'm facing, do you think it is just a matter of RAID configuration speed, or a matter of queue gluttony (and not leaving time for other processes to get into the IO queue in a reasonable time)? Because if it was just a matter of speed, ok, with my actual RAID configuration lets say it takes 10 minutes to process the ALTER TABLE (leaving no space to other IOs until the ALTER TABLE is done), lets say then i put the fastest possible RAID setup, or even remove RAID for the sake of speed, and it completes in lets say again, 10 seconds (an unreal assumption). But if my table now grows 60 times, I would be facing the very same problem again, even with the best RAID configuration. The problem would seem to be in the way the OS (or hardware, or someone else, or all of them) is/are inserting the IO requests into the queue. What can I do to control the order in which these IO requests are finally entered into the queue? I mean .. what i would like to obtain is: Considering the ALTER TABLE as a sequence of 100.000 READ/WRITE OPERATIONS Considering the SELECT * FROM xxx as a sequence of 100 READ OPERATIONS (totally unrelated in disk) First i run the ALTER TABLE on a thread... Lets say by the time it generates 1.000 READ/WRITE OPERATIONS, the other thread starts with the SELECT * FROM xxx ... I would expect the IO system to give chance to the those 100 READ OPERATIONS to execute immediately (with no need to wait for the remaining 990.000 READ/WRITE OPERATIONS finish), that is, to enter the queue at *almost* the very same moment the IO request were issued. If I can not guarantee that, I'm kinda doomed, because the largest the amount of IO operations requested by a "heavy duty operation", the longest it will take any other thread to start doing anything. What cards do I have to manipulate the order the IO requests are entered into the "queue"? Can I disable this queue? Should I turn disk's IO operation caches off? Not use some specific disk/RAID vendor, for instance? I think I have some serious reading to do on this matter, google will help of course, but as always, every advice for small it may seem, will be very much appreciated. Nonetheless, thanks a lot for all the light you already brought me on this matter. I really appreciate it. Eduardo. On Wed, Jan 13, 2010 at 3:02 AM, Craig Ringer wrote: > On 13/01/2010 1:47 PM, Eduardo Piombino wrote: > >> I'm sorry. >> >> The server is a production server HP Proliant, I don't remember the >> exact model, but the key features were: >> 4 cores, over 2GHz each (I'm sorry I don't remember the actual specs), I >> think it had 16G of RAM (if that is possible?) >> It has two 320G disks in RAID (mirrored). >> > > Plain 'ol SATA disks in RAID-1? > > Hardware RAID (and if so, controller model)? With battery backup? Write > cache on or off? > > Or software RAID? If so, Windows build-in sw raid, or some vendor's > fakeraid (Highpoint, Promise, Adaptec, etc) ? > > Anyway, with two disks in RAID-1 I'm not surprised you're seeing some > performance issues with heavy writes, especially since it seems unlikely > that you have a BBU hardware RAID controller. In RAID-1 a write must hit > both disks, so a 1Mb write effectively costs twice as much as a 1Mb read. > Since many controllers try for high throughput (because it looks good in > benchmarks) at the expense of latency they also tend to try to batch writes > into long blocks, which keeps the disks busy in extended bursts. That > slaughters read latencies. > > I had this sort of issue with a 3Ware 8500-8, and landed up modifying and > recompiling the driver to reduce its built-in queue depth. I also increased > readahead. It was still pretty awful as I was working with RAID 5 on SATA > disks, but it made a big difference and more importantly meant that my Linux > server was able to honour `ionice' priorities and feed more important > requests to the controller first. > > On windows, I really don't know what to do about it beyond getting a better > I/O subsystem. Google may help - look into I/O priorities, queue depths, > reducing read latencies, etc. > > > I don't even have the emails with the specs here, but I can give you the >> exact configuration by tomorrow. >> >> Operating system: Windows 2003 server, with latest patches. >> Postgres version: 8.2.4, with all defaults, except DateStyle and TimeZone. >> > > Urk. 8.2 ? > > Pg on Windows improves a lot with each release, and that's an old buggy > version of 8.2 at that. Looking into an upgrade would be a really, REALLY > good idea. > > > Please try interpreting again my original mail, considering that when I >> said "high CPU usage" It might very well be "high IO usage". >> >> The final effect was that the server went non-responsive, for all >> matters, not even the TaskManager would come up when i hit CTRL-ALT-DEL, >> and of course, every client would suffer horrific (+20 secs) for the >> simplest operations like SELECT NOW(); >> > > That sounds a LOT like horrible read latencies caused by total I/O > overload. It could also be running out of memory and swapping heavily, so do > keep an eye out for that, but I wouldn't expect to see that with an ALTER > TABLE - especially on a 16GB server. > > / My question then is: is there a way to limit the CPU* or **IO >> USAGE* assigned to a specific connection?/ >> > > In win32 you can set CPU priorities manually in Task Manager, but only once > you already know the process ID of the Pg backend that's going to be > hammering the machine. Not helpful. > > I don't know of any way to do per-process I/O priorities in Win32, but I > only use win32 reluctantly and don't use it for anything I care about (like > a production Pg server) so I'm far from a definitive source. > > -- > Craig Ringer > --0016e6d9a027aacfe1047d065cfc Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Excellent, lots of useful information in your message.
I will follow you= r advices, and keep you posted on any progress. I have yet to confirm you w= ith some technical details of my setup, but I'm pretty sure you hit the= nail in any case.

One last question, this IO issue I'm facing, do you think it is jus= t a matter of RAID configuration speed, or a matter of queue gluttony (and = not leaving time for other processes to get into the IO queue in a reasonab= le time)?

Because if it was just a matter of speed, ok, with my actual RAID confi= guration lets say it takes 10 minutes to process the ALTER TABLE (leaving n= o space to other IOs until the ALTER TABLE is done), lets say then i put th= e fastest possible RAID setup, or even remove RAID for the sake of speed, a= nd it completes in lets say again, 10 seconds (an unreal assumption). But i= f my table now grows 60 times, I would be facing the very same problem agai= n, even with the best RAID configuration.

The problem would seem to be in the way the OS (or hardware, or someone= else, or all of them) is/are inserting the IO requests into the queue.
= What can I do to control the order in which these IO requests are finally e= ntered into the queue?
I mean .. what i would like to obtain is:

Considering the ALTER TABL= E as a sequence of 100.000 READ/WRITE OPERATIONS
Considering the SELECT = * FROM xxx as a sequence of 100 READ OPERATIONS (totally unrelated in disk)=

First i run the ALTER TABLE on a thread...
Lets say by the time it g= enerates 1.000 READ/WRITE OPERATIONS, the other thread starts with the SELE= CT * FROM xxx ...
I would expect the IO system to give chance to the tho= se 100 READ OPERATIONS to execute immediately (with no need to wait for the= remaining 990.000 READ/WRITE OPERATIONS finish), that is, to enter the que= ue at *almost* the very same moment the IO request were issued.

If I can not guarantee that, I'm kinda doomed, because the largest = the amount of IO operations requested by a "heavy duty operation"= , the longest it will take any other thread to start doing anything.

What cards do I have to manipulate the order the IO requests are entere= d into the "queue"?
Can I disable this queue?
Should I turn= disk's IO operation caches off?
Not use some specific disk/RAID=A0 = vendor, for instance?

I think I have some serious reading to do on this matter, google will h= elp of course, but as always, every advice for small it may seem, will be v= ery much appreciated.

Nonetheless, thanks a lot for all the light yo= u already brought me on this matter.
I really appreciate it.

Eduardo.



On Wed, Jan 13, 2010 at 3:02 AM, Craig Ringer <= craig@postnewspapers.com.au<= /a>> wrote:
On 13/01/2010 1:47 PM, Eduardo Piombino wrote:
I'm sorry.

The server is a production server HP Proliant, I don't remember the
exact model, but the key features were:
4 cores, over 2GHz each (I'm sorry I don't remember the actual spec= s), I
think it had 16G of RAM (if that is possible?)
It has two 320G disks in RAID (mirrored).

Plain 'ol SATA disks in RAID-1?

Hardware RAID (and if so, controller model)? With battery backup? Write cac= he on or off?

Or software RAID? If so, Windows build-in sw raid, or some vendor's fak= eraid (Highpoint, Promise, Adaptec, etc) ?

Anyway, with two disks in RAID-1 I'm not surprised you're seeing so= me performance issues with heavy writes, especially since it seems unlikely= that you have a BBU hardware RAID controller. In RAID-1 a write must hit b= oth disks, so a 1Mb write effectively costs twice as much as a 1Mb read. Si= nce many controllers try for high throughput (because it looks good in benc= hmarks) at the expense of latency they also tend to try to batch writes int= o long blocks, which keeps the disks busy in extended bursts. That slaughte= rs read latencies.

I had this sort of issue with a 3Ware 8500-8, and landed up modifying and r= ecompiling the driver to reduce its built-in queue depth. I also increased = readahead. It was still pretty awful as I was working with RAID 5 on SATA d= isks, but it made a big difference and more importantly meant that my Linux= server was able to honour `ionice' priorities and feed more important = requests to the controller first.

On windows, I really don't know what to do about it beyond getting a be= tter I/O subsystem. Google may help - look into I/O priorities, queue depth= s, reducing read latencies, etc.


I don't even have the emails with the specs here, but I can give you th= e
exact configuration by tomorrow.

Operating system: Windows 2003 server, with latest patches.
Postgres version: 8.2.4, with all defaults, except DateStyle and TimeZone.<= br>

Urk. 8.2 ?

Pg on Windows improves a lot with each release, and that's an old buggy= version of 8.2 at that. Looking into an upgrade would be a really, REALLY = good idea.


Please try interpreting again my original mail, considering that when I
said "high CPU usage" It might very well be "high IO usage&q= uot;.

The final effect was that the server went non-responsive, for all
matters, not even the TaskManager would come up when i hit CTRL-ALT-DEL, and of course, every client would suffer horrific (+20 secs) for the
simplest operations like SELECT NOW();

That sounds a LOT like horrible read latencies caused by total I/O overload= . It could also be running out of memory and swapping heavily, so do keep a= n eye out for that, but I wouldn't expect to see that with an ALTER TAB= LE - especially on a 16GB server.

=A0 =A0/ My question then is: is there a way to limit the CPU* or **IO
=A0 =A0USAGE* assigned to a specific connection?/

In win32 you can set CPU priorities manually in Task Manager, but only once= you already know the process ID of the Pg backend that's going to be h= ammering the machine. Not helpful.

I don't know of any way to do per-process I/O priorities in Win32, but = I only use win32 reluctantly and don't use it for anything I care about= (like a production Pg server) so I'm far from a definitive source.

--
Craig Ringer

--0016e6d9a027aacfe1047d065cfc--