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.
159
questions
0
votes
1answer
41 views
PostgreSQL RDS: Slower than expected performance when large table / index is not in cache
I have a fairly large table, 43GB and a 14GB index, that consists of time series cost data. I am querying by date and summing the amount. When this data is not in the cache (either OS or Postgres) the ...
0
votes
0answers
14 views
Postgresql POD Master/Slave don't clean the cache when Client stopped sending data
I'm running a Performance Stress test on our backend (NODE JS) using a client(JAVASCRIPT) that generates the stress , with a Postgres DB.
I've stopped the client from sending data after 5-6 hours , ...
1
vote
1answer
46 views
What's the correct database behavior when two transactions change the same row/value?
I'm writing a database LRU cache in C++ for an embedded NoSQL database to solve a performance problem, and I'm trying to understand the correct assumed behavior and philosophy behind it.
Say there's a ...
1
vote
0answers
34 views
How to prioritize indices in Postgres cache?
The server config:
RAM = 16 GB
#CPUs = 6
The Postgres config:
There is only 1 Postgres instance. I.e. no partioning, no clustering, ...
shared_buffers = 5GB
work_mem = 10MB
maintenance_work_mem = ...
3
votes
2answers
455 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 ...
2
votes
0answers
65 views
Source (eg.statement) associated with security memory cache entry (USERSTORE_TOKENPERM / TokenAndPermUserStore)
We are having a security memory cache bloating issue, known as USERSTORE_TOKENPERM or TokenAndPermUserStore. At two different instances (SQL Server 2019, SQL Server 2014 – patched to the latest SP &...
4
votes
1answer
100 views
Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows
I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
0
votes
1answer
62 views
Create cache table [closed]
I need help with SQL Server. There is a task scheduled to run an .exe file. After the exe finish execution, I can get the logs below:
2021-03-05 22:45:00 : Create cache table
2021-03-05 22:45:00 : ...
0
votes
1answer
53 views
Does daily pg_dump mess up postgres cache?
I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory.
Performances are way worse than on my previous provider, and ...
0
votes
1answer
26 views
Does Sql Server caches parameterized anonymous blocks?
"Anonymous block" is a term from Oracle. But speaking of the thing for sql server
Declare
....
Begin
begin trans
. . .
commit
End
If I send this SQL in parameterized form to ...
1
vote
0answers
383 views
MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?
I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
4
votes
1answer
320 views
Does PostgreSQL always sequentially scan pages in the same order?
Yet another question from my discovery of the great new EXPLAIN options in PostgreSQL. This one focuses on the BUFFERS option.
Here is EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ...
0
votes
1answer
444 views
Is there any way to disable MongoDB's cache?
I'm not getting recent data when querying, and I believe it has to do with cache.
Probably, MongoDB's cache invalidation fails somewhere. I'd like to disable it completely and I don't care about ...
1
vote
4answers
69 views
Load index to cache is not using memory available in the key_buffer_size variable
Good afternoon folks,
I have a problem when trying to load the indexes from the MyISAM tables into MySQL memory, even though there is space available in the key_buffer_size variable, it stops loading ...
-1
votes
1answer
148 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
...
4
votes
1answer
493 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 ...
1
vote
1answer
36 views
Is filling up plan cache causes a decrease in space allocated for data cache?
SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly.
My question: ...
1
vote
1answer
29 views
Caching derived dataset automatically in SQL Server
I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there ...
0
votes
1answer
41 views
Is it possible to index or cache non-immutable function (based on date)
I have a function in my Postgres database where the calculation is based on how old a record is - so given the same parameters, it always outputs a different value.
However, I'd like to index or ...
0
votes
1answer
90 views
SSRS Report returning old data
I have created and tested a report in Visual Studio and it returns data from a SQL server database.
Once uploaded to SSRS, the report returns old data.
What would I need to check as it appears the ...
1
vote
2answers
255 views
How to clear SQL Agent Job Scheduler Cache
I have to submit tickets to alter job scheduling, so rather than a walk through of the GUI I try to update msdb..sysschedules via tsql script. I always see the alterations immediately via the GUI. ...
0
votes
0answers
107 views
Firebird begin backup and forced writes off, timestamp update delayed
We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 ...
2
votes
1answer
55 views
Can we set the fixed size for both plan cache and data cache?
As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?
7
votes
2answers
3k views
How SQL Server caches data in memory
I have couple of questions on how caching of data works
Imagine we have below situation:
Server restarted or we just ran DBCC DROPCLEANBUFFERS
We have a Table1 that is 50 GB and has columns A, B, C, ...
1
vote
0answers
271 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 ...
2
votes
2answers
907 views
Does Log File contain both committed and uncommitted transactions?
My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
0
votes
1answer
26 views
Amount of used cache / free cache
I need to find out how much cache is used, and if there is free cache available.
Background:
Queries on a production server take a lot of time when the server is under heavy load. I suspect that ...
1
vote
1answer
1k views
Result Cache disabled in Oracle 12c Enterprise - BYPASS status
I have two database instances, development and production that are fairly identical. On production, result cache is working fine, in development it is not. The value for the database parameter ...
0
votes
1answer
394 views
Prepare statements plan invalidation in postgresql
Postgresql caches plans for prepared statements and their subsequent executes. see:
https://www.postgresql.org/docs/current/sql-prepare.html
https://stackoverflow.com/questions/7142335/how-does-...
2
votes
1answer
98 views
PostgreSQL. Unstable execution time hugely exceeding planning time
PG version: 10.9
Schema:
create table core_bundle
(
id serial not null constraint core_bundle_pkey primary key,
dt_add timestamp with time zone not null,
dt_upd timestamp with time zone ...
-4
votes
1answer
571 views
SQL Server database slow performance [closed]
I have a SQL Server database in production, and typically the performance of the application connected the database is satisfying. For some days many clients were complaining about slow performance of ...
0
votes
1answer
256 views
How to cache inserted data with ProxySQL to avoid delays
we are using MariaDB 10.2 with Galera plugin. Cluster consists of 4 nodes - 3 are busy service complex queries and 1 is more for a backup.
Recently we came across ProxySQL and this sounds very ...
8
votes
3answers
4k views
Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?
Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
1
vote
2answers
361 views
Are Cataloge and relation cache in PostgreSQL per connection or global to the server?
I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
9
votes
1answer
519 views
SQL dumping all pages from buffer cache every few minutes
I have a single SQL2012 SP4 node running several databases.
The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box).
Every few minutes SQL dumps the whole ...
0
votes
1answer
41 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 ...
0
votes
1answer
33 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
...
1
vote
1answer
503 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
654 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 ...
0
votes
0answers
227 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.
...
1
vote
1answer
306 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 ...
0
votes
1answer
712 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 ...
2
votes
0answers
453 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 ...
1
vote
1answer
543 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 ...
2
votes
1answer
228 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 ...
0
votes
1answer
306 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 ...
0
votes
0answers
96 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 ...
2
votes
1answer
65 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.
...
14
votes
4answers
69k 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.
...
8
votes
3answers
1k 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 ...
