Matt,
One last question and then I promise to drop the topic ... what would be
the best way to force a complete load of an index into the key buffer ?
Thanks very much for your time.
John
"Matt W" <[EMAIL PROTECTED]>
2004-01-15 02:06
To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
cc:
Subject: Re: How does key buffer work ?
Classification:
Hi John,
----- Original Message -----
From: <[EMAIL PROTECTED]>
Sent: Wednesday, January 14, 2004 6:37 AM
Subject: Re: How does key buffer work ?
> Matt,
>
> Many thanks for the answer. It has helped enormously.
>
> First, I have been getting the odd index corruption that has proved to
be
> very annoying. I had checked the changes document for releases since
> 4.0.13 and there didn't seem to be any mention of an index problem but
now
> I'll upgrade asap. Thanks for that.
It was in 4.0.15: http://www.mysql.com/doc/en/News-4.0.15.html
"Fixed rare bug in MyISAM introduced in 4.0.3 where the index file
header was not updated directly after an UPDATE of split dynamic rows.
The symptom was that the table had a corrupted delete-link if mysqld was
shut down or the table was checked directly after the update."
> Next, I had extended my key buffer too much. When I calculated the
high
> water mark for key buffer usage, I found that indeed it was
considerably
> less than the space I had allocated. I will modify accordingly.
However, I
> was just thinking about what you said about this only being a high
water
> mark ... I can't see any way, apart from dropping an index or table,
that
> information is going to be purged from the cache especially as you say
> that MySQL updates the contents of the cache when an index is
modified, so
> won't that mean that during normal operation the key_blocks_used
should
> indicate exactly how many blocks are currently in use ? Some of the
> contents may of not been used for a while but still they won't be
purged
> unless the maximum extent of the cache is reached ?
When a table is closed, its blocks are released from the key_buffer. So
after running FLUSH TABLES, for example, Key_blocks_used should be 0 if
it was "current."
You can see that the blocks are removed from the buffer by running a
query that uses an index. Look at Key_reads. Run it again and Key_reads
shouldn't change. Use FLUSH TABLES and run it again. Key_reads will be
increased since the blocks were reloaded.
> With regards to the caching on myd data, is the fact that MySQL
doesn't
> cache myd data a design choice ? It makes perfect sense for MySQL
> installations on a dedicated machine as its a fair assumption that
there's
> no other nasty apps around filling your system cache with other data
and
> the OS is probably in the best position to cache the disks. However,
in my
> case (and I would guess in the proportion of the cases), the database
> shares the machine and in my case this is with Apache which depending
on
> the usage on the website, is likely to flush the cache reasonably
quickly.
> It would be easier to get more consistent query execution times if
MySQL
> maintained it's own caches (for myd data as well) so that more control
> could be kept on cache contents. In a previous life I worked with
Sybase
> and one of the advantages (only ?) is that the administrator has
control
> on the caching of index and data for each individual table. Very handy
if
> it was benficial to ensure the contents of specific tables were
available
> in a cache.
InnoDB may cache full row data too with its buffer_pool. But I don't
know much about that. :-)
But when you have something like Apache running, you want it to be able
to use the memory it needs. Isn't it better to not have .MYD data cached
than to have other processes swapping?
> Lastly, I'd love to use the query cache but I do have to update the
> indexes every 5 minutes (the system revolves around retrieving SNMP
data
> from a bunch of routers every 5 mins then dumping it into the db ... a
> user then requests a report periodically) so the QCache is invalidated
> every 5 mins anyway.
If you can get [repeated] queries to use the query cache for 5 minutes,
I'd say that's a pretty long time. :-)
> So, a) do you (or anyone else) know of any plans to
> extend the caching functionality
No.
> and b) are there any other parameters
> that may be worth a tweak ? One thing I had considered was to extend a
> composite index to incorporate the data that is required in the
> problematic query then the query should be able to extract the data
> required without having to search the myd file. I understand that this
> will increase the index size and slow the inserts but otherwise is
this a
> legitamate solution i.e. there must be some other downside surely ?
No, that would probably work pretty well if you don't mind making the
index a bit bigger (there's a limit of 16 cols/index or 512 (?) bytes,
and no [full] TEXT/BLOB columns). I've done this on one of my tables.
If EXPLAIN on your SELECTs says "Using index", then it's getting the
data without going to the .MYD file (I guess you already know that).
Making an index bigger (by adding columns) is not as bad as adding
another separate index. With a bigger index, the only slowdown on
inserts is writing the extra bytes -- not much. But for each separate
index, MySQL needs to find where in the index to put the new row. So I
don't think you'll notice any slowdown or other downsides. :-)
> Anyway, thanks again for the response.
>
> John
Hope that helps.
Matt
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
