Jul 30 2008

Open source databases; scaling the application stack.

Published by kgorman under postgres

After reading Allan Packers excellent blog entry; Are Proprietary Databases Doomed?, a couple of additional thoughts came to mind. He mentions that open source databases (OSDB’s) are becoming ‘good enough’, and explains about how most users don’t need or use the vast amount of the features available in the proprietary databases, and thus, OSDB’s are becoming more popular. Coming from a proprietary background myself, scaling Oracle at Paypal and then Ebay, I know that side of the business pretty well. In the last year, I have made the professional leap to OSDB’s in a big way at Hi5, where we do quite a bit of workload on an OSDB; PostgreSQL. So Allan’s points hit home for me.

Continue Reading »

2 responses so far

Jul 02 2008

Python script showing PostgreSQL objects in linux memory: pg_osmem.py

Published by kgorman under postgres

I got some email and comments about the code I used for my post; Mapping Linux memory for PostgreSQL using fincore so I thought I would post the code I am using. Nothing too fancy here, it needs a config file and some more bits. I highly recommend someone do this in perl vs python and incorporate fincore as a module instead. I used psycopg2, other than that, all the other modules are stock with python 2.5.2. If someone wants to show me how to query the data directory PostgreSQL is currently using and incorporate that into the script vs the static string: mydir = “/data/mydb/base”, that would be great. In order to use this script, you must change the variables for fincore, and mydir below.

 
#!/home/postgres/python/bin/python
#
# script to find show memory usage of PG buffers in OS cache
# 2008 kcg
#
import os
import psycopg2
import commands
import re
import sys
from optparse import OptionParser
 
parser = OptionParser()
parser.add_option("-u","--username",dest="username",help="username for PostgreSQL")
parser.add_option("-m","--machine",dest="machine",help="machine to connect to.. aka: hostname")
parser.add_option("-d","--dbname",dest="dbname",help="database name to connect to")
parser.add_option("-p","--password",dest="password",help="password for PostgreSQL")
(options, args) = parser.parse_args()
 
osmem   ={}
# change these to match actual locations
fincore ="/home/kgorman/fincore.pl"
mydir   = "/data/mydb/base"
 
# get list of dbs on host, and return dictionary of db=oid sets
def lookup_dbs():
 dbs={}
 connectstr="host="+options.machine+" dbname="+options.dbname+" user="+options.username+" port=5432 password="+options.password
 handle=psycopg2.connect(connectstr)
 curs=handle.cursor()
 sql="select datname,oid from pg_database where datname = '"+options.dbname+"' and datname not like '%template%'"
 curs.execute(sql)
 for d in curs.fetchall():
   dbs[d[0]]=d[1]
 return dbs
 
# get object
def lookup_oid(oid,dbname):
 connectstr="host="+options.machine+" dbname="+dbname+" user="+options.username+" port=5432 password="+options.password
 handle=psycopg2.connect(connectstr)
 curs=handle.cursor()
 sql="select relname from pg_class where oid = "+oid
 curs.execute(sql)
 for d in curs.fetchall():
  return d[0]
 
dbs=lookup_dbs()
for v, i in dbs.iteritems():
  for ii in os.listdir(mydir+"/"+str(i)):
     p = re.compile('\d')
     if p.match(ii):
     	#print ii
        rel=lookup_oid(ii,v)
        fullpath=mydir+"/"+str(i)+"/"+ii
	cmd=fincore+" "+fullpath
	#print cmd
	pages=commands.getstatusoutput(cmd)
	#print pages
	n=pages[1].split(' ')
        size=n[1]
	if p.match(size):
           if rel:
	     osmem[v+":"+rel]=(int(size)*1024)
 
# sort and output
sdata=sorted(osmem.iteritems(), key=lambda (k,v): (v,k), reverse=True)
a=0
print "OS Cache Usage:"
while a < len(sdata):
  print sdata[a][0]+":"+str(sdata[a][1])
  a=a+1

4 responses so far

Jun 24 2008

pg_standby lag monitoring

Published by kgorman under Uncategorized

