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 ...
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 ...
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
38 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
40 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 ...
0
votes
1answer
44 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 ...
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 ...
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
59 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
180 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 ...
0
votes
1answer
211 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 ...
0
votes
1answer
265 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 ...
4
votes
1answer
227 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" ...
3
votes
1answer
259 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
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 ...
1
vote
1answer
180 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 ...
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 ...
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 ...
1
vote
2answers
406 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, ...
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 ...
4
votes
2answers
880 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 ...
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. ...
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 ...
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 ...
0
votes
1answer
855 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
1answer
216 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 ...
1
vote
0answers
614 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", ...
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 ...
-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 ...
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 ...
0
votes
0answers
187 views

Req: Typical location of mysql query cache

My MVC framework (CodeIgniter) wants to know where my database (mysql 5.5) writes its query cache. My Ubuntu installation is unexceptional. Would that be /var/lib/mysql/ or further down the chain, ...
2
votes
0answers
356 views

Multithreading caching in SQL CLR

Is there any mechanism available to a SQLCLR assembly for caching that will still work when the assembly is registered as "safe"? The cache needs to be thread-safe, so it can be shared by multiple ...
4
votes
1answer
314 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 ...
0
votes
0answers
537 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?
1
vote
1answer
79 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 ...
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 ...
4
votes
1answer
552 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 ...
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 ...
1
vote
1answer
442 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 same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD. ...
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? ...