[mysql] Database grootte

[dwilmer]

Stomme vraag misschien, maar hoe kan het dat een export kleiner is dan de grootte die phpmyadmin aangeeft? Dit is een vrij grote database (van een Magento webwinkel), en volgens PhpMyAdmin is de totale grootte 431MB. Als ik de hele zooi vervolgens exporteer krijg ik een SQL-bestand van 226MB. Hoe kan dit? Mis ik data, of is de grootte die PHPMyAdmin aangeeft veel te veel?

Ik heb wel een paar weken geleden een paar grote tabellen (bij elkaar 200MB) ge-truncate. Zouden die meegeteld worden?

Alvast bedankt!

[StephanVierkant]

Heb je gzip aangezet? ;-)

[dwilmer]

Nee, het is gewoon platte tekst. __dbname__.sql, gewoon te openen in kladblok of vim, mits je geduld hebt.

[ Voor 56% gewijzigd door dwilmer op 27-04-2011 15:14 ]

[WeeJeWel]

Omdat de database anders wordt opgeslagen dan een query lijkt me.

[bindsa]

Maximale executie tijd van PHP scripts bereikt, maar geen error omdat error_reporting uit staat?

[RobIII]

WeeJeWel schreef op woensdag 27 april 2011 @ 15:14:
Omdat de database anders wordt opgeslagen dan een query lijkt me.

Waarbij het bestand met de queries dan groter zou moeten zijn dan de DB zelf natuurlijk (Ik mag hopen dat je beseft dat een RDBMS data wel wat efficienter opslaat als een crapload aan ASCII data ).

Ik denk eerder dat 't te maken heeft met 't feit dat vrijgegeven space in een DB niet per definitie ook daadwerkelijk op schijf wordt vrijgemaakt. Als je 200Mb truncate zal die 200Mb aan pages gewoon in gebruik blijven (maar 'gemarkeerd' als vrij/herbruikbaar).

[ Voor 5% gewijzigd door RobIII op 27-04-2011 15:19 ]

[KabouterSuper]

Waarbij het bestand met de queries dan groter zou moeten zijn dan de DB zelf natuurlijk

Ik verwacht toch dat een create statement van een index minder ruimte inneemt dan de index zelf.

[RobIII]

KabouterSuper schreef op woensdag 27 april 2011 @ 15:17:
[...]


Ik verwacht toch dat een create statement van een index minder ruimte inneemt dan de index zelf.

Ik had 't over data Voor zover ik weet wordt de inhoud van een index niet geëxporteerd
Als je DB meer index-data bevat (op de TS afgaand) dan daadwerkelijke data dan vraag ik me af hoe je DB er uit ziet).

[ Voor 28% gewijzigd door RobIII op 27-04-2011 15:21 ]

[Johnny]

Zoals hierboven al gezegd nemen de indices ook nog wat ruimte in beslag als je er veel hebt (waaronde rook fulltext-indices)

Kolommen met datatypes van een vaste lengte zoals CHAR gebruiken ook altijd de maximale lengte, maar in de meeste moderne databases komen die niet zoveel voor.

[KabouterSuper]

RobIII schreef op woensdag 27 april 2011 @ 15:19:
[...]

Ik had 't over data Voor zover ik weet wordt de inhoud van een index niet geëxporteerd
Als je DB meer index-data bevat (op de TS afgaand) dan daadwerkelijke data dan vraag ik me af hoe je DB er uit ziet).

Indices worden inderdaad nooit geëxporteerd. Qua data heb je niet geheel gelijk. Natuurlijk heeft een query wat overhead ten opzichte van de data. Een beetje database reserveert echter wat ruimte in een block om updates uit te kunnen voeren. Je loopt anders teveel risico dat de inhoud van een veld na de update verdeeld raakt over twee blocks.

[RobIII]

KabouterSuper schreef op woensdag 27 april 2011 @ 15:29:
Een beetje database reserveert echter wat ruimte in een block om updates uit te kunnen voeren. Je loopt anders teveel risico dat de inhoud van een veld na de update verdeeld raakt over twee blocks.

Ik neem aan dat je met een block een page bedoelt en geen sector op de HDD? Ik weet niet precies hoe MySQL 't doet dus daar kan ik weinig over zeggen; maar ik vraag me af als ik een tabel heb met een varchar(255) veld of er dan ook daadwerkelijk altijd 255 bytes gereserveerd worden ook al worden er maar 2 gebruikt. Logica zegt me van wel (immers, er kan veel sneller bepaald worden waar een record begint door dat de offsets van records allemaal op recordsize-"interval" liggen), maar of dat ook zo is weet ik niet (en al zeker niet voor MySQL; MSSQL daarentegen reserveert AFAIK in ieder geval niet die grootte maar ook daar kan ik het mis hebben).

