Problem Statement: Problem Statement



Yüklə 2,03 Mb.
tarix26.12.2016
ölçüsü2,03 Mb.



Problem Statement:

  • Problem Statement:

    • We want the Informix database server to be as invisible as possible.
    • After the initial install of IDS and uptime, we need the server to be able to adapt to any workload that comes its way.
    • Prior to 12.10xC2, several key server resources were not dynamic
      • This could and did lead to artificial performance ceilings, and an increase in downtime to fix, or degradation in performance until the issue was detected by human interface.
        • Not everyone knows how to detect ……. and if they do, getting the downtime from management is an issue …. always.
  • The improvements are in several different categories…..



Physical Log

  • Physical Log

  • Logical Log

  • Bufferpool

  • CPUs

  • These will follow similar rules guiding automatic tuning:

    • How to detect when insufficient resources for each of the above resources are causing a performance impact
    • How to increase the resource with minimal impact to the application
    • How to control the increase of the resource so it adapts in a timely manner… not too much, not too little, just right


Purpose:

  • Purpose:

    • To help avoid performance loss associated with an undersized physical log caused by frequent server checkpoints which can block user transactions, located within an already constrained storage space with other objects therein, such as database and server objects.
  • Expansion

    • We support the changing of the physical log location already.
    • We want to make sure there is only sole occupancy in the space for the physical log; nothing else hindering its successful expansion and requested size:
      • A new single Physical Log Storage Space (PLOGspace) per instance,
      • Has a single physical chunk containing only the server physical log with the physical page size of the host O/S.
  • Physical log expansion is triggered by any of the following

    • A checkpoint blocked user transactions because the physical log is too small.
    • 5 checkpoints triggered by physical log activity:
      • Since the last time the physical log was extended.
      • And within the last 20 checkpoints.


General Syntax:

  • General Syntax:

    • onspaces -c –P -p
      -o -s [-m ]
  • Examples:

    • onspaces -c –P plogdbs -p /dev/chk1 -o 0 -s 40000
    • onspaces -c –P pdbs1 -p /dev/pchk1 -o 500 -s 60000 -m /dev/mchk1 0
  • Note

    • If a PLOGspace already exists:


Creating a PLOGspace with SQL Admin API

  • Creating a PLOGspace with SQL Admin API

  • General Syntax:

    • create plogspace, ,
      , , , ,
  • Examples:

    • execute function task(“create plogspace”,”plogdbs”,”/dev/chk1”,30000,0);
    • execute function task(“create plogspace”,”plogdbs”,”/dev/chk1”,30000,0,”/dev/mchk1”,0);


Dropping a PLOGspace with onspaces

  • Dropping a PLOGspace with onspaces

    • The same space-dropping command (onspaces –d) may be used to drop this new type of space,
    • Only if the space is empty (53 pages, oncheck –pe to verify)
  • General Syntax:

    • onspaces -d
      [-y]
  • Example:

    • onspaces -d plogdbs -y


Dropping a PLOGspace with SQL Admin

  • Dropping a PLOGspace with SQL Admin

    • One must use a new “drop plogspace” command with SQL Admin API.
      • drop dbspace” will return an error.
    • drop plogspace” takes no arguments, since there can be at most one PLOGspace in the instance.
      • The PLOGspace must be empty to be dropped.
  • General Syntax:

    • drop plogspace
  • Example:

    • execute function task(“drop plogspace”);


In the message log file:

  • In the message log file:

    • Thru the onstat –m command line utility:
      • You will see a message saying “Physical Log too small ……”
  • Thru onstat –g ckp utility:

    • Look for a warning at the top about the physical log being too small or a “Trigger” of “Plog” with “Block Time” > 0.0
    • The presence of long transaction rollbacks. A “Trigger” of “LongTX” with “Block Time” > 0.0 can be a too small physical log or too little Logical Log space.


