Обсуждение: network saturation
Hi,
We have a fully switched 100mbps network, and communication between clients running vb6 apps via odbc (08.01.02, i.e., the latest stable) are taking up from 20 to 40% of the bandwidth - per APP!. The server is 8.1.1 running on mandrake 10.1.
The offending apps are not coded well but this is just enormous. Even worse, the apps do an OCR which takes about 5 seconds (during which no db access takes place) and for some reason it seems there is still a lot of traffic (we have HP switches and port usage NEVER falls below 15%).
Can anyone shed some light on this? When 4+ apps start hitting the db performance starts to degrade quite considerably.
Cheers
Antoine
--
This is where I should put some witty comment.
We have a fully switched 100mbps network, and communication between clients running vb6 apps via odbc (08.01.02, i.e., the latest stable) are taking up from 20 to 40% of the bandwidth - per APP!. The server is 8.1.1 running on mandrake 10.1.
The offending apps are not coded well but this is just enormous. Even worse, the apps do an OCR which takes about 5 seconds (during which no db access takes place) and for some reason it seems there is still a lot of traffic (we have HP switches and port usage NEVER falls below 15%).
Can anyone shed some light on this? When 4+ apps start hitting the db performance starts to degrade quite considerably.
Cheers
Antoine
--
This is where I should put some witty comment.
On 27/01/06, Antoine <melser.anton@gmail.com> wrote:
For info, these apps have a transaction open pretty much constantly (none of the other apps have this, and don't seem to use much bandwidth). Originally, they were open for 1.5hrs and now opened and closed every two minutes (as soon is one is closed another is opened)
Chrs
Hi,
We have a fully switched 100mbps network, and communication between clients running vb6 apps via odbc (08.01.02, i.e., the latest stable) are taking up from 20 to 40% of the bandwidth - per APP!. The server is 8.1.1 running on mandrake 10.1.
The offending apps are not coded well but this is just enormous. Even worse, the apps do an OCR which takes about 5 seconds (during which no db access takes place) and for some reason it seems there is still a lot of traffic (we have HP switches and port usage NEVER falls below 15%).
Can anyone shed some light on this? When 4+ apps start hitting the db performance starts to degrade quite considerably.
For info, these apps have a transaction open pretty much constantly (none of the other apps have this, and don't seem to use much bandwidth). Originally, they were open for 1.5hrs and now opened and closed every two minutes (as soon is one is closed another is opened)
Chrs
--
This is where I should put some witty comment.
> > We have a fully switched 100mbps network, and communication between > > clients running vb6 apps via odbc (08.01.02, i.e., the latest stable) are > > taking up from 20 to 40% of the bandwidth - per APP!. The server is > > 8.1.1running on mandrake 10.1. You forgot specify the client OS ;-) But the network bandwidth isn't psqlODBC related. We use libpq for communication with backend. I have seen similar problem a few days ago and Magnus Hagander wrote: | We've seen this a couple of times before with Windows 2000, but it's | always been slow in both psql and pgadmin then ;-) Search the archives, | there's something about the QoS driver. I hope this helps you Luf
On 27/01/06, Ludek Finstrle <luf@pzkagis.cz> wrote:
Yeah, windows xp (I think we have one with no SP, and the rest with SP1). We have some win2000 sp4s but they don't run the offending app (rather other ones that don't show the problem).
I looked and it didn't seem particularly related, seeing as they are XP boxen.
CPU usage for the postgres machine is also very high, getting regularly up to 50% for a single connection. I will try a vacuum full but I am pretty sure that won't make much difference. Maybe it is related to network traffic?
I will have a look with ethereal and see what I can gather.
Chrs> > We have a fully switched 100mbps network, and communication between
> > clients running vb6 apps via odbc (08.01.02, i.e., the latest stable) are
> > taking up from 20 to 40% of the bandwidth - per APP!. The server is
> > 8.1.1running on mandrake 10.1.
You forgot specify the client OS ;-)
Yeah, windows xp (I think we have one with no SP, and the rest with SP1). We have some win2000 sp4s but they don't run the offending app (rather other ones that don't show the problem).
But the network bandwidth isn't
psqlODBC related. We use libpq for communication with backend.
I have seen similar problem a few days ago and Magnus Hagander wrote:
| We've seen this a couple of times before with Windows 2000, but it's
| always been slow in both psql and pgadmin then ;-) Search the archives,
| there's something about the QoS driver.
I looked and it didn't seem particularly related, seeing as they are XP boxen.
I will have a look with ethereal and see what I can gather.
Antoine
--
This is where I should put some witty comment.
> I looked and it didn't seem particularly related, seeing as they are XP > boxen. > CPU usage for the postgres machine is also very high, getting regularly up > to 50% for a single connection. I will try a vacuum full but I am pretty > sure that won't make much difference. Maybe it is related to network > traffic? > I will have a look with ethereal and see what I can gather. Agrh I'm stupid. You feel too high HW load. Do you know what exactly the app makes? Feel free to create mylog output or you can log on the server side to have better idea what's going on. Than you can try simulate the situation via psql client. So you can decide where is the problem. If you don't understand the mylog output you could send it to me privately. It could be interesting to see what settings do you use for psqlodbc. BTW 08.01.0102 isn't very good. Please try 08.01.0107 development snapshot from pgfoundry.org. There is fixed a lot of bugs (Parse Statement, get values, stability, ...). Then you can try different options like use declare/fetch, Parse Statement, ... Different tasks need different options turned on. We're planning to release new psqlodbc official release next week. The new official release will be based on the 08.01.0107. Regards, Luf
It could be interesting to see what settings do you use for psqlodbc.
BTW 08.01.0102 isn't very good. Please try 08.01.0107 development
snapshot from pgfoundry.org. There is fixed a lot of bugs
(Parse Statement, get values, stability, ...).
Then you can try different options like use declare/fetch, Parse Statement,
... Different tasks need different options turned on.
I would love to try it, seeing as declaire/fetch doesn't change anything...
Do I simple replace the dlls in C:\Program Files\psqlODBC\bin?
Antoine
--
This is where I should put some witty comment.
Fri, Jan 27, 2006 at 03:40:06PM +0100, Antoine napsal(a): > > It could be interesting to see what settings do you use for psqlodbc. > > BTW 08.01.0102 isn't very good. Please try 08.01.0107 development > > snapshot from pgfoundry.org. There is fixed a lot of bugs > > (Parse Statement, get values, stability, ...). > > Then you can try different options like use declare/fetch, Parse > > Statement, > > ... Different tasks need different options turned on. > > I would love to try it, seeing as declaire/fetch doesn't change anything... > Do I simple replace the dlls in C:\Program Files\psqlODBC\bin? Yes. It's enough. But it's strange that declare/fetch doesn't change anything. Driver fetch "cache size" (option on Page1 in left bottom corner) rows from backend after open. "cache size" is 100 by default. So if you have less rows in selects you could try to play with the cache size parameter. Regards, Luf
On 27/01/06, Ludek Finstrle <luf@pzkagis.cz> wrote:
Fri, Jan 27, 2006 at 03:40:06PM +0100, Antoine napsal(a):
> > It could be interesting to see what settings do you use for psqlodbc.
> > BTW 08.01.0102 isn't very good. Please try 08.01.0107 development
> > snapshot from pgfoundry.org. There is fixed a lot of bugs
> > (Parse Statement, get values, stability, ...).
> > Then you can try different options like use declare/fetch, Parse
> > Statement,
> > ... Different tasks need different options turned on.
>
> I would love to try it, seeing as declaire/fetch doesn't change anything...
> Do I simple replace the dlls in C:\Program Files\psqlODBC\bin?
Yes. It's enough.
But it's strange that declare/fetch doesn't change anything.
Driver fetch "cache size" (option on Page1 in left bottom corner) rows
from backend after open. "cache size" is 100 by default. So if you have
less rows in selects you could try to play with the cache size parameter.
I have tested with the dlls changed (107) and WOW - what a difference! Thanks! We tried with 102 on another system and it didn't work. We aren't going to search to hard though cos, hey, we have the best solution!
Thanks heaps
Antoine
--
This is where I should put some witty comment.