Anyway; we gaan offtopic
Ik blijf vooralsnog bij m'n initiële vermoeden

[ Voor 10% gewijzigd door RobIII op 27-04-2011 15:38 ]

[dwilmer]

Hier kan je het database-diagram vinden, als je benieuwd bent naar hoe het eruit ziet. Het zou inderdaad best eens kunnen zijn dat er veel index-data bij zit. http://www.magentocommerc.../magento_database_diagram

Over die truncate: de tabellen die toen zijn getruncate, zijn wel kleiner dan eerst. Volgens mij is het zo dat-ie de ruimte wel gereserveerd laat als je alles verwijdert (DELETE FROM ...), maar niet als je truncate.

[KabouterSuper]

Oracle noemt het Oracle blocks of logical blocks, en dit is hetzelfde als pages. Van mysql weet ik niet precies of voor een varchar(255) altijd 255 bytes gereserveerd worden. Bij Oracle is dit zeker niet het geval. Het gevolg is row chaining. Strikt gezien is dit overigens iets wat speelt op rij-niveau en niet op kolom/rij niveau.

En verder ontopic: gealloceerde maar niet gebruikte ruimte is onderdeel van je database, maar niet van je script. Dus dit is een goede reden waardoor je database groter is dan je script.

[ Voor 22% gewijzigd door KabouterSuper op 27-04-2011 15:44 ]

[MrHarry]

heb je ook alle drop tables etc.. mee genomen in de backup?

[dwilmer]

drop tables? nog nooit van gehoord....

wat ik heb gedaan:
- open PhpMyAdmin
- selecteer de database
- klik op 'exporteer'
- 'selecteer alles' (alle tabellen)
- klik onderin op 'start'

En het lijkt er op het eerste gezicht op dat alles goed wordt ge-exporteerd.

[RobIII]

Dan doe eens een optimize tables (zie link in mijn eerste post) om te zien of er nog "lucht" in je DB zit. Verder zou ik even een check, check, dubbelcheck doen of alles er inderdaad in zit en me dan verder geen zorgen maken over 't verschil in grootte.

[ Voor 4% gewijzigd door RobIII op 27-04-2011 15:46 ]

[KabouterSuper]

Ik zou me inderdaad geen zorgen maken. Als de laatste regel van de laatste tabel in je script staat, zit volgens Bartjens alles in je script.

[dwilmer]

'even' een check doen of alles erin zit... 't is ruim 200 MB, verdeeld over ruim 200 tabellen, waar de data best versplinterd in staat opgeslagen. Moet je voor de grap het database-diagram eens bekijken (zie link in vorige post).

edit @KabouterSuper: dan ga ik me geen zorgen maken.

[ Voor 10% gewijzigd door dwilmer op 27-04-2011 15:52 ]

[KabouterSuper]

200MB valt best mee. Gewoon even openen in een editor die grote bestanden aankan. En anders importeer je de database even in een testomgeving en kijk je of alles weer bestaat.

[dwilmer]

Dat laatste ga ik dan maar doen. Lang leve localhost!

UPDATE: tijdens het importeren gaat-ie dood... MySql server has gone away...

[ Voor 43% gewijzigd door dwilmer op 27-04-2011 15:58 ]

[RobIII]

dwilmer schreef op woensdag 27 april 2011 @ 15:50:
'even' een check doen of alles erin zit... '

Zo moeilijk is dat toch niet Kijk of de eerste paar records van de belangrijkste tabellen er in zitten, de laatste paar en neem even een paar steekproeven "in het midden". Rocket science...

[Jrz]

varchar(100) is 100 bytes (of misschien 200). Maar als de content 'abc' is, is het in de dump 3 bytes

[RobIII]

Jrz schreef op woensdag 27 april 2011 @ 16:06:
varchar(100) is 100 bytes (of misschien 200).

Heb je een bron Daarbij is het nogal afhankelijk van o.a. de charset (1 tot 3 bytes per teken; 4 byte UTF-8 wordt AFAIK nog niet ondersteund in MySQL) en overige overhead. M.a.w.: een varchar(100) kan wel 100 leestekens bevatten, maar hoeft niet 100 bytes op disk te beslaan.

[ Voor 12% gewijzigd door RobIII op 27-04-2011 16:09 ]

