MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Marc Mertes
Date: January 18, 2012 03:34AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.1.49 - Optimal settings for memory usage
1650
January 18, 2012 03:34AM


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.