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