Обсуждение: dbi-link freezing up DBs, needing reboot

I was playing around with dbi-link, hoping to get it connected to a teradata
database. However, before I dive into that, I figured that I might as well
try it out first on a PG Database (on another server)

So, it installed dbi-link fine.

I did a select on a 30GB table and it froze the Originating database and it
ALSO froze the foreign database.

Looking into the foreign box's logs, I see for some reason the network just
ceased to function? (can't be a coincidence?)

Aug 30 15:15:18 kernel: NETDEV WATCHDOG: eth0: transmit timed out
Aug 30 15:15:18 kernel: r8169: eth0: link up


I then had to reboot the originating DB/box.

Once it got back up, I did a select on a very small table <1MB and the data
is returned properly and in timely fashion.

Then I tried it on a slightly bigger table --> 50MB and it froze again

select * from xmms_b4.log_update where record_update_date_time > '2009-08-30
10:00:00' and record_update_date_time < '2009-08-30 11:00:00' limit 10;
NOTICE:  SELECT dbi_link.cache_connection( 2 ) at line 12.

#\d log_update;
         Column          |            Type             |       Modifiers
-------------------------+-----------------------------+--------------------
----
 job_name                | text                        | not null
 table_name              | text                        | not null
 from_date               | timestamp without time zone | not null
 to_date                 | timestamp without time zone |
 rows_deleted            | integer                     |
 delete_duration         | interval                    |
 rows_inserted           | integer                     |
 insert_duration         | interval                    |
 rows_updated            | integer                     |
 update_duration         | interval                    |
 record_update_date_time | timestamp without time zone | not null default
now()

After 2 times of this happening, I'm really worried that it will do other
nasty things.


Help?

PS : dbi-link is, for me, ultimately to try if I can get it to connect to
teradata to pull some data on a daily basis. I currently use dblink for
pg-to-pg connections

Ow Mun Heng wrote:
> I was playing around with dbi-link, hoping to get it connected to a teradata
> database. However, before I dive into that, I figured that I might as well
> try it out first on a PG Database (on another server)
>
> So, it installed dbi-link fine.
>
> I did a select on a 30GB table and it froze the Originating database and it
> ALSO froze the foreign database.
>
> Looking into the foreign box's logs, I see for some reason the network just
> ceased to function? (can't be a coincidence?)
>
> Aug 30 15:15:18 kernel: NETDEV WATCHDOG: eth0: transmit timed out
> Aug 30 15:15:18 kernel: r8169: eth0: link up
>
>

That looks like it came from dmesg.  Did you look in the postgres log?

"froze" is not a helpful description.  PG spawns off a client for each connection, and I doubt one client could freeze
another. So was the one connection froze, all PG clients froze, or the entire computer froze? 

You said you had to reboot, so I assume the entire computer.

On the foreign box, have you ever pushed a large amount of data over the network?  You might wanna try to copy some
reallybig files a few times and see if you get the eth0 timeout error again. 

I assume you are using Linux and a new version of PG, right?

-Andy

-----Original Message-----
From: Andy Colson [mailto:andy@squeakycode.net]
Ow Mun Heng wrote:
>> I was playing around with dbi-link, hoping to get it connected to a
>teradata
>> database. However, before I dive into that, I figured that I might as
>well
>> try it out first on a PG Database (on another server)
>>
>> I did a select on a 30GB table and it froze the Originating database and
>it
>> ALSO froze the foreign database.
>>
>That looks like it came from dmesg.  Did you look in the postgres log?
>
>"froze" is not a helpful description.  PG spawns off a client for each
>connection, and I doubt one client could freeze another.  So was the one
>connection froze, all PG clients froze, or the entire computer froze?
>
>You said you had to reboot, so I assume the entire computer.
>
>On the foreign box, have you ever pushed a large amount of data over the
>network?  You might wanna try to copy some really big files a few times and
>see if you get the eth0 timeout error again.
>
>I assume you are using Linux and a new version of PG, right?


Sorry, I don't know how else to describe it cos I don't much activity over
my ssh connections. Even top refused to work on the foreign box.

Yeah, the foreign box has handled large amount of data before. I pushed out
over 300G of data while rsyncing the db to another slave.

Centos -5.2 and PG 8.3.7 on the foreign box and 8.3.12 on the originating
box.

I was told that I shouldn't use the views directly. I believe libpq or
something just tried to push out all 30G of data all at once from the
foreign box to the originating box.

After I used the remote_select functions. All is better (for now)

Thanks