Home > Articles > Operating Systems, Server > Solaris

Drill-Down Monitoring of Database Servers

This article presents a process for identifying and resolving problems with the performance of database servers. Learn how to monitor and tune databases, as well as how to recognize if a shared memory segment uses ISM (Intimate Shared Memory).
Like this article? We recommend

Introduction

Editor's Note

This material is extracted from Chapter 21 of Configuring and Tuning Databases on the Solaris Platform, by Allan N. Packer, (c) 2002, Sun Microsystems Press (ISBN 0-13-083417-3). Chapter 22, Monitoring and Tuning Oracle, will be presented in the July and August 2002 editions of Sun BluePrintsTM OnLine.

The time has come. Armed only with your wits, a little common sense, and some basic system knowledge, you're going to crack the performance problem bedevilling your database server. You roll up your sleeves and seat yourself firmly in front of a keyboard. A cluster of slightly awed colleagues watches wide-eyed over your shoulder.

OK, perhaps I'm getting a bit carried away here. Suffice it to say that the aim of this chapter is to develop a simple method for identifying performance problems on database servers.

I'm assuming you have already looked at the issues affecting server performance (covered in earlier chapters of Configuring and Tuning Databases on the Solaris Platform). Your application's behavior is well understood and consultants in small doses have already done wonders with application performance. You've done what you can to fix any database schema design problems and the addition of a couple of crucial indexes has already calmed the users down a little.

You've checked out obscure things like environment variables and racked your brains for other issues that might need attention. But performance problems still persist. Perhaps you need to upgrade your hardware, but at this point you're not sure.

Where should you start? I'm going to suggest a five-step process that will walk you through the major components of the system: memory, disk I/O, network, and CPU, followed by database monitoring and tuning. If a problem becomes apparent in one of these areas, there may be further steps to narrow the problem. This kind of "drill down" approach is an effective way to identify and ultimately solve problems.

If you find a bottleneck (by which I mean a constriction of performance, just as the neck of a bottle limits the flow of liquid into or out of a bottle), does that mean you should look no further? I would suggest going through the whole process anyway to see what you can discover.

Bear in mind, though, that fixing a bottleneck in one place might expose another elsewhere. Suppose, for example, your system is paging severely due to a lack of memory, but no problems are apparent elsewhere. Adding memory might allow your throughput to improve to the point where one of the disks becomes overutilized, resulting in a disk bottleneck. Checking out the disks as well might give a hint of problems to come.

Once you've found and resolved a bottleneck, go through the entire process again.

Finally, is the order of the steps important? Of course there are many possible ways of tackling system monitoring, but I suggest you go through the steps in the order shown.

STEP 1. Monitoring Memory

To check for a memory bottleneck, use the vmstat utility, which shows, among other things, memory behavior for the system. A 5-second interval is a good choice for live monitoring.

The vmstat trace in Figure 1 shows a system with no evidence of memory shortfall.

Figure 1Figure 1 vmstat trace with no memory shortfall

Figure 2 shows a vmstat trace from the same system during a severe memory shortfall.

Figure 2Figure 2 vmstat trace with severe memory shortfall

What to Look For

Look for po (pageouts—the kilobytes paged out per second) and sr (scan rate—the number of pages scanned by the clock algorithm). When both are consistently high at the same time (much more than 100 per second, say, on a system with up to 4 Gbytes of memory, more on a larger system), then it is possible the page daemon is being forced to steal free memory from running processes. Do you need to add more memory to the system? Maybe.

More memory may not help, though. That might sound crazy, but unfortunately the water is a little muddy here. Some explanation might help clarify the situation.

Pageouts can happen for a number of reasons, including the following:

  • Dirty (modified) file system pages are being flushed to disk. Such flushing is normal behavior and does not represent a problem. If database files are placed on file system files, expect to see this kind of pageout.

  • Application pages are being pushed out to the swap device to free up memory for other purposes. If the applications in question are active or about to become active, paging is bad!

  • New memory has been allocated by an application and swap space is being assigned to it. This, too, is normal behavior and does not represent a problem.

  • Memory pages have been freed by applications and are being flushed to disk. Isn't paging a waste of time if the memory is no longer required by the applications? You bet! Solaris 8 introduced a new madvise() flag called MADV_FREE to enable developers to tell the operating system not to bother to flush such pages to swap.

