Sorry, the variable is actually "key_buffer_size" (I don't use MyISAM); I'm not sure what it defaults to, but the typical recommendation is 25% of your memory.
You can tell if your cache is effective by looking at the key_reads and key_read_requests (from the MySQL window, type "SHOW STATUS" without the quotes).
If the key_reads/key_read_requests is >= .01 then you need to allocate more memory to the key_buffer_size.
For example our SHOW STATUS on a test database gives us:
| Key_read_requests | 156689872 | | Key_reads | 445700 |
Which is (445700 / 156689872), or 0.00284 (truncated), which is fine.
One other thing I would recommend is turn off your query cache (I can almost hear the gasps from other members of this list). The query cache is designed to return the results of frequently executed queries (assuming you have enough memory allocated to the query cache to store the results). From the sounds of your database (one table with 1.7 million records), it sounds like no two identical queries will be run with any frequency (I am guessing that a fairly even distribution of rows will be selected - you'll rarely-if-ever select the same row out 8 times in 5 minutes outside of testing). If that's the case, turn off the query cache (query_cache_type = OFF in your my.cnf) and give that memory to something else.
"I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here?"
Your query has to be parsed, the index paged in from disk, a lookup done on the index, the disk accessed to find the row, format it, and return it. Plus there is the overhead of puttting the query and the result into the query cache. Grep just spins through the file. For a non-complicated task like this, grep is fast. When selecting hundreds of rows from dozens of tables with all sorts of criteria in the where clause, grep is not usable.
MyISAM is fine for this sort of work (though I prefer InnoDB for the row-locking, etc).
David
Jacob Elder wrote:
On Tue 18 May 02004 at 12:26:41PM -0700, David Griffiths wrote:
Is that the only table in your MySQL installation?
Yes, and no one has access to it yet but me.
MyISAM primary keys are put in a b-tree index, which is cached by MySQL in memory in the key_buffer_size parameter. What is it set to on your system (the my.cnf file, probably in /etc or /var)?
key_buffer_size does not appear in my.cnf. Is the default sensible for my
setup?
The second time you run it, the index is definately in memory, which is why it is so fast.
Perhaps the OS is swapping MySQL pages out to disk, or perhaps there is not enough memory allocated to the key-buffer to keep the index in memory.
The more frequently you access data, the more likely it is to be cached by the OS or the database. Not sure what is running on your system or how it is configured, but the amount of memory you have looks a bit light. Databases are much faster with more memory.
David.
There are other services on this machine, but the load is rarely above 0.05.
I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here?
The chances of a given row being returned more than once per day is very small, so caching the result doesn't help a lot.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
