Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

114 questions
25
votes
5 answers

Making sense of INNODB buffer pool stats

After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. Here's the output from show engine innodb status\G…
Safado
  • 479
  • 1
  • 6
  • 17
17
votes
4 answers

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. I have a SQL performance tool grafana which…
user9516827
  • 1,345
  • 3
  • 19
  • 41
17
votes
4 answers

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 InnoDB Buffer Pool Size set to 4GB Which is…
Adergaard
  • 363
  • 1
  • 3
  • 8
11
votes
2 answers

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster (around 1s). After a few hours, a slow/then fast…
Brann
  • 325
  • 1
  • 3
  • 9
10
votes
3 answers

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 does not exist in this format already on disk,…
Dustin
  • 143
  • 2
  • 11
8
votes
2 answers

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 the mirror side I see Failed to allocate BUFs:…
John Wesley Gordon
  • 233
  • 1
  • 9
8
votes
1 answer

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 from 140 GB cached DB memory to 60 GB. And after…
Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
7
votes
2 answers

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 data and index pages needed to fulfill a query…
Rauf Asadov
  • 1,313
  • 14
  • 36
6
votes
3 answers

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 gb, and the frequently queried data is ~1.25…
Jeff Widman
  • 305
  • 3
  • 10
6
votes
1 answer

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 a way to manage how long they persist in memory…
J.D.
  • 40,776
  • 12
  • 62
  • 141
6
votes
4 answers

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 this issue): SELECT @ple = CAST([cntr_value] AS…
Vaccano
  • 2,550
  • 5
  • 31
  • 56
6
votes
2 answers

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 something that seems to point to a VMWare issue, but…
Vaccano
  • 2,550
  • 5
  • 31
  • 56
5
votes
1 answer

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 ways to maximize server performance after a…
Mark Henry
  • 123
  • 1
  • 5
4
votes
1 answer

Finding swap causes of MySQL

In my centos 6.3 server I have a MySQL 5.5.33 database. It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows. I refactored my schemas and added indexes for my slow logs. My average query time is 20-30 ms. And my database performs…
trante
  • 149
  • 1
  • 2
  • 5
4
votes
2 answers

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations increased from 720 to 822 seconds (14%…
Buttle Butkus
  • 1,230
  • 3
  • 11
  • 21
1
2 3 4 5 6 7 8