Learn 10 Tips for How You Can Improve JD Edwards Database Performance
Bill Rehm - Database Team Manager - Sr. Customer Success Manager
How can I improve my JD Edwards Database Performance in JDE E1? There are probably a hundred tips that I could give you on how to speed up JDE E1 and choosing just ten was kind of difficult. Should I list tips only for MSSQL or Oracle? What about generic tips? I decided to include a variety of tips that I recommend the most often overall. They generally offer the greatest benefit with the least amount of complexity. Now, you're going to look at a few items on this list and say, "Wait a second, this one right here is really difficult!" You'd be right to think that, but here's a secret: It's tough to do any performance tuning on a database that isn't somewhat complex. That's just databases for you.
Before you get in to the list, I have some disclaimers. This is a list of what I think are the ten best tips. Other DBAs may pick a different list or even say that what I've got listed here is a bunch of crazy talk. I also have to caution you that making some of the changes in here might be beyond your capabilities, budget, or both. Please don't do anything without doing research and determining if it's actually right for your system. Also, I know you understand every system is different. On one installation a particular tip could improve performance by 50%. There might be one IT shop that wouldn't see any improvement at all.
Finally, here are my ten most recommended tips for improving JDE database performance. I've included some links for reference, but I encourage you to spend some time reading as much about each topic as possible. Your database is the only one like it in the world, make sure your solution is the best one for you.
Top Ten Ways to Improve JD Edwards Database Performance
1. Solid State Drives
Nothing speeds up a JD Edwards production system faster than moving to solid-state drives. You could just put redo/transaction logs on SSD or move all your production and system data. It may be expensive to move everything to SSD, but the benefit is enormous.
2. Remove Unused Indexes to improve JDE Database Performance
Every index on a table adds an extra read or write to every transaction. If an index isn't used very often or not at all, it's stealing performance. You can test performance first without dropping the index:
- Oracle
- Make the index invisible - ALTER INDEX index_name INVISIBLE;
- Index is still maintained like any other index, but optimizer does not use it.
- MSSQL
- Disable the index - ALTER INDEX IX_IndexName ON Schema.TableName DISABLE;
- Index is not maintained and not used by the optimizer.
If it tests okay, then you can drop the index. Make sure you remove the indexes from JDE E1 specs as well, otherwise if you regenerate indexes using OMW they will come back.
3. Don't Generate Indexes from OMW
By default, OMW drops and recreates all indexes on a table. You can't change this behavior. Index generation from within JDE is an OFFLINE operation, meaning the table is not usable until ALL the indexes are regenerated. On a large, busy table this will cause your system to grind to a halt. Better idea: Create the indexes directly on the database. If they work for you, then create them inside JDE. DO NOT generate them. Simply promote them using OMW so the specs are saved.
4. MSSQL: Tempdb Optimization
Many MSSQL shops have only one file, or several files of different sizes and growth rates. Ideally you should have (or start with) eight identical, fixed size tempdb files then adjust as necessary. Make sure you allocate more space/files when enabling RCSI.
5. MSSQL: Adjust Max Server Memory
A common setting that installers overlook is the max server memory. By default, it is set to its maximum value of 2147483647 MB - 2.15 Petabytes! MSSQL will use all of your physical memory if you don't change this value. This can starve the operating system and other processes of memory and bring your system to a crawl. A good starting rule of thumb is to set the max server memory to 90% of the total available RAM.
https://msdn.microsoft.com/en-us/library/ms178067.aspx
6. MSSQL: Read-Committed Snapshot Isolation (RCSI) to Improve JDE Database Performance
Are you seeing a lot of blocking on your database? By default, Microsoft SQL Server uses "pessimistic locking". This means that read operations block writes and vice-versa. Enabling RCSI changes it to "optimistic locking", meaning the database is "optimistic" that the results of your read operation will not change. If it does change, the database simply re-reads the data after the write is finished. Since most write blocks do not change the data you're reading, implementing RCSI is almost always a huge performance improvement. NOTE: This requires increasing the amount of Tempdb space available.
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1275500.1
7. Oracle: Create Data Files at their Maximum Size
If you're using a smallfile tablespace (the default), you will have one or more data files that are 32GB or less. When the file has to grow, it causes a performance impact. Avoid the overhead of file growth operations on large, constantly growing data files by creating them at full size right away. It also covers you when you make a mistake on file growth settings.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles002.htm
8. Oracle: Redo Log Tuning
Your redo logs should switch about five times per hour and have enough redo log groups that each one can write out to the archive logs before the logs switches get back around. If you have less than 15 minutes between switches it can slow down your system. More than 15 minutes keeps too much data that's not being written to the database and puts you at greater risk for data loss in the event of disaster. When you figure out the right size you need, simply add them all to your database and force log switches until all your old logs are inactive. Once you've got that, delete the old redo logs.
9. Oracle: AIX/Linux - Huge/Large Pages
The memory page size on these operating systems is quite small out of the box. There are differences in opinion about the overall performance benefit of this setting, however it does provide protection against SGA swapping on memory-dependent systems. When dealing with large Oracle SGA (System Global Area), you can improve performance by reducing the number of OS memory pages the database has to deal with.
In AIX it's called large pages and on Linux it's huge pages. The size of your pages depends on the size of your SGA, the operating system, and the capability of the hardware. The general advice is to implement 16MB on AIX and up to 1GB on Linux. Create enough pages at the larger size to accommodate your entire SGA.
https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
10. Oracle: Pin the SGA to improve JDE Database Performance
This goes hand-in-hand with huge/large pages discussed above. "Pinning" means that the entire SGA will be loaded in to the memory pages you set aside, and it will never be swapped out to disk. If you don't pin, the SGA can get swapped out to disk due to memory exhaustion. Your database will run much more slowly if that happens. Swapped memory pages won't get moved back to disk even when the memory pressure subsides, so your database will continue to run poorly.
I hope these 10 tips help with your JD Edwards Performance. To find about GSI's JD Edwards Database Practice or any other of our products or services, call us at 855-GSI-4ERP or click on CONTACT US to send us a request for more information.
Meet the Author
Bill Rehm
CONTACT US TODAY
FOR MORE INFORMATION ON GSI'S SOLUTIONS & SERVICES