[PHP/SQL] 20.000.000 records op jaarbasis

[Bjk]

Heren,

Voor een klant schrijven wij een applicatie dat dagelijks zo'n 55.000 records wegschrijft naar de database. Deze records bestaan alleen uit getallen en datums. Dat betekent dat er na één jaar ongeveer 20 miljoen records in 1 table zal staan.

Data uit deze tabel (niet alle data maar een deel ervan, tussen bepaalde datums) zal aangesproken worden door gemiddeld 1500 bezoekers per dag. Nu mijn vraag, is het wel efficient om 20 miljoen records in 1 tabel te zetten? Wat voor alternatieven zijn er, en kan MySQL dit met gemak aan?

[Herko_ter_Horst]

Niet te zeggen zonder meer informatie. Wat betekenen die records? Welke indices heb je? Welke records gaan de gebruikers nodig hebben? (Een selectie uit) alle 20.000.000? Of heeft 90% van de gebruikers alleen maar de records van de laatste twee dagen/weken/maanden nodig?

[Janoz]

Of het werkbaar is is meer afhankelijk van de tabeldefinitie, de querys die je er op los laat en de hardware waarop je het draait dan van MySQL zelf. Wat ik me echter afvraag: heb je daadwerkelijk al die gegevens nodig? Kun je oudere gegevens niet aggregeren?

[ Voor 6% gewijzigd door Janoz op 12-01-2009 15:05 ]

[LuCarD]

Zover ik weet is de enige beperking aan rows de maximale grootte van een file.

