[MySQL] Grootte van database berekenen...

[Bender]

Ik ben al een tijdje over aan het nadenken, maar kan geen echt effectieve manier bedenken voor het volgende probleem..

Ik heb een database met iets als zo:

code:

id  | pid  | title      | text
----------------------------------
1   | 3    | test 2     | hallo, flip
1   | 4    | test 8     | hallo, flap
1   | 4    | test 4     | hallo, flop
1   | 3    | test 3     | hallo, flup

(Uiteindelijk zullen het iets van 50 verschillende tabellen zijn met gemiddeld 20 cellen en sommige met 5000 regels oplopend.)

Nu wilde ik als eindresultaat weten hoeveel kilobyte alle regels zijn gegroupeerd per pid.
Misschien iets duidelijker gezegd, stel voor dat pid gebruikers zijn en dat iedere gebruiker zijn eigen gebruik wil weten.

"TABLE STATUS" kan helaas alleen de formaten van tabellen terug geven, maar ik wilde van de regels afzonderlijk weten.

Uiteraard kan ik alles regel voor regel uitlezen, dan moet ik dus ieder tabel apart gaan bekijken hoe de berekening is etc, en is ook niet bepaald efficient, zeker niet als er nog wel eens een cel kan bijkomen/wegvallen.

[sjroorda]

Voor zover ik weet kan dat niet in een native SQL-query. Blijven twee mogelijkheden over:
1. Fetch alle rijen, en bekijk het totaal aantal bytes (kost een hoop resources, en ik weet niet of dit ook echt de grootte is die MySQL voor deze rijen gereserveerd heeft)
2. Achterhaal het aantal rijen met pid=x en het totaal aantal rijen; a.h.v. de verhouding tussen deze twee getallen en de gehele tabelgrootte kan je een benadering doen (moeten de rijen wel alle ongeveer even groot zijn).

edit:
@2: show table status geeft zelfs de gemiddelde lengte per rij terug, scheelt weer een berekening

[ Voor 13% gewijzigd door sjroorda op 04-04-2005 13:49 ]

[Bender]

1. Dat is inderdaad de allerlaatste optie waar ik dus voor wil gaan..
2. Ook een optie waar ik over nagedacht heb, maar de rijen zijn niet allemaal even groot (de rijen onderling kunnen enorm verschillen).

[Bender]

Nadeel van row voor row is overigens ook dat ik veel met de hand moet gaan maken/instellen.
Veel cel types werken net iets anders kwa grote, bijvoorbeeld text en blob is de lengte + 4 bytes voor die lengte weer...

[Soultaker]

Je kunt gewoon in de database-directory kijken hoe groot de bestanden zijn die daar in staan, lijkt me? Elke database heeft een aparte directory, en elke table een aantal bestanden die met de tabelnaam beginnen.

Slecht gelezen, sorry.

[ Voor 8% gewijzigd door Soultaker op 04-04-2005 13:51 ]

[sjroorda]

Triloxigen schreef op maandag 04 april 2005 @ 13:28:
Nadeel van row voor row is overigens ook dat ik veel met de hand moet gaan maken/instellen.
Veel cel types werken net iets anders kwa grote, bijvoorbeeld text en blob is de lengte + 4 bytes voor die lengte weer...

Je wil dus de grootte op de byte precies hebben...

Soultaker schreef op maandag 04 april 2005 @ 13:46:
Je kunt gewoon in de database-directory kijken hoe groot de bestanden zijn die daar in staan, lijkt me? Elke database heeft een aparte directory, en elke table een aantal bestanden die met de tabelnaam beginnen.

Het gaat er dus om dat TS de grootte wil weten van een subset van de tabel. Anders werkt 'show table status' wel erg makkelijk

[ Voor 38% gewijzigd door sjroorda op 04-04-2005 13:47 ]

[Bender]

