MySQL - på vei mot PostgreSQL, Oracle? (del 2)

[int20h]

Verdens mest utbredte open-source database, MySQL, står foran en versjonsoppgradering som vil føre til at databasen nærmer seg konkurrenter som PostgreSQL, Oracle og MS SQL. Vil MySQL noensinne bli kvitt "lettvektsstempelet"?

Les mer

[blackbrrd]

Fant en relativt interessant artikkel om mysql

http://www.anandtech.com/IT/showdoc.aspx?i=2447&p=7

 

I forhold til DB2 8.2 skalerer MySql relativt dårlig med flere cpu'r. Å bruke innoDB med mysql for å unngå table locking førte til et 40% ytelsestap... [skjønner ikke at jeg har klart å utelate det, men ytelsen til mysql var i utgangspunktet 200% høyere enn DB2, med transaksjonsstøtte sank ledelsen til ca 80%. Med noe sånt som 4 cores var DB2 ca like rask som mySql med innoDB. Det er verdt å merke seg at testen var lagt opp for å teste cpu'r, ikke databaser.]

 

Et spørsmål litt på siden... Hvordan skriver man om f.eks følgende sql fra å bruke inner selects til å kun bruke joins?

 

SELECT orderid, orderstatussnap_statusid FROM torder

INNER JOIN torderstatussnap ON torder.orderid = torderstatussnap.orderstatussnap_orderid

AND torderstatussnap.orderstatussnapid IN

(SELECT orderstatussnapid

FROM torderstatussnap

WHERE orderstatussnap_orderid = torder.orderid

ORDER BY orderstatussnapdate DESC, orderstatussnapid DESC LIMIT 1)

LIMIT 10

 

spørringen skal hente ut 10 ordre og hvilken ordrestatus de hadde

 

Det hadde vært litt interessant å se hvordan det skal gjøres...

Endret 4. august 2005 av blackbrrd

[godal]

Synes denne artikkelen holder svært lav kvalitet. Emnet er selvfølgelig interressant, og mye av det som står der er korrekt. Men det er jo nesten totalt blottet for informasjon, og ihvertfall mangler den noe som ikke alle som har tatt eller vurderer å ta denne avgjørelsen ikke allerede vet. Man får en sterk fornemmelse av at skribenten ikke har brukt andre løsninger enn MySQL og generelt sett vet lite om relasjonsdatabaser og historien rundt disse tingene.

[ymerguer]

Hei,

 

vi setter selvfølgelig pris på saklig tilbakemelding fra lesere.

 

Jeg må si jeg følte ditt innlegg var litt på vei over mot "usaklig"-kategorien, og ber deg derfor om å begrunne kommentaren din grundigere.

 

Jeg har forståelse for at du har et annet synspunkt enn andre, men det er lite konstruktivt med et innlegg av den typen du her kom med.

 

Jeg ser fram til et konstruktivt, men kritisk innlegg fra din side i nærmeste framtid, der du går mer i dybden på hva som var mangelfullt, osv.

[tigerdyr]

Det minner meg iøvrigt om at altid jeg hører folk klage på 'databasen er treg' (uanset hvilken database der er snakk om), så er det tankeløst design, dårlige valg av nøkler og håpløse spørringer som er årsaken. De forskjellige databaser fungerer faktisk forskjelligt og den bedste spørringen og hvordan denne kan optimeres varierer sterkt fra database til database og krever god viten om hva som skjer inne i den enkelte databasen - noe en skikkelig databaseadministrator naturligvis bør vite.

 

Når det gjelder artikkelen, så har jeg ikke indtryk av at forfatteren bare har brukt MySQL (eller har bukt den noe særlig i det hele tatt ;-) ) som en annen kommenterede. Vi bruker selv MSSQL (Visma-faenskap som trenger det), Oracle (for billingsystem) og MySQL for alt annet. Har tabeller i MySQL'en med 50+ millioner records (call-data records, jobber i et telekom firma) og totalt rundt 130 gig data - er storfornøyd for ytelsen og replikeringsmuligheterne i MySQL, men selvfølgelig krever det en del kompetanse for at det ikke skal bli tregt likesom du trenger en med mye Oracle-kendskap for at en Oracle database ikke skal bli dødstreg. Til sammenligning følger der med rundt 8000 siders dokumentation ("basic administration") til Oracle... ikke merkeligt at MySQL er et populært alternativ om du kan leve med de relativt få shortcomings den har.

