The tag has no wiki summary.

learn more… | top users | synonyms

2
votes
1answer
39 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
33 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
32 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 ...
2
votes
0answers
70 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
40 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
149 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
24 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
73 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
56 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
347 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
63 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 ...
0
votes
0answers
59 views

Caching data with MySQL and asp.net 2.0

I want to cache frequently used data. Is there simplest way to do this with asp.net itself? I am using framework 2.0. If i do this with MySQL technique how about innodb?? will this allow me to cache ...
3
votes
1answer
3k 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
45 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 ...
1
vote
1answer
828 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
500 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
113 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
469 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
163 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
3k 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
75 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
23 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
120 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
224 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
237 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
354 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
70 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
105 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
399 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 ...
17
votes
5answers
6k 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
302 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. ...
4
votes
1answer
2k 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? ...
21
votes
3answers
6k 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
votes
2answers
4k 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. ...
0
votes
1answer
93 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 ...
1
vote
1answer
569 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 ...
1
vote
2answers
2k 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 ...
0
votes
1answer
43 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 ...
5
votes
2answers
307 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 ...
1
vote
1answer
931 views

What are the pros and cons of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE

I have multiple questions For Oracle Database 11g Release 11.2.0.1.0 what are the benefits of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE. How will the CLIENT side cache be kept sync if ...
8
votes
2answers
1k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
4
votes
1answer
176 views

Can I partially invalidate the MySQL table cache?

I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB. After several hours, MySQL invalidates the entire table from cache, while < ...
9
votes
2answers
2k 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 ...
2
votes
1answer
624 views

Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?

I have some tables that are used primarily to keep historical data, or that are only used once a day. I do not want any operations on these tables to disrupt the more important tables present in ...
2
votes
2answers
246 views

Stored procedure compilation blocking due to many parameters to sp_executesql

I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: ...
1
vote
1answer
126 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 ...
1
vote
1answer
124 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
8
votes
3answers
4k views

How to boost performance of virgin queries in MS SQL Server?

I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
1
vote
1answer
85 views

how to use mysql temporal cache tables

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table. Create table ...
2
votes
1answer
63 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 ...