[MySQL] Counter performance

[_Gekkie_]

Al even niet geweest, maar ik kan niet direct iets vinden waar ik mn antwoord goed kan terug vinden.

De situatie is als volgt:
een eenvoudige tabel met per id een counter:

SQL:

CREATE TABLE `views` (
 `id` int(11) NOT NULL default '0',
 `views` bigint(20) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Eenvoudig dus. Het is een centrale hits counter per unieke ID elders in het systeem. Dit wordt via de volgende query bijgewerkt:

SQL:

INSERT INTO `views` (`id`, `views`)
   VALUES ('17703798', 1)
   ON DUPLICATE KEY UPDATE `views`=`views`+1

Ziet er ook eenvoudig uit. Echter is dit een nogal wisselvallig performende query (als ik mn slowlog en pt-query-digest erop na sla)

code:

# Query 2: 0.00 QPS, 0.00x concurrency, ID 0xF6C83C2A9EFFC15F at byte 568639365
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.00 [1.0], V/M = 3.33
# Query_time sparkline: |      ^_|
# Time range: 2011-12-06 14:21:15 to 2012-01-10 12:14:54
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     603
# Exec time     13   4910s      5s     91s      8s     19s      5s      7s
# Lock time     11    251s    24us     13s   416ms      3s      1s    38us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0  62.24k     101     106  105.69  102.22    0.56  102.22
# String:
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+  ##########

Nu ben ik al een tijdje opzoek naar een betere manier want het begint me nu toch een beetje te irriteren...

Any thoughts?

[4VAlien]

Heb je veel conurrent hits? Het lijkt erop alsof alle queries serieel, dus achter elkaar, worden uitgevoerd en daarom op elkaar staan te wachten. Bij een enkele query is dit geen probleem maar zodra de load omhoog gaat stapelen de requests zich op.

Standaard locking met InnoDB is echter op row level dus zou niet echt het probleem moeten zijn. wat gebeurt er als je een testje doet met een aparte update en insert query ?

[ Voor 26% gewijzigd door 4VAlien op 10-01-2012 15:20 ]

[TheNephilim]

Hoeveel bezoekers komen er op de website? Als het er heel weinig zijn en je hebt deze problemen is er iets niet goed, als je veel bezoekers hebt, kan uiteindelijk zelfs de hardware de limiet zijn op het aantal inserts dat je kunt doen.

De meesten duren 1 seconden, dat schiet inderdaad niet op. InnoDB zou dit makkelijk moeten kunnen, bij MyISAM heb je tablelocks, maar dat is het hier ook niet.

[_Gekkie_]

Het zijn inderdaad redelijk wat hits die erop komen.. Maar inderdaad met InnoDB zou het allemaal geen probleem moeten zijn... De tabellen zijn soms wel wat groot, dat wel, maar ach, op een 10GB geheugen waarvan zeker nog 3GB vrij moet dit allemaal wel goed te doen zijn lijkt me..

@4VAlien: een aparte update / insert query lijkt me alleen maar vreemder omdat ik dan in mn applicatie eerst een select moet doen (is er al een rij) en a.d.v. die uitkomt een insert of update ... lijkt me niet handig toch?

@TheNephilim: De machine zou het prima moeten kunnen handelen...

Een laatste optie is een soort memcached tussen oplossing om het een soort van gebuffered erin te duwen.. Maar echt fantastisch is dat nou ook weer niet ivm de betrouwbaarheid van memcached (het is tenslotte cache, en niet een persistent iets oid)

[CH4OS]

Als je het ID met 1 wil ophogen, waarom geef je die dan niet een auto_increment?

[Styxxy]

CptChaos schreef op dinsdag 10 januari 2012 @ 16:04:
Als je het ID met 1 wil ophogen, waarom geef je die dan niet een auto_increment?

Dat doet hij toch niet? Hij wilt de views updaten met +1.

[CH4OS]

Scratch this message.

[ Voor 98% gewijzigd door CH4OS op 10-01-2012 16:23 ]

[TheNephilim]

_Gekkie_ schreef op dinsdag 10 januari 2012 @ 16:01:
Een laatste optie is een soort memcached tussen oplossing om het een soort van gebuffered erin te duwen.. Maar echt fantastisch is dat nou ook weer niet ivm de betrouwbaarheid van memcached (het is tenslotte cache, en niet een persistent iets oid)

Oké duidelijk, dat moet prima werken dus gezien de specificaties van de machine. Memcache lijkt me inderdaad ook niet heel handig, dat is niet 100% persistent inderdaad.

Misschien is het een idee om het met AJAX op de lossen, dus met een JavaScript call de +1 doen. Dan hoeven je clients in ieder geval niet te wachten op de +1.

Een ander idee is om iets als MangoDB te gaan gebruiken, een snelle key => value database. Die kun je dan 1x per uur (bijv.) uitlezen om je views alsnog naar je MySQL database te schrijven.

Ik neem aan dat je MySQL persistent connection en dergelijke op orde is. Ook max clients kan een beperking zijn, je opent voor elke hit een database connectie namelijk.

[Tead]

Als er heel veel views zijn kan je ook maar een deel van de views tellen. Dus in de code een random opnemen tussen 1 en 10 en dan elke keer als hij 10 is de query uitvoeren. En dan je query +10 doen. Heb je maar een 10de van de query's die je nu hebt en bij 10.000+ views zal je zien dat de afwijking maar minimaal is. Analytics systemen werken ook zo.

[GlowMouse]

Vergeet bovenstaande antwoorden. Je zult wel geen geen raid controller met bbu cache hebben, en dan moet je met innodb-flush-log-at-trx-commit spelen om het snel te krijgen.

[WouZz]

Misschien heb je iets aan een delayed insert? (Als je er een MyISAM table van maakt)

[ Voor 63% gewijzigd door WouZz op 10-01-2012 19:03 ]

[frickY]

Ik neem aan dat het vaker gebeurd dat een view geupdate moet worden, dan dat deze geinsert moet worden. Ik zou als uitgangspunt daarom een update gebruiken, in plaats van een insert;

UPDATE `views` SET `views`=`views`+1 WHERE id =17703798

Vervolgens kun je in je client controleren of er een affected row was, en zo niet een nieuw record aanmaken.
Als je als INSERT dezelfde query gebruikt als nu hoef je ook de tabel niet te locken om te voorkomen dat een andere thread het record aanmaakt.

Vervang je primary key met een UNIQUE KEY(id, views) en er is ook geen 2de lookup meer nodig.

De kans dat je daadwerkelijk een bigint() nodig hebt lijkt me klein. Kun je niet uit te voeten met een unsigned int? Je kunt dan tellen tot en met 4.294.967.295 (136 jaar lang elke seconde een hit) en het kost je de helft van een bigint, hence er passen meer records in het geheugen.

Memcached kan hier oveirgens wel degelijk een optie zijn. Ja, er is een kans dat je eens wat hits kwijt raakt. Maar hoe belangrijk is dat? Dat wordt wel weer gecompenseerd door mensen die op F5 drukken.
Je kunt Memcached gebruiken voor het realtime bijhouden van de aantallen, en een losse thread op rustige momenten de gegevens uit memcached naar mysql laten zetten.

[ Voor 30% gewijzigd door frickY op 10-01-2012 19:13 ]

[WouZz]

Als het echt om een high traffic site gaat zou ik voor de oplossing van frickY gaan. Of een Gearman / RabbitMQ /oid messagebus ertussen knallen...

[GlowMouse]

frickY schreef op dinsdag 10 januari 2012 @ 19:05:
Ik neem aan dat het vaker gebeurd dat een view geupdate moet worden, dan dat deze geinsert moet worden. Ik zou als uitgangspunt daarom een update gebruiken, in plaats van een insert;

[...]

Vervolgens kun je in je client controleren of er een affected row was, en zo niet een nieuw record aanmaken.

Als je als INSERT dezelfde query gebruikt als nu hoef je ook de tabel niet te locken om te voorkomen dat een andere thread het record aanmaakt.

Je hoeft echt niet bang te zijn dat twee threads een rij met dezelfde PK aanmaken. Sterker, juist jouw oplossing werkt slecht bij concurrency en is bovendien niet sneller.

Vervang je primary key met een UNIQUE KEY(id, views) en er is ook geen 2de lookup meer nodig.

Dat is een zeer slechte suggestie. Niet alleen heeft hij nu geen 2de lookup nodig omdat de primary key de tabel zelf is, maar met jouw UNIQUE KEY (wat gewoon een PK is in InnoDB bij gebrek aan echte PK) kun je niet meer garanderen dat er maar één record per id is.

[_Gekkie_]

De optie van frickY ziet er inderdaad het beste uit:
- de bigInt is not een legacy probleem (unsigned op beide zou idd voldoende moeten zijn)
- persistent connections e.d. is allemaal al geregeld, sterker nog: er is al een verbinding tegen de tijd dat die call wordt uitgevoerd
- delayed insert heeft geen zin, het is innodb
- MangoDB had denk ik MongoDB moeten zijn, maar dat is hier niet zo handig voor, een Membase zou wellicht geschikter zijn (memcache+persistent+elastisch) maar dan moet ik die idd wel periodiek flushen/checken


de memcached tussenoplossing kan wel: maar die moet ik dan wel ieder uur checken / flushen ivm het een volatile cache is .. (en keys worden er klakkeloos uit gegooid als ie vol zit.. hence: cache)

Ik denk dus dat ik voor de update ga met een fallback voor de replace (ivm evt. tussenliggende hit oid) maar dan wel incl een centrale `views`-memcache key waarin in de hits +1

Overigens: dat is dus wel een get+set memcache per hit... is dat (ivm RTT) ook wel slim?

Ik had eerst gehoopt dat het eventueel in de query zelf anders zou kunnen (ivm de +1 oid)

Thnx anyway voor de verschillende tips... Een pipelining/bus via memcache zal het dus wel worden

[GlowMouse]

Je vergeet op mijn post over de oorzaak te reageren

[_Gekkie_]

We werken met de volgende mysql settings:
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_support_xa = 1
(wat innodb dan)

Die Unique overigens vind ik zelf nogal trikcy... ziet er zo gek uit? een simpele tabel en dan een unique? een PK is by definition to unique? die views is daarentegen alleen een teller... toch?

[GlowMouse]

Je dumpt je config, maar schept daarmee geen duidelijkheid.

[_Gekkie_]

Sorry, wat had je willen zien van me? Ik dacht dat je hierom vroeg?

[GlowMouse]

Hieraan zie ik niet dat je geen raid controller met bbu cache hebt. Dat zou, icm innodb_flush_log_at_trx_commit = 2, de traagheid compleet vertragen. De echte oplossingen zitten er dan ook in om zo'n raid controller te gebruiken of innodb_flush_log_at_trx_commit te wijzigen.

[_Gekkie_]

Er gebeurd nu toch geen IO per commit, maar eens per seconde?
aldus de manual:
"when the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it."

In elk geval gaat het hier om een grote EC2 instance die dedicated MySQL doet en andere queries die meer updaten al een dolle doet?

Zou het kunnen liggen aan de hoeveelheid records in die tabel? (er zijn nogal wat id's waarvoor views worden geteld)

[frickY]

_Gekkie_ schreef op dinsdag 10 januari 2012 @ 19:53:
Overigens: dat is dus wel een get+set memcache per hit... is dat (ivm RTT) ook wel slim?

Dat zou atomisch gezien niet zo handig zijn. Maar gelukkig heeft memcached een INCR (increment) commando om een bestaande waarde met 1 op te hogen.

@Glowmouse
Ik denk dat je het te diep zoekt. Ook op een oud barrel moet zoiets dergelijks prima kunnen performen.

[GlowMouse]

Je innodb_buffer_pool_size moet zeker nog ingesteld worden. De default waarde zorgt zeker voor traagheid. Icm stel je innodb_flush_method = O_DIRECT in. En probeer het eens zo: innodb_flush_log_at_trx_commit=0. innodb_log_file_size moet mogelijk ook omhoog.

frickY schreef op dinsdag 10 januari 2012 @ 20:29:
[...]
@Glowmouse
Ik denk dat je het te diep zoekt. Ook op een oud barrel moet zoiets dergelijks prima kunnen performen.

Lees de OP: deze query doet er minimaal 5 seconden over om te draaien. De kans is heel groot dat dit een i/o probleem is. Dat hij op een cloud draait, verklaart in ieder geval veel.

[ Voor 3% gewijzigd door GlowMouse op 10-01-2012 20:33 ]

[Voutloos]

Het zou me verbazen als een 486 met een Zip-drive het niet sneller dan dit zou kunnen, dus er zit echt iets goed fout in je config/hardware.

- Je noemt geheugengebruik, maar hoe zit het met de rest van de resources? Zit je niet aan je max qua load/cpu/IO?
- Het mag bij zo'n eenvoudige tabel eigenlijk niet uitmaken maar om hoeveel records gaat het nou? Hoeveel inserts per seconde?
- Is dit de enige query die traag is? Kost een SELECT 1+1 stiekem niet al 5s?
- Mag ook niet uitmaken, maar moet het toch zeggen: Haal eens de quotes om integers weg, het zijn geen strings.
- Is deze query ook echt as is, of heb je een aantal kolommen weggelaten?
- Is dit over een langere periode gemeten? Je meet niet stiekem enkel tijdens een of ander falend backup proces?

Overigens zou ik met een beetje volume sowieso met iets als memcached gaan spelen, performance verschil is gewoon eigenlijk te groot. Uiteraard moet je dan wel het syncen naar db een beetje slim doen, zodat je hoogstens x minuten kwijt kan raken.

[_Gekkie_]

Allen,

Ik heb meer innodb_-parameters uit de my.cnf weggelaten. MySQL is de enige service op de EC2 instance. De CPU load komt níet boven de 5%, mem is 3GB vrij en I/O is gemiddeld 23 OP/s (written)

Al met al heeft de machine het niet moeilijk.

De incr-actie op memcache zou leuk zijn, maar dan zit ik weer met het ophalen van de keys: ik weet niet welke ID's er allemaal gebruikt worden dus ik moet het ergens via een meta-key of een json (zoals ik het nu bedacht heb) opslaan en incrementen...

de query is de volledige query, de tabel ook. Niets meer, niets minder.

SQL:

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

lijkt me ook geen probleem

Voor de goede orde:
[code]
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_file_per_table
innodb_buffer_pool_size = 4500M
innodb_additional_mem_pool_size = 200M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_support_xa = 1
innodb_fast_shutdown = 1
[code]

Er draaien zo'n 180 databases op met elk dezelfde structuur en dezelfde views tabel. Dit is een dergelijke tabel:

SQL:

*************************** 1. row ***************************
           Name: views
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 23909
 Avg_row_length: 66
    Data_length: 1589248
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2011-12-06 13:45:24
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Ik heb een deel van de pt-query-digest getoond waaruit ook blijkt dat dit over een langere periode zo is. Wat ik alleen niet begrijp: als ik de query lukraak op een database doe als unittest, gaat het wel gewoon razendsnel... Tegelijk zie ik in alle logs en monitoring dit als top3 langzame queries terugkomen...

I'm baffled!

[ACM]

_Gekkie_ schreef op dinsdag 10 januari 2012 @ 23:32:
Ik heb meer innodb_-parameters uit de my.cnf weggelaten. MySQL is de enige service op de EC2 instance. De CPU load komt níet boven de 5%, mem is 3GB vrij en I/O is gemiddeld 23 OP/s (written)

Met 'iostat' kan je naast de totale iops ook meer details zien. Bijvoorbeeld door 'iostat -x 1' te doen kan je dan elke seconde een snapshot zien, dat is vast nuttiger dan een gemiddeld aantal iops te weten (je wilt juist de pieken) en daarbij zie je dan ook de gemiddelde wachttijd en queue-depth.

Al met al heeft de machine het niet moeilijk.

Dat bewijs je helaas niet met bovenstaande cijfers, hooguit dat ie het gemiddeld gezien niet moeilijk heeft

Er draaien zo'n 180 databases op met elk dezelfde structuur en dezelfde views tabel.

Misschien is het dan niet zozeer een probleem met innodb, maar bijvoorbeeld met de table_open_cache. Maar het is een beetje gokken zo. Er zijn tools beschikbaar (o.a. phpmyadmin heeft er een pagina voor) waar je aan kan zien wat er zoal aan status-vars beschikbaar is en of hun waardes een probleem kunnen impliceren.

Ik heb een deel van de pt-query-digest getoond waaruit ook blijkt dat dit over een langere periode zo is. Wat ik alleen niet begrijp: als ik de query lukraak op een database doe als unittest, gaat het wel gewoon razendsnel... Tegelijk zie ik in alle logs en monitoring dit als top3 langzame queries terugkomen...

Het is typisch een query die vrij snel is, maar ook zomaar vertraagd kan worden doordat andere processen ineens veel IO gaan opsnoepen. Het kan dan ook geen kwaad om te kijken of er bijvoorbeeld iets van een patroon in zit, of dat de vertragingen een correlatie met andere trage queries hebben. De kans is best groot dat je naar gevolgen van iets zit te kijken, ipv oorzaken.

Er zijn overigens nog wel wat alternatieve methoden beschikbaar om zoiets als dit te doen:
Kijk of partitioning van de tabel interessant is (mysql heeft daar functionaliteit voor), op die manier maak je de PK-index relatief ten op zichte van de insert kleiner en zal je daar minder queries hebben die elkaar in de weg zitten (je zou bijvoorbeeld kunnen partitioneren op blokken van 1000.000 id's of domweg op id%10)

Kijk naar een aanpak met staging tables. Wat je met memcached suggereert hoeft natuurlijk niet in memcached, inserts in een kleinere tabel zijn doorgaans efficienter dan inserts/updates op een grote. Je kan dus bijvoorbeeld een staging-tabel maken aan en daar domweg het id van het bekeken item in inserten (evt met een timestamp om makkelijk een bepaalde tijdsbereik te kunnen nemen). En elke X minuten kan je dan de data overzetten van die stagingtabel naar de uiteindelijke tabel. Als je het goed doet, aggregeer je daardoor alle inserts/updates van die X minuten tot 1 query per id. Je kan uiteraard ook met een stagingtabel per X tijd werken (bijv tussen 2 alterneren) en die in zijn geheel overzetten nadat de tijd afgelopen is.

Mocht je actuele view-data eruit moeten kunnen halen, dan kan je dat alsnog met vrij eenvoudige queries blijven doen (het is dan views uit je basistabel + count(x) uit je stagingtabel).

Een andere oplossing, die gebruiken we bij tweakers, is om met een queue te werken. Bij ons worden de berichten in activemq gestopt door de webservers die de views registreren (producers) en vervolgens wordt dat met een achtergrondproces (consumer) uitgelezen en rustig aan verwerkt. In ons geval doen we uitsluitend inserts, waardoor we e.e.a. vrij eenvoudig per 100 hits kunnen batchen in 1 statement met 100 values-blokjes.

[TheNephilim]

@GlowMouse: Daar had ik nog niet aan gedacht inderdaad, die O_DIRECT is inderdaad een goeie.

Input/Ouput op de harddisk kan een issue zijn inderdaad. Is het trouwens zo dat bij het doen van een insert hij de hele index (PK in dit geval) opnieuw moet maken?

[GlowMouse]

TheNephilim schreef op woensdag 11 januari 2012 @ 11:03:
Input/Ouput op de harddisk kan een issue zijn inderdaad. Is het trouwens zo dat bij het doen van een insert hij de hele index (PK in dit geval) opnieuw moet maken?

Nee.

[t_captain]

_Gekkie_ schreef op dinsdag 10 januari 2012 @ 15:13:
Al even niet geweest, maar ik kan niet direct iets vinden waar ik mn antwoord goed kan terug vinden.

De situatie is als volgt:
een eenvoudige tabel met per id een counter:

SQL:

CREATE TABLE `views` (
 `id` int(11) NOT NULL default '0',
 `views` bigint(20) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Je kolommen zijn maar klein (INT+BIGINT). Desondanks kan de tabel best groot worden als er erg veel regels in staan. Hoe groot is de disk size van je tabel?

InnoDB heeft de eigenschap dat de primary key ON PAGE wordt opgeslagen. Dat kan bij grote tabellen leiden tot veel seek operaties in de hard disk. Dan kan het beter zijn om een extra INT(11) kolom toe te voegen met daarop een secundaire index. Die wordt OFF PAGE opgeslagen. Kan best schelen ivm caching.

[_Gekkie_]

Hmmm.

We hebben het e.e.a. verbeterd door in elk geval de de int's gelijk te trekken tot unsigned int(10)'s en we hebben ook goed gekeken of we de query niet over kunnen slaan en middels post-processing dmv een logparser kunnen verwerken..

De call naar de query gebeurd nu nog 1.119 cpm (alsdus NewRelic, wat een toptool is) en daarmee is het in elk geval al iets naar beneden... Ook volgens RPM zou het per query een resp. time:8.1 ms en dus een throughput:1,119 cpm moeten hebben... al met al lijkt het dus wel ok... ( het is gelukkig niet meer de nr 1 in de lijst )

[GlowMouse]

t_captain schreef op woensdag 11 januari 2012 @ 13:24:
[...]


Je kolommen zijn maar klein (INT+BIGINT). Desondanks kan de tabel best groot worden als er erg veel regels in staan. Hoe groot is de disk size van je tabel?

InnoDB heeft de eigenschap dat de primary key ON PAGE wordt opgeslagen. Dat kan bij grote tabellen leiden tot veel seek operaties in de hard disk. Dan kan het beter zijn om een extra INT(11) kolom toe te voegen met daarop een secundaire index. Die wordt OFF PAGE opgeslagen. Kan best schelen ivm caching.

Elk numeriek veld wordt ON PAGE opgeslagen. De winst van een INT tov een BIGINT in termen van opslagruimte is niet zo groot omdat er bij elke rij ON PAGE ook nog een stuk of 25 bytes aan meta-data wordt opgeslagen.

[t_captain]

Gaat niet om de numerieke data van het veld, maar om de bijbehorende index.

Ik heb wel eens een MySQL 5.0 / InnoDB database gehad met daarin een tabel met enkele miljoenen records. Elke record had bovendien een blob veld, waardoor de disk size rond de 1TB lag. Gevolg was dat de performance bij JOIN operaties (other_table.foreign_key = big_table.primary_key) erg slecht was. De oplossing was om een extra kolom toe te voegen met daaraan ook een index gekoppeld. Deze secundaire index wordt OFF PAGE opgeslagen, waardoor dezelfde JOIN ineens wel goed liep.

[TheNephilim]

t_captain schreef op donderdag 12 januari 2012 @ 09:35:
Gaat niet om de numerieke data van het veld, maar om de bijbehorende index.

Ik heb wel eens een MySQL 5.0 / InnoDB database gehad met daarin een tabel met enkele miljoenen records. Elke record had bovendien een blob veld, waardoor de disk size rond de 1TB lag. Gevolg was dat de performance bij JOIN operaties (other_table.foreign_key = big_table.primary_key) erg slecht was. De oplossing was om een extra kolom toe te voegen met daaraan ook een index gekoppeld. Deze secundaire index wordt OFF PAGE opgeslagen, waardoor dezelfde JOIN ineens wel goed liep.

Ik vind het toch wel een bijzondere situatie, dat juist de Primary Key op de disk word opgeslagen. Dat is de belangrijkste key in de meeste situaties. Gaat dit nog veranderen in komende MySQL versies, of is het echt een 'eigenschap' van InnoDB?

[Voutloos]

De PK zal ook echt wel in memory staan hoor, daar hoeft iig niets veranderd te worden hoor.

[GlowMouse]

Voutloos schreef op donderdag 12 januari 2012 @ 10:52:
De PK zal ook echt wel in memory staan hoor, daar hoeft iig niets veranderd te worden hoor.

De PK is de tabel. Dat zijn de (standaard) 16 kB pages waar de BREE index in staat met in de leaves de data. Of een BLOB-veld daarbij staat, hangt af van oa. het file format (antilope/barracuda) en de grootte van de BLOB. In het geval van t_captain zal de PK niet volledig in het geheugen hebben gestaan. Om de negatieve gevolgen te verminderen, kan InnoDB tegenwoordig blobs automatisch comprimeren.

[t_captain]

@TheNephilim:

Het is een eigenschap van InnoDB. Om precies te zijn: een eigenschap van de ingebouwde InnoDB storage engine in de wat oudere MySQL versies, inclusief het daarbij behorende Antilope file format. Met de nieuwere InnoDB plugin en het Barracuda file format was ik niet zo hard tegen het probleem aangelopen, omdat de blob velden tegenwoordig op aparte pages kunnen worden opgeslagen. Dat houdt de rest van de tabel (waar de pk index zit) klein. Klein = cached in memory = fast join

Om de PK bij de data op te slaan is overigens geen gekke gedachte. In veel situaties doe je een lookup van een veld op pk (bv via de fk van een andere tabel), waarna je een aantal kolommen van het gevonden record wilt lezen. PK bij de data ("on page") scheelt in dat scenario een seek.

@Voutloos:
Hangt allemaal af van de afmeting van de tabel in relatie tot het bechikbare RAM.

@GlowMouse:
Klopt. Als ik de mogelijkheid had gehad om de InnoDB plugin + Barracuda file format te gebruiken, zou ik ook DYNAMIC of COMPRESSED row format hebben gespecificeerd. Voordeel: blobs in aparte pages, rest van tabel incl PK in RAM. Extra voordeel voor COMPRESSED is dat je wat load op de DBMS verplaatst van disk naar CPU. We waren compleet IO-bound, dus het zou een acceleratie opleveren.

[ACM]

t_captain schreef op woensdag 11 januari 2012 @ 13:24:
Je kolommen zijn maar klein (INT+BIGINT). Desondanks kan de tabel best groot worden als er erg veel regels in staan. Hoe groot is de disk size van je tabel?

InnoDB heeft de eigenschap dat de primary key ON PAGE wordt opgeslagen. Dat kan bij grote tabellen leiden tot veel seek operaties in de hard disk. Dan kan het beter zijn om een extra INT(11) kolom toe te voegen met daarop een secundaire index. Die wordt OFF PAGE opgeslagen. Kan best schelen ivm caching.

Hoe gaat dit nou precies schelen? Ik snap je uitleg niet. Zijn data bestaat basically uit een 4-byte id en een 8-byte viewcounter (tegenwoordig blijkbaar 4byte). Met jouw extra kolom (wat staat daar in? de id-waarde weer?) voeg je dus 4 bytes extra toe en wordt de tabel (tenzij er 8-byte padding is) groter.
Erger nog, bij InnoDB bevatten secundaire indexen weer verwijzingen naar de primary index/key... Dus als uiteindelijk views = (id (=PK), viewcounter, idcopy (= unique key)) zou opslaan, dan zou je via de idcopy eerst een btree-search naar de goede plek doen om vervolgens met de dan gevonden PK-waarde alsnog weer een btree-search naar het uiteindelijke record te doen. De enige manier om dat te voorkomen is om dan een key op (idcopy,viewcounter) te maken, zodat ie de index kan gebruiken... Maar dan heb je domweg je tabel gecopieerd, want dat deed in dit geval de PK al. Althans, wellicht is 'using index' iets efficienter, maar je hebt er dan wel je tabel voor gedupliceerd in een index (want die key bevat dan uiteindelijk in werkelijkheid (idcopy, viewcounter, id!).

t_captain schreef op vrijdag 13 januari 2012 @ 15:03:
omdat de blob velden tegenwoordig op aparte pages kunnen worden opgeslagen

Maar dan moet je natuurlijk niet datzelfde advies gaan geven over een compleet ander soort tabel, wat zo'n beetje de kleinst mogelijke row voor een tabel in InnoDB is (nouja een tabel met enkel een boolean/tinyint-kolom heeft nog kleinere rows).

Om de PK bij de data op te slaan is overigens geen gekke gedachte. In veel situaties doe je een lookup van een veld op pk (bv via de fk van een andere tabel), waarna je een aantal kolommen van het gevonden record wilt lezen. PK bij de data ("on page") scheelt in dat scenario een seek.

Belangrijker nog... Je moet toch een verwijzing naar je rowdata opslaan. Bij InnoDB komt dat automatisch mee doordat je domweg in de PK-tree naar de goede plek gaat. Het nadeel is natuurlijk dat de btree heel erg verspreid op disk wordt opgeslagen bij grote rows en bovendien moet je de PK-waarden in al je indexen herhalen (de tabel is effectief een tree-map met "PK -> data"-entries en de secundaire indexen is dan weer een treemap met "key -> PK" entries).

Bij een methode als die van PostgreSQL wordt een tabel in pages uitgeschreven, maar staan alle indexen los daarvan. Dus zowel de PK als elke andere index hebben dezelfde page-verwijzingen. Dat heeft uiteraard weer als voordeel dat een index veel compacter kan worden opgeslagen (zeker de PK maar ook gewone indexen hebben altijd een evengrote row-verwijzing), waardoor een tree-traversal minder duur zal zijn dan wanneer er ook nog allerlei data tussen zit en dat secundaire indexen efficienter gebruikt kunnen worden omdat je niet steeds terug hoeft naar je PK.
Het nadeel is dat je onderhoud aan je index-trees wat duurder is (je moet tenslotte elke index aanpassen als een record wordt aangepast/verplaatst, ipv enkel die PK-tree) en dat je ook na een PK-lookup alsnog een data-lookup moet doen (maar die is wel eenvoudiger dan een PK-lookup zoals bij die secundaire indexen van innodb).