Hva er bedre med MSSQL contra MYSQL?

[Manfred]

Helt klart. Og jeg har jo så lite å gjøre om dagen, he he. Jeg skal ha det i bakhodet, men først er det eksamen i MAT1000 på torsdag.

 

ÅÅÅÅÅÅÅ jeg er såååå glad jeg er ferdig meg eksamener. Har fortsatt mareritt om at jeg møter opp på eksamen helt uforberedt.

Det hørtes skremmende kjent ut Jeg hadde ikke lest en linje før sql-eksamen. Gikk bra, tho...

[roac]

Helt klart. Og jeg har jo så lite å gjøre om dagen, he he. Jeg skal ha det i bakhodet, men først er det eksamen i MAT1000 på torsdag.

 

ÅÅÅÅÅÅÅ jeg er såååå glad jeg er ferdig meg eksamener. Har fortsatt mareritt om at jeg møter opp på eksamen helt uforberedt.

Shh! Ikke si sånt da, jeg har nettopp begynt jo. Dette er første semesteret på Bachelor i Informatikk

[kaffenils]

Shh! Ikke si sånt da, jeg har nettopp begynt jo. Dette er første semesteret på Bachelor i Informatikk

 

Da har du vel i alle fall noen kjekke (men vanskelige?) fag å se frem til.

[blackbrrd]

Postgres 8.1.x, kan være det er forbedret i 8.2 eller 8.3

SELECT orderid FROM torder ORDER BY orderid LIMIT 100 OFFSET <16 ms

SELECT orderid FROM torder ORDER BY orderid LIMIT 100 OFFSET 100000 tar ~78ms

SELECT orderid FROM torder ORDER BY orderid LIMIT 100 OFFSET 500000 tar ~407ms

SELECT orderid FROM torder ORDER BY orderid LIMIT 100 OFFSET 900000 tar ~812ms

SELECT orderid FROM torder ORDER BY orderid LIMIT 900000 tar ~2774ms

 

Tabellen har masse felter som blir lagret inline, og dataene er cachet (ligger allerede i ram)

 

OFFSET er ikke spesiellt effektivt

 

Gidder ikke engang sjekke hvordan dette ser ut i en spørring med joins

Endret 11. desember 2007 av blackbrrd

[kaffenils]

Takk, det var interessant

 

Må si mønsteret er veldig likt det jeg får fra SQL Server. Kjørt tilsvarende spørring på SQL Server og som du ser så stiger kjøretiden på samme måte som de resultatene du postet.

 

--1 and 100 ~1ms

--100000 and 100099 ~76ms

--500000 and 500099 ~374ms

--900000 and 900099 ~725ms

--1 and 900000 ~2523ms

 

Da har jeg i alle fall fått svar på det jeg lenge har lurt på. Er redd det er mange som ikke skjønner effekten av LIMIT x,y med høy offset. Tenker spesielt på minne og I/O belastning.

Om MySQL har en bedre måte å gjøre det på tviler jeg vel egentlig sterkt på Men om noen MySQL-guruer har lyst så kan de godt gjøre tilsvarende tester.

 

Det som også hadde vært interessant å vite er på hvilke shared locks Postgres setter. SQL Server plasserer shared locks på alle keys/rows frem til offset+limit. For å forklare litt bedre: Hvis jeg ber om rad 900000 til 900001 (Dvs. to rader returneres) så vil allikevel SQL Server plassere shared locks på 900001 rader. SQL Server eskalerer selvfølgelig til en shared table lock i stedet for 900001 row/key locks.

Egentlig ganske logisk siden alle rader tross alt må leses for å finne offset.

 

Klarer du å finne ut hvordan Postgres gjør det?

[j000rn]

Hvis det er locks du er redd for, vil ikke dette hjelpe?

 

WITH rows AS

(

SELECT row_number() OVER (ORDER BY DatabaseName) as row, DatabaseID FROM Databases WITH (nolock)

)

SELECT Databases.* FROM rows WITH (nolock) INNER JOIN Databases ON Databases.DatabaseID = rows.DatabaseID

WHERE row BETWEEN 1000000 and 1000100

 

Da vil man jo kun få lock på de 100 radene, right? Og CTE'n vil være ganske liten (kun 2x int). Og den er ferdig sortert, siden den bruker index'n på DatabaseName, DatabaseID.

 

Kaffenils: Siden du er så nysgjerrig på dette kan du vel teste og dele resultatet med oss andre?

 

Edit: urg, funka ikke med formatering inni <code>...

Endret 11. desember 2007 av jorn79

[kaffenils]

WITH (NOLOCK) vil gi dirty reads. Men hvis en ikke har problemer med det så er det faktisk en smart løsning du kommer med der. Testet og det virket som forventet!

 

