Re: MySQL 5.1.49 - Optimal settings for memory usage
The other strange thing I noticed is, that when I query Wind data of one year - for example from 2011-01-01 00:00:00 to 2012-01-01 00:00:00 - it takes about 30sec!
If the "end-date" of the query is behind 2012-01-06 09:30:00 it takes the 8sec from above. I found the reason, but I don´t understand it. Maybe someone can explain that.
History:
The original database was designed and created on MySQL 5.0.51a-Max.
Now the machine is to old and everything has to switch to a new machine (described above - MySQL 5.1.49). So I configured the new machine as replication slave until the day the old machine will be turned off.
I made a consitent copy of all databases (lock and flush all tables) with mysqldump with --master-data and pushed the dump into the new server and made a mysql_upgrade after that.
Everything seemed to be fine.
Now the strange thing:
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
2012-01-06 09:15:00-09:30:00 could match the time where the replication slave started his work.
Differences between the EXPLAIN of this 2 querys:
#################################################
explain SELECT Win.time,UNIX_TIMESTAMP(Win.time) AS unixtime,YEAR(Win.time) AS jahr,MONTH(Win.time) AS monat,DAY(Win.time) AS tag,HOUR(Win.time) DIV 12 AS intervall,ROUND(AVG(SIN(Win.windricht/180*PI())),4) AS avg_sin_windricht,ROUND(AVG(COS(Win.windricht/180*PI())),4) AS avg_cos_windricht,ROUND(AVG(Win.windgeschw),4) AS avg_windgeschw,ROUND(AVG(-ABS(Win.windgeschw)*SIN(Win.windricht/180*PI())),4) AS avg_uwind,ROUND(AVG(-ABS(Win.windgeschw)*COS(Win.windricht/180*PI())),4) AS avg_vwind FROM MIUB_messdaten.Wind AS Win WHERE Win.time>='2011-01-01 00:00:00' AND Win.time<'2012-01-01 00:00:00' AND Win.Geraete_idGeraete=2 GROUP BY jahr,monat,tag,intervall ORDER BY Win.time;
+----+-------------+-------+------+------------------------------+----------------------+---------+-------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------+----------------------+---------+-------+---------+----------------------------------------------+
| 1 | SIMPLE | Win | ref | PRIMARY,idxGeraete_idGeraete | idxGeraete_idGeraete | 4 | const | 9222204 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+------------------------------+----------------------+---------+-------+---------+----------------------------------------------+
1 row in set (0.00 sec)
########################################################
explain SELECT Win.time,UNIX_TIMESTAMP(Win.time) AS unixtime,YEAR(Win.time) AS jahr,MONTH(Win.time) AS monat,DAY(Win.time) AS tag,HOUR(Win.time) DIV 12 AS intervall,ROUND(AVG(SIN(Win.windricht/180*PI())),4) AS avg_sin_windricht,ROUND(AVG(COS(Win.windricht/180*PI())),4) AS avg_cos_windricht,ROUND(AVG(Win.windgeschw),4) AS avg_windgeschw,ROUND(AVG(-ABS(Win.windgeschw)*SIN(Win.windricht/180*PI())),4) AS avg_uwind,ROUND(AVG(-ABS(Win.windgeschw)*COS(Win.windricht/180*PI())),4) AS avg_vwind FROM MIUB_messdaten.Wind AS Win WHERE Win.time>='2011-01-06 09:30:00' AND Win.time<'2012-01-06 09:30:00' AND Win.Geraete_idGeraete=2 GROUP BY jahr,monat,tag,intervall ORDER BY Win.time;
+----+-------------+-------+-------+------------------------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------+---------+---------+------+---------+----------------------------------------------+
| 1 | SIMPLE | Win | range | PRIMARY,idxGeraete_idGeraete | PRIMARY | 12 | NULL | 4423210 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+------------------------------+---------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)
###############################################################
As you see, the fields type, key, ken_len and ref changed and I have no clue why.
Therefor I´m not SQL professional enought yet, but that seems to be the reason why it´s so much faster if the end-date is behind 2012-01-06 09:30:00.
I checked the row data and I can´t see any changes.
This only happens in the Wind table, all other are without that strage behavior, I checked all tables.
Any ideas?
Regards Marc
Edited 1 time(s). Last edit at 01/18/2012 08:00AM by Marc Mertes.