On Thu, Jun 14, 2001 at 12:43:41PM -0400, Robin Senior wrote:
> On Thu, 14 Jun 2001, Jeremy Zawodny wrote:
> > I have a few comments and questions:
> >
> > (1) Is this a unique index? If not, consider doing a partial index
> > on the column. Index the first 32 characters or so. I'll save a
> > lot of disk I/O, memory, and CPU time but still yeild pretty
> > good results (depending on your data, of course).
>
> No, it isn't a unique index. Most of the entries are <32 chars, but
> I used a varchar(255) to be safe, as it only occupies the number of
> bytes required to record the string, plus 1 byte to record the
> length.
Ah, okay. Then my suggestion won't help nearly as much as I thought.
> There are 77million records, and I can't guarantee that they are all
> <32. Will the index know to only index the required number of chars,
> or will it do 255? What if I had used a TEXT datatype?
It'll do the Right Thing. :-)
> > (2) What are your key_buffer and record_buffer set to? I believe
> > that when MySQL is doing serious scanning (as needed in building
> > an index), a larger record_buffer will help.
>
> Unfortunately, my key_buffer is only adjusted to 64M, and the
> record_buffer is set at a piddling 131072... I only got into configuring
> mysql after I began the indexing. The other box (same specs) that I'm
> running is set with:
> set-variable=key_buffer=128M
> set-variable=table_cache=512
> set-variable=sort_buffer=8M
> set-variable=record_buffer=2M
>
> I'm trying to index on that machine at the same time, by indexing as
> I insert. I'm guessing 2M is still far too small? Can you recommend
> a better config?
Is this machine basically dedicated to MySQL? If so, I'd try bumping
up the record_buffer to 16 or 32MB and see what that does. I've got it
set at 32MB on a server with 1GB of RAM.
> > (3) Try running `vmstat 5' on the box for a bit. Is the CPU pegged?
> > Is it I/O bound? Is it swapping at all?
>
> Forgot to mention I'm running Win2K, don't have vmstat,
> sorry. Perfmon is shite, too.
Heh, okay.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
MySQL 3.23.29: up 0 days, processed 2,801,545 queries (75/sec. avg)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
