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).
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. At any given time, the server is on 0% CPU load, with peaks of 1%, 2%, max. In normal operation. I've been digging a little in the archives, and one thing that it helped me come up with, is that I don't really remember seeing high CPU usage (fact that surprised me, but i do remember seeing high IO activity). I'm sorry, its pretty late here. I know this single statement is enough to almost change everything I've just asked. 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(); I've just made a little modification to my original questions, to extend to the possibility of a IO usage issue, instead of just CPU. * * > > *Bottom line is, I can't seem to do any heavy processing on the database > (or any operation that would require the server to enter into high CPU usage > **or IO USAGE), and still expect the server to behave normally. Whatever > heavy duty operation, DDL, DML, on whatever table (related, or unrelated), > on whatever thread, would tear down my servers integrity.* > > * My question then is: is there a way to limit the CPU or IO USAGEassigned to > a specific connection? > * > * I mean, I don't care if my ALTER TABLE takes 4 days instead of 4 hours.* > > * Something like:* > * pg_set_max_cpu _or_io_usage(2/100);* > On Wed, Jan 13, 2010 at 2:14 AM, Craig James <craig_ja...@emolecules.com>wrote: > Eduardo Piombino wrote: > >> Hi list, I'm having a problem when dealing with operations that asks too >> much CPU from the server. >> The scenario is this: >> > > A nice description below, but ... you give no information about your > system: number of CPUs, disk types and configuration, how much memory, what > have you changed in your Postgres configuration? And what operating system, > what version of Postgres, etc., etc. The more information you give, the > better the answer. > > If you're operating on a single disk with a tiny amount of memory, and old, > misconfigured Postgres on a laptop computer, that's a whole different > problem than if you're on a big sytem with 16 CPUs and a huge RAID 1+0 with > battery-backed cache. > Craig > > > >> I have a multithreaded server, each thread with its own connection to the >> database. Everything is working fine, actually great, actually >> outstandingly, in normal operation. >> >> I've a table named "a" with 1.8 million records, and growing, but I'm ok >> with it, at least for the moment. Maybe in the near future we will cut it >> down, backup old data, and free it up. But this is not the issue, as I said, >> everything is working great. I have a cpl of indexes to help some queries, >> and that's it. >> >> Now my problem started when I tried to do some model refactoring on this >> production table. >> >> First I tried a dumb approach. >> I connected from pgadmin, opened a new session. >> I tried an ALTER TABLE on this table just to turn a char(255) field into >> char(250), and it locked up my system. >> >> No surprise, since I had many threads waiting for this alter table to >> finish. What I did not foresee was that this alter table would take up so >> much time. Ok, my fault, for not having calculated the time that it would >> take the ALTER TABLE to complete. >> >> Now, with this experience, I tried a simple workaround. >> Created an empty version of "a" named "a_empty", identical in every sense. >> renamed "a" to "a_full", and "a_empty" to "a". This procedure costed me >> like 0 seconds of downtime, and everything kept working smoothly. Maybe a >> cpl of operations could have failed if they tried to write in the very >> second that there was actually no table named "a", but since the operation >> was transactional, the worst scenario was that if the operation should have >> failed, the client application would just inform of the error and ask the >> user for a retry. No big deal. >> >> Now, this table, that is totally unattached to the system in every way (no >> one references this table, its like a dumpster for old records), is not >> begin accessed by no other thread in the system, so an ALTER table on it, to >> turn a char(255) to char(250), should have no effect on the system. >> >> So, with this in mind, I tried the ALTER TABLE this time on the "a_full" >> (totally unrelated) table. >> The system went non-responsive again, and this time it had nothing to do >> with threads waiting for the alter table to complete. The pgAdmin GUI went >> non-responsive, as well as the application's server GUI, whose threads kept >> working on the background, but starting to take more and more time for every >> clients request (up to 25 seconds, which are just ridiculous and completely >> unacceptable in normal conditions). >> >> This resulted in my client applications to start disconnecting after their >> operations failed due to timeout, and the system basically went down again, >> from a users point of view. >> >> This time, since I saw no relation between my operation on a totally >> unrelated table, and the server BIG slowdown, I blamed the servers memory. >> >> After some tests, I came up to the conclusion that any heavy duty >> operation on any thread (ALTER TABLE on 1.8 million records tables, updates >> on this table, or an infinite loop, just to make my point), would affect the >> whole server. >> >> Bottom line is, I can't seem to do any heavy processing on the database >> (or any operation that would require the server to enter into high CPU usage >> *or IO USAGE*), and still expect the server to behave normally. Whatever >> heavy duty operation, DDL, DML, on whatever table (related, or unrelated), >> on whatever thread, would tear down my servers integrity. >> >> My question then is: is there a way to limit the CPU* or **IO USAGE* assigned >> to a specific connection? >> I mean, I don't care if my ALTER TABLE takes 4 days instead of 4 hours. >> >> Something like: >> pg_set_max_cpu*_or_io_*usage(2/100); >> >> and rest assured that no matter what that thread is asking the database to >> do, it just wont affect the other running threads. Obviosly, assuring that >> the process itself does not involve any locking of the other threads. >> >> Is something like that possible? >> >> Thanks in advance, >> Eduardo. >> >> >