|
Our application gets stalled for longtime and becomes unresponsive. Clearing the cache between subsequent queries solves the issue. My Question is In case of multi-threaded scenarios where multiple threads are interacting with database, clearing the cache will impact other threads also in terms of performance Will there also be some data inconsistency since clearing the cache might clear some uncommitted data? Need some explanation in this regard. Thanks, Rohan |
|
Clearing the cache is almost never a good idea. If you are doing read-only queries, you are throwing away data that might be useful and is easily thrown out of cache if something else needs to take its place. If you are modifying the database, you are writing out the dirty pages and then throwing them away. A CHECKPOINT would write them out without throwing them away. Not that you would normally want to do a CHECKPOINT for performance either but it would be indication that dirty pages plus cache pressure forcing them out might affect the performance of some queries. Cache contents can have an impact on query access plans. Have you investigated to see if you are getting a different access plan for your queries before & after clearing the cache? BTW, flushing the cache will never cause inconsistencies. All necessary data is written out before any page is removed from cache. Thanks you john and breck for your answer. Your timely response helped our thought process. Customer can fire custom sql in our product as well as get the results through templates. This particular customer is firing very long queries. One more question, We saw that while inserting data if we clear the cache data gets written to the disk first before cache is cleared. Can sa_flush_cache command cause data inconsistency while select statement, lets say there is a cache hit and when data is about to be fetched, cache gets cleared and empty records gets returned to the thread. i know my imagination runs wild but still clarity of thought is required for me.
(17 May '17, 11:32)
rohanit05
Replies hidden
2
There should not be any circumstance that would cause the query to return wrong results - if there is then it is a bug! In your particular example (cached paged gets 'hit' and then flushed) either (a) it can't happen because the server will have locked the page while it was being read (hence the flush would happen after the read was complete), or (b) the server would simply re-read the page back into the cache after it had been flushed.
(17 May '17, 13:32)
Mark Culp
thanks mark. Once again, thanks all of you.
(18 May '17, 08:47)
rohanit05
|
|
Clearing the cache should never be necessary... except if a Query From Hell starts using the wrong plan. Clearing the cache should never cause data inconsistency unless there is a very very very very very big bug in the SQL Anywhere engine. Clearing the cache may cause temporary performance issues for other connections. You should look for the queries that are causing the application to stall, and study the plans. This may be a very difficult task because the plan is recalculated for each execution, and you have to capture the plan in context, as executed by the application, when performance becomes bad. The application profiler may help, but if you can find the location, you can instrument your code to capture the plan using GRAPHICAL_PLAN()... I have blogged on this topic in the past. thanks breck
(22 May '17, 04:59)
rohanit05
|
