Mengoptimumkan pertanyaan pangkalan data menggunakan contoh perkhidmatan B2B untuk pembina

Bagaimana untuk mengembangkan 10 kali ganda bilangan pertanyaan ke pangkalan data tanpa berpindah ke pelayan yang lebih produktif dan mengekalkan fungsi sistem? Saya akan memberitahu anda cara kami menangani penurunan dalam prestasi pangkalan data kami, cara kami mengoptimumkan pertanyaan SQL untuk melayani seberapa ramai pengguna yang mungkin dan tidak meningkatkan kos sumber pengkomputeran.

Saya membuat perkhidmatan untuk menguruskan proses perniagaan dalam syarikat pembinaan. Kira-kira 3 ribu syarikat bekerja dengan kami. Lebih daripada 10 ribu orang bekerja dengan sistem kami setiap hari selama 4-10 jam. Ia menyelesaikan pelbagai masalah perancangan, pemberitahuan, amaran, pengesahan... Kami menggunakan PostgreSQL 9.6. Kami mempunyai kira-kira 300 jadual dalam pangkalan data dan sehingga 200 juta pertanyaan (10 ribu yang berbeza) diterima setiap hari. Secara purata kami mempunyai 3-4 ribu permintaan sesaat, pada saat paling aktif lebih daripada 10 ribu permintaan sesaat. Kebanyakan pertanyaan adalah OLAP. Terdapat lebih sedikit penambahan, pengubahsuaian dan pemadaman, bermakna beban OLTP agak ringan. Saya menyediakan semua nombor ini supaya anda boleh menilai skala projek kami dan memahami betapa berguna pengalaman kami untuk anda.

Gambar satu. Berlirik

Apabila kami memulakan pembangunan, kami tidak benar-benar memikirkan jenis beban yang akan jatuh pada pangkalan data dan apa yang akan kami lakukan jika pelayan berhenti menarik. Semasa mereka bentuk pangkalan data, kami mengikuti cadangan umum dan cuba untuk tidak menembak diri kami sendiri, tetapi melampaui nasihat umum seperti "jangan gunakan corak Nilai Atribut Entiti kami tidak masuk. Kami mereka bentuk berdasarkan prinsip normalisasi, mengelakkan lebihan data dan tidak mengambil berat tentang mempercepatkan pertanyaan tertentu. Sebaik sahaja pengguna pertama tiba, kami menghadapi masalah prestasi. Seperti biasa, kami tidak bersedia sepenuhnya untuk ini. Masalah pertama ternyata mudah. Sebagai peraturan, semuanya telah diselesaikan dengan menambah indeks baharu. Tetapi ada masa apabila tampalan mudah berhenti berfungsi. Menyedari bahawa kami kurang pengalaman dan semakin sukar untuk kami memahami perkara yang menyebabkan masalah, kami mengupah pakar yang membantu kami menyediakan pelayan dengan betul, menyambungkan pemantauan dan menunjukkan kepada kami tempat untuk mendapatkannya statistik.

Gambar dua. Statistik

Jadi kami mempunyai kira-kira 10 ribu pertanyaan berbeza yang dilaksanakan pada pangkalan data kami setiap hari. Daripada 10 ribu ini, terdapat raksasa yang dieksekusi 2-3 juta kali dengan purata masa pelaksanaan 0.1-0.3 ms, dan terdapat pertanyaan dengan purata masa pelaksanaan 30 saat yang dipanggil 100 kali sehari.

Tidak mungkin untuk mengoptimumkan semua 10 ribu pertanyaan, jadi kami memutuskan untuk memikirkan tempat untuk mengarahkan usaha kami untuk meningkatkan prestasi pangkalan data dengan betul. Selepas beberapa lelaran, kami mula membahagikan permintaan kepada jenis.

Permintaan TOP

Ini adalah pertanyaan paling berat yang mengambil masa paling lama (jumlah masa). Ini adalah pertanyaan yang sama ada dipanggil sangat kerap atau pertanyaan yang mengambil masa yang sangat lama untuk dilaksanakan (pertanyaan yang panjang dan kerap telah dioptimumkan dalam lelaran pertama perjuangan untuk kelajuan). Akibatnya, pelayan menghabiskan paling banyak masa pada pelaksanaannya. Selain itu, adalah penting untuk memisahkan permintaan teratas mengikut jumlah masa pelaksanaan dan secara berasingan mengikut masa IO. Kaedah untuk mengoptimumkan pertanyaan sedemikian sedikit berbeza.

