Hey.
Nama saya Vanya dan saya seorang pengembang Java. Kebetulan saya banyak bekerja dengan PostgreSQL - menyiapkan database, mengoptimalkan struktur, kinerja, dan memainkan sedikit DBA di akhir pekan.
Baru-baru ini saya telah merapikan beberapa database di layanan mikro kami dan menulis perpustakaan Java , yang membuat pekerjaan ini lebih mudah, menghemat waktu saya dan membantu saya menghindari beberapa kesalahan umum yang dilakukan oleh pengembang. Perpustakaan inilah yang akan kita bicarakan hari ini.
Penolakan tanggung jawab
Versi utama PostgreSQL yang saya gunakan adalah 10. Semua kueri SQL yang saya gunakan juga diuji pada versi 11. Versi minimum yang didukung adalah 9.6.
prasejarah
Semuanya dimulai hampir setahun yang lalu dengan situasi yang aneh bagi saya: pembuatan indeks kompetitif tiba-tiba berakhir dengan kesalahan. Indeks itu sendiri, seperti biasa, tetap berada di database dalam keadaan tidak valid. Analisis log menunjukkan kekurangan . Dan kita berangkat... Menggali lebih dalam, saya menemukan sejumlah besar masalah dalam konfigurasi database dan, sambil menyingsingkan lengan baju, mulai memperbaikinya dengan kilatan cahaya di mata saya.
Masalah pertama - konfigurasi default
Mungkin semua orang sudah bosan dengan metafora tentang Postgres, yang bisa dijalankan di pembuat kopi, tapi... konfigurasi defaultnya benar-benar menimbulkan sejumlah pertanyaan. Minimal, perlu diperhatikan maintenance_work_mem, temp_file_limit, pernyataan_waktu habis ΠΈ lock_timeout.
Dalam kasus kami maintenance_work_mem adalah default 64 MB, dan temp_file_limit sekitar 2 GB - kami tidak memiliki cukup memori untuk membuat indeks pada tabel besar.
Oleh karena itu, dalam pg-indeks-kesehatan Saya mengumpulkan serangkaian , menurut saya, parameter yang harus dikonfigurasi untuk setiap database.
Masalah kedua - duplikat indeks
Basis data kami ada di drive SSD, dan kami menggunakannya HA-konfigurasi dengan beberapa pusat data, host utama dan n-jumlah replika. Ruang disk adalah sumber daya yang sangat berharga bagi kami; itu tidak kalah pentingnya dengan kinerja dan konsumsi CPU. Oleh karena itu, di satu sisi, kami memerlukan indeks untuk membaca cepat, dan di sisi lain, kami tidak ingin melihat indeks yang tidak perlu di database, karena indeks tersebut memakan ruang dan memperlambat pembaruan data.
Dan sekarang, setelah memulihkan semuanya dan sudah cukup melihat , saya memutuskan untuk mengadakan pembersihan βhebatβ. Ternyata developer tidak suka membaca dokumentasi database. Mereka tidak terlalu menyukainya. Karena itu, dua kesalahan umum muncul - indeks yang dibuat secara manual pada kunci utama dan indeks βmanualβ serupa pada kolom unik. Faktanya adalah mereka tidak diperlukan - Postgres akan melakukan semuanya sendiri. Indeks tersebut dapat dihapus dengan aman, dan diagnostik telah muncul untuk tujuan ini .
Masalah ketiga - indeks berpotongan
Kebanyakan pengembang pemula membuat indeks pada satu kolom. Secara bertahap, setelah benar-benar mengalami bisnis ini, orang-orang mulai mengoptimalkan kueri mereka dan menambahkan indeks yang lebih kompleks yang mencakup beberapa kolom. Ini adalah bagaimana indeks pada kolom muncul A, A + B, A+B+C dan seterusnya. Dua indeks pertama dapat dibuang dengan aman, karena merupakan awalan dari indeks ketiga. Ini juga menghemat banyak ruang disk dan ada diagnostik untuk ini .
Masalah keempat - kunci asing tanpa indeks
Postgres memungkinkan Anda membuat batasan kunci asing tanpa menentukan indeks pendukung. Dalam banyak situasi, hal ini tidak menjadi masalah, dan bahkan mungkin tidak muncul dengan sendirinya... Untuk saat ini...
Hal yang sama terjadi pada kami: hanya saja suatu saat suatu pekerjaan, yang berjalan sesuai jadwal dan membersihkan database pesanan pengujian, mulai βditambahkanβ kepada kami oleh host master. CPU dan IO menjadi sia-sia, permintaan melambat dan waktu habis, layanannya lima ratus. Analisis cepat menunjukkan bahwa pertanyaan seperti:
delete from <table> where id in (β¦)Dalam hal ini, tentu saja, ada indeks berdasarkan id di tabel target, dan sangat sedikit catatan yang dihapus sesuai kondisi. Sepertinya semuanya harusnya berhasil, tapi sayangnya, ternyata tidak.
Yang luar biasa datang untuk menyelamatkan menjelaskan menganalisis dan mengatakan bahwa selain menghapus record di tabel target, ada juga pemeriksaan integritas referensial, dan di salah satu tabel terkait pemeriksaan ini gagal pemindaian berurutan karena kurangnya indeks yang sesuai. Maka lahirlah diagnostik .
Masalah lima β nilai nol dalam indeks
Secara default, Postgres menyertakan nilai null dalam indeks btree, tetapi biasanya nilai tersebut tidak diperlukan di sana. Oleh karena itu, saya dengan tekun mencoba membuang nulls ini (diagnostics ), membuat indeks parsial pada kolom yang dapat dibatalkan berdasarkan jenisnya where <A> is not null. Dengan cara ini saya dapat mengurangi ukuran salah satu indeks kami dari 1877 MB menjadi 16 KB. Dan di salah satu layanan, ukuran database berkurang total 16% (sebesar 4.3 GB dalam angka absolut) karena pengecualian nilai nol dari indeks. Penghematan besar pada ruang disk dengan modifikasi yang sangat sederhana. π
Masalah keenam β kurangnya kunci utama
Karena sifat mekanismenya situasi seperti ini mungkin terjadi ketika ukuran tabel Anda berkembang pesat karena banyaknya catatan mati. Saya secara naif percaya bahwa ini tidak akan mengancam kami, dan ini tidak akan terjadi pada basis kami, karena kami, wow!!!, adalah pengembang biasa... Betapa bodoh dan naifnya saya...
Suatu hari, suatu migrasi yang luar biasa mengambil dan memperbarui semua catatan dalam tabel besar dan digunakan secara aktif. Kami tiba-tiba mendapatkan +100 GB untuk ukuran tabel. Sungguh memalukan, tapi kesialan kami tidak berakhir di situ. Setelah autovacuum di tabel ini berakhir 15 jam kemudian, menjadi jelas bahwa lokasi fisik tidak akan kembali. Kami tidak dapat menghentikan layanan dan menjadikan VACUUM FULL, jadi kami memutuskan untuk menggunakan . Dan ternyata begitu pg_repack tidak tahu cara memproses tabel tanpa kunci utama atau batasan keunikan lainnya, dan tabel kami tidak memiliki kunci utama. Maka lahirlah diagnostik .
Dalam versi perpustakaan 0.1.5 Kemampuan untuk mengumpulkan data dari tabel dan indeks yang membengkak dan meresponsnya secara tepat waktu telah ditambahkan.
Masalah tujuh dan delapan - indeks tidak mencukupi dan indeks tidak digunakan
Dua diagnostik berikut adalah: ΠΈ β muncul dalam bentuk akhirnya relatif baru-baru ini. Intinya tidak bisa diambil dan ditambah begitu saja.
Seperti yang sudah saya tulis, kami menggunakan konfigurasi dengan beberapa replika, dan beban membaca pada host yang berbeda pada dasarnya berbeda. Akibatnya, beberapa tabel dan indeks pada beberapa host praktis tidak digunakan, dan untuk analisis Anda perlu mengumpulkan statistik dari semua host di cluster. Hal ini juga diperlukan pada setiap host di cluster; Anda tidak dapat melakukan ini hanya pada master.
Pendekatan ini memungkinkan kami menghemat beberapa puluh gigabyte dengan menghapus indeks yang tidak pernah digunakan, serta menambahkan indeks yang hilang ke tabel yang jarang digunakan.
Sebagai kesimpulan
Tentu saja, hampir semua diagnostik dapat dikonfigurasi . Dengan cara ini, Anda dapat dengan cepat menerapkan pemeriksaan pada aplikasi Anda, mencegah munculnya kesalahan baru, dan kemudian secara bertahap memperbaiki kesalahan lama.
Beberapa diagnostik dapat dilakukan dalam pengujian fungsional segera setelah meluncurkan migrasi database. Dan ini mungkin salah satu fitur paling canggih di perpustakaan saya. Contoh penggunaan dapat ditemukan di .
Masuk akal untuk melakukan pemeriksaan untuk indeks yang tidak digunakan atau hilang, serta untuk mengasapi, hanya pada database nyata. Nilai yang dikumpulkan dapat dicatat atau dikirim ke sistem pemantauan.
Saya sangat berharap itu pg-indeks-kesehatan akan berguna dan diminati. Anda juga dapat berkontribusi pada pengembangan perpustakaan dengan melaporkan masalah yang Anda temukan dan menyarankan diagnostik baru.
Sumber: www.habr.com