Diagramme MySQL Zeit- und Antwortverhalten

[SeatSLF]

Deine APU hat doch 4 cores?

Gehst du jetzt von dem Load aus?

Mein Celeron mit 2 cores ist maximal 0.38 von maximal 2.00

Die Diagramme werden auch von dem Client "gezeichnet", was nutzt du?
Bei meiner Heizung mit ähnlichen Daten aufkommen braucht eine Ipad Air2 auch 3-4 sekunden

[toggle]

Auch wenn das Zeichnen vom Client gemacht wird, müssen die Daten auf dem Server aufbereitet werden, was offensichtlich zu dieser Last führt (die Server-Last geht hoch, sobald im Diagramm auf dem Client grüner Updatebalken blinkt). Auf meinem i7-Desktop dauert es auch 4-6 Sekunden und beim ersten Seitenaufruf sogar ca. 10 s. Ich denke, man muss die Größe der Datenbank/Datenarchive miteinbeziehen, um vergleichen zu können.

[gaert]

Eine SQL-Abfrage mit 50.000 Records dürfte schon so um die 10ms brauchen... Der Flaschenhals ist vermutlich das Netzwerk, schließlich müssen die Daten auch zum Client gelangen.

[SeatSLF]

Solche Lags kann ich bei meinem Celeron nicht nach vollziehen

[gaert]

Ein Tipp vielleicht noch: Mittelwert-Intervall nutzen! Dann werden die Daten geglättet, Diagramme sehen besser aus und es müssen viel weniger Daten übertragen/gerendert werden. Oftmals ist weniger mehr - überladene Diagramme im Sekundentakt verwirren i.d.R. mehr als sie nützen.

[toggle]

Eine SQL-Abfrage mit 50.000 Records dürfte schon so um die 10ms brauchen... Der Flaschenhals ist vermutlich das Netzwerk, schließlich müssen die Daten auch zum Client gelangen.

Das Netzwerk (1Gbps) verursacht aber keine 30%-CPU-Last. Im Wireshark kann man auch einigermassen gut die o.g "Lücke" zwischen der Seitenanforderung und der Datenlieferung erkenne. Die eigentliche Datenübertragung fürs Diagramm (Server->Client) dauert insgesamt auch nur ein paar Hundert Millisekuden:

Anforderung:
33    0.423317    10.10.10.13    10.10.10.51    HTTP    696    POST /visu/apps/app1021.php?cmd=chartDraw&appid=1021&visuid=4&pageid=0&datetime=13.05.2016%2018%3A24%3A01&sid=319484946189B6EA887D28B4D728 HTTP/1.1  (application/x-www-form-urlencoded)

Datenlieferung aus dem ersten Datenarchiv:
79    3.683885    10.10.10.51    10.10.10.13    TCP    1514    [TCP segment of a reassembled PDU]
...
95    3.697297    10.10.10.51    10.10.10.13    TCP    1034    [TCP segment of a reassembled PDU]

Datenlieferung aus dem zweiten Datenarchiv:
109    4.805763    10.10.10.51    10.10.10.13    TCP    1514    [TCP segment of a reassembled PDU]
...
169    4.911482    10.10.10.51    10.10.10.13    TCP    967    [TCP segment of a reassembled PDU]

Datenlieferung aus dem dritten Datenarchiv:
183    6.012600    10.10.10.51    10.10.10.13    TCP    1514    [TCP segment of a reassembled PDU]
...
316    7.347400    10.10.10.51    10.10.10.13    TCP    1514    [TCP segment of a reassembled PDU]

Es handelt sich nur um einen Test mit Updates alle 15-Sekunden - man kann die o.g. Effekte sehr gut erkennen. Die (vorübergehende) CPU-Last und die Verzögerung bleiben auch bei einer Aktualisierung alle 10 Minuten.

Ich werde mal testweise die DB löschen, um zu schauen, wie sich die Verzögerung mit der wachsenden DB verhält.

[toggle]

Vermutlich war die Zuordnung der einzelnen Datenblöcke im Post oben etwas unzutreffend, aber man kann gut erkennen, dass zwischen der Anforderung und dem Ende der Lieferung ca. 7 Sekunden vergingen (TCP-Stream-Betrachtung).

Ich habe folgende Versuche gemacht (ein Diagramm mit einem Datenarchiv, Zeitraum: now - 3 days):
- Ein Datenarchiv auf 3 Tage (ca. 600 Werte) gekürzt und siehe da - das Diagramm wird sofort gezeichnet, ohne wahrnehmbare Verzögerung.
- Ein Datenarchiv auf 2 Wochen (ca. 4000 Werte) gekürzt - die Verzögerung ist kaum wahrnehmbar.
- Ein Datenarchiv auf 4 Wochen (ca. 40000 Werte) gekürzt - die Verzögerung bis zur Datenlieferung beträgt ca. 1,5 Sekunden.

