Performance Tuning

Follow

Database Tuning
Version: 2.2
Rev Date: August 23, 2016

Background
Enterprise Random Password Manager (ERPM) can use of one of two brands of databases for its back-end data store:

  • Microsoft SQL 2008 and later
  • Oracle 11g - special support only, no longer generally available

Both Microsoft and Oracle databases have features available to them to adjust tuning parameters and make recommendations based on usage statistics. Sometimes, these results are better than others. More importantly, these usage statistics do change over time and these processes and recommendations that the Microsoft and Oracle databases make are not a replacement for a proper DBA who can tune and manage the database or setup automated procedures to perform these actions.

On the day you install ERPM the product runs well and fast. Typical customers will perform and institute many discovery processes during the initial roll. Over time, these discovery processes (which are write heavy to the database) get supplanted with password change jobs which are also write heavy to the database, though to a different set of tables than the discovery processes were. Then when passwords are managed, users will begin to read the new stored password information. This results in the creation of new stored procedures generated on demand to evaluate permission sets. In addition, a different object is now reading heavily from the database and writing back some information to new tables. Finally, for subsequent password change jobs, the database is now reading and writing to different tables than were used during the discovery, making use of new stored procedures generated on demand, and so on. The ultimate takeaway is that the usage profile of the customer database has changed significantly from the initial roll out to the current usage.

Modern databases will attempt to determine ahead of time what the requested resources will be based on past usage. This affects performance. Indexes are also generated on tables to help improve performance. However, like a book, an out of date index is not very helpful, and may even be detrimental to the performance of ERPM/RPM.

For website logins, there may be many domains available for authentication when working with Active Directory and it is possible to nest groups within groups; all of which must be evaluated and may slow down the login process considerably.

The point of this document is to mention steps that DBAs and possibly ERPM admins can take to help improve or maintain performance over time. Also to identify to the ERPM admin where login performance tuning can take place.


Web Login Improvements
Web users who are not granted all access to the website will find it is slower for them to login to the website and it is slower for them to gain access to certain features of the website. Specifically, a low powered user must have all of the following evaluated:

  • Global delegation permissions
  • Per management set delegation permissions
  • Per system delegation permissions
  • Per account delegation permissions
  • Account masks
  • Self-recovery rules
  • Application launch permissions

Compared to an all access user which just needs to check that it has "All Access".

Before any of the permissions can be evaluated, the user must also successfully be authenticated by the given authentication source. When using Active Directory for authentication, there may be multiple domains to validate. Within each of these domains, when delegating to groups, each group may contain other groups which may contain even more groups which may span multiple domains or forests. This whole process will greatly affect login time.

To help the process for logins there are a number of steps which can be taken:

  • Delegate to users only - evaluating groups can be slow and expensive.
  • Delete any ERPM roles that should not be evaluated.
  • If using groups, do not perform recursive lookups.
  • Disable any authentication servers that should not be evaluated.
  • Consider available system performance.
  • Optimize the database.

Delegate to users only: While Lieberman Software prefers delegating to groups as this makes it much easier to grant and remove access using existing identity and access management products, it does impact performance. When a user logs into the web interface, the website COM object will connect to the domain controller or domain identified in the authentication server entry list and attempt to enumerate the membership for each and every group found in the ERPM delegations. The more groups there are to evaluate, the longer the login takes as each group must be evaluated and its permissions calculated as noted above. Then the sum of all delegations must be calculated.

Delete any ERPM roles that should not be evaluated: ERPM/RPM create four roles for delegations during a default installation. Roles are used to provide LDAP users access into ERPM, which also includes Active Directory. These roles have permissions. When roles are included in the delegations list, a lookup for the membership of these roles must be performed and if any relevant membership is performed, the permissions must be calculated above and just as with any other groups, the sum of all delegations must be calculated.

If using groups, do not perform recursive lookups: Out of the box, ERPM does not perform recursive lookups on group memberships. This is designed to improve performance and it applied to Windows Domains only. Global groups can contain users and other global groups. This nesting can hurt lookup performance. The option from the website configuration should not be enabled if it is not expressly needed.

