The tag has no usage guidance.

learn more… | top users | synonyms

6
votes
1answer
129 views

Procedure Cache Dropping

We've experienced an issue with SQL Server dropping proc cache out of the blue. I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks. It does happens ...
9
votes
1answer
425 views

Where does an execution plan come from?

Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
1
vote
1answer
25 views

What are the consequences of enabling slow_query_log and flushing the cache on mysql?

I have a large database that is performing poorly recently. I want to enable the slow_query_log to investigate the queries and flushing the cache but I need to know what are the consequences of doing ...
0
votes
1answer
48 views

Mysql only using 10% of cache [duplicate]

I have a dedicated Mysql server that only uses 1.1 GB out of 16 GB ram (picture link 1). I belive this leads to my Disk I/O utilization beeing at 75% peaks (picture link1). How can i configure sql ...
1
vote
1answer
29 views

What will happen with cached index or table when it is modified, will it stay in cache?

if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?
0
votes
0answers
15 views

Optimise communication to check record status

Imagine an universe full of products. I have an app that electronically marks products and saves this status into the server DB. The app may be used from different devices so each one will mark ...
0
votes
2answers
42 views

MySQL query cache with time based expiration

The database we use has a read and write intensive load. Some read queries are heavy. Unfortunately the Query Cache cannot be used as it is invalidated too quickly. We wanted to avoid the classic ...
1
vote
1answer
284 views

Would daily/weekly db memory cache clearing be of use?

So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use? (Ignoring the fact I probably should be looking at reducing size of database/indexes) ...
1
vote
0answers
47 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
vote
1answer
50 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 ...
1
vote
0answers
62 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
43 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 ...
5
votes
1answer
60 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, ...
2
votes
0answers
88 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
105 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
44 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
52 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
145 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
33 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
246 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
33 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
363 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
81 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
353 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
340 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
331 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 ...
1
vote
2answers
131 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 ...
5
votes
1answer
293 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
314 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
86 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
248 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
126 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
57 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
525 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
28 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
103 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
84 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 ...
5
votes
2answers
1k 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
128 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
12k 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
89 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
965 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
269 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
708 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
475 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
7k 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
109 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
29 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
225 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, ...