The tag has no usage guidance.

learn more… | top users | synonyms

1
vote
0answers
21 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 ...
6
votes
2answers
1k 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 ...
4
votes
1answer
28 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 ...
1
vote
0answers
13 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. I have some algorithms that are based on hash tables, which cause much cache misses by ...
0
votes
1answer
176 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 ...
2
votes
0answers
50 views

Denormalization vs Cached Data [closed]

I'm in a dilemma about denormalization of the database vs. caching the joint query results of the database. We have 2 tables. Let's say A and B. A may have many of Bs but B can be belong to only one ...
0
votes
1answer
37 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 ...
0
votes
1answer
39 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 ...
2
votes
1answer
70 views

SQL Server Cache population on server / service startup

We have upgraded our SQL Server instance with buckets of RAM, so it can now much more effectively cache . The issue is that it takes time (days) to populate the cache. As far as I understand this is ...
0
votes
1answer
43 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 ...
2
votes
0answers
91 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 ...
4
votes
1answer
225 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" ...
1
vote
1answer
21 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 ...
2
votes
2answers
185 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 ...
1
vote
1answer
25 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 ...
25
votes
5answers
12k 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 ...
3
votes
1answer
258 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 ...
1
vote
0answers
124 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 ...
0
votes
0answers
58 views

MySQL MEMORY Engine vs NoSQL in-memory

I have a WebService that runs a hybrid functionality of Secure WebSockets and HTTPS REST API. Both functionalities translate the calls to the same under-the-hood generic calls, wherein the WebService ...
0
votes
1answer
210 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 ...
3
votes
2answers
121 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 ...
0
votes
1answer
263 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 ...
0
votes
2answers
94 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 ...
0
votes
0answers
168 views

table_open_cache

I know table_open_cache, max_connections, and open_files_limit are related. Is there a tried and true way to set table_open_cache correctly? I have raise it three times and still MySQL Enterprise ...
1
vote
1answer
67 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 ...
3
votes
2answers
6k 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. ...
1
vote
1answer
178 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 ...
1
vote
2answers
405 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, ...
5
votes
1answer
4k 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
vote
0answers
50 views

Flush procedure cache or drop and create?

Okay I had a very strange issue today on SQL Server 2005. We had a stored procedure update that added new parameters to part of it. One database out of ~150 or more seem to not work properly. Using ...
3
votes
2answers
3k views

Does Oracle cache queries when using a database link

Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized. I can't find any information ...
1
vote
1answer
183 views

Command line switch to disable Coherence?

We have a Java app that happens to have the coherence jar in the path. My application doesn't need it, so I'd like to pass some type of -D variable to the JVM to tell it to ignore coherence, or ...
4
votes
2answers
877 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 ...
0
votes
0answers
26 views

Database cache problem

I have server based on Linux 64 Bit centos 6.5 and Mysql innoDB engine I created database for php script require database installation , after i successfully finished installation for first time i ...
1
vote
1answer
85 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 ...
0
votes
1answer
81 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 ...
13
votes
2answers
4k 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 ...
1
vote
1answer
91 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 ...
4
votes
1answer
9k 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. ...
26
votes
3answers
10k 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 ...
1
vote
0answers
74 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 ...
3
votes
1answer
133 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 ...
2
votes
1answer
2k 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 ...
1
vote
1answer
215 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 ...
0
votes
1answer
853 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 ...
1
vote
0answers
613 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", ...
-4
votes
0answers
97 views

Why is LRU is not suitable for joining 2 relations in DBMS [closed]

In DBMS, can someone please explain how the LRU (least recently used) buffer replacement policy acts when joining 2 relations in a database? I have read that LRU is not efficient when joining two ...
2
votes
1answer
354 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 ...
1
vote
1answer
6k 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 ...
0
votes
0answers
26 views

Should store procedures be cached?

Should store procedures be cached in MySQL? If so, How long it is stay in cache? In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...