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.
28
questions
24
votes
3
answers
11k
views
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 ...
- 395
43
votes
6
answers
56k
views
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?
- 11.5k
43
votes
3
answers
18k
views
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 ...
- 3,396
14
votes
1
answer
31k
views
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. ...
- 2,226
11
votes
1
answer
13k
views
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?
...
- 1,452
6
votes
1
answer
756
views
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 ...
- 194
3
votes
1
answer
6k
views
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).
...
- 6,178
50
votes
5
answers
35k
views
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:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
- 1,973
19
votes
2
answers
13k
views
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 ...
- 873
4
votes
1
answer
3k
views
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 < ...
- 41
15
votes
4
answers
99k
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.
...
- 1,295
10
votes
1
answer
3k
views
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" ...
- 255
9
votes
1
answer
2k
views
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 ...
- 2,323
7
votes
2
answers
4k
views
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 ...
- 171
6
votes
2
answers
2k
views
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 ...
- 163
5
votes
1
answer
2k
views
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 ...
- 181
5
votes
5
answers
10k
views
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 ...
- 53
4
votes
1
answer
2k
views
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 ...
- 61
3
votes
1
answer
19k
views
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
...
- 215
2
votes
2
answers
221
views
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 ...
- 268
1
vote
0
answers
558
views
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 ...
- 121
1
vote
2
answers
7k
views
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 ...
- 171
1
vote
2
answers
9k
views
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......
- 111
1
vote
1
answer
986
views
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 ...
- 531
0
votes
1
answer
574
views
How to cache select with variables? (MySQL)
Context:
I have complicated very often executed query:
CREATE OR REPLACE VIEW ordered_words AS SELECT word.id
FROM category JOIN frequency ON category.id = frequency.category_id
JOIN word ON ...
- 209
0
votes
1
answer
257
views
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 ...
0
votes
1
answer
1k
views
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 ...
- 433
-1
votes
1
answer
387
views
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
...
- 113