Mikro hizmet mimarisinin, bu dünyadaki her şey gibi artıları ve eksileri vardır. Bununla birlikte bazı süreçler kolaylaşır, bazıları ise daha da zorlaşır. Değişimin hızı ve daha iyi ölçeklenebilirlik adına fedakarlıklar yapmanız gerekiyor. Bunlardan biri analitiklerin artan karmaşıklığıdır. Bir monolitte tüm operasyonel analizler, SQL sorgularına ve analitik bir kopyaya indirgenebiliyorsa, o zaman çok hizmetli bir mimaride her hizmetin kendi veritabanı vardır ve tek bir sorgunun yapılamayacağı (ya da belki yapılabilir?) gibi görünür. Şirketimizde operasyonel analitik sorununu nasıl çözdüğümüz ve bu çözümle yaşamayı nasıl öğrendiğimizi merak edenler için hoş geldiniz.
Adım Pavel Sivash, DomClick'te analitik veri ambarının bakımından sorumlu bir ekipte çalışıyorum. Geleneksel olarak faaliyetlerimiz veri mühendisliği olarak sınıflandırılabilir ancak aslında görev yelpazesi çok daha geniştir. Veri mühendisliği, veri analizine yönelik araçların desteklenmesi ve uyarlanması ve kendi araçlarınızın geliştirilmesi için ETL/ELT standardı vardır. Özellikle operasyonel raporlama için, elimizde bir monolit varmış gibi "rol yapmaya" ve analistlere ihtiyaç duydukları tüm verileri içerecek bir veritabanı vermeye karar verdik.
Genel olarak farklı seçenekleri değerlendirdik. Tam teşekküllü bir depo oluşturmak mümkündü - hatta denedik, ancak dürüst olmak gerekirse, mantıktaki oldukça sık değişiklikleri, oldukça yavaş bir depo oluşturma ve üzerinde değişiklik yapma süreciyle birleştiremedik (eğer biri başarılı olursa) , nasıl olduğunu yorumlara yazın). Analistlere şunu söylemek mümkündü: "Arkadaşlar, python öğrenin ve analitik kopyalara gidin", ancak bu işe alım için ek bir gerekliliktir ve mümkünse bundan kaçınılması gerektiği görülüyordu. FDW (Yabancı Veri Sarmalayıcı) teknolojisini kullanmaya karar verdik: esasen bu, SQL standardında olan ancak çok daha kullanışlı bir arayüze sahip standart bir dblink'tir. Buna dayanarak, sonunda benimsenen bir çözüm ürettik ve bunda karar kıldık. Ayrıntıları ayrı bir makalenin konusu ve belki de birden fazla makalenin konusu çünkü çok şey hakkında konuşmak istiyorum: veritabanı şemalarının senkronizasyonundan erişim kontrolüne ve kişisel verilerin kişiselleştirilmesine kadar. Ayrıca bu çözümün gerçek analitik veritabanları ve depoların yerine geçmediğine, yalnızca belirli bir sorunu çözdüğüne dair bir rezervasyon yaptırmak gerekir.
En üst düzeyde şöyle görünür:
Kullanıcıların iş verilerini saklayabilecekleri bir PostgreSQL veri tabanı bulunmaktadır ve en önemlisi tüm servislerin analitik replikaları FDW aracılığıyla bu veri tabanına bağlanmaktadır. Bu, çeşitli veritabanlarına bir sorgu yazmayı mümkün kılar ve ne olduğu önemli değildir: PostgreSQL, MySQL, MongoDB veya başka bir şey (dosya, API, aniden uygun bir sarmalayıcı yoksa, kendinizinkini yazabilirsiniz). Her şey harika görünüyor! Ayrılıyor muyuz?
Her şey bu kadar çabuk ve basit bir şekilde bitseydi, o zaman muhtemelen bir makale olmazdı.
Postgres'in uzak sunuculara gelen istekleri nasıl işlediği konusunda net olmak önemlidir. Bu mantıklı görünüyor, ancak çoğu zaman insanlar buna dikkat etmiyor: Postgres, isteği uzak sunucularda bağımsız olarak yürütülen parçalara böler, bu verileri toplar ve son hesaplamaları kendisi gerçekleştirir; böylece sorgu yürütme hızı büyük ölçüde bağlı olacaktır. nasıl yazılıyor. Ayrıca şunu da belirtmek gerekir: Veriler uzak bir sunucudan geldiğinde, artık indeksleri yoktur, zamanlayıcıya yardımcı olacak hiçbir şey yoktur, bu nedenle ona yalnızca biz yardım edebilir ve tavsiyelerde bulunabiliriz. Ve bu tam olarak daha ayrıntılı olarak konuşmak istediğim şey.
Basit bir sorgu ve onunla birlikte bir plan
Postgres'in uzak bir sunucudaki 6 milyon satırlık bir tabloyu nasıl sorguladığını göstermek için basit bir plana bakalım.
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 msVERBOSE deyimini kullanmak, uzak sunucuya gönderilecek sorguyu ve daha sonraki işlemler için (RemoteSQL satırı) alacağımız sonuçları görmemizi sağlar.
Biraz daha ileri gidelim ve isteğimize birkaç filtre ekleyelim: bir tane boole alan, olaya göre zaman damgası aralıklarla ve birer birer 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 msSorgu yazarken dikkat etmeniz gereken nokta tam da burasıdır. Filtreler uzak sunucuya aktarılmadı; bu, bunu yürütmek için Postgres'in daha sonra yerel olarak filtrelemek (Filtre satırı) ve toplama işlemini gerçekleştirmek için 6 milyon satırın tamamını çıkardığı anlamına gelir. Başarının anahtarı, filtrelerin uzaktaki makineye aktarılmasını ve yalnızca gerekli satırları alıp toplamamızı sağlayacak bir sorgu yazmaktır.
Bu çok saçma bir şey
Boole alanlarıyla her şey basittir. Orijinal talepte sorun operatörden kaynaklanıyordu is. Eğer şununla değiştirirseniz =, o zaman aşağıdaki sonucu elde ederiz:
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 msGördüğünüz gibi filtre uzak bir sunucuya uçtu ve yürütme süresi 27 saniyeden 19 saniyeye düşürüldü.
Operatöre dikkat etmek önemlidir. is operatörden farklı = Çünkü Null değeriyle çalışabilir. Bu demektir doğru değil filtrede False ve Null değerlerini bırakacak, oysa != Doğru yalnızca False değerleri bırakacaktır. Bu nedenle, operatörü değiştirirken değil OR operatörüyle iki koşulun filtreye iletilmesi gerekir; örneğin, NEREDE (sütun != Doğru) VEYA (sütun boş).
Boolean konusunu hallettik, devam edelim. Şimdilik, diğer değişikliklerin etkisini bağımsız olarak değerlendirmek için Boolean filtresini orijinal biçimine döndürelim.
zaman damgası? hz
Genel olarak, genellikle uzak sunucuları içeren bir isteğin nasıl doğru şekilde yazılacağını denemeniz ve ancak bundan sonra bunun neden olduğuna dair bir açıklama aramanız gerekir. Bu konuda internette çok az bilgi bulunabilir. Dolayısıyla deneylerde, sabit bir tarih filtresinin uzaktaki sunucuya bir patlama sesiyle uçtuğunu bulduk, ancak tarihi dinamik olarak ayarlamak istediğimizde, örneğin now() veya CURRENT_DATE gibi, bu gerçekleşmez. Örneğimizde, created_at sütununun geçmişteki tam 1 aya ait verileri içermesi için bir filtre ekledik (CURRENT_DATE - INTERVAL '7 ay' VE CURRENT_DATE - INTERVAL '6 ay' ARASINDA). Bu durumda ne yaptık?
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 msPlanlayıcıya alt sorgudaki tarihi önceden hesaplamasını ve hazır değişkeni filtreye geçirmesini söyledik. Ve bu ipucu bize mükemmel bir sonuç verdi, istek neredeyse 6 kat daha hızlı hale geldi!
Yine burada dikkatli olmakta fayda var: Alt sorgudaki veri tipi, filtrelediğimiz alanın veri tipiyle aynı olmalıdır, aksi takdirde planlamacı, tipler farklı olduğundan, öncelikle tümünün alınması gerektiğine karar verecektir. Verileri yerel olarak filtreleyin ve filtreleyin.
Tarih filtresini orijinal değerine döndürelim.
Freddy vs. Jsonb
Genel olarak Boolean alanları ve tarihler zaten sorgumuzu yeterince hızlandırdı ancak bir veri türü daha kaldı. Dürüst olmak gerekirse, filtrelemeyle mücadele hala bitmedi, ancak burada da başarı var. Böylece filtreyi geçmeyi başardık jsonb uzak sunucuya alan.
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 msOperatörleri filtrelemek yerine bir operatörün varlığını kullanmalısınız jsonb farklı bir şekilde. Orijinal 7 yerine 29 saniye. Şu ana kadar filtrelerin iletilmesinde başarılı olan tek seçenek bu. jsonb uzak bir sunucuya, ancak burada bir sınırlamayı dikkate almak önemlidir: veritabanının 9.6 sürümünü kullanıyoruz, ancak Nisan ayı sonuna kadar son testleri tamamlayıp 12 sürümüne geçmeyi planlıyoruz. Güncelleme yaptığımızda bunun nasıl etkilendiğini yazacağız çünkü çok fazla umut vaat eden pek çok değişiklik var: json_path, yeni CTE davranışı, aşağı itme (sürüm 10'dan beri mevcut). Gerçekten yakın zamanda denemek istiyorum.
Bitir işini
Her değişikliğin istek hızını nasıl etkilediğini ayrı ayrı test ettik. Şimdi üç filtrenin tümü doğru yazıldığında ne olacağını görelim.
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 msEvet, istek daha karmaşık görünüyor, bu zorunlu bir ücrettir, ancak yürütme hızı 2 saniyedir, bu da 10 kattan daha hızlıdır! Ve nispeten küçük bir veri kümesine yönelik basit bir sorgudan bahsediyoruz. Gerçek taleplerde birkaç yüz kata kadar artış elde ettik.
Özetlemek gerekirse: PostgreSQL'i FDW ile kullanıyorsanız, her zaman tüm filtrelerin uzak sunucuya gönderildiğini kontrol edin, mutlu olursunuz... En azından farklı sunuculardaki tablolar arasındaki bağlantılara ulaşana kadar. Ama bu başka bir makalenin hikayesi.
İlginiz için teşekkür ederiz! Yorumlarda deneyimlerinizle ilgili soruları, yorumları ve hikayeleri duymayı çok isterim.
Kaynak: habr.com