Mengoptimalkan kueri basis data menggunakan contoh layanan B2B untuk pembangun

Bagaimana cara meningkatkan jumlah kueri ke database 10 kali lipat tanpa berpindah ke server yang lebih produktif dan mempertahankan fungsionalitas sistem? Saya akan memberi tahu Anda bagaimana kami menangani penurunan kinerja database kami, bagaimana kami mengoptimalkan kueri SQL untuk melayani pengguna sebanyak mungkin dan tidak meningkatkan biaya sumber daya komputasi.

Saya membuat layanan untuk mengelola proses bisnis di perusahaan konstruksi. Sekitar 3 ribu perusahaan bekerja sama dengan kami. Lebih dari 10 ribu orang bekerja dengan sistem kami setiap hari selama 4-10 jam. Ini memecahkan berbagai masalah perencanaan, pemberitahuan, peringatan, validasi... Kami menggunakan PostgreSQL 9.6. Kami memiliki sekitar 300 tabel dalam database dan hingga 200 juta kueri (10 ribu kueri berbeda) diterima setiap hari. Rata-rata kami memiliki 3-4 ribu permintaan per detik, pada saat paling aktif lebih dari 10 ribu permintaan per detik. Sebagian besar kuerinya adalah OLAP. Penambahan, modifikasi dan penghapusan jauh lebih sedikit, yang berarti beban OLTP relatif ringan. Saya memberikan semua angka ini agar Anda dapat menilai skala proyek kami dan memahami betapa bermanfaatnya pengalaman kami bagi Anda.

Gambar satu. Liris

Saat kami memulai pengembangan, kami tidak terlalu memikirkan jenis beban apa yang akan menimpa database dan apa yang akan kami lakukan jika server berhenti melakukan penarikan. Saat merancang database, kami mengikuti rekomendasi umum dan mencoba untuk tidak menyalahkan diri sendiri, namun melampaui saran umum seperti “jangan gunakan pola tersebut. Nilai Atribut Entitas kami tidak masuk. Kami merancang berdasarkan prinsip normalisasi, menghindari redundansi data dan tidak peduli untuk mempercepat kueri tertentu. Segera setelah pengguna pertama tiba, kami mengalami masalah kinerja. Seperti biasa, kami sama sekali tidak siap menghadapi hal ini. Masalah pertama ternyata sederhana. Sebagai aturan, semuanya diselesaikan dengan menambahkan indeks baru. Namun ada saatnya patch sederhana berhenti bekerja. Menyadari bahwa kami kurang pengalaman dan semakin sulit bagi kami untuk memahami apa yang menyebabkan masalah, kami menyewa spesialis yang membantu kami menyiapkan server dengan benar, menghubungkan pemantauan, dan menunjukkan kepada kami di mana mencarinya. statistik.

Gambar dua. Statistik

Jadi kami memiliki sekitar 10 ribu kueri berbeda yang dieksekusi di database kami setiap hari. Dari 10 ribu tersebut, ada monster yang dieksekusi 2-3 juta kali dengan waktu eksekusi rata-rata 0.1-0.3 ms, dan ada query dengan waktu eksekusi rata-rata 30 detik yang dipanggil 100 kali sehari.

Tidak mungkin mengoptimalkan semua 10 ribu kueri, jadi kami memutuskan untuk mencari tahu ke mana harus mengarahkan upaya kami untuk meningkatkan kinerja database dengan benar. Setelah beberapa kali pengulangan, kami mulai membagi permintaan menjadi beberapa tipe.

Permintaan TERATAS

Ini adalah pertanyaan terberat yang memakan waktu paling lama (total waktu). Ini adalah kueri yang sering dipanggil atau kueri yang membutuhkan waktu sangat lama untuk dieksekusi (kueri yang panjang dan sering dioptimalkan pada iterasi pertama pertarungan demi kecepatan). Akibatnya, server menghabiskan sebagian besar waktu untuk mengeksekusinya. Selain itu, penting untuk memisahkan permintaan teratas berdasarkan total waktu eksekusi dan secara terpisah berdasarkan waktu IO. Metode untuk mengoptimalkan kueri tersebut sedikit berbeda.

