Tuning U2 Databases on Windows. Nik Kesic, Lead Technical Support

Tuning U2 Databases on Windows

2

Nik Kesic’s Bio

Joined uniData in 1995

ATS (Advanced Technical Support), U2 Common Clients and DB tools

College degree in Telecommunications

Provides consultancy, Level 3 support and training

Published articles on web enablement using RedBack, Sockets, XML, SOAP, SSL and Encryption

Complex Performance Analysis

HUMAN

Money

Vacation

Nice Car

Nice Home

COMPUTER

CPU Speed

Disk criteria

Tasks

4

Agenda

Webinar origins

Basic Windows architecture

U2 and Windows interaction

Windows tuning considerations

U2 config parameters

System Performance Tools

Services Available

Webinar Origins

Microsoft releasing new products every few years

Windows becoming more complex

Customers reporting performance issues on Windows

Basic Windows Architecture

6

• OS interacts

directly with the hardware

• OS is called system kernel

Basic Windows Architecture

Operating system: Manages the resources of a computer

Resources: CPUs, Memory, I/O devices, Network

Kernel: Memory resident portion of the Windows system

File system and process control system: Two major components of Windows kernel

Basic Windows Architecture

Major tasks of kernel

Process Management

Device Management

File Management

Additional services for kernel

Virtual Memory

System Protection

Networking

Network File Systems

Block Diagram of System Kernel

Hardware

Device Drivers _{Device Drivers}Graphic

Microkernel System Services I/O Manager Win 32 Window Manager & GDI Object Manager Security Reference Monitor Process Manager Local Procedure Call Facility Virtual Memory Manager Executive Services

Windows NT Executive

User Mode Kernel Mode POSIX Subsystem POSIX Application Security Subsystem Logon Process OS/2 Subsystem OS/2 Application Win32 Application DOS/Win16 Application NTVDM Win32 Subsystem

Process Control Subsystem

Process synchronization

Inter-process communication

Memory management

Scheduler

Process scheduling

Allocate CPU to processes

Files and Directories

A directory is a collection of files and sub-directories on a disk or tape in standard Windows file system format.

The kernel’s file subsystem regulates data flow between the kernel and secondary storage devices_{The kernel’s file subsystem regulates data flow}

Hardware Control

Hardware control handles interrupts

Networks, disks or terminal devices may interrupt the CPU

Kernel resumes interrupted process

Windows API

Device Management: DeviceIoControl, InstallNewDevice,

RegisterDeviceNotification, UnregisterDeviceNotification

File I/O: CopyFile, CreateFile, DeleteFile, OpenFile, ReadFile

Desktop Windows Mgr: WM_DWMCOMPOSITIONCHANGED,

WM_DWMSENDICONICTHUMBNAIL

Others: GetCursor, Button,

Interlude

“

U2 products are not like bowls of fruit

They do not go bad over time…

But files can…

”

U2 and Windows Interaction

U2 databases and utilities are a series of ‘C’ and ‘C++’ programs

U2 uses the NTFS file system for data storage

U2 processes use the inherent Windows Device I/O Control

U2 typically uses shared memory segments for:

Printer management

Program memory management

U2 can leverage O/S level functionality

Number, Type & Speed of Processors

Larger L2 processor caches provide better

performance

Two CPUs are not as fast as one CPU that is twice

as fast

A dual core processor is not twice as fast as a

single core processor

Amount of Physical Memory (RAM)

When your computer is running low on memory and more is needed immediately, Windows Servers use hard drive space to simulate system RAM (virtual memory or paging file)

Try to avoid having a pagefile on the same drive as the operating system files

Avoid putting a pagefile on a fault-tolerant drive, such as a mirrored volume or a RAID-5 volume

Don't place multiple pagefiles on different partitions on the same physical disk drive

Type of Disk Controller, Number of

Physical Disks & Their Capacity

File Servers cache frequently accessed files in memory; however, files that are not accessed frequently must

come from disk

Handling large amounts of data with a high number of requests to a high number of files require good disk performance

RAID controller connected to a large number of disks

Make sure the allocation unit size is appropriate for the size of the volume

Memory / CPU

PagedPoolSize

HKLM\System\CurrentControlSet\Control\SessionManager\MemoryManagement\(REG_DWORD)

File cache space and paged pool space share a common area in system virtual address

Limiting the paged pool allows for a larger system cache

Causes more content to be cached and allows faster serving of files

PagedPoolSize registry example

DISK I/O:

NtfsDisable8dot3NameCreation

NtfsDisable8dot3NameCreation

HKLM\System\CurrentControlSet\Control\FileSystem\ (REG_DWORD)

Default is 0

Determines whether NTFS generates a short name

DISK I/O: Disablelastaccess

Disablelastaccess

HKLM\System\CurrentControlSet\Control\FileSystem\. (REG_DWORD)

Not created by default

Increases speed of access to a folder or file

Can have significant impact with NTFS, high numbers of folders/files, frequent updates

After you use this command and restart the computer, the Last Access Time is no longer updated

For new files, Last Access Time remains the same as the File Creation Time

DISK I/O: NumTcbTablePartitions

NumTcbTablePartitions

HKLM\system\CurrentControlSet\Services\Tcpip\Parameters\. (REG_DWORD)

Not created by default

Controls the number of TransportControlBlock (TCB) table partitions

Windows 2003 only; obsolete in Windows 2008

Improves scalability on multiprocessor systems by reducing contention

DISK I/O: NTFS File System Setting

NTFS File System Setting

