Re: Çok veriyle çalışırken, veritabanı yapılan işlemi kesiyor.

Merhaba Samed bey,

Öncelikle pg_cron eklentisini kaldırdım ve yine aynı hatayı alıyorum.

Söylediğiniz parametrelerin değerleri;

session_preload_libraries|
-------------------------|
|

shared_preload_libraries|
------------------------|
|

local_preload_libraries|
-----------------------|
|

Sorularınızın cevapları;

1- PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1
20191121 (Red Hat 8.3.1-5), 64-bit

2- 30 GB Ram (28 GB Free),
16 Core CPU (% 95 Idle),
2.5 TB disk (1.4 TB Free)

3- postgresql.conf
shared_buffers = 128MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB

4- Kurulu olan extension'lar
postgis
orafce

5- Subquery Scan on r (cost=463362.37..515099.44 rows=80000 width=282)

|
-> Limit (cost=463362.37..514299.44 rows=80000 width=534)

|
-> Hash Join (cost=463362.37..774820.91 rows=489166 width=534)

|
Hash Cond: (a.id_abone_adres_uavt = bf.id)

|
-> Hash Left Join (cost=439755.24..715093.91 rows=664869
width=129)

|
Hash Cond: ((((((((((nvl(a.id_sayac_marka,
'-9'::numeric))::text || '~'::text) || (nvl(a.sayac_capi,
'-9'::integer))::text) || '~'::text) || (nvl(a.sayac_devirrakami,
'-9'::numeric))::text) || '~'::text) || nvl(btrim(upper(a.sayac_tipi|
-> Merge Right Join (cost=439714.19..687627.01
rows=664869 width=147)

|
Merge Cond: (x.id_abone = a.id_abone)

|
-> GroupAggregate (cost=439713.76..460925.11
rows=729055 width=86)

|
Group Key: x.id_abone, x.id_abone_tipi,
x.bitis_tarihi, x.abone_durum

|
-> Sort (cost=439713.76..442497.92
rows=1113664 width=78)

|
Sort Key: x.id_abone,
x.id_abone_tipi, x.bitis_tarihi, x.abone_durum

|
-> Subquery Scan on x
(cost=189912.33..228890.57 rows=1113664 width=78)

|
-> WindowAgg
(cost=189912.33..217753.93 rows=1113664 width=84)

|
-> Sort
(cost=189912.33..192696.49 rows=1113664 width=30)

|
Sort Key:
sas.id_abone, sas.id_abone_sicil

|
-> Seq Scan on
su_abone_sicil sas (cost=0.00..51416.64 rows=1113664 width=30)

|
-> Index Scan using sql121209005336850 on
su_abone a (cost=0.42..210562.64 rows=664869 width=115)

|
Filter: (id_abone_adres_uavt IS NOT NULL)

|
-> Hash (cost=27.69..27.69 rows=1069 width=16)

|
-> Seq Scan on cs_meter_model_prm mmp
(cost=0.00..27.69 rows=1069 width=16)

|
-> Hash (cost=13999.06..13999.06 rows=585606 width=6)

|
-> Seq Scan on gis_building_flat bf
(cost=0.00..13999.06 rows=585606 width=6)

|

On Wed, Aug 12, 2020 at 1:22 PM Samed YILDIRIM <samed(at)reddoc(dot)net> wrote:

> Merhabalar,
>
> 1. Hangi PostgreSQL versiyonunu kullanıyorsunuz?
> 2. Sistem kaynakları nedir?
> 3. Yapılandırma ayalarınızı paylaşır mısınız.
> 4. Veritabanında hangi eklentileri kullanıyorsunuz?
> 5. Explain ile planı alarak paylaşır mısınız.
>
> En önemli soru 4. soru. Veritabanı "Segmentation fault" ile kapanıyor
> loglarınıza göre. Veritabanı bellek alamadım diye sonlanmıyor, sistem
> kaynaklarının yetersizliği ile alakalı olduğunu düşünmüyorum. Veritabanı
> süreçlerinden birisi, kendisine ait olmayan bir bellek alanını okumaya ya
> da yazmaya çalışıyor. Bu da en sık C ile yazılmış bir
> fonksiyonun/eklentinin içerisindeki hatalı bellek yönetiminden veya kodun
> içerisindeki buglardan kaynaklanır.
>
> Loglardan ilk gözüme çarpan pg_cron kullandığınız. Onu kaldırmayı
> deneyebilirsiniz. local_preload_libraries, session_preload_libraries ve
> shared_preload_libraries parametrelerinin değerlerini de kontrol
> ederseniz iyi olur.
>
> show session_preload_libraries;
> show shared_preload_libraries;
> show local_preload_libraries;
> Best regards.
> Samed YILDIRIM
>
>
>
> 12.08.2020, 12:17, "Ali Kemal DEMIRCI" <demirci(dot)alikemal(at)gmail(dot)com>:
>
> Merhaba arkadaşlar,
>
> Create table xxx as
> Select ....
> Limit 80000;
>
> Yukardaki SQL komutunu çalıştırdığımda aşağıdaki hatayı alıyorum;
>
> SQL Error [08006]: An I/O error occurred while sending to the backend.
>
> Fakat "Limit 70000" yaptığımda başarılı şekilde select ifadesinden dönen
> kayıtlarla tablo oluşturulabiliyor.
>
> Hat oluştuğunda log dosyasında oluşan kayıtlar aşağıdaki gibidir.
> Bu konuda yardımcı olabilirmisiniz?
>
> 2020-08-12 11:08:58.037 +03 [94157] LOG: server process (PID 6072) was
> terminated by signal 11: Segmentation fault
> 2020-08-12 11:08:58.037 +03 [94157] DETAIL: Failed process was running:
> create table cs_subscriber_ as
> select r.id_abone id, 1::numeric corporation_id, r.id_abone
> subscriber_number, 1::numeric area_id, r.abone_tipi subscriber_type_id,
> r.id_abone_adres_uavt building_flat_id,
> r.creuser created_user_id, r.credate created_timestamp,
> 0::numeric "version", r.address address_description
> from (select a.id_abone, a.abone_no, nvl(x.id_abone_tipi, 1)
> abone_tipi, a.id_abone_adres_uavt, bf.building_door_id,
> nvl(a.creuser,1::numeric) creuser , nvl(a.credate,
> '01/01/1900 00:00:00'::timestamp) credate, (a.a4_adr1 ||a.a4_adr2
> ||a.a4_adr3)::varchar address,
> mmp.id meter_model, a.sayac_no, a.sayac_konum,
> a.sayac_takma_tarihi, a.sayac_uretim_tarihi, to_char(a.sayac_uretim_tarihi,
> 'yyyy')::numeric uretim_yili,
> a.sayac_karttipi_adi, a.kart_sayac_mrk ,
> a.kart_sayac_no , a.kart_sayac_seri, a.sayac_konum, a.kayit_tarihi,
> a.aciklama, a.iptal_tarih,
> ('1'||lpad(a.defter_no::tex
> 2020-08-12 11:08:58.037 +03 [94157] LOG: terminating any other active
> server processes
> 2020-08-12 11:08:58.038 +03 [6074] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6074] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6074] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.038 +03 [6030] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6030] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6030] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.038 +03 [6073] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6073] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6073] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.038 +03 [6075] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6075] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6075] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.038 +03 [6056] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6056] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6056] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.038 +03 [6022] WARNING: terminating connection
> because of crash of another server process
> 2020-08-12 11:08:58.038 +03 [6022] DETAIL: The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2020-08-12 11:08:58.038 +03 [6022] HINT: In a moment you should be able
> to reconnect to the database and repeat your command.
> 2020-08-12 11:08:58.041 +03 [94157] LOG: all server processes terminated;
> reinitializing
> 2020-08-12 11:08:58.066 +03 [6086] LOG: database system was interrupted;
> last known up at 2020-08-12 11:08:46 +03
> 2020-08-12 11:08:58.093 +03 [6087] FATAL: the database system is in
> recovery mode
> 2020-08-12 11:08:58.373 +03 [6088] FATAL: the database system is in
> recovery mode
> 2020-08-12 11:08:58.415 +03 [6089] FATAL: the database system is in
> recovery mode
> 2020-08-12 11:08:58.449 +03 [6090] FATAL: the database system is in
> recovery mode
> 2020-08-12 11:08:58.493 +03 [6086] LOG: database system was not properly
> shut down; automatic recovery in progress
> 2020-08-12 11:08:58.504 +03 [6086] LOG: redo starts at EC/8764F088
> 2020-08-12 11:08:58.600 +03 [6086] LOG: invalid record length at
> EC/88476D80: wanted 24, got 0
> 2020-08-12 11:08:58.600 +03 [6086] LOG: redo done at EC/88476CB8
> 2020-08-12 11:08:58.675 +03 [94157] LOG: database system is ready to
> accept connections
> 2020-08-12 11:08:58.679 +03 [6101] LOG: pg_cron scheduler started
>
> --
> Saygılarımla,
>
> Ali Kemal DEMİRCİ
>
>

--
Saygılarımla,

Ali Kemal DEMİRCİ