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@xxxxxxxxxxxxxxx> wrote:
Eduardo Piombino wrote:I'd suggest http://www.hdtune.com/ as a better way to test transfer speed here across the drive(s).
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 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@xxxxxxxxxxxxxxx www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
