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
1
vote
0
answers
988
views
What causes the creation of such a large number of Galera cache files?
I manage a Percona XtraDB 3-node cluster. Recently the MySQL datadir of one of the nodes (not the one from which the cluster has been bootstrapped) started being filled up with gcache.page.00nnnn ...
- 1,314
1
vote
1
answer
113
views
Caching A Query Daily
How to ensure a query runs only once a day in MySQL? Once it runs, returned result should be cached, and even though returned data were changed in database, my query's results should remain the same ...
- 11
1
vote
0
answers
623
views
Do you need external caching for a Postgres database?
I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
- 111
8
votes
1
answer
133
views
Does MySQL/InnoDB use table caches when dealing with foreign tables?
TL;DR: Does a table referred to via schema.table get held in table cache?
I have a large number of client databases that our application connects to based on an http request. These are used for every ...
- 251
5
votes
1
answer
1k
views
To minimize Cache misses in PostgreSQL?
You can calculate cache misses as described here.
However, I am interested in how to minimize the phenomenon in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, ...
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
0
votes
1
answer
118
views
Cache statistic data in SQL Server
Currently my team develop additional web app to let customers analyze user statistics to theirs portals.
So we have a table with all logins. It contains about 100 millions of rows. One user could ...
- 103
0
votes
1
answer
1k
views
Confused over table_open_cache
I've been reading about the table_open_cache and I'm slightly confused about how big it should be, I've seen conflicting advice. My DB is running on MyISAM and has 199 tables.
I ran into an issue ...
- 113
2
votes
0
answers
798
views
SELECT stuck giving results from the query result cache, though table has changed
A process that runs every two minutes or so started to misbehave. We noticed it was because the cursor fetched repeatedly 0 rows, even if indeed there were new rows satisfying the conditions in the ...
- 173
1
vote
1
answer
671
views
Reducing query cache for mostly INSERT-heavy database?
What sort of MySQL query cache should I choose considering the following...
Large, rapidly growing database (I got 500,000 rows).
UPDATEs practically never happen.
SELECTs are rare, but when they do ...
Mitchfizz05
3
votes
2
answers
2k
views
Can I tell MySQL what to cache?
Pretty straightforward question:
Let's say I have a specific query that I know is regularly used, can I tell MySQL to always cache it "automatically" ... i.e. once the cached query results are ...
- 363
1
vote
1
answer
135
views
MySQL - stay in cache priority for queries
Is there a way to tell MySQL that some queries has highter priority in cache and mustn't be popped out until it's data become invalid?
The problem is that innodb cache size is rather small and data ...
- 365
1
vote
0
answers
3k
views
Reducing the size of a PostgreSQL Database, utilizing a process other than full VACUUM
We have a Postgres 8.4 installation which hosts both our Defect tracking and Code management (Surround) systems. We are looking at converting and migrating Surround to MSSQL on another server. Our ...
- 11
3
votes
1
answer
10k
views
Lowering MySQL Memory Usage without Restart
We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...
- 31
0
votes
1
answer
2k
views
Pagination, check number of selected records
I am working on pagination for a complex join query and unfortunately I am not sure what's the best way to go... Right now I am thinking about the following solution.
Imagine that SELECT id... is the ...
- 127
1
vote
1
answer
1k
views
Columns count in SELECT query and SQL Server buffer cache
Let's assume that I have a table with 30-50 millions records. It has ~40 columns. Currently when I request data it always requests all columns because of legacy ORM that we use.
I know that if we ...
- 113
2
votes
2
answers
3k
views
How to determine if open_table_cache is set properly?
Does anyone know what this really means? (from MySQL docs)
You can determine whether your table cache is too small by checking
the mysqld status variable Opened_tables, which indicates the number
...
- 79
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
5
votes
1
answer
2k
views
How can I identify queries that are producing cache misses in Postgres?
I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory. The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%.
I am trying ...
- 255
1
vote
1
answer
560
views
Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?
I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response time ...
- 113
3
votes
1
answer
1k
views
MySQL: can a table get stuck in the table cache?
I've asked this question on the ServerFault stackexchange, someone directed me here.
I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that ...
- 153
3
votes
3
answers
324
views
Stored Procedure slow after weekend
When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on.
However, after running the procedures 10-...
- 133
1
vote
2
answers
2k
views
Oracle 11.2 using too few memory
it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012).
The problem is that with more than doubled cpu power, ...
- 11
1
vote
1
answer
300
views
PostgreSQL: cache, disk, replication. What order?
I am thinking of using two servers in Master/Slave replication
I want to add following condition: RAM size = dataset size * x, where x > 2.
After that, I plan to force PGSQL to keep all data in RAM ...
- 113
0
votes
1
answer
210
views
MySQL Query Cache - what type of queue is used?
I am wondering what type of queue is MySQL Query Cache using.
When there is new query and cache is full which query is deleted? Oldest one? One with fewer number of hits? Or maybe there is some ...
- 259
6
votes
2
answers
4k
views
Sudden performance decrease in SQL Server
I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no ...
- 61
1
vote
1
answer
432
views
Battery backed write cache and Postgres
I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that ...
- 143
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
2
votes
0
answers
164
views
mysql vs mongodb cache/load balancing
I have a system where users can 'like' content. There will likely be many hundreds of these likes going on at once. I'd like it to be AJAX driven so you get an immediate response.
At the moment I ...
- 21
6
votes
1
answer
5k
views
How bad is query plan pollution (too many equivalent plans in cache)?
Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well.
I noticed some old modules of a system have non-optimal SQL ...
- 500
1
vote
1
answer
2k
views
Calculating time spent on query cache lock
How can I calculate the total time my MySQL database is spending Waiting for query cache lock ?
I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the ...
- 969
1
vote
1
answer
699
views
Query_cache doesn't work with join
I have a simple join query, but for some reason query_cache won't cache it!
SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
- 141
1
vote
0
answers
1k
views
SELECT + INSERT + Query Cache = MySQL lock up
MySQL server seems to constantly lock up and stop responding on certain types of queries and eventually (after couple of minutes of not responding) give up with an error "MySQL server has gone away", ...
- 111
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
4
votes
1
answer
758
views
Buffer usage for index with no reads
Using SQL Server 2008 R2, the main transactional table in a vendor database is massive compared to all others and has about 14 indexes. Some of these indexes don't get used in our environment, but we ...
- 143
1
vote
1
answer
2k
views
Temporarly disable cache in oracle 11g
I want to disable the caching for my session in oracle 11g to see how long a query takes.
Is this possible or is there a better way for meassuring the time a query takes to run?
- 125
1
vote
1
answer
119
views
If MySQL isn't caching, then what is?
I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case.
mysql> show variables like 'query_cache_size';
--------------
show variables like '...
- 173
3
votes
1
answer
358
views
Postgres caching and bytea
I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB data....
- 131
4
votes
1
answer
1k
views
Reindexing and its effects on SQL cache
Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL ...
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
1
vote
1
answer
698
views
Does MySQL's MyISAM use cache before insert?
I want to test two different disks, one of them is HDD and another is SSD.
I use exactly the same information and app for both servers. They are exactly the same, just one of them is SSD and another ...
- 141
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
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
8
votes
2
answers
41k
views
query_cache_type = 0 vs query_cache_type = 1
Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf.
However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. (...
- 267
0
votes
1
answer
248
views
Does SQL Server cache data results for DAO?
I was surprised to be confronted with the idea that using .NET and DAO one could make use of the SQL Server data cache in a way not at all possible with stored procedures (which benefit from the plan ...
- 3
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
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
0
votes
1
answer
64
views
Oracle scheduler chain priority in data warehouse applications
The scenario is as follows:
We have a data warehouse type application that relies on the Oracle job scheduler and a Chain. This chain is responsible for generating materialized views (caches) that ...
- 201
6
votes
2
answers
393
views
mysql performance / cache configuration enigma [closed]
I have two mysql 5.1 instances (say A, B) hosting the same database schema. If I run (with mysql workbench) the same query on both instances I don't understand why I get very different response times ...
- 161