Барилгачдад зориулсан B2B үйлчилгээний жишээг ашиглан мэдээллийн сангийн асуулгыг оновчтой болгох

Хэрхэн илүү бүтээмжтэй сервер рүү шилжихгүйгээр мэдээллийн санд асуулгын тоог 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 хүсэлтүүд

Эдгээр нь хамгийн их цаг зарцуулдаг (нийт цаг) хамгийн хүнд асуултууд юм. Эдгээр нь маш олон удаа дуудагддаг эсвэл гүйцэтгэхэд маш удаан хугацаа шаардагддаг асуулга юм (хурдны төлөөх тэмцлийн эхний давталтуудад урт ба байнгын асуулга оновчтой болсон). Үүний үр дүнд сервер тэдний гүйцэтгэлд хамгийн их цаг зарцуулдаг. Түүнчлэн, дээд хүсэлтүүдийг гүйцэтгэх нийт хугацаа болон IO хугацаанд тусад нь салгах нь чухал юм. Ийм асуултуудыг оновчтой болгох аргууд нь арай өөр юм.

Бүх компаниудын ердийн практик бол TOP хүсэлттэй ажиллах явдал юм. Тэдгээрийн цөөхөн нь нэг асуултыг оновчтой болгох нь нөөцийн 5-10% -ийг чөлөөлөх боломжтой. Гэсэн хэдий ч төсөл боловсорч гүйцэхийн хэрээр TOP асуултуудыг оновчтой болгох нь улам бүр чухал биш ажил болж байна. Бүх энгийн аргуудыг аль хэдийн боловсруулсан бөгөөд хамгийн "хүнд" хүсэлт нь "ердөө" 3-5% нөөцийг авдаг. Хэрэв TOP асуулга нийт цаг хугацааны 30-40% -иас бага хувийг эзэлдэг бол та тэдгээрийг хурдан ажиллуулахын тулд аль хэдийн хүчин чармайлт гаргасан байх магадлалтай бөгөөд дараагийн бүлгийн асуулгыг оновчтой болгох цаг болжээ.
Энэ бүлэгт хэчнээн шилдэг асуулт багтах ёстой вэ гэсэн асуултад хариулах л үлдлээ. Би ихэвчлэн 10-аас доошгүй авдаг, гэхдээ 20-оос илүүгүй. Би TOP бүлгийн эхний болон сүүлчийнх нь цаг 10-аас илүүгүй ялгаатай байхыг хичээдэг. Өөрөөр хэлбэл, асуулгын гүйцэтгэлийн хугацаа 1-р байрнаас 10-р байр хүртэл огцом буурсан бол би TOP-10-ыг авдаг, хэрэв буурах нь аажмаар байвал бүлгийн хэмжээг 15 эсвэл 20 болгон нэмэгдүүлдэг.

Дундад тариачид

Эдгээр нь сүүлийн 5-10%-ийг эс тооцвол TOP-ын дараа шууд ирдэг бүх хүсэлтүүд юм. Ихэвчлэн эдгээр асуулгыг оновчтой болгох нь серверийн гүйцэтгэлийг ихээхэн нэмэгдүүлэх боломжийг олгодог. Эдгээр хүсэлт нь 80% хүртэл жинтэй байж болно. Гэхдээ тэдний эзлэх хувь 50% -иас давсан ч гэсэн тэдгээрийг илүү анхааралтай авч үзэх цаг болжээ.

Сүүл

Өмнө дурьдсанчлан, эдгээр асуулга нь төгсгөлд ирдэг бөгөөд 5-10% -ийг эзэлдэг. Хэрэв та автомат асуулгын дүн шинжилгээ хийх хэрэгслийг ашиглахгүй бол тэдгээрийг мартаж болно, дараа нь тэдгээрийг оновчтой болгох нь хямд байх болно.

Бүлэг бүрийг хэрхэн үнэлэх вэ?

Би PostgreSQL-д ийм үнэлгээ хийхэд тусалдаг SQL асуулга ашигладаг (үүнтэй төстэй асуулгыг бусад олон DBMS-д бичиж болно гэдэгт би итгэлтэй байна)

TOP-MEDIUM-TAIL бүлгүүдийн хэмжээг тооцоолох SQL асуулга

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN 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