Et annet alternativ er å bruke en av de to row-versioning metodene en har i SQL Server.

 

Grunnen til at jeg synes dette er veldig interessant jeg endelig kan slenge en "in your face" til enkelte MySQL-disipler jeg kjenner neste gang de hoverer med at SQL Server ikke har en LIMIT x,y funksjon

Neida, det er ikke (bare) derfor. Har alltid vært litt misunnelig på LIMIT funksjonen, men nå vet jeg vel egentlig at row_number() over() kan gjøre samme nytten og at begge egentlig suger big time ved høye offsetverdier.

 

Jeg har satt roac på saken med å teste det når han er ferdig med eksamen. Så følg med på www.mssql.no...

[j000rn]

WITH (NOLOCK) vil gi dirty reads. Men hvis en ikke har problemer med det så er det faktisk en smart løsning du kommer med der. Testet og det virket som forventet!

 

De vil jo bli "vasket" slik at de ikke er dirty når man joiner med "seg selv-tabellen" som det ER locks på...? Eneste som kan skje er at man kan en sjelden gang får litt mindre enn 100 rader. Og hvis man har såpass mange rader at dette faktisk er en issue (ytelse) så bryr man seg vel ikke om man får 99 el. 100 rader.... Regner med at dette er for presentasjonslaget uansett.

 

Edit: skriveleif

Endret 11. desember 2007 av jorn79

[roac]

Når det gjelder with nolock så har jeg bare dette å si: Hvis dirty reads ikke er en issue og du må spesifisere det, så kjører du sannsynligvis med galt transaksjonsnivå i utgangspunktet, og i så tilfelle er det transaksjonsnivået du bør se på.

 

Ellers kan det nevnes (hvis det ikke allerede er gjort) at en clustered index på kolonnen du sorterer på vil effektivisere slike spørringer, siden SQL Server i så tilfelle vil søke å bruke page locks i steden for row locks. Videre vil ALLOW_ROW_LOCKS og ALLOW_PAGE_LOCKS på en index ville kunne hjelpe, siden SQL Server i så tilfelle ikke trenger å lese radene for å låse dem.

 

Mao: Det er en rekke faktorer som kan påvirke ytelsen, i forskjellige sammenhenger

[kaffenils]

De vil jo bli "vasket" slik at de ikke er dirty når man joiner med "seg selv-tabellen" som det ER locks på...?

 

Du kan risikerer at verdiene i kolonnen du joiner cte og tabellen med endres mens du leser så du kan i prinsippet ende opp med feil data.

[kaffenils]

Når det gjelder with nolock så har jeg bare dette å si: Hvis dirty reads ikke er en issue og du må spesifisere det, så kjører du sannsynligvis med galt transaksjonsnivå i utgangspunktet, og i så tilfelle er det transaksjonsnivået du bør se på.

 

Det som gjør at Jørns eksempel fungerer er jo nettopp at en har forskjellige isolasjonsnivåer på cte (nolock) og tabellen en joiner cte'en med (f.eks. read committed). Du vil ikke plassere låser på radene cte'en leser, men radene som joines fra Databases tabellen vil en plassere shared locks på. Uansett så er jeg generellt skeptisk til å bruke nolock/read uncommitted.

[j000rn]

Men... dette vil vel uansett være en dårlig løsning på data som skal gjøres noe med i en større transaksjon/jobb. Da vil man vel heller bruke TOP x og heller sette en status eller legge de som er "utført" inn i en annen tabell og sjekke mot.

 

Poenget mitt er at dette er for presentasjonslaget, og da vil det som regel ikke være så nøye om man får noen rader ut av sync... Så lenge dette ikke er en del av en seriøs økonomisk rapport

 

I akuratt mitt eksempel vil heller aldri DatabaseName *endres*. Derfor vil eneste som kan skje være at det kommer et par rader mindre i resultatet.

Endret 11. desember 2007 av jorn79

[kaffenils]

Ellers kan det nevnes (hvis det ikke allerede er gjort) at en clustered index på kolonnen du sorterer på vil effektivisere slike spørringer, siden SQL Server i så tilfelle vil søke å bruke page locks i steden for row locks.

 

Jeg har søkt på nettet, søkt i BOL, søkt overalt, men har ikke klart å finne info om når SQL Server vil velge en page lock istedet for row locks. Klarer ikke engang å fremtvinge en page lock selv om jeg SELECTer rader som garantert ligger i samme page. Alt blir til key locks.

[roac]

Jeg har søkt på nettet, søkt i BOL, søkt overalt, men har ikke klart å finne info om når SQL Server vil velge en page lock istedet for row locks. Klarer ikke engang å fremtvinge en page lock selv om jeg SELECTer rader som garantert ligger i samme page. Alt blir til key locks.

