[gelöst] DBLog / MariaDB - optimizeTables verringert die physische Größe nicht

[DS_Starter]

Ich habe noch ein besseres SQL für die Speicherplatzbelegung gefunden.

Code Auswählen Erweitern


SELECT ROUND((SUM(data_length+index_length+data_free) + (COUNT(*) * 300 * 1024))/1048576+150, 2) AS MegaBytes FROM information_schema.TABLES WHERE table_schema = 'DATABASE-NAME'


Enthält den zzgl. den Freiplatz  sowie Overhead-Größe und die Metadatengröße.

Das baue ich mal mit ein.

[DS_Starter]

Hallo zusammen,

ich habe optimizeTables überarbeitet. Es gibt mehr Informationen und die Größenangaben werden genauer ermittelt.
Neu ist der Aufruf von optimizeTables mit Optionen:

Code Auswählen Erweitern


optimizeTables [showInfo | execute] - optimiert die Tabellen in der angeschlossenen Datenbank (MySQL).

    showInfo : zeigt Informationen zum belegten / freien Speicherplatz innerhalb der Datenbank
    execute : führt die Optimierung aller Tabellen in der Datenbank aus


Man kann sich also vorher anschauen ob sich ein optimizeTables evtl. lohnt. Aufgerufen mit showInfo zeigt die Funktion diese Infos:

Code Auswählen Erweitern


SizeDbBegin_MB   Data size -> 1831.15, Index size -> 1487.75, Space free -> 27, Overhead -> 150.59


Man sieht in dem Beispiel sind 27 MB Freiplatz in der DB vorhanden die man gewinnen könnte. Mit verbose 5 im Device aufgerufen sieht man die genauen Daten:

2022.01.29 17:57:36.488 3: DbRep Rep.LogDBShort.OptimizeTable - ################################################################
2022.01.29 17:57:36.489 3: DbRep Rep.LogDBShort.OptimizeTable - ###          New optimize table / vacuum execution           ###
2022.01.29 17:57:36.489 3: DbRep Rep.LogDBShort.OptimizeTable - ################################################################
2022.01.29 17:57:36.490 3: DbRep Rep.LogDBShort.OptimizeTable - execute command before optimize: 'set LogDBShort reopen 7200'
2022.01.29 17:57:36.490 2: DbLog LogDBShort: Connection closed until 19:57:36 (7200 seconds).
2022.01.29 17:57:36.523 4: DbRep Rep.LogDBShort.OptimizeTable - Database connect - user: fhemshort, UTF-8 option set: yes
2022.01.29 17:57:36.526 4: DbRep Rep.LogDBShort.OptimizeTable - Searching for tables inside database fhemshort....
2022.01.29 17:57:36.528 5: DbRep Rep.LogDBShort.OptimizeTable - ......... Table definition found: .........
2022.01.29 17:57:36.529 5: DbRep Rep.LogDBShort.OptimizeTable - Avg_row_length: 144
2022.01.29 17:57:36.529 5: DbRep Rep.LogDBShort.OptimizeTable - Collation: utf8_bin
2022.01.29 17:57:36.530 5: DbRep Rep.LogDBShort.OptimizeTable - Comment:
2022.01.29 17:57:36.530 5: DbRep Rep.LogDBShort.OptimizeTable - Create_options:
2022.01.29 17:57:36.530 5: DbRep Rep.LogDBShort.OptimizeTable - Create_time: 2022-01-01 12:15:00
2022.01.29 17:57:36.531 5: DbRep Rep.LogDBShort.OptimizeTable - Data_free: 0
2022.01.29 17:57:36.531 5: DbRep Rep.LogDBShort.OptimizeTable - Data_length: 131072
2022.01.29 17:57:36.531 5: DbRep Rep.LogDBShort.OptimizeTable - Engine: InnoDB
2022.01.29 17:57:36.532 5: DbRep Rep.LogDBShort.OptimizeTable - Index_length: 0
2022.01.29 17:57:36.532 5: DbRep Rep.LogDBShort.OptimizeTable - Max_data_length: 0
2022.01.29 17:57:36.532 5: DbRep Rep.LogDBShort.OptimizeTable - Name: current
2022.01.29 17:57:36.533 5: DbRep Rep.LogDBShort.OptimizeTable - Row_format: Compact
2022.01.29 17:57:36.533 5: DbRep Rep.LogDBShort.OptimizeTable - Version: 10
2022.01.29 17:57:36.533 5: DbRep Rep.LogDBShort.OptimizeTable - ......... Table definition END ............
2022.01.29 17:57:36.534 5: DbRep Rep.LogDBShort.OptimizeTable - ......... Table definition found: .........
2022.01.29 17:57:36.534 5: DbRep Rep.LogDBShort.OptimizeTable - Avg_row_length: 122
2022.01.29 17:57:36.534 5: DbRep Rep.LogDBShort.OptimizeTable - Collation: utf8_bin
2022.01.29 17:57:36.535 5: DbRep Rep.LogDBShort.OptimizeTable - Comment:
2022.01.29 17:57:36.535 5: DbRep Rep.LogDBShort.OptimizeTable - Create_options:
2022.01.29 17:57:36.535 5: DbRep Rep.LogDBShort.OptimizeTable - Create_time: 2022-01-01 12:28:00
2022.01.29 17:57:36.536 5: DbRep Rep.LogDBShort.OptimizeTable - Data_free: 28311552
2022.01.29 17:57:36.536 5: DbRep Rep.LogDBShort.OptimizeTable - Data_length: 1919959040
2022.01.29 17:57:36.536 5: DbRep Rep.LogDBShort.OptimizeTable - Engine: InnoDB
2022.01.29 17:57:36.537 5: DbRep Rep.LogDBShort.OptimizeTable - Index_length: 1560018944
2022.01.29 17:57:36.537 5: DbRep Rep.LogDBShort.OptimizeTable - Max_data_length: 0
2022.01.29 17:57:36.537 5: DbRep Rep.LogDBShort.OptimizeTable - Name: history
2022.01.29 17:57:36.538 5: DbRep Rep.LogDBShort.OptimizeTable - Row_format: Compact
2022.01.29 17:57:36.538 5: DbRep Rep.LogDBShort.OptimizeTable - Version: 10
2022.01.29 17:57:36.538 5: DbRep Rep.LogDBShort.OptimizeTable - ......... Table definition END ............

Mit verbose 4 seht ihr die neuen SQL-Statements zur genaueren Ermittlung der Größen:

Code Auswählen Erweitern


2022.01.29 18:13:45.647 3: DbRep Rep.LogDBShort.OptimizeTable - ################################################################
2022.01.29 18:13:45.648 3: DbRep Rep.LogDBShort.OptimizeTable - ###          New optimize table / vacuum execution           ###
2022.01.29 18:13:45.649 3: DbRep Rep.LogDBShort.OptimizeTable - ################################################################
2022.01.29 18:13:45.649 3: DbRep Rep.LogDBShort.OptimizeTable - execute command before optimize: 'set LogDBShort reopen 7200'
2022.01.29 18:13:45.650 2: DbLog LogDBShort: Connection closed until 20:13:45 (7200 seconds).
2022.01.29 18:13:45.685 4: DbRep Rep.LogDBShort.OptimizeTable - Database connect - user: fhemshort, UTF-8 option set: yes
2022.01.29 18:13:45.689 4: DbRep Rep.LogDBShort.OptimizeTable - Searching for tables inside database fhemshort....
2022.01.29 18:13:45.691 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: SELECT table_name, round (data_length / 1024 / 1024, 2) "data size in MB", round (index_length / 1024 / 1024, 2) "index size in MB", round (data_free / 1024 / 1024, 2) "free space in MB" FROM information_schema.TABLES where table_schema = 'fhemshort';
2022.01.29 18:13:45.692 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: SELECT round ((COUNT(*) * 300 * 1024)/1048576 + 150, 2) "overhead in MB" FROM information_schema.TABLES where table_schema = 'fhemshort';
2022.01.29 18:13:45.694 3: DbRep Rep.LogDBShort.OptimizeTable - Estimate of fhemshort before optimize (MB): Data size -> 1831.15, Index size -> 1487.75, Space free -> 26, Overhead -> 150.59
2022.01.29 18:13:45.694 3: DbRep Rep.LogDBShort.OptimizeTable - Optimizing tables
2022.01.29 18:13:45.695 3: DbRep Rep.LogDBShort.OptimizeTable - Optimizing table `current` (INNODB). It may take a while ...
2022.01.29 18:13:45.695 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: OPTIMIZE TABLE `current`
2022.01.29 18:13:45.832 3: DbRep Rep.LogDBShort.OptimizeTable - Table 1 `current` optimized successfully.
2022.01.29 18:13:45.833 3: DbRep Rep.LogDBShort.OptimizeTable - Optimizing table `history` (INNODB). It may take a while ...
2022.01.29 18:13:45.833 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: OPTIMIZE TABLE `history`