Soultaker schreef op maandag 04 april 2005 @ 13:46:
Je kunt gewoon in de database-directory kijken hoe groot de bestanden zijn die daar in staan, lijkt me? Elke database heeft een aparte directory, en elke table een aantal bestanden die met de tabelnaam beginnen.

Maar dan heb ik dus alleen de tabel..
Overigens zal de database dir. uiteraard niet bereikbaar zijn voor een HTTP account

sjroorda schreef op maandag 04 april 2005 @ 13:46:
[...]

Je wil dus de grootte op de byte precies hebben...

In ieder geval zo nauwkeurig mogelijk, een gemiddelde zal ver weg van nauwkeurig zijn

sjroorda schreef op maandag 04 april 2005 @ 13:46:
[...]

Het gaat er dus om dat TS de grootte wil weten van een subset van de tabel. Anders werkt 'show table status' wel erg makkelijk

Exact.

[Soultaker]

Nu ik het probleem wel goed gelezen heb, lijkt me oplossing 2 van sjroorda eigenlijk de enige zinnige aanpak. De grootte van de result set bekijken is net zo ingewikkeld en alleen betrouwbaar als je binary data overstuurt (de tekstrepresentatie ervan is natuurlijk veel groter). Oplossing 2 is wel ongeveer betrouwbaar omdat in een normale database voor elke rij een vaste hoeveelheid data wordt gereserveerd, behalve voor variabele velden als TEXT en BLOB's. (Als je die veel hebt, werkt het dus niet.)

De allocatiedetails hangen natuurlijk af van de gebruikte tabletypes.

[ Voor 8% gewijzigd door Soultaker op 04-04-2005 14:00 ]

[sjroorda]

Hoe realtime wil je die informatie hebben? Als het erom gaat dat je bijvoorbeeld gebruikers bij excessief gebruik een waarschuwing wil sturen, dan is eens per dag of zelfs week voldoende. In een dergelijk geval kan je best een wat 'duurder' script runnen. Deze zelfde redenering gaat op als je je gebruikers ongeveer wilt laten zien wat ze gebruiken: eens per dag vernieuwen zou dan genoeg kunnen zijn.

Als je echt verdergaand wil programmeren, kan je eventueel zelfs mijn beide oplossingen combineren tot een soort AI-achtig scriptje: eens per dag exact het aantal bytes berekenen, per user kijken welke factor hij gebruikt ten opzichte van het gemiddelde (bijvoorbeeld: user1 gebruikt chronisch 2x het gemiddelde, user2 en user3 slechts 0.4 keer), en a.h.v. deze gegevens een iets accuratere berekening uitvoeren met behulp van het gemiddelde.

Volg je 'm nog? Ik niet meer

[Verwijderd]

Of gewoon iets als de data die je wil berekenen in een nieuwe tabel gooien (select into (dacht ik?)). Dan heb je een fysieke tabel, waar je wel de grootte van kan opvragen.
Zogauw je de grootte weet, de tabel weer weggooien.

Dan hoef je tenminste geen data over te gooien naar je client, en hou je het lekker serverside.

Maar zowiezo blijft wat je wilt een behoorlijk dure query. (Ongeacht hoe je het gaat implementeren)

[Bender]

@ Soultaker
Er zijn inderdaad een aantal text velden, niet veel, maar wel wat.
is er niet toevallig 2 groot overzicht van de MySQL data berekening, of zal ik dit voor iedere cel apart moeten gaan opzoeken (ik weet de meeste wel, maar een aantal moet ik voor de zekerheid opzoeken).

@ Sjoorda
Het zal maar 1x per dag gedraaid worden (Om 03.00 NL tijd), het mag dus wel een beetje resources gebruiken maar niet de hele server lam zetten want er zijn ook US bezoekers
Helaas zullen gemiddelde niet reeel berekend kunnen worden er gebruiker, dit kan namelijk altijd anders zijn

@ jeanpaul
Alle te berekenen data naar een ander tabel zetten is geen oplossing, dit omdat gebruiker zelf ook weer data kan verwijderen. Er moet dan teveel aangepast en omgebouwd moeten worden dat dat niet te doen is.

