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.
173
questions
0
votes
1
answer
115
views
Redis has "commands" that mutate the various data types it can store. Why? Redis is primarily a cache
If you look at something like https://redis.io/commands#list, you will see that types that Redis stores and supports are fully mutable, atomically, within the data store's memory.
This does not make ...
- 113
0
votes
1
answer
59
views
Caching results of query for part of table that almost never change
In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below):
Query1
...
- 101
1
vote
1
answer
809
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 ...
- 1,928
0
votes
1
answer
1k
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 ...
- 127
0
votes
0
answers
340
views
no performance improvement with pgpool query result caching
I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution.
...
- 451
1
vote
1
answer
526
views
Is any caching mechamism used for non-persisted computed columns?
Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation?
Additional Context
I've always given the advice that if using computed ...
- 449
0
votes
1
answer
1k
views
query_cache_type on mysql galera cluster
I was wondering could I turn on the query_cache_type for mysql galera cluster of two nodes.
The scenario is :
1. Proxysql infront of two master-master galera(mysql) nodes
2. Inserts are coming ...
- 115
3
votes
0
answers
579
views
Should Azure host write-caching be enabled when using MongoDB's WiredTiger storage engine
I have several virtual machines on Azure running a mongoDB replicaSet with the WiredTiger storage engine. I'm not sure about the proper host caching option to set on the disks that contain the MongoDB ...
- 31
1
vote
1
answer
591
views
Reduce query execution time in frequently updated tables
The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets ...
- 111
2
votes
1
answer
235
views
What can data cache contain besides pages from physical disk?
While setting statistics io option ON, I see this result
physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, but ...
- 2,120
0
votes
1
answer
453
views
mysql - 35GB binlog files are cached in OS cache
I have a mysql database with 5.6.
Binary logs are enabled and it has 5 slaves.
GTID mode enabled.
Maximum binlog file size is 1GB.
sync_binlog = 0
When I check the free -gh
total ...
- 1,910
0
votes
0
answers
101
views
Does PostgreSQL do the caching like applications do? [duplicate]
Applications usually cache data on an implementation of hash data structures to speed up the query, typically Memcached or Redis. Does relational databases also do the same things internally?
For ...
- 477
2
votes
1
answer
91
views
Caching queries that last longer then then X seconds
Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.
...
- 121
15
votes
4
answers
100k
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,305
8
votes
3
answers
2k
views
Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?
I have to find the total time taken for query to execute in SQL-SERVER 2016 when all the data pages should be in Disk only.
To accomplish that , I cleared all buffers in the SQL-SERVER using the ...
- 93
4
votes
2
answers
19k
views
Is it possible to delete cache in Mongo?
My case is that I run several queries in Mongo and measure the response time. I know that Mongo uses Memory Mapped files and cache some data in RAM.
If I create an index and run a query the execution ...
- 175
0
votes
0
answers
546
views
Service broker stuck infinitely looping using outdated activation stored procedure
I was debugging and stuck a bunch of log statements into my activation procedure including a statement that logs the value of @@ROWCOUNT that is used in this manner:
INSERT INTO ###Debug(SomeMessage, ...
- 711
0
votes
1
answer
2k
views
Am I clearing the query cache correctly?
I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working.
I ran a query the first time and it took about 3.5 ...
- 385
-1
votes
1
answer
57
views
SQL Server 2012 cache system? [closed]
I have an application that uses SQL Server 2012.
Even if I change a value directly in the database (not via an application), it takes time until the application sees those changes, even if the ...
- 21
1
vote
1
answer
5k
views
Reasonable values for thread_cache_size in relation with Connections [closed]
I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients ...
- 59
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
12
votes
2
answers
2k
views
Find uncompressed size of all tables in a database
In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'...
- 15.6k
2
votes
0
answers
1k
views
MongoDB + WiredTiger cache usage stuck on 95%
I'm running a mongo 3.4.7 cluster (8 CPU, 120GB ram, primary + 2 secondaries) with ±130 open connections, each one of them to a separate DB with ±50 collections.
After a few minutes of running, cache ...
- 297
1
vote
2
answers
4k
views
mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*))
I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set ...
- 145
1
vote
0
answers
22
views
What kind of object is being checked against the cache when a new query arrives? [duplicate]
After reading several resources, it's not clear to me what kind of object is being checked against SQL Server Plan Cache when a new query arrives.
I see 3 possible options:
The pure query text.
The ...
- 437
0
votes
1
answer
409
views
How to calculate total memory used by one table?
I have innodb_buffer_pool_size=100GB;
show table status where name='test_tb'\G
gives
Data_length: 4686086144
...
- 175
5
votes
4
answers
3k
views
Is our MySQL poor performance related to excessive daily query cache prunes?
We are experiencing a high number of query cache prunes per day, currently 80851746. MySQL really struggles even under a reasonable load of say 50+ concurrent connections.
The host is a Physical ...
- 51
2
votes
1
answer
543
views
Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%
If 100% Buffer and Procedure cache hit ratio is considered not good in general?
I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000)
SELECT (a.cntr_value * 1.0 / ...
- 1,475
0
votes
1
answer
123
views
Disallowing MariaDB query cache except from webserver (JDBC)
I have an application server (Play Framework), webserver (Caddy), and database server (MariaDB with InnoDB) that are running on a single machine for now.
The database is used in two ways:
client ...
2
votes
1
answer
615
views
PostgreSQL 9.6.2 perfomance
I need help for server side optimization.
I think it is a system file cache problem but I'm not sure, maybe I am wrong.
I have 2 servers (S1, S2) with PostgreSQL 9.6.2 on both of them, the database ...
- 121
1
vote
3
answers
12k
views
What exactly is a "thread" in thread_cache_size, Thread_connected and thread_created?
In the MySQL 5.7 manual, I read that the thread cache has a size determined by the thread_cache_size system variable.
The default value is 0 (no caching), which causes a thread to be set up for each ...
- 341
5
votes
1
answer
2k
views
Performance considerations for Caching Aggregate counts
We have an InventoryActivity table that holds transactional changes to item quantity:
CREATE TABLE dbo.InventoryActivity(
InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
...
- 159
0
votes
1
answer
575
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
46
views
Is there possible chache query or save it in memory in MySql? Mayby Should I use something other?
I have complicated query (some joins and ordering), that I want to execute very often. Results of query can change very rarely.
Query gets words form given language and category sorted by frequency, ...
- 209
2
votes
1
answer
1k
views
SSAS Cache Levels
I am using SSAS for reporting statistics on a big amount of data.
Recently I began learning best practices for tuning and I am interesting in how SSAS caches the data.
I have done my research and I'...
- 170
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
0
votes
1
answer
3k
views
Clear cache in Windows
I would like to make some timing tests in my PostgreSQL database. I would like to clear cache every time I run a query to disable influence of caching on my timing results. I've read some topics about ...
- 13
0
votes
1
answer
33
views
MySQL consumes memory for each portion of read
I'm using MySQL instance for read only operations (data migration to another DB).
I'm reading several dozens of millions rows from one table using limited portions of data (100 000 rows) using select ...
- 527
0
votes
1
answer
931
views
SQL Server Performance hit after cache rebuild
After I rebuild my indexes and update my statistics, SQL Server will have to rebuild the execution plan cache to get up to speed with the current setup. So, will the SQL server do this immediately ...
- 339
1
vote
1
answer
2k
views
Table cache hit rate: 0%
I have an AWS RDS Mysql Server with Table cache hit rate's problem. If I do a query on phpmyadmin (also simple query) always do it without cache.
Here the mysqltuner log:
MySQLTuner 1.6.12 - Major ...
- 11
2
votes
2
answers
13k
views
How to load entire database into memory?
I want to load my entire database into memory, but how can I do this? I have about 256 GB memory and my database is about 200 GB, so I can easily handle in memory.
When I do select count(*) from ...
- 351
1
vote
1
answer
5k
views
Memory usage of MongoDB
I've got Django app with mongoengine, which executes some aggregation-queries often enough.
The main problem is that mongod takes all the RAM and then crashes, so no LRU working, mongod just trying ...
- 125
1
vote
2
answers
1k
views
How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?
I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand.
But how do i do it in sqlalchemy ?
Is it possible ?
6
votes
1
answer
758
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
10
votes
1
answer
617
views
Where does an execution plan come from?
Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
- 4,757
1
vote
1
answer
64
views
What are the consequences of enabling slow_query_log and flushing the cache on mysql?
I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
- 11
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 ...
1
vote
1
answer
42
views
What will happen with cached index or table when it is modified, will it stay in cache?
if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?
- 237
0
votes
2
answers
2k
views
MySQL query cache with time based expiration
The database we use has a read and write intensive load. Some read queries are heavy.
Unfortunately the Query Cache cannot be used as it is invalidated too quickly.
We wanted to avoid the classic ...
- 93
1
vote
1
answer
694
views
Would daily/weekly db memory cache clearing be of use?
So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use?
(Ignoring the fact I probably should be looking at reducing size of database/indexes)
...
- 113