I have been using pg_standby quite a bit lately, and thus we needed some management tools around it.  One simple thing that was sorely missed was lag monitoring.  When you have 100’s of standby’s,  and many of them on the bleeding edge of keeping up with the primary, then lag monitoring is very important.  Heck, it’s even important if you just have one standby database.  If your standby is lagging when you need it, you might effect your ability to have a up to date database when you fail over to it. The problem is that PostgreSQL has no way to actively query the database for it’s lag amount, because it’s actively recovering.  So one must rely on the control file for the last checkpoint time as the most accurate marker of how up to date the standby actually is. Here is some code for implementing this: 

#!/home/postgres/python/bin/python
#
#
# script returns number of seconds since last checkpoint
# to be used for standby lag detection
#
import os
import commands
import string
import time
import datetime
 
pg_controldata  ="/usr/local/pgsql/bin/pg_controldata"
cmd             =pg_controldata+" /data/mydb"
 
out = commands.getoutput(cmd)
line = string.split(out, "\n")
checkpoint_date_dict = string.split(line[16], ": ")
checkpoint_date=checkpoint_date_dict[1].lstrip()
# format: Thu 08 May 2008 01:58:18 PM PDT
checkpoint_epoch = int(time.mktime(time.strptime(checkpoint_date, '%a %d %B %Y %H:%M:%S %p %Z')))
 
t = datetime.datetime.now()
now = time.mktime(t.timetuple())
 
print "lag="+str(now-checkpoint_epoch)

The output of this script is quite simple, so it can be called from monitoring solutions like HQ or whatever you use in-house. You will need to alter the line that shows where your base installation is; pg_controldata+” /data/mydb/” in my example.

$>./pg_standby_lag.py
$>lag=10

This indicates the standby is 10 seconds lagging from the primary. Perhaps you alert when you see lag over 3600 (or 1 hour).

One response so far

Jun 18 2008

Mapping Linux memory for PostgreSQL using fincore

Published by kgorman under postgres

PostgreSQL, more-so than other databases generally relies on OS file system cache to keep performance high. It is pretty common not to see gains in performance for buffer cache sizes over about 2GB, why this is true I am unsure, but perhaps the tracking of buffer overhead becomes high? Anyways, since the OS buffer cache keeps file buffers in memory as well, there are lots of database objects in the PostgreSQL cache, OS cache, or both. We can debate double buffering later.

To see what is in the PostgreSQL cache, one can use the contrib module (and now built in in 8.3) pg_buffercache to map each object with it’s footprint in cache. But, if one wanted to see the entire cache picture, including OS cache, how would that be done?

Enter fincore, pronounced “eff in core”. This utility was originally presented at LISA2007 by David Plonka, Archit Gupta, and Dale Carder of University of Wisconsin—Madison. This utility (and the sister utility fadvise) are great utilities to find out what your PostgreSQL database has pulled into OS cache.

In order to see what database objects these files map to, we can just query the database itself and join each database object to it’s associated OS file using pg_class.relfilenode. Then call fincore for each file name, and output the resulting # of pages per database object. Slow but simple.

I put together some python to glue this all together. The best way would be to use perl, and use fincore as a module. In our environment at least we have standardized on python and psycopg2 for database access. So this script utilizes fincore on the command line and captures the output. Perhaps not as clean as I would have liked, but this tool is not used every day. In our case we left fincore stock as we downloaded it in order to keep these components as separate as possible. This is also a diagnostic tool; it’s quite slow and resource consumptive. If you have lots of objects things might get slow. YMMV.

Using this code, one can see the set of most used buffers in the OS cache, and compare them to the PG cache. Sizes are in bytes.

	$>./pg_osmem.py
	postgres:accounts:268435456
	postgres:tellers:3332096
	postgres:branches:3155968
	postgres:history:615424
	postgres:pg_proc:96256
	postgres:tellers_pkey:91136
	postgres:pg_depend:75776
	postgres:pg_proc_proname_args_nsp_index:74752
	postgres:pg_depend_reference_index:58368
	postgres:pg_statistic:55296
	postgres:pg_attribute:55296
	postgres:pg_depend_depender_index:51200
	postgres:pg_attribute_relid_attnam_index:49152
	postgres:pg_toast_2618:36864
	postgres:pg_description:34816
	postgres:pg_operator:28672
	postgres:pg_description_o_c_o_index:23552
	postgres:pg_operator_oprname_l_r_n_index:22528
	postgres:pg_rewrite:18432
	postgres:pg_proc_oid_index:16384
	postgres:pg_class_relname_nsp_index:16384
	postgres:pg_type_typname_nsp_index:14336
	postgres:pg_attribute_relid_attnum_index:14336
	postgres:branches_pkey:14336
	postgres:sql_features:12288

