How can I tell specific Postgres processes not to consume all CPU?

What is your operating system? Usermode apps do not cooperate in CPU scheduling.

Usual problem scenario is that you are out of memory and both programms attempt to use swap and that slows down rest of IO in system as it goes at maximum priority.
Uptime 10..100 can be observed at that time.
Solution - restrict program's memory usage like -server -Xmx for Java or use some pooling proxy for Postgresql do that less connection serving processes run routinely.

Hi,

You could make use of 'nice' which manipulates process priority.  The higher the nice value, the lower it's priority, the less nice it is (per se), the higher priority it will run with.

Simply give your postgresql a higher niceness than your webapp.

Man page:
http://linux.about.com/library/cmd/blcmdl1_nice.htm

Little article on using nice:
http://blogs.techrepublic.com.com/opensource/?p=140


HTH.

hardware is so cheap separate the db from the appserver and have two separate servers.

It looks like memory shortage firsthand. There is no danger running all apps on same system if it has good CPU scheduler, like Windows Advanced or any Linux.

Sorry for answering so late. We had another urgent task.

@gheist
Our operationg system is Ubuntu 8.04

@hfraser
I couldn't recreate the same situation with 100% CPU, but I have attached a snippet of vmstat of the next import.
You were right, the high values come from wa.
>>If the problem's I/O, there are many articles on tuning postgres for performance.
The only effect of tuning postgres is that the import would be faster. But what I'm aiming at, is that the import app must not consume so much ressources.

vmstat 1 3600
 
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  1 738440  42440   8232 1201496    0    0    64  5476  141 3544 21  1 76  2
 1  0 738440  40520   8232 1203800    0    0    64   312   76 3091 23  2 73  2
 1  0 738440  38048   8252 1206056    0    0    16  4496  124 3051 21  1 76  2
 1  0 738440  35568   8276 1208444    0    0   200  3400  132 2890 23  1 73  3
 3  0 738440  32748   8300 1211268    0    0    64     0   85 3534 22  1 75  2
 1  0 738436  29956   8324 1213688   32    0    92  3540  137 3053 20  2 75  3
 1  0 738436  27724   8336 1215968    0    0    96  3528  102 3221 21  1 75  3
 1  0 738436  24728   8352 1218808    0    0   112  3608  134 3572 21  1 76  2
 1  0 749908  49468   7656 1190776    0 6352   184  9756  131 2939 20  2 75  3
 1  0 749908  46632   7660 1193968    0    0   328    96   74 3633 23  2 74  1
 0  1 749908  44092   7672 1196404    0    0    56  5916  111 3116 23  1 75  2
 1  2 749844  29368   7712 1210096   60    0    64  6252  430 1854  6  2 57 35
 0  3 749844  29192   7712 1210100    0    0     0  2376  312 1561  2  1 45 52
 1  3 749844  29216   7712 1210100    0    0     0  2328  376 1877  5  1 35 59
 1  3 749844  29280   7712 1210100    0    0     0  2604  364 1621  5  1 37 57
 0  2 749844  28804   7712 1211104    0    0    16  2912  345 2249 15  1 40 44
 0  2 749844  28628   7712 1211116    0    0     0  2860  375 1738  5  0 56 38
 0  2 749844  28668   7712 1211116    0    0     0  3132  363 1761  2  0 53 44
 0  2 749844  28504   7712 1211116    0    0     0  2384  398 2207  1  0 56 43
 0  6 749844  25988   7756 1211072    0    0    84  2112  338 1561  1  1 44 53
 0  2 749844  25440   7776 1211340    0    0   280  2840  308 1501  1  1 48 50
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 0  2 749844  25488   7776 1211424    0    0     0  4064  367 1388  1  0 51 47
 0  2 749844  25364   7776 1211424    0    0     0  2404  387 1906  1  0 55 43
 0  2 749844  25364   7776 1211424    0    0     0  2452  358 1432  1  1 65 34
 0  2 749844  25364   7776 1211424    0    0     0  3116  324 1420  1  0 54 45
 1  0 749844  26268   7836 1212468    0    0   156  2012  295 2742  7  1 70 22
 1  0 794192  54208   7804 1159104    0 9800    72 14060  201 2999 19  3 67 11
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

It is swapping out in 13,33 and back in 13 get more RAM or limit app memory usage.
Ask if you need help.
You cannot force postgresql into RAM, nor it is advisable to do so with java, so limiting memory consumption to chips you have is only option.

