Deploying and Migrating Enterprise Databases on SLES9

Marcin Kurc & Jens W. Sandmann

Linux Impact Team

Novell

Introduction

Todays businesses are dependent on being able to store, manipulate and retrieve data in a highly efficient and reliable manner.

Linux is the ideal platform to run these mission critical systems on (***......)

We will discuss today how Linux, OSS and third party solutions can meet this important business need.

Agenda

Stuff we will cover:

Main Players

Enabling Technologies

Storage

High Availability

Databases on SUSE LINUX

Best Practices

Migration

Main Players

Main Players

Commercial Databases

Microsoft

Oracle

DB2

Sybase

Software AG

Market Share – Commercial DB's

Gartner Dataquest (May 2004)

Main Players

OSS Databases

MySQL

MaxDB – SAP DB, given to MySQL

Firebird

PostgreSQL (various incarnations, RH-DBS....)

Plenty of others

Market Share – OSS DB's

Evans' Database Development Survey 2005, Winter

Enabling Technologies

Enabling Technologies

File Systems

Polyserve – Clustered File System

Veritas - Cluster Server

GFS – RedHat's Clustered FS. Formerly Sistina

OCFS – Oracle's Open Source Clustered File System

RAW – Directly written data

Enabling Technologies

Volume Management

LVM / LVM2

EVMS

Veritas

Polyserve

Enabling Technologies

Interconnects

Infiniband – 30Gb/s, Intel backed interconnect.

Myrinet – Full duplex, 2+Gb/s interconnect.

Gig-E – Cheap, prevelant

10G Ethernet – Nexgen E-net

Hiper-sockets – Mainframe “interconnects”

Enabling Technologies

uDAPL

uDAPL is a System Area Network (SAN) provider that enables an application to bypass the standard TCP/IP provider and use the native transport to communicate between hosts in a cluster of servers and workstations on the fabric. This also enables the applications to take advantage of the underlying transport service provided by InfiniBand to permit direct I/O between the user mode processes.

Enabling Technologies - uDAPL

CmdLine Debug Tool

UDAPL Redirector

RealTime DebugInterface

EVD/CNO/PROXY Internal Service

Management Interface

Connection Management

Verbs

IpoIB Driver

UDAPL Application

CM Service

Resource Manager

PnP Service

Data Transfer & Completion Service

Management Interface

Connection Management

Verbs

Application

Library

User Mode

Kernel Mode

Hardware

uDAPL

User Mode AL

Kernel Mode AL

Storage

Storage

Comparison of NAS, SAN and directly attached storage

Features

Performance

Reliability

Directly Attached Storage

Pros:

time tested and proven access method regardless of the storage device, database. app, I/O interface

Cons:

limited scalability

can't be shared

NAS

Pros:

can be reconfigured dynamically

sharing is possible

Cons:

extra workload placed on the network

no fast I/O

SAN

Pros:

sharing possible

can be configured dynamically

uses it's own fabric

fast I/O

Cons:

sharing requires global filesystem

High Availability

High Availability

Oracle Real Application Clusters (RAC)

Improved throughput

Scalability over single instance systems

Improved response time

High availability

Transparency

High Availability

DB2 – Integrated Cluster Environment (ICE)

Scalability

Price/performance

Availability

Manageability

Interoperability

High Availability

MySQL Clusters

Cost-effective

99.999% Availability

Sub-second Failover

Linear Scalability

High Performance

Easy Administration

Support, Services, & Training

High Availability

Polyserve Overview

Enables multiple servers to concurrently read and write to shared storage

Supports multi-path I/O for redundant SAN fabric

Fully symmetric with no central lock or metadata servers — no single point of failure

Flexible — servers and storage can be added dynamically

Highly scalable (up to 16 servers)

Fully journaled for fast, on-line recovery of file system without interruption

Single point of administration from any server

No changes to applications required

High Availability

Polyserve - Oracle

PolyServe Matrix Server: Most Technical Value-Add for Oracle

Oracle Disk Manager: Better than raw performance

Clusterwide I/O stats

Shared Oracle Home and rolling SUSE and Oracle upgrades

Polyserve (cont.)

“Free” High Availability

Eliminate active/passive pairs

Easier Oracle Manageability

Shared Oracle home

Cluster-wide I/O statistics

Flexible Deployment

Rebalance load within the cluster without data migration

Better Performance

Oracle Disk Manager support

