E146 Adventures in Linux: Live on ASE and Rep Server

1
E146Adventures in Linux Live on ASE and Rep
Server

• Carl Moritz
• Database Administrator
• Iroquois Gas Transmission System
• carl_moritz_at_iroquois.com

2
Overview

• Starting Point
• Charting the Course
• Installation Adventure
• Compatibility Adventure
• Configuration Adventure
• Migration Adventure
• Performance Adventure
• Epilogue

3
Starting Point Company Background

• Relatively small natural gas pipeline operating
  company
• 130 employees
• 400 mile mainline pipe from Canada to NYC
• Federally regulated
• Web based order entry, status, and reporting
• High availability 24 x 7

4
Starting Point Current System

• User Interface
• Powerbuilder/stored procedures/triggers
• Browser enabled via Citrix
• Database Servers
• Digital AlphaServer 4000 533 mhz single
  CPU/OpenVMS
• 1 GB RAM
• 30 GB mirrored
• ASE 11.0.3

5
Starting Point Current System

• Replication Server
• Digital Alphaserver 1000A 333mhz Digital Unix
• 200 MB RAM
• 8 GB
• ASE 11.9.2 (RSSD)
• Rep Server 11.5 / LTM
• Warm Standby

6
Starting Point Server Host Location

• Shelton, CT
• alpha/VMS ASE
• Active OLTP
• alpha/Digital Unix
• Rep Server
• Oxford, CT
• alpha/VMS ASE
• Standby OLTP
• Active Reporting

7
Starting Point Production Databases

• Gas Accounting aka vega
• 2 GB
• Replicated Shelton to Oxford
• Invoicing aka mgi
• 500 MB
• NOT Replicated - Oxford only

8
Starting Point Current System Diagram
Digital Unix Rep Server
Shelton
Oxford
VMS Active
VMS Standby
LTM
ASE 11.0.3
ASE 11.0.3
Rep Server 11.5
Stable Device
vega 2GB
mgi 500MB
vega 2GB
RSSD
ASE 11.9.2
9
Starting Point System Characteristics

• No major performance issues
• Max 50 concurrent connections
• EXTREMELY STABLE
• Production and test ASE run on same Shelton host
• Production and test RepServers run on same
  Shelton host
• Production, test, and development ASE servers run
  on same Oxford host

10
Starting Point Host costs

• Real Costs
• Purchase price 120,000
• Annual support 18,000
• Intangibles
• 3 hosts
• 2 operating systems
• LTMs

11
Charting the Course Desired System

• ASE 12.5
• Rep Server 12.0
• 2 Hosts (1 local, 1 remote)
• 1 Operating system
• Stability
• Reduce costs
• RATs

12
Charting the Course Alternatives

• Windows NT - MS SQL Server
• Windows NT - Sybase ASE
• Sun/Solaris
• Intel/Linux

13
Charting the Course New Hosts

• Dell PowerEdge 2250 Servers (1 local, 1 remote)
• Linux 2.4.7-10SMP, Red Hat 7.2
• 2 GB RAM
• Dual 1.2 ghz P4 processors
• 36 GB mirrored drives, single controller
• Cost 14,000 total
• Annual support 1,500

14
Charting the Course Server Location

• Shelton Host
• ASE Production
• ASE Test
• ASE Development
• Oxford Host
• ASE Production
• RepServer Production
• ASE Test
• RepServer Test

15
Charting the Course New System Diagram
Shelton, CT
Oxford, CT
Linux Standby
16
Charting the Course ASE Linux Limitations

• No large device support. 2GB limit
• No async support. SGI patch?
• No ext3 file type support
• SCSI disks limited to 15 partitions
• Less boot, ext partition, os fs, other fs
• Requires OpenClient 11.x or later
• Windows 2000 or XP requires OpenClient 12.0

17
Charting the Course RepServer Linux Limitations

• Requires ASE 11.9.2 common libraries
• No RepServer 12.1
• No support for raw devices (stable queues)
• No support for some ASE 12.5 features
• Column count and length
• Row length
• Parameter count and length
• Parameter list length
• SSL, LDAP, and Unicode

18
Compatibility Adventure ASE 11.0.3 - 12.5

• FUNCTION keyword
• Table eliminated
• GROUP BY sort order
• ORDER BY added to all SQL

19
Installation Adventure The Kernel Controversy

• Early ASE 12.5 Install Guide
• 2.2.14 - compiled and certified
• 2.2.18 - required for raw devices
• ASE 12.5.0.1 Install Guide/Rel Note
• 2.2.14-5.0 - compiled and certified
• 2.2.19-6 - required for raw devices

20
Installation Adventure The Kernel Controversy