@gheist
>>Usual problem scenario is that you are out of memory and both programms attempt to use swap and that slows down >>rest of IO in system as it goes at maximum priority.
>>Uptime 10..100 can be observed at that time.
I didn't figure out that point completely. How can I determine that the swaps are the problem?

@stephenhoeks&
If I would nice postgres I would also make my Wepapp slower, but I only want to nice the postgres processes which come from the import-app. Is there a possibility that postgres knows from which app the query comes and therefore consumes either more or less ressources. But looking at the posting of woolmilkporc, the nice-approach seems not to work anyway.

@earthman2
Yes hardware is cheap, but we are a small company and budget is low. But we've already planned that for the future.

@gheist
Here ist the memory situation. I know, that only 134 MB are left, but as I learned so far that the cached 918 MB is also available, so everything should be OK. Please correct me if I'm not right.

# free -m
            total       used       free     shared    buffers     cached
Mem:          3986       3851        134          0          1        918
-/+ buffers/cache:       2931       1055
Swap:         8197         85       8111

@gheist
I've attached the memory consumption on our server. Actually we have 3 wepapps, but that wasn't important so far.
So if I understand you right, I should reduce the Xmx values to say 600m, because I think I may not reduce the value under the RSS value. Looking at the VSZ value I wonder why that process may consume over 1 GB even if only 800 MB are allowed.

You said the problem is the swapping (line 13), but the the high wa-values are from line 17 to 25. Does that fit?

ps aux
 
root  9300  0.1  8.3 1092648 340020 ? Sl   Mar13  35:50 java -Xmx800m -server webapp1
root 28243  0.7 12.7 1103288 521024 ? Sl   Mar24  19:52 java -Xmx800m -server webapp2
root 32433  0.2 10.5 1099644 428740 ? Sl   Mar19  22:58 java -Xmx800m -server webapp3
 
root 31287 27.9 12.0 1290380 493676 pts/4  Sl+  13:04   4:41 java Import

                                              

1:
2:
3:
4:
5:
6:
7:

That fits perfectly. IO is counted and then swap pages are commited to disk and system is lazy while it waits for memory management operation to complete.
Java memory management is describet alongside -XX options. BTW is your system 32bit or 64bit?
Do you have any real need to exceed 256mb od -Xmx by overriding default value? Like OOM when running webapp.

What is your application server? Why you do not run all your "webapps" inside single server?

The 4 java processes together only appear to be using about 40% of the memory, and each appears to consuming about the same amount of memory, which to me doesn't look like any of them were swapped out. It's possible some other apps on the system were swappped that caused the "so" spike when the import started.

There's definitely a lot of wait state, presumably after the import begins, which isn't unexpected. You might want to check how the import's done, especially concerning locks on the table that might be there to speed the import.

Sorry. I didn't mean to suggest there isn't swapping happening, or that more memory won't help (it always does). But there are only two major swap events:  at 13 (swap out) and 33 (swap in). The "ps aux" command doesn't seem to indicate either of them related to his java apps, and the high I/O wait activity would indicate the system is waiting on disk, which I'd presume is the import process.reading and postgres reading/writing. It's highly likely that everything unrelated has been swapped out, but they might be bash, X, gnome/kde, etc. which won't affect his web apps.

It's also possible that the import process does something very intrusive, like table locks, to improve the update process speed, but at the expense of db clients.

It's all speculation, of course, since I don't know what got swapped out. Checking for processes with a RSZ of 0 and a high VSZ will show what got swapped out.

My point is just that there might be more than swapping affecting the web app's performance.

Having said that, I have a couple of machines that are pure app servers (one is a db server), and I always disable X to conserve memory (start them at runlevel 3). Disabling them for a test is certainly easier and quicker than re-writing the import code, and would be my first thing to try.

1000 writes/sec are normal disk interface ability.
You really have the problem on your system identified in previous posts.
I propose solution, I do not wish to continue trading about opinions. Have a nice weekend!

Perhaps. But 50% wait state is a lot of disk contention for active processes doing I/O.

recommend that you make the database disk partition ext2 filesystem, otherwise you get buffering by both the database and the operating system, while multiplies up the  disk io.    How are your disks configured ?  Can you split indexes onto separate disks ?

Problem is that highest priority IO - swapper takes precedence over any balance of I and O needed by DB.
Actually one can use ext3 options to make it easier on IO and still keep avoiding fsck.

Clarification to another tip:
By "other disk" and independent disk spindle or SSD is assumed, not another filesystem on same disk.

Sorry I forgot to close the question. Thx for the many answers. It was difficult for me to split the points as to whole discussion was valuable.