Асуулгын үр дүн нь гурван багана бөгөөд тус бүр нь энэ бүлгийн асуулгыг боловсруулахад зарцуулсан цагийн хувийг агуулдаг. Хүсэлт дотор нэг бүлгийн хүсэлтийг нөгөө бүлгээс тусгаарладаг хоёр тоо (миний хувьд 20 ба 800) байдаг.

Оновчлолын ажил эхэлж байсан болон одоо байгаа хүсэлтийн хувь хэмжээг ойролцоогоор ингэж харьцуулж байна.

Диаграмаас харахад TOP хүсэлтийн эзлэх хувь огцом буурч, харин "дунд тариачид" нэмэгдсэн байна.
Эхний ээлжинд ТОП хүсэлтүүдэд илт бүдүүлэг алдаанууд багтсан. Цаг хугацаа өнгөрөхөд хүүхдийн өвчлөл алга болж, ТОП хүсэлтийн эзлэх хувь буурч, хүнд хэцүү хүсэлтийг хурдасгахын тулд илүү их хүчин чармайлт гаргах шаардлагатай болсон.

Хүсэлтийн текстийг авахын тулд бид дараах хүсэлтийг ашиглана

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 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

ТОП асуултуудыг хурдасгахад бидэнд тусалсан хамгийн түгээмэл хэрэглэгддэг аргуудын жагсаалтыг энд оруулав.

• Системийг дахин төлөвлөх, тухайлбал мэдээллийн санд тогтмол асуулгын оронд мессеж брокер ашиглан мэдэгдлийн логикийг дахин боловсруулах.
• Индекс нэмэх эсвэл өөрчлөх
• ORM асуулгыг цэвэр SQL болгон дахин бичих
• Залхуу өгөгдөл ачаалах логикийг дахин бичих
• Өгөгдлийг хэвийн бус болгох замаар кэш хийх. Жишээ нь: Хүргэлт -> Нэхэмжлэх -> Хүсэлт -> Програм гэсэн хүснэгтийн холболттой. Өөрөөр хэлбэл хүргэлт бүр нь бусад хүснэгтээр дамжуулан програмтай холбоотой байдаг. Хүсэлт бүрийн бүх хүснэгтийг холбохгүйн тулд бид Хүргэлтийн хүснэгт дэх хүсэлтийн холбоосыг хуулбарласан.
• Статик хүснэгтүүдийг лавлах номоор кэшлэх, програмын санах ой дахь хүснэгтүүдийг ховор өөрчлөх.

Заримдаа өөрчлөлтүүд нь гайхалтай дахин дизайнтай байсан ч системийн ачааллын 5-10% -ийг хангаж, үндэслэлтэй байв. Цаг хугацаа өнгөрөхөд яндан нь багасч, багасч, илүү нухацтай дахин боловсруулах шаардлагатай болсон.

Дараа нь бид хоёр дахь бүлэг буюу дунд тариачдын бүлэгт анхаарлаа хандуулав. Үүнд өөр олон асуулт байгаа бөгөөд бүхэл бүтэн бүлгийг шинжлэхэд маш их цаг хугацаа шаардагдах бололтой. Гэсэн хэдий ч, ихэнх асуулга нь оновчтой болгоход маш хялбар байсан бөгөөд олон асуудал өөр өөр хувилбараар хэдэн арван удаа давтагдсан. Бид олон арван ижил төстэй асуулгад ашигласан зарим ердийн оновчлолын жишээг энд оруулав, оновчтой асуулгын бүлэг бүр нь мэдээллийн санг 3-5% -иар буулгасан.

• COUNT болон хүснэгтийг бүрэн скан ашиглан бичлэг байгаа эсэхийг шалгахын оронд EXISTS ашиглаж эхэлсэн.
• DISTINCT-аас салсан (ерөнхий жор байдаггүй, гэхдээ заримдаа та хүсэлтийг 10-100 дахин хурдасгах замаар амархан салж болно).

  Жишээлбэл, хүргэлтийн том хүснэгтээс бүх драйверуудыг сонгох асуулгын оронд (ХҮРГЭЛТ)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  харьцангуй жижиг 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)
  

  Бид харилцан хамааралтай дэд асуулга ашигласан юм шиг санагдаж байна, гэхдээ энэ нь 10 дахин их хурдыг өгдөг.

• Ихэнх тохиолдолд COUNT-г бүрмөсөн орхисон
  ойролцоо утгын тооцоогоор солино
• оронд нь

  UPPER(s) LIKE JOHN%’ 
  

  ашиглах

  s ILIKE “John%”
  