2022.01.29 18:26:31.211 3: DbRep Rep.LogDBShort.OptimizeTable - Table 2 `history` optimized successfully.
2022.01.29 18:26:31.214 3: DbRep Rep.LogDBShort.OptimizeTable - 2 tables have been optimized.
2022.01.29 18:26:31.215 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: SELECT table_name, round (data_length / 1024 / 1024, 2) "data size in MB", round (index_length / 1024 / 1024, 2) "index size in MB", round (data_free / 1024 / 1024, 2) "free space in MB" FROM information_schema.TABLES where table_schema = 'fhemshort';
2022.01.29 18:26:31.219 4: DbRep Rep.LogDBShort.OptimizeTable - SQL execute: SELECT round ((COUNT(*) * 300 * 1024)/1048576 + 150, 2) "overhead in MB" FROM information_schema.TABLES where table_schema = 'fhemshort';
2022.01.29 18:26:31.220 3: DbRep Rep.LogDBShort.OptimizeTable - Estimate of fhemshort after optimize (MB): Data size -> 1462.09, Index size -> 1223, Space free -> 0, Overhead -> 150.59
2022.01.29 18:26:31.221 3: DbRep Rep.LogDBShort.OptimizeTable - Optimize tables of fhemshort finished - total time (hh:mm:ss): 00:12:45
2022.01.29 18:26:31.226 4: DbRep Rep.LogDBShort.OptimizeTable - execute command after optimize: 'set LogDBShort reopen'
2022.01.29 18:26:31.279 2: DbRep Rep.LogDBShort.OptimizeTable - command message after optimize: "Reopen executed."
2022.01.29 18:26:31.287 3: DbRep Rep.LogDBShort.OptimizeTable - Optimize tables finished successfully.


Die Readings zeigen nach Abschluß nun mehr Details:

Code Auswählen Erweitern


SizeDbBegin_MB   Data size -> 1831.15, Index size -> 1487.75, Space free -> 26, Overhead -> 150.59
SizeDbEnd_MB     Data size -> 1462.09, Index size -> 1223, Space free -> 0, Overhead -> 150.59


Deutlcih wird auch, dass nicht nur Space free nun 0 ist, auch den Datenbereich wird reorganisiert und dadurch Platz freigegeben.

Hinweis: Man kann sich den Fortschritt des optimize in einem separaten DbRep-Device mit dem Befehl "get ... procinfo" anschauen. Screenshot seht ihr im Anhang. Die Spalte Progress zeigt den Fortschritt in %.

Die Version liegt zunächst in meinem contrib. Wer schon mal testen möchte kann sie sich herunterladen.

Zum Download in der FHEMWEB Kommandozeile inklusive der Anführungszeichen angeben und danach FHEM restarten:

Code Auswählen Erweitern


"wget -qO ./FHEM/93_DbRep.pm https://svn.fhem.de/fhem/trunk/fhem/contrib/DS_Starter/93_DbRep.pm"


Grüße,
Heiko