yoel caspersen blog bloghoved

JSON i MySQL - en god idé?

Til et projekt har jeg skullet finde en måde at logge trafik mellem en Linux-baseret PHP-klient og et REST API, som er hosted hos en tredjepart. Trafikken består af HTTP(S)-forespørgsler, hvor der indgår et mix af JSON-objekter både i request og response.

Objekterne kan være yderst forskellige afhængig af konteksten, og nogle gange indeholder et objekt et array af nestede objekter.

Indtil nu har jeg gemt trafikken i en flad tekstfil, men det er åbenlyst ikke en optimal måde at gøre det på.

Jeg har overvejet at smide forespørgslerne ind i en MySQL-tabel og gemme selve objekterne i et felt med datatypen JSON - men jeg kan ikke befri mig selv for tanken om, at det føles lidt ligesom at gemme billedfiler og andre binære data i et BLOB-felt i en database, der overhovedet ikke er egnet til formålet.

MySQL ser ud til at indeholde nogle helper-funktioner, der gør det nemmere at behandle JSON - men så vidt jeg kan gennemskue, er det ikke muligt at indeksere et JSON-felt i MySQL, og så bliver det pr. definition tungt at skulle genfinde data på et senere tidspunkt.

Jeg har også overvejet, om jeg skulle kategorisere de enkelte objekter, så jeg har en tabel for hver objekttype, hvor selve objektet gemmes i et JSON-felt, mens relevante og søgbare metadata gemmes i separate felter, der kan indekseres - men det virker umiddelbart også som en skidt måde at gøre det på, for så er jeg jo i bund og grund i gang med at opfinde min egen database engine.

Dertil kommer, at metadata i separate felter vil medføre et ekstra pladsforbrug og gøre backup- og restoreprocesserne væsentligt tungere.

Hvad gør læserne i lignende situationer?

Kommentarer (42)
sortSortér kommentarer
  • Ældste først
  • Nyeste først
  • Bedste først
#2 Thomas Kæregaard

Jeg har tidligere været udsat for XML puttet i felter i en Oracle-database. Det må frarådes. Oracle tilbyder godt nok værktøjer til at søge i XML, men det var vældigt kluntet, bestemt ikke hurtigt, og det fyldte ganske enormt (som XML nu gør).

Jeg tænker at din idé med at udtrække og gemme relevant metadata (jeg forestiler mig noget som klient-id, tidspunkt og beskedtype) i en database sammen med et filnavn, og så have JSON-telegrammerne liggende på disk, sandsynligvis i separate filer. De kan så bundtes og komprimeres efter behov/behag. Lidt afhængig af filsystem kan det være bøvlet med millioner af filer i samme mappe - i så fald kan man opdele pr. f.eks. tid.

  • 3
  • 0
#3 Yoel Caspersen Blogger

Har du overvejet at bruge PostgreSQL? Den har glimrende json support, herunder for indeksering

Når vi valgte MySQL oprindeligt, var det en kombination af vane (mange års erfaringer med MySQL) og en opfattelse af, at performance generelt var højere i MySQL.

Men jeg må da indrømme, at PostgreSQL i højere grad begynder at ligne et godt alternativ.

Hvor tungt er det at migrere fra MySQL til PostgreSQL - og er der noget, man bør være særligt opmærksom på?

Og ville det være muligt at mixe vilkårlige objekttyper samt søge på vilkårlige object properties i samme tabel?

  • 2
  • 0
#4 Yoel Caspersen Blogger

Jeg har tidligere været udsat for XML puttet i felter i en Oracle-database. Det må frarådes. Oracle tilbyder godt nok værktøjer til at søge i XML, men det var vældigt kluntet, bestemt ikke hurtigt, og det fyldte ganske enormt (som XML nu gør).

Jeg mangler ligeledes en god løsning til XML - vi har et SOAP API hos en underleverandør, der genererer enorme mængder af XML, der skal logges. Hidtil har vi gemt det i et text-felt i MySQL, men det er tungt og stort set umuligt at søge i.

