Postgresql behind firewall: Query takes too long

Question

Here is my setup: two CentOS 5.2 boxes on VMWare ESXi 4.0. The first box ip is 192.168.22.52 on eth0 and 192.168.99.1 on eth1. The second box runs PostgreSQL 8.3 with ip 192.168.99.2 on eth0. Here are iptables for box1, for box2 see comment below.

I have set up port 5432 forwarding on box1 and am able to connect to PostgreSQL on box2 via pgAdminIII or psql from Vista notebook (192.168.22.1, there is no other boxes in this subnet, it has its own switch and is physically isolated). The database I am connecting to has two schemas, one is 'smaller' (basically just one table), another one is bigger (some 30 tables, 100 functions, etc.) So I am able to work with the smaller schema (browse the table and so on) but I when I try to expand the bigger schema - pgAdminIII freezes for 20 min or so.

PostgreSQL log shows there is a query which takes way too long:

2009-06-04 21:04:46 EEST LOG: 00000: duration: 493578.874 ms statement: SELECT pr.oid, pr.xmin, pr.*, format_type(TYP.oid, NULL) AS typname, typns.nspname AS typnsp, lanname, proargnames, proconfig, pg_get_userbyid(proowner) as funcowner, description FROM pg_proc pr JOIN pg_type typ ON typ.oid=prorettype JOIN pg_namespace typns ON typns.oid=typ.typnamespace JOIN pg_language lng ON lng.oid=prolang LEFT OUTER JOIN pg_description des ON des.objoid=pr.oid WHERE proisagg = FALSE AND pronamespace = 2200::oid AND typname <> 'trigger' ORDER BY proname 

Both box1 and box2 are clones of the development boxes, and the original network structure was different - box2 was directly accessible without port forwarding and there was no problem accessing the databases whatsoever.

Now, if I run the above query via psql on box2 or 'original' machine, or from box1 connecting to box2, it executes immediately.

During the query is run, tcpdump on the box2 periodically says:

12:45:39.770609 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 8760:10220(1460) ack 1 win 54 12:45:39.968496 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 10220 win 16425 12:45:39.968541 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 10220:11680(1460) ack 1 win 54 12:45:39.968574 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 11680:13140(1460) ack 1 win 54 12:45:39.969250 IP 192.168.22.1.49484 > 192.168.99.2.postgres: . ack 13140 win 16425 12:45:39.969275 IP 192.168.99.2.postgres > 192.168.22.1.49484: . 13140:17520(4380) ack 1 win 54 12:45:39.969408 IP 192.168.22.52 > 192.168.99.2: ICMP 192.168.22.1 unreachable - need to frag (mtu 1500), length 556 

Other than that, I do not see much traffic. MTU on all ethN interfaces is 1500. ping -l 1472 -f 192.168.99.1 from the notebook goes through without problems.

I suspect that I am missing something about iptables or network setup and would appreciate your advise.

Answer 1

Some things to try:

1. Start off by verifying your network is behaving itself. Assuming you have managed switches, look at the interface statistics for speed/duplex mismatching or a mismatched MTU. Consider checking / replacing cabling if anything is running errors (eg: trying to run GigE over Cat5 instead of Cat5e will likely give grief).

2. Run some tests to prove you can get wire-speed transfers between the two machines and to the external machine; netcat, ftp or http transfers are a good start here (scp may get CPU bound, and thus, may not be the best test).

3. Test the same query locally on the Postgres server. If it completes in an appropriate timeframe, you know it's not the database. If it doesn't complete or takes "too long", then you have a bad query or other database problem to debug. Make sure to consider the storage I/O side of things; you may be saturating what your disks are capable of providing. Check the VMware performance graphs to confirm / deny.

4. Assuming that works, disable the firewall and run the same query against the postgres server from "box1". If that works, the VM->VM connectivity is likely fine.

5. Assuming that works, bring the firewall back up and test again. If that works, then your problem is likely external to that host, leaving the switch or the external host to debug.

Good luck.

Answer 2

You're having an MTU issue, but I'm not sure why. I'm trying to get my head wrapped around your virtual topology here.

So, your Windows Vista notebook is connected to the "local" network, or the Internet network?

I am assuming that your Windows Vista notebook is connected to the Internet and that you're accessing the external-side IP address of "box 1" to use the port-forwarding on port 5432 to get to "box 2". If that's the case, what do you get back when you try to:

ping -l 1472 -f <box 1 IP address>

Edit: Okay-- very good. If you would, run an "ifconfig" on both "box 1" and "box 2" and examine the MTU value on each Ethernet interface. They should all be 1500. (I'm just trying to get a handle on why "box 1" told "box 2" that it couldn't fragment a 556 byte datagram bound for your notebook...)

Edit: Zow. Okay-- that's wild.

If it's not too much to ask, could you post the contents (or links thereto) of your iptables configs into the question? (I'm starting to get stumped here. What you're describing is something that I've done frequently, but I'm not sure how it's breaking down.)

Edit: Back with you now. Okay. I'm getting perplexed at this one now. The iptables configuration doesn't look like it should be causing any problems. I do see that you're forwarding UDP 5432 onto "box 2". You don't need to forward that-- Postgres only uses TCP. That won't hurt anything, though.

During your 20 minute wait, did you see traffic moving between the Vista notebook and "box 2"? Can you reproduce that condition every time you connect?

Not that it makes a huge difference, but on the FORWARD chain on "box 1", I'd typically make the rule that ACCEPTs packets with RELATED,ESTABLISHED set to be the first rule in the chain (to short-circuit processing). I can't think this would have any significant performance impact for you, though.

I hate not knowing the answer to a problem. This is going to keep me awake at night.

Answer 3

Is it conceivable that one of these machines is trying to inappropriately use IPv6? That is, have you made sure IPv6 is turned off everywhere it's not supposed to be used, and, if used at all, correctly configured?