Как вырасти в 10 раз под количеству запросов к БД не переезжая на более производительный сервер и сохранить работоспособность системы? Я расскажу, как мы боролись с падением производительности нашей базы данных, как оптимизировали SQL запросы, чтобы обслуживать как можно больше пользователей и не повышать расходы на вычислительные ресурсы.
Я делаю сервис для управления бизнес процессами в строительных компаниях. С нами работает около 3 тысяч компаний. Более 10 тысяч человек каждый день работают с нашей системой по 4-10 часов. Она решает разные задачи планирования, оповещения, предупреждения, валидации… Мы используем PostgreSQL 9.6. В базе данных у нас около 300 таблиц и каждые сутки в нее поступает до 200 млн запросов (10 тысяч различных). В среднем у нас 3-4 тысяч запросов в секунду, в самые активные моменты более 10 тысяч запросов в секунду. Большая часть запросов — OLAP. Добавлений, модификаций и удалений намного меньше, то есть OLTP нагрузка относительно небольшая. Все эти цифры я привел, чтобы вы могли оценить масштаб нашего проекта и понять насколько наш опыт может быть полезен для вас.
Картина первая. Лирическая
Когда мы начинали разработку, то особо не задумывались о том, какая нагрузка ляжет на БД и что мы будем делать если сервер перестанет вытягивать. При проектировании БД мы следовали общим рекомендациям и старались не стрелять себе в ногу, но дальше общих советов вроде “не используйте паттерн мы не заходили. Проектировали исходя из принципов нормализации избегая избыточности данных и не заботились об ускорения тех или иных запросов. Как только пришли первые пользователи мы столкнулись с проблемой производительности. Как обычно мы оказались абсолютно не готовы к этому. Первые проблемы оказались простыми. Как правило все решалось добавлением нового индекса. Но наступил момент когда простые заплатки перестали работать. Осознав, что опыта не хватает и нам все сложнее понять в чем причина проблем, мы наняли специалистов, которые помогли нам правильно настроить сервер, подключить мониторинг, показали куда смотреть, чтобы получить .
Картина вторая. Статистическая
Итак у нас есть около 10 тысяч различных запросов, которые выполняются на нашей БД за сутки. Из этих 10 тысяч есть монстры, которые выполняются по 2-3 млн раз со средним временем выполнения 0.1-0.3 мс и есть запросы со средним временем выполнения 30 секунд, которые вызываются 100 раз в сутки.
Оптимизировать все 10 тысяч запросов не представлялось возможным, поэтому мы решили разобраться с тем, куда направлять усилия, чтобы повышать производительность БД правильно. После нескольких итераций мы стали делить запросы на типы.
TOP запросы
Это самые тяжелые запросы, которые занимают больше всего времени (total time). Это запросы, которые либо очень часто вызываются либо запросы, которые очень долго выполняются (долгие и частые запросы были оптимизированы еще на первых итерациях борьбы за скорость). В итоге суммарно на их исполнение сервер тратит больше всего времени. Причем важно отделять топ запросы по общему времени исполнения и отдельно по IO time. Способы оптимизации таких запросов немного разные.
Обычная практика всех компаний- работать с TOP запросами. Их немного, оптимизация даже одного запроса может освободить 5-10% ресурсов. Однако, по мере “взросления” проекта оптимизация TOP запросов становится все более нетривиальной задачей. Все простые способы уже отработаны, да и самый “тяжелый” запрос отнимает “всего” 3-5% ресурсов. Если TOP запросы в сумме занимают менее 30-40% времени, то скорее всего вы уже приложили усилия, чтобы они работали быстро и пришла пора переходить к оптимизации запросов из следующей группы.
Остается ответить на вопрос сколько верхних запросов включить в эту группу. Я обычно беру не меньше 10, но не больше 20. Стараюсь, чтобы время первого и последнего в TOP группе отличалось не более чем в 10 раз. То есть если время исполнения запросов резко падает с 1 места до 10, то беру TOP-10, если падение более плавное, то увеличиваю размер группы до 15 или 20.
Середняки (medium)
Это все запросы, которые идут сразу за TOP, за исключением последних 5-10%. Обычно в оптимизации именно этих запросов кроется возможность сильно поднять производительность сервера. Эти запросы могут “весить” до 80%. Но даже если их доля перевалила за 50%, значит пора на них взглянуть более внимательно.
Хвост (tail)
Siç u tha, këto kërkesa vijnë në fund dhe ato kërkojnë 5-10% të kohës. Mund të harrohen për sa kohë që nuk po përdorni mjete automatike për analizën e kërkesave, ndryshe optimizimi i tyre mund të jetë gjithashtu ekonomik.
Si ta vlerësojmë çdo grup?
Unë përdor një kërkesë SQL që ndihmon në praktikimin e kësaj vlerësimi për PostgreSQL (jam i sigurt se për shumë sisteme të tjera DBMS mund të shkruhet një kërkesë e ngjashme)
Kërkesa SQL për të vlerësuar madhësinë e grupeve TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail)
FROM
(
SELECT CASE WHEN rn 20 AND rn 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Rezultati i kërkesës - tre kolona, secila përmban përqindjen e kohës që harxhohet për përpunimin e kërkesave nga ky grup. Brenda kërkesës ka dy numra (në rastin tim janë 20 dhe 800), të cilët ndajnë kërkesat e një grupi nga një tjetër.
Kështu, përqindjet e kërkesave në fillim të punës për optimizimin dhe tani janë lidhur.
Nga diagrami shihet se pjesa e kërkesave TOP ka rënë ndjeshëm, ndërsa janë rritur "mesatarët".
Fillimisht, kërkesat TOP përmbanin gabime të dukshme. Me kalimin e kohës, sëmundjet e fëmijërisë zhduken, pjesa e kërkesave TOP u pakësua, dhe duhej të bënim më shumë përpjekje për të përshpejtuar kërkesat e rënda.
Për të marrë tekstin e kërkesave përdorim një kërkesë të tillë
SELECT * FROM (
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
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn 20 AND rn 800 -- TAIL
Ja lista e praktikave më të përdorura, të cilat na ndihmuan të përshpejtonim kërkesat TOP:
- Rindizajnimi i sistemit, për shembull ripunimi i logjikës së njoftimeve në një mesazh broker në vend të kërkesave periodike në DB
- Shtimi ose ndryshimi i indekseve
- Rishkrimi i kërkesave ORM në SQL të pastër
- Rishkrimi i logjikës së ngarkimit të të dhënave 'lazy'
- Keshimi përmes denormalizimit të të dhënave. Për shembull, ne kemi një lidhje midis tabelave Dërgesa -> Fatura -> Kërkesë -> Aplikim. Pra, çdo dërgesë është e lidhur me aplikimin përmes tabelave të tjera. Për të mos lidhur të gjitha tabelat në çdo kërkesë, ne e kopjuam lidhjen me aplikimin në tabelën Dërgesa.
- Keshimi i tabelave statike me tabela referencash dhe tabelave që ndryshojnë rrallë në memorjen e programit.
Ndonjëherë ndryshimet kërkonin një rindizajn të konsiderueshëm, por jepnin 5-10% shkarkim të sistemit dhe ishin të justifikuara. Me kalimin e kohës, përfitimet bëheshin gjithnjë e më të vogla, dhe rindizajni kërkonte gjithnjë e më shumë serioz.
Atëherë ne i kushtuam vëmendje grupit të dytë të kërkesave, grupit të mesatarëve. Në atë grup kishte shumë më tepër kërkesa dhe dukej se analiza e gjithë grupit do të kërkonte shumë kohë. Megjithatë, shumica e kërkesave ishin shumë të thjeshta për t'u optimizuar, dhe shumë probleme përsëriteshin dhjetëra herë në variante të ndryshme. Ja disa shembuj të optimizimeve tipike, të cilat ne i kemi aplikuar në dhjetëra kërkesa të ngjashme dhe çdo grup i kërkesave të optimizuara shkarkonte DB-në me 3-5%.
- Në vend të kontrollit të pranisë së regjistrimeve me COUNT dhe skanimin e plotë të tabelës, filluam të përdornim EXISTS
- U shpëtuam nga DISTINCT (nuk ka një recetë të përgjithshme, por ndonjëherë mund të hiqet lehtësisht duke përshpejtuar kërkesën 10-100 herë).
Për shembull, në vend të një kërkese për të nxjerrë të gjithë shoferët nga një tabelë e madhe dërgesash (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDbënë një kërkesë për një tabelë të krahasueshme të PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Duket sikur kemi përdorur një nënkërkesë të lidhur, por ajo jep një përshpejtim më shumë se 10 herë.
- Në shumë raste, ne u shkëputëm nga COUNT dhe
- në vend të
UPPER(s) LIKE JOHN%përdorim
s ILIKE "John%"
Çdo kërkesë specifike ishte e mundur ta përshpejtonim ndonjëherë në 3-1000 herë. Pavarësisht nga treguesit e mahnitshëm, në fillim na dukeshin se nuk kishte kuptim të optimizohej një kërkesë që ekzekutohej për 10 ms, e cila ishte në të tretat më të rënda të kërkesave dhe në përgjithësi, zënien e kohës së ngarkesës në DB e kishte në përqindjet e vogla. Por, duke aplikuar të njëjtën recetë për një grup kërkesash të ngjashme, ne e fitonim disa përqindje. Për të mos humbur kohë në shikimin manual të të gjitha kërkesave qindra, ne shkruem disa skripta të thjeshta, të cilat me ndihmën e shprehjeve të rregullta gjetën kërkesat e ngjashme. Si rezultat, kërkimi automatizuar i grupeve të kërkesave na lejoj përmirësime më të mëdha në performancën tonë, duke shpenzuar një sasi të vogël përpjekjesh.
Si përfundim, ne tashmë po punojmë për tre vjet me të njëjtin harduer. Ngarkesa mesatare ditore është rreth 30%, në kulme arrin deri në 70%. Numri i kërkesave dhe përdoruesve është rritur rreth 10 herë. Dhe gjithçka është falë monitorimit konstant të këtyre grupeve të kërkimeve TOP-MEDIUM. Sa herë që një kërkesë e re shfaqet në grupin TOP, ne e analizojmë atë menjëherë dhe përpiqemi ta përshpejtojmë. Grupi MEDIUM ne e shqyrtojmë çdo javë me ndihmën e skripteve për analizën e kërkesave. Nëse na bien në sy kërkesa të reja, të cilat ne tashmë dimë si t'i optimizojmë, ne i ndryshojmë ato menjëherë. Ndonjëherë zbulojmë kritere të reja optimizimi, të cilat mund të aplikohen menjëherë për disa kërkesa.
Sipas parashikimeve tona, serveri aktual do të përballojë rritjen e numrit të përdoruesve edhe 3-5 herë të tjera. Sidoqoftë, kemi një shtesë në dorë - ende nuk i kemi transferuar kërkesat SELECT në një pasqyrë, siç rekomandohet. Por ne nuk e bëjmë këtë me vetëdije, pasi dëshirojmë fillimisht të shfrytëzojmë deri në fund mundësitë e optimizimit 'inteligjent', para se të aktivizojmë 'artillerinë e madhe'.
Një qasje kritike ndaj punës së kryer mund të sugjerojë përdorimin e shkallëzimit vertical. Të blejmë një server më të fuqishëm, në vend që të harxhojmë kohën e specialistëve. Një server mund të mos kushtojë kaq shumë, veçanërisht duke pasur parasysh që kufijtë e shkallëzimit vertical ende nuk janë shteruar. Megjithatë, numri i kërkesave është rritur vetëm 10 herë. Në disa vite, funksionaliteti i sistemit është rritur dhe tani ka më shumë lloje kërkesash. Funksionaliteti që kishte, përmes caching, përfundohet me një numër më të vogël kërkesash, për më tepër, kërkesa më efikase. Kështu duhet të dyfishojmë edhe 5 herë, për të marrë koeficientin real të përshpejtimit. Pra, sipas llogarive më modeste, mund të themi se përshpejtimi është 50 herë ose më shumë. Shkallëzimi vertical i serverit për 50 herë do të kishte kushtuar më shumë. Sidomos duke pasur parasysh, se optimizimi i kryer njëherë punon gjithmonë, ndërsa faturat për serverin e marrë me qira vijnë çdo muaj.
Burimi: habr.com
