Sådan optimerer du hastigheden på MySQL med memory tabeller

PHP og MySQL hænger som regel tæt sammen. Ønsker man at optimere hastigheden i sin PHP applikation, vil det være oplagt at forsøge at minimere kald til MySQL databasen.

Af Kristian Just

I artikelserien "Hastighedsoptimering af PHP" går jeg i dybden med, hvordan man bygger hurtige og skalerbare PHP applikationer. Det er ikke en masse tips til små optimeringer, men håndgribelige metoder til at undgå flaskehalse og suboptimal kode.

Artiklerne omhandler udelukkende god eksekveringstid på dine PHP scripts. Vil du forbedre din hjemmesides overall loadtid, så start på PageSpeed Insights, eller se i din browsers Netværks-fane for nogle lavthængende frugter - det er ikke lige det, denne artikelserie handler om.

Artikler i serien:

PHP og MySQL hænger som regel tæt sammen. Ønsker man at optimere hastigheden i sin PHP applikation, vil det være oplagt at forsøge at minimere kald til MySQL databasen eller at gøre eksekveringen af MySQL forespørgslerne hurtigere.

De mest almindelige MySQL tabeltyper er MyISAM og InnoDB. Men MySQL tilbyder også andre mindre populære tabeltyper – heriblandt *MEMORY* tabeltypen.

Med MEMORY gemmes tabeldataene i hukommelsen i stedet for på et drev. At hente data direkte fra hukommelsen giver åbenlyse hastighedsfordele, men MEMORY tabellerne er ikke egnede i alle situationer – faktisk meget få.

Herunder kan du finde de væsentligste fordele, ulemper og egnede situationer for MEMORY tabellerne.

Fordele ved MEMORY tabeller

Data gemmes og hentes direkte fra hukommelsen – der er næsten intet drev trafik. Læsning går derfor meget hurtig!
Du kan oprette MEMORY tabeller på en almindelig MySQL server (det er ikke nødvendigt at installere MySQL Cluster).

Ulemper ved MEMORY tabeller

Dataene er ikke vedholdende. Data vil forsvinde ved genstart af server eller nedbrud på MySQL servicen.
Der skal være plads til dataene i hukommelsen, ellers vil den benytte et drev som virtuel hukommelse, hvilket er langsommere.
Tilbyder kun locking af hele tabellen, og ikke en enkelt række. Det betyder, at ved hvert INSERT/UPDATE/DELETE af data i tabellen, skal andre processer vente på, at den pågældende process er færdig med at opdatere, inden der er adgang til tabellen igen. Dette tilfører en del ventetid, hvis der er flere processer om samme tabel.
Understøtter ikke MySQL transactions.

Use-cases

De mange ulemper gør situationerne, hvor MEMORY tabellerne kan bruges, temmeligt begrænset.

I følgende tilfælde vil MEMORY tabeller være ubrugelige:

Din tabel indeholder meget data.
Din server har meget lidt hukommelse tilgængeligt.
Du både skriver (INSERT/UPDATE/DELETE) og læser (SELECT) meget fra tabellen.
Flere processer læser og skriver engang imellem lidt til tabellen.
Dataene er vigtige, og må ikke gå tabt ved genstart af MySQL.

Tilbage er følgende egnede use-cases:

Tabel med data, der nemt kan genopbygges (fx statistik eller cache data)
Tabel med data, der skal bruges som en mellemregning i en analyse, og hvor det kun er det endelige resultat, der er vigtigt.

Selvom use-cases og fordele er få, vil der være udtrykkelige hastighedsfordele ved at benytte MEMORY tabeller i de få situationer, hvor de faktisk kan bruges.

Indexes

MEMORY tabeller understøtter både B-Tree og Hash indexes.

Hash indexes er meget hurtige ved eksakt sammenligning (fx WHERE columnA = ‘ABC’), men understøtter ikke range søgninger (fx WHERE columnA LIKE ‘AB%’)

B-Tree understøtter ranges, men er langsommere.

MEMORY tabeller bruger som standard Hash indexes. Ønsker du at benytte B-Tree, skal du angive dette, når du opretter tabellen med CREATE TABLE.

InnoDB er populært og godt alternativ

På grund af de begrænsede use-cases, vælger mange i stedet at benytte en InnoDB database med stor buffer pool eller et MySQL Cluster, der også giver mulighed for skalering over flere servere.

InnoDB følger rigtigt godt med på hastigheden (se nedenstående afsnit “Benchmarks”) i forhold til MEMORY tabeller på grund af deres buffer pool, men tilbyder derudover locking på enkelte rækker i stedet for hele tabellen, vedholdende data og understøtter transactions.

Rammer du ikke en MEMORY use-case meget klart, vil du få færrest problemer ud af at vælge InnoDB i stedet.

Benchmarks – MEMORY versus InnoDB

Værktøj

