Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am running a python program that spawns 8 threads and as each thread launch its own postmaster process via psycopg2. This is to maximize the use of my CPU-cores (8). Each thread call a series of SQL Functions. Most of these functions go through many thousands of rows each associated to a large FLOAT8[] Array (250-300) values by using unnest() and multiplying each FLOAT8 by an another FLOAT8 associated to each row. This Array approach minimized the size of the Indexes and the Tables. The Function ends with an Insert into another Table of a row of the same form (pk INT4, array FLOAT8[]). Some SQL Functions called by python will Update a row of these kind of Tables (with large Arrays).

Now I currently have configured PostgreSQL to use most of the memory for cache (effective_cache_size of 57 GB I think) and only a small amount of it for shared memory (1GB I think). First, I was wondering what the difference between Cache and Shared memory was in regards to PostgreSQL (and my application).

What I have noticed is that only about 20-40% of my total CPU processing power is used during the most Read intensive parts of the application (Select unnest(array) etc). So secondly, I was wondering what I could do to improve this so that 100% of the CPU is used. Based on my observations, it does not seem to have anything to do with python or its GIL.

Thanks

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

effective_cache_size does not change any memory setting, it's used only for estimation purposes in query planning. Crank up the shared_buffers to about 25% of your available RAM and see if there are any differences in speed.

Also, use EXPLAIN to get the queryplan and see if you need some extra indexes or better configuration.

share|improve this answer
    
Yup, the shared_buffers to 25% helped. Thx –  user38338 Mar 22 '10 at 10:46
    
Why not increase it to 50%? –  user38338 Mar 22 '10 at 20:35
    
Because you don't want to kill your server. If you have a lot of RAM, you might use more than 25% for shared_buffers, but be carefull. Be very carefull! PostgreSQL is using more RAM than just shared_buffers and your server also needs RAM for other things. Every connection needs RAM, every sort operation needs RAM, etc. etc. etc. When you push it too much, your server might kill the postmaster (eats too much RAM) or starts swapping and killing performance. A better queryplan, using less RAM, might be a better idea. But that's up to the database. –  Frank Heikens Mar 22 '10 at 21:02
    
I see. When I look at top or dstat, I see 58GB used for cache. Does this include both shared buffers and kernel cache? It seems like it is both because my 8 postmaster processes are using 9 GB of shared memory while I only have 64GB of ram. ? Thx again :) –  user38338 Mar 23 '10 at 0:36
    
See this picture: postgresql.org/files/documentation/books/aw_pgsql/… And yes, the 58GB is all the cache. PostgreSQL doesn't take all shared_buffers when it starts, it just takes what it needs, up to the limits set in the configuration. –  Frank Heikens Mar 23 '10 at 8:54
show 2 more comments

It seems that you have hit an I/O bottleneck. You have a lot of cache memory, but how big is the dataset? What is the current disk configuration? How busy are the disks? Could the bottleneck be the network?

Another thing to check is how much work memory has each process. It is possible that there is a lot of memory traffic for no reason.

This site has a good overview for tuning postgres.

share|improve this answer
    
The dataset is more then 60GB, but the Tables whose entire rows are used by each SQL Function take about 15GB. This disk configuration is 2 RAID 10 Arrays of 4 SAS 10k Rpm 146 GB each. The 15 GB of frequently used Tables are on the first RAID 10 array, while everything else, including those than get a lot of Inserts, are on the other RAID 10 array. The disks do not seem that busy. Its definitely not the network. Each process's memory usage does seem to grow a lot as the application progresses. What do you suggest? –  user38338 Mar 21 '10 at 21:50
    
You should check the I/O performance anyway just to exclude it as an issue. Check mainly the number of trasactions per seconds. Second, each postgres worker should get a fixed amount of memory, specified by the work memory config option. Try increase that. Edited the answer as well –  Dan Andreatta Mar 21 '10 at 22:20
    
That working memory config seems to have helped. Thx. –  user38338 Mar 22 '10 at 10:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.