PostgreSQL – Setting shared_buffers Over 320 MB Prevents Restart

configurationmemorypostgresql

I'm running Postgres 8.1 on a CentOS 5 system with 4 GB of RAM. Whenever I set shared_buffers above 320 MB, it won't run.

I've checked SHMMAX and it's at 4294967295 which is the max allowed for a 32-bit/4 GB system. Below are some of the other memory related settings:

max_connections = 225
shared_buffers = 327680
work_mem = 524288
max_fsm_pages = 500000
max_fsm_relations = 10000

Any suggestions on what could improve this?

Best Answer

Just a misunderstanding I guess. The unit for the given number is blocks (or buffers), not 1 kb like you assume.
Quoting the manual for shared_buffers:

Each buffer is 8192 bytes, unless a different value of BLCKSZ was chosen when building the server.

Bold emphasis mine.

SELECT pg_size_pretty(327680 *  8192::numeric);

pg_size_pretty
--------------
2560 MB

Not 320 MB. In modern Postgres the syntax with appended unit is preferred: 320MB, but I don't think that's implemented in the ancient Postgres 8.1, yet. For 320 MB set the GUC to:

shared_buffers = 40960

Have you considered installing a modern version of Postgres? (Like has been hinted in the comments ever so gently). Postgres 8.1 was released 10 years ago.

Related Solutions

Postgresql – Postgres shared_buffers and pooled processes

No, it's still only 1GB of total memory.

The buffer is allocated as shared memory and it only allocated once, but shared between all server processes.

It's basically an "error" in the memory display of the top command, which simply reports the shared memory (that only exists once) for each process.

PostgreSQL – Poor Query Performance Due to Recheck Condition on Index Scan

Since the selective predicate on the big table eav_value_text_data big table is v.value = 'rs145368920', you need an index on value more than anything else. The index on attribute is hardly relevant - only in combination with the first to allow index-only scans if possible:

CREATE INDEX eav_value_text_data_val_att_idx ON eav_value_text_data (value, attribute);

This should make a huge difference.
value should be the first column in the index. More:

Is a composite index also good for queries on the first field?

Index md5 hash as `uuid`

Since it turns out that your value column is too big to be indexed and you decided to use md5 hashes instead (which is a good solution):

Character varying index overhead & length limit

Consider storing the md5 hash as uuid, which would be most efficient (smaller, faster storage). You can just cast the result to uuid:

md5(value)::uuid

Details in the last chapter of this answer:

Convert hex in text representation to decimal number

Your index would look like this:

CREATE INDEX eav_value_special_idx
ON eav_value_text_data (cast(md5(value) AS uuid), attribute);

Note the explicit cast() syntax in the index definition:

How to create an index on an integer json property in postgres

Your query can still use shorthand syntax:

SELECT count(*)
FROM   eav_value_text_data v 
JOIN   eav_attribute a ON a.id = v.attribute 
WHERE  a.data_type = 11 
AND    a.name = 'id' 
AND    md5(v.value)::uuid = md5('rs145368920')::uuid;

Best Answer

Related Solutions

Postgresql – Postgres shared_buffers and pooled processes

PostgreSQL – Poor Query Performance Due to Recheck Condition on Index Scan

Index md5 hash as uuid

Related Question

Index md5 hash as `uuid`