Apa yang harus dilakukan ketika ada aplikasi sumber tertutup yang tidak mengakses database secara optimal? Bagaimana cara menyetel kueri tanpa mengubah aplikasi, dan mungkin database itu sendiri?
Jika Anda belum mengajukan pertanyaan seperti itu, Anda adalah DBA yang sangat sukses dan teliti.
Nah, jika ditanya, izinkan saya berbagi penderitaan dan pengalaman.
Perlu menyimpan lebih banyak data, atau mengatur tugas
Anda dapat dengan aman menggulir bagian ini jika riwayat masalah tidak menarik.
Awalnya, kami memiliki sistem berpemilik yang mem-parsing datanya dari format tertutup ke dalam database PostgreSQL, tempat kami membaca, menganalisis, dan memproses data ini.
Selain itu, alat-alat sistem ini juga menggunakan basis ini untuk operasi tertentu, sehingga untuk meninggalkannya dan membuat salinan dengan strukturnya sepertinya ide yang sia-sia.
Secara default, sistem secara otomatis menghapus catatan yang lebih lama dari satu minggu, jadi tidak ada masalah kinerja di stand.
Namun, kita perlu menyimpan data lebih lama, selama ada cukup ruang pada disk server. Yah, sangat disarankan untuk tidak kehilangan akses ke data ini dan masih menggunakan alat built-in dari sistem, bahkan untuk data lama.
Oleh karena itu, keputusan yang jelas adalah membuat partisi dan pemicu pada operasi INSERT. Fokusnya cukup sederhana dan efektif. Data dimasukkan ke dalam partisi yang diperlukan, penghapusan catatan lama dinonaktifkan, semuanya tampak baik-baik saja.
Sampai beberapa tahun telah berlalu dan data belum terakumulasi dengan baik.
Di sini, "tiba-tiba" ternyata permintaan yang dibuat oleh toolkit dari sistem yang digunakan tidak membatasi pemilihan berdasarkan tanggal (atau lebih tepatnya, membatasi tidak ke bidang yang sesuai dengan partisi dilakukan). Yaitu jika kita mencari sesuatu - pencarian berjalan di semua partisi. Operasi UPDATE juga mulai melambat - dalam kondisi hanya ada ID-shnik yang digunakan.
Akibatnya, permintaan dieksekusi untuk waktu yang lama, menarik semua permintaan lainnya, beban tumbuh dengan cepat.
Tentu saja, hal pertama yang terlintas dalam pikiran adalah menghubungi pengembang.
Namun, dalam banyak kasus tidak lagi berada di zona akses, atau akan meminta biaya sistem lain untuk penyelesaian dalam beberapa jalur.
Karena itu, muncul ide bahwa mungkin sudah ada semacam proxy yang dapat membantu kita.
Kami membutuhkan proxy
Googling cepat tidak menemukan jawaban yang jelas untuk pertanyaan tentang bagaimana menulis ulang kueri masuk di sisi PostgreSQL atau beberapa perangkat lunak pihak ketiga.
Oleh karena itu (well, hanya untuk bersenang-senang juga, tentu saja) perangkat lunak yang cukup sederhana ditulis yang menerima koneksi dari klien dan proksi mereka di PostgreSQL. Pada saat yang sama, kueri SQL yang masuk dibaca, dan, jika perlu, diganti.
Berbagi tautan ke githubMeskipun saya tidak membuat paket biner, tangan saya tidak dapat menjangkau. Namun perakitannya cukup sederhana. Semuanya ditulis dalam C ++ / Qt, karena Saya sudah menulis ini sejak lama ...
Konfigurasi ini cukup sederhana:
Tentukan antarmuka dan port mana yang ingin didengarkan:
listen_address=0.0.0.0 listen_port=5433
Kami memaksa perangkat lunak yang lalai untuk terhubung ke alamat yang ditentukan alih-alih langsung terhubung ke server PostgreSQL.
Kami menuliskan tempat untuk meneruskan koneksi (dalam contoh ini, proksi terletak pada mesin yang sama dengan server PostgreSQL):
dst_address=127.0.0.1 dst_port=5432
Kami menetapkan ekspresi reguler untuk menangkap permintaan yang diinginkan:
query = SELECT \* FROM tablename WHERE (.+)
Kami mengatakan bahwa kami perlu menulis ulang:
action = rewrite
Kami mengatakan bagaimana menulis ulang:
rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)
Dalam contoh ini, kami menambahkan filter ke kondisi kueri oleh kolom dengan tanggal, yang menunjukkan bahwa kami hanya tertarik pada catatan untuk bulan lalu.
Orang bisa menulis seperti ini:
rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)
Tetapi kemudian permintaan tersebut tidak akan optimal karena kehadiran fungsi now () - pencarian akan tetap dilakukan pada semua partisi. Untuk mencari hanya di yang diperlukan, Anda harus menentukan nilai konstan. Karenanya, proksi kami mengganti timestamp dengan pergeseran satu bulan alih-alih konstruk $ (sekarang-1M).
Hasil (dari log):
ORIGINAL query: SELECT * FROM tablename WHERE id=1; MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;
Jadi, pada dasarnya, mungkin untuk mengganti permintaan apa pun. Respons dari server tidak berubah dan dikirim ke klien apa adanya. Dengan cara ini, penundaan transmisi diminimalkan. Selain itu, aplikasi biasanya menunggu respons dengan format tertentu, sehingga tidak diinginkan untuk mengubah kumpulan kolom dalam permintaan dan respons.
Dimungkinkan juga untuk dengan mudah mencetak semua permintaan yang menarik ke log:
query = .+ action = log
Repositori memiliki konfigurasi dengan contoh dan deskripsi yang lebih rinci.
Omong-omong, mudah untuk menentukan seberapa baik pengembang menulis dengan benar untuk bekerja dengan database. Misalnya, jika Anda melihat permintaan yang sering dieksekusi, maka sudah saatnya seseorang untuk merokok manual.
INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')
Seharusnya seperti ini:
INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)
Sayangnya, sejauh ini proksi kami tidak dapat menulis dengan cara ini: / tapi ini tidak sulit dilakukan. Mungkin di masa depan akan memungkinkan untuk menulis ulang permintaan pertama ke yang kedua.
Ya, poin pentingnya adalah bahwa SSL belum didukung, sehingga semua koneksi dari klien ke proxy akan tanpa enkripsi.
Saya akan senang memberikan komentar dan komentar.
Jika ada minat aktif pengguna, mungkin saya akan mengembangkan proyek lebih lanjut.
Anda dapat menambahkan pekerjaan dengan database lain.