And then you can see that the PG buffer cache has the following:

	postgres=# SELECT current_database(),c.relname, count(*)*8192 as bytes
	postgres-# FROM pg_buffercache b INNER JOIN pg_class c
	postgres-# ON b.relfilenode = c.relfilenode AND
	postgres-# b.reldatabase IN (0, (SELECT oid FROM pg_database
	postgres(# WHERE datname = current_database()))
	postgres-# GROUP BY c.relname
	postgres-# ORDER BY 3 DESC LIMIT 25;
 
	 current_database |             relname             |   bytes   
	------------------+---------------------------------+-----------
	 postgres         | testtable                       | 376979456
	 postgres         | accounts                        | 309608448
	 postgres         | accounts_pkey                   | 225583104
	 postgres         | testtable_pk                    | 141058048
	 postgres         | branches                        |  10715136
	 postgres         | tellers                         |   5488640
	 postgres         | history                         |   2457600
	 postgres         | tellers_pkey                    |    360448
	 postgres         | pg_proc                         |    131072
	 postgres         | pg_operator                     |    106496
	 postgres         | pg_attribute                    |     65536
	 postgres         | pg_operator_oprname_l_r_n_index |     65536
	 postgres         | pg_proc_oid_index               |     65536
	 postgres         | branches_pkey                   |     57344
	 postgres         | pg_statistic                    |     57344
	 postgres         | pg_attribute_relid_attnum_index |     49152
	 postgres         | pg_class                        |     49152
	 postgres         | pg_class_relname_nsp_index      |     49152
	 postgres         | pg_depend_reference_index       |     49152
	 postgres         | pg_type_typname_nsp_index       |     40960
	 postgres         | pg_proc_proname_args_nsp_index  |     40960
	 postgres         | pg_statistic_relid_att_index    |     40960
	 postgres         | pg_operator_oid_index           |     32768
	 postgres         | pg_opclass_am_name_nsp_index    |     24576
	 postgres         | pg_amop_opr_opc_index           |     24576

3 responses so far

Apr 30 2008

Incremental Commits in PostgreSQL

Published by kgorman under postgres

On large and/or busy databases it’s often not feasible to update an entire table at one time. For instance, if one has to perform some background DML task to change all credit card limits on a 300gb table that sees 1000 transactions per second, and increase each card limit by $1000. Simply executing an update statement with no predicate would attempt to lock every row in the table and perform the update. Even if the lock was granted on such a busy table, you would block all updates to those rows (they would enqueue) until you commit (or rollback), and you would also cause lots of reads from the read-before images. This is bad in Oracle; ORA-01555: snapshot too old, and also has vacuum implications in PostgreSQL. So in order to run our theoretical job above, we need some sort of procedure that runs in the background and commits the work every N rows. This is a pretty common technique in Oracle PL/SQL, and a simple construct exists:

DECLARE
 
 CURSOR mycur IS SELECT id FROM bigtab;
 COUNTER number := 0;
 
BEGIN
 
 FOR myrec IN mycur LOOP
 
    UPDATE bigtab SET credit_limit = credit_limit+1000 WHERE id = myrec.id;
 
    IF mod(counter,2000)=0 THEN
       COMMIT;
    END IF;
 
    COUNTER:=COUNTER+1;
 
 END LOOP;
 
COMMIT;
 
END;
/

The above code incrementally commits every 1000 rows and performs all updates until the cursor is exhausted. In PostgreSQL, there is no simple PL/pgSQL substitute because COMMIT can not be called in a function. So what to do? Well, python has a nice fetchmany(size) construct that allows us to create a small memory footprint program that commits incrementally just like the above Oracle code. The variable incremental_commit_size is the size of the commit batch, and the throttle_time is an amount to sleep after each commit to further make this process very slow and low impact. The code is also uses binds so you don’t create many cursor permutations.

#!/home/postgres/python/bin/python
#
# incremental commits
# 2008 kcg
 
import psycopg2
import time
 
# vars
incremental_commit_size=1000	# number of rows
throttle_time=0		    # seconds
 
connectstr="host=localhost dbname=postgres user=postgres port=5432"
handle=psycopg2.connect(connectstr)
cursor=handle.cursor()
cursor2=handle.cursor()
sql="select id from bigtab"
cursor.execute(sql)
 
while 1:
 
 output = cursor.fetchmany(incremental_commit_size)
 
 if not output:
  break
 for row in output:
 
   # update table
   sql="update bigtab set credit_limit = credit_limit+1000 where id = %s"
   cursor2.execute(sql,([row[0]]))
 
 #commit, invoked every incremental commit size
 handle.commit()
 time.sleep(throttle_time)
 
handle.commit()

No responses yet

Apr 18 2008

int vs bigint

Published by kgorman under postgres

So which one is a better use of space given that an application uses 32bit integers? In PostgreSQL, the int column takes only 4 bytes, and the bigint takes 8 bytes. So one would think there is significant space savings to be had by using int. In the real world with disk space costs where they are at, it just does not matter in a practical sense. Furthermore, if you are running on an extent based filesystem like ext3 or vxfs, then you really don’t see any practical benefits. Why? Well, your extent size is likely to be much larger then (number of tuples * 4 bytes) so the difference in size is masked because the FS sizes up to the next extent size. Consider the below example:

 
vendor_id	: GenuineIntel
cpu family	: 6
model		: 23
model name	: Intel(R) Xeon(R) CPU           E5405  @ 2.00GHz
2.6.16.46-0.12-smp #1 SMP Thu May 17 14:00:09 UTC 2007 x86_64 x86_64 x86_64 GNU/Linux
pg_ctl (PostgreSQL) 8.2.4
 
postgres=# \d testtable2;
           Table "public.testtable2"
  Column  |          Type          | Modifiers 
----------+------------------------+-----------
 id       | bigint                 | not null
 text     | character varying(200) | 
 thecount | integer                | 
Indexes:
    "testtable2_pk" PRIMARY KEY, btree (id)
 
postgres=# \d testtable3;
           Table "public.testtable3"
  Column  |          Type          | Modifiers 
----------+------------------------+-----------
 id       | integer                | not null
 text     | character varying(200) | 
 thecount | integer                | 
Indexes:
 
postgres=# select count(*) from testtable2;
  count  
---------
 2253530
(1 row)
 
postgres=# select count(*) from testtable3;
  count  
---------
 2253530
 
postgres=# analyze testtable2;
ANALYZE
postgres=# analyze testtable3;
ANALYZE
 
postgres=# select tablename, attname, avg_width,n_distinct from pg_stats where tablename = 'testtable2';
 tablename  | attname  | avg_width | n_distinct 
------------+----------+-----------+------------
 testtable2 | text     |        56 |          1
 testtable2 | thecount |         4 |          0
 testtable2 | id       |         8 |         -1
 
postgres=# select tablename, attname, avg_width,n_distinct from pg_stats where tablename = 'testtable3';
 tablename  | attname  | avg_width | n_distinct 
------------+----------+-----------+------------
 testtable3 | thecount |         4 |          0
 testtable3 | id       |         4 |         -1
 testtable3 | text     |        56 |          1
(3 rows)
 
postgres=# SELECT * FROM pgstattuple('testtable3');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
 227917824 |     2253530 | 207324760 |         90.96 |                0 |              0 |                  0 |    1897096 |         0.83

Two tables with the only difference being the testtable2 is int and testtable3 is bigint. Same number of rows. Different row sizes. Even pgstattuple shows different row lengths.

postgres=# SELECT schemaname, tablename,                                                                
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS size,
pg_total_relation_size(schemaname||'.'||tablename) AS total_size
FROM pg_tables where tablename like '%testtable%') AS TABLES
ORDER BY total_size DESC;
 schemaname | tablename  | size_pretty | total_size_pretty 
