Обсуждение: checkpoint starting: time (insert statement hangs
Hello,
I'm having an issue with Insert statement (through oracle_fdw) that hangs after checkpoint starting is "time" instead of "xlog". see end of log below.
Migrating data from Oracle (Windows 2008 R2) to Postgresql (Windows 2012 R2). Both systems are virtual machines residing on same host.
Over 200 migrations have been done without any issues. Trying to understand what's happening here...
2020-04-20 14:02:36.103 EDT,5e9dbe0e.990,126,,,,00000,LOG: checkpoint complete: wrote 112333 buffers (28.6%); 0 trans
2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s, total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB
2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s, total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB
2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time
2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s, total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB
2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s, total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB
2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog
2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s, total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB
2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time
2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0 transaction log file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s, total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB
The transaction still active in pg_stat_activity but not writing on disk since last checkpoint complete. Non errors after last checkpoint. Also no errors in logs on Oracle side.
Any help or hints would be greatly appreciated.
Thank in advance!
Yannick
On Tue, 2020-04-21 at 12:10 -0400, Yannick Collette wrote: > I'm having an issue with Insert statement (through oracle_fdw) that hangs after checkpoint starting is "time" instead of"xlog". see end of log below. > Migrating data from Oracle (Windows 2008 R2) to Postgresql (Windows 2012 R2). Both systems are virtual machines residingon same host. > Over 200 migrations have been done without any issues. Trying to understand what's happening here... > > 2020-04-20 14:02:36.103 EDT,5e9dbe0e.990,126,,,,00000,LOG: checkpoint complete: wrote 112333 buffers (28.6%); 0 trans > 2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog > 2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0 transactionlog file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s, > total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB > 2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog > 2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0 transactionlog file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s, > total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB > 2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time > 2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0 transactionlog file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s, > total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB > > The transaction still active in pg_stat_activity but not writing on disk since last checkpoint complete. Non errors afterlast checkpoint. Also no errors in logs on Oracle side. > Any help or hints would be greatly appreciated. How does pg_stat_activity look? Is there an entry with "granted = FALSE" in pg_locks? What is the PostgreSQL query and the Oracle query? What is the state of the statement in Oracle? Is it running, blocked or waiting for the client? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com