Die erwähnten Datenarchive wurden seit Anfang April mit Daten gefüttern (zunächst alle 30 Sekunden, dann alles 300 Sekunden).

[gaert]

"Netzwerk" war wohl etwas zu allgemein formuliert - gemeint ist die gesamte Kommunikation zw. Server und Client: Die SQL-Abfrage dauert nur ein paar Millisekunden, aber der Server (Apache/PHP) muss daraus ja noch ein JS-Array bauen usw... Wie gesagt würde ich die Daten auf ein sinnvolles Maß reduzieren, d.h. ein Mittelwert-Intervalll definieren etc.!

Lass' Dir spaßeshalber mal ein Excel-Diagramm mit 40.000 Daten zeichnen - viel Spaß und Kaffee kochen nicht vergessen

[toggle]

Wir wollen ja nicht Äpfel und Birnen vergleichen!

Die Messungen bezogen sich alle auf das Zeichen von ca. 800 Werten aus Datenmengen verschiedener Größe. D.h. es wurde immer dieselbe Datenmenge zum Client übertragen. Für mich klingt es ausschließlich nach einem Backend-Thema.

Dass die Zeichnungsdauer mit der zu zeichnenden Datenmenge etwa linear skaliert, ist völlig klar. Aber ich hätte nicht erwartet, dass die Aufbereitung einer konstanten Datenmenge so stark von der verfügbaren Datenmenge in einer relationalen Datenbank abhängt. Und wir sprechen hier über eine Datenmenge, die in bloß 2 Monaten aufgezeichnet wurde.

Ich hatte halt gehofft, die Daten 1-2 Jahre aufheben zu können. Aber so wie es aussieht, sollten Datenarchieve 4000 Werte nicht überschreiten (zumindest nicht mit APU & Co). Alternativ könnte man einen Satz an Datenarchieven anlegen, die mit verschiedenen Intervallen/Totzeiten arbeiten und entsprechende Zeiträume abdecken (1 Woche, 1 Monat, 3 Monate, etc.). 4000 Werte pro Jahr entsprechen ca. 11 Meßpunkten pro Tag.

[gaert]

Ich habe schon Archive mit >1 Mio Einträgen gestestet auf meiner Hardware und so gut wie keinen Unterschied feststellen können! Dein Problem hat seine Ursache offenbar in der Hardware und/oder im "Unterbau" (Linux/mySql). Die Archiv-DBs haben für alle relevanten Columns einen Index (Key), insofern sollten derart geringe Datenmengen (40.000 ist nix für eine mySQL-DB) nicht zu sekundenlangen Wartezeiten bezüglich der Abfrage führen.

[toggle]

Genau das hatte ich auch erwartet, aber es kam anders.

Ich habe eine Standard-Installation CentOS 6.5 + edomi. Wie könnte ich die Ursache weiter eingrenzen? Könnte man einzelne edomi-Komponenten/Funktionsschritte irgendwie "profilen"?

EDIT:
Die top-Ausgabe:

top - 10:29:29 up 2 days, 14:21,  1 user,  load average: 0.29, 0.39, 0.36
Tasks: 117 total,   1 running, 116 sleeping,   0 stopped,   0 zombie
Cpu(s): 11.4%us,  5.7%sy,  0.0%ni, 82.8%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3905808k total,   777324k used,  3128484k free,   133804k buffers
Swap:  4046840k total,        0k used,  4046840k free,   334860k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1408 mysql     20   0 1296m 153m 6088 S 45.0  4.0 163:38.71 mysqld
21379 root      20   0  251m  13m 6364 S  6.0  0.3   0:36.90 php
23319 apache    20   0  180m 7404 2804 S  4.0  0.2   0:09.61 httpd

mySQL scheint ordentlich zuzulangen.

[toggle]

Das Ergebnis von tuning-primer:

[root@localhost ~]# ./tuning-primer.sh

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.1.71 x86_64

Uptime = 2 days 14 hrs 41 min 36 sec
Avg. qps = 125
Total Questions = 28256415
Threads Connected = 11

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 5 out of 28256481 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 3
Historic threads_per_sec = 0
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 10
Historic max_used_connections = 14
The number of used connections is 9% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 715 M
Configured Max Per-thread Buffers : 4.75 G
Configured Max Global Buffers : 264 M
Configured Max Memory Limit : 5.01 G
Physical Memory : 3.72 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 22 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 97
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 2 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 30.99 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 31 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 123 tables
You have 64 open tables.
Current table_cache hit rate is 1%
, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 389820 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 16 M
Current table scan ratio = 5 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_buffer