------------+------------+-------------+-------------------
 public     | testtable2 | 217 MB      | 266 MB
 public     | testtable3 | 217 MB      | 266 MB
 
select relname, relpages from pg_class where relname in ('testtable2','testtable3');
  relname   | relpages 
------------+----------
 testtable2 |    27822
 testtable3 |    27822
(2 rows)
 
postgres=# select 27822*8192;
 ?column?  
-----------
 227917824
(1 row)
 
postgres=# select 27822*8192/1024/1024;
 ?column? 
----------
      217

But since we are on an extent based filesystem, we see the exact same size on disk. So for anything other than the most extravagant setups, it’s smart to use bigint and not limit yourself to 32bit integers. There is no space penalty for doing so at least. There are performance penalties in performing arithmetic on these columns, but no downside in most practical applications. For instance, a row access by index.

 
postgres=# select id from testtable2 where id = 2003;
  id  
------
 2003
(1 row)
 
Time: 0.131 ms
 
postgres=# select id from testtable3 where id = 2003;
  id  
------
 2003
(1 row)
 
Time: 0.165 ms
 
insert 100000 rows into testtable2 and commit:
Elapsed Seconds: 21.438544035
TPS: 4664.49586487
 
insert 100000 rows into testtable3 and commit:
Elapsed Seconds: 21.7363810539
TPS: 4600.58184258

