Seni bina perkhidmatan mikro, seperti segala-galanya di dunia ini, mempunyai kebaikan dan keburukan. Sesetengah proses menjadi lebih mudah dengannya, yang lain lebih sukar. Dan demi kelajuan perubahan dan skalabiliti yang lebih baik, anda perlu membuat pengorbanan. Salah satunya ialah kerumitan analisis. Jika dalam monolit semua analisis operasi boleh dikurangkan kepada pertanyaan SQL kepada replika analitik, maka dalam seni bina pelbagai perkhidmatan setiap perkhidmatan mempunyai pangkalan data sendiri dan nampaknya satu pertanyaan tidak mencukupi (atau mungkin akan?). Bagi mereka yang berminat dengan cara kami menyelesaikan masalah analisis operasi di syarikat kami dan cara kami belajar untuk hidup dengan penyelesaian ini - dialu-alukan.

Nama saya Pavel Sivash, di DomClick saya bekerja dalam pasukan yang bertanggungjawab untuk mengekalkan gudang data analisis. Secara konvensional, aktiviti kami boleh dikaitkan dengan kejuruteraan data, tetapi, sebenarnya, julat tugas adalah lebih luas. Terdapat ETL / ELT kejuruteraan data standard, sokongan dan penyesuaian alat analisis data dan pembangunan alat mereka sendiri. Khususnya, untuk pelaporan operasi, kami memutuskan untuk "berpura-pura" bahawa kami mempunyai monolit dan memberi penganalisis satu pangkalan data yang akan mengandungi semua data yang mereka perlukan.

Secara umum, kami mempertimbangkan pilihan yang berbeza. Ia adalah mungkin untuk membina repositori penuh - kami juga mencuba, tetapi, sejujurnya, kami tidak dapat berkawan dengan perubahan yang agak kerap dalam logik dengan proses yang agak perlahan membina repositori dan membuat perubahan padanya ( jika ada yang berjaya, tulis dalam komen bagaimana). Anda boleh berkata kepada penganalisis: "Kawan-kawan, pelajari ular sawa dan pergi ke talian analisis," tetapi ini adalah keperluan pengambilan tambahan, dan nampaknya ini harus dielakkan jika boleh. Kami memutuskan untuk mencuba menggunakan teknologi FDW (Foreign Data Wrapper): sebenarnya, ini adalah dblink standard, yang berada dalam standard SQL, tetapi dengan antara muka yang lebih mudah. Atas dasar itu, kami membuat keputusan, yang akhirnya berakar umbi, kami menyelesaikannya. Butirannya adalah topik artikel yang berasingan, dan mungkin lebih daripada satu, kerana saya ingin bercakap tentang banyak perkara: daripada penyegerakan skema pangkalan data kepada kawalan akses dan penyahperibadian data peribadi. Ia juga harus diperhatikan bahawa penyelesaian ini bukan pengganti untuk pangkalan data dan repositori analisis sebenar, ia hanya menyelesaikan masalah tertentu.

Di peringkat atas ia kelihatan seperti ini:

Terdapat pangkalan data PostgreSQL di mana pengguna boleh menyimpan data kerja mereka, dan yang paling penting, replika analisis semua perkhidmatan disambungkan ke pangkalan data ini melalui FDW. Ini memungkinkan untuk menulis pertanyaan kepada beberapa pangkalan data, dan tidak kira apa itu: PostgreSQL, MySQL, MongoDB atau sesuatu yang lain (fail, API, jika tiba-tiba tiada pembungkus yang sesuai, anda boleh menulis sendiri). Nah, semuanya nampaknya hebat! Putus?

Sekiranya semuanya berakhir dengan cepat dan ringkas, maka, mungkin, artikel itu tidak akan wujud.

Adalah penting untuk menjadi jelas tentang cara postgres mengendalikan permintaan kepada pelayan jauh. Ini kelihatan logik, tetapi selalunya orang tidak memberi perhatian kepadanya: postgres membahagikan pertanyaan kepada bahagian yang dilaksanakan secara bebas pada pelayan jauh, mengumpul data ini dan melakukan pengiraan akhir itu sendiri, jadi kelajuan pelaksanaan pertanyaan akan sangat bergantung pada bagaimana sudah tertulis. Ia juga harus diperhatikan: apabila data datang dari pelayan jauh, mereka tidak lagi mempunyai indeks, tidak ada apa-apa yang akan membantu penjadual, oleh itu, hanya kami sendiri yang boleh membantu dan mencadangkannya. Dan itulah yang saya ingin bincangkan dengan lebih terperinci.

Permintaan mudah dan rancangan dengannya

Untuk menunjukkan cara Postgres menanyakan jadual 6 juta baris pada pelayan jauh, mari lihat rancangan ringkas.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

Menggunakan pernyataan VERBOSE membolehkan anda melihat pertanyaan yang akan dihantar ke pelayan jauh dan hasilnya akan kami terima untuk pemprosesan selanjutnya (rentetan RemoteSQL).

Mari pergi lebih jauh dan tambah beberapa penapis pada pertanyaan kami: satu demi satu boolean bidang, satu dengan kemasukan cap masa setiap selang dan satu demi satu jsonb.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

