25. marts 2004 - 01:28Der er
21 kommentarer og 1 løsning
Database design spm
Hej.
Jeg har en database system med ca 20 tabeler. Bla f.eks. en 'kunde_tabel'
Nu har jeg brug for et system, hvor jeg for hver kunde i kunde_tabel, vil logge noget info om denne kunde.
Min plan er når man opretter en nye kunde med f.eks. id = 56, så samtidig oprette en nye tabel (fra koden forstås) med navnet 'kunde_56_log_tabel' . Og tilsvarende slette denne tabel når kunden slettes.
Derefter vil jeg så skrive logging info til 'kunde_56_log_tabel' efterhånden som systemet kører.
Jeg er i tvivl om myt design er helt forkert. Om jeg istedet blot skulle lave en enkel tabel som hed 'kunde_log_tabel' og så skrive logging info fra alle kunder til den med angivelse af kunde id.
Der bliver ca 200 kunder i mit system, og hver kunde får ca 1000 loging linjer per dag.
Hvis jeg vælger design 2, vil der altså blive skrevet til 'kunde_log_tabel' 200.000 gange per dag.
Hvilket design er det rigtige ? og er mit design 1 helt helt forkert.
Denne side indeholder artikler med forskellige perspektiver på Identity & Access Management i private og offentlige organisationer. Artiklerne behandler aktuelle IAM-emner og leveres af producenter, rådgivere og implementeringspartnere.
Jo, det tror jeg sku også. Men jeg er også ude efter at finde ud af om mit design 1 er helt helt forkert og misforstået. Eller blot almindeligt forkert.
Hvis du opretter tabeller ad hoc til de enkelte kunder får du et sikkerhedshul af dimensioner idet brugeren du forbinder med så skal have ret til at oprette tabeller.
Det betyder at brugeren også har ret til at droppe tabeller; Enhver der får adgang til din db kan så - i teorien - smadre din database (f.eks. via SQL Injection).
200.000 rækker pr dag er en stor tabel - men den er ikke umanerligt stor. Jeg har arbejdet med tabeller med op til 900 millioner rækker, SQL Server klarer det fint, omend det kræver seriøse mængder ram.
Til gengæld, med mindre du vil gøre en storage sælger glad, hvor mange dage vil du have liggende i din log? Uanset om du vælger design 1 eller 2 vil du få samme antal rækker i databasen og dermed samme mængde data. På et år taler vi om over 70 millioner rækker.
Mht antal dage i loggen - oprydning når du skal slette log data vil også ske nemmest fra en enkelt tabel.
arne_v> Umiddelbart vil jeg mene at 200 tabeller vil være langsommere end 1 tabel. Al SQL vil skulle være dynamisk og dermed vil alt blive rekompileret ved hvert kald inkl. at lave query planer etc. Man vil også få ekstra joins - og det koster også.
Mht at dele IO ud på diske så er det dybt lige gyldigt om du har en eller mange tabeller. Man kan blot understøtte tabellens filgruppe med mange separate filer - det vil faktisk give en bedre spredning af belastningen (end ved mange tabeller) i det SQL Server vil vælge at benytte hver fil lige meget.
Med ordenlige indeks (bl.a. på kundenummer) så vil der heller ikke være noget samtidighedsproblem / låseproblem med en enkelt tabel.
Dynamtisk SQL har betyding i og med det vil kræve væsentligt mere CPU kraft for den enkelte query - og kræve det for hver afvikling. Normalt vil en query blive cachet (og hvis man er heldig - parameteriseret) så query planer mv kan genbruges fra gang til gang - det sker ikke med dynamisk SQL.
Mht caching og joins er vi ganske enige.
Så alt i alt er det svært at argumentere for de mange tabeller.
Da det er log tabeller vi snakker om, så vil der nok sjældent være tale om læsninger, mest skrivninger. Indsættelserne sker i slutningen af tabellen, så SQL Server kan kunne nøjes med at cache sidste extent af tabellen (det er jo der der indsættes) - vælger man mange tabeller skal der caches et extent for hver tabel.
At lokke SQL Server til kun at cache sidste extent er ikke særlig svært, det kræver blot at man har et (clustered) indeks på en kolonne der altid vil være i stigende orden ifht indsættelserne. Typisk en identity kolonne eller datetime med default getdate() - aldrig en GUID eller lignende.
Hvis SQLServer som du antyder inserter records spredt ud, så vil det da gå ud over SELECT performance. Jeg ville have troet at den fyldte datapages op inden den skiftede device.
Men: - jeg tvivler på at gevindsten er stor ved simple INSERT - hvis der er forskel i disk IO så vil det normalt dominere i forhold til SQL parsning & analyse
Indeks opdateringerne vil også ske på sidste extent, så vidt har du ret, men antallet af opdateringer er ikke så ekstremt (okay, de kommer nok i grupper fremfor jævnt fordelt over hele døgnet, men..) - jeg ville være mere opsat på at undgå pagesplits etc.
Med hensyn til cahing så er det min hypotese, at SQLServer bedre vil kunne effektivt cache 1 last datapage + index pages for 1 tabel end 200 last datapages og index pages for 200 tabeller.
SQL Server læser og skriver naturligvis altid i hele extents - men med flere fysiske filer der understøtter en filgruppe vil den sprede de læste og skrevne extents ud over de forskellige filer - og dermed sprede belastningen. Det større antal fysiske filer betyder bl.a. at den kan have flere asynkrone i/o igang for den enkelte query.
Får man for mange fysiske filer sker der i øvrigt det, at ens filsystem bliver en flaskehals og performance ryger ned - man skal finde en balance.
Iøvrigt vil jeg ikke skrive hele tiden, men have en klasse som holder 'log-entry´s" i ram, og så f.eks. flusher til DB, hvergang der er 100 eller hvert kvarter eller noget (og før systemet lukker ned).
Tabelen vil blive slettet løbende, og istedet skrevet i nogen filer til langtidsopbevaring.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
