Am 09.01.2014 21:26, schrieb Andreas Goetz:
Hallo Heiko,
jetzt hatte ich extra den Schlepptop hochgefahren um Dich mit
Diagnosetipps zu versorgen und schon rennt die Mieze?! Aber lieber so
als anders ;)
Sorry und Danke dir für deine Arbeit, sieht top aus!
PS.: Logging brauchst Du egtl auch nicht- dafür gibts ja &debug=1 im
Querystring ;)
Hm, wie kann ich das dem Server beim Starten mit übergeben?
(/etc/init.d/mysql start &debug=1 oder wie? Nee funktioniert nicht. Aber
so: im laufenden Betrieb einfach eine mysql-shell aufmachen und dann
SET GLOBAL general_log = 'ON', danach dann mit ... 'OFF' wieder
ausschalten).
Beim 2. Nachdenken- 11s für 180 Werte (=Tage) ist _viel_ zu langsam. Kannst Du
mal die Queries für diese eine Abfrage abschauen? Bei mir geht sowas in 1sec...
Ok, was ich gemacht hab: startseite vz, erst mal alle Channels
unsichtbar gestellt, dann nur einen sichtbar gemacht. Logging an, auf
"Jahresansicht" geklickt, gewartet bis der Graph angezeigt wurde, dann
logging aus. Ergibt das angehängte Log.
Ok, der aggregate-Teil dauert wohl nur 3 Sekunden, der Rest wird von
s0-Ereignissen erzeugt.
Also doch alles ok?
Kleine Anmerkung zu den s0-Ereignissen - ist aber andere Baustelle: im
log fällt mir auf, dass das sehr häufig vorkommt:
911 Query SELECT e0_.id AS id0, e0_.uuid AS
uuid1, e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS
value5, e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities
e0_ LEFT JOIN properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'90da22c0-f2dc-11e2-a59d-e9b55d71b128') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
911 Query START TRANSACTION
911 Query INSERT INTO data (timestamp, value,
channel_id) VALUES ('1389302896063', '1', 14)
911 Query UPDATE properties SET value = '1' WHERE
id = 71
911 Query UPDATE properties SET value = '1' WHERE
id = 69
911 Query UPDATE properties SET value = '1000'
WHERE id = 67
911 Query commit
Das ist offenbar ein s0-Eintrag (value=1 im channel 14, Stromzähler).
Was mich wundert: warum muss der aufwändige join vorher ausgeführt
werden? Liefert bei mir z.B.
+-----+--------------------------------------+-------+------+------------+---------------------+---------+------------+
| id0 | uuid1 | type2 | id3 | pkey4
| value5 | class6 | entity_id7 |
+-----+--------------------------------------+-------+------+------------+---------------------+---------+------------+
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 71 | active
| 1 | channel | 14 |
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 70 | color
| navy | channel | 14 |
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 69 | public
| 1 | channel | 14 |
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 67 | resolution
| 1000 | channel | 14 |
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 72 | style
| steps | channel | 14 |
| 14 | 90da22c0-f2dc-11e2-a59d-e9b55d71b128 | power | 68 | title
| Strom-Ferienwohnung | channel | 14 |
+-----+--------------------------------------+-------+------+------------+---------------------+---------+------------+
6 rows in set (0.00 sec)
Muss das wirklich vor jedem Insert sein?
Und danach werden _immer_ die Werte für resolution, active und public
"geupdatet" (Unnötig, sind die alten Werte)
Da die Stromzähler ja mitunter heftig feuern (Wärmepumpe unter Last
4kWh, PV-Wechselsrichter gern auch mal 12kWh), könnt ich mir vorstellen,
dass das ziemlich viel unnötige Queries auslöst.
@ Hendrik, liest du vielleicht mit? s0vz ist ja dein Baby, oder..? ;)
Denke also dass alles passt. Werds mal beobachten wie sich die Zeiten
und Zahlen verhalten...
DANKE und ne gute Nacht :)
Heiko
/usr/sbin/mysqld, Version: 5.5.33-0+wheezy1 ((Debian)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
140109 22:45:36 1242 Connect vz@localhost on volkszaehler
1242 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'37e34a40-f2dc-11e2-a9f5-617f327e9a54') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1242 Query SELECT MIN(timestamp) FROM (SELECT timestamp
FROM data WHERE channel_id='7' AND timestamp<'1357767914445' ORDER BY timestamp
DESC LIMIT 2) t
1242 Query SELECT MAX(timestamp) FROM (SELECT timestamp
FROM data WHERE channel_id='7' AND timestamp>'1389303914445' ORDER BY timestamp
ASC LIMIT 2) t
1242 Query SELECT aggregate.type, COUNT(aggregate.id) AS
count FROM aggregate INNER JOIN entities ON aggregate.channel_id = entities.id
WHERE uuid = '37e34a40-f2dc-11e2-a9f5-617f327e9a54' GROUP BY type HAVING count
> 0 ORDER BY type DESC
1242 Query SELECT
UNIX_TIMESTAMP(FROM_UNIXTIME(MIN(timestamp) / 1000, "%Y-%m-%d")) * 1000 FROM
aggregate WHERE channel_id='7' AND type='3' AND timestamp>='1357767914445'
1242 Query SELECT
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d"),
INTERVAL 1 day)) * 1000 FROM aggregate WHERE channel_id='7' AND type='3' AND
timestamp<'1389303914445'
1242 Query SELECT SUM(count) FROM (SELECT COUNT(1) AS
count FROM data WHERE channel_id = '7' AND ( timestamp >= '1357767914445' AND
timestamp < '1374444000000' OR timestamp >= '1388271600000' AND timestamp <=
'1389303914445') UNION SELECT SUM(count) AS count FROM aggregate WHERE
channel_id = '7' AND type = '3' AND timestamp >= '1374444000000' AND timestamp
< '1388271600000') AS agg
1242 Query SELECT aggregate.type, COUNT(aggregate.id) AS
count FROM aggregate INNER JOIN entities ON aggregate.channel_id = entities.id
WHERE uuid = '37e34a40-f2dc-11e2-a9f5-617f327e9a54' GROUP BY type HAVING count
> 0 ORDER BY type DESC
1242 Query SELECT
UNIX_TIMESTAMP(FROM_UNIXTIME(MIN(timestamp) / 1000, "%Y-%m-%d")) * 1000 FROM
aggregate WHERE channel_id='7' AND type='3' AND timestamp>='1357767914445'
1242 Query SELECT
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d"),
INTERVAL 1 day)) * 1000 FROM aggregate WHERE channel_id='7' AND type='3' AND
timestamp<'1389303914445'
1242 Query SELECT timestamp, value, 1 AS count FROM data
WHERE channel_id='7' AND timestamp >= '1357767914445' AND timestamp <=
'1389303914445' ORDER BY timestamp ASC
140109 22:45:39 1243 Connect vz@localhost on volkszaehler
1243 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'267f2810-f2dc-11e2-8d7c-cb14f79472e0') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1243 Query START TRANSACTION
1243 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303939334', '4.12', 6)
1243 Query UPDATE properties SET value = '1' WHERE id = 28
1243 Query UPDATE properties SET value = '1' WHERE id = 26
1243 Query commit
1243 Quit
140109 22:45:40 1244 Connect vz@localhost on volkszaehler
1244 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'c80251e0-f2db-11e2-8178-ef2453dba49c') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1244 Query START TRANSACTION
1244 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303940579', '3.81', 1)
1244 Query UPDATE properties SET value = '1' WHERE id = 2
1244 Query commit
1244 Quit
140109 22:45:41 1245 Connect vz@localhost on volkszaehler
1245 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'112ddc60-f2dc-11e2-9294-971d5a648238') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1245 Query START TRANSACTION
1245 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303941827', '4.00', 3)
1245 Query UPDATE properties SET value = '1' WHERE id = 13
1245 Query UPDATE properties SET value = '1' WHERE id = 11
1245 Query commit
1245 Quit
140109 22:45:42 1246 Connect vz@localhost on volkszaehler
1246 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'1fb4b660-f2dc-11e2-9685-936d7367e7ac') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
140109 22:45:43 1246 Query START TRANSACTION
1246 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303943174', '3.62', 5)
1246 Query UPDATE properties SET value = '1' WHERE id = 23
1246 Query UPDATE properties SET value = '1' WHERE id = 21
1246 Query commit
1246 Quit
140109 22:45:44 1247 Connect vz@localhost on volkszaehler
1247 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'06e27ce0-f2dc-11e2-9196-1793d6d6626c') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1248 Connect vz@localhost on volkszaehler
1248 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'89277390-f2dc-11e2-94d6-9984a2fec1dc') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1247 Query START TRANSACTION
1247 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303944531', '3.94', 2)
1247 Query UPDATE properties SET value = '1' WHERE id = 8
1247 Query UPDATE properties SET value = '1' WHERE id = 6
1247 Query commit
1247 Quit
1248 Query START TRANSACTION
1248 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303944236', '1', 13)
1248 Query UPDATE properties SET value = '1' WHERE id = 65
1248 Query UPDATE properties SET value = '1' WHERE id = 63
1248 Query UPDATE properties SET value = '1000' WHERE id =
61
1248 Query commit
1248 Quit
140109 22:45:45 1249 Connect vz@localhost on volkszaehler
1249 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'1843ebf0-f2dc-11e2-b68a-0d188c147a46') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
1249 Query START TRANSACTION
1249 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303945897', '3.75', 4)
1249 Query UPDATE properties SET value = '1' WHERE id = 18
140109 22:45:46 1249 Query UPDATE properties SET value = '1' WHERE id = 16
1249 Query commit
1249 Quit
1242 Quit
1250 Connect vz@localhost on volkszaehler
1250 Query SELECT e0_.id AS id0, e0_.uuid AS uuid1,
e0_.type AS type2, p1_.id AS id3, p1_.pkey AS pkey4, p1_.value AS value5,
e0_.class AS class6, p1_.entity_id AS entity_id7 FROM entities e0_ LEFT JOIN
properties p1_ ON e0_.id = p1_.entity_id WHERE (e0_.uuid =
'37e34a40-f2dc-11e2-a9f5-617f327e9a54') AND e0_.class IN ('channel',
'aggregator') ORDER BY p1_.pkey ASC
140109 22:45:47 1250 Query START TRANSACTION
1250 Query INSERT INTO data (timestamp, value, channel_id)
VALUES ('1389303947024', '16.88', 7)
1250 Query UPDATE properties SET value = '1' WHERE id = 33
1250 Query UPDATE properties SET value = '1' WHERE id = 31
1250 Query commit
1250 Quit
658 Query SET GLOBAL general_log = 'OFF'