Amalan biasa semua syarikat adalah bekerja dengan permintaan TOP. Terdapat sedikit daripada mereka; mengoptimumkan walaupun satu pertanyaan boleh membebaskan 5-10% daripada sumber. Walau bagaimanapun, apabila projek itu matang, mengoptimumkan pertanyaan TOP menjadi tugas yang semakin tidak remeh. Semua kaedah mudah telah diusahakan, dan permintaan yang paling "berat" mengambil "hanya" 3-5% daripada sumber. Jika pertanyaan TOP secara keseluruhan mengambil masa kurang daripada 30-40%, kemungkinan besar anda telah berusaha untuk menjadikannya berfungsi dengan cepat dan tiba masanya untuk beralih kepada mengoptimumkan pertanyaan daripada kumpulan seterusnya.
Ia kekal untuk menjawab soalan tentang berapa banyak pertanyaan teratas yang harus dimasukkan dalam kumpulan ini. Saya biasanya mengambil sekurang-kurangnya 10, tetapi tidak lebih daripada 20. Saya cuba memastikan bahawa masa yang pertama dan terakhir dalam kumpulan TOP berbeza tidak lebih daripada 10 kali. Iaitu, jika masa pelaksanaan pertanyaan menurun secara mendadak dari tempat pertama ke tempat ke-1, maka saya mengambil TOP-10, jika penurunan itu lebih beransur-ansur, maka saya meningkatkan saiz kumpulan kepada 10 atau 15.

Petani pertengahan

Ini semua adalah permintaan yang datang sejurus selepas TOP, kecuali 5-10% terakhir. Biasanya, dalam mengoptimumkan pertanyaan ini terletak peluang untuk meningkatkan prestasi pelayan dengan banyak. Permintaan ini boleh menimbang sehingga 80%. Tetapi walaupun bahagian mereka telah melebihi 50%, maka sudah tiba masanya untuk melihat mereka dengan lebih teliti.

Ekor

Seperti yang dinyatakan, pertanyaan ini datang pada penghujung dan mengambil masa 5-10% daripada masa. Anda boleh melupakannya hanya jika anda tidak menggunakan alat analisis pertanyaan automatik, maka mengoptimumkannya juga boleh menjadi murah.

Bagaimana untuk menilai setiap kumpulan?

Saya menggunakan pertanyaan SQL yang membantu membuat penilaian sedemikian untuk PostgreSQL (saya pasti pertanyaan serupa boleh ditulis untuk banyak DBMS lain)

Pertanyaan SQL untuk menganggarkan saiz kumpulan TOP-MEDIUM-TAIL

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

Hasil pertanyaan ialah tiga lajur, setiap lajur mengandungi peratusan masa yang diperlukan untuk memproses pertanyaan daripada kumpulan ini. Di dalam permintaan terdapat dua nombor (dalam kes saya ialah 20 dan 800) yang memisahkan permintaan daripada satu kumpulan daripada yang lain.

Beginilah kira-kira perbandingan bahagian permintaan pada masa kerja pengoptimuman bermula dan sekarang.

Rajah menunjukkan bahawa bahagian permintaan TOP telah menurun dengan ketara, tetapi "petani pertengahan" telah meningkat.
Pada mulanya, permintaan TOP termasuk kesilapan yang nyata. Lama kelamaan, penyakit zaman kanak-kanak hilang, bahagian permintaan TOP berkurangan, dan semakin banyak usaha perlu dilakukan untuk mempercepatkan permintaan yang sukar.

Untuk mendapatkan teks permintaan kami menggunakan permintaan berikut

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

Berikut ialah senarai teknik yang paling biasa digunakan yang membantu kami mempercepatkan pertanyaan TOP:

• Reka bentuk semula sistem, contohnya, mengolah semula logik pemberitahuan menggunakan broker mesej dan bukannya pertanyaan berkala kepada pangkalan data
• Menambah atau menukar indeks
• Menulis semula pertanyaan ORM kepada SQL tulen
• Menulis semula logik pemuatan data malas
• Caching melalui penyahnormalan data. Sebagai contoh, kami mempunyai sambungan jadual Penghantaran -> Invois -> Permintaan -> Permohonan. Iaitu, setiap penghantaran dikaitkan dengan aplikasi melalui jadual lain. Untuk tidak memautkan semua jadual dalam setiap permintaan, kami menduplikasi pautan ke permintaan dalam jadual Penghantaran.
• Caching jadual statik dengan buku rujukan dan jarang menukar jadual dalam memori program.

Kadangkala perubahan itu merupakan reka bentuk semula yang mengagumkan, tetapi ia memberikan 5-10% daripada beban sistem dan wajar. Lama kelamaan, ekzos menjadi lebih kecil dan lebih kecil, dan reka bentuk semula yang lebih serius diperlukan.

Kemudian kami mengalihkan perhatian kami kepada kumpulan permintaan kedua - kumpulan petani pertengahan. Terdapat banyak lagi pertanyaan di dalamnya dan nampaknya ia akan mengambil banyak masa untuk menganalisis keseluruhan kumpulan. Walau bagaimanapun, kebanyakan pertanyaan ternyata sangat mudah untuk dioptimumkan, dan banyak masalah diulang berpuluh-puluh kali dalam variasi yang berbeza. Berikut ialah contoh beberapa pengoptimuman biasa yang kami gunakan pada berpuluh-puluh pertanyaan serupa dan setiap kumpulan pertanyaan yang dioptimumkan memunggah pangkalan data sebanyak 3-5%.

