Bagaimana cara tumbuh 10 kali di bawah jumlah permintaan basis data tanpa pindah ke server yang lebih efisien dan menjaga sistem tetap berfungsi? Saya akan memberi tahu Anda bagaimana kami berjuang dengan penurunan kinerja basis data kami, bagaimana kami mengoptimalkan permintaan SQL untuk melayani sebanyak mungkin pengguna dan tidak menambah biaya sumber daya komputasi.
Saya membuat layanan untuk mengelola proses bisnis di perusahaan konstruksi. Sekitar 3 ribu perusahaan bekerja bersama kami. Lebih dari 10 ribu orang bekerja dengan sistem kami selama 4-10 jam setiap hari. Ini memecahkan berbagai tugas perencanaan, peringatan, peringatan, validasi ... Kami menggunakan PostgreSQL 9.6. Kami memiliki sekitar 300 tabel dalam database dan setiap hari hingga 200 juta permintaan (10 ribu berbeda) dikirim ke sana. Rata-rata, kami memiliki 3-4 ribu permintaan per detik, pada saat-saat paling aktif lebih dari 10 ribu permintaan per detik. Sebagian besar permintaan adalah OLAP. Ada penambahan yang jauh lebih sedikit, modifikasi dan penghapusan, yaitu, beban OLTP relatif kecil. Saya memberikan semua angka ini sehingga Anda dapat mengevaluasi ruang lingkup proyek kami dan memahami bagaimana pengalaman kami dapat berguna bagi Anda.
Gambar pertama. Liris
Ketika kami memulai pengembangan, kami tidak benar-benar berpikir tentang jenis muatan apa yang akan ada di database dan apa yang akan kami lakukan jika server berhenti menarik. Saat merancang basis data, kami mengikuti rekomendasi umum dan berusaha untuk tidak menembak diri sendiri, tetapi di luar kiat umum seperti “jangan gunakan pola
Nilai Atribut Entitas , kami tidak melakukannya. Dirancang berdasarkan prinsip normalisasi, menghindari redundansi data dan tidak peduli mempercepat pertanyaan tertentu. Segera setelah pengguna pertama tiba, kami mengalami masalah kinerja. Seperti biasa, kami benar-benar tidak siap untuk ini. Masalah pertama sederhana. Sebagai aturan, semuanya diputuskan dengan menambahkan indeks baru. Tetapi ada saatnya ketika tambalan sederhana berhenti bekerja. Setelah menyadari bahwa tidak ada pengalaman yang cukup dan menjadi semakin sulit untuk memahami apa penyebab masalah, kami mempekerjakan spesialis yang membantu kami mengatur server dengan benar, menghubungkan pemantauan, menunjukkan ke mana harus mencari untuk mendapatkan
statistik .
Gambar kedua. Statistik
Jadi kami memiliki sekitar 10 ribu kueri berbeda yang dijalankan pada basis data kami per hari. Dari 10 ribu ini, ada monster yang berlari 2-3 juta kali dengan waktu lari rata-rata 0,1-0,3 ms dan ada pertanyaan dengan waktu lari rata-rata 30 detik yang disebut 100 kali sehari.
Itu tidak mungkin untuk mengoptimalkan semua 10 ribu kueri, jadi kami memutuskan untuk mencari tahu di mana harus mengarahkan upaya untuk meningkatkan kinerja database dengan benar. Setelah beberapa iterasi, kami mulai membagi permintaan menjadi beberapa tipe.
Kueri TOP
Ini adalah pertanyaan yang paling sulit yang membutuhkan waktu paling lama (total waktu). Ini adalah query yang disebut sangat sering atau query yang membutuhkan waktu sangat lama (query yang panjang dan sering dioptimalkan bahkan pada iterasi pertama dari perjuangan untuk kecepatan). Akibatnya, server menghabiskan waktu paling banyak pada eksekusi mereka secara total. Selain itu, penting untuk memisahkan permintaan teratas dengan total waktu pelaksanaan dan secara terpisah oleh waktu IO. Cara untuk mengoptimalkan pertanyaan semacam itu sedikit berbeda.
Praktik yang biasa dilakukan semua perusahaan adalah bekerja dengan permintaan TOP. Ada beberapa dari mereka, optimasi bahkan satu permintaan dapat membebaskan 5-10% sumber daya. Namun, seiring dengan bertambahnya proyek, mengoptimalkan kueri TOP menjadi tugas yang semakin tidak sepele. Semua metode sederhana telah berhasil, dan bahkan permintaan yang paling “sulit” menghilangkan “hanya” 3-5% sumber daya. Jika total permintaan TOP membutuhkan waktu kurang dari 30-40%, maka kemungkinan besar Anda telah melakukan upaya untuk membuatnya bekerja dengan cepat dan inilah saatnya untuk beralih ke mengoptimalkan permintaan dari grup berikutnya.
Tetap menjawab pertanyaan berapa banyak kueri teratas untuk disertakan dalam grup ini. Saya biasanya mengambil tidak kurang dari 10, tetapi tidak lebih dari 20. Saya mencoba untuk memastikan bahwa waktu yang pertama dan terakhir dalam kelompok TOP berbeda tidak lebih dari 10 kali. Artinya, jika waktu eksekusi permintaan turun tajam dari 1 tempat ke 10, maka saya mengambil TOP-10, jika musim gugur lebih lancar, maka saya meningkatkan ukuran grup menjadi 15 atau 20.
Petani menengah (menengah)
Ini semua permintaan yang langsung muncul setelah TOP, dengan pengecualian 5-10% terakhir. Biasanya, dalam mengoptimalkan permintaan khusus ini terletak kemampuan untuk sangat meningkatkan kinerja server. Pertanyaan ini dapat "menimbang" hingga 80%. Tetapi bahkan jika bagian mereka telah melebihi 50%, maka sudah saatnya untuk melihat mereka lebih dekat.
Buntut
Seperti yang dikatakan, permintaan ini berjalan pada akhirnya dan mereka mengambil 5-10% dari waktu. Anda dapat melupakannya hanya jika Anda tidak menggunakan alat analisis kueri otomatis, maka pengoptimalannya juga bisa murah.
Bagaimana cara mengevaluasi setiap kelompok?
Saya menggunakan kueri SQL yang membantu membuat penilaian untuk PostgreSQL (saya yakin bahwa untuk banyak DBMS lainnya Anda dapat menulis kueri yang sama)
Permintaan SQL untuk memperkirakan ukuran grup TOP-MEDIUM-TAILSELECT 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 adalah tiga kolom, yang masing-masing berisi persentase waktu yang dihabiskan untuk memproses permintaan dari grup ini. Di dalam kueri, ada dua angka (dalam kasus saya, 20 dan 800) yang memisahkan permintaan dari satu grup dari yang lain.
Ini adalah bagaimana proporsi permintaan pada saat dimulainya pekerjaan optimasi sekarang secara kasar berkorelasi.
Diagram menunjukkan bahwa bagian permintaan TOP telah menurun tajam, tetapi "petani menengah" telah tumbuh.
Awalnya, TOP blunder memukul kueri TOP. Seiring waktu, penyakit pada masa anak-anak hilang, bagian permintaan TOP berkurang, dan lebih banyak upaya harus dilakukan untuk mempercepat permintaan yang sulit.
Untuk mendapatkan teks dari permintaan kami menggunakan permintaan tersebut 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
Berikut adalah daftar trik yang paling umum digunakan yang membantu kami mempercepat kueri TOP:
- Mendesain ulang sistem, misalnya, memproses logika pemberitahuan pada pialang pesan alih-alih permintaan basis data berkala
- Menambah atau Memodifikasi Indeks
- Menulis ulang permintaan ORM dalam SQL murni
- Menulis ulang logika pemuatan data malas
- Caching melalui denormalisasi data. Misalnya, kami memiliki tautan antara tabel Pengiriman -> Faktur -> Permintaan -> Permintaan. Artinya, setiap pengiriman dikaitkan dengan aplikasi melalui tabel lain. Agar tidak menautkan semua tabel di setiap permintaan, kami menggandakan tautan ke aplikasi di tabel Pengiriman.
- Caching tabel statis dengan direktori dan jarang mengubah tabel dalam memori program.
Terkadang perubahan menyeret pada desain ulang yang mengesankan, tetapi mereka memberikan 5-10% dari bongkar sistem dan dibenarkan. Seiring waktu, knalpot menjadi semakin berkurang, dan desain ulang menjadi semakin serius.
Lalu kami menarik perhatian pada kelompok permintaan kedua - kelompok petani menengah. Ini memiliki lebih banyak permintaan dan tampaknya akan membutuhkan banyak waktu untuk menganalisis seluruh grup. Namun, sebagian besar pertanyaan ternyata sangat sederhana untuk optimasi, dan banyak masalah diulang puluhan kali dalam variasi yang berbeda. Berikut adalah beberapa contoh optimisasi khas yang kami terapkan pada lusinan kueri serupa dan setiap grup kueri yang dioptimalkan menurunkan database sebesar 3-5%.
- Alih-alih memeriksa keberadaan catatan dengan COUNT dan pemindaian tabel penuh, EXISTS mulai digunakan.
- Kami menyingkirkan DISTINCT (tidak ada resep umum, tetapi kadang-kadang Anda dapat dengan mudah menyingkirkannya dengan mempercepat permintaan 10-100 kali).
Misalnya, alih-alih meminta untuk memilih semua driver pada tabel pengiriman yang besar (PENGIRIMAN)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
membuat permintaan untuk tabel PERSON yang relatif 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)
Tampaknya kami menggunakan subquery yang berkorelasi, tetapi memberikan percepatan lebih dari 10 kali.
- Dalam banyak kasus, COUNT dan
diganti dengan perhitungan nilai perkiraan
- bukannya
UPPER(s) LIKE JOHN%'
gunakan
s ILIKE “John%”
Setiap permintaan khusus terkadang dipercepat sebanyak 3-1000 kali. Terlepas dari kinerja yang mengesankan, pada awalnya kami merasa bahwa tidak ada gunanya mengoptimalkan kueri, yang dieksekusi selama 10 ms, termasuk dalam ke-3 dari kueri terberat dan dalam total waktu pemuatan basis data, butuh ratusan persen. Tetapi dengan menerapkan resep yang sama pada sekelompok permintaan serupa, kami memenangkan beberapa persen. Agar tidak membuang waktu secara manual untuk melihat ratusan kueri, kami menulis beberapa skrip sederhana yang, menggunakan ekspresi reguler, menemukan kueri yang serupa. Akibatnya, pencarian otomatis untuk grup kueri memungkinkan kami untuk lebih meningkatkan kinerja kami dengan menghabiskan upaya sederhana.
Sebagai hasilnya, kami telah mengerjakan perangkat keras yang sama selama tiga tahun sekarang. Beban harian rata-rata sekitar 30%, pada puncaknya mencapai 70%. Jumlah permintaan serta jumlah pengguna telah meningkat sekitar 10 kali lipat. Dan semua ini berkat pemantauan terus-menerus dari kelompok-kelompok kueri TOP-MEDIUM ini. Segera setelah permintaan baru muncul di grup TOP, kami segera menganalisanya dan mencoba mempercepatnya. Kami meninjau grup MENENGAH seminggu sekali menggunakan skrip analisis kueri. Jika Anda menemukan permintaan baru yang telah kami ketahui cara mengoptimalkannya, maka kami segera mengubahnya. Terkadang kami menemukan metode optimasi baru yang dapat diterapkan ke beberapa kueri sekaligus.
Menurut perkiraan kami, server saat ini akan menahan peningkatan jumlah pengguna sebanyak 3-5 kali. Benar, kami memiliki satu kartu truf lagi di lengan, kami masih belum menerjemahkan pertanyaan SELECT ke cermin, seperti yang disarankan. Tapi kami tidak melakukan ini secara sadar, karena kami pertama-tama ingin sepenuhnya menguras kemungkinan optimasi "pintar" sebelum menyalakan "artileri berat".
Pandangan kritis pada pekerjaan yang dilakukan mungkin menyarankan menggunakan penskalaan vertikal. Beli server yang lebih kuat, daripada menghabiskan waktu para spesialis. Server mungkin tidak terlalu mahal, terutama karena batas penskalaan vertikal belum habis. Namun, hanya jumlah permintaan meningkat 10 kali lipat. Selama beberapa tahun, fungsi sistem telah meningkat dan sekarang ada lebih banyak jenis permintaan. Fungsionalitas yang disebabkan oleh caching dilakukan oleh lebih sedikit permintaan, apalagi permintaan yang lebih efisien. Jadi, Anda dapat dengan aman mengalikan 5 dengan yang lain untuk mendapatkan koefisien akselerasi yang sebenarnya. Jadi, menurut perkiraan paling konservatif, kita dapat mengatakan bahwa akselerasi adalah 50 kali atau lebih. Mengguncang server secara vertikal 50 kali lebih mahal. Terutama mengingat bahwa begitu optimasi dilakukan sepanjang waktu, dan tagihan untuk server sewaan datang setiap bulan.