Тодорхой хүсэлт бүрийг заримдаа 3-1000 дахин хурдасгадаг. Хэдийгээр гайхалтай гүйцэтгэлтэй байсан ч эхлээд 10 мс зарцуулдаг, 3-р зуун хамгийн хүнд асуулгын нэг бөгөөд мэдээллийн баазыг ачаалах нийт хугацааны XNUMX хувийг эзэлдэг асуулгыг оновчтой болгох нь утгагүй юм шиг санагдаж байсан. Гэхдээ ижил төрлийн асуулгад ижил жор хэрэглэснээр бид хэдхэн хувийг буцааж авсан. Хэдэн зуун асуултыг гараар шалгахад цаг алдахгүйн тулд бид ижил төрлийн асуулга олохын тулд ердийн илэрхийлэл ашигладаг хэд хэдэн энгийн скрипт бичсэн. Үүний үр дүнд асуулгын бүлгүүдийг автоматаар хайх нь бага зэрэг хүчин чармайлт гаргаж гүйцэтгэлээ сайжруулах боломжийг бидэнд олгосон.

Үүний үр дүнд бид нэг техник хангамж дээр ажиллаад гурван жил болж байна. Өдөр тутмын дундаж ачаалал ойролцоогоор 30%, оргил үед 70% хүрдэг. Хүсэлтийн тоо, түүнчлэн хэрэглэгчдийн тоо ойролцоогоор 10 дахин нэмэгдсэн байна. Энэ бүхэн нь TOP-MEDIUM хүсэлтүүдийн ижил бүлгүүдийг байнга хянаж байдагт талархаж байна. TOP бүлэгт шинэ хүсэлт гарч ирмэгц бид тэр даруйд нь дүн шинжилгээ хийж, хурдасгахыг хичээдэг. Бид MEDIUM бүлгийг долоо хоногт нэг удаа асуулгын шинжилгээний скрипт ашиглан хянадаг. Хэрэв бид хэрхэн оновчтой болгох талаар аль хэдийн мэддэг шинэ асуултуудтай тулгарвал бид тэдгээрийг хурдан өөрчилдөг. Заримдаа бид хэд хэдэн асуулгад нэгэн зэрэг хэрэглэж болох шинэ оновчлолын аргуудыг олдог.

Бидний таамаглаж байгаагаар одоогийн сервер нь хэрэглэгчдийн тоог дахин 3-5 дахин нэмэгдүүлэхийг тэсвэрлэх болно. Бидэнд дахин нэг хөзөр байгаа нь үнэн - бид санал болгосны дагуу SELECT асуултуудыг толинд шилжүүлээгүй хэвээр байна. Гэхдээ бид үүнийг ухамсартайгаар хийдэггүй, учир нь бид "хүнд их буу" асаахаас өмнө "ухаалаг" оновчлолын боломжуудыг бүрэн шавхахыг хүсч байна.
Хийсэн ажилд шүүмжлэлтэй хандах нь босоо масштабыг ашиглахыг санал болгож магадгүй юм. Мэргэжилтнүүдийн цагийг дэмий үрэхийн оронд илүү хүчирхэг сервер худалдаж аваарай. Сервер нь тийм ч их зардал гарахгүй байж магадгүй, ялангуяа бид босоо масштабын хязгаарыг хараахан дуусгаагүй байгаа тул. Гэтэл зөвхөн хүсэлтийн тоо 10 дахин нэмэгдсэн байна. Хэдэн жилийн туршид системийн үйл ажиллагаа нэмэгдэж, одоо илүү олон төрлийн хүсэлтүүд гарч байна. Кэшийн ачаар одоо байгаа функцууд нь цөөн хүсэлтээр, илүү үр дүнтэй хүсэлтээр хийгддэг. Энэ нь та бодит хурдатгалын коэффициентийг авахын тулд өөр 5-аар үржүүлж болно гэсэн үг юм. Тиймээс, хамгийн консерватив тооцоогоор бид хурдатгал нь 50 дахин ба түүнээс дээш байсан гэж хэлж болно. Серверийг босоо байдлаар эргүүлэх нь 50 дахин их зардал гарах болно. Ялангуяа оновчлолыг хийснээр энэ нь үргэлж ажилладаг бөгөөд түрээсэлсэн серверийн төлбөр сар бүр ирдэг гэдгийг харгалзан үзвэл.

Эх сурвалж: www.habr.com