Wie kann ich auslesen wieviel speicher eine Zeile belegt?

[Lennynero]

Hi,

gibt es einen MySQL Befehl, mit dem ich auslesen kann wieviel Speicher eine Zeile belegt?

So etwa:

SELECT Memory(), id FROM table

Gruss,
Markus

[onemorenerd]

Du kannst mit DESCRIBE `table` die Spaltentypen herausfinden. Dann weißt du schon mal wie viel Speicher ein Datensatz maximal belegen kann.
Falls es Spalten mit variable breiten Typen geben, musst du den konkreten Datensatz herausziehen und analysieren.

Wofür soll das eigentlich gut sein? Warum willst du das wissen?

[Lennynero]

Nach einem "ALTER TABLE" (Änderung eines ENUM-Feldes) sind einige (nicht alle!) Textfelder einer Spalte auf 256 Zeichen gekürzt worden.

Es ist mir bisher noch nicht ersichtlich wo die Gemeinsamkeiten der beschnittenen Zeilen liegen und es ist ebenfalls erstaunlich, das ich wieder Texte die länger als 256 Zeichen sind reinschreiben kann.

Tabellentyp ist ndbcluster.

[AmicaNoctis]

Wie lautete denn das ALTER TABLE Statement, was du abgesetzt hast und wie sieht das komplette CREATE TABLE Statement aus, welches du mit SHOW CREATE TABLE bekommst?

Amica

[Lennynero]

