Informix

 View Only
  • 1.  virtual segments

    Posted Tue February 21, 2023 06:01 AM

    Hello,

    Are perf issues related to virtual segments ? if so, need an explanation please

    i know that when seeing many virtual segments we have to resize the shmvirtsize, but we cannot resize indefinitely :), could we find out the sessions using those virtual segments ?

    thanks in advance



    ------------------------------
    John Smith
    ------------------------------


  • 2.  RE: virtual segments

    IBM Champion
    Posted Tue February 21, 2023 06:10 AM
    Hi,

    onstat -g ses will give you the memory consumption per session.
    onstat -g ses xxxx will give you the used memory in each pool (V Segment).

    Typically, memory consumption which is increasing over the time will be the
    result of non-closed cursors (in case of java: Statements or ResultSets).
    (when connections are are reused).

    MARCUS HAARMANN






  • 3.  RE: virtual segments

    IBM Champion
    Posted Tue February 21, 2023 06:34 AM

    Simple answer is no, the amount of memory allocated to the db server, or to an individual db server session for that matter, typically has no performance implication (nor does it matter performance-wise whether that memory got allocated in one/few larger segments or many smaller ones.)  There might, of course, be some overhead associated with more segments (from which free memory had to be searched or to which it had to be returned) or when closing and cleaning up a session that has a lot of memory allocated, but I don't remember having seen this as impacting overall performance.

    Still, esp. if your db server runs on a dedicated system, you want to allocated the typical amount of memory to this server up front - what else would that memory do on this system? - and you'd rely on dynamic shared memory expansion only for extraordinary or unexpected demands.

    If, on the other hand, your system's memory demand grows in some unexpected ways, esp. from some SQL sessions growing unexpectedly large (in terms of memory), this could be an indication of some inefficiency in what it is doing which in turn would have a performance implication, but there should be more obvious signs of inefficiency (cpu consumption, i/o or other waiting, read/write activity, ...).

    In short, memory consumption can be a problem of its own (or it simply is a function of what's being asked from the system), and performance problems should be understood from different angles.



    ------------------------------
    Andreas Legner
    ------------------------------