[Verwijderd]

Ik bedoel... aanbegin van je berekening even in nieuwe tabel gooien, aan einde die tabel weer weggooien.

Voor en na je berekening is de database dan gewoon 100% hetzelfde, dus hoeft er verder geen code aangepast te worden.

[Soultaker]

Trilogixen: weet je zeker dat je zo nauwkeurig moet schatten? Kun je niet gewoon een gemiddelde nemen (ervan uitgaan dat alle text fields voor verschillende gebruikers gemiddeld even lang zijn) en dan je limiet wat hoger instellen?

[Bender]

Verwijderd schreef op maandag 04 april 2005 @ 14:19:
Ik bedoel... aanbegin van je berekening even in nieuwe tabel gooien, aan einde die tabel weer weggooien.

Voor en na je berekening is de database dan gewoon 100% hetzelfde, dus hoeft er verder geen code aangepast te worden.

Het eindresultaat van de berekening wordt zowiezo opgeslagen in een tabel en bewaard..

Maar ik begrijp niet helemaal wat je bedoeld, of wat je precies in een tabel wil zeten.
Ik dacht dat je bedoelde dat alle data die geinsert wordt ook nog eens in een aparte tabel wordt gezet ter berkening (waarbij het probleme hetzelfde blijft )...

Soultaker schreef op maandag 04 april 2005 @ 14:21:
Trilogixen: weet je zeker dat je zo nauwkeurig moet schatten? Kun je niet gewoon een gemiddelde nemen (ervan uitgaan dat alle text fields voor verschillende gebruikers gemiddeld even lang zijn) en dan je limiet wat hoger instellen?

Het gaat om textberichten, deze verschillen nou eenmaal gigantisch..
Als iemand heeft iets belangrijks te melden heeft kan dit makkelijk 50 regels zijn,
maar als iemand allene een link wil mededelen ben je met 2 regels klaar...
En dus is het erg verschillend

[ Voor 63% gewijzigd door Bender op 04-04-2005 14:25 ]

[sjroorda]

Gezien de precisie waarmee je het wil doen samen met het feit dat het script maar 1x per dag gerund zal worden, zou ik toch eens kijken naar ofwel mijn oplossing, ofwel die van jeanpaul (SELECT INTO). Die laatste lijkt me makkelijker en sneller te programmeren. Welke meer resources vreet weet ik niet, dat zou je eens moeten benchmarken.

Het zal in geen geval zo zijn dat je server echt lam ligt: daarvoor lijkt me een dergelijke reeks queries gewoon te weinig.

[Flapp]

je kan toch ook met myphpadmin kan je toch zien hoe groot een database is ?
of ligt dat aan mij... je kan in iedere geval van alle tabellen geloof ik apart de grote in kb zien

[DamadmOO]

Flapietoetoe schreef op maandag 04 april 2005 @ 14:31:
je kan toch ook met myphpadmin kan je toch zien hoe groot een database is ?
of ligt dat aan mij... je kan in iedere geval van alle tabellen geloof ik apart de grote in kb zien

maar de TS wil het van een deel van de tabel weten.

Verder heb ik ooit wel eens zoiets gemaakt en daarbij maakte ik gewoon gebruik van een temp tabel waar alles in werd gezet. dan berekenen. en dan de tabel weer dumpen. oftewel precies hetgeen wat jeanpaul zegt.

[Bender]

Ah, nu begrijp ik hoe jullie bedoelen met INSERT INTO..
Goed plan, zolang hij 150x (oplopend) per nacht niet erg vind...

(Kwartje viel wat laat )

[ Voor 12% gewijzigd door Bender op 04-04-2005 15:26 ]

[bRight]

never mind..

[ Voor 94% gewijzigd door bRight op 04-04-2005 20:13 ]

[Gomez12]