Polyserve (cont.)

Simplified DB2 Clusters

Shared DB2 home

Single repository for database data

Databases in files with performance equal to raw (rendered direct I/O)

Simplified High-availability

All databases become highly available

PolyServe-provided DB2 failover agent

Flexible Deployment

Easy rebalancing of load among cluster nodes

Databases on Linux

Why Linux/SLES for Database Servers

Why is Linux/SLES an ideal OS for DB Servers?

Cross/Multi Platform Support

I/O Characteristics

Scalability

RAS – Reliability, Availability, Serviceability

Performance

SUSE LINUX Kernel Features

Why Linux/SLES for Database Servers

Cross Platform Support

Desktop

Intel x86 Pentium, Xeon (32 bit)

Intel Itanium 2 (64 bit)

AMD x86 (32 bit)

AMD64 (32/64 bit)

Intel EM64T

IBM POWER

IBM zSeries and IBM S/390

Server

Main Frame

AutoBuild

Why Linux/SLES for Database Servers

I/O Characteristics

Asynchronous I/O (AIO) - enables a process or a thread to overlap I/O operations with other processing, by providing an interface for submitting one or more I/O requests in one system call without waiting for completion, and a separate interface to reap completed I/O operations associated with a given completion group.

Raw I/O – enables to bypass buffer cache

Modular I/O Scheduler – algorithm most suitable for workload can be chosen dynamically

Why Linux/SLES for Database Servers

Scalability

Support for 512/1024 CPUs for vertical scaling

Support for 2^32 devices vs. 2^16 in 2.4 kernel

2^15 vs. 256 SCSI devices

Support for file-systems larger than 2TB

Support for >=4GB RAM

Why Linux/SLES for Database Servers

Features to improve RAS

Multipath I/O

LVMs/EVMS

Hotplug

LKCD – Linux Kernel Crash Dumps

Reliability, Availability, Serviceability

Best Practicses

Optimizing the OS

Main Factors Influencing Performance

I/O Subsytem

Scheduler

Buffers and Caches

File Systems

VM

hugepages

swappiness

lower zone protection

memory lock

bdflush

Optimizing the OS

Main Factors Influencing Performance

CPU Scheduler

timeslice

set/getaffinity

Kernel Parameters

sysctl

Optimizing the OS - I/O Tuning

Scheduler pluggable via boot parameter: elevator=XXX

cfq: default elevator for enterprise server. Good compromise between latency and throughput.

deadline: more simplistic, caps max latency, similar throughput as cfq, but less fair

as: Anticipates related reads and thus more fair to (synchronous) reads compared to the (asynchronous) writes. Suitable for desktop workloads with single IDE/SATA disks.

noop: No reordering, just merging. If the storage itself does extensive caching and reordering, noop performs very well

Optimizing the OS – I/O Tuning

Read Ahead Cache

/sys/block/sdX/queue/read_ahead_kb Increase from 128 to 512 for fast SCSI disks or RAID storage. This may speed up streaming reads a lot.

Direct I/O

Databases tend to use raw disks. /dev/raw devices can be used for this; using O_DIRECT on block devices / partitions yields more performance and should be preferred.

Optimizing the OS – I/O Tuning

Async I/O

Improves the performance for databases, so this feature should be turned on in the database.

File Systems

If file systems rather than raw I/O are used for the data stores, journaling file systems one are not recommended, as databases generally include journaling mechanisms. ocfs2 has been designed for a popular database and is recommended for this purpose

Optimizing the OS – VM Tuning

boot-parameter: hugepages=N

Number of hugepages (2MB each on i386) to reserve at boot time for the hugetlbfs.

/proc/sys/vm/swappiness

This decreases the swappiness of the kernel. It will tend to swap less. It will shrink the pagecache more, before falling back into swap. So increasing the mapped ratio will result in less cache and less swap. On a low memory machine reducing the cache, and the swap can decrease performance.

Optimizing the OS – VM Tuning

/proc/sys/vm/lower_zone_protection

Instructs the kernel to avoid allocating memory from lower zones, if memory from higher zones can be used instead. This value defaults to 0, but should be set to large values (1024) for systems with >= 8GB; it makes sure to keep enough memory free below 4GB to make the kernel survive in critical situations on i386.

If your I/O hardware is unable to support 64bit addressing modes, you'll be less likely able to do zero-copy I/O though and end up with a higher likelihood of bounce buffering, reducing the I/O performance.