Det oplagte valg af værktøj for benchmarking ville være det etablerede sysbench. Under indledende tests viser det dog grundlæggende fejl i udførelsen af MEMORY benchmarks på store mængder testdata; tests der kører for evigt etc. Fejl i forbindelse med MEMORY benchmark i sysbench bekræftes af flere tredjemænd på fora.

For at kunne bekræfte/afkræfte resultaterne af sysbench har jeg udviklet et PHP benchmark værktøj, der ved hjælp af simple MySQL forespørgsler med MySQLi driveren, kan køre en række forskellige typer af tests på InnoDB og MEMORY tabeltyperne.

Værktøjet er tilgængeligt på https://github.com/KristianI/innodb-memory-benchmarks og licenseret under MIT.

Efter fuldført benchmarking kan det bekræftes, at sysbench udviste utilregnelige resultater for MEMORY tabellen.

I det følgende er resultaterne fra innodb-memory-benchmarks værktøjet benyttet. Benchmarks er kørt på min personlige bærbare computer på en MySQL database optimeret i forhold til buffer størrelse, maksimum tabelstørrelse med mere.

Benchmarks kan køres på en hvilken som helst server, der har PHP og MySQL installeret, hvis du ønsker at dobbelttjekke nogle af testene i dit eget miljø.

Værktøjet tester ikke søgning i tabellerne, og tester derfor ikke indeksering. Alene simpel udtræk og opdatering af data testes.

innodb-memory-benchmark-example

Benchmark 1: Kun læsning

For læsning er der kørt 2 slags teste:

Test med en tabel med 10.000 rækker data.
Test med en tabel med 100.000 rækker data.

Der er foretaget læsning af henholdsvis 1 række og alle rækker i tilfældigt skiftende rækkefølge.

Test 1A

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
10.000	10.000	InnoDB	231 sekunder
10.000	10.000	MEMORY	138 sekunder	40 % hurtigere

Test 1B

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
100.000	1.000	InnoDB	245 sekunder
100.000	1.000	MEMORY	138 sekunder	44 % hurtigere

Benchmark 2: Læsning og skrivning, 1 process

For læsning og skrivning er der kørt 2 slags teste:

Test med en tabel med 10.000 rækker data.
Test med en tabel med 100.000 rækker data.

Der er tilfældigt skiftet imellem læsning og skrivning igennem alle iterationer.

Når der tilfældigt er valgt læsning, er der tilfældigt skiftet imellem læsning af henholdsvis 1 række og alle rækker.

Når der tilfældigt er valgt skrivning, er der tilfældigt skiftet imellem UPDATE og INSERT.

Kun 1 process har tilgået databasen under benchmarking.

Test 2A

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
10.000	10.000	InnoDB	180 sekunder
10.000	10.000	MEMORY	93 sekunder	48 % hurtigere

Test 2B

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
100.000	1.000	InnoDB	197 sekunder
100.000	1.000	MEMORY	71 sekunder	64 % hurtigere

Benchmark 3: Læsning og skrivning, flere processer

Benchmark 3 udføres som benchmark 2 pånær, at der vil være tilsat en forstyrrende proces, som har til formål at tilgå databasen samtidig med benchmarken.

Den forstyrrende proces vil påvirke locking af tabel og rækker, som er en af de store forskelle mellem InnoDB og MEMORY, og som oftest forekommer i produktionsmiljøer med mange brugere.

Test 3A

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
10.000	10.000	InnoDB	248 sekunder
10.000	10.000	MEMORY	133 sekunder	46 % hurtigere

Test 3B

Rækker i tabel	Gentagelser	Tabeltype	Tid brugt	Resultat
100.000	1.000	InnoDB	236 sekunder
100.000	1.000	MEMORY	107 sekunder	55 % hurtigere

Resultat af benchmarks

Benchmarkene viser, at MEMORY tabellerne vinder i alle tests.

Overraskende var det, at testene med både læsning og skrivning var hurtigere end testene, hvor der kun blev læst data.

MEMORY tabellerne tog i alle testene kun cirka halvdelen af den tid, som de tilsvarende InnoDB tests brugte, selv under påvirkning af en ekstern databaseproces, der havde til formål at påvirke locking af tabellen/rækkerne.

Under påvirkning af en forstyrrende proces blev føringen til MEMORY tabellen reduceret en smule. Ved påvirkning af flere kørende processer vil der kunne opstå yderligere reduktion. Det kan ikke udelukkes, at mange INSERT/UPDATE/DELETE vil kunne påvirke locking i så høj grad, at MEMORY ikke længere er gunstig.

Så hvad vil du anbefale, Kristian?

De udførte benchmarks viser en meget stor hastighedsgevinst ved at benytte MEMORY tabeller.

Det er klart værd at undersøge, om man har nogle oplagte use-cases for MEMORY tabellen. Om ikke andet bør det tænkes ind i fremtidige projekter.

01. JAN 1970

LinkedIn - Email