Questions tagged [buffer-pool]

A place in system memory that is used for caching table and index data pages as they are modified or read from disk.

Filter by
Sorted by
Tagged with
0 votes
1 answer
47 views

Problem with saving data SQL

For some time now I have had a problem with saving data in the database. The problem is that after a sudden shutdown of the server, the data that was sent a minute earlier from the application was not ...
DanPil's user avatar
  • 1
0 votes
1 answer
85 views

Do stolen pages (as in sys.dm_os_performance_counters) report anything that sys.dm_os_memory_clerks does not?

I have been under the misconception that SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%'; shows how much memory the OS is stealing from SQL Server. The ...
J. Mini's user avatar
  • 121
0 votes
1 answer
46 views

Is Monitoring Buffer Pages Too Noisy To Be Useful?

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my production SQL Server 2019 server. Specifically, I ran this Get-DbaDbMemoryUsage -SqlInstance [MyProdServer] -...
J. Mini's user avatar
  • 121
0 votes
1 answer
54 views

76% of the RAM usage on my server is TEXT_MIX_PAGE in tempdb. What does this indicate?

I quite like dbatools, so I gave Get-DbaDbMemoryUsage (docs here) a try on my test SQL Server 2019 server. Specifically, I ran this Get-DbaDbMemoryUsage -SqlInstance [MyServer] -IncludeSystemDbs | ...
J. Mini's user avatar
  • 121
1 vote
1 answer
49 views

Trying to understand the buffer pool usage - actual usage greatly exceeds the total from