[blackbrrd]

Hei,

 

vi setter selvfølgelig pris på saklig tilbakemelding fra lesere.

 

Jeg må si jeg følte ditt innlegg var litt på vei over mot "usaklig"-kategorien, og ber deg derfor om å begrunne kommentaren din grundigere.

 

Jeg har forståelse for at du har et annet synspunkt enn andre, men det er lite konstruktivt med et innlegg av den typen du her kom med.

 

Jeg ser fram til et konstruktivt, men kritisk innlegg fra din side i nærmeste framtid, der du går mer i dybden på hva som var mangelfullt, osv.

Man kan vel egentlig si at artikkelen er subjektiv uten noe særlig objektiv informasjon.

 

Ta f.eks dette avsnittet:

Ytelse

 

MySQL har rykte på seg for å være rask. Dette stemmer i de fleste tilfeller, men også her har man begrensninger. Så lenge man ikke skal gjøre kompliserte SELECT, er det faktisk lite som slår MySQL rent ytelsesmessig - ikke engang Oracle.

 

Når spørringene begynner å inkludere haugevis av SUBQUERIES og eventuelt JOINS er sagaen en helt annen.

 

Enkelhet gir høy ytelse - dessverre er ikke skalerbarheten lineær med stigende kompleksitet i SQL-spørringer.

 

MyISAM-tabellen har svært god ytelse på SELECT, men har den begrensningen at hele tabellen må låses dersom man skal kjøre en UPDATE. Rent skaleringsmessig kan det dermed heller være lønnsomt å velge InnoDB, som kun låser den raden som skal oppdateres.

 

InnoDB er noe tregere dersom man ser på én enkelt spørring, men har sine fordeler der man har mye trafikk som skal oppdatere databasen.

 

Jeg er fullt klar over at å teste denne informasjonen er en relativt stor oppgave, men jeg tror det er dette godal prøve å få sagt.

[godal]

Det står ingenting om plattform valg f.eks. støtter jo postgres og mysql "alt" mens ms kun går på windows og oracle støtter windows og til en viss grad linux.

 

I tillegg så er det kun løst snakk om funksjoner, lite snakk om hvor godt og hvor modne disse er. F.eks. postgres har jo hatt mange av disse funksjonene i årevis.

 

Hvor er avsnittet om query-optimizer for eksempel? Hva med bugs?

 

Når det gjelder support er det ingen sammenligning med de andre, alle databasene har proff support.

 

Dessuten finnes det jo en del andre aktører, Ingres, DB2 osv.

 

Installasjon, var en ting jeg lurte på som det ikke sto noe om. Må man f.eks. installere innoDB separat?

 

En annen ting er jo at databasene har forskjellige ting som mål.

 

Som postgres bruker må jeg si at jeg tviler sterkt på at skribenten noen sinne har vært borti dette, på tross av at det står i tittelen.

 

Man skal imidlertid også være klar over at det finnes en god del virksomhetskritiske systemer som faktisk kjører på MySQL. Databasen har rykte på seg for å være svært stabil, noe som har kanskje har sammenheng med enkelt design og effektiv virkemåte.

 

Istedenfor generell synsing og svada, bør det stå konkrete ting som ACID, transaksjoner, og WAL (write ahead logging).

 

Noen generelt gode bruksområder for MySQL:

 

    * Publiseringsløsninger

    * Forum

    * Gjestebøker

    * Bannersystemer

    * Blog

    * Portalfunksjoner, polls, tilbakemeldinger, osv.

    * Innloggingssystemer, kundeweb

    * FAQ

 

Hva er dette? her finnes det ingen argumenter bare løse påstander. Noe kan jeg være enig, men flere ting (Publiseringsløsninger, bannersystemer og innloggingssystemer, kundeweb) mener jeg overhodet ikke egner seg på MySQL på grunn av grunnleggende mangler innenfor integritet.

 

Dersom man skal sette opp et "high-availability" databasesystem basert på MySQL kan man velge om man vil gjøre dette på hardwarelaget, applikasjonsnivå eller en blanding av begge deler.

 

