0

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

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
6
  • Yup, the shared_buffers to 25% helped. Thx
    – user84590
    Mar 22, 2010 at 10:46
  • Why not increase it to 50%?
    – user84590
    Mar 22, 2010 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, 2010 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 :)
    – user84590
    Mar 23, 2010 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, 2010 at 8:54
1

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.

Improve this answer
3
  • 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?
    – user84590
    Mar 21, 2010 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, 2010 at 22:20
  • That working memory config seems to have helped. Thx.
    – user84590
    Mar 22, 2010 at 10:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .