Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [shared-buffers]

16 questions
6
votes
2 answers

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

I'm using AWS Aurora PostgreSQL Serverless with autoscaling. It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with an I/O bottleneck. After we get warmed up, we…
Brandon
  • 163
  • 1
  • 5
4
votes
3 answers

Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it spends time in wait state IO:BufFileWrite. From…
gvatreya
  • 201
  • 1
  • 4
  • 8
2
votes
1 answer

Exclude tables from shared_buffers in postgres

Is there a way to exclude tables from being added to the shared_buffers? I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. There are some tables which we only write to but don't…
jcfrei
  • 121
  • 3
1
vote
1 answer

Shared buffers, WAL buffers and Checkpointers

I am taking this EDB PostgreSQL essential course and Instructor explained about PostgreSQL architecture referring to the diagram that, whenever a client make an update request and suppose data is present in shared buffer (that means no need to fetch…
commonSense
  • 123
  • 5
1
vote
1 answer

pg_prewarm like funcationality in MySQL

I know in Postgres, we can explicitly load a table into the cache using pg_prewarm extension, is there a similar mechanism/UDF/etc in MySQL? I know MySQL would cache data in shared buffer implicitly, but is there a way to explicitly do so? Thanks!
Chujun Song
  • 11
  • 1
1
vote
0 answers

Why is PostgreSQL reporting a higher shared_buffers value than the parameter in the configuaration file when trying to enable huge_pages?

On a 7.5 GB host (Ubuntu 16.04.5 LTS), I have allocated 3 GB to shared_buffers: postgres=# SHOW shared_buffers; shared_buffers ---------------- 3GB (1 row) with: postgres@my-host:~$ cat /etc/postgresql/10/main/postgresql.conf | grep…
dw8547
  • 947
  • 3
  • 11
  • 24
0
votes
0 answers

how to avoid the postgresql 14 shared buffer swap out

I am facing a issue that the sql first time query was slow. I read the execution plan and found out the index are not in shared buffers the first time query. then I prewarm the index like this: SELECT…
Dolphin
  • 929
  • 5
  • 21
  • 40
0
votes
1 answer

Why is PostgreSQL reading from heap and how to improve cache usage?

I have an SQL query that executes slowly on PostgreSQL 13.15 with 128GB of memory. The query mainly performs a Bitmap Heap Scan, and I’ve noticed that many reads are coming from the heap instead of the cache. Here’s a link to the query and execution…
Mykola Shved
  • 13
  • 3
0
votes
1 answer

PostgreSQL: Is there any way to differentiate an actual Disk I/O from system cache hit?

When using EXPLAIN with buffers, the blocks read sometimes are actually from system cache, this is understandable sometimes based on the read time. But is there any proper way to understand a system cache-miss as of version 13?
goodfella
  • 589
  • 4
  • 14
0
votes
1 answer

Postgresql tuning

We have migrated our PROD DB to new server with 24GB RAM. Based on that value it is possible to tune some PG configuration values? I think we can increase some values. Is it possible to suggest which value can be increased like shared_buffers to 50%…
Robert
  • 141
  • 2
0
votes
1 answer

What is the root cause of 'out of shared memory ' issue?

I'm having 'out of shared memory' issue in PostgreSQL 13.3 on x86_64-pc-linux-gnu (The database has 41G data), first, it suggested me to increase max_locks_per_transaction. After increasing max_locks_per_transaction to 1024, I still get 'out of…
MiH
  • 125
  • 3
0
votes
1 answer

What is a good test command to cause Postgres to use/fill shared_buffers?

I am doing testing on a replica, trying to diagnose why servers take several minutes to shutdown when fast mode is used. It seems to correlate with high shmem usage, can anyone recommend a good query / command to run which would cause postgresql to…
Gregg Leventhal
  • 135
  • 2
0
votes
1 answer

Does it make sense to enlarge "shared_buffers" for writing Postgresql?

I'm using Postgresql-11, with a traditional HDD as the physical storing device. I need to optimize the performance for writing. I have changed the wal_buffers from -1 to 512MB, that means that wal_buffers no long use shared_buffers. Am I right? I…
Leon
  • 411
  • 6
  • 15
0
votes
1 answer

PostgreSQL size of used shared buffer vs actual result size

I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading so much from buffers it often performs I/O which…
jdziwbc
  • 3
  • 3
0
votes
1 answer

PostgreSQL. Shared_memory and sessions

I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with application Jira then there is 16GB left for other usage…
KUE
  • 25
  • 4
1
2