If you perform calculations on these id’s you do see a performance hit, so be careful:

postgres=# select id*10000+45/10 from testtable2 where id = 2003;
 ?column? 
----------
 20030004
(1 row)
 
Time: 0.268 ms
postgres=# select id*10000+45/10 from testtable3 where id = 2003;
 ?column? 
----------
 20030004
(1 row)
 
Time: 0.133 ms

No responses yet

Apr 10 2008

The next breed of DBA’s?

Published by kgorman under postgres

University of California, Berkeley and Carnegie Mellon University students now have what appears to be a first rate course on database internals. What I like about this course is not only is it very practical experience, but also, that it teaches internals. So much of how a database works has to do with how it’s built, and the syllabus shows that this course should turn out excellent talent ready to develop on any database platform. However, how many of these students will go on to become DBA’s? I hope many, but it’s not likely I am afraid. It’s too bad, because this is the kind of knowledge that makes excellent DBA’s.

In the non-open source world, Oracle DBA’s are plentiful, and rightly so because of the scale and complication of the software itself. However, many DBA’s coming from this side of the fence are more ‘operators’ in my opinion. They know how to manipulate the software to achieve a result, but do they really understand deep deep down? In my experience at Paypal and Ebay I was lucky enough to get good experience with internals because at our transaction rate, you simply had to understand internals. I remember re-reading James Morles book; Scaling Oracle 8i and having a lightbulb moment about latches - Ah! I get why something so seemly small as latches mean so much when you scale a really busy system.

It’s great to see that students are now getting good hands on expertise in these internals right from the start of their careers. Here is to hoping some of these students take this excellent and practical knowledge and decide to be DBA’s.

No responses yet

Mar 31 2008

Python/cx_Oracle utility functions

Published by kgorman under random

I recently created some utility functions to startup, mount and stop Oracle via cx_Oracle and thought I would share them. You will want to make sure you have password files setup correctly in order for this stuff to work.

# shutdown abort:
def shutdown_abort(sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA)
   handle.shutdown(mode = cx_Oracle.DBSHUTDOWN_ABORT)
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   return 1
 
# startup nomount:
def startup_nomount(sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH)
   handle.startup()
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   return 1
 
# mount:
def db_mount(sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA)
   cursor = handle.cursor()
   cursor.execute("alter database mount")
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   return 1
 
# open resetlogs
def db_open_resetlogs(sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA)
   cursor = handle.cursor()
   cursor.execute("alter database open resetlogs")
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   return 1
 
# open
def db_open(sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA)
   cursor = handle.cursor()
   cursor.execute("alter database open")
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   return 1
 
