{"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\/sv\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimering av databass\u00f6kningar med B2B-tj\u00e4nst f\u00f6r byggare som exempel","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hur kan man \u00f6ka antalet databasfr\u00e5gor med tio g\u00e5nger utan att byta till en kraftfullare server och \u00e4nd\u00e5 beh\u00e5lla systemets funktionalitet? Jag ska ber\u00e4tta hur vi k\u00e4mpade mot minskad prestanda i v\u00e5r databas, hur vi optimerade SQL-fr\u00e5gor f\u00f6r att betj\u00e4na s\u00e5 m\u00e5nga anv\u00e4ndare som m\u00f6jligt utan att \u00f6ka kostnaderna f\u00f6r ber\u00e4kningsresurser.
\n<\/a><\/noindex>
\nJag skapar en tj\u00e4nst f\u00f6r hantering av aff\u00e4rsprocesser f\u00f6r byggf\u00f6retag. Cirka 3 000 f\u00f6retag arbetar med oss. Mer \u00e4n 10 000 personer arbetar varje dag med v\u00e5rt system i 4-10 timmar. Det l\u00f6ser olika uppgifter inom planering, avisering, varning, validering\u2026 Vi anv\u00e4nder PostgreSQL 9.6. I databasen har vi cirka 300 tabeller och varje dag f\u00e5r vi upp till 200 miljoner fr\u00e5gor (10 000 olika). I genomsnitt har vi 3-4 000 fr\u00e5gor per sekund, under de mest aktiva stunderna \u00f6ver 10 000 fr\u00e5gor per sekund. Majoriteten av fr\u00e5gorna \u00e4r OLAP. Till\u00e4gg, modifieringar och borttagningar \u00e4r mycket f\u00e4rre, s\u00e5 OLTP-belastningen \u00e4r relativt liten. Jag gav dessa siffror s\u00e5 att ni kan bed\u00f6ma omfattningen av v\u00e5rt projekt och f\u00f6rst\u00e5 hur v\u00e5r erfarenhet kan vara anv\u00e4ndbar f\u00f6r er.<\/p>\n

F\u00f6rsta bilden. Lyrisk<\/h3>\n

\nN\u00e4r vi b\u00f6rjade utveckla, t\u00e4nkte vi inte s\u00e4rskilt mycket p\u00e5 vilken belastning som skulle ligga p\u00e5 databasen och vad vi skulle g\u00f6ra om servern slutade fungera. Vid utformningen av databasen f\u00f6ljde vi allm\u00e4nna rekommendationer och f\u00f6rs\u00f6kte undvika att skjuta oss sj\u00e4lva i foten, men l\u00e4ngre \u00e4n s\u00e5 gick vi inte i r\u00e5d som \"anv\u00e4nd inte m\u00f6nstret Entity Attribute Values<\/a><\/noindex> Vi gick inte. Vi designade utifr\u00e5n normaliseringsprinciperna f\u00f6r att undvika dataduplicering och brydde oss inte om att p\u00e5skynda vissa fr\u00e5gor. N\u00e4r de f\u00f6rsta anv\u00e4ndarna kom, st\u00f6tte vi p\u00e5 prestandaproblem. Som vanligt var vi helt of\u00f6rberedda f\u00f6r detta. De f\u00f6rsta problemen var enkla. I allm\u00e4nhet l\u00f6stes allt genom att l\u00e4gga till en ny index. Men det kom en tid d\u00e5 enkla lappar slutade fungera. N\u00e4r vi ins\u00e5g att vi saknade erfarenhet och att det blev allt sv\u00e5rare att f\u00f6rst\u00e5 vad problemen berodde p\u00e5, anlitade vi specialister som hj\u00e4lpte oss att korrekt konfigurera servern, ansluta \u00f6vervakning, och visade oss vart vi skulle titta f\u00f6r att f\u00e5 statistik.<\/a><\/noindex>.<\/p>\n

Andra bilden. Statistik<\/h3>\n

\nVi har ungef\u00e4r 10 000 olika f\u00f6rfr\u00e5gningar som k\u00f6rs p\u00e5 v\u00e5r databas varje dag. Av dessa 10 000 finns det stora f\u00f6rfr\u00e5gningar som k\u00f6rs 2-3 miljoner g\u00e5nger med en genomsnittlig k\u00f6rningstid p\u00e5 0,1-0,3 ms, och s\u00e5 finns det f\u00f6rfr\u00e5gningar med en genomsnittlig k\u00f6rningstid p\u00e5 30 sekunder som anropas 100 g\u00e5nger om dagen.<\/p>\n

Det var inte m\u00f6jligt att optimera alla 10 000 f\u00f6rfr\u00e5gningar, s\u00e5 vi beslutade att ta reda p\u00e5 hur vi skulle rikta v\u00e5ra insatser f\u00f6r att korrekt \u00f6ka databasens prestanda. Efter flera iterationer b\u00f6rjade vi dela upp f\u00f6rfr\u00e5gningarna i typer.<\/p>\n

TOP-f\u00f6rfr\u00e5gningar<\/h4>\n

\nDessa \u00e4r de tyngsta f\u00f6rfr\u00e5gningarna som tar mest tid (total tid). Det \u00e4r f\u00f6rfr\u00e5gningar som antingen anropas mycket ofta eller som tar mycket l\u00e5ng tid att genomf\u00f6ra (l\u00e5nga och frekventa f\u00f6rfr\u00e5gningar har redan optimerats under de f\u00f6rsta iterationerna i jakten p\u00e5 hastighet). Totalt spenderar servern mest tid p\u00e5 att exekvera dem. Det \u00e4r ocks\u00e5 viktigt att separera toppf\u00f6rfr\u00e5gningarna efter total exekveringstid och separat efter IO-tid. S\u00e4tten att optimera s\u00e5dana f\u00f6rfr\u00e5gningar \u00e4r n\u00e5got olika.<\/p>\n

Det \u00e4r vanlig praxis f\u00f6r alla f\u00f6retag att arbeta med TOP-f\u00f6rfr\u00e5gningarna. De \u00e4r f\u00e5, och \u00e4ven optimering av en enda f\u00f6rfr\u00e5gan kan frig\u00f6ra 5-10% av resurserna. Men ju mer projektet \"v\u00e4xer\", desto mer okomplicerad blir optimeringen av TOP-f\u00f6rfr\u00e5gningarna. Alla enkla metoder \u00e4r redan utf\u00f6rda, och den mest \"tunga\" f\u00f6rfr\u00e5gan tar \"bara\" 3-5% av resurserna. Om TOP-f\u00f6rfr\u00e5gningarna totalt tar mindre \u00e4n 30-40% av tiden, har du f\u00f6rmodligen redan anstr\u00e4ngt dig f\u00f6r att de ska fungera snabbt, och det \u00e4r dags att g\u00e5 vidare till optimering av f\u00f6rfr\u00e5gningar i n\u00e4sta grupp.
\nFr\u00e5gan kvarst\u00e5r: hur m\u00e5nga toppf\u00f6rfr\u00e5gningar ska inkluderas i den h\u00e4r gruppen? Jag brukar ta minst 10, men inte fler \u00e4n 20. Jag f\u00f6rs\u00f6ker se till att tiden f\u00f6r den f\u00f6rsta och sista i TOP-gruppen inte skiljer sig mer \u00e4n 10 g\u00e5nger. S\u00e5 om k\u00f6rningstiden f\u00f6r f\u00f6rfr\u00e5gningarna faller kraftigt fr\u00e5n plats 1 till 10, s\u00e5 tar jag TOP-10; om nedg\u00e5ngen \u00e4r mer gradvis, \u00f6kar jag gruppens storlek till 15 eller 20.
\n\"Optimering
\n<\/p>\n

Medel (medium)<\/h4>\n

\nDetta \u00e4r alla f\u00f6rfr\u00e5gningar som kommer direkt efter TOP, med undantag f\u00f6r de senaste 5-10%. Vanligtvis ligger m\u00f6jligheten att avsev\u00e4rt \u00f6ka serverns prestanda i optimeringen av just dessa f\u00f6rfr\u00e5gningar. Dessa f\u00f6rfr\u00e5gningar kan utg\u00f6ra upp till 80%. Men \u00e4ven om deras andel har \u00f6verskridit 50%, betyder det att det \u00e4r dags att titta noggrannare p\u00e5 dem.<\/p>\n

Svansen (tail)<\/h4>\n

\nSom n\u00e4mnts, dessa f\u00f6rfr\u00e5gningar ligger i slutet och tar upp 5-10 % av tiden. Man kan gl\u00f6mma dem, endast om man inte anv\u00e4nder automatiska analysverktyg f\u00f6r f\u00f6rfr\u00e5gningar, d\u00e5 kan deras optimering ocks\u00e5 g\u00f6ras billigt.<\/p>\n

Hur bed\u00f6mer man varje grupp?<\/p>\n

Jag anv\u00e4nder en SQL-fr\u00e5ga som hj\u00e4lper till att g\u00f6ra en s\u00e5dan bed\u00f6mning f\u00f6r PostgreSQL (jag \u00e4r s\u00e4ker p\u00e5 att man kan skriva en liknande fr\u00e5ga f\u00f6r m\u00e5nga andra databashanteringssystem).<\/p>\n

SQL-fr\u00e5ga f\u00f6r att bed\u00f6ma storleken 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) AS sum_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
Fr\u00e5geresultatet best\u00e5r av tre kolumner, var och en inneh\u00e5ller procentandelen av tiden som g\u00e5r \u00e5t till att behandla f\u00f6rfr\u00e5gningar fr\u00e5n denna grupp. Inom fr\u00e5gan finns tv\u00e5 siffror (i mitt fall \u00e4r det 20 och 800) som skiljer f\u00f6rfr\u00e5gningar fr\u00e5n en grupp till en annan.<\/p>\n
S\u00e5 h\u00e4r relaterar andelarna av f\u00f6rfr\u00e5gningar vid b\u00f6rjan av optimeringsarbetet och nu.<\/p>\n
\"Optimering
\n
\nDiagrammet visar att andelen TOP-f\u00f6rfr\u00e5gningar har minskat kraftigt, medan \"medel\" har \u00f6kat.
\nTill en b\u00f6rjan inkluderade TOP-f\u00f6rfr\u00e5gningar uppenbara misstag. Med tiden f\u00f6rsvann barnsjukdomarna, andelen TOP-f\u00f6rfr\u00e5gningar minskade, och det kr\u00e4vdes allt mer anstr\u00e4ngning f\u00f6r att snabba upp de tunga f\u00f6rfr\u00e5gningarna. <\/p>\n
F\u00f6r att f\u00e5 texten av f\u00f6rfr\u00e5gningarna anv\u00e4nder vi f\u00f6ljande fr\u00e5ga<\/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
H\u00e4r \u00e4r en lista \u00f6ver de mest anv\u00e4nda metoderna som hj\u00e4lpte oss att snabba upp TOP-f\u00f6rfr\u00e5gningar:<\/p>\n