Can someone tell me their experience with scaling up SQL92? We need the SQL92 engine to support large numbers of concurrent connections.
As an example, lets say that the remote ODBC clients hold on to their connections for long periods of time but use them infrequently. (eg. because the connection pools grow quickly, but don't get trimmed by the connection-pool-timeout until five minutes have elapsed). Currently that type of grown in remote clients can cause problems. The problems seem to be somewhat self-inflicted because the SQL92 engine has limited capacity for remote client connections (as determined by things like -n and -Mpb/-Ma on the secondary broker).
We are tempted to configure the number of remote SQL92 connections to grow indefinitely (1000's) via -Mpb and -Ma. This is based on the fact that the remote client connections (part of an application's connection pool) are normally inactive and, when they do make queries they are resolved from memory buffers very quickly. There is low CPU and low disk overhead.
My understanding is that SQL92 servers are able to scale up easily . I think they are designed to use true OS-based multi-threading (instead of some sort of "time-slicing" mechnanism). IE. if a single _sqlsrv2 process is working on more than one request at a time, then the operating system will allow the work to be done in parallel on separate cores. Is that correct?
So what would be the impact of increasing -Ma very high number (eg. 100 or 200) and then increasing -Mpb at our discretion (based on the number of separate processes we can easily fit in memory, eg. 40 or 50)? When will we run out of resources? Which resources will we run out of first? I'm guessing the bottleneck is going to be TCP ports or system memory (which we have in abundance).
Any personal experiences in this area would be greatly appreciated. The limits we have on -Mpb (10) and -Ma (50) seem to cause a lot of "self-inflicted" pain, and cause us to frequently run into the capacity of 500. It seems to me that our SQL92 servers can handle a much, much higher number of remote connections - especially if most of them remain inactive much of the time (until the five minute CPTimeout expires).
I was able to confirm that sql92 servers use true multi-threading (they did so in 9.1D, long before the ABL remote servers):
"In Progress 9.1D and above the SQL engine is multithreaded"
So given that the _sqlsrv2 process is multi-threaded, what would be wrong with configuring a process to allow 1000 user connections per server (-Ma 1000)? The OS itself probably has a reasonable limit to the number of threads per process, but is there an OE-specific reason to restrict the number of threads a lot more than the OS does?
It seems to me that on the SQL92 side of things we might want to have a -Mpb value of just 2 or 3 (for redundancy) and then allow -Ma to get as high as the operating system allows. I don't have any of my own experiences tuning these parameters for SQL92. The docs don't make it clear why a person would prefer increasing -Ma over -Mpb (in a secondary SQL92 broker).
(This question is about SQL92 and is NOT related to the ABL "remote servers". On the ABL side of things it is pretty clear why you would keep -Ma as low as possible since OE is trying to do its own CPU multi-tasking using a custom time-slicing algorithm and it will starve clients for CPU if the -Ma gets too high).
I don't know of any benchmark reports that have investigated the number of threads versus processes for the OpenEdge SQL server. I have not done any.
Threads are not some sort of free magic bullet. They require resources like memory, stacks, database connections, and other things. When there are too many threads they become more and more inefficient because they block on locks for memory allocation, scheduler queues and other in-process shared data structures.
Also, if a fatal error of some sort occurs, all the threads ion the process will usually die.
In the absence of data, I cant give a decent recommendation, but until then, I wouldn't have more than 50 threads per process.=
>> Threads are not some sort of free magic bullet. They require resources like memory, stacks, database connections, and other things
Thanks Gus, I know they aren't totally free, but I would guess they are less expensive than having lots of servers (-Mpb). The memory, database connections, and CPU will be consumed regardless of whether we scale up on the -Ma side or on the -Mpb side. But as you indicate there may slightly less blocking and resource conflict when using independent processes rather than independent threads. However I would think this would be minimal until you get up to 100's of threads. Maybe Progress makes this stuff "flexible" so we can account for differences in the behaviors of Linux/Windows/HPUX. We are currently using HPUX, and have configured it to allow ~2000 threads per process as I recall.
Also, it is not clear to me whether each -Ma (a slot used by a connected client) is going to consume a *dedicated* thread in the first place. Perhaps there is a threadpool whereby only the clients that have *active* queries are paired to a thread. In that case I would think that the -Ma could be set to a value that is much higher than the number of threads that the OS will allow in a process.
If/when an entire server process crashes, I realize that all connected clients will experience errors. This is a good factor to remember, and may help us be moderate with the -Ma. While the SQL92 server processes rarely crash, it was already clear to me that more than one server process should be running at all times - for failover purposes if nothing else.
>> I wouldn't have more than 50 threads per process.
So this means you are suggesting -Ma of no more than 50? Are you basing that on the assumption that each of the -Ma has a dedicated thread?
I wish I could find a KB that tells us what factors to consider when tuning the -Ma and -Mpb. It seems like there may be an art in it. What would be really nice is if these things were dynamic and governed by the DBMS itself (rather than by OE dba's). I'd rather not run into the self-inflicted user limit over and over again, as we slowly increase -Ma and -Mpb by small increments of five or whatever. It seems like a dba should have better things to work on, and nobody appreciates the database outages that are required to make each adjustment.
you are right, this should not be so hard.
the trouble with all these parameters (Ma, Mpb, and a bunch of others) is that the optimum values are heavily dependent on the total server and database workload and the nature of the queries being executed and we have absolutely no data to guide us to to support any decisions.
therefore, i chose 50 purely as a rectal extraction with no evidence.
but since you brought this up, i will take it as a PUG Challenge topic for a talk.
-gus
> On May 31, 2019, at 2:43 PM, ChUIMonster wrote:
>
> We're waiting...
>
>
done=