Re: MySQL 5.1.49 - Optimal settings for memory usage
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.