• ASE 12.5.0.1 EBF 9989 Cover Letter
• Bug 256883 new features RH 7.2 (aka 2.4.x)
• Bug 256883 not actually fixed
• News Groups
• RH 7.2 (2.4.x) unofficially supported
• ASE engineering
• 2.2.18 - min for raw devices

21
Installation Adventure The Kernel Controversy

• Sybase Tech Support
• 2.2.x is supported
• 2.4.x is NOT supported
• RepServer 12.0 Install Guide/Release Note
• 2.2.5 - compiled and certified

22
Installation Adventure The RepServer Controversy

• Install Guide/Release Notes
• Requires 11.9.2 ASE
• No raw device support for stable queues
• RepServer Engineering
• Will run ok w/ASE 12.5
• ASE 12.5 features not supported
• 2.2.18 is ok too
• Raw device stable queues not necessary

23
Installation Adventure The RepServer Controversy

• File System stable queue devices are ok. The
  risks are
• Crash when a stable queue I/O spans segments.
• Write is followed by network I/O to ASE to record
  a transaction.
• Typically enough time to flush the stable queues
  file buffer.
• Sybase testing could not create this scenario.
• Not detectable.

24
Installation Adventure The RepServer Controversy

• Crash after transactions are reported to be
  successfully queued advancing the secondary
  truncation point.
• Write is followed by network I/O to ASE to record
  a transaction.
• Typically enough time to flush the stable queues
  file buffer.
• Sybase testing could not create this scenario.
• Detectable.

25
Installation Adventure Speed Bumps

• Red Hat Package Manager
• Installs into /opt (Sybase recommended)
• End up with
• /opt/sybase-11.9.2
• /opt/sybase-12.5
• ASE 12.5 installed first so RPM complained when
  installing 11.9.2 common and client components.
• Use --force qualifier to overcome this
• Edit rs_install_systables.sql
• put quotes around function

26
Installation Adventure Upgrade Pitfalls

• Upgrading ASE to 12.5.0.1 (aka EBF 9989)
• Original sequence ASE 12.5, EBF9901, ASE 11.9.2,
  Rep Server 12.0
• When running rpm to install 12.5.0.1 on Rep
  Server host
• failed dependency errors
• i.e. sybase-common - 11.9.2 is needed by
  sybase-repsrv-12.0-1
• Using --force doesnt work

27
Installation Adventure Upgrade Alternative

• Upgrading ASE to 12.5.0.1 Alternative Procedure
• Make a backup of /opt/sybase-11.9.2
• Uninstall the Rep Server and ASE 11.9.2 packages
• i.e. rpm -e sybase-repsrv-12.0-1
• Apply the ASE 12.5.0.1 EBF9989 rpm packages
• Re-install Rep Server 12.0 and ASE 11.9.2 rpm
  packages
• Restore the backup of /opt/sybase-11.9.2

28
Installation Adventure Directory Structures

• The /sybase-12.5 structure contains directory
  links for all components
• ASE -gt ASE-12_5
• OCS -gt OCS-12_5
• SYSAM -gt SYSAM-1_0
• Breaks the sybase start/stop shell script
• No links in /sybase-11.9.2 structure
• No start/stop script supplied for Rep Server

29
Configuration Adventure Building ASE Servers

• srvbuild produced several X windows font and
  screen management errors.
• Difficulty creating a raw master device.
• Used srvbuildres instead.
• Did not install SYSAM.
• Eval did not work.
• All devices start up using .
• standard unix i/o
• with dsync on
• Trace flag 1625

30
Configuration Adventure Raw Devices

• ASE 12.5 Install Guide had instructions Appendix
  B
• ASE 12.5.0.1 No Appendix B
• Use fdisk to create the partition (2GB max)
• Set up a binding to the partition in
  /etc/sysconfig/rawdevices
• /dev/raw/raw1 /dev/sda1
• Run rawdevices script to execute the bindings
• cd /etc/rc.d/init.d
• rawdevices start

31
Configuration Adventure Raw Devices

• Make sure they bind on startup
• /sbin/chkconfig rawdevices on
• Permissions
• Put sybase in disk group so it can create
  database devices on raw devices
• Assign read, write, and ownership of /dev/rawctl
  to sybase
• Assign read, write, and ownership of any bound
  /dev/raw to sybase

32
Configuration Adventure Raw Device Alternative

• Create a sybase raw device directory
• mkdir /dev/sybraw
• Assign ownership and full access to sybase
• Make a node using a meaningful name
• mknod /dev/sybraw/prod_db_dev c 162 203
• Assign ownership and full access to sybase
• Bind this file to the raw partition device
• raw /dev/sybraw/prod_db_dev /dev/sdb3
• Create the database device
• disk init nameprod_db_dev', physname'/dev/sybra
  w/prod_db_dev,

33
Configuration Adventure ASE Memory Configuration