HKLM\System\CurrentControlSet\Control\FileSystem\ is NtfsDisableLastAccessUpdate

(REG_DWORD) 1.

System-global switch

Does not exist by default

Reduces disk I/O load and latencies

Effective when used with large data sets/number of hosts containing thousands of directories

Use IIS logging instead for Web administration

In Windows 2008 this is disabled by default

netsh command

To check the current status of the TCP/IP parameters which can be tweaked

netsh interface tcp show global

TCP / IP

Set maximum size of TCP window

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Tcp WindowSize=[wmax]

Turn on window scaling option

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameter s\Tcp1323Opts=1

TCPTimedWaitDelay

HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

Determines the time that must elapse before TCP can release a closed connection and

reuse its resources.

Virtualization

Virtualized Servers provide the foundation for building

and managing a virtualized IT infrastructure

A single physical server can run several virtual machines

simultaneously

• Each of these machines believes it

is running on its own dedicated hardware, as if it were separate

Virtualization

UVCONFIG Parameters

MFILES

T30FILE

UVTEMP

FLTABSZ

FSEMNUM

GSEMNUM

GLTABSZ

RLTABSZ

RLOWNER

UVTSORT

TXMEM

UDTCONFIG Parameters

GLM_MEM_ALLOC

NFILES

SHMMAX

SHMMIN

SHM_GNPAGES

SHM_GPAGESZ

SHM_MAX_SIZE

TMP

Connection Pools

Client / Server applications with non-persistent

connections can improve performance by adopting

Windows and U2 Performance Tools

Process Monitor

Process Explorer

Windows Performance Monitor

uvdiag

udtdiag

Process Explorer

udtdiag

udtdiag ships with UniData in the udtbin directory

Latest version is also available in entitled tech note SFMA-9735

U2 technical support providers and customers use output to diagnose problems on a UniData (UD) system

Gets info from the UD log files, UD commands, and

operating system commands and produces a snapshot

C:\$UDTBIN\udtdiag target_directory C:\.udthome\bin\udtdiag -h

uvdiag

uvdiag is available with UniVerse 11.1.0 and higher

Also available in public tech notes UNV-4 and UNV-8 for Windows respectively

Preserves data about the UniVerse (UV) system for future analysis

Only administrator can execute this script

Users can be active on the system

C:\.uvhome\bin\uvdiag target_directory C:\.uvhome\bin\uvdiag -h

Conducting a Benchmark: Approach

Create a benchmark indicative of functions being performed in the applications running on the U2 databases

Determine the type of CPU and I/O load you want to sustain during benchmarking

Generate good metrics to compare the environment before and after applying tuning parameters

Use Windows utilities to monitor system activities during the benchmark

Identify Tuning Areas: Memory/CPU

Review Memory Capacity

Review CPU Capacity

Check for paging

Check for high CPU usage by a process

Identify Tuning Areas: Disk I/O

Check parameters for tuning NTFS file system

Check RAID configuration

RAID 10 is a good performer for U2 databases

Turn NTFS file system logging off

Spread files over multiple spindles in a LUN (Logical Unit Number)

Benchmark: Be Proactive

Perform Windows health checks on at least a yearly basis

Perform U2 health checks on a yearly basis

Monitor the system using the Windows performance utilities

Check logs on a regular basis

Monitor I/O and CPU usage

Make sure the system is not paging

Services Available

New System Optimization

Make sure U2 is optimized for the new hardware

Ongoing Annual HealthCheck

40% of all down systems are caused by personnel errors

We review the system annually to try and avert problems

42

For more information about Health Checks email: [email protected]

Questions & Answers

Or you may email us your question later at:

[email protected]

To ask a question:

• Click on hand icon with green arrow and we will call your name

44

Important Disclaimer

THE INFORMATION CONTAINED IN THIS PRESENTATION IS PROVIDED FOR INFORMATIONAL PURPOSES ONLY.

WHILE EFFORTS WERE MADE TO VERIFY THE COMPLETENESS AND

ACCURACY OF THE INFORMATION CONTAINED IN THIS PRESENTATION, IT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED.

IN ADDITION, THIS INFORMATION IS BASED ON ROCKET SOFTWARE’S CURRENT PRODUCT PLANS AND STRATEGY, WHICH ARE SUBJECT TO CHANGE BY ROCKET SOFTWAREWITHOUT NOTICE.

ROCKET SOFTWARE SHALL NOT BE RESPONSIBLE FOR ANY DAMAGES

ARISING OUT OF THE USE OF, OR OTHERWISE RELATED TO, THIS

PRESENTATION OR ANY OTHER DOCUMENTATION.

NOTHING CONTAINED IN THIS PRESENTATION IS INTENDED TO, OR SHALL HAVE THE EFFECT OF:

• CREATING ANY WARRANTY OR REPRESENTATION FROM ROCKET

SOFTWARE(OR ITS AFFILIATES OR ITS OR THEIR SUPPLIERS AND/OR LICENSORS); OR

• ALTERING THE TERMS AND CONDITIONS OF THE APPLICABLE LICENSE

Trademarks and Acknowledgements

The following are trademarks or registered trademarks of Rocket Software, Inc.: Dynamic Connect, SystemBuilder, U2, U2 Web Development

Environment, UniData, UniVerse, and wIntegrate.

IBM, the IBM logo, AIX, and DB2 are trademarks of IBM in the United States and other countries.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Microsoft, SQL Server, Windows, and Excel are trademarks of the Microsoft group of companies.

UNIX is a registered trademark of The Open Group.

Other company, product, and service names mentioned herein may be trademarks or service marks of others.