Jeg tænker at din idé med at udtrække og gemme relevant metadata (jeg forestiler mig noget som klient-id, tidspunkt og beskedtype) i en database sammen med et filnavn, og så have JSON-telegrammerne liggende på disk, sandsynligvis i separate filer. De kan så bundtes og komprimeres efter behov/behag. Lidt afhængig af filsystem kan det være bøvlet med millioner af filer i samme mappe - i så fald kan man opdele pr. f.eks. tid.

Det er måske den bedste løsning, men der er nu engang noget rart i at have en backup-fil fra databasen, som indeholder alle de data, der skal bruges, og som sikrer en eller anden form for consistency - der skal ikke mange fejl til, før en mappe fuld af filer samt en tilhørende databasetabel kommer "out of sync".

  • 1
  • 0
#6 Jens Beltofte

Har i kigget på ELK-stack (Elasticsearch, Logstash og Kibana)?

Elasticsearch er jo oplagt til at opbevare, gemme og søge i jeres JSON dokumenter, dashboards m.m. kan relativt nemt laves i Kibana, og jeres XML-filer kan i transformere om til JSON med Logtash.

Hvis i i fremtiden har behov for at håndtere logs i andre formater, så kan de jo nemt transformeres om med Logstash eller FileBeat (eller et andet af Beat værktøjerne fra Elastic).

  • 9
  • 0
#7 Jonas Høgh

Hvor tungt er det at migrere fra MySQL til PostgreSQL - og er der noget, man bør være særligt opmærksom på?

Og ville det være muligt at mixe vilkårlige objekttyper samt søge på vilkårlige object properties i samme tabel?

Jeg har desværre ikke erfaring med migrering.

Du kan godt indeksere heterogene dokumenter, men det er mindre effektivt end at indeksere bestemte properties, hvis du ved strukturen altid er den samme.

  • 2
  • 0
#8 Jens Madsen

Der er mange gode cloud logging løsninger (google, azure, AWS etc.). Hvis det skal være på jorden (ikke i skyen), så ville jeg anbefale at bruge en search engine i stedet (det er det cloud log services gør). Har brugt ElasticSearch som virker godt til formålet. Det lyder til at dine data er schema-free" så en "schema-free" løsning ala ElasticSearch kunne passe som fod i hose.

  • 3
  • 0
#9 Philip Kaare Løventoft

RavenDB er også schema-free og kan varmt anbefales. Den bruger i øvrigt JSON internt, så der kan du sådan set bare dumpe det ned og så er du klar til at indeksere det. Ellers kan du med fordel overveje Logstash+Elasticsearch, det er også et super godt værktøj.

  • 2
  • 0
#12 Mark Klitgaard

MongoDB er en ganske fin non-relational DB model til at gemme records af den type du nævner. Som andre har været inde på kan man sagtens gøre det i postgres, som måske er mere oplagt hvis man ikke rigtig har været omkring non-relational db'er før, da MySQL og postgres er utrolig ens at bruge.

Hvis det er for at logge inbound og outbound traffik, så vil jeg til gengæld melde mig ind i team ELK-stack. Jeg har af flere omgange brugt Kibana og det er rigtig kraftfuld værktøj til at håndtere store mængder logs samt man rent faktisk kan søge i data og finde det man leder efter, som nogen gange kan blive lidt uoverskueligt ellers.

  • 1
  • 0
#13 Nicolai Heilbuth

Vi har generelt gode erfaringer med JSON i Postgres. Syntaksen kræver lige lidt tilvænning, men kan det man har brug for, og der er mulighed for indexering. Ift. at strukturere tingene lidt mere bruger vi JsonSchema til at validere så det hele ikke bliver en stor ukendt klump data. I vores setup har vi forskellige typer i én tabel som har et JSON schema (gemt som json i en kolonne). I en anden tabel har vi data som så refererer en type og har en json kolonne. Json kolonnen i data tabellen valideres så mod den struktur beskrevet i typens schema kolonne. Det har vist sig at være et yderst fleksibelt setup, men har selvfølgelig krævet en del udvikling ift. validering mm.

