How about auto trimming _mproserve processes for SQL or/and ABL after a given idle time? - Forum - OpenEdge General - Progress Community

How about auto trimming _mproserve processes for SQL or/and ABL after a given idle time?

 Forum

How about auto trimming _mproserve processes for SQL or/and ABL after a given idle time?

This question is not answered

Hi Forum, an old KBase (000020546) says:

"Database remote Servers can neither be trimmed or timed-out like UBroker processes."   So far, it seems possible to trim these _mproserve manually and individually from promon.

We got a case that created many SQL connections for a very short time that has lead to 200 SQL remote server, letting no room left for ABL connections later on.  Most of these SQL connections are no longer used.  Yes it is possible to define a dedicated broker for SQL to limit the number of connection, but this leads to many additional points to administrate (port, Mi Ma Mn etc...).   Wouldn't it be useful to auto close these idle connections after a given idle time?  That would also release some resources.

Any plan for such a feature, or good reason to not implement such a thing?

All Replies
  • You can terminate a server from promon R&D 4 | 7 | 7.

    I *highly* recommend that you don't use shared brokers for 4GL and SQL.  If you can configure one broker, configuring two isn't difficult.  And it eliminates problems like the one you're describing.

    Personally, I don't have a use-case for auto-trimming servers.  Client load may vary over time, but eventually load will increase again so I will again need the number of running servers that were already instantiated.  Trimming them in the interim when demand is low would just mean that future clients would have to wait for their re-instantiation.  As for freeing resources, I don't care about that as I need the machine to have those resources available for the DB when load is high.  In this context I'm referring to a DB that is production-like (prod, DR, UAT, etc.).

    Maybe a feature like this would be more valuable in a shared-resource environment like a dev DB server.  On the other hand, you could just restart the DB, which shouldn't be difficult in dev.

  • Thank you for your input Rob.  However this reminds me hard some Sonic days**

    Just splitting the connection needs with two brokers for ABL Apps and SQL reporting is not enough.  We also have some Java Apps doing SQL too.

    An efficient DB system would let us handle a maximum number of connections for some defined *users* (with patterns for unlimited or 1..).  Example: ServiceA 4 connections, ServiceB 3 connections... and so forth.  I do not know if such a DB system exists yet...

    This way, a faulty service would not mess up the entire system by consuming all the remote servers.  This would also add value to using well defined and dedicated users..  For now, too many systems just use one single Technical DB User that makes it very hard to diagnose connection leaks problems.

    So for now, we have to handle this rule on the client side (in our case, the faulty application is a Java that was creating to many connections for a short while), with a limited pool of connection.  But still a well dedicated App can be victim of a non well educated App.

    In that picture, having something simple that auto trim non used remote servers would be helpful even for production.  At the end of the day, the one that survives is not the purest, but the one that leaves/makes less problems...

    ** for large messaging infrastructures, where we had to define many acceptors to anticipate this kind of situation.  Our messaging system was looking like a labyrinthine system to handle non-explicit complex rules, very hard to maintain.

  • That's kind of the idea behind brokers. In an ideal world you would have a different broker for each type of application, so you'd have one for the ABL, one for the SQL reporting and one for the Java application. That way, if one of them gets out of hand, like you say, the impact on the others is limitable. And with different brokers for different things you can set them up to effectively throttle certain less critical applications, whilst the business critical ones are prioritised. Yes, you're adding extra maintenance points, but it's not a big deal IMO, and the flexibility and security it adds are worth it.

  • I agree that trimming is remote ABL brokers is important.  In some respects, the brokers act like glorified "shared memory" ABL sessions.  The big difference is that they do time-slicing* to handle proxied requests on behalf of a "client/server" ABL session. (*in OE 12 it will change to multi-threading)

    By trimming brokers (eg. after an inactivity period, or after a specified lifetime period has elapsed) it clears out any latent cruft that may have accumulated in the broker process over time.

    We have noticed issues that would have been mitigated by trimming ABL brokers regularly.  For example this bug ( knowledgebase.progress.com/.../raw-transfer-statement-failed-due-to-stale-schema-for-table-abc ) only takes place because of ABL remote brokers that refuse to recognize an "online" schema change.  Every other type of OpenEdge client picks up the schema changes, but not the pre-existing ABL brokers for some reason.  The bug is scheduled to be fixed one day, but it has been about a year since I reported it.

    Trimming ABL brokers after a specified lifetime would be very handy.  After that lifetime expired, the broker could be flagged, and any new clients would be directed to go to another one.

  • > By trimming brokers

    servers?

  • > By trimming brokers

    servers?

  • >> servers?

    "Remote ABL broker-servers".  Lets just call them _mprosrv that have outlasted their expiration date.

  • DB Brokers don't bring enough value, plus they actually do require a lot of configuration pains (think of automated deployments, maintaining maps of port, security consideration with our hosting partners,etc...)   A real value would be added if we could define the type of a policy I was describing in my previous post, based on named DB-users.  Those would be defined in some DB tables, so easy to deploy, or configure with simple ABL or SLQ code.

    Has product management ever considered this possibility?  How does it sound?