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.
91
questions
0
votes
1answer
29 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, ...
1
vote
1answer
64 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 ...
0
votes
0answers
95 views
MySQL: Configuring the right innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size on RDS with huge RAM
We are on a db.r4.8xlarge MySQL (version 5.7.23) AWS RDS instance with 16 cores, 32 vCPUs, and 244GB RAM. We set the Buffer Pool Size to around 144GB at the default innodb_buffer_pool_chunk_size and ...
3
votes
1answer
199 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
...
1
vote
1answer
30 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 { \
...
0
votes
1answer
949 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.
...
1
vote
1answer
65 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 ...
1
vote
1answer
34 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: ...
2
votes
1answer
90 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....
3
votes
2answers
321 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 ...
2
votes
1answer
50 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?
2
votes
2answers
603 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 ...
6
votes
1answer
101 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 ...
1
vote
2answers
593 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 ...
1
vote
1answer
72 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 ...
2
votes
1answer
101 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 [...
0
votes
0answers
402 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'...
0
votes
1answer
24 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 ...
0
votes
1answer
76 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 ...
0
votes
1answer
61 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 ...
8
votes
1answer
284 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 ...
1
vote
0answers
91 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 ...
0
votes
0answers
725 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....
2
votes
0answers
302 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 ...
0
votes
1answer
71 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 ...
1
vote
1answer
418 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 ...
0
votes
1answer
113 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 ...
0
votes
1answer
533 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 ...
1
vote
1answer
366 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 ...
0
votes
0answers
264 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 ...
0
votes
2answers
178 views
In Full recovery mode, when does the buffer/transaction log changes get applied to the database data pages?
When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead ...
2
votes
0answers
74 views
MySQL Queries Truncate Causing Connection Spikes
We have a MySQL Master Instance & Read (failover only) on AWS. MySQL 5.6 Aurora running Magento. Performance is great, and better than standard MySQL (we have high traffic site so see the benefits)...
4
votes
3answers
4k views
Would my MySQL database benefit from increasing the InnoDB buffer pool size?
Of course, the answer is pretty much always "yes", but I'm interested in my specific situation.
This is similar to Making sense of INNODB buffer pool stats but, IMO, a little more specific.
First of ...
3
votes
1answer
2k views
getting innodb buffer usage
I am running MySQL 5.5.52 I am trying to see how much of my innodb buffer pool is used. If I run 'show engine innodb status' I see this:
----------------------
BUFFER POOL AND MEMORY
-----------------...
2
votes
0answers
631 views
SQL Buffer Pool and Plan cache
Does SQL Buffer Pool size = Max server memory.?
I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours.
I have 128 GB Physical Memory.
Earlier i ...
13
votes
4answers
57k views
Memory Usage by SQL Server
How do i check memory usage by my SQL server in production box.
I am using SQL Server 2016.When ever i check task manager,it shows above 90%.
I don't think that is the real memory usage by sql server.
...
0
votes
1answer
106 views
Permanent solution for Fixing buffer size
I have installed MySQL 5.7 on windows server 2016 and set the buffer pool size to 4 GB through SQL query but when the server is restarted buffer pool size getting reduced resulting in the slowness of ...
0
votes
1answer
52 views
Querying MySQL InnoDB to find if the Record is in its Buffer Pool
For us, it is very important to get high response time for our website php script which queries a large database. It is essentially getting user information for the user who visited the page. There ...
5
votes
1answer
297 views
Dump the buffer pool to disk so it can be loaded after a restart
Is there a way to preserve the buffer pool cache so that is can be loaded in after a restart? My research suggests that other DB servers can do this but SQL Server is not one of them. I'm looking for ...
3
votes
2answers
2k views
MySQL `innodb_log_file_size`: how big is too big?
In many answers, discussions, and comments, the prevailing wisdom seems to be to "set innodb_log_file_size to be 25% of the inndb_buffer_pool_size.
What about fairly large buffer pools? For example, ...
2
votes
1answer
47 views
SQL Server Buffer Pool Extension in FullText Index Population
I have a server that has 32GB of RAM and SQL Server 2014 Enterprise installed over it. I want to try the impact of Buffer Pool Extension (BPE) on Full Text Population.
I created a table and populated ...
1
vote
0answers
67 views
Why are pages being removed from the buffer cache
I have an test environment with SQL Server 2016 SP1 installed.
My machine has 32GB of RAM and I give 30GB to SQL Server. I have 2 databases in my server - BGD and, BGD_Mem. The second one contains ...
4
votes
1answer
12k views
SQL Server 2012/2014 insufficient memory available in the buffer pool [closed]
We have bunch of SQL Server 2012 Standard and SQL Server 2014 Standard database instances running on Windows 2012, each have different storage and memory. We are getting:
Error Code: 802; There is ...
1
vote
1answer
63 views
What is the purpose of a buffer and a buffer manager?
In a database system, what is the purpose of a buffer and a buffer manager?
This concept currently came up in my class and am very interested to learn more.
2
votes
1answer
95 views
If a query reads more pages than the InnoDB buffer can hold, are all the previous pages evicted?
If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?
0
votes
1answer
2k views
Data Synchronization process in Database Mirroring and Alwayson Groups SQL Server
I have few questions on Database Mirroring and Alwayson, i am reading about always on and i found below data synchronization process from primary replica to secondary replica in msdn,
Quesitions:
...
1
vote
0answers
27 views
MySQL optimizing buffer_pool_size among buffer_pool_instances - where is the trade off? [duplicate]
I'm looking to optimize MySQL INNODB by tuning buffer pool individual instances.
According to the MySQL documentation:
"The total size you specify is divided among all the buffer pools. For ...
3
votes
2answers
84 views
Why are certain objects loaded into Buffer?
I have been experimenting on my test environment with clearing buffer cache via
DBCC DROPCLEANBUFFERS
Before any work is done or operational queries run the buffer is instantly starting to ...
1
vote
1answer
3k views
Mysql innodb_log_buffer_size change
I'm new to Mysql, and i'm reading this in a book: "A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big ...
10
votes
3answers
3k views
How does SQL Server handle the data for a query where there is not enough room in the buffer cache?
My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache than there is space available? This query would contain multiple joins, so the result set ...
