Hallo,
mein Fhem reariert in letzter Zeit sehr langsam,
Hab jetzt mal Apptime aktiviert und laufen lassen.
LogDb scheint die Ursache zu sein.
Kann mir einer helfen was ich tun kann.
Als DB Server läuft auf einem Virtuellen windows Server 2012 ein Mysql mit Mysql Workbench.
Warum arbeitet der so langsam ?
active-timers: 107; max-active timers: 121; max-timer-load: 71 min-tmrHandlingTm: 0.0ms; max-tmrHandlingTm: 3535.6ms; totAvgDly: 340.9ms
name function max count total average maxDly avgDly TS Max call param Max call
logdb DbLog_Get 57409 1744 17062110.85 9783.32 0.00 0.00 23.09. 05:02:04 HASH(logdb); logdb; HISTORY; INT; 2021-09-22_05:05:00; 2021-09-23_05:04:59; CM160:Wattgerechnet::; grenzeziehen:state::; MQTT2_DVES_547F9C:ENERGY_Power::; MQTT2_DVES_32B440:ENERGY_Power::; MQTT2_DVES_5481E6:ENERGY_Power::; MQTT2_DVES_3B3D89:ENERGY_Power::; MQTT2_DVES_54B87F:ENERGY_Power::
tmr-UVRm_GetUpdate HASH(0x2762a68) 3535 1115 1209534.39 1084.78 21606.52 45.90 23.09. 12:03:01 HASH(myuvr_messuebersicht)
CM160 CM160_read 2330 337382 2913830.49 8.64 0.00 0.00 24.09. 04:01:43 HASH(CM160)
tmr-LW12_updateStatus HASH(0x2a3fea0) 2071 3345 566860.28 169.46 20222.13 49.01 22.09. 15:13:46 HASH(ledKueche)
tmr-OWX_Init HASH(0x2ecda60) 1683 1 1683.03 1683.03 1.35 1.35 22.09. 11:39:12 HASH(OWio1)
tmr-at_Exec HASH(0x1a58790) 1356 2 2380.01 1190.00 1.49 1.16 22.09. 22:58:01 HASH(StatusMailTH)
tmr-CUL_HM_ActCheck ActionDetector 1300 334 7028.93 21.04 19717.37 105.38 22.09. 11:48:33 ActionDetector
LAN_HmUART HMUARTLGW_Read 1173 30764 706051.79 22.95 0.00 0.00 23.09. 16:46:27 HASH(LAN_HmUART)
ActionDetectorMail notify_Exec 1139 2 2061.62 1030.81 0.00 0.00 22.09. 11:48:33 HASH(ActionDetectorMail); HASH(ActionDetector)
n_batt_chk notify_Exec 1134 287452 97611.22 0.34 0.00 0.00 23.09. 16:46:27 HASH(n_batt_chk); HASH(Ausentuersensor_Haustuere)
tmr-at_Exec HASH(0x22c02b0) 824 2 1582.77 791.39 2.00 1.55 23.09. 08:05:00 HASH(Alle_Heizungen_Auto1)
tmr-at_Exec HASH(0x11d3750) 548 2 1087.19 543.60 2.08 1.76 23.09. 05:23:00 HASH(Alarmanlage_off_0610)
ANLAGE_SCHARF dummy_Set 545 50 1634.75 32.70 0.00 0.00 23.09. 05:23:00 HASH(ANLAGE_SCHARF); ANLAGE_SCHARF; off
act_on_ANLAGE_SCHARF_off notify_Exec 533 6 1293.39 215.57 0.00 0.00 23.09. 05:23:00 HASH(act_on_ANLAGE_SCHARF_off); HASH(ANLAGE_SCHARF)
tmr-rssFeed_GetUpdate HASH(0x4799b78) 349 55 16022.59 291.32 437.65 11.77 22.09. 16:38:30 HASH(myNewsNTV)
update_stromzaehler_notify notify_Exec 348 54111 1698341.34 31.39 0.00 0.00 24.09. 18:43:38 HASH(update_stromzaehler_notify); HASH(CM160)
Notify_Bewegungsmelder_Stellplatz_Sunrise notify_Exec 316 596 1645.78 2.76 0.00 0.00 24.09. 06:30:04 HASH(Notify_Bewegungsmelder_Stellplatz_Sunrise); HASH(BewegungsmelderStellplatz)
tmr-at_Exec HASH(0x11d3528) 315 2 553.42 276.71 2.21 1.78 22.09. 22:30:00 HASH(Alarmanlage_on_2230)
act_on_ANLAGE_SCHARF_on notify_Exec 299 4 528.27 132.07 0.00 0.00 22.09. 22:30:00 HASH(act_on_ANLAGE_SCHARF_on); HASH(ANLAGE_SCHARF)
FlammTronik ECMD_Read 277 65735 2084732.76 31.71 0.00 0.00 23.09. 10:18:15 HASH(FlammTronik)
Notify_Bewegungsmelder_Stellplatz_Sunset notify_Exec 261 596 2019.09 3.39 0.00 0.00 22.09. 20:04:03 HASH(Notify_Bewegungsmelder_Stellplatz_Sunset); HASH(BewegungsmelderStellplatz)
sunRiseOnFront notify_Exec 254 124 3991.39 32.19 0.00 0.00 23.09. 05:47:56 HASH(sunRiseOnFront); HASH(Ausentuersensor_Haustuere)
tmr-HMUARTLGW_CheckCmdResp HASH(0x49fd718) 253 347 2729.73 7.87 43464.87 1356.33 24.09. 15:48:39 HASH(LAN_HmUART)
tmr-at_Exec HASH(0x3a860b8) 243 669 129734.57 193.92 41248.64 2322.07 22.09. 23:03:15 HASH(grenzeziehenat)
OWio1 OWX_Read 234 70985 1585548.86 22.34 0.00 0.00 23.09. 09:08:56 HASH(OWio1)
HVE_AUS notify_Exec 221 2884 1652.34 0.57 0.00 0.00 23.09. 10:18:15 HASH(HVE_AUS); HASH(Heizkessel)
HVStatus dummy_Set 220 276 764.74 2.77 0.00 0.00 23.09. 10:18:15 HASH(HVStatus); HVStatus; AUS
tmr-MPD_watch_idle HASH(0x2fd4210) 212 3344 9039.75 2.70 54325.89 62.13 23.09. 09:55:59 HASH(myMPD)
es_klingelt_esp8622 notify_Exec 200 1268 4816.51 3.80 0.00 0.00 24.09. 14:33:07 HASH(es_klingelt_esp8622); HASH(MQTT2_Klingel_ESP)
tmr-OWID_GetValues HASH(0x4865190) 184 669 45180.32 67.53 6483.65 57.68 23.09. 05:38:50 HASH(OWX_00_000000000000)
tmr-MQTT2_SERVER_keepaliveChecker HASH(0x3937890) 175 18603 58777.47 3.16 53408.40 794.92 23.09. 08:24:57 HASH(MQTT2_FHEM_Server)
tmr-at_Exec HASH(0xad37d60) 169 3 276.77 92.26 2.86 2.25 24.09. 07:21:28 HASH(checkFlutlichtFrontSunrise)
tmr-__ANON__ HASH(0xbc6bbb8) 169 7 436.11 62.30 0.00 0.00 24.09. 13:36:15 HASH(MQTT2_FHEM_Server_192.168.4.163_54354)
tmr-__ANON__ HASH(0xafab350) 159 7 375.64 53.66 0.00 0.00 24.09. 08:59:01 HASH(MQTT2_FHEM_Server_192.168.4.152_57933)
logdb DbLog_Log 157 287452 1311716.64 4.56 0.00 0.00 24.09. 04:09:01 HASH(logdb); HASH(myuvr_messuebersicht)
tmr-CUL_HM_procQs CUL_HM_procQs 147 845 22221.75 26.30 51188.19 534.10 22.09. 12:20:25 CUL_HM_procQs
myMPD MPD_Set 145 939 13500.47 14.38 0.00 0.00 24.09. 14:33:07 HASH(myMPD); myMPD; playfile; jingles_8.mp3
tmr-__ANON__ HASH(0xbf36a28) 145 4 146.05 36.51 0.00 0.00 24.09. 16:04:42 HASH(MQTT2_FHEM_Server_192.168.4.152_60235)
Rauchmelder_Team CUL_HM_Set 143 26 337.81 12.99 0.00 0.00 23.09. 05:23:00 HASH(Rauchmelder_Team); Rauchmelder_Team; alarmOff
tmr-at_Exec HASH(0x6bba8e0) 135 3 238.47 79.49 4.26 2.49 22.09. 20:14:03 HASH(checkFlutlichtFrontSunset)
tmr-at_Exec HASH(0x6d91d10) 134 3 214.11 71.37 2.07 1.57 22.09. 22:34:42 HASH(checkFlutlichtFrontSunset)
list dblog:
Internals:
COLUMNS field length used for Device: 64, Type: 64, Event: 512, Reading: 64, Value: 128, Unit: 32
CONFIGURATION ./db.conf
DEF ./db.conf .*:.*
FUUID 5c4c3e85-f33f-2bfa-63f6-ab259f451e8eb53a
FVERSION 93_DbLog.pm:v4.12.3-s24440/2021-05-15
MODE asynchronous
MODEL MYSQL
NAME logdb
NR 72
NTFY_ORDER 50-logdb
PID 30857
REGEXP .*:.*
STATE connected
TYPE DbLog
UTF8 1
dbconn mysql:database=fhem1;host=192.168.4.9;port=3306
dbuser fhem
HELPER:
COLSET 1
DEVICECOL 64
EVENTCOL 512
OLDSTATE connected
PACKAGE main
READINGCOL 64
TC current
TH history
TYPECOL 64
UNITCOL 32
VALUECOL 128
VERSION 4.12.3
READINGS:
2021-09-24 19:30:02 CacheOverflowLastNum 0
2021-09-11 10:47:49 CacheOverflowLastState normal
2021-09-24 19:30:06 CacheUsage 2
2021-09-24 19:30:02 NextSync 2021-09-24 19:30:07 or if CacheUsage 500 reached
2021-08-09 21:03:34 lastRowsDeleted 0
2019-05-28 00:07:53 reduceLogState Rows processed: 114063, deleted: 102928, updated: 2891, time: 126.00sec
2021-09-24 19:30:02 state connected
helper:
bm:
DbLog_Get:
cnt 1744
dmx -1000
dtot 0
dtotcnt 0
mTS 23.09. 05:02:04
max 57.4092779159546
tot 17062.1108458042
mAr:
HASH(logdb)
logdb
HISTORY
INT
2021-09-22_05:05:00
2021-09-23_05:04:59
CM160:Wattgerechnet::
grenzeziehen:state::
MQTT2_DVES_547F9C:ENERGY_Power::
MQTT2_DVES_32B440:ENERGY_Power::
MQTT2_DVES_5481E6:ENERGY_Power::
MQTT2_DVES_3B3D89:ENERGY_Power::
MQTT2_DVES_54B87F:ENERGY_Power::
DbLog_Log:
cnt 287782
dmx -1000
dtot 0
dtotcnt 0
mTS 24.09. 04:09:01
max 0.157574892044067
tot 1313.45705509186
mAr:
HASH(logdb)
HASH(myuvr_messuebersicht)
DbLog_Set:
cnt 86895
dmx -1000
dtot 0
dtotcnt 0
mTS 24.09. 07:12:37
max 0.0042729377746582
tot 43.0093612670898
mAr:
HASH(logdb)
logdb
?
DbLog_regexpFn:
cnt 1743
dmx -1000
dtot 0
dtotcnt 0
mTS 23.09. 17:08:30
max 0.000465869903564453
tot 0.229523897171021
mAr:
logdb
CM160:Wattgerechnet:: grenzeziehen:state:: MQTT2_DVES_547F9C:ENERGY_Power:: MQTT2_DVES_32B440:ENERGY_Power:: MQTT2_DVES_5481E6:ENERGY_Power:: MQTT2_DVES_3B3D89:ENERGY_Power:: MQTT2_DVES_54B87F:ENERGY_Power::
Attributes:
DbLogExclude .*
DbLogSelectionMode Exclude/Include
DbLogType History
asyncMode 1
cacheEvents 1
commitMode basic_ta:on
devStateIcon connected:10px-kreis-gruen .*disconnect:10px-kreis-rot .*done:10px-kreis-gruen
event-on-change-reading .*
room 00_Home,5_SYSTEM
syncInterval 5
useCharfilter 1
userReadings DbFileSize:reduceLogState.* { (split(' ',`du -m fhem.db`))[0] }
valueFn {
if ($DEVICETYPE eq 'GPIO4')
{
if ($EVENT eq 'temperature: 85')
{
$IGNORE = 1;
}
}
elsif ($DEVICETYPE eq 'CUL_HM')
{
if ($READING eq 'desired-temp' && $VALUE eq 'off')
{
$VALUE = 4.5;
}
elsif ($READING eq 'desired-temp' && $VALUE eq 'on')
{
$VALUE = 30.5;
}
elsif ($READING eq 'desired-temp' && $VALUE !~ m/^[-+]?[\.\d]/)
{
$IGNORE = 1;
}
}
}
verbose 1
Warum deine DB so langsam ist kann ich dir nicht beantworten.
Aber DbLog_Get ist die Anzeigefunktion für SVG-Plots. Diese Selektion ist bei dir wahrscheinlich noch blockierend eingestellt.
Führe ein "set .. configCheck" aus und schau dir die Hinweise in der Rubrik "Result of plot generation method check" an.
Grüße,
Heiko
Hallo,
ein set logdb configcheck liefert dies:
Result of version check
Used Perl version: 5.24.1
Used DBI (Database independent interface) version: 1.636
Used DBD (Database driver) version mysql: 4.041
Used DbLog version: 4.12.3.
Your local DbLog module is up to date.
Recommendation: No update of DbLog is needed. Caution: Full UTF-8 support exists from DBD version 4.032, but installing DBD version 4.042 is highly suggested.
Result of configuration read check
Connection parameter store type: file
Connection parameter: Connection -> mysql:database=fhem1;host=192.168.4.9;port=3306, User -> fhem, Password -> read o.k.
Result of connection check
Connection to database fhem1 successfully done.
Recommendation: settings o.k.
Result of encoding check
Encoding used by Client (connection): UTF8
Encoding used by DB fhem1: UTF8
Recommendation: settings o.k. Caution: Full UTF-8 support exists from DBD version 4.032, but installing DBD version 4.042 is highly suggested. If you want use UTF8 database option, you must update DBD (Database driver) to at least version 4.032.
Result of logmode check
Logmode of DbLog-device logdb is: asynchronous
Recommendation: settings o.k.
Result of insert mode check
Insert mode of DbLog-device logdb is: Array
Recommendation: Setting attribute "bulkInsert" to "1" may result a higher write performance in most cases. Feel free to try this mode.
Result of plot generation method check
WEB: plotfork=1 / plotEmbed=2
WEBphone: plotfork=1 / plotEmbed=2
WEBtablet: plotfork=1 / plotEmbed=2
Recommendation: settings o.k.
Result of table 'history' check
Column width set in DB history: 'DEVICE' = 64, 'TYPE' = 64, 'EVENT' = 512, 'READING' = 64, 'VALUE' = 255, 'UNIT' = 32
Column width used by logdb: 'DEVICE' = 64, 'TYPE' = 64, 'EVENT' = 512, 'READING' = 64, 'VALUE' = 128, 'UNIT' = 32
Recommendation: settings o.k.
Result of table 'current' check
Column width set in DB current: 'DEVICE' = 64, 'TYPE' = 64, 'EVENT' = 512, 'READING' = 64, 'VALUE' = 128, 'UNIT' = 32
Column width used by logdb: 'DEVICE' = 64, 'TYPE' = 64, 'EVENT' = 512, 'READING' = 64, 'VALUE' = 128, 'UNIT' = 32
Recommendation: settings o.k.
Result of check 'Search_Idx' availability
The index 'Search_Idx' is missing.
Recommendation: You can create the index by executing statement 'CREATE INDEX Search_Idx ON `history` (DEVICE, READING, TIMESTAMP) USING BTREE;'
Depending on your database size this command may running a long time.
Please make sure the device 'logdb' is operating in asynchronous mode to avoid FHEM from blocking when creating the index.
Note: If you have just created another index which covers the same fields and order as suggested (e.g. a primary key) you don't need to create the 'Search_Idx' as well !
Result of check 'Report_Idx' availability for DbRep-devices
At least one DbRep-device assigned to logdb is used. Index 'Report_Idx' exists and contains recommended fields 'TIMESTAMP', 'READING'.
Recommendation: settings o.k.
hab jetzt etwas gefunden auf dem mysql server, und zwar habe ich "mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16"
ausgeführt und dadurch den innodb buffer auf 9 gb gesetzt. läuft jetzt flüsiger.
Gruß Josty
Das ist schonmal gut, aber lege dir den Index noch an wenn du keinen Ersatz in Form eines primary keys hast.
Ohne Index sind die Selektionen unterirdisch.
Result of check 'Search_Idx' availability
The index 'Search_Idx' is missing.
Recommendation: You can create the index by executing statement 'CREATE INDEX Search_Idx ON `history` (DEVICE, READING, TIMESTAMP) USING BTREE;'
Depending on your database size this command may running a long time.
Please make sure the device 'logdb' is operating in asynchronous mode to avoid FHEM from blocking when creating the index.
Note: If you have just created another index which covers the same fields and order as suggested (e.g. a primary key) you don't need to create the 'Search_Idx' as well !
Hallo,
Ja danke, habe den Index jetzt mit viel mühe angelegt.
Jetzt rennt fhem, so schnell war es noch nie.
Denke kann man als gelößt ansehen.
Gruß Josty
Bitte, keine Ursache.
Den Index mit viel Mühe angelegt ?
In dem configCheck steht doch der Befehl schon drin. Außerdem gibt es noch DbRep.
Dort gibt es ein "set ... index recreate_Search_Idx" Kommando um den Index einfach anzulegen.
Grüße,
Heiko