{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/da\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Database queryoptimering med et B2B-servicefokus for byggere","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hvordan kan man vokse med 10 gange antallet af foresp\u00f8rgsler til databasen uden at flytte til en mere kraftfuld server og samtidig opretholde systemets funktionalitet? Jeg vil fort\u00e6lle, hvordan vi k\u00e6mpede med faldet i ydeevnen for vores database, hvordan vi optimerede SQL-foresp\u00f8rgsler for at betjene s\u00e5 mange brugere som muligt uden at \u00f8ge omkostningerne til beregningsressourcer.
\n<\/a><\/noindex>
\nJeg laver en service til at styre forretningsprocesser i byggevirksomheder. Vi arbejder med omkring 3.000 virksomheder. Mere end 10.000 mennesker arbejder hver dag med vores system i 4-10 timer. Det l\u00f8ser forskellige planl\u00e6gnings-, varslings-, advarsels- og valideringsopgaver... Vi bruger PostgreSQL 9.6. I databasen har vi omkring 300 tabeller, og hver dag modtager vi op til 200 millioner foresp\u00f8rgsler (10.000 forskellige). I gennemsnit har vi 3-4.000 foresp\u00f8rgsler pr. sekund, og p\u00e5 de mest aktive tidspunkter over 10.000 foresp\u00f8rgsler pr. sekund. St\u00f8rstedelen af foresp\u00f8rgslerne er OLAP. Tilf\u00f8jelser, modificeringer og sletninger er meget f\u00e6rre, dvs. OLTP-belastningen er relativt lille. Alle disse tal n\u00e6vnte jeg for at give dig et indtryk af omfanget af vores projekt og forst\u00e5, hvor nyttig vores erfaring kan v\u00e6re for dig.<\/p>\n

Billede \u00e9t. Lyrisk<\/h3>\n

\nDa vi begyndte udviklingen, t\u00e6nkte vi ikke meget over, hvor meget belastning der ville v\u00e6re p\u00e5 databasen, og hvad vi ville g\u00f8re, hvis serveren ikke l\u00e6ngere kunne klare det. Ved design af databasen fulgte vi generelle anbefalinger og pr\u00f8vede ikke at tr\u00e6de os selv over t\u00e6erne, men l\u00e6ngere end de generelle r\u00e5d som \u2018brug ikke m\u00f8nsteret Entity Attribute Values<\/a><\/noindex> kom vi ikke. Vi designede baseret p\u00e5 normaliseringsprincipper og undgik dataduplicering, og vi bekymrede os ikke om at fremskynde bestemte foresp\u00f8rgsler. S\u00e5 snart de f\u00f8rste brugere kom, st\u00f8dte vi p\u00e5 et ytelsesproblem. Som vanligt var vi helt uforberedte p\u00e5 dette. De f\u00f8rste problemer var enkle. Som regel blev det l\u00f8st ved at tilf\u00f8je et nyt indeks. Men der kom et tidspunkt, hvor de enkle l\u00f8sninger ikke l\u00e6ngere virkede. Inds\u00e5, at vi ikke havde nok erfaring, og at det blev sv\u00e6rere at forst\u00e5, hvad \u00e5rsagen til problemerne var, ansatte vi specialister, der hjalp os med at konfigurere serveren korrekt, tilslutte overv\u00e5gningen, og viste os, hvor vi skulle kigge for at f\u00e5 statistik<\/a><\/noindex>.<\/p>\n

Billede to. Statistisk<\/h3>\n

\nS\u00e5 vi har omkring 10.000 forskellige foresp\u00f8rgsler, der k\u00f8rer p\u00e5 vores database hver dag. Blandt disse 10.000 er der monstre, der udf\u00f8res 2-3 millioner gange med en gennemsnitlig udf\u00f8relsestid p\u00e5 0,1-0,3 ms, og der er foresp\u00f8rgsler med en gennemsnitlig udf\u00f8relsestid p\u00e5 30 sekunder, der kaldes 100 gange om dagen.<\/p>\n

Det var ikke muligt at optimere alle 10.000 foresp\u00f8rgsler, s\u00e5 vi besluttede at finde ud af, hvor vi skulle rette vores indsats for at \u00f8ge databaseydelsen korrekt. Efter flere iterationer begyndte vi at opdele foresp\u00f8rgslerne i typer.<\/p>\n

TOP foresp\u00f8rgsler<\/h4>\n

\nDette er de mest ressourcekr\u00e6vende foresp\u00f8rgsler, der bruger mest tid (total tid). Det er foresp\u00f8rgsler, der enten kaldes meget ofte, eller foresp\u00f8rgsler, der tager meget lang tid at udf\u00f8re (lange og hyppige foresp\u00f8rgsler blev optimeret allerede i de f\u00f8rste iterationer i kampen for hastigheden). I sidste ende bruger serveren mest tid p\u00e5 deres udf\u00f8relse. Det er vigtigt at adskille topforesp\u00f8rgsler efter samlet udf\u00f8relsestid og separat efter IO-tid. Metoderne til optimering af s\u00e5danne foresp\u00f8rgsler er lidt forskellige.<\/p>\n

Det er almindelig praksis blandt alle virksomheder at arbejde med TOP foresp\u00f8rgsler. Der er ikke mange af dem, og optimering af selv \u00e9n foresp\u00f8rgsel kan spare 5-10% af ressourcerne. Men efterh\u00e5nden som projektet \"modnes\", bliver optimeringen af TOP foresp\u00f8rgsler en mere og mere ikke-trivial opgave. Alle de enkle metoder er allerede blevet anvendt, og den \"tungere\" foresp\u00f8rgsel bruger \"kun\" 3-5% af ressourcerne. Hvis TOP foresp\u00f8rgsler samlet set tager mindre end 30-40% af tiden, har du sandsynligvis allerede gjort en indsats for at f\u00e5 dem til at k\u00f8re hurtigt, og det er tid til at g\u00e5 videre til optimering af foresp\u00f8rgslerne i den n\u00e6ste gruppe.
\nDet er tilbage at besvare sp\u00f8rgsm\u00e5let om, hvor mange topforesp\u00f8rgsler der skal inkluderes i denne gruppe. Jeg tager normalt ikke mindre end 10, men ikke mere end 20. Jeg pr\u00f8ver at sikre, at tiden for den f\u00f8rste og den sidste i TOP-gruppen ikke adskiller sig mere end 10 gange. S\u00e5 hvis udf\u00f8relsestiden for foresp\u00f8rgslerne falder pludseligt fra 1. plads til 10. plads, tager jeg TOP-10; hvis faldet er mere gradvist, \u00f8ger jeg gruppens st\u00f8rrelse til 15 eller 20.
\n\"Database
\n<\/p>\n

Mellemforskelle (medium)<\/h4>\n

\nDette er alle foresp\u00f8rgsler, der f\u00f8lger lige efter TOP, med undtagelse af de sidste 5-10%. Normalt er det i optimeringen af disse foresp\u00f8rgsler, at muligheden for alvorligt at \u00f8ge serverens ydeevne ligger. Disse foresp\u00f8rgsler kan \"veje op til 80%.\" Men selv hvis deres andel overstiger 50%, er det tid til at se n\u00e6rmere p\u00e5 dem.<\/p>\n

Haleforf\u00f8gelser (tail)<\/h4>\n

\nSom n\u00e6vnt g\u00e5r disse foresp\u00f8rgsler til slutningen, og de tager 5-10% af tiden. Man kan glemme dem, medmindre man bruger automatiske analysesoftware til foresp\u00f8rgsler, s\u00e5 kan optimeringen ogs\u00e5 v\u00e6re billig.<\/p>\n

Hvordan vurderer man hver gruppe?<\/p>\n

Jeg bruger en SQL-foresp\u00f8rgsel, som hj\u00e6lper med at lave s\u00e5dan en vurdering for PostgreSQL (jeg er sikker p\u00e5, at man kan skrive en lignende foresp\u00f8rgsel for mange andre databaser).<\/p>\n

SQL-foresp\u00f8rgsel til vurdering af st\u00f8rrelsen p\u00e5 TOP-MEDIUM-TAIL grupper<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail)\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
Resultatet af foresp\u00f8rgslen - tre kolonner, hver indeholdende procentdelen af tiden, der bruges p\u00e5 at behandle foresp\u00f8rgsler fra denne gruppe. Inden i foresp\u00f8rgslen er der to tal (i mit tilf\u00e6lde 20 og 800), som adskiller foresp\u00f8rgslerne fra den ene gruppe til den anden.<\/p>\n
Her er forholdet mellem andelene af foresp\u00f8rgsler ved begyndelsen af optimeringsarbejdet og nu.<\/p>\n
\"Database
\n
\nDiagrammet viser, at andelen af TOP-foresp\u00f8rgsler er faldet kraftigt, mens 'mellemstore' foresp\u00f8rgsler er steget.
\nI starten var der \u00e5benlyse fejl i TOP-foresp\u00f8rgslerne. Over tid forsvandt de barne sygdomme, andelen af TOP-foresp\u00f8rgsler faldt, og der skulle l\u00e6gges mere og mere arbejde i at accelerere de tunge foresp\u00f8rgsler. <\/p>\n
For at f\u00e5 teksten af foresp\u00f8rgslerne bruger vi en s\u00e5dan foresp\u00f8rgsel<\/b><\/p>\n
SELECT * FROM (\n  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Her er listen over de mest brugte metoder, der har hjulpet os med at accelerere TOP-foresp\u00f8rgslerne:<\/p>\n