Purpose

  • Purpose

    • To improve performance, having sufficient logical log space helps prevent frequent checkpoints from blocking transactions and also from having long or too frequent checkpoints.
    • Knowing an appropriate amount of logical log space for a dynamic workload is difficult to supply in advance without first knowing and estimating the transaction load.
    • Configuring a server with too much logical log space in advance can limit the out-of-box experience by requiring substantial disk space.
    • We will therefore allocate logical log space dynamically as needed and therefore dynamically tunable.
  • Detection

    • The best way to detect if the logical log is a bottleneck is to look at the recent checkpoint activity:
      • The server keeps track of the last 20 checkpoints.
      • If a substantial portion of the recent checkpoints were triggered because of the logical log, then we should increase the logical log space by adding another logical log.
      • If a long transaction or checkpoint blocking occurs because of the logical log, we should also add another logical log to the log space.


Expansion

  • Expansion

    • We already have the adding logical logs dynamically feature:
      • DYNAMIC_LOGS 2 in the configuration file defined by $ONCONFIG environment variable.
        • Typically executes only when the server runs out of logical log space.
      • The server already contains the logic to extend a dbspace while adding a logical log.
    • There is a new, additional Logical Log expansion parameter in the configuration file called AUTO_LLOG
      • Enables the automatic addition of logical logs when the database server detects that adding logical logs improves performance.
      • If enabled, log expansion occurs:
        • When 5 of the last 20 checkpoints were caused by logical logs filling up
        • When a logical log causes a blocking checkpoint
        • When a logical log causes a long checkpoint
      • AUTO_LLOG & DYNAMIC_LOGS do not interact


Configuration

  • Configuration

    • The new AUTO_LLOG configuration parameter specifies
      • Dbspace for new logical log files
      • Maximum size of all logical log files.
  • Estimation of maximum logical log space requirements:

    • Depending on the number of concurrent users accessing your database server…….
      • 1 - 100: 200 MB
      • 101 - 500: 500 MB
      • 501 - 1000: 1 GB
      • More than 1000: 2 GB


onconfig.std value 0

  • onconfig.std value 0

  • By default this is 0, disabled.

  • AUTO_LLOG 1, dbspace_name, max_size

    • 1 - Add Logical logs when needed to improve performance.
    • dbspace_name - dbspace name in which to add logical log files.
      • This dbspace must have the default page size for the operating system.
    • max_size = Optional. Default is 2048000 KB (2 GB).
      • The maximum size in KB of all logical log files, including any logical log files that are not stored in the dbspace dbspace_name.
      • If max_size is not specified, the AUTO_TUNE_SERVER_SIZE configuration parameter setting affects the maximum size.
    • Takes effect:
      • After you edit the onconfig file and restart the database server.
      • Reset the value dynamically in your onconfig file by running the onmode -wf command
      • Reset the value in memory only by running the onmode -wm command.


When the maximum size of the logical log files is reached, logical log files are no longer added to improve performance. However, if the DYNAMIC_LOGS configuration parameter is enabled, logical logs are added to prevent transaction blocking.

  • When the maximum size of the logical log files is reached, logical log files are no longer added to improve performance. However, if the DYNAMIC_LOGS configuration parameter is enabled, logical logs are added to prevent transaction blocking.

  • The settings of the DYNAMIC_LOGS and the AUTO_LLOG configuration parameters do not interact.

  • If the value of the max_size field is larger than the size of the specified dbspace, enable automatic expansion of storage spaces.



In the message log file:

  • In the message log file:

    • Thru the onstat –m command line utility:
      • You may see unusual numbers of logical logs have been added.
  • Thru onstat –g ckp utility:

    • Look for a “Trigger” of “Llog” with “Block Time” > 0.0


Dynamic Buffer pools have been requested for a while by our users.

  • Dynamic Buffer pools have been requested for a while by our users.

  • We will therefore add the ability to dynamically extend a buffer pool and do so automatically, which will allow us to start with relatively small pools and increase the size of the pools only as needed.

  • This will improve performance in many cases by getting the memory resources necessary to the database server it needs, if available, to increase application transaction performance much sooner, in near real-time, rather than after the fact, which has been the norm.



