Questions tagged [cache]

A cache is a mechanism for temporarily storing (caching) data in memory in order to reduce access time to data stored on disk drives.

Filter by
Sorted by
Tagged with
24 votes
3 answers

Is the overhead of frequent query cache invalidation ever worth it?

I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
Craig Sefton's user avatar
43 votes
6 answers

PostgreSQL: Force data into memory

Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
Adam Matan's user avatar
  • 11.5k
43 votes
3 answers

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
RThomas's user avatar
  • 3,396
14 votes
1 answer

Difference between FreeProcCache and FreeSystemCache

Currently I run DBCC FREEPROCCACHE when I want to wipe information out of the buffer pool between running SQL queries. However, I was reviewing this Technet article referencing DBCC FREESYSTEMCACHE. ...
Sean Long's user avatar
  • 2,226
11 votes
1 answer

Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes

I just started dabbling with the query cache for our CMS. Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free? ...
Nifle's user avatar
  • 1,452
6 votes
1 answer

Procedure Cache Dropping

We've experienced an issue with SQL Server dropping proc cache out of the blue. I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks. It does happens ...
Hpk's user avatar
  • 194
3 votes
1 answer

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
James Jenkins's user avatar
50 votes
5 answers

Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?

I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one: Why ...
SomeGuy's user avatar
  • 1,973
19 votes
2 answers

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
davetapley's user avatar
4 votes
1 answer

Can I partially invalidate the MySQL table cache?

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB. After several hours, MySQL invalidates the entire table from cache, while < ...
Bas Groot's user avatar
15 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. ...
user9516827's user avatar
  • 1,295
10 votes
1 answer

Why does a SELECT statement dirty cache buffers in Postgres?

I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output. From the documentation, "dirtied" ...
D-Rock's user avatar
  • 255
9 votes
1 answer

How much memory is a table taking up?

Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)? For example, suppose I have a table with 30 million records. I'd like to know how many pages ...
ivanmp's user avatar
  • 2,323
7 votes
2 answers

How to calculate cache misses for PostgreSQL

I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
silvavlis's user avatar
  • 171
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 ...
Brandon's user avatar
  • 163
5 votes
1 answer

Pre Caching Index on a large table in PostgrSQL

I have a table with about 10mln rows in it with a primary key and an index defined on it: create table test.test_table( date_info date not null, string_data varchar(64) not null, data ...
Karlson's user avatar
  • 181
5 votes
5 answers

Why is MySQL caching data on our dynamic PHP site?

We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that ...
user1867's user avatar
4 votes
1 answer

user permission cache in session or check from database?

On each page of my websites 2 or 3 permissions have to be checked and I use role-permission model. Which one is better? to check permissions of each user always through database directly or once ...
sheno's user avatar
  • 61
3 votes
1 answer

What is wrong with table_cache hit rate?

In my.cnf I have: table_cache = 524288 open_files_limit = 65535 Both are at max allowed value for mysql config. Both are less than max open file limit: # cat /proc/sys/fs/file-max ...
rahul286's user avatar
  • 215
2 votes
2 answers

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
vettipayyan's user avatar
1 vote
0 answers

Why did mongoDB reads from disk into cache suddenly increase?

This is a mongoDB replica set with 1 primary, 1 secondary and 1 arbiter. Suddenly the reads from disk into cache and cache page evictions increased 2x-3x and stay elevated since then (all metrics ...
Manuel's user avatar
  • 121
1 vote
2 answers

How to set cache on windows 10 pro 64bit machine

When running Mongod I get the following WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor ...
DCR's user avatar
  • 171
1 vote
2 answers

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been cached......
PavelBY's user avatar
  • 111
1 vote
1 answer

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
David Parks's user avatar
0 votes
1 answer

How to cache select with variables? (MySQL)

Context: I have complicated very often executed query: CREATE OR REPLACE VIEW ordered_words AS SELECT FROM category JOIN frequency ON = frequency.category_id JOIN word ON ...
Daniel's user avatar
  • 209
0 votes
1 answer

Mysql only using 10% of cache [duplicate]

I have a dedicated Mysql server that only uses 1.1 GB out of 16 GB ram (picture link 1). I belive this leads to my Disk I/O utilization beeing at 75% peaks (picture link1). How can i configure sql ...
Lars Erik Fagernaes's user avatar
0 votes
1 answer

Why is the result cache always disabled in Oracle 11g

I'm trying to enable the result cache in Oracle 11g. I have used Enterprise Manager to change the result_cache_max_size and result_cache_max_result values to '1M' and '100' respectively (maybe these ...
user1578653's user avatar
-1 votes
1 answer

On reducing MYSQL disk activity

Following this topic, I want to reduce the disk activity for my Moodle database. Currently, the following variables are set innodb_buffer_pool_size 8589934592 innodb_buffer_pool_chunk_size 134217728 ...
mahmood's user avatar
  • 113