MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Marc Mertes
Date: January 19, 2012 05:38AM

Rick James Wrote:
-------------------------------------------------------
> To use an index, or not to use an index, that is
> the question.
>
> If less than 10% of the table needs to be looked
> at, a relevant index will be used.
> If more than 30% of the table needs to be looked
> at, the seemingly relevant index will not be
> used.
The whole table has now 18.453.619 rows.
The 30% border you were talking about is 5.536.086 rows.
A one year query has 3.153.600 rows,
so this query is between 15-20% of the whole table.

If you are wondering now (see table status and time range below) - yes
it´s correct because the interval of measurements had become smaller over the years, from 10min at the beginning down to now 10sec.


> Why? To use an index means flipping back and
> forth between the index and the data. (Note: the
> index and the data are usually kept in separate
> places, and sorted in different ways.)
>
> As long as Geraete_idGeraete is always 2 (> 30%),
> indexing that field is mostly useless.
> Scanning a whole year is scanning most of the
> data, so it does not bother with the index.
>
> This may involve a different issue:
> > The query 2011-01-01 00:00:00 to 2012-01-01
> 00:00:00 takes 30sec
> > The query 2011-01-06 09:30:00 to 2012-01-06
> 09:30:00 takes 8sec
> Are there a similar number of rows in each query?

Yes,
2011-01-01 00:00:00 to 2012-01-01 00:00:00 has 729 rows around 30sec
2011-01-06 09:30:00 to 2012-01-06 09:30:00 has 730 rows around 8sec


> Did you run the first query first?

Yes, but after turning query_cache off I made a lot of queries - in many constellations, direktions and repetitions - it´s allways the same (like above said).


> Was the system 'cold' when you ran the first
> query?

Yes it was

> Is there so much data that it cannot all be cached
> in RAM?

It´s less then RAM, the table Wind has at all ~1GB at the moment.


>
> Typically if you run any 'big' query twice in a
> row, the second run will be ten times as fast,
> simply because all the stuff it needs has been
> pulled from disk and "cached".
>
> Please provide SHOW TABLE STATUS LIKE 'Wind';
> (Without it, I am guessing at some of what I am
> saying.)

SHOW TABLE STATUS LIKE 'Wind';
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Wind | InnoDB | 10 | Compact | 18453619 | 41 | 767557632 | 0 | 328138752 | 6291456 | NULL | 2012-01-17 15:45:38 | NULL | NULL | latin1_swedish_ci | NULL | | |
+------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

>
> As for the differences in the EXPLAINs, first
> realize that the query optimizer has heuristics,
> and approximations. InnoDB, as you may have
> noticed, never quite knows how many rows are in a
> table, and what to put in EXPLAIN's "Rows colunn.
> Hence, two EXPLAINs may go in different directions
> because of the phase of the moon. Probably (and
> hopefully) the two choices are about equally
> 'good'.
>
> key_len = 4 refers to the storage size of
> > `Geraete_idGeraete` int
>
> key_len = 12 refers to the storage size of the
> index being used, which includes
> > `time` datetime NOT NULL DEFAULT '0000-00-00
> 00:00:00', -- 8 bytes
> > `Geraete_idGeraete` int(10) unsigned NOT NULL
> DEFAULT '0', -- 4 bytes

What I´m wondering about is, why is the KEY,ref and type changing from "one minute to another" - the data and structure are still the same, thats why I thought it´s maybe a reason of mysql 5.1 and replication slave stored data.
The "break" is between 2011-01-06 09:15:00 - 2011-01-06 09:30:00.

>
> "ref=const" refers to
> > Geraete_idGeraete=2
> But then it estimated that it would need 9M rows.
>
> What is the range of `time` in the table?
> SELECT min(`time`), max(`time`) FROM Wind;

SELECT min(`time`), max(`time`) FROM Wind;
+---------------------+---------------------+
| min(`time`) | max(`time`) |
+---------------------+---------------------+
| 1994-10-01 00:00:00 | 2012-01-19 11:08:50 |
+---------------------+---------------------+

>
> Have you tried a PRIMARY KEY or INDEX ordered this
> way?
> (Geraete_idGeraete, time)
Not yet

> If that were the PK, then the queries you have
> shown would hit the minimum number of things --
> namely a "range" scan over all the rows with
> Geraete_idGeraete=2 in the given time range, and
> no other rows. Also it would not need to flip
> back and forth between the index and the data.
>
> Still, that is a lot of rows, so it will take a
> "long" time.

True the amount of rows makes it slow - so I try to tune it as best as possible.
The max query is a whole year at once.

Regards Marc

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.1.49 - Optimal settings for memory usage
1502
January 19, 2012 05:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.