Hvis målet derimod er hurtigt at få noget ind i en søgbar/filtrerbar løsning ville jeg nok også kigge på ELK-stack'en

  • 5
  • 0
#16 Kristian Klausen

Til log aggregation vil jeg anbefale ELK-stakken, som flere nævner. Hvis du er modig kan du også kigge imod Grafana Loki. Det er nyt, men ser spændende ud.

Jeg skulle lige til at skrive det samme. ELK-stacken er efter min opfattelse forholdsvis tung, så vi bruger selv Loki+Grafana. Man skal dog lige være opmærksom på at Loki ikke indekserer alt, hvilket dog ikke nødvendigvis er et problem:

Compared to other log aggregation systems, Loki: * does not do full text indexing on logs. By storing compressed, unstructured logs and only indexing metadata, Loki is simpler to operate and cheaper to run.

  • 1
  • 0
#17 Yoel Caspersen Blogger

Det er svært at komme med meget gode råd når man ikke ved hvad behovet er. Hvor meget data er der? Og hvad skal det bruges til?

I dette konkrete projekt er der tale om logning af API-trafik - jeg vil anslå, at der er tale om ca. 25 MB JSON-data pr. dag, eller noget der ligner 9-10 GB pr. år.

Hvis logningen skal give mening, skal man også kunne søge i den - fx lede efter bestemte objekttyper med specifikke properties og tilhørende værdier.

  • 1
  • 0
#18 Palle Simonsen

Som andre allerede har sagt ELK stakken eller bare Mongo eller en anden JSON database, der kan bruge en fri træ-struktur eller et decideret log management værktøj som SumoLogic, splunk eller logz.io hvis det skal være næsten 'out-of-the-box' - mulighederne er legio. Ja sågår Firebase, hvis I vil rode med lidt NodeJS eller BigQuery hvis vi skal på den store klinge.

Det kommer helt an på anvendelsen. Er det drifts / sikkerheds overvågning, analyseformål eller ?

Men mindre I allerede har erfaring med RDBMS eller noget andet at bruge MySQL, MariaDB, Postgres etc. til ville jeg ikke anbefale en RDBMS.

  • 0
  • 0
#21 Bjørn Göttler

Jeg gik og bøvlede med at finde en snitflade der gav mening. Efter noget tid besluttede jeg mig for at prøve det af i MySQL, da det har været det de har været ude og slå på tromme over at man pludselig kunne, og det virkede ret godt ud af boksen. Jeg har ikke behov for at søge i de data jeg gemmer. Jeg skal blot kunne hente det hele, og tilføje enkelt objekter. Hvis du allerede er 1337 i MySQL, så vil jeg foreslå dig at prøve det af. Du risikerer kun at blive klogere

  • 2
  • 1
#24 Mark Klitgaard

Det, mener jeg, er en forkert påstand. PostgresQL er mere at sammenligne med Oracle, hvis vi ser bort fra, at Schema og Database er det samme i Oracle, hvilket ikke er tilfældet for PostgresQL.

Selvfølgelig vil der være forskelle, ellers ville der jo ikke være nogen grund til at de begge eksisterer, pointen var at hvis man går fra MySQL til postgres får man ikke et kultur chok af episke propotioner.

  • 2
  • 0
 
#27 Jesper Lund Stocholm Blogger

Vi har med gode erfaringer brugt Azure CosmosDB til at gemme JSON-dokumenter fra logning. Den har indbygget, automatisk indeksering af alle properties.

Husk dog at holde dine dokumenter små. CosmosDB bliver voldsomt dyr, hvis dine dokumenter er store.

  • 0
  • 0
#29 Nis Peder Bonde