• Daripada menyemak kehadiran rekod menggunakan COUNT dan imbasan jadual penuh, EXISTS mula digunakan
• Menghilangkan DISTINCT (tiada resipi umum, tetapi kadang-kadang anda boleh dengan mudah menyingkirkannya dengan mempercepatkan permintaan sebanyak 10-100 kali).

  Sebagai contoh, bukannya pertanyaan untuk memilih semua pemacu daripada jadual penghantaran yang besar (PENGHANTARAN)

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

  membuat pertanyaan pada PERSON jadual yang agak kecil

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  Nampaknya kami menggunakan subquery berkorelasi, tetapi ia memberikan kelajuan lebih daripada 10 kali.

• Dalam banyak kes, COUNT telah ditinggalkan sama sekali dan
  digantikan dengan pengiraan nilai anggaran
• bukannya

  UPPER(s) LIKE JOHN%’ 
  

  menggunakan

  s ILIKE “John%”
  

Setiap permintaan khusus kadangkala dipercepatkan sebanyak 3-1000 kali. Walaupun prestasi yang mengagumkan, pada mulanya kami nampaknya tidak ada gunanya mengoptimumkan pertanyaan yang mengambil masa 10 ms untuk diselesaikan, merupakan salah satu daripada ratus pertanyaan terberat ke-3, dan mengambil perseratus peratus daripada keseluruhan masa muat pangkalan data. Tetapi dengan menggunakan resipi yang sama pada kumpulan pertanyaan daripada jenis yang sama, kami memenangi kembali beberapa peratus. Untuk tidak membuang masa menyemak semua ratusan pertanyaan secara manual, kami menulis beberapa skrip ringkas yang menggunakan ungkapan biasa untuk mencari pertanyaan daripada jenis yang sama. Akibatnya, carian kumpulan pertanyaan secara automatik membolehkan kami meningkatkan lagi prestasi kami dengan usaha yang sederhana.

Hasilnya, kami telah mengusahakan perkakasan yang sama selama tiga tahun sekarang. Purata beban harian adalah kira-kira 30%, pada puncaknya mencapai 70%. Bilangan permintaan, serta bilangan pengguna, telah meningkat kira-kira 10 kali ganda. Dan semua ini berkat pemantauan berterusan kumpulan permintaan TOP-MEDIUM yang sama ini. Sebaik sahaja permintaan baharu muncul dalam kumpulan TOP, kami segera menganalisisnya dan cuba mempercepatkannya. Kami menyemak kumpulan MEDIUM sekali seminggu menggunakan skrip analisis pertanyaan. Jika kami menjumpai pertanyaan baharu yang sudah kami ketahui cara mengoptimumkannya, kami segera mengubahnya. Kadangkala kami menemui kaedah pengoptimuman baharu yang boleh digunakan pada beberapa pertanyaan sekaligus.

Menurut ramalan kami, pelayan semasa akan menahan peningkatan bilangan pengguna sebanyak 3-5 kali lagi. Benar, kami mempunyai satu lagi ace - kami masih belum memindahkan pertanyaan SELECT ke cermin, seperti yang disyorkan. Tetapi kami tidak melakukan ini secara sedar, kerana kami ingin terlebih dahulu menghabiskan sepenuhnya kemungkinan pengoptimuman "pintar" sebelum menghidupkan "artileri berat".
Pandangan kritikal pada kerja yang dilakukan mungkin mencadangkan penggunaan penskalaan menegak. Beli pelayan yang lebih berkuasa daripada membuang masa pakar. Pelayan mungkin tidak menelan kos sebanyak itu, terutamanya kerana kami masih belum menghabiskan had penskalaan menegak. Bagaimanapun, hanya bilangan permintaan meningkat 10 kali ganda. Sepanjang beberapa tahun, fungsi sistem telah meningkat dan kini terdapat lebih banyak jenis permintaan. Terima kasih kepada caching, kefungsian yang wujud dilakukan dalam permintaan yang lebih sedikit dan permintaan yang lebih cekap. Ini bermakna anda boleh mendarab dengan 5 lagi dengan selamat untuk mendapatkan pekali pecutan sebenar. Jadi, mengikut anggaran yang paling konservatif, kita boleh mengatakan bahawa pecutan adalah 50 kali atau lebih. Mengayun pelayan secara menegak akan menelan kos 50 kali lebih tinggi. Terutama memandangkan sebaik sahaja pengoptimuman dijalankan, ia berfungsi sepanjang masa, dan bil untuk pelayan yang disewa datang setiap bulan.

Sumber: www.habr.com