Three goals:

  • Three goals:

    • Use a new memory format method of allocating pool memory:
      • Specifying buffer pool size in bytes (Kb, Mb, Gb) units.
    • Marking the pool as extendable.
    • Maintain the legacy format method of BUFFERPOOL for current user compatibility:
      • Specifying buffer pool size and its limits in pagesize units.
  • Some BUFFERPOOL arguments are now incompatible with others.

  • Must follow rules to properly set this parameter:

    • Order of precedence for all BUFFERPOOL definitions:
      • All definitions shall have the same format (all legacy or all memory) or the server will not boot.
      • If an argument’s value is not defined, we use the value from the default BUFFERPOOL setting.
      • If the default BUFFERPOOL setting is not defined, use the internal definition for the format being used. If unable to determine format (ex. BUFFERPOOL size=2k), use internal format based on any other BUFFERPOOL definitions present. If not, use internal legacy.


Detection

  • Detection

    • We want to add more memory/buffers to a buffer pool only when the working set doesn’t fit.
    • To determine when to extend the buffer pool, we keep track of the cache hit ratio of each buffer pool by sampling the cache hit ratio once a minute for 5 minutes.
    • If the average cache hit ratio is less than a configured threshold, we extend the buffer pool.
  • Extension

    • In order to support buffer pool extension, we need to move buffer pools into their own shared memory segments.
  • Shared Memory in Informix can now have 5 possible segments

    • Resident
    • Virtual
    • Message
    • Bufferpool *** NEW ****
    • Extended


Bufferpool Segments

  • Bufferpool Segments

    • Buffer pools will no longer be allocated within the resident segment.
    • We will add a new IDS memory segment class that will contain only buffer pool memory structures and adhere to the RESIDENT flag.
    • The buffer pool class segment will have virtually no overhead (bitmap or TTree) for maintaining the segment. This will allow for maximum memory usage.
  • Bufferpool Extending for Legacy Format

    • The BUFFERPOOL configuration parameter’s legacy “buffers” format uses these attributes:
      • buffers – starting number of buffers
      • next_buffers – is the number of buffers to add when the buffer pool is extended and will be doubled every 4th extension *.
      • max_extends – maximum number of extensions the buffer pool is allowed


Bufferpool Extending for Memory Format

  • Bufferpool Extending for Memory Format

    • The BUFFERPOOL configuration parameter’s new “Memory” format uses these attributes:
      • memory – target amount of memory that a buffer pool can grow to. Values will be rounded up to 32mb alignments.
      • start_memory – starting amount of memory
    • Both memory and start_memory values can be expressed in a variety of units.
    • For example:
      • BUFFERPOOL size=4k,start_memory=32mb,memory=54gb
      • BUFFERPOOL size=8k,start_memory=48000,memory=204mb


A BUFFERPOOL definition can NOT include aspects of both formats; either it’s expressed in legacy format or memory format.

  • A BUFFERPOOL definition can NOT include aspects of both formats; either it’s expressed in legacy format or memory format.

  • A BUFFERPOOL definition with mixed formats is rejected and the database server will not start.

  • The charts on the next several slides describe each argument to the BUFFERPOOL parameter:

    • both = works with legacy & memory format.
    • legacy & memory are mutually exclusive.








BUFFERPOOL size=2k,extendable=1,buffers=1000,next_buffers=2000,max_extends=8

  • BUFFERPOOL size=2k,extendable=1,buffers=1000,next_buffers=2000,max_extends=8

    • Create a 2k pagesize buffer pool.
    • The buffer pool is extendable up to 8 times.
      • The 1st 3 extensions will be of 2000 buffers,
      • Next 4 will be of 4000 buffers and
      • The 8th extension will be of 8000 buffers.
  • BUFFERPOOL size=10k,start_memory=auto,memory=4gb

    • Create a 10k pagesize buffer pool.
    • The buffer pool will be extendable and have a max size of 4gb.
    • IDS will determine the initial # of buffers and the extension sizes.
    • Note: when using legacy format, it is possible to ask for N buffers and get slightly more or fewer depending on memory alignment. We allocate extra memory to allow for alignment and then consume all of the memory allocated.




  • As before, the ‘class’ column indicates the type of memory segment, the ‘B’ within the column indicating the new ‘Buffer pool’ segment, and as before: ‘R’ indicating resident and ‘V’ indicating virtual segments.