Praktik yang biasa dilakukan semua perusahaan adalah menangani permintaan TOP. Jumlahnya sedikit; mengoptimalkan satu kueri saja dapat mengosongkan 5-10% sumber daya. Namun, seiring dengan semakin matangnya proyek, mengoptimalkan kueri TOP menjadi tugas yang semakin tidak sepele. Semua metode sederhana telah berhasil, dan permintaan yang paling "berat" membutuhkan "hanya" 3-5% sumber daya. Jika total kueri TOP memakan waktu kurang dari 30-40%, kemungkinan besar Anda telah melakukan upaya untuk membuatnya bekerja dengan cepat dan inilah saatnya beralih ke pengoptimalan kueri dari grup berikutnya.
Masih menjawab pertanyaan tentang berapa banyak pertanyaan teratas yang harus dimasukkan dalam grup ini. Saya biasanya mengambil setidaknya 10, tetapi tidak lebih dari 20. Saya mencoba memastikan bahwa waktu pertama dan terakhir di grup TOP berbeda tidak lebih dari 10 kali lipat. Artinya, jika waktu eksekusi query turun tajam dari peringkat 1 ke peringkat 10, maka saya ambil TOP-10, jika penurunannya lebih bertahap, maka saya tingkatkan ukuran grup menjadi 15 atau 20.

Petani menengah

Ini semua adalah permintaan yang datang segera setelah TOP, dengan pengecualian 5-10% terakhir. Biasanya, dalam mengoptimalkan kueri ini terdapat peluang untuk meningkatkan kinerja server secara signifikan. Permintaan ini bisa mencapai 80%. Namun meski porsinya sudah melebihi 50%, maka inilah saatnya untuk mencermatinya dengan lebih cermat.

Ekor

Seperti disebutkan, pertanyaan ini muncul di akhir dan memakan waktu 5-10%. Anda dapat melupakannya hanya jika Anda tidak menggunakan alat analisis kueri otomatis, maka mengoptimalkannya juga bisa memakan biaya yang murah.

Bagaimana cara mengevaluasi setiap kelompok?

Saya menggunakan kueri SQL yang membantu membuat penilaian untuk PostgreSQL (saya yakin kueri serupa dapat ditulis untuk banyak DBMS lainnya)

Kueri SQL untuk memperkirakan ukuran grup 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 kueri berupa tiga kolom yang masing-masing berisi persentase waktu yang diperlukan untuk memproses kueri dari grup ini. Di dalam permintaan ada dua nomor (dalam kasus saya 20 dan 800) yang memisahkan permintaan dari satu grup dengan grup lainnya.

Ini adalah perbandingan kasar jumlah permintaan pada saat pekerjaan pengoptimalan dimulai dan sekarang.

Diagram menunjukkan bahwa porsi permintaan TOP telah menurun tajam, namun “petani menengah” telah meningkat.
Pada awalnya, permintaan TOP berisi kesalahan besar. Seiring waktu, penyakit masa kanak-kanak menghilang, jumlah permintaan TOP menurun, dan semakin banyak upaya yang harus dilakukan untuk mempercepat permintaan yang sulit.

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 adalah daftar teknik yang paling umum digunakan yang membantu kami mempercepat kueri TOP:

• Desain ulang sistem, misalnya, pengerjaan ulang logika notifikasi menggunakan perantara pesan alih-alih kueri berkala ke database
• Menambah atau mengubah indeks
• Menulis ulang kueri ORM ke SQL murni
• Menulis ulang logika pemuatan data yang lambat
• Caching melalui denormalisasi data. Misalnya, kita memiliki koneksi tabel Pengiriman -> Faktur -> Permintaan -> Aplikasi. Artinya, setiap pengiriman dikaitkan dengan aplikasi melalui tabel lainnya. Agar tidak menautkan semua tabel di setiap permintaan, kami menggandakan tautan ke permintaan di tabel Pengiriman.
• Menyimpan tabel statis dengan buku referensi dan jarang mengubah tabel di memori program.

Terkadang perubahan tersebut merupakan desain ulang yang mengesankan, namun memberikan 5-10% beban sistem dan dapat dibenarkan. Seiring waktu, knalpot menjadi semakin kecil, dan diperlukan desain ulang yang semakin serius.

Kemudian kami mengalihkan perhatian kami ke kelompok permintaan kedua – kelompok petani menengah. Masih banyak lagi pertanyaan di dalamnya dan sepertinya butuh banyak waktu untuk menganalisis keseluruhan grup. Namun, sebagian besar kueri ternyata sangat mudah untuk dioptimalkan, dan banyak masalah yang diulangi puluhan kali dalam variasi yang berbeda. Berikut adalah contoh beberapa pengoptimalan umum yang kami terapkan pada lusinan kueri serupa dan setiap grup kueri yang dioptimalkan menurunkan muatan database sebesar 3-5%.