I am reviewing the buffer pool size and usage on a setup that was recently moved to a machine with more RAM (new machine has 256 GiB). MySQL server version is 5.7.42. The total data size in the DB (...
Neilski's user avatar
  • 11
1 vote
2 answers
83 views

Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O

On MySQL 8.0.33, I'm experiencing a form of contention (reproduced on multiple instances) that is not explained by the documentation. As example: I boot an instance on a console client, I invoke a ...
Marcus's user avatar
  • 378
1 vote
1 answer
109 views

View memory usage by database on an SQL server

My team (no DBAs in the team) is responsible for a database hosted on an SQL server with multiple other databases used by other areas of the business. Memory usage has recently been spiking to 100% on ...
user avatar
2 votes
4 answers
43 views

Buffer Pool caching in Sql clarification

I might sound a little naive to ask this question but I couldn’t find out the answer anywhere. So my understanding is that the buffer in a sql db maintains disk pages in cache so that when a query ...
saurabh singh's user avatar
2 votes
2 answers
508 views

How to identify if my innodb buffer pool is choking ? (and should i increase it ?)

We have a mysql box where i do rigorous scans/aggregations every few minutes (~ 35GB of data). Innodb buffer pool is ~ 1.5GB. Off late i have started noticing a few latent queries, where the 'read ...
Harshit's user avatar
  • 123
1 vote
1 answer
39 views

What is the Rule of thumb for alerting on`mysql.innodb.buffer_pool_reads / mysql.innodb.buffer_pool_read_requests`?

Is there a rule of thumb for monitoring mysql.innodb.buffer_pool_reads / mysql.innodb.buffer_pool_read_requests? I am using the following formula, but I'm starting to think it's too sensitive. (mysql....
Nicholas's user avatar
  • 123
0 votes
1 answer
300 views

SQL Server - Find Memory ALLOCATED vs. Memory ACTUALLY USED

I want to find average Memory USED per day. SQL server caches as much data as possible in RAM (Buffer Cache) - this is ALLOCATED RAM But how much of this data does it actually read subsequently - this ...
d-_-b's user avatar
  • 1,154
0 votes
1 answer
1k views

How is the size of the buffer pool (data cache) calculated?

What is the formula for calculating the maximum size of the buffer pool (data cache) for SQL Server (when there is only one instance of the machine). Say the machine has 1 GB of physical memory, what ...
xhr489's user avatar
  • 805
0 votes
1 answer
5k views

SQL Server RAM usage - how to find out where it is going?

Short Version SQL Server is using 34 GB of RAM. But when query the Memory Consumption Report, the buffer pool size, and the ad-hoc query size, it only adds up to around 2 GB. What is the other 32 GB ...
Ian Boyd's user avatar
  • 976
0 votes
3 answers
591 views

How Important is the buffer pool really?

I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
SE1986's user avatar
  • 1,960
-1 votes
1 answer
79 views

need to do some fine tuning on my Innodb Buffer

Evening all, I have a modest 6GB database running in MySQL 8.0.x.. my dedicated server is only running 4 cores and 8GB RAM and it doesn't really have any major running issues. after learning some ...
LeccyFez's user avatar
-1 votes
1 answer
86 views

How to Create a user Dedicated innoDb Buffer pool or create a Query Dedicated innodb buffer pool

we are using 16 GB RAM and We have Allotted 4Gb space to Mysql InnoDB Buffer Pool IS there any way we can create multiple Buffer pool in INNoDB or Can we create a User Named Buffer Pool or Per Query ...
adil khan's user avatar
1 vote
2 answers
162 views

MySQL:memory used by query joins does not get released

I have a MySQL db running on a 8cpu 24Gb machine. There are multiple scripts writing or reading from it at any point in time, often in parallel. My settings: [mysqld] innodb_buffer_pool_instances = 6 ...
Vincent Teyssier's user avatar
0 votes
1 answer
660 views

Why is InnoDB so painfully slow on full table scans even though entire data is in buffer pool?

Environment AMD Ryzen 9 5950X, 128GB 3200MHz Dual Channel Datacenter NVMe SSDs with 3GB/s+ read and write MariaDB 10.6.3 x64 Windows Server 2019 (same issue on Debian though) dedicated machine, no ...
Alex K.'s user avatar
3 votes
2 answers
461 views

In SQL Server, what's the impact of a checkpoint to log records in log buffer?

Does checkpoint operations flush everything in the log buffer to log file? Or just the log records relating to the dirty pages that are about to be flushed? I found some inconsistent description about ...
Just a learner's user avatar
3 votes
1 answer
7k views

Buffer Pool scan took 10 seconds error

Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment. Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command 'CHECKPOINT', operation 'FlushCache', ...
Tomasz's user avatar
  • 186
0 votes
1 answer
278 views

Can large data deletion affect Innodb buffer pool performance? [duplicate]

At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
Yassine LD's user avatar
0 votes
1 answer
194 views

Assign different RAM usage (buffer pool size) per db in mysql

I have quite some very large mysql databases (dev, production, staging, etc.) on a single server. Total size of indexes of all my databases is 64Gb. However, the InnoDB Buffer Pool is only 16Go, ...
Robycool's user avatar
  • 101
1 vote
1 answer
717 views

Mysql 5.7 innodb_buffer_pool_size - zero I/O?

I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about. Server ...
mejdenovac's user avatar
3 votes
1 answer
3k views

I can´t change innodb_buffer_pool_instances

I recently updated my mariadb database to 10.5.5 and I can't set innodb_buffer_pool_instances innodb_log_file_size = 3G innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 12 But when ...
URO's user avatar
  • 33
1 vote
1 answer
71 views

Reason for using write barrier before unlocking the buffer header in Postgresql

I was reading the source code for buffer management in Postgres. In buffer management, there is a macro to unlock the buffer header #define UnlockBufHdr(desc, s) \ do { \ ...
Abishek0398's user avatar
0 votes
1 answer
9k views

MEMORYCLERK SQLBUFFERPOOL

SQL Buffer pool is consuming more memory in sql server 2017. Is there any way to reduce it?? or How can we find what is consuming more? We are receiving alert for low buffer cache hit ratio. ...
kumar's user avatar
  • 161
2 votes
1 answer
488 views

Sum of all Memory Clerks and Total Server Memory (KB)

I am trying to understand the memory management of SQL Server. I have a Windows Server 2019 on virtual environment with 64 GB of RAM. I also have SQL Server 2019 installed with Max Server Memory 59776 ...
Rauf Asadov's user avatar
  • 1,263
1 vote
1 answer
105 views

Is filling up plan cache causes a decrease in space allocated for data cache?

SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly. My question: ...
Rauf Asadov's user avatar
  • 1,263
2 votes
1 answer
462 views

Partitioning and the InnoDB Buffer Pool

I am trying to maintain a database that contains a table with 40 billion rows (7.2 terabytes) on a server using InnoDB as a storage engine and MariaDB with MySQL 5.5. When my database reaches about 2....
hbarnes425's user avatar
3 votes
2 answers
1k views

New bufferpool not being used

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a: SQL1218N There ...
Lennart - Slava Ukraini's user avatar
2 votes
1 answer
66 views

Can we set the fixed size for both plan cache and data cache?

As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?
Rauf Asadov's user avatar
  • 1,263
5 votes
2 answers
5k views

Difference between Physical Reads and Read-Ahead Reads

I am trying to understand read-ahead reads, but it seems a bit complicated to me. I searched on the web and got the following: From Reading Pages (Microsoft documentation): Read-ahead anticipates the ...
Rauf Asadov's user avatar
  • 1,263
6 votes
1 answer
112 views

At what point is a (disk-based) table removed from memory after data is read from it?

Are (disk-based, so no Hekaton) tables persisted in memory for longer than the lifespan of the query that is reading data from them? If so, what determines how long they stay in memory for? Is there ...
J.D.'s user avatar
  • 37.5k
1 vote
2 answers
3k views

What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log. The definition itself is simple: InnoDB tries to flush data from the buffer pool so that the percentage ...
Marcus's user avatar
  • 378
1 vote
1 answer
189 views

Restore plan cache and buffer pool after database restore? [duplicate]

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use? Similar question: is there a way to pro-actively restore the buffer ...
Conrad S.'s user avatar
  • 515
2 votes
1 answer
238 views

Big Amount of <free_space_in_bytes> in Buffer Pool's Database Pages

We were viewing sys.dm_os_buffer_descriptors using below query select d.[name] [Database_Name], (count(file_id) * 8) / 1024 [...
Aleksey Vitsko's user avatar
0 votes
0 answers
1k views

MySQL 5.7 isn't using new innodb buffer pool settings

I have a very tiny VM which keeps running out of memory. I believe this is due to the buffer pool size. Default appears to be 128M for innodb_buffer_pool_size and innodb_buffer_pool_chunk_size. So I'...
David's user avatar
  • 133
0 votes
1 answer
35 views

Amount of used cache / free cache

I need to find out how much cache is used, and if there is free cache available. Background: Queries on a production server take a lot of time when the server is under heavy load. I suspect that ...
triton3373's user avatar
0 votes
2 answers
462 views

Least recently used replacement strategy - where is pin count stored?

I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies. The two popular replacement startegies mentioned are least recently used - LRU and ...
sanjihan's user avatar
  • 253
0 votes
1 answer
100 views

Direct database interaction with memory - how it works?

I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies. I've noticed that the DBMS interacts directly with memory, as opposed to asking OS to ...
sanjihan's user avatar
  • 253
8 votes
1 answer
526 views

Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric Database Cache Memory (KB) drops down significantly. If to be exact, it dropped ...
Aleksey Vitsko's user avatar
1 vote
0 answers
269 views

How can I reproduce InnoDB buffer pool 'warm-up' time? What am I doing wrong with sysbench?

I've found that a large production DB experiences a severe degradation in performance, after a restart. It makes sense that this is because after a restart, the innoDB buffer pool isn't yet warmed up ...
Joe Mag Fhionnghaile's user avatar
0 votes
0 answers
1k views

buffer overflow detected in mariadb 10.3.11

We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory. Example: InnoDB: Assertion failure in file /home/buildbot/buildbot/build/mariadb-10.3....
Sathish Kumar S's user avatar
2 votes
0 answers
393 views

SQL Server buffer cache smaller after reboot with same workload [closed]

We are hosting a SQL Server 2014 SP2 CU16 in a Windows Server 2016 64-bit environment for a customer. Performance has been fine for a long while. On March 31 at midnight, the database server ...
Paul Williams's user avatar
0 votes
1 answer
127 views

Does Lazy Writer spend resources on flushing out pages from the buffer pool?

In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages ...
igelr's user avatar
  • 2,132
1 vote
1 answer
877 views

How does SQL Server determine how much memory to allocate to Data cache

SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets ...
SE1986's user avatar
  • 1,960
0 votes
1 answer
266 views

innodb cache duplication due to os page cache and buffer pool

We have set our innodb buffer pool 80% of total memory. Mysql page allocation gets stalled sometimes when page cache is occupying memory. Now since mysql is caching table rows and indexes and follows ...
Kalyana Sundaram's user avatar
0 votes
1 answer
1k views

How does MySQL evict pages from the buffer pool?

I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as ...
Hadi's user avatar
  • 127
1 vote
1 answer
804 views

Cannot set innodb_buffer_pool_size above 1023M in my.ini

Windows server 2012 R2 with 64GB RAM, MySQL 8. The Database is about 5GB. I am using a buffer pool of 1023M, as shown below: innodb_buffer_pool_size=1023M If I enter anything above 1023M e.g. 1024M ...
Simon's user avatar
  • 11
0 votes
0 answers
553 views

MySQL connection timeout issues

We have a PXC environment with three nodes, and each node has 8GB of total RAM, with half of that (4G) dedicated to SWAP, and 3GB configured for Innodb-buffer_pool_size. Our developers have reported ...
The Georgia's user avatar