# Flashback database to a given restore point:
def flashback_db(restore_point,sid):
 try:
   os.environ['TWO_TASK']=sid
   handle=cx_Oracle.connect("sys", "yourpwd", sid, cx_Oracle.SYSDBA)
   cursor = handle.cursor()
   sql="flashback database to restore point "+restore_point
   print sql
   cursor.execute(sql)
   return 0
 except cx_Oracle.DatabaseError,info:
   print "Error: ",info
   os.exit(1)

No responses yet

Mar 23 2008

Primary Keys for Slony-I

Published by kgorman under postgres

One of the prerequisites for Slony replication is to be sure that primary keys (or at least suitable columns) are defined for each table to be under replication. This is a simple example script for getting that data in postgresql. This assumes that the intention is to replicate all of the public schema.

SELECT tablename 
FROM pg_tables 
WHERE tablename NOT IN 
 (SELECT r.relname
 FROM pg_class r, pg_constraint c
 WHERE r.oid = c.conrelid
 AND c.contype = 'p'
 )
AND schemaname = 'public';

If the output shows tables without primary keys, then the decision can be made to either add them, or instruct slonik to use suitable columns that form a logical unique key. However, as the documentation states, you might as well just define the keys if you go this far.

No responses yet

Mar 11 2008

Memory sizing for PostgreSQL

Published by kgorman under database engineering, postgres

I needed to make an assessments of a linux server’s memory footprint consumed by postgresql, however, calculating the exact memory footprint of a postgresql database is not as straightforward as one might think. Since unix utilities like top, and ps tend to show some amount of shared memory usage in their tallies, they are next to useless for this purpose. The other problem is Linux caches filesystem buffers to fill up most free space on the box, so deciphering what is actually used by postgresql and what is really filesystem buffers is confusing as well. The calculation needed to be just a reasonable estimate of the memory usage, so this method is non-perfect, but should be good for most planning purposes.

In order to come up with the memory usage, I decided to subtract the postgresql usage from the total memory footprint shown in /proc/meminfo. I divided up the postgresql memory usage parameters into two types of consumption:

  1. fixed memory usage - memory consumption that doesn’t change rapidly
  2. variable memory usage - memory consumption that does change rapidly

An example of fixed memory is anything allocated at startup that remains relatively fixed, an example of variable buffers is anything multiplied by the number of connections.

fixed parameters:

  1. shared_buffers
  2. wall_buffers
  3. max_fsm_pages

variable parameters:

  1. work_mem
  2. maintenance_work_mem

Using /proc/meminfo and the postgresql.conf file one can calculate the memory footprint with a reasonably high degree of certainty using:

(MemTotal-MemFree-Cached)-((shared_buffers*8)+(wal_buffers*8)+((fsm_pages*6)/1024))=variable size in KB

For instance:

$>cat /proc/meminfo
MemTotal: 32876680 kB
MemFree: 1762176 kB
Buffers: 256664 kB
Cached: 30040432 kB
$>cat /data/postgresql.conf | grep buffers
shared_buffers = 65535 # min 16 or max_connections*2, 8KB each
wal_buffers = 1024 # min 4, 8KB each
$>cat /data/postgresql.conf | grep fsm
max_fsm_pages = 8000000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 2000 # min 100, ~70 bytes each
$>cat /data/postgresql.conf | grep mem
work_mem = 32768 # size in KB
maintenance_work_mem = 768000 # min 1024, size in KB

Thus:

(32876680-1762176-30040432)-(524280+8192+46875)=494725 KB

Now that we have the variable memory size, we have defined the major components of the memory footprint. Then we can calculate the average size of each backend process by dividing the total usage by the number of backend processes. Assuming for we expect very few, or perhaps no backends consuming maintenance_work_mem at any time (for instance auto-vacuum’s running) 

$>ps -ef | grep "postgres: postgres" | grep -v grep | wc -l
122
$>echo 494725/122 | bc
4055

So each connection consumes avg ~4KB. We can now adjust, tune, and play what-if with those numbers as we desire.

It makes sense to sample the number of backend connections and memory usage during different periods of the day and/or during batch runs, etc to get a good read on the usage throughout the day.

2 responses so far

Next »