• Alih-alih memeriksa keberadaan catatan menggunakan COUNT dan pemindaian tabel lengkap, EXISTS mulai digunakan
• Singkirkan DISTINCT (tidak ada resep umum, tetapi terkadang Anda dapat dengan mudah menghilangkannya dengan mempercepat permintaan 10-100 kali lipat).

  Misalnya, alih-alih meminta untuk memilih semua driver dari tabel pengiriman yang besar (DELIVERY)

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

  membuat kueri pada tabel yang relatif kecil 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)
  

  Tampaknya kami menggunakan subkueri yang berkorelasi, tetapi ini memberikan percepatan lebih dari 10 kali lipat.

• Dalam banyak kasus, COUNT ditinggalkan sama sekali dan
  diganti dengan perhitungan nilai perkiraan
• daripada

  UPPER(s) LIKE JOHN%’ 
  

  menggunakan

  s ILIKE “John%”
  

Setiap permintaan spesifik terkadang dipercepat 3-1000 kali lipat. Terlepas dari kinerjanya yang mengesankan, pada awalnya bagi kami tampaknya tidak ada gunanya mengoptimalkan kueri yang membutuhkan waktu 10 ms untuk diselesaikan, merupakan salah satu dari 3 ratus kueri terberat, dan memakan seperseratus persen dari keseluruhan waktu muat database. Namun dengan menerapkan resep yang sama ke sekelompok kueri dengan jenis yang sama, kami memperoleh keuntungan beberapa persen. Agar tidak membuang waktu meninjau ratusan kueri secara manual, kami menulis beberapa skrip sederhana yang menggunakan ekspresi reguler untuk menemukan kueri dengan jenis yang sama. Hasilnya, penelusuran grup kueri secara otomatis memungkinkan kami meningkatkan kinerja lebih lanjut dengan sedikit usaha.

Hasilnya, kami telah mengerjakan perangkat keras yang sama selama tiga tahun sekarang. Rata-rata beban harian sekitar 30%, pada puncaknya mencapai 70%. Jumlah permintaan serta jumlah pengguna telah meningkat sekitar 10 kali lipat. Dan semua ini berkat pemantauan terus-menerus terhadap kelompok permintaan TOP-MEDIUM yang sama. Begitu muncul request baru di grup TOP, kami langsung menganalisanya dan mencoba mempercepatnya. Kami meninjau grup MEDIUM seminggu sekali menggunakan skrip analisis kueri. Jika kami menemukan kueri baru yang sudah kami ketahui cara mengoptimalkannya, kami segera mengubahnya. Terkadang kami menemukan metode optimasi baru yang dapat diterapkan pada beberapa query sekaligus.

Menurut perkiraan kami, server saat ini akan tahan terhadap peningkatan jumlah pengguna sebanyak 3-5 kali lipat. Benar, kami memiliki satu kartu as lagi - kami masih belum mentransfer kueri SELECT ke mirror, seperti yang direkomendasikan. Namun kami tidak melakukan ini secara sadar, karena kami ingin memanfaatkan sepenuhnya kemungkinan pengoptimalan "pintar" sebelum menggunakan "artileri berat".
Pandangan kritis terhadap pekerjaan yang dilakukan mungkin menyarankan penggunaan penskalaan vertikal. Beli server yang lebih kuat daripada membuang-buang waktu para spesialis. Biaya server mungkin tidak terlalu mahal, terutama karena kami belum kehabisan batas penskalaan vertikal. Namun, hanya jumlah permintaannya yang meningkat 10 kali lipat. Selama beberapa tahun, fungsionalitas sistem telah meningkat dan sekarang terdapat lebih banyak jenis permintaan. Berkat caching, fungsionalitas yang ada dijalankan dalam permintaan yang lebih sedikit, dan permintaan yang lebih efisien. Artinya, Anda dapat dengan aman mengalikannya dengan 5 lagi untuk mendapatkan koefisien percepatan sebenarnya. Jadi, menurut perkiraan paling konservatif, kita dapat mengatakan bahwa percepatannya mencapai 50 kali lipat atau lebih. Mengayunkan server secara vertikal akan memakan biaya 50 kali lebih banyak. Apalagi mengingat sekali optimasi dilakukan, server tersebut berfungsi sepanjang waktu, dan tagihan untuk server yang disewa datang setiap bulan.

Sumber: www.habr.com