Saya sarankan Anda membaca transkrip laporan awal tahun 2016 oleh Vladimir Sitnikov “PostgreSQL dan JDBC sedang memeras semua manfaatnya”
Selamat siang Nama saya Vladimir Sitnikov. Saya telah bekerja untuk NetCracker selama 10 tahun. Dan saya lebih tertarik pada produktivitas. Segala sesuatu yang berhubungan dengan Java, segala sesuatu yang berhubungan dengan SQL adalah yang saya suka.
Dan hari ini saya akan berbicara tentang apa yang kami temui di perusahaan ketika kami mulai menggunakan PostgreSQL sebagai server database. Dan kami kebanyakan bekerja dengan Java. Namun yang akan saya ceritakan hari ini bukan hanya tentang Java. Seperti yang telah ditunjukkan oleh praktik, hal ini juga terjadi dalam bahasa lain.
Kita akan bicara:
- tentang pengambilan sampel data.
- Tentang menyimpan data.
- Dan juga tentang kinerja.
- Dan tentang garu bawah air yang terkubur di sana.
Mari kita mulai dengan pertanyaan sederhana. Kami memilih satu baris dari tabel berdasarkan kunci utama.
Basis data terletak di host yang sama. Dan semua pertanian ini membutuhkan waktu 20 milidetik.
20 milidetik ini sangat banyak. Jika Anda memiliki 100 permintaan seperti itu, maka Anda menghabiskan waktu per detik untuk menelusuri permintaan ini, artinya kami membuang-buang waktu.
Kami tidak suka melakukan ini dan melihat apa yang ditawarkan pangkalan kepada kami untuk ini. Basis data menawarkan kepada kita dua opsi untuk menjalankan kueri.
Opsi pertama adalah permintaan sederhana. Apa bagusnya? Fakta bahwa kami mengambil dan mengirimkannya, dan tidak lebih.
Basis data juga memiliki kueri tingkat lanjut, yang lebih rumit, namun lebih fungsional. Anda dapat mengirim permintaan penguraian, eksekusi, pengikatan variabel, dll secara terpisah.
Kueri super panjang adalah sesuatu yang tidak akan kami bahas dalam laporan saat ini. Kita mungkin menginginkan sesuatu dari database dan ada wish list yang sudah terbentuk dalam beberapa bentuk, yaitu ini yang kita inginkan, tapi tidak mungkin sekarang dan tahun depan. Jadi kami hanya merekamnya dan kami akan berkeliling mengguncang orang-orang utama.
Dan yang bisa kita lakukan adalah query sederhana dan query diperpanjang.
Apa yang istimewa dari setiap pendekatan?
Kueri sederhana bagus untuk eksekusi satu kali. Setelah selesai dan dilupakan. Dan masalahnya adalah ia tidak mendukung format data biner, yaitu tidak cocok untuk beberapa sistem berperforma tinggi.
Kueri yang diperluas – memungkinkan Anda menghemat waktu dalam penguraian. Inilah yang kami lakukan dan mulai gunakan. Ini benar-benar membantu kami. Tidak hanya penghematan pada penguraian. Ada penghematan pada transfer data. Mentransfer data dalam format biner jauh lebih efisien.
Mari kita lanjutkan ke latihan. Seperti inilah tampilan aplikasi pada umumnya. Bisa jadi Java, dll.
Kami membuat pernyataan. Menjalankan perintah. Dibuat dekat. Dimana kesalahannya disini? Apa masalahnya? Tidak masalah. Inilah yang tertulis di semua buku. Beginilah seharusnya penulisannya. Jika ingin performa maksimal, tulislah seperti ini.
Namun praktik menunjukkan bahwa hal ini tidak berhasil. Mengapa? Karena kami memiliki metode "dekat". Dan ketika kita melakukan ini, dari sudut pandang database ternyata seperti seorang perokok yang bekerja dengan database. Kami mengatakan "PARSE EXECUTE DEALLOCATE".
Mengapa harus melakukan semua pembuatan dan pembongkaran pernyataan ekstra ini? Tidak ada yang membutuhkannya. Namun yang biasanya terjadi di PreparedStatements adalah ketika kita menutupnya, semua yang ada di database akan ditutup. Ini bukan yang kami inginkan.
Kami ingin, seperti orang sehat, bekerja dengan pangkalan. Kami mengambil dan menyiapkan pernyataan kami satu kali, lalu kami mengeksekusinya berkali-kali. Faktanya, berkali-kali - ini sekali seumur hidup aplikasi - aplikasi tersebut telah diurai. Dan kami menggunakan id pernyataan yang sama pada REST yang berbeda. Ini adalah tujuan kami.
Bagaimana kita bisa mencapai hal ini?
Ini sangat sederhana - tidak perlu menutup pernyataan. Kami menulisnya seperti ini: "persiapkan" "eksekusi".
Kalau kita luncurkan yang seperti ini, maka jelas ada yang meluap entah kemana. Jika kurang jelas, Anda bisa mencobanya. Mari kita menulis benchmark yang menggunakan metode sederhana ini. Buat pernyataan. Kami meluncurkannya pada beberapa versi driver dan menemukan bahwa driver tersebut mogok cukup cepat dengan hilangnya semua memori yang dimilikinya.
Jelas bahwa kesalahan tersebut mudah diperbaiki. Saya tidak akan membicarakannya. Namun menurut saya versi baru bekerja lebih cepat. Metodenya bodoh, tapi tetap saja.
Bagaimana cara bekerja yang benar? Apa yang perlu kita lakukan untuk ini?
Pada kenyataannya, aplikasi selalu menutup pernyataan. Di semua buku mereka mengatakan untuk menutupnya, jika tidak, memori akan bocor.
Dan PostgreSQL tidak tahu cara menyimpan kueri dalam cache. Setiap sesi harus membuat cache ini untuk dirinya sendiri.
Dan kami juga tidak ingin membuang waktu untuk menguraikannya.
Dan seperti biasa kita punya dua pilihan.
Opsi pertama adalah kita mengambilnya dan mengatakan bahwa mari kita bungkus semuanya dalam PgSQL. Ada cache di sana. Itu menyimpan semuanya dalam cache. Ini akan menjadi luar biasa. Kami melihat ini. Kami memiliki 100500 permintaan. Tidak bekerja. Kami tidak setuju untuk mengubah permintaan menjadi prosedur secara manual. Tidak tidak.
Kami memiliki pilihan kedua - ambil dan potong sendiri. Kami membuka sumbernya dan mulai memotong. Kami melihat dan melihat. Ternyata hal itu tidak terlalu sulit untuk dilakukan.
Ini muncul pada bulan Agustus 2015. Sekarang ada versi yang lebih modern. Dan semuanya bagus. Ini berfungsi dengan baik sehingga kami tidak mengubah apa pun di aplikasi. Dan kami bahkan berhenti berpikir ke arah PgSQL, artinya ini cukup bagi kami untuk mengurangi semua biaya overhead hingga hampir nol.
Oleh karena itu, pernyataan yang disiapkan Server diaktifkan pada eksekusi ke-5 untuk menghindari pemborosan memori dalam database pada setiap permintaan satu kali.
Anda mungkin bertanya – di mana nomornya? Apa yang kamu dapatkan? Dan disini saya tidak akan memberikan nomornya, karena setiap permintaan memilikinya sendiri-sendiri.
Kueri kami sedemikian rupa sehingga kami menghabiskan sekitar 20 milidetik untuk menguraikan kueri OLTP. Ada 0,5 milidetik untuk eksekusi, 20 milidetik untuk penguraian. Permintaan – 10 KiB teks, 170 baris rencana. Ini adalah permintaan OLTP. Ia meminta 1, 5, 10 baris, terkadang lebih.
Namun kami tidak ingin menyia-nyiakan 20 milidetik sama sekali. Kami menguranginya menjadi 0. Semuanya bagus.
Apa yang bisa Anda ambil dari sini? Jika Anda memiliki Java, maka ambil driver versi modern dan bergembiralah.
Jika Anda berbicara bahasa lain, pikirkan - mungkin Anda juga membutuhkannya? Karena dari sudut pandang bahasa akhir, misalnya, jika PL 8 atau Anda memiliki LibPQ, maka tidak jelas bagi Anda bahwa Anda membuang-buang waktu bukan untuk eksekusi, untuk parsing, dan ini patut untuk diperiksa. Bagaimana? Semuanya gratis.
Kecuali ada kesalahan dan beberapa keanehan. Dan kita akan membicarakannya sekarang. Sebagian besar akan membahas tentang arkeologi industri, tentang apa yang kami temukan, apa yang kami temukan.
Jika permintaan dihasilkan secara dinamis. Itu terjadi. Seseorang merekatkan string tersebut, menghasilkan kueri SQL.
Kenapa dia jahat? Ini buruk karena setiap kali kita mendapatkan string yang berbeda.
Dan kode hash dari string yang berbeda ini perlu dibaca lagi. Ini benar-benar tugas CPU - menemukan teks permintaan yang panjang bahkan dalam hash yang sudah ada tidaklah mudah. Oleh karena itu, kesimpulannya sederhana - jangan membuat permintaan. Simpan dalam satu variabel. Dan bersukacitalah.
Masalah selanjutnya. Tipe data itu penting. Ada ORM yang mengatakan bahwa tidak peduli jenis NULL apa yang ada, biarlah ada jenisnya. Jika Int, maka kita ucapkan setInt. Dan jika NULL, maka biarlah selalu VARCHAR. Dan apa bedanya pada akhirnya apa yang NULL itu ada? Basis data itu sendiri akan memahami segalanya. Dan gambar ini tidak berfungsi.
Dalam praktiknya, database tidak peduli sama sekali. Jika pertama kali Anda mengatakan bahwa ini adalah angka, dan kedua kalinya Anda mengatakan bahwa ini adalah VARCHAR, maka tidak mungkin untuk menggunakan kembali pernyataan yang disiapkan Server. Dan dalam hal ini, kita harus membuat ulang pernyataan kita.
Jika Anda menjalankan kueri yang sama, pastikan tipe data di kolom Anda tidak tertukar. Anda harus berhati-hati terhadap NULL. Ini adalah kesalahan umum yang kami alami setelah kami mulai menggunakan PreparedStatements
Oke, dihidupkan. Mungkin mereka mengambil supirnya. Dan produktivitas menurun. Segalanya menjadi buruk.
Bagaimana ini bisa terjadi? Apakah ini bug atau fitur? Sayangnya, tidak mungkin untuk memahami apakah ini bug atau fitur. Namun ada skenario yang sangat sederhana untuk mereproduksi masalah ini. Dia secara tak terduga menyergap kami. Dan itu terdiri dari pengambilan sampel secara harfiah dari satu tabel. Kami, tentu saja, memiliki lebih banyak permintaan seperti itu. Biasanya, mereka menyertakan dua atau tiga tabel, tetapi ada skenario pemutaran seperti itu. Ambil versi apa pun dari database Anda dan mainkan.
Intinya kita punya dua kolom yang masing-masing diindeks. Ada sejuta baris dalam satu kolom NULL. Dan kolom kedua hanya berisi 20 baris. Saat kami mengeksekusi tanpa variabel terikat, semuanya berfungsi dengan baik.
Jika kita mulai mengeksekusi dengan variabel terikat, yaitu kita mengeksekusi perintah "?" atau “$1” untuk permintaan kita, apa yang akhirnya kita dapatkan?
Eksekusi pertama seperti yang diharapkan. Yang kedua sedikit lebih cepat. Sesuatu telah di-cache. Ketiga, keempat, kelima. Lalu bang - dan sesuatu seperti itu. Dan parahnya hal ini terjadi pada eksekusi keenam. Siapa yang tahu bahwa enam eksekusi perlu dilakukan untuk memahami rencana eksekusi sebenarnya?
Siapa yang bersalah? Apa yang telah terjadi? Basis data berisi optimasi. Dan sepertinya dioptimalkan untuk kasus umum. Dan, oleh karena itu, mulai dari titik tertentu, dia beralih ke rencana umum, yang sayangnya, mungkin berubah menjadi berbeda. Bisa saja hasilnya sama, atau mungkin berbeda. Dan ada semacam nilai ambang batas yang mengarah pada perilaku ini.
Apa yang dapat Anda lakukan? Di sini, tentu saja, lebih sulit untuk berasumsi apa pun. Ada solusi sederhana yang kami gunakan. Ini +0, OFFSET 0. Tentunya Anda tahu solusi seperti itu. Kami hanya mengambilnya dan menambahkan "+0" ke permintaan dan semuanya baik-baik saja. Akan kutunjukkan padamu nanti.
Dan ada pilihan lain - lihat rencananya dengan lebih cermat. Pengembang tidak hanya harus menulis permintaan, tetapi juga mengatakan “jelaskan analisis” sebanyak 6 kali. Jika 5, itu tidak akan berhasil.
Dan ada opsi ketiga - menulis surat kepada peretas pgsql. Saya tulis, namun belum jelas apakah ini bug atau fitur.
Selagi kami memikirkan apakah ini bug atau fitur, mari kita perbaiki. Mari kita terima permintaan kita dan tambahkan "+0". Semuanya baik-baik saja. Dua simbol dan Anda bahkan tidak perlu memikirkan bagaimana atau apa itu. Sangat sederhana. Kami hanya melarang database menggunakan indeks pada kolom ini. Kami tidak memiliki indeks pada kolom “+0” dan hanya itu, database tidak menggunakan indeks, semuanya baik-baik saja.
Ini adalah aturan 6 penjelasan. Sekarang di versi saat ini Anda harus melakukannya 6 kali jika Anda memiliki variabel terikat. Jika Anda tidak memiliki variabel terikat, inilah yang kami lakukan. Dan pada akhirnya justru permintaan ini yang gagal. Itu bukanlah hal yang rumit.
Tampaknya, seberapa besar kemungkinannya? Ada bug di sini, ada bug di sana. Sebenarnya bugnya ada dimana-mana.
Mari kita lihat lebih dekat. Misalnya, kami memiliki dua skema. Skema A dengan tabel S dan diagram B dengan tabel S. Kueri – memilih data dari tabel. Apa yang akan kita dapatkan dalam kasus ini? Kita akan mendapat kesalahan. Kita akan mendapatkan semua hal di atas. Aturannya adalah - bug ada di mana-mana, kita akan mengalami semua hal di atas.
Sekarang pertanyaannya adalah: “Mengapa?” Tampaknya ada dokumentasi bahwa jika kita memiliki skema, maka ada variabel "search_path" yang memberitahu kita di mana mencari tabel. Tampaknya ada sebuah variabel.
Apa masalahnya? Masalahnya adalah pernyataan yang disiapkan server tidak mencurigai bahwa search_path dapat diubah oleh seseorang. Nilai ini tetap konstan untuk database. Dan beberapa bagian mungkin tidak memiliki arti baru.
Tentu saja, ini tergantung pada versi yang Anda uji. Tergantung pada seberapa serius perbedaan tabel Anda. Dan versi 9.1 hanya akan mengeksekusi query lama. Versi baru mungkin menangkap bug tersebut dan memberi tahu Anda bahwa Anda memiliki bug.
Bagaimana cara mengobatinya? Ada resep sederhana - jangan lakukan itu. Tidak perlu mengubah search_path saat aplikasi sedang berjalan. Jika Anda berubah, lebih baik buat koneksi baru.
Anda bisa berdiskusi, yaitu membuka, berdiskusi, menambah. Mungkin kita bisa meyakinkan pengembang database bahwa ketika seseorang mengubah suatu nilai, database harus memberitahu klien tentang hal ini: “Lihat, nilai Anda telah diperbarui di sini. Mungkin Anda perlu mengatur ulang pernyataan dan membuatnya kembali?” Sekarang database berperilaku diam-diam dan tidak melaporkan dengan cara apa pun bahwa pernyataan telah berubah di suatu tempat di dalam.
Dan saya tekankan lagi - ini adalah sesuatu yang tidak khas untuk Java. Kita akan melihat hal yang sama di PL/pgSQL satu lawan satu. Tapi itu akan direproduksi di sana.
Mari kita coba beberapa pemilihan data lagi. Kami memilih dan memilih. Kami memiliki tabel dengan sejuta baris. Setiap baris berukuran satu kilobyte. Sekitar satu gigabyte data. Dan kami memiliki memori kerja di mesin Java sebesar 128 megabyte.
Kami, seperti yang direkomendasikan di semua buku, menggunakan pemrosesan aliran. Artinya, kita membuka resultSet dan membaca data dari sana sedikit demi sedikit. Apakah ini akan berhasil? Akankah itu hilang dari ingatan? Maukah kamu membaca sedikit? Mari percaya pada database, percaya pada Postgres. Kami tidak mempercayainya. Apakah kita akan kehilangan OutOFMemory? Siapa yang mengalami OutOfMemory? Siapa yang berhasil memperbaikinya setelah itu? Seseorang berhasil memperbaikinya.
Jika Anda memiliki sejuta baris, Anda tidak bisa sembarangan memilih. OFFSET/LIMIT diperlukan. Siapa yang mendukung opsi ini? Dan siapa yang mendukung bermain dengan autoCommit?
Di sini, seperti biasa, pilihan yang paling tidak terduga ternyata benar. Dan jika Anda tiba-tiba mematikan autoCommit, itu akan membantu. Mengapa demikian? Sains tidak mengetahui hal ini.
Namun secara default, semua klien yang terhubung ke database Postgres mengambil seluruh data. PgJDBC tidak terkecuali dalam hal ini; ia memilih semua baris.
Ada variasi pada tema FetchSize, yaitu Anda dapat mengatakan pada tingkat pernyataan terpisah bahwa di sini, silakan pilih data sebanyak 10, 50. Namun ini tidak akan berfungsi sampai Anda mematikan autoCommit. Mematikan autoCommit - itu mulai berfungsi.
Namun menelusuri kode dan menyetel setFetchSize di mana pun tidak nyaman. Oleh karena itu, kami membuat pengaturan yang akan menyatakan nilai default untuk seluruh koneksi.
Itu yang kami katakan. Parameter telah dikonfigurasi. Dan apa yang kami dapatkan? Jika kita memilih jumlah yang kecil, misalnya jika kita memilih 10 baris sekaligus, maka kita mempunyai biaya overhead yang sangat besar. Oleh karena itu, nilai ini harus ditetapkan sekitar seratus.
Idealnya, tentu saja, Anda masih harus mempelajari cara membatasinya dalam byte, tetapi resepnya begini: setel defaultRowFetchSize ke lebih dari seratus dan berbahagialah.
Mari beralih ke memasukkan data. Penyisipan lebih mudah, ada opsi berbeda. Misalnya, MASUKKAN, NILAI. Ini adalah pilihan yang bagus. Anda bisa mengucapkan “MASUKKAN PILIH”. Dalam prakteknya adalah hal yang sama. Tidak ada perbedaan dalam kinerja.
Buku mengatakan bahwa Anda perlu menjalankan pernyataan Batch, buku mengatakan bahwa Anda dapat menjalankan perintah yang lebih kompleks dengan beberapa tanda kurung. Dan Postgres memiliki fitur luar biasa - Anda dapat melakukan COPY, yaitu melakukannya lebih cepat.
Jika Anda mengukurnya, Anda dapat kembali membuat beberapa penemuan menarik. Bagaimana kita ingin ini berhasil? Kami tidak ingin mengurai atau menjalankan perintah yang tidak perlu.
Dalam praktiknya, TCP tidak mengizinkan kami melakukan hal ini. Jika klien sibuk mengirimkan permintaan, maka database tidak membaca permintaan tersebut dalam upaya mengirimkan tanggapan kepada kami. Hasil akhirnya adalah klien menunggu database membaca permintaan, dan database menunggu klien membaca responsnya.
Oleh karena itu, klien terpaksa mengirimkan paket sinkronisasi secara berkala. Interaksi jaringan ekstra, buang-buang waktu ekstra.
Dan semakin banyak kita menambahkannya, semakin buruk jadinya. Pengemudi cukup pesimis dan cukup sering menambahkannya, sekitar sekali setiap 200 baris, tergantung pada ukuran garis, dll.
Kebetulan Anda mengoreksi hanya satu baris dan semuanya akan dipercepat 10 kali lipat. Itu terjadi. Mengapa? Seperti biasa, konstanta seperti ini telah digunakan di suatu tempat. Dan nilai “128” berarti tidak menggunakan batching.
Ada baiknya ini tidak disertakan dalam versi resmi. Ditemukan sebelum rilis dimulai. Semua arti yang saya berikan didasarkan pada versi modern.
Mari kita mencobanya. Kami mengukur InsertBatch dengan sederhana. Kami mengukur InsertBatch beberapa kali, yaitu hal yang sama, tetapi ada banyak nilainya. Langkah yang rumit. Tidak semua orang bisa melakukan ini, tapi ini adalah langkah yang sederhana, jauh lebih mudah daripada COPY.
Anda dapat melakukan SALINAN.
Dan Anda dapat melakukan ini pada struktur. Deklarasikan tipe default Pengguna, lewati array dan INSERT langsung ke tabel.
Jika Anda membuka tautan: pgjdbc/ubenchmsrk/InsertBatch.java, maka kode ini ada di GitHub. Anda dapat melihat secara spesifik permintaan apa yang dihasilkan di sana. Tidak masalah.
Kami meluncurkan. Dan hal pertama yang kami sadari adalah tidak mungkin menggunakan batch. Semua opsi pengelompokan adalah nol, yaitu waktu eksekusi praktis nol dibandingkan dengan eksekusi satu kali.
Kami memasukkan data. Ini adalah meja yang sangat sederhana. Tiga kolom. Dan apa yang kita lihat di sini? Kami melihat bahwa ketiga opsi ini kira-kira sebanding. Dan COPY tentu saja lebih baik.
Ini adalah saat kita memasukkan potongan. Ketika kami mengatakan bahwa satu nilai VALUES, dua nilai VALUES, tiga nilai VALUES, atau kami menunjukkan 10 di antaranya dipisahkan dengan koma. Sekarang ini hanya horizontal. 1, 2, 4, 128. Terlihat bahwa Batch Insert yang digambar dengan warna biru membuatnya merasa jauh lebih baik. Artinya, ketika Anda memasukkan satu per satu atau bahkan ketika Anda memasukkan empat sekaligus, itu menjadi dua kali lebih baik, hanya karena kita menjejalkan lebih banyak ke dalam VALUES. Lebih sedikit operasi EXECUTE.
Menggunakan COPY pada volume kecil sangatlah tidak menjanjikan. Saya bahkan tidak menggambar pada dua yang pertama. Mereka pergi ke surga, yaitu angka hijau untuk COPY.
COPY harus digunakan ketika Anda memiliki setidaknya seratus baris data. Biaya pembukaan koneksi ini besar. Dan sejujurnya, saya tidak menggali ke arah ini. Saya mengoptimalkan Batch, tetapi tidak COPY.
Apa yang kita lakukan selanjutnya? Kami mencobanya. Kami memahami bahwa kami perlu menggunakan struktur atau pemandian cerdas yang menggabungkan beberapa makna.
Apa yang dapat Anda petik dari laporan hari ini?
- PreparedStatement adalah segalanya bagi kami. Ini memberi banyak produktivitas. Ini menghasilkan kegagalan besar pada salep.
- Dan Anda perlu melakukan EXPLAIN ANALYZE sebanyak 6 kali.
- Dan kita perlu mengencerkan OFFSET 0, dan trik seperti +0 untuk memperbaiki persentase sisa kueri bermasalah kita.
Sumber: www.habr.com