Husk også en sikkerhedsvinkel: Hvis du ikke har kontrol over input, ikke kan validere alle data, og hvis der samtidig er eller på et tidspunkt kommer en sårbarhed i databasens logik omkring JSON håndtering, kan det gå helt galt. Så hvis du i virkeligheden ikke har brug for databasens JSON funktionalitet, eller kan undgå at bruge denne, ville jeg måske overveje at undgå dette under sådanne forhold, hvor du ikke har kontrol over input. Skal du indeksere noget, kan du måske i stedet ekstrahere de specifikke data og så gemme dem i almindelige databasefelter. Alt resten kan så gemmes i et BLOB felt. Det kunne øge robustheden.

  • 2
  • 0
#30 Baldur Norddahl

Har du overvejet om det dataset indeholder data der skal behandles efter GDPR regelsættet? Herunder hvor længe data må opbevares, retten til aktindsigt i data og retten til at få slettet data.

  • 2
  • 0
#32 Yoel Caspersen Blogger

Det lader til, at der er mange fortalere for en ELK-stak, så det vil nok være det første, jeg tager et kig på.

Forslagene om migrering til PostgreSQL er også interessante, men jeg vurderer umiddelbart at det er en større mundfuld, fordi det nok lægger op til en generel migrering fra MySQL til PostgreSQL.

I skal have tak for de mange gode forslag og pointer.

  • 6
  • 0
#33 Martin Storgaard Dieu

Det lader til, at der er mange fortalere for en ELK-stak, så det vil nok være det første, jeg tager et kig på.

Jeg har også gode erfaringer med ELK-stakken og MongoDB, men som du selv er indepå med PostgreSQL, så skal du huske på det overhead der er når man bringer ny og smart teknologi ind i sin stak. Det er endnu et komponent, som skal konfigureres rigtigt, holdes opdateret/sikkerhedspatchet og som skal læres af dem der bruger det (både dem der putter data ind og dem som trækker/tager data ud).

Har du målt performance på dine forskellige forslag på MySQL? At optimere uden data, kan betyde at du optimere på det forkerte, hvis du er uheldig. Jeg ville foreslå at du har en tabel med metadata og en tabel med JSON (som hvis du gemmer billedefiler i en database).

Uanset hvad du vælger, så vil jeg anbefale dig at konstruere omkring 10 GB data (1 års data) og forsøge at indsætte det i din løsning. Tjek hvilken løsning du kan få til at passe ind i dine quries (retten til indsigt, retten til at blive glemt, retten til at få rette urigtigt data osv) og om performance (hastighed, plads, backup/restore, omkostninger) er acceptablet.

  • 0
  • 0
#35 Morten Hartvig

Hvis dine data passer til MySQL/Mariadb så er det jo fint at konvertere det til en tabel (hvis det gør du kommer hurtigt videre).

Vil dog lige nævne Neo4J som en anden mulighed ift. Elasticsearch. Når vi først er gået over mod NoSQL, så kan vi lige så godt få en graph database på banen også. Den er endnu stærkere hvis det vigtigste i dine data er relationerne:)

Men igen. Har ikke set data, så aner ikke den perfekte løsning.

  • 0
  • 1
#36 Jacob Christian Munch-Andersen

I dette konkrete projekt er der tale om logning af API-trafik - jeg vil anslå, at der er tale om ca. 25 MB JSON-data pr. dag, eller noget der ligner 9-10 GB pr. år.

Hvis du kun har 10 GB data om året er der jo ikke brug for et indeks, med en god SSD kan du læse 3 GB i sekundet. Skip databasen, lav en tekstfil per dag, og søg så meget du vil. Det er også en hel del lettere at deduplikere backuppen på den måde.

  • 3
  • 0
#37 Bo AA

Som det er blevet foreslået et par gange, vil Elasticsearch være et rigtig fint valg, dog primært som en hosted løsning (selv at drifte Elasticsearch er ikke nemt). Dog har jeg selv meget erfaring med ES, så det er måske ikke ligeså oplagt hvis man ikke har og bare hurtigt skal have noget til at virke. Et nok nemmere alternativ kunne være Postgres, som har fin JSON understøttelse. Har ikke selv erfaring med MongoDB, men tænker det også lyder som et fint bud.

  • 0
  • 0