Of ga eens kijken naar een ander dbms met triggers, dan kan je gewoon een extra tabel aanmaken met grootte per pid en dan gewoon bij een insert / update / delete een trigger aanroepen die de strlength van een cel berekent en dan in extra tabel grootte + stringlength zet.

Nou ik er over nadenk kan dit ook best in je applicatie afgewerkt worden. En dan gewoon een handmatige methode voor herberekening erin gooien zodat je het op moment van factureren precies zelf kan narekenen.

Voordeel hiervan is ook dat je er uitzonderingen in kan verwerken ( &nbsp telt deze als 1 byte of als 5 bytes )

[ Voor 1% gewijzigd door Gomez12 op 04-04-2005 22:09 . Reden: ff wat bold gemaakt, omdat ik niet alleen ander dbms voorstel ]

[Bender]

Andere database is helaas ook geen optie, zit vast aan MySQL.
Anders had ik wel MsSQL gekozen

[thomaske]

Denk ik nu te simpel of zie ik iets over het hoofd?

code:

SELECT
   pid
,  SUM(LENGTH(title)+LENGTH(text))
FROM table
GROUP BY pid

[Bender]

Nee, je ziet niks over het hoofd...
Maar gezien het uit 50 tabellen bestaat met gemiddeld 20 cellen en allemaal verschillende type's kost dat erg veel tijd om dat te bouwen..
(Maar hou hte zeker nog wel in overweging)...

Overigens is het dan

code:

SELECT
   pid
,  SUM(LENGTH(title)+4+LENGTH(text)+4)
FROM table
GROUP BY pid

MySQL gebruikt 4 byte om op te slaan hoegroot de lengte is
Maar het bestaat dus uit veel meer cellen, en weet niet van elk type hoeveel byte MySQL er uiyeindelijk voor gebruikt...

[Verwijderd]

Waarom maak je niet gewoon een tabel waar permanent de groottes per pid instaan? En dan iedere keer als een client iets verstuurd stuurt de client ook de grootte mee van wat hij verstuurt.

Grootte uitlezen is dan niet meer dan

code:

SELECT grootte
FROM grootte_tabel
WHERE pid = 'iets'

Voordeel: op de server hoeft helemaal niet gerekend te worden. Behalve dan berekenen van de grootte van wat er al in de database zit.

[Bender]

En wat als een gebruiker een update doet?
Of iets delete?

En dat het om een stuk of 300 queries gaat (als het trouwens niet veel meer zijn) verspreid over 30 bestanden...

[thomaske]

Triloxigen schreef op dinsdag 05 april 2005 @ 10:14:
Nee, je ziet niks over het hoofd...
Maar gezien het uit 50 tabellen bestaat met gemiddeld 20 cellen en allemaal verschillende type's kost dat erg veel tijd om dat te bouwen..
(Maar hou hte zeker nog wel in overweging)...
[..]

Er is toch makkelijk iets generieks te bouwen wat automatisch de tabellen scanned:
mysql_list_tables()
mysql_list_fields()

[Bender]

thomaske schreef op dinsdag 05 april 2005 @ 13:34:
[...]


Er is toch makkelijk iets generieks te bouwen wat automatisch de tabellen scanned:
mysql_list_tables()
mysql_list_fields()

Maar dan blijft het probleem nog hoe alle typen te berekenen..
het is namelijk niet zomaar length()....

[sjroorda]

Triloxigen schreef op dinsdag 05 april 2005 @ 10:14:
MySQL gebruikt 4 byte om op te slaan hoegroot de lengte is
Maar het bestaat dus uit veel meer cellen, en weet niet van elk type hoeveel byte MySQL er uiyeindelijk voor gebruikt...

Wellicht heb je iets aan http://dev.mysql.com/doc/mysql/en/storage-requirements.html?

[Bender]

sjroorda schreef op woensdag 06 april 2005 @ 08:34:
[...]

Wellicht heb je iets aan http://dev.mysql.com/doc/mysql/en/storage-requirements.html?

Dat zocht ik, maar kon niks vinden wat erop leek.
Bedankt