Optimizing the OS – VM Tuning

/proc/sys/vm/disable_cap_mlock

Memory can only be locked by root (CAP_IPC_LOCK) normally; this is to avoid DoS attacks by normal users. Some databases need this feature, and do not have other reasons to run as root; this sysctl avoids the need to run as root, as it allows all users to pin down memory into RAM.

/proc/sys/kernel/shm_use_hugepages

SysV IPC shared mem can be made using hugetlb pages instead of normal ones without the process using SHM_HUGETLB.

Optimizing the OS – VM Tuning

bdflush parameters: /proc/sys/vm/XXX

dirty_expire_centisecs: Time after which dirty pages are scheduled for write-out. 30s by default, good for DB workloads,where rewrites within the 30s are not uncommon. Can be lowered to start write-out earlier.

dirty_ratio: If this percentage of mem is dirty, write-out starts

dirty_background_ratio: The percentage of dirty pages, under which background write-out stops; it should be a bit lower than dirty_ratio.

Optimizing the OS – VM Tuning

bdflush parameters: /proc/sys/vm/XXX

dirty_writeout_centisecs: The frequency with whicH background writeout of dirty pages happens, if more than dirty_ratio percent of the pages is dirty; this value can be made smaller to trigger writeout more aggressively especially if the storage is fast.

The ratios are rather high, which is good for databases; there you often change pages and again somewhat later; if you haven't written the data yet, you saved a write. For other workloads, you may want to decrease the ratios and put them closer together. In general, you may want to trigger write-outs more often to keep your disks busy and your memory clean.

Optimizing the OS – CPU Scheduler

/proc/sys/kernel/XXX

- min-timeslice and max-timeslice:

Min and Max scheduling timeslices of the CPU scheduler in ηs.

By default, they are set to 10 and 300ms, resulting in processes with default priority to run for about 150ms if they're not preempted. Processes with higher (dynamic) priority do preempt lower prio processes if woken up. The dynamic priority is adjusted to help interactive processes.

Optimizing the OS – Kernel Parameters

Read current parameters: 'sysctl -a'

Set parameters at runtime:

'echo "1" > /proc/sys/vm/disable_cap_mlock or:

sysctl -w vm.disable_cap_mlock="1"

Permanent:

edit /etc/sysctl.conf add the following line:

vm.disable_cap_mlock = 1

Optimizing the OS – Kernel Parameters

Enable initialisation at boot time:

insserv /etc/init.d/boot.sysctl

or:

chkconfig boot.sysctl on

Migrations

Migrating from Microsoft DBs to MySQL

Planning

Data Types – i.e. VARCHAR to TEXT

Data Modification – YYYY-MM-DD vs. MM-DD-YYYY

Built-In Functions – ISNULL vs. IFNULL

Cursors - ***will add stuff here after reading****

User Defined Functions – stored proc. vs. C code.

Migrating from MS SQL to MySQL

Planning

Stored Procedures – New to MySQL 5

Maintenance Planning – Backups etc.

Migrating from MS SQL to MySQL

Tools

MSSQL2MYSQL – VB script for conversions

Microsoft DTS – included in MS SQL

SQLyog – Commercial tool for MySQL management

Access Export – included in Access

Text Import/Export – hard to do, but works!

Migrating from Oracle to PostgreSQL

Planning

Grammar Differences

sysdate, decode, sequence, decode

Functional Differences

Outer Joins

No CLOBS/BLOBS in PostgreSQL

PostgreSQL's PL/pgSQL and Oracle's PL/SQL differ

Migrating from Oracle to PostgreSQL

Tools

Ora2Pg

Database schema export (tables, views, sequences, indexes), with unique, primary and foreign key, check constraint.

Grants/privileges export by user and group.

Table selection (by name and max table) export.

Migrating from Oracle to PostgreSQL

Tools

Ora2Pg

Export Oracle schema to PostgreSQL 7.3+ schema.

Predefined functions/triggers/procedures/packages export.

Data export.

Migrating from Oracle to PostgreSQL

Tools

orapgsqlviews

create data dictionary views and tables that mimic Oracle's as close as possible, allowing the vast set of Oracle tools and scripts to be used with PostgreSQL

create documented views and data diagrams

TUT 303 - Brainshare 2005 SLC

Deploying and Mirgrating Enterprise Databases on SLES9

2003-12-08T12:43:39