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
2 votes
1 answer
180 views

How to know how much memory is used by each DB user in the cache buffer?

Using this query: SELECT A.CACHE_BUFFER_TOTAL, B.CACHE_BUFFER_USED, A.CACHE_BUFFER_TOTAL - B.CACHE_BUFFER_USED AS CACHE_BUFFER_FREE FROM (SELECT BYTES / 1024 / 1024 ...
Isaac PM's user avatar
0 votes
0 answers
29 views

Why MongoDB getting higher memory usage?

This is the sample Python code that I used to write data in MongoDB database. After every connection close, I restart the program and I noticed an accumulation in memory count in the Task Manager. ...
Thiwanka Gunasinghe's user avatar
0 votes
1 answer
255 views

Why the second query is so much faster than the first one?

I have a query that look like this: SELECT DISTINCT "articles"."id", "articles"."company_uuid", "articles"."status", "articles&...
Mio's user avatar
  • 621
1 vote
1 answer
107 views

Which SQL Server component is in charge with checking cached execution plans for a given query?

I understand that whenever SQL server receives a query it looks in the cache plan for any matching execution plans for that query. However, most of the resources that I encountered don't specify ...
Mehdi Charife's user avatar
0 votes
0 answers
72 views

Disk based Key-Value store to use as LRU cache?

I am looking for a key value store to use as a LRU cache for my application. It needs to hold a lot of data (100G - 1000G) which is why I say disk based. Things I have found via Google seem to be ...
David Tinker's user avatar
0 votes
2 answers
39 views

Performance for query filtered by user preference

I have an app, that shows data based on user preference. I will simplify it in this theoretical example: The app shows articles filtered based on user preference for their favorite authors or their ...
Kash's user avatar
  • 103
0 votes
1 answer
58 views

Oracle 19c create table result_cache clause syntax

I could not find the exact syntax for the "Result_Cache" clause in the Oracle 19c documentation for the CREATE TABLE statement. It mentions the values of DEFAULT or FORCE, but not the ...
Mark Stewart's user avatar
  • 1,113
0 votes
1 answer
61 views

Testing strategies when tuning a stored procedure

I'm tuning a stored procedure that is used by the first page that loads whenever a user logs into our website and it's very frequently used. When I run the procedure the first time it runs in about 10 ...
JoeCharles's user avatar
1 vote
1 answer
578 views

How to check how many keys stored in Aerospike?

I am new to Aerospike and exploring AQL and ASADM. I Executed command "show sets" and got response - aql> show sets +------------------+-------------+---------+-------------------+--------...
Vibhav Singh Rohilla's user avatar
0 votes
0 answers
15 views

Keeping up-to-date values aggregated based on some time period

I'm considering a few solutions for a problem involving some sort of a time series data. Imagine: you have a system that keeps track of financial transactions in multiple currencies. To keep it simple,...
Psycho Punch's user avatar
-1 votes
1 answer
60 views

Slow database actions - moving in consolidated server

We are currently proactively correcting performance issues with a specific SQL Server by clearing the procedure cache nightly. In the near future, we are intending to consolidate many databases onto a ...
Kujtim Hyseni's user avatar
0 votes
1 answer
266 views

Postgres hit ratio: should big history tables be removed from the production database?

A database has these cache hit ratios: table A: 0.006 table B: 0.955 table C: 0.023 Tables A and C are history tables. No relationship, large content and no need of fast queries, only few read ...
Slim's user avatar
  • 203
0 votes
0 answers
324 views

PostgreSQL cache function return value

Suppose I have defined a function that is expensive to compute. Is there a way to define an index in PostgreSQL to cache the return value for a given set of parameters? For example, let's say that I ...
Eerik Sven Puudist's user avatar
1 vote
1 answer
97 views

pg_prewarm like funcationality in MySQL

I know in Postgres, we can explicitly load a table into the cache using pg_prewarm extension, is there a similar mechanism/UDF/etc in MySQL? I know MySQL would cache data in shared buffer implicitly, ...
Chujun Song's user avatar
2 votes
1 answer
1k 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 ...
bmck's user avatar
  • 123
0 votes
0 answers
60 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 , ...
JAN's user avatar
  • 101
1 vote
1 answer
111 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 ...
The Quantum Physicist's user avatar
1 vote
0 answers
232 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 = ...
glowfish's user avatar
5 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 ...
Brandon's user avatar
  • 153
2 votes
0 answers
109 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 &...
jericzech's user avatar
  • 945
5 votes
1 answer
253 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 ...
valkmit's user avatar
  • 53
0 votes
1 answer
171 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 : ...
Joerg's user avatar
  • 5
0 votes
1 answer
219 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 ...
Pak's user avatar
  • 101
0 votes
1 answer
58 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 ...
T.S.'s user avatar
  • 196
1 vote
0 answers
1k 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....
P S's user avatar
  • 11
4 votes
1 answer
667 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 ...
dcc310's user avatar
  • 189
0 votes
1 answer
1k 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 ...
sqlbie's user avatar
  • 1
1 vote
4 answers
376 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 ...
alexandredelcol's user avatar
-1 votes
1 answer
339 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 ...
mahmood's user avatar
  • 113
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 ...
Karlson's user avatar
  • 181
1 vote
1 answer
77 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: ...
Rauf Asadov's user avatar
  • 1,243
1 vote
1 answer
102 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 ...
Cowthulhu's user avatar
  • 277
0 votes
1 answer
77 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 ...
AlexMA's user avatar
  • 105
0 votes
2 answers
534 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 ...
Jamesttuk's user avatar
1 vote
2 answers
735 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. ...
Nicholas McQuillen's user avatar
0 votes
0 answers
191 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 ...
Kjell Rilbe's user avatar
2 votes
1 answer
63 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?
Rauf Asadov's user avatar
  • 1,243
7 votes
2 answers
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, ...
Aleksey Vitsko's user avatar
1 vote
0 answers
523 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 ...
Manuel's user avatar
  • 121
2 votes
2 answers
2k 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 ...
Rauf Asadov's user avatar
  • 1,243
0 votes
1 answer
33 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 ...
triton3373's user avatar
1 vote
1 answer
2k 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 ...
Mark Stewart's user avatar
  • 1,113
0 votes
1 answer
779 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-...
Hadi's user avatar
  • 127
2 votes
1 answer
209 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 ...
idle sign's user avatar
  • 123
-4 votes
1 answer
866 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 ...
Asrar Ahmad Ehsan's user avatar
0 votes
1 answer
333 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 ...
Anton Aleksandrov's user avatar
8 votes
3 answers
6k 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?
J.D.'s user avatar
  • 34k
2 votes
2 answers
707 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 ...
Hadi's user avatar
  • 127
9 votes
1 answer
790 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 ...
Charleh's user avatar
  • 231
0 votes
1 answer
98 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 ...
Neeraj Murarka's user avatar