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 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?
-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