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

I will give it a try, thanks.

However, besides all the analysis and tests and stats that I've been
collecting, I think the point of discussion turned into if my hardware is
good enough, and if it can keep up with the needs in normal, or even
heaviest users load. And if that is the question, the answer would be yes,
it does. The whole system performs outstandingly well under the maximum
stress users can ever request.
Of course it could be better, and that of course would be fantastic, but I
have the feeling that in this setup, buying more hardware, replace parts,
etc, would be just a temporary fix (maybe temporary = forever in this
context). I'm not saying that I won't buy that battery for the card, no,
because that will greatly boost my performance for this kind of
administrative background tasks, but my point is that current hardware,
seems more than sufficient for current users needs.

What I'm trying to say is that:
I think pg is wasting resources, it could be very well taking advantage of,
if you guys just tell me get better hardware. I mean ... the IO subsystem is
obviously the bottleneck of my system. But most of the time it is on very
very light load, actually ALL of the time, unless I do some heavy background
processing like the original ALTER, or the procedure that updates 800.000
rows. What I would consider to be a great feature, would be able to tell
pgsql, that a certain operation, is not time critical, so that it does not
try to use all the available IO subsystem at once, but rather rationalize
its use. Maybe that's the operating system / disk controller / other system
component responsibility, and in this case it's others module fault, that it
turns out that a single process has full control over a shared resource.

The "whole system" failed to rationalize the IO subsystem use, and I agree
that it is not pgsql fault, at all.
But already knowing that the base system (i.e. components out of pg's
control, like OS, hardware, etc) may be "buggy" or that it can fail in
rationalizing the IO, maybe it would be nice to tell to whoever is
responsible for making use of the IO subsystem (pg_bg_writer?), to use it in
a moderately manner. That is ... This operation is not critical, please do
not trash my system because it is not necessary. Use all the delays you
would like, go slowly, please, I don't really care if you take a month. Or
at least, be aware of current status of the IO system. If it is being busy,
slow down, if it is free, speed up. Of course I won't care if it takes less
time to complete.

Today, one can rationalize use of CPU, with a simple pg_sleep() call.
It would be nice to have maybe an ALTER table option (for ALTERs) or an
option in the BEGIN transaction command, that would let me say:
BEGIN SLOW TRANSACTION;
or BEGIN TRANSACTION RATIONALIZE_IO;
indicating that all the IO operations that are going to be performed in this
transaction, are not time critical, and thus, there is no need to put the
system in risk of a IO storm, just for a silly set of updates, that no one
is waiting for.

So if that feature was available, there would be no need for me (or maybe,
thousands of pg users), to upgrade hardware just to be able to perform a
single, unrelated, operation. I mean, the hardware is there, and is working
pretty good. If I could just tell pg that I don't care if an operation takes
all the time in the world, I think that would be awesome, and it would be
making the MOST of every possible hardware configuration.

I truly love pg. I just feel that something is not quite right the moment I
am required to upgrade my hardware, knowing that at any given time, I have
90% of the IO subsystem idle, that could be very well used in a better
fashion, and now would be completely wasted.

Well thank you, just some thoughts. And if the idea of a RATIONALIZED
transaction picks up, I would be more than glad to help implement it or to
help in any other way I can.

Best regards,
Eduardo.

On Fri, Jan 15, 2010 at 7:32 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Eduardo Piombino wrote:
>
>> Going to the disk properties (in windows), I just realized it does not
>> have the Write Cache enabled, and it doesn't also allow me to set it up.
>> I've read in google that the lack of ability to turn it on (that is, that
>> the checkbox remains checked after you apply the changes), has to do with
>> the lack of batter backup in the controller (which is default bundle option
>> for embedded EA-200, which is our case).
>>
>> Regarding actual disk performance, I did some silly tests:
>> Copied a 496 Mbytes file from a folder to another folder in C: and it took
>> almost 90 secs.
>> That would be 496MB/90 sec = 5.51MB/sec
>>
>>
> I'd suggest http://www.hdtune.com/ as a better way to test transfer speed
> here across the drive(s).
>
> I think you'll find that your server continues to underperform expectations
> until you get the battery installed that allows turning the write cache on.
> A quick look at HP's literature suggests they believe you only need the
> battery to enable the write-cache if you're using RAID5. That's completely
> wrong for database use, where you will greatly benefit from it regardless of
> underlying RAID setup. If you've got an EA-200 but don't have a battery for
> it to unlock all the features, you're unlikely to find a more cost effect
> way to improve your system than to buy one.
>
>
> --
> Greg Smith 2ndQuadrant Baltimore, MD
> PostgreSQL Training, Services and Support
> greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>