Produces output per bufferpool in use.

  • Produces output per bufferpool in use.

  • Will report either format in use.





This configuration parameter is now expanded to use with cpu class to make the cpu class extendable.

  • This configuration parameter is now expanded to use with cpu class to make the cpu class extendable.

  • VPCLASS class,aff=range,noage,num=N,autotune,max=N

    • autotune is optional.
      • When set, IDS will determine when to add another VP of class.
    • max is the maximum number of CPU VPs we can extend to.
  • When using autotune, affinity will not be used for any cpu vps added as part of automatic tuning.

  • This is again for performance reasons, adding an additional cpu vp dynamically thru autotune allows more resource availability to make those transactions complete quicker due to less likelihood of there being not enough cpu to complete a given operation quickly.

    • Manual intervention is likely to be not as quick in many such cases.


Detection

  • Detection

    • CPU VPs are added whenever the average ready Q count across all the CPU VPs for the last minute exceeds 4.
    • If the average depth of the ready Q is >= 5, we add another CPU VP up to a maximum of the number of physical processors on the box.
    • Monitoring the ready queue (onstat –g rea, large number of threads sitting waiting), and the global usage of the CPU virtual processors (onstat –g glo, the eff column always at 100).
  • Configuration

    • A new argument to the VPCLASS configuration parameter format has been added: autotune.
    • This argument will be processed only for the CPU and AIO vp classes. For more info see VPCLASS below.
  • Expansion

    • Internal procedure to add a cpu vp.
    • See Appendix A for Event Alarm relevant to this.


onstat –g rea

  • onstat –g rea

  • IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 2 days 23:53:12 -- 240192 Kbytes

  • Ready threads:

  • tid tcb rstcb prty status vp-class name

  • 6 536a38 406464 4 ready 3cpu main_loop()

  • 28 60cfe8 40a124 4 ready 1cpu onmode_mon

  • 33 672a20 409dc4 2 ready 3cpu sqlexec







Use a VPCLASS configuration parameter entry for the AIO virtual processor class to specify an exact number of AIO virtual processors or to enable the database server to add AIO virtual processors as needed.

  • Use a VPCLASS configuration parameter entry for the AIO virtual processor class to specify an exact number of AIO virtual processors or to enable the database server to add AIO virtual processors as needed.

  • Again this being done for performance, it is sometimes hard for a DBA to know when he might need an AIO VP and where to look to get this information.

  • AIO VP’s are used on operating systems that do not support KAIO, the database server uses the AIO class of virtual processors to perform database I/O that is not related to physical or logical logging.

    • The database server uses the CPU class to perform KAIO for database I/O when KAIO is available on a platform.
    • If the database server implements KAIO, a KAIO thread performs all I/O to raw disk space, including I/O to the physical and logical logs.


Configuration parameter used to buffer pool size when automatic tuning is not possible.

  • Configuration parameter used to buffer pool size when automatic tuning is not possible.

  • When BUFFERPOOL:memory is set to auto, we set memory using AUTO_TUNE_SERVER_SIZE.

    • Sets internal structures to assume that’s how much memory will get used.
    • Therefore, it is possible to exceed these amounts.
  • AUTO_TUNE_SERVER_SIZE [OFF | SMALL | MEDIUM | LARGE | XLARGE]

    • OFF use 10% available memory
    • SMALL use 10% available memory
    • MEDIUM use 20% available memory
    • LARGE use 33% available memory
    • XLARGE use 50% available memory


This is set only when you install the product and request, as part of the installation process, that a server instance be created:

  • This is set only when you install the product and request, as part of the installation process, that a server instance be created:

    • It sets the sizes of memory and storage spaces to allocate based on the number of expected concurrent users provided by the user during the install.
      • SMALL = 1 - 100 users
      • MEDIUM = 101 - 500 users
      • LARGE = 501 - 1000 users
      • XLARGE = more than 1000 users
  • The setting affects the following:

    • The size of the buffer pool.
    • The maximum size of logical log files (from 200 MB up to 2 GB) before the server stops automatically adding logical logs to improve performance
    • The initial size (from 50 MB to 500MB) of the following created storage spaces, which are created automatically during installation:
      • An extendable plogspace for the physical log
      • A dbspace for the logical log
      • Dbspaces for databases and tables
      • A temporary dbspace
      • An sbspace
      • A temporary sbspace


