On Tue, Jan 30, 2001 at 05:46:04PM -0500, Sheer El-Showk wrote:
>
> Can someone (maybe with some MySQL-innards code experience) please
> explain this problem:
Many of the MySQL don't monitor this list, but let's give it a shot.
> Generating an index on the first character of a varchar(70) on a 350
> meg table on a PII 366 128 MB Ram, normal IDE disk takes over 17000
> seconds (ie it never even finished but the process took that much
> time in "show processlist" before I finally killed it). And the
> server was not hung in any way ... in fact I watched it generate a
> ~550Mb temporary table in the MySQL data dir (filename began with a
> hash #sql). In fact the system's resources, disk access aside,
> weren't even being strained ... ~8% memory, 3-4% CPU.
>
> Obviously the limiting factor was disk access which was slowing
> everything down.
Maybe.
What do your server variables look like? Could you have given MySQL
more room for buffers and allowed it to be way more efficient?
> So here are the questions:
>
> (1) Why does mysql do this all of this on disk -- the table (~300 MB) may
> well have required more than physical memory, but the first character of
> each varchar and the associated file offset of the record would only be a
> fraction of that space (and isn't that all you need to create an index
> ... just pull out the values and the offsets and sort them and then store
> in an index file).
MySQL does store the keys in memory as it is building up the
index. But if it doesn't have a lot of memory to play with, it can't
be as efficient.
Where there other indexes on the table already? MySQL has to basically
re-write the entire index file when you add/drop an index.
Was this a ISAM or MyISAM table?
> (2) Even if the whole table needed to be manipulated and done on disk
> (seems like a really poor idea) why is a temporary table much larger than
> the original table being generated.
Was it the table or the index file? I suspect it was the index file.
Can you show use your server variables and the table and index
structures? (DESCRIBE table, SHOW KEYS FROM table)
> (3) Are there configuration options to somehow make MySQL behave more
> intelligently than this, because this is really impractical -- 300 MB
> isn't really large -- I know this system may seem like a low-end system
> but its just my laptop. On our production system this takes less time,
> but still much to long.
Yes, there are.
http://www.mysql.com/doc/S/y/System.html
Plus I'm working on getting some of this better documented in the
MySQL distribution and manual, but it'll be a couple days before I'm
done...
> I'm not a database engineer, in fact I'm very new to databases, and
> I don't mean to sound hostile (I know I may come off that way), but
> I'm just a little suprised. I'm guessing there is a way around this
> behaviour (MySQL is usually used with large DB's ... I can't imagine
> this would have been ignored), but I am a little suprised that the
> default configuration is _so_ badly tuned to large databases.
The default suits most people. Most people don't have large
databases. The few who do end up having to tweak it for their
needs. That's the way it is.
But the manual contains lots of useful info, and MySQL comes with
sample config files for small, medium, large, and huge
installs. (That's part of what I'm looking to improve. They need more
documentation.)
> I would appreciate any help and explanations (for my own benefaction,
> in fact the more you are willing to explain and DB's and their innards
> the happier I'd be) -- the more detailed the better.
If you've looked over the manual sections and still have questions,
send them to the list. We'll be glad to try and answer them.
Good luck,
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878 Fax: (408) 530-5454
Cell: (408) 439-9951
---------------------------------------------------------------------
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