[dwilmer]

http://dev.mysql.com/doc/refman/5.0/en/char.html
"In contrast to CHAR, VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes"

Dus met varchar(200) heeft de entry 'foo' maar 4 bytes nodig. In een kolom met varchar(257) heeft dezelfde entry 5 bytes nodig, als ik het goed lees.
Maar dat is dus het grootste voordeel van VARchar over char: het is dus juist niet zo groot als wordt aangegeven.

UPDATE: server is al een half uur bezig met "optimize tables"..

[ Voor 5% gewijzigd door dwilmer op 27-04-2011 16:21 ]

[RobIII]

dwilmer schreef op woensdag 27 april 2011 @ 16:20:
Maar dat is dus het grootste voordeel van VARchar over char: het is dus juist niet zo groot als wordt aangegeven.

Dat is inderdaad de crux van varchar
En hence deze post.

dwilmer schreef op woensdag 27 april 2011 @ 16:20:
UPDATE: server is al een half uur bezig met "optimize tables"..

Wat dus mijn vermoeden sterkt dat er nogal wat lucht (en/of fragmentatie e.d.) in zit

[dwilmer]

Na een uur istie getimeout, en nu is het inderdaad minder. Alsnog 399MB, maar wel 31MB minder.

Als ik nu opnieuw ga optimaliseren, gaat-ie dan verder waar hij gebleven was? Oftewel: herkent-ie tabellen die al geoptimaliseerd zijn?

[RobIII]

dwilmer schreef op woensdag 27 april 2011 @ 16:57:
Als ik nu opnieuw ga optimaliseren, gaat-ie dan verder waar hij gebleven was?

Lijkt me dat wat 'ie al geoptimaliseerd heeft niet nog eens gaat optimaliseren nee.

[dwilmer]

"Select all" "Optimize table" Server, doe je best!

[KabouterSuper]

Overigens, een mysql-database exporteren en importeren via phpmyadmin is niet echt de handigste manier, onder andere omdat het via php gaat. Als je bestand groter dan een tiental MB's is, krijg je al problemen. Je kunt beter command-line een export en import doen. Dat is veel sneller en betrouwbaarder.

[MegaTronics]

Om alles in een keer te optimizen vanaf de command prompt:

mysqlcheck -u *user* -p*wachtwoord* --optimize --all-databases

[YopY]

dwilmer schreef op woensdag 27 april 2011 @ 15:54:
Dat laatste ga ik dan maar doen. Lang leve localhost!

UPDATE: tijdens het importeren gaat-ie dood... MySql server has gone away...

Importeren (en ook exporteren, overigens) kun je het beste via de mysqldump en mysqladmin tools doen, ipv via de webinterface van PMA - bij shared hosting komt het bij grotere databases eigenlijk altijd voor dat je over je CPU limiet heengaat en de MySQL databaseverbinding gewoon afgekapt wordt (en dan krijg je die melding). Idem bij imports. Je kunt, als je een export wilt, het beste even contact met je hoster opnemen danwel toevallig commandline toegang hebben, en als je lokaal wilt importeren zou ik de commandline tool gebruiken ipv PMA. PMA zit met bestandsgrootte, PHP execution time limieten, upload limieten, etc.

[ACM]

Wat een aannames over MySQL die niet kloppen...

Het is - bij ons - vrij gebruikelijk dat een dump kleiner is dan de data. MySQL probeert helemaal niet per se 'zo compact mogelijk' de boel op te slaan, er worden allerlei afwegingen gemaakt ter bevordering van performance en dataveiligheid.

Varchar's nemen in je dump tekstbytes+quotes aan ruimte in, maar in MySQL tekstbytes+watoverhead, waardoor dat weinig scheelt qua opslag. Integers zijn weliswaar 4 bytes, maar dat geldt voor alle, dus zowel voor 1 als voor 100000. Afhankelijk van je data is dat dus in het voordeel van de database of jouw dump.

Op die manier zit je met allerlei verschillen in datarepresentatie.

Belangrijker zijn waarschijnlijk de (automatisch toegevoegde) indexen, zoals de primary keys in InnoDB en zoals KabouterSuper correct aanhaalt zaken als reserveringen van ruimte in blocks bij InnoDB. Of MyISAM ook ruimte reserveert weet ik eigenlijk niet, in principe is dat een append-only omgeving, waardoor het minder relevant is. Maar bij MyISAM kan je dan dus weer wat overhead hebben van verwijderde en/of aangepaste records.