 |
Knowledge Collection/Troubleshooting Guide: Known causes of slow database performance
|
| | | | Question | 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.
See also, "Knowledge Collection: Domino Server Performance" (#7003037) | | | | | Answer | The purpose of this document is explain the most common reasons for database performance issues. It is not intended to diagnose performance issues related to ALL databases on a particular server.
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.
Related Technotes:
Troubleshooting Script: How to diagnose Notes and Domino TCP/IP issues (#7003388)
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.
Related Technote:
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:
***** 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 (frequency of document updates) x (processor) x (network latency)
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:
Time/Date views in Notes: What are the options? (#7003557)
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 technote 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:
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:
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.
Summary of technotes cited above:
Troubleshooting Script: How to diagnose Notes and Domino TCP/IP issues (#7003388)
Why does a Notes document containing a large file attachment take a long time to open? (#1085177)
What causes Notes/Domino indexes to rebuild? (#1090329)
The Indexer and its functionality (#7003075)
The basics of view index refreshing (#7002624)
How to optimize database performance using Database properties (#1085205)
Switches for COMPACT Server task for Domino (#1084388)
Notes Indexing Basics: NIF, Update, Updall (and Updall Switches) (#1085954)
Tool to show the UNK Table size for a Notes database (#4004373)
Time/Date views in Notes: What are the options? (#7003557)
Use of ReaderNames fields slows view performance in Notes (#1097609)
Opening document in Read mode is slow when form contains @DbLookup or @DbColumn (#1100081)
Performance degradation using "Private on First Use" views/folders (#1322578)
How to obtain a full Microsoft Diagnostics Report (MSD) for analysis (#1203322)
R5-only issues:
Too Many Unique Fields in Domino Directory Slow LDAP Queries (#1095208)
R5 Full-Text-Indexed Documents Containing Large Attachments Taking Long time to Open (#1095991)
Notes Client/Server Hang in Agent/LotusScript Getting Handle to Private-on-First-Use Folder (#1085379)
Other Performance related technotes:
Knowledge Collection: Domino Server performance (#7003037)
Slow performance when looping via GetNthDocument in Notes (#199588)
Agents with numerous Script Libraries load slower in Domino 6 (#1108765)
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)
Multilingual databases take a long time to open (#1084805)
Performance and usability issues when using LotusScript vs. @formulas (#1110222)
Administrator's guide to Domino Server maintenance (#7006573)
R5-only issues:
Agent Performance: LotusScript Send method runs slower than expected (#1102279)
Out of Office Agent takes a long time to run on larger mailfiles (#1097464)
Agent performance degrades when Doc.send is used to send more than 800 messages (#1102279)
LotusScript NotesRegistration Agent causes server to slow or crash when password recovery is enabled (#1098571)
Guidelines on Performance of Notes R5 Client on Macintosh platform (#7003268)
Long Full-Text Query string with special characters results in slow R5 Server performance (#1098296)
DEFINITION OF A KNOWLEDGE COLLECTION
A focused compilation of documents about a specific product or topic and serves as a source for general reference and as a supplement for training material. Knowledge Collections are not designed to be an all-inclusive list of all documents dealing with the specific topic. | | | | | | | | Cross Reference information | | Segment | Product | Component | Platform | Version | Edition | | Messaging Applications | Lotus Domino | Lotus Domino Server | | 8.0, 7.0, 6.5, 6.0, 5.0 | | | Messaging Applications | Lotus Notes | Not Applicable | Linux, Mac OS, Windows | 8.0, 7.0, 6.5, 6.0, 5.0 | All Editions |
| | |
|
|
| IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml. |
|
|
|
|
| Please take a moment to complete this form to help us better serve you. |
|
 |
|
|
|
|
|
|
| Product categories: |
|
| | Software | |
| | Messaging Applications | |
| | Advanced Messaging | |
| | Lotus Domino | |
| | Lotus Domino Designer | |
|
| Operating system(s): |
| |
Windows
|
|
| Software version: |
| |
5.0, 6.0, 6.5, 7.0, 8.0
|
|
| Software edition: |
| |
All Editions
|
|
| Reference #: |
| |
1174563
|
|
| IBM Group: |
| | Software Group |
|
| Modified date: |
| | 2007-11-01 |
|
|