Feil, feil, feil. Hvis man skal ha high-availability, må man ha dette på applikasjonsnivå, og eventuelt hardware i tillegg.

[roac]

Det står ingenting om plattform valg f.eks. støtter jo postgres og mysql "alt" mens ms kun går på windows og oracle støtter windows og til en viss grad linux.

Og man må ikke glemme Oracle på Solaris.

[godal]

Det står  ingenting om plattform valg f.eks. støtter jo postgres og mysql "alt" mens ms kun går på windows og oracle støtter windows og til en viss grad linux.

Og man må ikke glemme Oracle på Solaris.

selvfølgelig, solaris, aix, bsd, osv. masse stoff.

[Martin Lie]

Hvor kommer MaxDB (http://www.mysql.com/products/maxdb/) inn i bildet her? Er SAP med på utviklingen av denne, eller er det bare det at MySQL AB først implementerer avansert funksjonalitet i MaxDB, for så å "lekke" det over til GPL-databasen sin når tiden er rett, politiske og markedsmessige hensyn tatt i betraktning (ikke ulikt det som skjer i med ulike produktlinjer i maskinvare-bransjen)?

 

Speaking of lisenser... Savner en sammenligning av lisenser for de ulike databasene. MySQL har en litt snodig tolkning av GPL, såvidt jeg har forstått (at bruk av MySQL til kommersielt bruk ikke er kompatibelt med GPL, men krever en egen lisens?).

[roac]

Hvor kommer MaxDB (http://www.mysql.com/products/maxdb/) inn i bildet her? Er SAP med på utviklingen av denne, eller er det bare det at MySQL AB først implementerer avansert funksjonalitet i MaxDB, for så å "lekke" det over til GPL-databasen sin når tiden er rett, politiske og markedsmessige hensyn tatt i betraktning (ikke ulikt det som skjer i med ulike produktlinjer i maskinvare-bransjen)?

 

Speaking of lisenser... Savner en sammenligning av lisenser for de ulike databasene. MySQL har en litt snodig tolkning av GPL, såvidt jeg har forstått (at bruk av MySQL til kommersielt bruk ikke er kompatibelt med GPL, men krever en egen lisens?).

Jeg har noe begrenset oversikt over lisenser, det er ofte en litt ullen materie. De to produktene jeg i det hele tatt har sett på lisensen på, er MSSQL og Oracle. CALs er et kjent og kjedelig fenomen, som gjør at man i større miljøer (i likhet med webapplikasjoner) har lisenser pr prosessor. Hadde det enda bare vært så enkelt. Microsoft (og visstnok IBM) lisensierer pr fysisk prosessor, mens Oracle har lettet litt på lisesnkostnadene, og krever en lisens for den første kjernen, mens etterfølgende kjerner teller 3/4 av. Hadde det enda vært så enkelt. Microsoft SQL Server krever lisens kun for de nodene som er aktive, eller rettere sagt noder hvor SQL Server tjenesten går og har databaser som brukere kan gjøre spørringer mot. Med andre ord, inaktiv node i et cluster, speilserver og vitneserver er i utgangspunktet ikke lisenspliktige. Oracle sin politikk er "noe" annerledes, da de er krever lisens for hver PC hvor Oracle er installert, uavhengig av om tjenesten går eller ikke.

 

Til slutt skal det nevnes at dersom CPU Affinnity Mask benyttes, så krever Microsoft kun lisens for de fysiske prosessorene som faktisk benyttes. Hvordan Oracle tolker dette er jeg ikke sikker på.

[Martin Lie]

roac: Takk for oversikten for MSSQL og Oracle. Har MS en måte å verifisere hvilken CPU affinity man bruker, da?

 

PostgreSQL er så vidt jeg vet BSD-lisensert, altså fritt til hva du måtte ønske. MySQL sin GPL-tolkning er gjengitt her: http://www.mysql.com/company/legal/licensing/

 

Det virker som de mener at man må GPL-lisensere sitt eget program bare fordi det lagrer data i en MySQL-database. Noe jeg da mener er feil. Har også sett tolkninger der tilgjengeliggjøring av en dynamisk nettside som bruker MySQL til lagring regnes som "distribusjon", som altså kommer inn under GPL. Lurer på om de bevisst prøver å gjøre det ekstra ullent for å få flere til å kjøpe kommersiell lisens "for å være på den sikre siden".

 

 

blackbrrd: Er ikke helt sikker på om jeg skjønner oppgaven din, men dette er min forståelse av problemet: For hver ordre, finn ordrens status av nyeste dato (og hvis flere statusmeldinger har samme dato, velg den som er lagt sist inn i dagabasen - altså den med høyest id).

 

Puh... Den typen spørringer er nettopp grunnen til at subselects er populært. Det er mulig å skrive spørringer som er nesten like, men det er litt kompli. Men first things first: Ettersom du ikke bruker noe annet enn "orderid" fra torder-tabellen, og orderstatussnap_orderid har samme verdi (den er, så vidt jeg kan se, en fremmednøkkel som peker mot toder.orderid, right?), så trenger du ikke bruke en join i spørringen din i det hele tatt.

 

Dette skulle være det samme som din spørring (trur eg):

SELECT orderstatussnap_orderid, orderstatussnap_statusid FROM torderstatussnap t1

WHERE orderstatussnapid IN

(SELECT orderstatussnapid

FROM torderstatussnap t2

WHERE t2.orderstatussnap_orderid = t1.orderstatussnap_orderid

ORDER BY orderstatussnapdate DESC, orderstatussnapid DESC LIMIT 1)

LIMIT 10

 

(MySQL 4.1, som støtter subselects, støtter av en eller annen grunn ikke "IN (SELECT ... LIMIT)"-syntax, så man må bruke "= (SELECT ... LIMIT)" isteden, men det har egentlig ingenting med denne saken å gjøre. )

 

Så... Tilbake til problemet ditt. Hvis man fjerner subselecten, så får man for mange svar - ett for hver statusmelding, i motsetning til ett for hver ordre. I slike tilfeller er det naturlig å bruke GROUP BY for å begrense resultatsettet. Om du grupperer på orderstatussnap_orderid, vil du få riktig antall svar (ett pr. ordre, som du igjen kan begrense til 10 med LIMIT):

 

SELECT orderstatussnap_orderid, orderstatussnap_statusid FROM torderstatussnap t1

GROUP BY orderstatussnap_orderid

 

...nå blir orderstatussnap_orderid riktig, men hvilken orderstatussnap_statusid som velges er tilfeldig/udefinert. Det vi ønsker er at orderstatussnap_statusid skal vise id-en til statusmeldingen med nyeste dato. Så det vi gjør er et aldri så lite triks: Joiner torderstatussnap-tabellen med seg selv (på ordreid-en), grupperer på tabellens primærnøkkel, og bruker HAVING for å luke vekk de uønskede radene. Slik:

 

SELECT t1.*

FROM torderstatussnap t1 INNER JOIN torderstatussnap t2

ON t1.orderstatussnap_orderid = t2.orderstatussnap_orderid

GROUP BY t1.orderstatussnapid

HAVING t1.orderstatussnapdate = MAX(t2.orderstatussnapdate)

 

Problemet her er at vi her, i motsetning til subselecten, ikke kan slenge på en ORDER BY for å sortere etter flere kriterier, og LIMIT 1 for å kun få ett treff. Denne spørringen vil returnere alle statusmeldinger som matcher den nyeste datoen. Selvsagt er det mulig å slenge på en ORDER BY for å sortere endelig output synkende etter orderstatussnapid, og programmatisk behandle dette i etterkant slik at du kun viser første treff for en enkelt ordre. Men da kan man egentlig ikke være helt sikker på hvor mange reelle ordre som returneres, og det blir meningsløst å begrense hele spørringen til 10 "ordre" med å slenge på LIMIT 10. Siden du har to kriterier i sorteringen din (både dato og id), så klarer jeg altså ikke helt å etterape subselect-spørringen. Hadde du kun hatt ett sorteringskrav, så hadde spørringen som jeg viste over vært grei nok.

 

Forresten... Det er mulig det finnes en løsning vha. GROUP_CONCAT(... ORDER BY ...), men jeg er usikker på om dette er en MySQL-spesifikk sak. Men jeg tror jeg skal begrense rantingen min for denne gang. Hvis du vil høre meg ralle videre om dette får du heller rope ut.

 

Her er dessuten MySQLs egen "løsning":

http://dev.mysql.com/doc/mysql/en/example-...-group-row.html

[langsjoen]

PostgreSQL støtter replikering/clustring.

Det er flere former for replikering/clustring som er laget for postgresql.

http://slony.info er mest trolig den mest annerkjente av open source løsningene. Slony-I er laget av en av postgresql sine hovedutviklere.

 

"Slony-I is a "master to multiple slaves" replication system with cascading and failover.

 

The big picture for the development of Slony-I is a master-slave system that includes all features and capabilities needed to replicate large databases to a reasonably limited number of slave systems. "

 

Det jobbes med flere varianter for multi master, men jeg tror ikke noen opensource variant av dette er klar for produksjon enda. Slony-II er en av disse. Slony-II utvikles av utvikleren bak Slony-I.

 

Databasesystemet for administrasjon av .ORG og .INFO domener kjører PostgreSQL og SLONY-I.

 

-martin

[roac]

Takk for oversikten for MSSQL og Oracle. Har MS en måte å verifisere hvilken CPU affinity man bruker, da?

Det beste du har (som jeg har funnet i hvert fall) på SQL Server 2000 er:

 

Enterprise Manager | Høyreklikk SQL Server Instans | Properties | Processors

[ymerguer]

Man kan vel egentlig si at artikkelen er subjektiv uten noe særlig objektiv informasjon.

 

Jeg er fullt klar over at å teste denne informasjonen er en relativt stor oppgave, men jeg tror det er dette godal prøve å få sagt.

Hei,

 

jeg vil understreke at det her ikke var snakk om å kritisere Godal for det han forsøker å få fram her. Det er et synspunkt han har, og det er selvfølgelig helt i orden for oss.

 

Det var mer en oppfordring til å komme med argumenter på hvorfor han mente det han gjorde. Det har han nå gjort, og da hadde mitt svar til han den ønskede effekt.

 

Og så vil jeg si at det er positivt at dere her engasjerer dere i å diskutere en artikkel og et emne på en dyp og bra måte.

 

Jeg skal videreformidle tråden til Jan Aril Sigvartsen, som for øyeblikket er på ferie. Han har sikkert lyst til å komme med et svar når han kommer tilbake.

 

Tilslutt har jeg lyst til å si at dette med testing og "objektiv" informasjon er en utfordring. Det er vanskelig å få allmenngyldig og sammenlignbart materiale ut av dette, men jeg skal oppfordre Jan Aril Sigvartsen til å svare på dette så godt han kan (hva han baserer seg på, helt konkret).

[blackbrrd]

Pureblade:

 

Takker for svar, det ser ut som noe vi en gang brukte med et view - det gikk uendelig tregt med ca 1 mill rader i torderstatussnap Testet ditt forslag til løsning og den så ut til å fungere omtrent på samme måte. GROUP_CONCAT finnes ikke i postgres såvidt jeg kan se og er nok en mysql-utvidelse.

 

Å bruke Having for å vesentlig begrense et søk er forresten ingen god ide. Having fungerer ikke på samme måte som where clausen. Spørringen kjøres først som vanlig, deretter blir having-clausen utført. I dette tilfellet så henter man da ut 1 million rader for deretter å redusere det til 10 med having clausen så vidt jeg kan forstå.

 

Det smarteste hadde vært å lage en trigger i torderstatussnap som la inn siste status i torder tabellen. Har ikke kommet så langt, en kjapp db server får heller treske noen millisekunder lenger.

 

Grunnen til at torder er med i min spørring er fordi vi gjerne er interessert i å få ut mer info en bare orderid, men det var ikke så veldig relevant for mitt spørsmål.

 

Postgres sin native windows støtte er forresten av nyere dato.

 

Vi kjører Postgres på Suse Linux 9.1 el 9.2 64bit versjonen på en Dual Xeon 2,8ghz med 2gb ram og 3x15000rpm disker i raid 5. Kjørte det en stund på en maskin med ide-disker noe som fungerte utmerket inntil vi begynnte å gå tom for ram.

 

Ytelsesmessig så har vi sett en ca 10x økning etter at vi byttet til 2.6kernel linux, postgres 8.0 og java 1.4.2 i forhold til 2.4kernel linux, postgres 7.2 (eller der omkring) og java 1.4.0. Hardwaren har også blitt byttet til en som er ca 2x så hurtig.

 

Vi har portet en applikasjon fra å bruke mssql server til postgres 7.2 for ca 3 år siden. Ytelsesmessig ganske likt, men det krever endel omskriving av sql. Synes mssql server optimizeren virket mindre quirky enn postgres sin, bl.a. slet postgres sin veldig med unødvendige "Distinct"'r i spørringene. Det kan godt være at 8.0 har fikset dette.

 

MySql var totalt uaktuelt å bruke når vi skulle velge en opensource database ettersom den på det tidspunktet ikke støttet transaksjoner.

 

I tillegg har Postgres en åpnere lisens enn f.eks mysql.

Endret 4. august 2005 av blackbrrd

[blackbrrd]

Tilslutt har jeg lyst til å si at dette med testing og "objektiv" informasjon er en utfordring. Det er vanskelig å få allmenngyldig og sammenlignbart materiale ut av dette, men jeg skal oppfordre Jan Aril Sigvartsen til å svare på dette så godt han kan (hva han baserer seg på, helt konkret).

Typisk allmenngyldig materiale er f.eks en WAL fra sitt eget system. Dere kunne f.eks brukt hw.no sitt forum som eksempel. Da vil dere gi ut objektiv informasjon om hvilken database som egner seg til forum bruk.

 

På den andre siden så brukes det vel der mysql uten wal, ettersom det ikke er så farlig om litt data blir borte og dere sliter med ytelsen.

Endret 4. august 2005 av blackbrrd

[roac]

Vi kjører Postgres på Suse Linux 9.1 el 9.2 64bit versjonen på en Dual Xeon 2,8ghz med 2gb ram og 3x15000rpm disker i raid 5. Kjørte det en stund på en maskin med ide-disker noe som fungerte utmerket inntil vi begynnte å gå tom for ram.

Jeg håper det er behov for datamengde og sikkerhet som gjorde at RAID 5 ble valgt her. Normalt sett vil denne løsningen ha dårligere skriveytelse enn et speil eller en enkelt disk, mens leseytelsen omtrent den samme som for speilet. Caching kan selvfølgelig bedre noe på dette, en en disk ekstra og RAID 10 ville sannsynligvis føre til en drastisk økning i diskytelsen.

[Ueland]

blackbrrd: Ved litt testing har vi fint klart å få MySQL til å servere rundt 30 000 spørringer i sekundet, da både på single server, og via clustret MySQL nett (med litt forskjellg testing).

 

Man kan diskutere mye frem og tilbake, men 30k spørringer i sekundet er absolutt nok for alle våre tjenester i ganke god tid fremover.

 

Med tanke på at man og fint kan lage CMS systemer til å ligge på rundt 1->3 spørringer per visning vil man da teoretisk kunne sitte med 10->30 000 sidevisninger i sekundet, er nok ikke så alt for galt det.

 

blackbrrd: Det med ram stemmer hvertfall bra, mysql sluker minne, og når det ikke er nok blir det ganske så sent.

Endret 4. august 2005 av Ueland

[blackbrrd]

Vi kjører Postgres på Suse Linux 9.1 el 9.2 64bit versjonen på en Dual Xeon 2,8ghz med 2gb ram og 3x15000rpm disker i raid 5. Kjørte det en stund på en maskin med ide-disker noe som fungerte utmerket inntil vi begynnte å gå tom for ram.

Jeg håper det er behov for datamengde og sikkerhet som gjorde at RAID 5 ble valgt her. Normalt sett vil denne løsningen ha dårligere skriveytelse enn et speil eller en enkelt disk, mens leseytelsen omtrent den samme som for speilet. Caching kan selvfølgelig bedre noe på dette, en en disk ekstra og RAID 10 ville sannsynligvis føre til en drastisk økning i diskytelsen.

Du har nok rett i det ang. ytelse, men det ville bare gjort at vi venta 5min istedet for 7min på en spørring. Som sagt, harddisk ytelse har lite å si i forhold til å ha ram nok til å cache så å si alle relevante data.

 

Tror raid 5 er svært avhengig av kontrolleren. Hvis kontrolleren er bra nok burde raid 5 ha x-1/x (hvor x er antall disker) så bra lese/skrive ytelse som raid 0. Det er nok ikke slik i praksis dog.