{"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\/nl\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimalisatie van databaseverzoeken aan de hand van een voorbeeld van een B2B-service voor bouwbedrijven.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hoe kun je het aantal databaseverzoeken met 10 keer vergroten zonder over te stappen naar een krachtigere server en de werking van het systeem te behouden? Ik zal vertellen hoe we de daling van de prestaties van onze database hebben bestreden, hoe we SQL-query's hebben geoptimaliseerd om zoveel mogelijk gebruikers te bedienen zonder de kosten voor rekenkracht te verhogen.
\n<\/a><\/noindex>
\nIk ontwikkel een service voor het beheren van bedrijfsprocessen in bouwbedrijven. Ongeveer 3000 bedrijven werken met ons. Meer dan 10.000 mensen werken elke dag 4-10 uur met ons systeem. Het lost verschillende taken op op het gebied van planning, waarschuwingen, notificaties en validatie\u2026 We maken gebruik van PostgreSQL 9.6. In onze database hebben we ongeveer 300 tabellen en elke dag komen er tot 200 miljoen verzoeken binnen (10.000 verschillende). Gemiddeld hebben we 3-4 duizend verzoeken per seconde, tijdens de meest actieve momenten meer dan 10.000 verzoeken per seconde. Het grootste deel van de verzoeken is OLAP. Toevoegingen, wijzigingen en verwijderingen zijn veel minder, wat betekent dat de OLTP-last relatief klein is. Ik geef deze cijfers om de omvang van ons project te laten zien en te begrijpen hoe nuttig onze ervaring voor jou kan zijn.<\/p>\n

Eerste sc\u00e8ne. Lyrisch<\/h3>\n

\nToen we met de ontwikkeling begonnen, dachten we niet echt na over de last die op de database zou komen en wat we zouden doen als de server het niet meer zou trekken. Bij het ontwerpen van de database volgden we de algemene aanbevelingen en probeerden we niet in onze eigen voet te schieten, maar verder dan algemene adviezen zoals 'gebruik geen patroon' gingen we niet. Entity Attribute Values<\/a><\/noindex> we gingen verder. We ontwierpen volgens de beginselen van normalisatie, waarbij we redundantie in gegevens vermeden, en maakten ons geen zorgen over het versnellen van bepaalde verzoeken. Zodra de eerste gebruikers kwamen, stuitten we op prestatieproblemen. Zoals gewoonlijk waren we helemaal niet voorbereid op deze situatie. De eerste problemen waren eenvoudig. Meestal werd alles opgelost door een nieuwe index toe te voegen. Maar er kwam een moment waarop eenvoudige oplossingen niet meer werkten. Ons realiseren dat we niet genoeg ervaring hadden en dat het steeds moeilijker werd om de oorzaak van de problemen te begrijpen, hebben we specialisten ingehuurd die ons hielpen om de server goed in te stellen, monitoring aan te sluiten en ons lieten zien waar we moesten kijken om statistieken<\/a><\/noindex>.<\/p>\n

Tweede sc\u00e8ne. Statistisch<\/h3>\n

\nWe have about 10,000 different queries executed on our database per day. Among these 10,000, there are monsters that run 2-3 million times with an average execution time of 0.1-0.3 milliseconds, and there are queries with an average execution time of 30 seconds that are called 100 times a day.<\/p>\n

Optimizing all 10,000 queries was not feasible, so we decided to figure out where to focus our efforts to correctly enhance database performance. After several iterations, we started categorizing the queries by type.<\/p>\n

TOP queries<\/h4>\n

\nThese are the heaviest queries that take the most time (total time). These are queries that are either frequently called or queries that take a long time to execute (long and frequent queries have already been optimized in earlier iterations in the speed race). Ultimately, the server spends the most time executing them. It is also important to separate top queries by total execution time and separately by IO time. The optimization methods for such queries vary slightly.<\/p>\n

The common practice among all companies is to work with TOP queries. There are only a few, and optimizing even one query can free up 5-10% of resources. However, as the project 'grows up', optimizing TOP queries becomes an increasingly non-trivial task. All simple approaches have already been implemented, and the most 'heaviest' query consumes 'only' 3-5% of resources. If TOP queries collectively take less than 30-40% of the time, you've probably already made efforts to ensure they run quickly, and it's time to move on to optimizing queries from the next group.
\nThe remaining question is how many top queries to include in this group. I usually take no less than 10 but no more than 20. I try to ensure that the execution time of the first and last query in the TOP group differs by no more than 10 times. That is, if the execution time of the queries drops sharply from 1st place to 10th, I take TOP-10; if the drop is more gradual, I increase the group size to 15 or 20.
\n\"Optimalisatie
\n<\/p>\n

Middlers (medium)<\/h4>\n

\nThese are all the queries that immediately follow TOP, except for the last 5-10%. Usually, the optimization of these queries holds the potential to significantly boost server performance. These queries can account for up to 80%. But even if their share exceeds 50%, it means it's time to take a closer look at them.<\/p>\n

Tail (tail)<\/h4>\n

\nZoals eerder vermeld, vinden deze verzoeken aan het einde plaats en kost het 5-10% van de tijd. Je kunt ze vergeten, tenzij je automatische analysetools voor verzoeken gebruikt, dan kan hun optimalisatie ook goedkoop zijn.<\/p>\n

Hoe elke groep te evalueren?<\/p>\n

Ik gebruik een SQL-query die helpt bij het maken van een dergelijke evaluatie voor PostgreSQL (ik weet zeker dat voor vele andere DBMS's een vergelijkbare query kan worden geschreven).<\/p>\n

SQL-query voor het evalueren van de grootte van de TOP-MEDIUM-TAIL groepen.<\/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              THEN tt_percent ELSE 0 END AS time_top,\n         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,\n         CASE WHEN 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
Het resultaat van de query zijn drie kolommen, waarvan elke kolom het percentage tijd bevat dat wordt besteed aan de verwerking van verzoeken uit deze groep. In de query zijn er twee getallen (in mijn geval zijn dat 20 en 800), die de verzoeken van de ene groep van de andere scheiden.<\/p>\n
Zo verhouden de aandelen van de verzoeken zich aan het begin van de optimalisatie en nu.<\/p>\n
\"Optimalisatie
\n
\nUit de diagram blijkt dat het aandeel TOP-verzoeken sterk is afgenomen, terwijl de 'gemiddelden' zijn toegenomen.
\nAanvankelijk waren er duidelijke blunders in de TOP-verzoeken. Na verloop van tijd verdwenen de kinderziekten, het aandeel TOP-verzoeken nam af en het was steeds moeilijker om zware verzoeken te versnellen. <\/p>\n
Om de teksten van de verzoeken te krijgen, gebruiken we de volgende query.<\/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 -- TOP\n-- rn > 20 AND rn <= 800 -- MEDIUM\n-- rn > 800  -- TAIL\n<\/code><\/pre>\n
Hier is een lijst van de meest gebruikte technieken die ons hielpen om de TOP-verzoeken te versnellen:<\/p>\n