Plutselig dukker det opp noe spennende her. Det kommer én låsing ikke sant? key range locks hadde jeg helt glemt, de gir muligheten til å låse alle rader med key (id) mellom to bestemte verdier, og vil i så tilfelle være en effektiv låsing. Dette skal du også få hvis du bruker between på en primærnøkkel.

 

Jeg føler meg da rimelig sikker på at page locks brukes når du skal låse en stor mengde enkeltstående rader i en og samme datablokk. I ekstreme tilfeller hvor dette gjøres på de fleste datablokker vil sågar SQL Server generere en table lock istedenfor row locks.

 

Nå har jeg ikke en slik tabell tilgjengelig til å leke med, men gitt at du har en tabell med personer, med en kolonne som heter gender, hvor du har M for mann og K for kvinne, kan man tenke seg at man kjører følgende spørring:

 

update tabell set gender = 'F' where gender = 'K'

 

Dette føler jeg meg rimelig sikker på at vil generere en table lock dersom det er et tilstrekkelig antall rader og en tilstrekkelig mengde kvinner i tabellen.

Endret 12. desember 2007 av roac

[kaffenils]

Plutselig dukker det opp noe spennende her. Det kommer én låsing ikke sant? key range locks hadde jeg helt glemt, de gir muligheten til å låse alle rader med key (id) mellom to bestemte verdier, og vil i så tilfelle være en effektiv låsing. Dette skal du også få hvis du bruker between på en primærnøkkel.

Range locks får du kun ved serializable transaksjonsnivå. Og hvis du selecter 1000 rader så får hver eneste key en RangeS-S lock, så det krever like mye ressurser som en vanlig S lock.

 

Jeg føler meg da rimelig sikker på at page locks brukes når du skal låse en stor mengde enkeltstående rader i en og samme datablokk. I ekstreme tilfeller hvor dette gjøres på de fleste datablokker vil sågar SQL Server generere en table lock istedenfor row locks.

 

Det er det som av en eller annen grunn ikke skjer. Selv om jeg leser mange rader som finnes i et fåtall pages så setter SQL Server key/row locks, ikke page locks. Jeg ser av og til page locks, men har ingen anelse om hvilke kriterier som må være tilstede for at SQL Server foretrekker en page lock foran flere row/key locks. Angående eskalering til table lock så skjer dette når antall locks overstiger ~5000 locks av enten page eller key/row.

 

Nå har jeg ikke en slik tabell tilgjengelig til å leke med, men gitt at du har en tabell med personer, med en kolonne som heter gender, hvor du har M for mann og K for kvinne, kan man tenke seg at man kjører følgende spørring:

 

update tabell set gender = 'F' where gender = 'K'

 

Dette føler jeg meg rimelig sikker på at vil generere en table lock dersom det er et tilstrekkelig antall rader og en tilstrekkelig mengde kvinner i tabellen.

 

Ja, som sagt så eskalerer SQL Server til en table lock hvis antall locks overstiger ~5000.

Endret 12. desember 2007 av kaffenils

[roac]

Inside Microsoft SQL Server 2005: The Storage Engine:

The locking of rows (or keys) is heavily favored. The type of locking chosen is based on the umber of rows and pages to be scanned, the number of rows on a page, the isolation level in effect, the update activity going on, the number of users on the system needing memory for their own purposes, and so on.

 

Og så (til min store overraskelse):

Note SQL Server never escalates to page locks, and it is not possible to lock just a partition of a table or index. The result of a lock escalation is always a full table lock

(Merk: Partition som i partitioned index/partitioned table, ikke som i "del av")

 

Så mao, with PAGLOCK kan helt klart være på sin plass når man vet at det er mest effektivt. Jøss, dette var jo faktisk en lærerik dag. Alltid morosamt når man kan grave seg lenger ned i materien. Men jeg undres på... eskalerte ikke SQL Server 2000 (eller var det 7.0) til pagelocks av seg selv?

Endret 12. desember 2007 av roac

[kaffenils]

Så mao, with PAGLOCK kan helt klart være på sin plass når man vet at det er mest effektivt. Jøss, dette var jo faktisk en lærerik dag. Alltid morosamt når man kan grave seg lenger ned i materien. Men jeg undres på... eskalerte ikke SQL Server 2000 (eller var det 7.0) til pagelocks av seg selv?

 

Hvis jeg ikke husker helt feil så støttet SQL Server 6.5 kun page locks og table locks. SQL Server satte først page locks med mulighet for eskalering til table lock.

 

