Hi.
On Sat, Mar 24, 2001 at 10:53:19AM -0000, [EMAIL PROTECTED] wrote:
> Hi!
>
> I have this table :
>
> mysql> describe proc;
> +-----------------+-----------------------+------+-----+--------------------
> -+-------+
> | Field | Type | Null | Key | Default
> | Extra |
> +-----------------+-----------------------+------+-----+--------------------
> -+-------+
> | timecode | datetime | | MUL | 0000-00-00 00:00:00
> | |
> | system_id | smallint(5) unsigned | | | 0
> | |
> | pid | char(8) | | |
> | |
> | ppid | char(8) | | |
> | |
> | username | char(20) | | |
> | |
> | cpu_usage | float(4,2) | YES | | NULL
> | |
> | memory_physical | mediumint(8) unsigned | YES | | NULL
> | |
> | memory_virtual | mediumint(8) unsigned | YES | | NULL
> | |
> | usertime | float(4,2) | YES | | NULL
> | |
> | systemtime | float(4,2) | YES | | NULL
> | |
> | priority | tinyint(4) | YES | | NULL
> | |
> | input_block | mediumint(8) unsigned | YES | | NULL
> | |
> | output_block | mediumint(8) unsigned | YES | | NULL
> | |
> | major_fault | mediumint(8) unsigned | YES | | NULL
> | |
> | minor_fault | mediumint(8) unsigned | YES | | NULL
> | |
> | processname | char(20) | | |
> | |
> +-----------------+-----------------------+------+-----+--------------------
> -+-------+
>
>
> With these indexes :
>
> mysql> show index from proc;
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
> | Cardinality | Sub_part | Packed | Comment |
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
> | proc | 1 | timecode | 1 | timecode | A
> | NULL | NULL | NULL | |
> | proc | 1 | system_id | 1 | system_id | A
> | NULL | NULL | NULL | |
> +-------+------------+-------------+--------------+-------------+-----------
> +-------------+----------+--------+---------+
First, you should run an analyize on your indexes (e.g. with
(my-)isamchk). This will give MySQL a value for "Cardinality" and
enable the optimizer to do its work better.
> A common select wouldbe something like this :
>
> mysql> explain SELECT timecode,usertime,systemtime FROM proc WHERE
> (system_id = '1') AND timecode BETWEEN '2001-03-23 11:08:11' AND '2001-03-24
> 11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY
> timecode;
> +-------+-------+--------------------+----------+---------+------+------+---
> ---------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +-------+-------+--------------------+----------+---------+------+------+---
> ---------+
> | proc | range | timecode,system_id | timecode | 8 | NULL | 5394 |
> where used |
> +-------+-------+--------------------+----------+---------+------+------+---
> ---------+
A good index would probably be (system_id,username,processname,timecode).
Maybe even (system_id,username,processname,timecode,usertime,systemtime),
which should enable "using index" only.
You may want to use partial keys in order to reduce space consumption, e.g.
Maybe even (system_id,username(5),processname(5),timecode),
Depending on the Cardinality of the different columns you may want to
change the order to (username,processname,system_id,timecode) or alike.
> Or with a bigger timespan like this :
>
> mysql> explain SELECT timecode,usertime,systemtime FROM proc WHERE
> (system_id = '1') AND timecode BETWEEN '2001-03-13 11:08:11' AND '2001-03-24
> 11:08:11' AND (username = 'statdb') AND (processname = 'httpd') ORDER BY
> timecode;
> +-------+------+--------------------+------+---------+------+--------+------
> ----------------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
> +-------+------+--------------------+------+---------+------+--------+------
> ----------------------+
> | proc | ALL | timecode,system_id | NULL | NULL | NULL | 206878 | where
> used; Using filesort |
> +-------+------+--------------------+------+---------+------+--------+------
> ----------------------+
No index is chosen, because you obviouly SELECTed a major part of the
table. This probably won't happen, if you got these 100 million rows
are talking about below (because you won't select about 20.000.000
rows, will you?).
Reasonable indexes would be the same as above, which should reduce the
number of rows to read far enough to prevent a full table scan.
[...]
> Now, the problem is that I have about 30 system_id's in the database. Each
> system insert about 500 rows into the proc table every 10 minute.
> ...so after a month or so I have alot of rows (> 100 million), and a
> performance problem.
> In Oracle I have fixed this by partitioning on system_id and/or timecode,
> but this is not possible to do transparently in MySQL,
You could probably use the MERGE table type.
> and redesigning the
> database structure with one set of tables or one database for each system
> will get very ugly.
> ...so the result now is that MySQL is pathetically slow and Oracle extremely
> fast, in fact more than 30 times faster.
Nothing to wonder about, you are missing the indexes which help. MySQL
needs combined keys to reduces sufficiently the rows which have to be
read in.
> I have tried to combine the indexes, but then MySQL fails using them
> properly,
Would you please elaborate on this? This is IMHO the main point of
your problem. You need these indexes.
> so the best I've been able to come up with is seperate index on
> timecode,system_id and processname. Then it seems that MySQL decides
> to use whatever is best for the selected timescope.
Of course. MySQL tries to figure out the best key to use for each
query. With indexes type MySQL uses, only one can be used to help
retrieval from one table. Don't know if that is different with Oracle.
> I've also had isamchk to sort the data tables according to timecode, since
> the result is allways ordered by timecode.
Good idea. That will help at least bit.
> Have I forgotten something, or are the current indexes as good as it gets ?
No, combined indexes will be magnitudes faster.
Tell the problems you had with them and someone on this list hopefully
will be able to work it out.
Regards,
Benjamin.
---------------------------------------------------------------------
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