Je zou kunnen denken aan partioning? ( http://dev.mysql.com/doc/refman/5.1/en/partitioning.html ) Maar op zic moet het geen problemen op leveren

[leuk_he]

Herko_ter_Horst schreef op maandag 12 januari 2009 @ 15:01:
Niet te zeggen zonder meer informatie. Wat betekenen die records?

20 miljoen x 100 bytes per record (zomaar wilde ongenuaceerde gok) =2GB. past in dat geval in geheugen! (64 bit machine). Maar inderndaad, hoe ga je ze gebruiken?

kijk eens naar pationeren inderdaad, maar ook daar is van belang heo data opgehaald wordt.

[pasz]

Gooi je tabel definitie hier eens neer.

Verder moet je goed bedenken waar je die 20.000.000 records voor wilt gebruiken. Als je dat weet kun je indices gaan plaatsen op die tabel. Zoals je het nu weergeeft, is het 1 tabel en bevinden er zich geen andere tabellen met afhankelijkheden/relaties in de database ?

[Bjk]

Bedankt voor alle reacties tot nu toe. De tabel ziet er ongeveer zo uit:

code:

id    |   datum         |   value1    | value2(tm 20)
---------------------------------------------------------------
1     |   12-1-2009    | 30           |     1000
2     |   12-1-2009    | 30           |     1000
3     |   12-1-2009    | 30           |     1000
4     |   12-1-2009    | 30           |     1000

De id is primary key en de datum is een index.
Standaard zullen de bezoekers de getallen van afgelopen maand te zien krijgen, maar zij kunnen de selectie zelf aanpassen. Selecties van maximaal een maand groot en maximaal 3 jaar terug.

[ Voor 0% gewijzigd door Bjk op 12-01-2009 16:16 . Reden: Voorbeelden zijn dummy namen ]

[Creepy]

Ja ik ben aan het miepen maaruh: value1 tot en met value20? Zijn dat dummy namen of zijn dit de echte kolomnamen? Ik hoop op dummy namen.........

En kan je ook nog aangeven hoe je indexen op deze tabel liggen?

[Voutloos]

Meten = weten. Zorg voor een aantal rijen dat representabel is voor een dag en insert die vervolgens voor de afgelopen 3 jaar.

edit:
Argh, je bedoelt t/m kolom 20. Dan is je tabel meteen fors groter, incl. stupide naamgeving...

[ Voor 30% gewijzigd door Voutloos op 12-01-2009 16:14 ]

[LuCarD]

Kan je deze data niet geaggregeerd opslaan?

1 record per dag of zoiets?

Creepy schreef op maandag 12 januari 2009 @ 16:12:
Ja ik en aan het miepen maaruh: value1 tot en met value20? Zijn dat dummy namen of zijn dit de echte kolomnamen? Ik hoop op dummy namen.........

VAST WEL..... Vast wel.... vast wel.... vast wel.... toch

[SchizoDuckie]

LuCarD schreef op maandag 12 januari 2009 @ 16:18:
VAST WEL..... Vast wel.... vast wel.... vast wel.... toch

* SchizoDuckie wedt voor een sneetje lekker oud brood van niet

[Kettrick]

SchizoDuckie schreef op maandag 12 januari 2009 @ 16:43:
[...]

* SchizoDuckie wedt voor een sneetje lekker oud brood van niet

Je hebt verloren, TS heeft in een edit al aangegeven dat het dummy namen zijn

MySQL zou dit volgens mij prima moeten trekken, zolang je maar de juiste indexes aanmaakt.
Is 20.000.000 de top of blijft het ook nog 10 jaar staan en hebben we het eigenlijk over 200.000.000 ?

De tekst in mijn sig is overigens wel van toepassing , maar omdat je post al over mysql gaat neem ik aan dat dat een weloverwogen beslissing is ?

[ Voor 14% gewijzigd door Kettrick op 12-01-2009 16:53 ]

[.oisyn]

Even ter indicatie, GoT slaat momenteel zo'n 31.3 miljoen reacties op. Zo'n post bestaat naast wat metagegevens als user, tijd, topic ook uit de originele post in RML vorm en de geconverteerde post in HTML vorm. Behoorlijk wat data dus. Toch loopt het niet bepaald traag.

[Kettrick]

.oisyn schreef op maandag 12 januari 2009 @ 16:52:
Even ter indicatie, GoT slaat momenteel zo'n 31.3 miljoen reacties op. Zo'n post bestaat naast wat metagegevens als user, tijd, topic ook uit de originele post in RML vorm en de geconverteerde post in HTML vorm. Behoorlijk wat data dus. Toch loopt het niet bepaald traag.

En dan heeft GoT ook nog *iets* meer dan 1500 bezoekers per dag

[ValHallASW]

Het hangt er natuurlijk ook heel erg vanaf wát er opgevraagd wordt. Als al die 20M rows worden opgevraagd dan heb je inderdaad een probleem. Op het moment dat het puur opslag is en alleen de laatste paar honderd rows worden gebruikt dan is het allemaal goed te cachen.

Ter vergelijking: de engelse wikipedia heeft nu ongeveer 200 miljoen rows in de revision-tabel. Voor queries wordt gebruik gemaakt van één masterserver en 8 slaves. Daarvoor geldt ook wel weer: alleen de laatste paar duizend rows worden veel gebruikt (en er is natuurlijk sprake van wat meer dan 1500 gebruikers. (en het gaat ook over iets in de orde van 60000 nieuwe rijen per dag!)
Op het moment dat de load te hoog wordt kan je dus gebruik gaan maken van slave servers. Je zult in ieder geval niet snel problemen krijgen met het aantal rijen in een tabel.

[ Voor 4% gewijzigd door ValHallASW op 12-01-2009 17:40 ]

[CubicQ]

Bjk schreef op maandag 12 januari 2009 @ 16:10:
Standaard zullen de bezoekers de getallen van afgelopen maand te zien krijgen, maar zij kunnen de selectie zelf aanpassen. Selecties van maximaal een maand groot en maximaal 3 jaar terug.

Met 55K per dag records krijgt een gebruiker standaard dus 1.65 miljoen records te zien? Ik kan me haast niet voorstellen dat dat gewenst is. Wanneer ik deze functionele requirements zou krijgen, is het 'back to the drawing board' en met de klant / functioneel ontwerper overleggen wat er nou echt functioneel de bedoeling is.

En verder: gaat het puur om reads (in de historische data), of ook om writes? In hoeverre valt het resultaat te cachen (indien noodzakelijk)? En tsja, zoals al gemeld: meten is weten....

[Gomez12]

CubicQ schreef op maandag 12 januari 2009 @ 20:19:
[...]

Met 55K per dag records krijgt een gebruiker standaard dus 1.65 miljoen records te zien? Ik kan me haast niet voorstellen dat dat gewenst is.

Tsja, ken wel een paar situaties waarbij dat de bedoeling is. Gewoon algemene statistiek en dan mogelijkheden geven om op de individuele dingen in te zoomen.
Alleen heb je dan wel een paar aggregate tabellen erbij zodat het gemiddelde van 1.65 miljoen records max 36 getallen ophalen is ( aggregate per maand is het grootste ).

Echt, met dit soort aantallen moet je gewoon hulptabellen gaan maken. 99% van je klanten is volgens mij niet geinteresseerd in 55K records per dag maar in een aantal kengetallen hierover.

20 miljoen records in 1 tabel zou in principe geen probleem moeten zijn als je de hardware er maar voor hebt om ze allemaal op te halen...
Wil je de hardware efficient houden dan ga je gewoon kijken wat je klant nou echt wil ( en ik gok dat dat geen 55K records per dag op zijn scherm is )