Fra og med SQL Server 7.0 så kom støtte for row/key locks. Eskaleringen har alltid kun vært til table lock, aldri til page locks. Og jeg undres fortsatt på hva som er kriteriet for at SQL Server initiellt velger page locks istedet for key/row locks. Du får sjekke med noen av guruene hos ErgoGroup hva som er kriteriet for at SQL Server skal velge page lock istedet for key locks. En måte er selvfølgelig å sette ALLOW_ROW_LOCKS OFF på indexen, men det er egentlig en dårlig løsning da en som regel ønsker key locks over page locks. En annen måte er som du sier å bruke PAGLOCK hintet hvis du vet at page locks er optimalt.

Så jeg lurer fortsatt. Får høre med Jon Jahren om han vet noe mer på neste MSDN (i februar tror jeg). Eller kanskje jeg kan sende en mail til han som holdt Tuning & PErformance kurset jeg var på hos Microsoft for noen uker siden.

 

Partition locking mener jeg å ha hørt at vil komme i SQL Server 2008. Ikke det at jeg foreløpig har brukt partitioned tables.

 

Edit: Leste ikke første avsnitt du skrev. Der står det jo hvilke kriterier som bestemmer hvilken lock type (row eller page) SQL Server velger. Men det er nok ikke ment for oss vanlige dødelige å forstå hvilke threshold verdier som medfører det ene eller andre. Eskaleringen til table lock er enkel å forstå, men om det settes page eller key lock... Neppe.

Endret 12. desember 2007 av kaffenils

[Frank2004]

Klarer du å finne ut hvordan Postgres gjør det?

Så langt jeg kan se (gjør bare sampling av locks, logger ikke.. - men de ser ikke ut til å endre seg underveis) bruker postgres to locks på en tilsvarende spørring: en eksklusiv lock på transaksjons-id'en (dette gjelder for alle transaksjoner) og en shared lock på tabellen.

 

Kos med MVCC.

Endret 12. desember 2007 av Frank2004

[deviant]

Når det gjelder LIMIT i MySql kan dette enkelt gjøres med ROW_NUMBER() i MSSQL

 

Hvis du har lyst til å ta livet av SQL Serveren så må du gjerne bruker row_number() får å simulere LIMIT x,y til MySQL.

Regner med du tenker på denne metoden?

 

mysql> select count(*) from guestbookmessage;
+----------+
| count(*) |
+----------+
|  7970977 |
+----------+
1 row in set (0.00 sec)

mysql> select guestbookmessage_id from guestbookmessage limit 1000000,1;
+---------------------+
| guestbookmessage_id |
+---------------------+
|             1054263 |
+---------------------+
1 row in set (3.30 sec)

mysql> select guestbookmessage_id from guestbookmessage limit 2000000,1;
+---------------------+
| guestbookmessage_id |
+---------------------+
|             2112151 |
+---------------------+
1 row in set (4.66 sec)

mysql> explain select guestbookmessage_id from guestbookmessage limit 1000000,1;
+------------------+-------+---------------+---------+---------+------+---------+-------------+
| table            | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------------------+-------+---------------+---------+---------+------+---------+-------------+
| guestbookmessage | index | NULL          | PRIMARY |       4 | NULL | 7897721 | Using index |
+------------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

 

Moralen må være at når man har behov for å bla i store resultatsett er det på tide å åpne en peker, eller å ta i bruk plattformens standard-brytende offsett-funksjonalitet (Årets julegave går herved til Sprakradet).

Endret 13. desember 2007 av deviant

[kaffenils]

Og med mindre det allerede er blitt for sent på kvelden skulle jeg mene at Innodb bare benytter row level locking, og vil uansett ikke benytte blokkerende låser for denne operasjonen.

 

Dette er ikke stedet å mene, her må man vite... 100% sikkert

Alle operasjoner som innebærer lesing eller skriving til en rad vil medføre at en lock må settes. Husk at en lock ikke nødvendigivis betyr "lås slik at ingen andre kan lese". Ved lesing settes en shared lock. Det betyr at andre også kan lese objektet (les: row,key,page eller table) og plassere en shared lock på. Det er kun skriveoperasjoner, som bruker exclusive lock, som ikke er kompatibel med en shared lock, og det er da du ser at insert/update/delete statementet henger, pga. at det må vente på at andre locks må frigis før exclusive lock kan settes. Når jeg snakker om shared og exclusive locks så er disse ekvivalente til det MySQL kaller read og write locks.

 

At InnoDB kun benytter row-level locking høres ut som noe som kan skape problemer ved store recodsets. Det må da sluke mye minne å plassere en million låser istedet for en table lock, selv om concurrency selvfølgelig blir høyere.

Liker bedre SQL Server sin måte å gjøre det på, nemlig å eskalere locks til en table-lock når antall row/key/page locks overskrider en grenseverdi.

 

Takk forresten for innspill angående målingene for LIMIT x,y.

 

Og angående sprakradet. Jeg har en "stygg" uvane med å blande norsk og engelsk når jeg snakker om datatekniske ting. lock, shared, exculsive, key u name it.