Di sinilah letaknya, yang perlu anda perhatikan semasa menulis pertanyaan. Penapis tidak dipindahkan ke pelayan jauh, yang bermaksud bahawa untuk melaksanakannya, postgres menarik semua 6 juta baris untuk menapis secara setempat (garisan Penapis) dan melakukan pengagregatan kemudian. Kunci kejayaan ialah menulis pertanyaan supaya penapis dihantar ke mesin jauh, dan kami menerima dan mengagregatkan hanya baris yang diperlukan.

Itu sesuatu yang booleanshit

Dengan medan boolean, semuanya mudah. Dalam pertanyaan asal, masalahnya adalah disebabkan oleh pengendali is. Jika kita menggantikannya dengan =, maka kita mendapat hasil berikut:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Seperti yang anda lihat, penapis terbang ke pelayan jauh, dan masa pelaksanaan dikurangkan daripada 27 kepada 19 saat.

Perlu diingatkan bahawa pengendali is berbeza dengan operator = yang boleh berfungsi dengan nilai Null. Maksudnya begitu adalah tidak Benar dalam penapis akan meninggalkan nilai False dan Null, manakala != Benar hanya akan meninggalkan nilai Palsu. Oleh itu, apabila menggantikan operator tidak anda harus lulus dua syarat kepada penapis dengan operator ATAU, sebagai contoh, WHERE (col != True) ATAU (col is null).

Dengan boolean difikirkan, teruskan. Sementara itu, mari kita kembalikan penapis mengikut nilai boolean kepada bentuk asalnya untuk mempertimbangkan secara bebas kesan perubahan lain.

timestamptz? hz

Secara umum, anda sering perlu mencuba cara menulis pertanyaan dengan betul yang melibatkan pelayan jauh, dan hanya kemudian mencari penjelasan mengapa ini berlaku. Sangat sedikit maklumat tentang ini boleh didapati di Internet. Jadi, dalam eksperimen, kami mendapati bahawa penapis tarikh tetap terbang ke pelayan jauh dengan bunyi yang kuat, tetapi apabila kami mahu menetapkan tarikh secara dinamik, contohnya, now() atau CURRENT_DATE, ini tidak berlaku. Dalam contoh kami, kami telah menambahkan penapis supaya lajur create_at mengandungi data tepat 1 bulan yang lalu (ANTARA CURRENT_DATE - INTERVAL '7 bulan' DAN CURRENT_DATE - INTERVAL '6 bulan'). Apa yang kami lakukan dalam kes ini?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Kami menggesa perancang untuk mengira tarikh terlebih dahulu dalam subquery dan menghantar pembolehubah yang telah disediakan kepada penapis. Dan pembayang ini memberikan kami hasil yang hebat, pertanyaan menjadi hampir 6 kali lebih pantas!

Sekali lagi, adalah penting untuk berhati-hati di sini: jenis data dalam subkueri mestilah sama dengan medan yang kami tapis, jika tidak, perancang akan memutuskan bahawa kerana jenisnya berbeza dan anda perlu mendapatkan semua data dan menapisnya secara setempat.

Mari kita kembalikan penapis mengikut tarikh kepada nilai asalnya.

Freddy lwn. jsonb

Secara umum, medan dan tarikh boolean telah cukup mempercepatkan pertanyaan kami, tetapi terdapat satu lagi jenis data. Perjuangan dengan menapisnya, sejujurnya, masih belum berakhir, walaupun terdapat kejayaan di sini juga. Jadi, inilah cara kami berjaya melepasi penapis jsonb medan ke pelayan jauh.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Daripada menapis operator, anda mesti menggunakan kehadiran satu operator. jsonb dalam yang berbeza. 7 saat dan bukannya 29 yang asal. Setakat ini, ini adalah satu-satunya pilihan yang berjaya untuk memindahkan penapis jsonb ke pelayan jauh, tetapi di sini adalah penting untuk mengambil kira satu had: kami menggunakan versi 9.6 pangkalan data, tetapi menjelang akhir April kami merancang untuk menyelesaikan ujian terakhir dan beralih ke versi 12. Sebaik sahaja kami mengemas kini, kami akan menulis bagaimana ia mempengaruhi, kerana terdapat banyak perubahan yang terdapat banyak harapan: json_path, tingkah laku CTE baharu, tolak ke bawah (sedia ada dari versi 10). Saya benar-benar ingin mencubanya tidak lama lagi.

Habiskan dia

Kami menyemak cara setiap perubahan mempengaruhi kelajuan pertanyaan secara individu. Sekarang mari kita lihat apa yang berlaku apabila ketiga-tiga penapis ditulis dengan betul.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Ya, pertanyaan itu kelihatan lebih rumit, ia adalah harga yang terpaksa, tetapi kelajuan pelaksanaan adalah 2 saat, iaitu lebih daripada 10 kali lebih pantas! Dan kita bercakap tentang pertanyaan mudah pada set data yang agak kecil. Atas permintaan sebenar, kami menerima peningkatan sehingga beberapa ratus kali ganda.

Kesimpulannya: jika anda menggunakan PostgreSQL dengan FDW, sentiasa semak sama ada semua penapis dihantar ke pelayan jauh dan anda akan gembira... Sekurang-kurangnya sehingga anda dapat bergabung antara jadual dari pelayan yang berbeza. Tetapi itu cerita untuk artikel lain.

Terima kasih kerana memberi perhatian! Saya ingin mendengar soalan, ulasan dan cerita tentang pengalaman anda dalam ulasan.

Sumber: www.habr.com