#38 Rune Hansen

Jeg ville nok bare bundle alle kald i en json fil og splitte den pr. år eller måned. Og lave et script til at søge i den.

Migrering til postgreSQL burde være lige til. Bare sikre at jeres php application benytter PDO og ikke MySQLi. Hvis den gør bør i overveje at ændre det.

  • 0
  • 0
#39 Morten Brørup

I dette konkrete projekt er der tale om logning af API-trafik - jeg vil anslå, at der er tale om ca. 25 MB JSON-data pr. dag, eller noget der ligner 9-10 GB pr. år.

Hvis logningen skal give mening, skal man også kunne søge i den - fx lede efter bestemte objekttyper med specifikke properties og tilhørende værdier.

Under antagelse af, at du kun sjældent foretager søgninger...

Det er meget små datamængder; så kan fem års data snildt ligge i RAM, når du skal søge i det. Du får ikke meget softwareudvikling for prisen på 64 GB RAM.

Jeg ville nok fortsætte med flade tekstfiler. Lad datoen indgå i filnavnet, eller gem hver transaktionslog i separate filer i et directory, hvor datoen indgår. Med datoen i fil- eller directory-navnet er det let at slette gamle data. Backup og øvrig drift er også ukompliceret med flade filer.

mvh

Morten Brørup, CTO, SmartShare Systems

  • 1
  • 0
#40 Troels Arvin

Lidt mere name dropping: Hvis du har en strøm af data in i PostgreSQL, hvor du gerne effektivt vil kunne rydde op igen, så bør TimescaleDB (som kan ses som en PostgreSQL add-on) nok overvejes. Jeg har ingen personlige erfaringer med den.

  • 0
  • 0
#41 Yoel Caspersen Blogger

Har du målt performance på dine forskellige forslag på MySQL? At optimere uden data, kan betyde at du optimere på det forkerte, hvis du er uheldig. Jeg ville foreslå at du har en tabel med metadata og en tabel med JSON (som hvis du gemmer billedefiler i en database).

Vi har i dag en change log, som gemmer delta på object updates i vores system - den er lavet med en tabel med et text-felt med JSON-data i. Den fungerer for så vidt fint, men den er med tiden blevet meget stor og udgør en barriere for backup og (hurtig) restore.

Man kan dog argumentere for, at det mere er et partitioneringsproblem end et decideret performance-problem - logs burde nok ligge i en separat database med lavere prioritet.

  • 0
  • 0
#42 Danny Asmussen

NoSQL databaser er klart at vælge hvis du skal persist JSON objekter. Elasticsearch ville være god hvis JSON keys er det samme, da ES bruger mapping - men hvis det kun er 9-10gb om året er ES måske overkill. MongoDB er en hel del lettere at sætte op.

  • 1
  • 0
#43 Martin Jensen

Jeg vil klart anbefale ELK.

Du kan potentielt opnå en kompleksitets-reduktion ved at bruge ingestion-pipelines frem for Logstash, hvis du skal tilrette/berige dine dokumenter.

Jeg vil i samme ombæring klart anbefale ikke at bruge MongoDB; den er (imo) markant mere 'besværlig' og følsom at være i stue med.

En ElasticSearch/Kibana kender vil kunne hjælpe med at sælge idéen til dig. Elasticos gratis-licens giver alt det du skal bruge til den opgave du beskriver; ellers er der 'Open Distro' som er community + 'det man køber' i en færdig gratis pakke (den har jeg dog ikke erfaringer med).

Jeg bruger ES, og synes det er super nemt at gå til, da du i den helt simple form bare kan poste JSON ind i den, og så er du i gang. Så er der så mulighed for at gå i dybden efter behov.

Vær fra start opmærksom på at definere hvad dine attribut-typer mappes til (det nemmeste i et opstarts-forsøg er at se hvad ES gætter, og tilpasse det hvor den fejler).

Men go go ELK herfra. :-)

  • 1
  • 0
Log ind eller Opret konto for at kommentere