MySQL Forums
Forum List  »  Performance

Re: MySQL 5.1.49 - Optimal settings for memory usage
Posted by: Marc Mertes
Date: January 17, 2012 02:16AM

Hi Rick!
Thanks so far for your information.
I corrected the settings to:
key_buffer_size = 16M
innodb_buffer_pool_size = 42G

Right now I am testing the performance with different query-cache settings.

I only have one slow query, following below:
----------------------------------------------
# Time: 120117 8:54:34
# User@Host: wetterdaten[wetterdaten] @ host.my.domain.de [1xx.xxx.xx.1]
# Query_time: 8.245151 Lock_time: 0.000179 Rows_sent: 730 Rows_examined: 3125515
SET timestamp=1326786874;
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-17 07:54:00' AND Win.time<'2012-01-17 07:54:00' AND Win.Geraete_idGeraete=2 GROUP BY jahr,monat,tag,intervall ORDER BY Win.time;
----------------------------------------------

Here the table infos:
mysql> show create table Wind;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Wind | CREATE TABLE `Wind` (
`time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`windgeschw` float NOT NULL DEFAULT '-9999',
`Geraete_idGeraete` int(10) unsigned NOT NULL DEFAULT '0',
`windricht` float NOT NULL DEFAULT '-9999',
PRIMARY KEY (`time`,`Geraete_idGeraete`),
KEY `idxGeraete_idGeraete` (`Geraete_idGeraete`),
CONSTRAINT `Wind_ibfk_1` FOREIGN KEY (`Geraete_idGeraete`) REFERENCES `Geraete` (`idGeraete`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN Wind;
+-------------------+------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------------------+-------+
| time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| windgeschw | float | NO | | -9999 | |
| Geraete_idGeraete | int(10) unsigned | NO | PRI | 0 | |
| windricht | float | NO | | -9999 | |
+-------------------+------------------+------+-----+---------------------+-------+
4 rows in set (0.00 sec)


mysql> 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-17 07:54:00' AND Win.time<'2012-01-17 07:54: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 | 3928896 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+------------------------------+---------+---------+------+---------+----------------------------------------------+
1 row in set (0.00 sec)


--------------------------------------------------------------------

Records:
SELECT count( * ) FROM `Wind`; = 18435050
In this table is comming any 10sec one new record.

Maybe you have some tips for optimizing.

Thanks and regards Marc



Edited 1 time(s). Last edit at 01/17/2012 02:31AM by Marc Mertes.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL 5.1.49 - Optimal settings for memory usage
2108
January 17, 2012 02:16AM


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.