> -----Original Message-----
> From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]]
> Sent: martes, 27 de marzo de 2001 10:00
> To: Nemholt, Jesper Frank
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: How to index this properly (to make it go faster) ?
>
[clip]
> > Why that, usertime & systemtime are data values, they are
> never used in any
> > where clause. As far as I can see it's complete waste to
> put index on those.
>
> If all columns of the SELECT clause are part of the index, MySQL will
> solve the query only with reading from the index file and never touch
> the data file itself.
>
> Since the index is quite big already and usertime+systemtime only add
> 8 bytes per row, this may pay off regarding the speed win / size
> increase.
Aha. I'll think about that, but I'll have to look in the logfiles to see if
there's a pattern regarding what the users select. I don't think it's just
usertime & systemtime every time.
[clip]
> > > Depending on the Cardinality of the different columns you
> may want to
> > > change the order to (username,processname,system_id,timecode)
> > > or alike.
> >
> > Aha, I'll have to test more with the individual order of
> combined indexes to
> > see what I can get from it.
>
> Well, normally that is not really important, but in your case,
> processname has a low cardinality and therefore one value has a lot of
> matches in the index and it is worth a try to put it to the right.
So the lower the cardinality the more to the right in a combined index ?
[clip]
> May I bother you to add the following indexes and post the EXPLAIN
> after adding them:.
>
> (system_id, processname, timecode)
> (system_id, processname, timecode, username)
> (system_id, processname, timecode, username, usertime, systemtime)
I'll try. Tonight I'm going for system_id, processname, timecode
I will probably make the next tests on the disk table, since recreating
indexes and analyzing/optimizing on the process table takes too many hours.
The disk table only takes 20 minutes.
> > Every explain ends up saying "where used" and the showing
> the full number of
> > rows. This is in 3.22.32. My test setup is the latest, it
> might be different
> > there.
>
> Well, using two different systems isn't a good idea, except if you
> plan to upgrade your 3.22.32 system, because if we find a solution on
> your test setup (3.23.?), it won't behave the same on the older
> MySQL. In most cases, 3.23 is faster than 3.22 (which is to be
> expected).
I plan to upgrade asap, but had a few problems building the latest stable
under 4.0f ev56.
On a side note, with 3.22.32 I have some weird memory allocation problems.
The database runs on a 4 CPU machine with 4 GB memory, and most of these 4
GB are free (or rather used for filesystem cache by the OS). The memory is
free if any process want it.
With the default settings (ie. no custom my.cnf) everything runs fine, but
MySQL use allmost no memory which is not optimal given the size of the
database & indexes.
If I instead use the my-huge.cnf that comes with MySQL which makes a key
buffer of 384 MB, a big select makes MySQL crash with a "Out of memory"
error.
I have ulimit set to unlimited, and no memory allocation errors are logged
to /var/adm/messages (Tru64 allways logs to that file if some user hit the
limit of some sysconfigtab/kernel parameter). This means that the "Out of
memory" error from MySQL was not caused but the OS disallowing mysqld to
allocate more memory.
I have tried this several times to be sure it's reproduceable. I get a crash
every time.
Is there a memory allocation bug in MySQL on Tru64 ?
> Anothing thing: If you cannot get it work with combined indexes, can
> you post some testcase (i.e. the SQL commands to build a sample
> database), so that we can play around and see if we have more luck?
Sure.
Everything will in fact be put on a web page soon, since it's a OpenSource
solution.
The database is used to store performance info for Tru64, Solaris & Linux
(and others soon).
A bit like MRTG/RDDTool/Cricket but much more dynamic & detailed.
l8r/Jspr
---------------------------------------------------------------------
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