TABLE LOCKING
Current Lock Wait ratio = 1 : 30
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

[MarkusS]

Ich hatte halt gehofft, die Daten 1-2 Jahre aufheben zu können. Aber so wie es aussieht, sollten Datenarchieve 4000 Werte nicht überschreiten (zumindest nicht mit APU & Co). Alternativ könnte man einen Satz an Datenarchieven anlegen, die mit verschiedenen Intervallen/Totzeiten arbeiten und entsprechende Zeiträume abdecken (1 Woche, 1 Monat, 3 Monate, etc.). 4000 Werte pro Jahr entsprechen ca. 11 Meßpunkten pro Tag.

Es sollte doch möglich sein, die Daten für ein "normales Haus" über ca. 10 Jahre zu speichern und bei Bedarf einigermaßen performant abzurufen - ohne Servercluster, Flash-SAN und SAP HANA.

Und diesen Murks mit den Archiven mit unterschiedlichen Intervallen und Zeiträumen fand ich beim HS schon immer Banane - das ist doch irgendwie die Notlösung für die die garnicht verstanden haben wie das geht.

15 Sekunden für 40k Records sind ja ungefähr die Werte von dBase III vor 30 Jahren.

Dazu sind das doch sehr übersichtliche Tabellen, so ein Temperaturarchiv hat ca. vier Spalten und die Daten stehen auch noch der Reihe nach in den Zeilen? Und es geht auch nicht drum, Daten aus X Tabellen einzusammeln und zu präsentieren.

Da bin ich irgendwie beim großen Meister, dass muss in einem Fingerschnippen gehen. Mit einem steinalten Edomi mit iPad als Client im WLAN brauchts ca. 2 Sekunden um eine Seite zu öffnen mit acht Raumtemperaturarchiven von 30 Tagen (Werte werden alle 15 Minuten ins Archiv geschrieben).

[toggle]

15 Sekunden für 40k Records sind ja ungefähr die Werte von dBase III vor 30 Jahren.

In meinem Fall bezogen sich 15 Sekunden auf das Updateintervall, nicht auf die Abfragedauer.

Ich habe im XDebug für drawChart folgendes gemessen (Werte in ms):

          Calls    Count    Total Call Cost    
  sql_call @ 574     1       [COLOR=#0000FF]412[/COLOR]    
  sql_call @ 135     2       [COLOR=#0000FF]395     [/COLOR]
  sql_call @ 171     2       [COLOR=#0000FF]363     [/COLOR]
  sql_call @ 528     1       [COLOR=#0000FF]360     [/COLOR]

Ähnliche Werte bekomme ich auch in mySQL, wenn ich die o.g. Zeilen aus dem edomi-Code verwende.
Abfrage ohne Zeitangaben (liefert die Anzahl aller Werte):

mysql> SELECT COUNT(datetime) AS anz0,MIN(CAST(gavalue AS DECIMAL(20,4))) AS anz1,MAX(CAST(gavalue AS DECIMAL(20,4))) AS anz2 FROM edomiLive.archivKoData WHERE (targetid="5");
+-------+---------+---------+
| anz0  | anz1    | anz2    |
+-------+---------+---------+
| [COLOR=#FF0000]55432 [/COLOR]| 21.9000 | 54.9000 |
+-------+---------+---------+
1 row in set ([COLOR=#FF0000]0.66 sec[/COLOR])

Abfrage mit Zeitangabe (liefert die Anzahl relevanter Werte):

mysql> SELECT COUNT(datetime) AS anz0,MIN(CAST(gavalue AS DECIMAL(20,4))) AS anz1,MAX(CAST(gavalue AS DECIMAL(20,4))) AS anz2 FROM edomiLive.archivKoData WHERE (targetid="5") AND datetime>='2016-05-01 00:00:00' AND datetime<='2016-05-14 17:00:00';
+------+---------+---------+
| anz0 | anz1    | anz2    |
+------+---------+---------+
| [COLOR=#FF0000]3720 [/COLOR]| 22.6000 | 54.0000 |
+------+---------+---------+
1 row in set ([COLOR=#FF0000]0.51 sec[/COLOR])

Solche Aufrufe aus drawChart summieren sich zu einer Verzögerung von ca. 1,5 Sekunden bei einer Auswahl von 3720 aus 55432 Werten (für eine Kurve). Und wenn man mehr als 3 Kurven hat, dann akkumulieren sich die Verzögerungen entsprechend.

[gaert]

Also rund 500ms für die SQL-Abfrage - das ist nicht gerade rekordverdächtig, aber akzeptabel würde ich sagen. Wenn's schneller sein soll: Schnellere Hardware einsetzen - mySQL ist mySQL... Was soll man da machen?!