I am running MySQL 4.1.11 with an innoDB table holding about 17GB of
records. I took a few hundreds of randomly selected records from the table
and measured the average access time:
1st test: average access time is 600ms
2nd test: average access time is 30ms
3rd test: average access time is 15ms
Stop and restart MySQL
4th test: average access time is 15ms
Note that I stopped and restarted mysql between the 3rd and 4th test but the
average access time does not change.
I also tried another set of random records that are not in the table, the
average access time is about 2s for the first test and 115ms for the second
test. After stop and restart MySQL, I still got the 115ms access time.
Clearly MySQL have both positive and negtive caching. But does anyone know
why the cache is not flushed after MySQL restart??
I understand that MySQL has a query_cache, but it is turned off by default
and I do NOT have it on.
There is also an innodb_buffer_pool_size variable, which in my case is at
the default value 1048576 (and can't seem to be set smaller).
My data file path in my.cnf file is:
# Configure the datafile to be auto expanding
innodb_data_file_path = ibdata1:10M:autoextend
Thanks a lot!