This parameter can be used even if you did not create a server during installation, or if you change its value after you initialize the server for the first time.

  • This parameter can be used even if you did not create a server during installation, or if you change its value after you initialize the server for the first time.

  • In this case, the new value affects the size of only the following properties:

    • The size of the buffer pool, if the BUFFERPOOL configuration parameter setting includes the memory='auto' option.
    • The maximum size of all logical log files before the server stops automatically adding logical logs to improve performance.
  • Auto-instance-creation with the physical log, logical log and smart large object spaces, and 2 tempspaces all in their own separate dbspaces, along with the separately created rootdbs, will lead to less down time, fewer configuration steps, and increased ‘out-of-the-box’ experiences for all users, from the very first initial install.

  • At this point, all they have to do is create databases. But where?



This controls the dbspace location of user defined databases, tables, and indexes and the automatic fragmentation of tables.

  • This controls the dbspace location of user defined databases, tables, and indexes and the automatic fragmentation of tables.

  • If 0 (default) this is disabled:

    • New databases still default created in rootdbs
    • New table and indexes still default created in the same dbspace as the database
  • Set from 1 to 32, enables automatic location and fragmentation:

    • Indicates how many round-robin fragments to initially allocate to a table.
  • Stores new databases, tables, and indexes in server defined optimal dbspaces:

    • By default, all dbspaces are available.
    • Users can control the list of available dbspaces.
    • Fragments new tables by round-robin, where the number of fragments is equal to the value of the AUTOLOCATE configuration parameter.
    • Adds more table fragments as the table grows.
    • In” dbspace_name clause overrides


If enabled, you can use the autolocate database arguments with the admin() or task() function to

  • If enabled, you can use the autolocate database arguments with the admin() or task() function to

    • Manage the list of dbspaces for automatic location and fragmentation:
      • The list of available dbspaces is in the sysautolocate system catalog table.
    • Disable automatic location and fragmentation for the specified database.
  • You can use the AUTOLOCATE environment option of the SET ENVIRONMENT statement of SQL to enable or disable the value of the AUTOLOCATE configuration parameter for a session.

  • It is anticipated that the implementation of this feature will lead to fewer out of storage space errors and fewer databases and objects being created in under sized/allocated storage spaces.

  • Users can still override automatic server based allocation of location by using the “in dbspace” clause of the create database, table and index clauses



It is anticipated that this will lead to fewer outages caused by having user defined databases and tables and indexes being stored in the rootdbs and causing that space to fill up completely (and stop the instance) or extend/expand unnecessarily beyond its original size.

  • It is anticipated that this will lead to fewer outages caused by having user defined databases and tables and indexes being stored in the rootdbs and causing that space to fill up completely (and stop the instance) or extend/expand unnecessarily beyond its original size.

    • This has been a long standing user issue and seen in many places.
  • This is a good feature.

  • Stands alone as a feature. In combination with the implementation of the storage pool it will be very difficult, if configured and administered properly initially, to run out of space.

  • Best practice. With the storage pool feature implemented.



Turn off autolocation for the stores database:

  • Turn off autolocation for the stores database:

    • execute function task(“autolocate database off”, “stores”);
  • Turn on autolocation for the stores databases:

    • execute function task(“autolocate database add”, “stores”, “datadbs1”);
    • execute function task(“autolocate database add”, “stores”, “datadbs1,datadbs2,datadbs3,datadbs4”);
    • execute function task(“autolocate database anywhere”, “stores”);
  • Remove datadbs2 from the list of available dbspaces for the stores database:

    • execute function task("autolocate database remove", “stores", "datadbs1");
  • onmode –wf/wm














Yüklə 2,03 Mb.

Dostları ilə paylaş:




Verilənlər bazası müəlliflik hüququ ilə müdafiə olunur ©azkurs.org 2020
rəhbərliyinə müraciət

    Ana səhifə