• Host memory 2GB
• Max Memory 1GB
• 2 online engines
• Procedure cache 50MB
• 500MB default data cache
• 300MB 2k pool
• 100MB 4k pool
• 100MB 16k pool

34
Configuration Adventure Building Rep Servers

• Used resource files to setup the Rep Server and
  active and standby connections
• Copy the samples and edit them
• rs_init would not create a stable device gt 2MB
• Create a 2MB temp stable device
• Add a 2GB device later
• Drop the 2MB device
• rs_init could not modify the interfaces file
• Could not run dscp because it was in the other
  ASE path
• Used vi to edit the interfaces file
• Linked it to the ASE 12.5 interface file

35
Configuration Adventure Warm Standby
Configuration

• Some new features of 12.5 are not supported in RS
  12.0
• Must adhere to limits of ASE 12.0, i.e.
• Column count and length
• Row length
• Parameter count and length
• Parameter list length
• Configure the rep agent
• "skip unsupported features", true
• "data limits filter mode", 'stop'

36
Migration Adventure Objectives

• Load test the new systems.
• Exercise replication on linux.
• Minimize downtime during cut over.
• Phase in the cut over.
• Run reports on the new standby
• Run invoicing on the new standby

37
Migration Adventure Outline

• Build new ASE and Rep servers on new hosts.
• Create new databases and objects from scripts.
• Replicate from existing production to the new
  active server.
• Move production data to new active.
• Set up the new warm standby.

38
Migration Adventure Replicate Existing
Production Servers

• Define Replication Definitions that use the
  production logical connection as a data source.
• Used script to generate them.
• replicate all columns
• Made manual modifications to the tables with text
  data types.
• always_replicate ( NOTICE_TEXT)
• Make sure primary keys exist on current
  production database.

39
Migration Adventure Replicate Existing
Production Servers

• Create subscriptions the use the new active as
  the destination.
• Did not use new logical connection.
• Requires adding the new Rep Server into the old
  Rep Servers domain.
• Both are ID Servers.
• The old Rep Server would later be retired from
  the domain.
• Turn on autocorrection.

40
Migration Adventure Move data to new active ASE
Server

• Dropped indexes on large tables
• Tuned new active server for bcp
• max network packet size 8192
• additional network memory 75776
• native mode -n
• bcp in batch size -b20000, packet size -A8192
• Ran 5 bcps at once
• Elapsed bcp out/in time 25 min for 2GB

41
Migration Adventure Move data to new active ASE
Server

• Created new indexes for large table with sorted
• Ran update statistics and sp_recompile
• dbcc checkalloc, checkdb, checkcatalog
• dump database
• Set up the new warm standby
• Create the logical connection, active, and
  standby databases
• Materialize new standby with dump of new active

42
Migration Adventure Move data to new active ASE
Server

• Test replication
• Insert test row in active production database
• See if it gets to new standby
• If so turn off autocorrection
• Validated replication for 3 weeks
• Compared row counts
• Compared business reports
• Survived many reboots and upgrades

43
Migration Adventure Cut over

• All users forced to exit and locked out at
  quiet time
• Replication was quiesced
• DSI connection from production to new active was
  suspended
• Network aliases redefined as the new active and
  standby
• Users allowed back in

44
Performance Adventure Misc Performance

• Fast bcp in 1,000,000 rows, no index, 280 byte
  row
• 65 seconds vs 40 min
• create clustered index
• 3 min vs 74 min
• Create 7 more non-clustered indexes
• 9 min vs 46 min

45
Performance Adventure Misc Performance

• TSQL loop 1,000,000 times
• 7 sec vs 30 sec
• Select all rows into temp table
• 2 min vs 20 min
• Select agg/group by account/3 way join
• 1.6 min vs 16 min

46
Performance Adventure System Admin Performance

• checkalloc 2GB db
• 5 min vs 81 min
• checkdb 2GB db
• 54 min vs 272 min
• dump 2GB database with compression
• 250 MB vs 1.8 GB
• 2 min vs 50 min

47
Performance Adventure Application Performance

• OBA Report
• 2 min vs 24 min
• Invoice Run
• 6 min vs 46 min

48
Epilogue Aftershocks

• The hosts, ASE servers, and RepServers are
  sufficiently stable
• Linux 2.2.16 caused some instability problems
• System hang during intensive I/O
• Error 694, 3935 write errors
• No data loss or corruption
• Attributable to bad linux SMP release
• Eliminated in 2.4.7-10SMP
• All ksh scripts ported well from Digital Unix
• Only 2 minor bugs

49
Epilogue Linux Update

• Red Hat 7.2 compiled ASE 12.5 due in Summer 2002
• Large device, async I/O support
• Rep Server 12.5 due in Summer
• New Pricing
• Enterprise vs Workplace discontinued
• Linux pricing is lower than other Unix
• Rep Server to be CPU based

50
Questions