The scan rate is a measure of the activity of the page daemon. The page daemon wakes up and looks for memory pages to free when an application is unable to find enough memory on the free list (memory has fallen to the lotsfree system parameter). The greater the memory shortfall, the faster the page daemon will scan pages in main memory.

The major consumers of memory in a system are:

  • Applications, including text (binary code), stack (which contains information related to the current phase of execution of the program and its functions), heap (which contains program working space), and shared memory.

  • The file system page cache, which contains file system data (all file system disk blocks must first be read into memory before they can be used). This cache becomes important when database files are stored on file systems.

  • The operating system kernel.

Normal Paging Behavior Prior to Solaris 8

Before Solaris 8, the free column reported by vmstat may not be a good indication of the available memory in the system. The reason is that once memory pages are used by the file system page cache, they are not returned to the free list. Instead, the file system data blocks are left in the cache in case they are needed again in the future.

When the page daemon detects a memory shortfall and scans for pages to free, it may well choose to free some of the pages in the file system page cache. If the pages have been modified, they are first flushed to disk. There is no simple way of finding out how much of main memory is being used by the file system page cache at any point, but you can bet it will be substantial if database files are located on UFS files rather than raw devices. The memtool package (Richard McDougall's memory monitoring tool), available on the book website, can identify memory use by UFS files.

The problem is that the page daemon may free application memory pages as well as file system page cache pages since it doesn't know which is which. The result can be severe paging and major performance problems. Adding more memory won't help much, either. It will simply mean that more database pages can be cached in the memory. Fortunately, there is a solution.

Priority Paging

As of Solaris 7, a new feature called priority paging has been added. Priority paging lowers the priority of file system pages in memory so that the page daemon will choose to free them ahead of application pages. This behavior can make a huge difference to paging problems; priority paging should be enabled wherever databases coexist with active file systems, and especially where database files are placed on file systems.

You can activate priority paging by adding the following line to /etc/system and rebooting:

set priority_paging = 1

Patches are available for Solaris 2.5.1 and Solaris 2.6 to add priority paging functionality. From Solaris 8, changes to the virtual memory system mean that priority paging is no longer required and should not be used.

UFS Files and Paging

If your database files are UFS files rather than raw devices, you may observe significant scanning even once you have enabled priority paging. In fact, the scan rate may increase since priority paging causes the page daemon to become active sooner. This behavior is a natural consequence of the need to bring all database pages into the UFS page cache before they can be accessed by the database. The ongoing need to find free memory gives rise to constant scanning activity on busy database servers using UFS files.

If your application carries out updates, inserts, and deletes, you should also expect to see pageout activity. All database writes must go through the UFS page cache before being written to disk. Although the page being written to disk would previously have been read into the UFS page cache, the memory might have since been reused if the scan rate is high. In that case the page must be reread from disk before the write to disk can proceed. This process in turn displaces another page, and the cycle continues.

How do you stop all this paging activity? To eliminate scanning (assuming you have enough memory for your applications), either use raw devices or mount your database partitions with the Direct I/O flag (forcedirectio).

A word of caution: eliminating paging activity with Direct I/O may not always result in instant performance improvements. The file system page cache acts as a second-level cache for database pages, and removing it from the picture will expose any inadequacies in the sizing of your database buffer cache. Make sure that your database buffer cache is adequately sized; otherwise, you may find yourself with plenty of free memory and a database buffer cache starved of buffers.

Enabling Direct I/O for database files, and especially for database logs, can offer significant performance benefits as of the Solaris 8 1/01 release; earlier versions of Direct I/O may not offer significant performance gains. Direct I/O is described in more detail in "Unix File System Enhancements" on page 21 of Configuring and Tuning Databases on the Solaris Platform.

As a final caution, although Direct I/O can prove very useful for database files, do not enable it for nondatabase files without first examining carefully the performance implications of doing so.

Normal Paging Behavior as of Solaris 8

As we have seen, priority paging doesn't go all the way to solving the problem. Although the page daemon will choose file system pages in preference to application pages, the page daemon still has to search through the whole of memory to find them. And if large database buffer caches are being used, file system pages may only represent a small proportion of the total memory, so a lot of searching will be necessary.

As of Solaris 8, file system pages are separately accounted for, so they can be freed without a memory scan to find them. Consequently, the page daemon is not needed at all unless there is a major memory problem. As a result, the likelihood of paging problems is greatly diminished.

Drilling Down Further

If you want to find out where the memory is going, there are a number of options:

  • For the final answer on memory consumption, use memtool. The procmem script described below will provide a detailed breakdown of process memory usage. Both memtool and procmem are available on the book website.

  • Run dmesg and look for Avail mem. The difference between available memory and physical memory (use prtconf or /usr/platform/'arch -k'/sbin/prtdiag to find out the physical memory) indicates the amount of memory reserved for the kernel.

  • Use /usr/ucb/ps -aux to find out which processes are the major memory hogs. This command lists the percentage of memory used by each process. Beware, though! The memory listed is virtual memory, not physical memory, and it may not be a good indication of how much physical memory is actually being consumed by the process at any given moment.

If you have installed the unbundled memtool package, the procmem script will use the pmem program (similar to the standard Solaris pmap program, but with various bugs fixed on some Solaris releases). Some users are unwilling to install an unbundled package on a production system—procmem uses pmap instead if memtool (and therefore pmem) is not available. The procmem script summarizes memory use for all processes and gives a breakdown into resident, shared, and private memory usage. Please note that both procmem and memtool are unsupported software.

Passing the -h parameter to procmem results in the following usage information:

usage: procmem [-v] [-h | -p pidlist | [ -u username ] [ searchstring ]]
Examples:
procmem -p 10784 10759
   - show memory usage for processes with pids 10784 10759
procmem -u root
   - show memory usage for all processes owned by the root user
procmem -u "daemon root"
     - show memory usage for all processes owned by the root & daemon users
procmem netscape
   - show memory usage for process(es) in 'ps -ef' with "netscape"
procmem -u fred netscape
   - show memory usage for "netscape" processes owned by fred
procmem
   - show memory usage for all processes (provided current user has
    superuser access privileges)
Definition of terms
 'Kbytes' is the total memory size of the process or file.
 'Resident' is that portion currently occupying physical memory.
 'Shared' is resident memory capable of being shared.
 'Private' is resident memory unique to this process or file.
 Resident = Shared + Private
Sizing
 For reporting purposes, the 'Shared' component has been counted once
 only while the 'Private' component has been summed for each process
 or file. The /usr/lib shared libraries have been reported separately
 since they tend to be widely used across applications. To be totally
 accurate, though, the shared component of these shared libraries
 should only be counted once across all applications, not once for
 every group of applications. The same logic may apply to other
 shared libraries also used by multiple applications.

The -v flag offers additional detail. An example of procmem output follows for all processes on a server running an Oracle database.

Processes                  Kbytes Resident  Shared Private
---------                  ------ --------  ------ -------
Process Summary (Count)
 -csh (3)                    5376     2456    1064    1392
 -ksh (8)                   14624     2912    1456    1456
 automountd (1)              4088     3656    1792    1864
 cimomboot (1)               1576     1384    1256     128
 cron (1)                    1936     1744    1464     280
 devfsadmd (1)               2776     2536    1592     944
 devfseventd (1)             1272     1232     952     280
 dmispd (1)                  3160     2648    1744     904
 dtlogin (1)                 4920     2856    2192     664
 dwhttpd (2)                20080     7440    4496    2944
 esd (2)                    24712    21768    3880   17888
 grep (1)                     968      936     832     104
 in.ndpd (1)                 1856     1488    1304     184
 in.rdisc (1)                1616     1416    1272     144
 in.rlogind (6)             10368     2464    1360    1104
 inetd (1)                   2648     2384    1384    1000
 init (1)                    1888     1608    1136     472
 iostat (1)                  1824     1784     904     880
 ksh (5)                     9040     2320    1456     864
 lockd (1)                   1896     1656    1264     392
 lpsched (1)                 3040     1768    1552     216
 mibiisa (1)                 2952     2752    1504    1248
 mountd (1)                  2952     2536    1528    1008
 nfsd (1)                    1888     1672    1264     408
 nscd (1)                    3200     2928    1528    1400
 ora_ckpt_bench (1)       1405264  1372488 1371824     664
 ora_dbw0_bench (1)       1406976  1374208 1371824    2384
 ora_dbw1_bench (1)       1406968  1374200 1371824    2376
 ora_dbw2_bench (1)       1406968  1374200 1371824    2376
 ora_dbw3_bench (1)       1406968  1374200 1371824    2376
 ora_lgwr_bench (1)       1405248  1372472 1371824     648
 ora_pmon_bench (1)       1405696  1372920 1371824    1096
 ora_reco_bench (1)       1405160  1372384 1371824     560
 ora_smon_bench (1)       1405192  1372424 1371824     600
 oraclebench (50)        70258944  1400768 1371824   28944
 powerd (1)                  1632     1576     976     600
 rpcbind (1)                 2584     2088    1296     792
 sac (1)                     1736     1488    1296     192
 sendmail (1)                2936     2280    1816     464
 sh (4)                      4176     1320     912     408
 snmpXdmid (1)               3744     3184    2024    1160
 snmpdx (1)                  2144     1920    1520     400
 statd (1)                   2592     2208    1464     744
 syslogd (1)                 4192     3008    1456    1552
 tail (1)                     968      936     792     144
 tee (2)                     1808      952     792     160
 tpccload (50)             418800    38944    3344   35600
 ttymon (2)                  3464     1744    1336     408
 utmpd (1)                   1000      944     816     128
 vmstat (1)                  1312     1280     808     472
 vold (1)                    2696     2408    1768     640
 vxconfigd (1)              14656    13944    1328   12616
-----------------------------------------------------------------------------
File (Count)                      Kbytes Resident  Shared Private
-----------                       ------ --------  ------ -------
/usr/lib Shared Library Totals    291936    27928    2856   25072
Other Shared Library Totals      1473544    29304    7248   22056
Mapped File Totals                   560      488     488       0
Binary File Totals               1378632    18864   13328    5536
Shared Memory Totals            80280128  1360688 1360688       0
Anonymous Memory Totals            89680    84008       0   84008
-----------------------------------------------------------------------------
Grand Totals                    83514480  1521280 1384608  136672

The bulk of the 1.5 Gbytes of resident memory used on this server is accounted for by 1.3 Gbytes of shared memory, which also constitutes the major component of the memory used for the Oracle processes.

The script can be used to report the physical and virtual memory consumption for a group of processes. For example, procmem ora will report memory consumption for all processes that have the string ora in a ps -ef report (Oracle processes typically meet this criterion). If another Oracle user running the same applications is added to the system, you would not expect an increase in the Shared component of memory for /usr/lib shared libraries, other shared libraries, binary files, or shared memory segments. The Private component would be expected to grow, though, for the shared libraries, the mapped files, and anonymous memory. The additional private memory required would probably be roughly equivalent to the private memory total for these applications divided by the current number of users.

Detail is available for all processes, as well as summaries for /usr/lib shared libraries (which tend to be used by many processes throughout a system and so should be counted only once for sizing purposes), other shared libraries (for example, Oracle shared libraries), mapped files (memory-mapped file system files), binary files (executable programs), shared memory segments, and anonymous memory (heap and stack).

The procmem script will accurately show all memory directly used by processes, but not memory belonging to UFS files that are resident in the file system page cache. Since pages from UFS database files are not directly mapped into the address spaces of database processes, they will not appear in the totals. The memps -m command from memtool provides this information (it requires the memtool kernel module—installed when the memtool package is first set up—to be loaded).

What You Can Do to Reduce Paging

If you are using file systems for your database files, the first step is to upgrade to Solaris 8 or else enable priority paging for earlier releases of Solaris. If necessary, you could also consider include the following steps to relieve memory pressures on your database server:

  • Add more memory to the system.

  • Use Direct I/O for database files.

  • Reduce the size of the database buffer cache. This reduction may result in additional database I/O, but that is almost always preferable to paging.

  • Remove applications from the server. If applications are running on the server, move them to a client system and run the applications in client/server mode. Memory should be freed up as a result.

  • Reduce the number of users on the system.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020