Authenticate to a particular domain controller: Out of the box, ERPM will place the name of the PDC emulator in the Server Name field for the Authentication Server record of the target domain. For HA practices, Lieberman Software recommends using the DNS name of the domain to ensure we can always reach a domain controller for authentication. However, for performance, this may not be the nearest or quickest domain controller. To ensure the best response, modify the authentication server record (Delegation | Authentication Servers) to contain the name of the domain controller that is closest and most available to the ERPM web server.

Disable any authentication servers that should not be evaluated: Authentication servers are used by ERPM/RPM to determine where and how to authenticate users who attempt to login to the ERPM/RPM website. Having more authentication server entries may slow down the login process unnecessarily. To remove an authentication server from evaluation (can be added back at any time), go to Delegation | Authentication Servers in the management console and deselect the Show as an Authentication Server Option in Web Interface check box in the top right corner of the dialog. There is no need to restart anything.

Consider available system performance: A whole section can be written on this alone and a good job of performance tuning for hosting web servers and database servers has already been written by their respective providers. Consider high disk queue, CPU utilization, paging, etc. This gets even more true when virtual machines are used. In general, follow best practices provided by Microsoft for the Windows hosts and your best practices regarding your visualization platform. As en example, on a virtual host, if the disk queue length is 50, your guest machine doing nothing other than launching notepad will appear to be exceptionally slow. Tuning virtual hosts, guests, SAN, etc. is as always, the job of the systems and network administrators.

Optimize the database: Please continue reading into the database section below.

Optimizing The Database - MS SQL

ERPM may use Microsoft SQL Server 2008 and later. There are no constraints from ERPM regarding patching or service pack level. Generally speaking, Lieberman software invites and recommends the latest version, patches, and service pack levels be used when possible.

ERPM makes use of the built in tuning objects within Microsoft SQL but these processes can also be automated by a database admin. First and foremost, for ERPM to display possible tuning actions to the ERPM admin, the user must have the View Server State permission for the database server.

In the ERPM management console, go to Settings | Data Store Configuration | SQL Server Auto-Index Tuning. If an error is generated on launch, then the required permissions are not granted to the user accessing the database. Be sure to grant View Server State to the calling user account. Otherwise, if possible, ERPM will return a list of possible indexes that Microsoft SQL recommends be generated. Starting from the top, add one index at a time as sometimes there are multiple indexes for the same table. Adding one index will remove the other recommendations. Sometimes, indexes will already exist on the selected tables. ERPM cannot overwrite existing indexes and will fail if you attempt to do so. The name of the table and offending index will be logged. Simply delete the current index on the target table (those created by ERPM will begin with ac_) and try the operation again.

The query used to retrieve the MS SQL recommendation is (replace database_name_here with the name of your ERPM database):

Code:
SELECT '$PREFIX$_' + LEFT (PARSENAME(mid.statement, 1), 32) + '_$HASH$' AS index_name, migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 'CREATE INDEX [$PREFIX$_' + LEFT (PARSENAME(mid.statement, 1), 32) + '_$HASH$]' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id] , PARSENAME(mid.statement, 1) AS table_name , mid.equality_columns AS equality_columns , mid.inequality_columns AS inequality_columns , mid.included_columns AS included_columns FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle INNER JOIN sys.databases ON sys.databases.database_id = mid.database_id AND sys.databases.name = 'database_name_here' ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

After generating indexes, from Settings | Data Store Configuration run Generate Stats FullScan. If performing this action by hand, run the Generate Stats Fullscan for each table in the ERPM/RPM database after manipulating indexes.

Also, indexes to get out of date. An old index, like that of a book, will not contain information for new entries and data. The result can be a database which performs worse than a database with no indexes. It is this scenario which requires a DBA action. Some customers do regular full index rebuilds on a monthly basis as a matter of habit to ensure the index information is always up to date. Their DBA's trigger this automatic processes directly in the database as this is not a feature or function of the ERPM product.

