MySQL Forums
Forum List  »  Performance

Re: Queries slow down daily, optimizing helps -> why?
Posted by: Kaspar Con
Date: February 19, 2009 12:24PM

Rick James Wrote:
-------------------------------------------------------
> A possible explanation for speed difference...
> Some of RAM is being used as a 'cache'. OPTIMIZE
> brings the entire table and indexes into memory
> (since it will fit). Then the SELECT can do its
> job without touching the disk.
>
> If you don't run that SELECT for a while, and if
> other tables are busy, the data may get flushed
> from cache. Then when you run your SELECT again,
> it has to hit the disk.

I thought about what you wrote. Just to understand it correctly: If the data gets flushed from the cache, hasn't the key_buffer to be "full"? In my case the key_buffer_size is 512M and the Key buffer usage is about 70M. Or isn't the key_buffer cache used?
Could the queries be flushed after such a short timespan (one day) as there is so much space left?

Our system is not very heavily used at the moment, some 5-10 concurrent users, CPU at 10%. BTW we have 2 dual-core 3Ghz processors and 16GB RAM

aftab khan your suggestion really sounds interesting, thanks for track me to that path.

This couldn't be a Query cache problem, can't it? The problem arose about the time we tweaked our Qcache (but we can't say for sure, that's the problem).

We use following variables

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 40960     |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 40960     |
| query_cache_size             | 536870912 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

As a side question: Is a query_cache_min_res_unit of 40k possibly sensible? My predecessor used this value but I am quite uncertain about it. We have a lot of very tiny queries and some huge ones.

Would the table definitions or any othe debuginfo give you usable insights?

Thank you for your patience!

Sami

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Queries slow down daily, optimizing helps -> why?
2217
February 19, 2009 12:24PM
2200
March 18, 2009 03:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.