Hi
Nama saya Vanya dan saya seorang pembangun Java. Kebetulan saya banyak bekerja dengan PostgreSQL - menyediakan pangkalan data, mengoptimumkan struktur, prestasi, dan bermain sedikit DBA pada hujung minggu.
Baru-baru ini saya telah mengemas beberapa pangkalan data dalam perkhidmatan mikro kami dan menulis perpustakaan java , yang menjadikan kerja ini lebih mudah, menjimatkan masa saya dan membantu saya mengelakkan beberapa kesilapan biasa yang dilakukan oleh pembangun. Perpustakaan inilah yang akan kita bicarakan hari ini.
Penafian
Versi utama PostgreSQL yang saya gunakan ialah 10. Semua pertanyaan SQL yang saya gunakan juga diuji pada versi 11. Versi minimum yang disokong ialah 9.6.
prasejarah
Semuanya bermula hampir setahun yang lalu dengan situasi yang pelik bagi saya: penciptaan indeks yang kompetitif secara tiba-tiba berakhir dengan ralat. Indeks itu sendiri, seperti biasa, kekal dalam pangkalan data dalam keadaan tidak sah. Analisis log menunjukkan kekurangan . Dan kita pergi... Menggali lebih dalam, saya menemui banyak masalah dalam konfigurasi pangkalan data dan, menyingsing lengan baju saya, mula membetulkannya dengan cahaya mata saya.
Masalah satu - konfigurasi lalai
Mungkin semua orang agak bosan dengan metafora tentang Postgres, yang boleh dijalankan pada pembuat kopi, tetapi... konfigurasi lalai benar-benar menimbulkan beberapa soalan. Sekurang-kurangnya, ia patut diberi perhatian penyelenggaraan_kerja_mem, temp_file_limit, penyata_masa tamat ΠΈ lock_timeout.
Dalam kes kita penyelenggaraan_kerja_mem ialah 64 MB lalai, dan temp_file_limit sesuatu sekitar 2 GB - kami tidak mempunyai cukup memori untuk mencipta indeks pada meja besar.
Oleh itu, di pg-index-health Saya mengumpul satu siri , pada pendapat saya, parameter yang harus dikonfigurasikan untuk setiap pangkalan data.
Masalah dua - indeks pendua
Pangkalan data kami hidup pada pemacu SSD, dan kami gunakan HA-konfigurasi dengan pelbagai pusat data, hos induk dan n-bilangan replika. Ruang cakera adalah sumber yang sangat berharga untuk kami; ia tidak kurang penting daripada prestasi dan penggunaan CPU. Oleh itu, di satu pihak, kita memerlukan indeks untuk membaca pantas, dan sebaliknya, kita tidak mahu melihat indeks yang tidak diperlukan dalam pangkalan data, kerana ia memakan ruang dan memperlahankan pengemaskinian data.
Dan sekarang, setelah memulihkan segala-galanya dan sudah cukup melihat , saya memutuskan untuk menganjurkan pembersihan "hebat". Ternyata pembangun tidak suka membaca dokumentasi pangkalan data. Mereka tidak suka sangat. Disebabkan ini, dua ralat biasa timbul - indeks yang dibuat secara manual pada kunci utama dan indeks "manual" yang serupa pada lajur unik. Hakikatnya ialah mereka tidak diperlukan - Postgres akan melakukan segala-galanya sendiri. Indeks sedemikian boleh dipadamkan dengan selamat, dan diagnostik telah muncul untuk tujuan ini .
Masalah tiga - indeks bersilang
Kebanyakan pembangun pemula mencipta indeks pada satu lajur. Secara beransur-ansur, setelah mengalami perniagaan ini secara menyeluruh, orang mula mengoptimumkan pertanyaan mereka dan menambah indeks yang lebih kompleks yang merangkumi beberapa lajur. Beginilah cara indeks pada lajur muncul A, A + B, A + B + C dan sebagainya. Dua indeks pertama ini boleh dibuang dengan selamat, kerana ia adalah awalan bagi yang ketiga. Ini juga menjimatkan banyak ruang cakera dan terdapat diagnostik untuk ini .
Masalah empat - kunci asing tanpa indeks
Postgres membolehkan anda mencipta kekangan kunci asing tanpa menentukan indeks sokongan. Dalam banyak situasi ini bukan masalah, malah mungkin tidak nyata... Buat sementara waktu...
Ia adalah sama dengan kami: cuma pada satu ketika suatu kerja, berjalan mengikut jadual dan mengosongkan pangkalan data pesanan ujian, mula "ditambahkan" kepada kami oleh hos induk. CPU dan IO menjadi sia-sia, permintaan menjadi perlahan dan tamat masa, perkhidmatannya adalah lima ratus. Analisis cepat menunjukkan bahawa pertanyaan seperti:
delete from <table> where id in (β¦)Dalam kes ini, sudah tentu, terdapat indeks mengikut id dalam jadual sasaran, dan sangat sedikit rekod telah dipadamkan mengikut syarat. Nampaknya semuanya sepatutnya berfungsi, tetapi, malangnya, ia tidak.
Yang indah datang untuk menyelamatkan menerangkan menganalisis dan berkata bahawa selain memadamkan rekod dalam jadual sasaran, terdapat juga semakan integriti rujukan, dan pada salah satu jadual berkaitan semakan ini gagal imbasan berurutan kerana ketiadaan indeks yang sesuai. Oleh itu diagnostik dilahirkan .
Masalah lima β nilai nol dalam indeks
Secara lalai, Postgres menyertakan nilai nol dalam indeks btree, tetapi ia biasanya tidak diperlukan di sana. Oleh itu, saya dengan tekun cuba membuang nol ini (diagnostik ), mencipta indeks separa pada lajur yang boleh dibatalkan mengikut jenis where <A> is not null. Dengan cara ini saya dapat mengurangkan saiz salah satu indeks kami daripada 1877 MB kepada 16 KB. Dan dalam salah satu perkhidmatan, saiz pangkalan data berkurangan secara keseluruhan sebanyak 16% (sebanyak 4.3 GB dalam nombor mutlak) disebabkan oleh pengecualian nilai nol daripada indeks. Penjimatan besar dalam ruang cakera dengan pengubahsuaian yang sangat mudah. π
Masalah enam β kekurangan kunci utama
Oleh kerana sifat mekanisme keadaan seperti ini mungkin berlaku apabila saiz meja anda berkembang pesat disebabkan oleh sejumlah besar rekod mati. Saya secara naif percaya bahawa ini tidak akan mengancam kami, dan ini tidak akan berlaku kepada pangkalan kami, kerana kami, wow!!!, adalah pemaju biasa... Betapa bodoh dan naifnya saya...
Pada suatu hari, satu penghijrahan yang indah telah mengambil dan mengemas kini semua rekod dalam jadual yang besar dan digunakan secara aktif. Kami mendapat +100 GB ke saiz jadual secara tiba-tiba. Sungguh memalukan, tetapi kemalangan kami tidak berakhir di situ. Selepas autovakum di atas meja ini tamat 15 jam kemudian, menjadi jelas bahawa lokasi fizikal tidak akan kembali. Kami tidak dapat menghentikan perkhidmatan dan membuat VACUUM PENUH, jadi kami memutuskan untuk menggunakan . Dan kemudian ternyata begitu pg_repack tidak tahu cara memproses jadual tanpa kunci utama atau kekangan keunikan lain, dan jadual kami tidak mempunyai kunci utama. Oleh itu diagnostik dilahirkan .
Dalam versi perpustakaan 0.1.5 Keupayaan untuk mengumpul data daripada lonjakan jadual dan indeks dan bertindak balas terhadapnya tepat pada masanya telah ditambah.
Masalah tujuh dan lapan - indeks tidak mencukupi dan indeks tidak digunakan
Dua diagnostik berikut ialah: ΠΈ β muncul dalam bentuk terakhir mereka agak baru-baru ini. Intinya ialah mereka tidak boleh diambil dan ditambah begitu sahaja.
Seperti yang telah saya tulis, kami menggunakan konfigurasi dengan beberapa replika, dan beban bacaan pada hos yang berbeza pada asasnya berbeza. Akibatnya, keadaan ternyata bahawa beberapa jadual dan indeks pada beberapa hos praktikalnya tidak digunakan, dan untuk analisis anda perlu mengumpul statistik daripada semua hos dalam kelompok. Ini juga perlu pada setiap hos dalam kelompok; anda tidak boleh melakukan ini hanya pada tuan.
Pendekatan ini membolehkan kami menyimpan beberapa puluh gigabait dengan mengalih keluar indeks yang tidak pernah digunakan, serta menambah indeks yang hilang pada jadual yang jarang digunakan.
Sebagai kesimpulan
Sudah tentu, untuk hampir semua diagnostik anda boleh mengkonfigurasi . Dengan cara ini, anda boleh melaksanakan semakan dengan cepat dalam aplikasi anda, menghalang ralat baharu daripada muncul, dan kemudian membetulkan ralat lama secara beransur-ansur.
Sesetengah diagnostik boleh dilakukan dalam ujian berfungsi serta-merta selepas melancarkan migrasi pangkalan data. Dan ini mungkin salah satu ciri pustaka saya yang paling berkuasa. Contoh penggunaan boleh didapati di .
Adalah wajar untuk melakukan semakan untuk indeks yang tidak digunakan atau hilang, serta untuk mengasapi, hanya pada pangkalan data sebenar. Nilai yang dikumpul boleh direkodkan dalam atau dihantar ke sistem pemantauan.
Saya sangat berharap itu pg-index-health akan berguna dan dalam permintaan. Anda juga boleh menyumbang kepada pembangunan perpustakaan dengan melaporkan masalah yang anda temui dan mencadangkan diagnostik baharu.
Sumber: www.habr.com