Shared Memory for PostgreSQL

Came across this post while looking for Postgres shared memory configuration. 
Posting the link for source reference

Source : Alexey Vasiliev

Setting up shared memory for PostgreSQL

Hello my dear friends. In this article I will cover about setting up shared memory on Linux for PostgreSQL.

What is shared memory?

Shared memory is memory that may be simultaneously accessed by multiple programs with an intent to provide communication among them or avoid redundant copies. Shared memory is an efficient means of passing data between programs. Using memory for communication inside a single program, for example among its multiple threads, is also referred to as shared memory.

PostgreSQL and shared memory

The “shared_buffers” configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data. One reason the defaults are low is because on some platforms (like older Solaris versions and SGI), having large values requires invasive action like recompiling the kernel. Even on a modern Linux system, the stock kernel will likely not allow setting shared_buffers to over 32MB without adjusting kernel settings first.

If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system. If you have less RAM you’ll have to account more carefully for how much RAM the OS is taking up; closer to 15% is more typical there. There are some workloads where even larger settings for shared_buffers are effective, but given the way PostgreSQL also relies on the operating system cache, it’s unlikely you’ll find using more than 40% of RAM to work better than a smaller amount.

It’s likely you will have to increase the amount of memory your operating system allows you to allocate at once to set the value for shared_buffers this high. On UNIX-like systems, if you set it above what’s supported, you’ll get a message like this

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5440001, size=4011376640, 03600).

This error usually means that PostgreSQL's request for a shared memory
segment exceeded your kernel's SHMMAX parameter. You can either
reduce the request size or reconfigure the kernel with larger SHMMAX.
To reduce the request size (currently 4011376640 bytes), reduce
PostgreSQL's shared_buffers parameter (currently 50000) and/or
its max_connections parameter (currently 12).

probably means your kernel’s limit on the size of shared memory is smaller than the work area PostgreSQL is trying to create (4011376640 bytes in this example). Or it could mean that you do not have System-V-style shared memory support configured into your kernel at all. As a temporary workaround, you can try starting the server with a smaller-than-normal number of buffers (shared_buffers).

Configure shared memory

We must to setup our Unix system shared memory, if we want tune PostgreSQL. We can go by this link and see how to setup it. But what values to set for shmmax and shmall? To do this, I made a small script. It’s mission - to calculate and display the size of shared memory, which is half the available memory on the server:

#!/bin/bash
# simple shmsetup script
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo kernel.shmmax = $shmmax
echo kernel.shmall = $shmall

For example, for a server with 2GB of RAM script will generate the following:

kernel.shmmax = 1055092736
kernel.shmall = 257591

Here SHMMAX maximum size (in bytes) on the segment of shared memory, is set to 1 GB. SHMALL - the total amount of shared memory (in pages), which all processes on the server can use. The number of bytes in a page depends on the operating system, basically, the default is 4096 bytes. In order to have these data have applied for Linux (Ubuntu, Debian), run this command from the root (./shmsetup - the script):

$ ./shmsetup >> /etc/sysctl.conf

And check that:

$ sysctl -p

Also do not forget about semaphores in the system:

$ ipcs -l
------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

The values in sysctl:

$ sysctl kernel.sem = 250 32000 32 128

All four values may need to be increased on systems with a large number of processes.

PostgreSQL 9.3

In 9.3, PostgreSQL has switched from using SysV shared memory to using Posix shared memory and mmap for memory management. This allows easier installation and configuration of PostgreSQL, and means that except in usual cases, system parameters such as SHMMAX and SHMALL no longer need to be adjusted.

Summary

As can be seen, setting up shared memory on Linux for PostgreSQL is not so hard.  

Postgres parameters to check performance

Need to check/Set below parameters: 

show shared_preload_libraries;

show debug_print_plan;

show debug_print_parse;

show logging_collector;

show log_statement;

show log_min_duration_statement;

show log_temp_files;

show log_connections;

show log_disconnections;

show log_lock_waits;

show log_line_prefix;

show log_checkpoints;

show log_error_verbosity;

ALTER DATABASE DATABASE_NAME set shared_preload_libraries = pg_stat_statements;

ALTER DATABASE DATABASE_NAME set log_statement = 'all';

ALTER DATABASE DATABASE_NAME set log_min_duration_statement = 0;

ALTER DATABASE DATABASE_NAME set logging_collector = on;

ALTER DATABASE DATABASE_NAME set debug_print_plan = on;

ALTER DATABASE DATABASE_NAME set debug_print_parse = on;

ALTER DATABASE DATABASE_NAME set log_connections = on;

ALTER DATABASE DATABASE_NAME set log_disconnections = on;

ALTER DATABASE DATABASE_NAME set log_lock_waits = on;

ALTER DATABASE DATABASE_NAME set log_temp_files = 0;

ALTER DATABASE DATABASE_NAME set log_line_prefix = '%t:%r:%u@%d:[%p]: db=%d,user=%u,app=%a,client=%h';

ALTER DATABASE DATABASE_NAME set log_checkpoints = on;

ALTER DATABASE DATABASE_NAME set log_error_verbosity = default;

select tc.table_schema, tc.table_name, kc.column_name

from information_schema.table_constraints tc

  join information_schema.key_column_usage kc 

    on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema and kc.constraint_name = tc.constraint_name

where tc.constraint_type = 'PRIMARY KEY'and kc.ordinal_position is not nullorder by tc.table_schema,

         tc.table_name,

         kc.position_in_unique_constraint;

Queries to check Postgres query performance

Some queries to check Postgres Performance

Show sessions older than 5 minutes

===================================

SELECT

pid,

now() - pg_stat_activity.query_start AS duration,

query,

state

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Show mean time per query

========================

select queryid,query as short_query,

round(total_time::numeric,2) as total_time,

calls, round(mean_time::numeric,2) as mean,

round((100 * total_time / sum(total_time::numeric)

OVER ())::numeric, 2) as percentage_overall

from pg_stat_statements order by total_time desc

limit 20;

Sequential scans on tables

===========================

select schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as AVG

from pg_stat_user_tables

where seq_scan > 0

order by seq_tup_read desc;

Fragmentation of tables

=======================

SELECT

relname AS ObjectName

,pg_stat_get_live_tuples(c.oid) AS LiveTuples

,pg_stat_get_dead_tuples(c.oid) AS DeadTuples

FROM pg_class c

where pg_stat_get_live_tuples(c.oid) > 0 or pg_stat_get_dead_tuples(c.oid) > 0

order by pg_stat_get_live_tuples(c.oid),pg_stat_get_dead_tuples(c.oid) desc;

Show status of tables

=====================

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables

where relname='TABLE_NAME';

Check Locks:

============

select * from pg_locks;

Check Waits:

============

select pid, wait_event_type, wait_event from pg_stat_activity where wait_event is not null;

Any blocks or locked queries/sessions:

=======================================

SELECT

pl.pid as blocked_pid

,psa.usename as blocked_user

,pl2.pid as blocking_pid

,psa2.usename as blocking_user

,psa.query as blocked_statement

FROM pg_catalog.pg_locks pl

JOIN pg_catalog.pg_stat_activity psa

ON pl.pid = psa.pid

JOIN pg_catalog.pg_locks pl2

JOIN pg_catalog.pg_stat_activity psa2

ON pl2.pid = psa2.pid

ON pl.transactionid = pl2.transactionid

AND pl.pid != pl2.pid

WHERE NOT pl.granted;

Connections into the DB

==========================

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Connections waiting for a lock

================================

SELECT count(distinct pid) FROM pg_locks WHERE granted = false;