I have some procedures that we run in batch (I didn't wrote them) and when they run on shared memory connected database it's n times faster than when they run on the same machine but connected over TCP. Why is this happening? What can I do to improve performance on TCP client?
Shared memory access is inherently faster than TCP. You should always use shared memory access when you can.
Therefore it is always best to run an AppServer on the same machine as the database.
-peter
Just to clarify why what Peter says is true, a shared memory client is reaching directly into -B without any intervening software. A networked client, even on the same machine, is not only going down and up through a TCP layer, but is relying on the broker to do its access to -B for it.
I realize it should be faster using shared memory but I was suprised that it's up to 10x faster. Is it always advisable to used shared memory? What are the downsides or risks of using it?
vjug wrote:
I realize it should be faster using shared memory but I was suprised that it's up to 10x faster. Is it always advisable to used shared memory? What are the downsides or risks of using it?
About 10 times faster is to be expected and certainly what I have measured before.
Like I said, always use shared memory whenever you can.
The only limitation is that you can only do it on the same machine where the database resides. So from self-service clients, batch clients and AppServer. In that case never use TCP.
As a matter of fact, I know of customers disallowing TCP access to the database at all. Shared memory is safest since you can only use it if you have access to the database machine itself.
TCP potentially provides access to the database from anywhere on your entire network, which is a potential risk.
HTH
-peter
I haven't done my own benchmarks on this, but the up to 10X figure is consistent with remarks Alon Blich made at this year's PUG Challenge Americas when discusssing his query optimizer and benchmarking he had done. Think about the difference and it may not be so surprising. The shared memory client has only to reach right into the memory to get the data for 98% of the fetchs (assuming a reasonably tuned database). Whereas, the networked client has to have another process do the reaching ... with potential competition ... compose the data into messages, punch down through the transport layers, punch back up through the transport layers, and then convert the message into OE records again. That's a lot of overhead!
What are the downsides or risks of using it?
The only risk is that your batch will run much faster than remote clients... :-) Depending on the nature of the job that might have implications.
Let's say it's a batch job that needs a lot of (historical) data, that no other process needs. When it has 10 times faster access to the DB than remote clients that typically just need current data the batch job *might* pollute the buffer pool with just his own data causing a low buffer hit ratio for the rest. This is because of the nature of how the buffer pool is managed (LRU).
So you might need to think about private buffers for your batch job to avoid this effekt.
The difference in speed of tcp compared to shared memory access varies depending on a lot of factors. Operating system, network stack code quality, whether you are using 127.0.0.1, localhost, hostname, or host.example.com for the name of the endpoint to which you are connecting, network speed, available cpu power, network configuration parameters, etc. Even when you are making a connection to a server process on the same machine, in some circumstances I've seen the data actually go out on the network when it does not need to.
With slow (i.e. 10 mb Ethernet) networks with other traffic, 3x to 5x difference is not uncommon. But that can be improved upon.
In tests I have done on Linux, with client and server on different machines, both with gigabit NICs, and connected to a gigabit Ethernet switch, with no other network traffic, performance was only a little slower (less than 10 percent) than if client and server were on the same computer and client was using shared memory. That test is not definitive though and YMMV, transportation, meals, and accomodation not included.
Well, one drawback is that I've seen a number of cases where there was the need to kill a self-service client (i.e. connect through shared memory) and doing so brought down the database as well. This was on Windows 2008 R2 (yes, I know) but I don't know if this "behavior" is exclusive to Windows. Clients being prowin32 and AppServer.
There is a particular (admittedly uncommon) use case that is constrained by the use of shared-memory connections. I have a client who chose to deploy on a Windows back-end, and wanted a single-server solution, i.e. clients running on the DB server. In this case, running GUI (therefore 32-bit) clients requires the use of the 32-bit RDBMS if shared memory is to be used. On the other hand, if the clients connect via TCP then the database can be 64-bit and address a lot more virtual memory, which in my book is a bigger win than the minor client performance delta.
The broker does not do the memory access for the client. A server is spwaned to handle those requests for a pool of clients. The -Ma and -Mi parameters control how clients are distributed among and pooled in a server.
If your clients are doing many NO-LOCK reads and returning multi-record sets of data (rather than one record at a time stuff) then the -Mm parameter can make a big difference to client-server performance. (It allows more memory so that many records can be packed into a single request.)
I also noticed this kind of problem in all versions of progress (at least since version 8) if the shared memory session is manipulation data on the database when bein killed. So we use the following procedure if we have to kill shared memory connections that can't be stopped "in normal ways":
1. delete the session in the promon (8.->1.): (You should check the PID to be sure to delete the right session).
2. kill the process in the operating system if 1. didn't stop the process.