Create Statement:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foreign_key_1` int(11) DEFAULT NULL,
  `foreign_key_2` int(11) DEFAULT NULL,
  `foreign_key_3` int(11) DEFAULT NULL,
  `enum_field_1` enum('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci 
  DEFAULT 'option_1',
  `enum_field_2` enum('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci 
  DEFAULT NULL,
  `enum_field_3` enum('Keine Angabe','2002','2003','2004','2005',
  '2006','2007','2008','2009','2010') DEFAULT NULL,
  `enum_field_4` enum('option_1','option_2','option_3','option_4','option_5','option_6',
  'option_7','option_8','option_9','option_10','option_11','option_12','option_13') DEFAULT NULL,
  `varchar_field_1` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_2` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_3` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_4` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_5` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_6` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `text_field_1` text CHARACTER SET latin1 COLLATE latin1_german1_ci,
  `varchar_field_7` varchar(255) DEFAULT NULL,
  `varchar_field_8` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_9` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_10` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_11` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `set_field_1` set('option_1','option_2','option_3','option_4') CHARACTER SET latin1 
COLLATE latin1_german1_ci DEFAULT NULL,
  `set_field_1` set('option_1','option_2','option_3','option_4') DEFAULT NULL,
  `text_field_2` text CHARACTER SET latin1 COLLATE latin1_german1_ci,
  `varchar_field_12` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `varchar_field_13` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `set_field_1` set('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci 
  DEFAULT NULL,
  `set_field_2` set('option_1','option_2','option_3','option_4') CHARACTER SET latin1 
  COLLATE latin1_german1_ci DEFAULT NULL,
  `int_field_1` tinyint(4) DEFAULT NULL,
  `set_field_3` set('option_1','option_2','option_3','option_4','option_5','option_6') 
  CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `set_field_4` set('option_1','option_2') CHARACTER SET latin1 COLLATE latin1_german1_ci 
  DEFAULT NULL,
  `text_field_3` text CHARACTER SET latin1 COLLATE latin1_german1_ci,
  `int_field_1` tinyint(4) DEFAULT NULL,
  `int_field_2` tinyint(4) DEFAULT NULL,
  `int_field_3` tinyint(4) DEFAULT NULL,
  `int_field_4` tinyint(4) DEFAULT NULL,
  `int_field_5` tinyint(4) DEFAULT NULL,
  `int_field_6` tinyint(4) DEFAULT NULL,
  `int_field_7` tinyint(4) DEFAULT NULL,
  `int_field_8` tinyint(4) DEFAULT NULL,
  `int_field_9` tinyint(4) DEFAULT NULL,
  `int_field_10` tinyint(4) DEFAULT NULL,
  `int_field_11` tinyint(4) DEFAULT NULL,
  `int_field_12` tinyint(4) DEFAULT NULL,
  `varchar_field_14` varchar(255) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL COMMENT 'Status',
  `created` datetime DEFAULT NULL COMMENT 'Erstellt am',
  `created_by` int(11) DEFAULT NULL COMMENT 'Erstellt von',
  `modified` datetime DEFAULT NULL COMMENT 'Bearbeitet am',
  `modified_by` int(11) DEFAULT NULL COMMENT 'Bearbeitet von',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_varchar_field_14` (`varchar_field_14`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Alter-Table Statement:

ALTER TABLE `table`
MODIFY COLUMN `enum_field_3`
ENUM('Keine Angabe','2002','2003','2004','2005','2006','2007','2008','2009','2010');

(War vorher ohne 2010).

Auch das dritte Textfeld wurde gekürzt, bei Textefeld 2 gibt es keine Text der so lang war /ist.

[h3ll]

Das Datenbankdesign ist doch ziemlicher Schrott. Am besten alles niederreißen und vernünftig aufbauen.

http://de.wikipedia.org/wiki/Normali...28Datenbank%29

[AmicaNoctis]

Dann hast du vermutlich die Gesamtspeicherbreite eines Datensatzes überschritten, was bei so vielen Spalten kein Wunder ist.

[Lennynero]

Das Datenbankdesign ist doch ziemlicher Schrott. Am besten alles niederreißen und vernünftig aufbauen.

Normalisierung (Datenbank) ? Wikipedia

Die Praxis zeigt durchaus, das nicht immer "normalisieren" der zwingend beste Weg ist.

Nebenbei finde ich es beachtlich, das du in der Lage bist Aufgrund des Layoutes einer Tabelle auf das Design der ganzen Datenbank zu interpolieren. Sicherlich könnte man einige (wenn nicht sogar alle) ENUM und SET Felder durch weitere Tabellen abbilden, das würde aber jetzt einen Rattenschwanz an arbeit nach sich ziehen, der aber überflüssig ist da wir sowieso schon an der nächsten Version arbeiten. Mein Problem besteht aber jetzt und bis die nächte Version läuft wird es noch ein paar Wochen evt. sogar Monate dauern.

Es ist ja auch so, das man in die entsprechenden Spalten auch wieder Texte die länger als 256 Zeichen sind schreiben kann und es ist ja nicht jede Zeile betroffen gewesen.

Danke für deine Belehrung.

[Lennynero]

Dann hast du vermutlich die Gesamtspeicherbreite eines Datensatzes überschritten, was bei so vielen Spalten kein Wunder ist.

Genau das vermute ich auch, deshalb auch die Frage nach der Möglichkeit die Gesamtspeicherbreite der Tupel zu ermitteln.

[AmicaNoctis]

Die Praxis zeigt durchaus, das nicht immer "normalisieren" der zwingend beste Weg ist.

Dort wo du diese Aussage aufgeschnappt hast, steht bestimmt, dass es nicht immer sinnvoll oder machbar ist, bis zur 5NF zu normalisieren. Aber 3NF oder BCNF darf es schon sein und das bringt in der Praxis auch keine Nachteile.

[Lennynero]

Dort wo du diese Aussage aufgeschnappt hast, steht bestimmt, dass es nicht immer sinnvoll oder machbar ist, bis zur 5NF zu normalisieren. Aber 3NF oder BCNF darf es schon sein und das bringt in der Praxis auch keine Nachteile.

Abhängig vom Verwendungszweck ist es nicht zwingend notwendig, aber letzten Endes ist das hier vorliegende DB-Design ja nicht wirklich neu und vor allem gewachsen, ein Relaunch steht wie erwähnt sowieso bevor. Von da aus ist es auch nicht meine Absicht über das Tabellendesign zu diskutieren, vielmehr habe ich anfangs eine recht klare Frage gestellt:

Hi,

gibt es einen MySQL Befehl, mit dem ich auslesen kann wieviel Speicher eine Zeile belegt?

So etwa:

SELECT Memory(), id FROM table

Gruss,
Markus

bekomme eine Belehrung über DB-Design und

Dann hast du vermutlich die Gesamtspeicherbreite eines Datensatzes überschritten, was bei so vielen Spalten kein Wunder ist.

Onemorenerds Antwort ging ja schon in die Richtung und auch eine Antwort: es gibt keine entsprechende Funktion wäre ja schon okay.

EDIT: Bzgl. ENUM vs. JOIN:
http://www.mysqlperformanceblog.com/...hat-is-faster/
http://stackoverflow.com/questions/3...vs-join-tables

[onemorenerd]

Achja, das hab ich vergessen zu erwähnen: Es gibt keine solche Funktion. Deshalb habe ich den "Umweg" gezeigt.

Ich bezweifle übrigens, dass o.g. ALTER-Statement die VARCHAR-Spalten in irgendeiner Form beeinflusst hat. Damit wurde schließlich nur deine ENUM-Spalte geändert. Die Textspalten waren vorher schon VARCHAR(255) und damit unmöglich länger als 255 Zeichen.
Zwar kann VARCHAR ab 5.0.3 bis 2^16 Zeichen lang sein, aber wenn man eine Spalte als VARCHAR(255) definiert, werden die Daten auf diese Länge beschnitten. Das geschieht schon beim Einfügen, nicht erst beim nächsten ALTER.

[Lennynero]

Die Felder waren weder vorher noch nachher VARCHAR Felder, sondern sind in allen Sicherung als Text-Felder definiert (und sind auch in der fehlerhaften Variante immer noch als Text-Felder definiert).

Was die Sache mit dem ALTER Befehl betriff: es ist reproduzierbar, d.h. ausgeführt auf dem "sauberen" Datenbestand werden einige (nicht alle) der Textfelder auf 256 Zeichen gekürzt.

Die Änderung des ENUM_Feldes mit Navicat (über Tabelle bearbeiten) oder dem MySQL Query Browser führt interessanterweise nicht zu diesem verhalten.

Es ist auch unerheblich ob ich dem ALTER Statement ein "DEFAULT NULL" oder nicht mitgebe.

Serverversion ist: 5.1.30-ndb-6.3.20, Tabellentyp (wie bereits erwähnt) ndbcluster.

[onemorenerd]

Sorry, ich hab dich misverstanden. Du hast kein Problem mit VARCHAR- sondern mit TEXT-Spalten.

Ich hab im Manual gewühlt und folgendes gefunden:

Quelle: MySQL :: MySQL 5.1 Reference Manual :: 10.5 Data Type Storage Requirements
TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000).

MySQL :: MySQL 5.1 Reference Manual :: 17.4.21 ndb_size.pl ? NDBCLUSTER Size Requirement Estimator

[Lennynero]

Sorry, ich hab dich misverstanden. Du hast kein Problem mit VARCHAR- sondern mit TEXT-Spalten.

Ich hab im Manual gewühlt und folgendes gefunden:

Thx. (ich müsste erst noch ein paar andere User bewerten, bevor ich dich wieder bewerten kann).

Das mit den 256 Zeichen scheint da ja sehr gut zu passen, nun muss ich noch herausfinden wieso die nur bei einigen Zeilen abgeschnitten werden (ein Oberflächlicher Vergleich von betroffenen und nicht betroffenen Zeilen offenbart mir zumindest keinen Hinweis).

MIt dem "reproduzierbarem Fehler" war ich dagegen zu voreilig: in etwa 50% der Tests wurde Text abgeschnitten, in anderen Fällen nicht.