Questions tagged [buffer-pool]
The buffer-pool tag has no usage guidance.
0
votes
0answers
24 views
SQL Server Resource Governor for Buffer Pool [duplicate]
We plan to have multiple databases on a Server Instance. Does Resource Governor allow buffer pool to be allocated for each database in SQL Server 2017?
1
vote
0answers
13 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
76 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
140 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
43 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
63 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
53 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
134 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
82 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
105 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
52 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
30 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)...
2
votes
3answers
1k 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 ...
1
vote
1answer
423 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
-----------------...
1
vote
0answers
284 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 ...
4
votes
4answers
12k 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
44 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
227 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 ...
2
votes
2answers
609 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
36 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
59 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 ...
3
votes
1answer
5k 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
34 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
78 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
1k 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
20 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
81 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
2k 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
2k 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 ...
1
vote
3answers
61 views
Identify cached data for a specific query in data cache
I'm trying to identify, if possible, how long it takes for data being cached by a stored procedure to be taken out of the cache.
I know there is the PLE counter in sys.dm_os_performance_counters, but ...
3
votes
1answer
396 views
select data which is bigger than buffer pool size
Imagine we have a table which has 100 Million rows and 80 GB size.
Every rows has Text column and multiply integer columns.
we set Innodb_buffer_pool_size to 40G
I executing this Query:
select ...
1
vote
1answer
1k views
What are the different ways to Warmup InnoDB Buffer Pool in MySQL 5.6?
Warming up a cold MySQL instance is moving all data or the data with the highest probability of getting used to main memory. And, yes, by main memory, I mean InnoDB Buffer Pool. The Buffer Pool is ...
0
votes
1answer
219 views
Innodb_buffer_pool_bytes_data increases day by day even after delete the data
We have MySQL server V5.7.11 in which the Innodb_buffer_pool_bytes_data is getting increasing day by day. Initially we have set innodb_buffer_pool_size to 8G but we got error of max connection reached,...
1
vote
3answers
6k views
Page life expectancy very low on certain time?
Everyday we receive an alert on a specific period (03:00 AM - 04:00 AM) indicating that Page life expectancy is too low :
SCOM :Alert: SQL DB 2008 Engine Page Life Expectancy is too low
We have ...
0
votes
1answer
2k views
memory leaks in innodb
I have MySQL server 5.6 and I use stress test to evaluate the performance of it. after the test the result of mysqltuner.pl was like below :
root@master-1:/home/debian# perl mysqltuner.pl
Please ...
0
votes
1answer
324 views
MySQL 5.7 not accepting values of innodb_buffer_pool_instances greater than 1
I've been tweaking the values of the buffer pool size (innodb_buffer_pool_size) and the number of buffer pool instances (innodb_buffer_pool_instances) in the my.ini initialization file to optimize ...
0
votes
2answers
403 views
MySql not using Buffer Pool
I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than ...
0
votes
1answer
279 views
Does innodb_data_reads include reads from the buffer pool?
We have set up some monitoring on many of our databases that serve many different applications. It seems that they all report back more writes than reads when in reality that is very unlikely. I ...
1
vote
0answers
419 views
Does “CREATE INDEX WITH DROP_EXISTING” use the bufferpool for the recreated index?
We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message:
"Msg 802, Level 17, State 2, Line 30
There is ...
6
votes
4answers
893 views
How can I Debug a Buffer Issue?
I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms.
I am running this every minute on my server (to track ...
10
votes
4answers
23k views
Optimal Number of MySQL InnoDB Buffer Pool Instances
Server Characteristics
Total system RAM: 8GB (running MySQL + other stuff than MySQL on it i.e. not dedicated to MySQL)
Number of CPU Cores: 6
I have data in the db amounting to about 2GB
I have ...
6
votes
2answers
2k views
Buffer Size Changing?
I have a production database that is experiencing wildly fluctuating Page Life Expectancy (PLE) issues. (It crashes to zero at random times.)
I have been researching the PLE issue and have found ...
4
votes
2answers
947 views
Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries
I'm systematically (1/2 times per day) experiencing a freeze in the DB server.
Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
1
vote
0answers
82 views
AlwaysOn AG Live Migration
A couple of thoughts on AlwaysOn AG migrations:
Is there a reason why Microsoft didn't implement similar technology to Hyper-V Live Migration to AlwaysOn AGs so that the buffer pool and cache are ...
0
votes
0answers
703 views
Should innodb_buffer_pool_size be much larger than the innodb_buffer_pool_bytes_data?
Our old server had 32GB total RAM, 18GB allocated to innodb_buffer_pool_size.
It also had 10GB for the php_memory_limit.
The new one had 16GB RAM, 9GB for innodb, 5GB for php. The same application ...
2
votes
1answer
3k views
In Sql Server what is the difference between VAS and Buffer Pool?
My understanding:
In Sql Server,
Buffer Pool is where all the Data/Proc/Log Cache, etc are located.
Virtual Address Space is a logical location that refers to physical locations (either in RAM or ...
8
votes
2answers
828 views
Microsoft SQL Server 2012 Mirrors Go Into Disconnected
I have some Microsoft SQL server 2012 mirrors, without a witness in asynchronous mode, that are sporadically going into disconnected. I am running SQL Server 2012 11.0.5058. Looking at the logs on ...
6
votes
3answers
2k views
Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?
I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
3
votes
1answer
3k views
dbcc memorystatus - buffer pool target on VM higher than max memory
I'm doing some digging into potential memory/configuration problems on a sql server 2012 SP2. I stumbled upon something that seems a bit strange in the performance counters as well as the dbcc ...
1
vote
1answer
120 views
Does InnoDB Log Files take RAM space or Disk Space
When I set InnoDB Log File size to 512M, does it take the space (512M * 2) from RAM or the SSD Disk space?
When I explored, I can see that the log files are located inside /var/lib/mysql folder. So ...
