Troubleshooting Guide: Known causes of slow database performance
The purpose of this document is to assist you in determining the possible causes of database performance issues. It is not intended to diagnose performance issues relative to ALL databases on a particular server.
Resolving the problem
The most common reasons for slow database performance are related to one or a combination of issues in these areas:
A. Hardware/OS-related causes
B. 3rd party application interference
C. Database Design
D. Database corruption (would also be paired with other symptoms)
Note: Database corruption potential is discussed within the Database Design section.
|A. HARDWARE/OPERATING SYSTEM RELATED CAUSES:|
- Limited amount of available disk space on the server or clients.
- Out of date/obsolete Network Interface Cards (NIC) or other server or client hardware.
- Older client operating system (OS), or not the most recent OS or Service Pack installed.
- Slow processing speed of client or server (Random Access Memory (RAM), etc.).
- Detected and undetected viruses on a client or server machine.
- Network issues, excessive network traffic, network bottlenecks, OR database traffic.
TROUBLESHOOTING Hardware or Network-related issues:
1. Verify the disk space, virtual memory, etc. available on client and server.
2. Verify that all the hardware, software, service packs, and anti-virus applications are current.
3. Become aware of any diagnostics running on the client or server, such as Win MSD, NSD, etc.
4. For the Microsoft Windows OS, monitor the Task Manager; for other client operating systems, monitor memory and central processing unit (CPU) usage for the machine during database operations.
5. Run a network trace on the system/Notes network environment.
6. Create a local replica of the database in question. Does the issue still occur (after removing the network and server from the environment)?
Note: New database copies and replicas do not include view indexes. Opening the database and views will initially be slower while the view indexes are being built.
7. If the issue happens for only a small percentage of users, have one user take his/her id file to another machine and retest.
8. Next, give that user a machine with a different OS, same Notes client version, and retest.
9. Was the client OS/software installed via an image? If so, please install the OS via CD. Then add ONLY the Notes Client. Does database performance improve?
10. If possible, rebuild a client machine completely. For cases where performance issues occur for only a small percentage of users, it has been found that the primary solution is to give that user a different machine, if possible. New hardware and a new build of a workstation resolves a high percentage of database performance issues.
|B. 3rd PARTY APPLICATION INTERFERENCE:|
3rd party applications can affect database performance. Here are some examples:
- Anti-Virus software running in the background OR simply some releases of anti-virus software.
- Other applications and software (3rd party) installed on the machine; those that interface with Notes or not.
- Older version of a virus scan software.
- Daily or weekly virus scans. These scans would also slow down overall performance of the entire workstation, not just the Notes Client.
- Notes and database performance can also be affected by programs running in the background, such as those launched via the Startup menu. It is common to forget that all programs launched at startup are always running in the background.
- Pop-up programs can also slow workstation performance and interfere with Notes data.
TROUBLESHOOTING 3rd Party and Software issues:
1. Create a local replica of the database. Does the issue still occur?
2. Start the client machine in Safe mode and retest (using a local replica).
3. Run WinMSD on the client to determine memory usage, etc.
4. Through the Windows Task Manager or other OS task bar, disable all other applications running in the background. Retest with the server-based copy first, then test with the local replica.
5. Rename the desktop file (Desktop6.ndk in Notes 6.x and 7.x, or Desktop5.dsk in Notes 5.x), cache file (Cache.ndk in 6.x and 7.x, or Cache.dsk in 5.x), and bookmark.nsf file on the client, then restart Notes and retest. These files exist in the Notes \data directory on the client.
6. Uninstall some of the 3rd party applications, and retest.
7. If necessary, please uninstall all other 3rd party software from the client; OR rebuild a machine to contain just the OS and Notes Client; OR build a new client workstation with just the OS and the Notes Client, all installed via CD instead of an image.
8. Test the database in multiple versions of the Notes Client, and when necessary, reinstall the Notes Client.
9. Install a tool that will eliminate pop-up ads.
Why does a Notes document containing a large file attachment take a long time to open? (#1085177)
|C. DATABASE DESIGN: Possible causes and known issues:|
Most common cause
The most common reason for slow database performance is based on this "equation":
(number of users)
x (size of database)
x (number of views)
x (number of docs in each view) x (frequency of document updates)
x (network latency)
Another factor to consider
How many "All documents" views exist in the db, and if these all docs views are exposed to users? Typically it is recommended to have, at most, one true "all docs" view that is hidden, rather than used in the UI by the users.
List of possible, individual causes
Note: Several of these causes may be at play in any one database.
- Large number of documents in the database. In addition, if there are views that reference all documents in the database, this could also decrease performance.
- Large number of views in a database.
- Complex View Selection formulas or column formulas in a view.
- Columns or view selection formulas that include time functions. For example: @Today, @Now, etc.
- Frequent view index refreshing and/or Full text indexing of a large database.
- Readers fields on documents.
- @DbLookup and/or @DbColumn formulas (which also overlaps with network-related issues).
- Some Database properties can slow performance while others improve performance.
- Fixup, Compact and Updall -r can slow down performance of database open. For example, running any of these database utilities against the same database that a user is attempting to open or within which he/she is attempting to manipulate data can slow performance.
- Large cache.dsk/cache.ndk on a workstation.
- Private or Private on First Use views that are stored on the server.
- Multilingual databases. These databases are slower to open than unilingual databases.
- Large attachments in databases.
- A large UNique Key Table (UNK table), meaning there are many unique fields in the database.
- Database or View corruption.
- Inefficient coding/programming of custom applications. The list of available Domino programmability features continues to expand with each new major release. Thus, it is a good idea to review a custom application when a major upgrade is being planned, to review new programmability features.
TROUBLESHOOTING Design related performance issues:
1. Verify that all of the design elements having known issues are not in use in the database.
2. Create a copy of the database and retest. Does performance improve? Many of the steps below can be performed on this database copy.
3. Reduce by half the number of documents in the database. Does performance improve?
4. Eliminate all documents in the database, and create one new document. Does performance improve?
5. Reduce the number of views in the database. Does performance improve?
6. Simplify the view; for example, eliminate complex column and View Selection formulas.
IMPORTANT NOTE: For best results, it is more efficient that a view column reference a field on a document, instead of execute calculations at the view level. It is best to eliminate this type of code at the view level, and ensure that your views have simpler, more efficient formulas.
7. Eliminate @functions and other formulas based on elements of time (@Today, for example) where possible. For tips on building views that use time/date as a factor refer to the technote:
8. Create a very simple view that references a small subset of documents, but has no other column formulas. Does this view behave differently?
9. Some Database properties can slow performance while others improve performance. Refer to the following Information Center topic for more information:
10. Check all forms and views for @DbLookup and @DbColumn formulas. Are there any? How many? These types of @Functions are known to slow database performance, since they execute a function call to another view or database, across the network.
11. Run Fixup, Compact -c and Updall -r on the database in question, to see if any corruption is detected.
Note: The compact switch c causes copy-style compaction to take place. It SHOULD NOT be used in cases where
transaction logging is being used. In these cases use either the b switch or the default; these switches use in-place compaction which is safe for use with transaction logging.
Review the following technotes for details on the Compact and Updall switches:
Switches for COMPACT Server task for Domino (#1084388)
Notes Indexing Basics: NIF, Update, Updall (and Updall Switches) (#1085954)
12. Change the indexing property of views to refresh less often.
13. If relevant, delete the full-text index (FTI) of the database and retest. Then re-create the index and retest.
14. Modify the list of indexed items in the database. Remember that large attachments being indexed can slow database performance.
For best practices with view indexing and the update task, please refer to the following technotes:
What causes Notes/Domino indexes to rebuild? (#1090329)
The basics of view index refreshing (#7002624)
15. Create a new replica of the database. Does this make a difference? (See note below next tip).
16. Create a new copy of this database. Is there a significant size difference? Does the database copy perform differently?
Note for steps 15 and 16 that new database copies and replicas do not include view indexes. Opening the database (and hence, the default view) and views will be slower while the view indexes are rebuilt. It may be best to run Updall with the -C option before accessing the database. This switch forces a rebuild of all views which are not already rebuilt.
17. Reduce the number of Readers fields in an application. For more information see this technote:
18. Reduce the number of Private or Private on First Use views that are stored on the server. Also, unnecessary duplication of the personal version of these views/folders should and can be avoided. For more information see this technote:
19. Determine if the database has a large UNK table. There is a utility available which shows the UNK table of a database. Please refer to the online resource document below for more information:
If none of the above steps resolve the performance issues, it would then be necessary to perform an entire application review of all code, agents, forms, views, etc. From the application review, it may be necessary to redesign either parts of or the entire application.
|Other Performance related technotes:|
Slow performance when looping via GetNthDocument in Notes (#199588)
LotusScript performance problems after upgrade to Notes/Domino 6 (#1176211)
Slow mail delivery (performance degradation) when large group is bcc'd in mail message (#1089346)
Performance and usability issues when using LotusScript vs. @formulas (#1110222)
Administrator's guide to Domino Server maintenance (#7006573)
|Messaging Applications||IBM Domino||8.5, 8.0, 7.0, 6.5, 6.0|
|Messaging Applications||IBM Notes||Not Applicable||8.5, 8.0, 7.0, 6.5, 6.0||All Editions|