Re: Help! My applications run very slow if running over the network!

In article <4uqh9e$bn8@lex.zippo.com>, jwhite@arrowshirt.com writes >We've got a DB server and an application server. >My 4gl programs run much faster on the DB server than on the application server. > >Here's a test I ran: > >I've got a long, wide table (Rowsize: 426 bytes. Number of Rows: 105365. Table >size: 44MB.) > >Here's the timing results: > > DBSRVR (shared mem) DBSRVR (local network loopback) APPSRVR >select * 34.14 52.00 142.14>select 1col 26.47 26.46 26.55 > >Results are in seconds. Each result is the average of three consective tests. I >was the only user on the DB. > >Can anyone tell me just what the heck is going on here? Better yet, can anyone >tell me how to make this >network bottleneck go away? > I'm assuming your using Online since you talk about shared memory. I'll guess onlihe 7.x since your machine has 1Gb memory you're probably and multi-CPU I guess your using a multi-thread server i.e. the latest. Shared memory - 1. 4GL writes to shared memory 2. kernel process switches to engine 3. engine which reads shared memory and vice versa. Local loopback 1. 4Gl writes to socket 2. kernel process switches to tcp device driver which passes request to 3. ip device driver (output side) which passes to 4. ip device driver (input side) which passes to 5. tcp device driver 6. process switch to Online engine which reads request and vice versa. APP_SERVER 1. 4gl -> socket -> tcp driver -> ip driver -> ethernet driver -> .... across network.... -> ethernet driver -> ip driver -> tcp driver -> engine. >These are both 4 processor HP 9000 K220s with 1G of memory and running HP-UX >10.01. >Our network cards are 10BaseT. Each machine hangs directly off a 100BaseT cut- >through switch. > > Can't be sure that something is wrong since this kind of setup faster than I'm used to. First a) ftp a 44Mb file between the machines and log the time for the transfer. This provides a lower bound for the time taken for OnLine to reply to a request. OnLine will not get close to this since 4GL requests one row at the time whereas ftp makes one request and then just reads all the transmitted data. b) Run netstat -i and check you are not getting network errors or >5% collisions. Either network failures or the network in use by something else will reduce network performance. Other than that yes networks are a lot slower than shared memory, Loopback involves all the layers of TCP/IP code and process switches between kernel device drivers and user process (4GL and engine). True network transfer are even worse with delays due to signal transmission times (across several metres rather than a few centimeres across a system board. Your application should not fetch a lot of rows at any one time. E.g. when displaying a list of search results only fetch the ones which are visible on the screen i.e. fetch on demand. Run large reports/large updates on the database server. Yes batch processing is back! If the application must run across the network try looking at the environment variable FET_BUF_SIZE which allow you to configure the size of the buffer used to communicate between 4GL and the engine. It to reduce the number of messages sent between them. >John White Sysadmin and DBA Cluett-Peabody jwhite@arrowshirt.com -- David Williams