Also overtime, indexes, like the database at large, can become fragmented. ERPM provides access to the information provided by Microsoft SQL Server to determine how fragmented the indexes are. If, as mentioned above, regular full rebuilds of the indexes are happening, this should be a non-issue. On the other hand, if no maintenance is occurring, fragmented indexes and databases can also hamper overall performance. To keep these indexes performing well, use the SQL Server Index Defragmentation option. There is no equivalent option within the ERPM/RPM UI for an Oracle database server. Many customers create jobs directly in the database which defragment the indexes on a regular basis; this is a concept applicable to both MS SQL and Oracle databases.
The query used to determine the MS SQL index fragmentation is (replace database_name_here with the actual datastore name):

Code:
SELECT tblIndexes.name AS IndexName, tblIndexStats.avg_fragmentation_in_percent AS FragmentationPercent, tblObjects.name AS ObjectName, tblIndexStats.fragment_count AS FragmentCount FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID(N'database_name_here'), NULL, NULL, NULL ) AS tblIndexStats JOIN sys.indexes AS tblIndexes ON tblIndexStats.object_id = tblIndexes.object_id AND tblIndexStats.index_id = tblIndexes.index_id JOIN sys.objects AS tblObjects ON tblIndexStats.object_id = tblObjects.object_id WHERE tblIndexStats.avg_fragmentation_in_percent >= 10 AND tblIndexStats.fragment_count >= 10

Once again, after manipulating indexes, from Settings | Data Store Configuration run Generate Stats FullScan. If performing this action by hand, run the Generate Stats Fullscan for each table in the ERPM database.

Optimizing The Database - Oracle

A company must make use of the Oracle Database Performance tools in the command-line interface to optimize database performance. For the complete tuning guide, read: http://docs.oracle.com/cd/E11882_01/...112/e41573.pdf. For specifics on the Oracle Optimizer, read The Query Optimizer - 11g Release 2 (11.2). Tools for tuning Oracle are licensed separately: Options and Packs

Much more care must be given when Using Oracle as the back-end datastore as it is expected by Oracle that the Oracle database will have a properly certified Oracle DBA to attend to it.

To start the conversation on using the Oracle database, first ensure that the installation will not starve the ERPM system of resources. The default memory and threading allocations are sparse at best compared to the heavily threaded operations that ERPM will perform. In a default configuration where ERPM is spawning at least 100 threads to the database, this can cause the database to run out of resources resulting in failed jobs (incomplete password changes). This behavior is easily seen and replicated by trying to do things such as changing passwords across a largish number of systems. One way to combat this is to drop the thread count down to 40 (Settings | Program Options) for ERPM. This has the effect of slowing down the job processing while increasing the likelihood of a successful job (as far as the DB is concerned). Another [highly recommended] option is to change the memory and thread allocation to the oracle database. Start with:

  • show parameter memory
  • show parameter process


This will give you your baseline settings. To change the allocations use:

  • alter system set memory_target=xxxxM scope=spfile;
  • alter system set processes=yyyy scope=spfile;


Where xxxx is the amount of memory allocated to the database and yyyy is the number of threads. We recommend a minimum value of 2000 or much higher for the memory and a minimum value of 1000 threads.

The ERPM database should be profiled once or twice a year to ensure the current execution plan is properly aligned with realistic performance expectations. With the proper packs optional Oracle packs the Oracle database can be kept running strong.

The Virtual Environments

Enterprise Random Password Manager can be run in a physical or virtual or hybrid environment. When virtualizing, Lieberman Software places no constraints as to which virtual hosts may be used or to what extent. Most customers employ either VMware ESX(i) or Microsoft Hyper-V.

For the database, how things are virtualized are exceptionally important:

  • NEVER thin provision memory usage to a VM that is running a database.
  • Modern databases are also heavily multi-threaded so assign as many real cores as is reasonable. Do not grant the guest a single processor and expect the host to marshal resources accordingly. Rather, assign multiple CPUs. typically 4 CPUs is the recommended starting point.
  • Do not place a database on a heavily loaded virtual host. If the disk queue is high on the host, or the CPU utilization is high on the host, less resources will be available to the guest running the ERPM databases which will cause great delays in the ability of the database to process program requests. It is therefor important to monitor the virtual host resources and plan accordingly.


Applies To

  • Enterprise Random Password Manager (ERPM)
Was this article helpful?
1 out of 1 found this helpful

Comments

Powered by Zendesk