Kellyn PotVinGorman Technical Intelligence Manager Delphix Essential Linux

Kellyn Pot’Vin-Gorman Technical Intelligence Manager, Delphix Essential Linux for the SQL Server DBA

Kellyn Pot’Vin-Gorman Deck of ACEs 2018 Idera ACE and Oracle ACE Alumnus and member of Oak Table Network, a group of Oracle scientists. Two Decades of Data Management http: //dbakevlar. com @DBAKevlar https: //linkedin/in/kel lynpotvin Managing Oracle, SQL Server, My. SQL, Sybase and big data. Current Technical Intelligence Manager for the Office of CMO for Delphix. Community Board President of Rocky Mountain Oracle User Group and for Denver SQL Server User Group and presenter at 45 technical events annually.

AWESOME!!

So SQL Server Has Come to the World of Linux There’s a lot to learn about Linux… Learning how to: • Navigate • Manage • Gather information • Monitor

But You Need to Learn to Walk Before You Run. . 5

History Lesson • • • 1991 project by Linus Torvalds from Finland to create free operating system kernel. Based on C programming Almost was named Freax Now all part of the GNU Project Debian, Redhat, Oracle Linux, Ubuntu, Suse are all flavors of Linux. Microsoft introduces SQL Server on Linux in version 2017 beta 6

Accessing a Linux Host • Most Common Tools • Putty, (free download) • Terminal, (MAC) • Docker or VM image with terminal via Linux console. • SSH, (Secure Shell) ssh OSUsername@<hostname or IP address> 7

User, Groups and Penguins, Oh My! • • • Don’t do everything as ROOT, (aka super user) Perform administrator tasks by using SUDO, (switch user domain owner) Log in as with your own user ID and su, (switch user) to the application owner. There is a UID, (User ID) for every user name. Each user is assigned a group(s) for role hierarchy A home directory will contain the following: • Profile, (RC) with environment variables, alias’ and other necessary configurations for the user environment. 8

RC and Profiles • RC file stands for Run Commands • Profile is similar, but set up as a secondary set of profile settings, including: • • • Shell to be used Alias’ Environment variables Path information File name begins with a “. ” View with command ls -la . bashrc. bash_rc. profile_xx 9

Example of a profile #/! /usr/bin/sh EDITOR=nano # EDITOR=vi sudo visudo export PATH = $PATH: . # export ORACLE_HOME=/u 01/app/oracle/dbhome_1/db 12 c export SQL_HOME=/u 01/app/mssql/140 export LANG=en. UTF-8 export JAVA_HOME=/usr/bin/java # alias info alias reload=’source ~/. bash_profile’ alias pre-‘open -a Preview’ alias cd. . =’cd. . ’ 10

Creating Users • Useradd has different results than adduser, but both work. • Useradd will require you to: • Manually update the password with passwd <username> • Manually assign a group with usermod after creating a manual group with groupadd command • If you wish to add any pertinent information about the user, that must be added manually. A user should be created for every user logging in. Don’t share logins. Avoid non-audited, such as direct ROOT access. Grant SUDO with great care. Grant SU to only those that require it. 11

SU and SUDO • To switch to from existing user to become jsmith: $ su – jsmith • To execute a command as ROOT after being granted SUDO privileges $ sudo setsys. sh –y 12

Understanding File Permissions Where this can be confusing for some, is that we visibly identify the grants by their values, either as separate values or as totals. • Read, (r)= 4 • Write, (w)= 2 • Execute, (x)=1 • owner has 4+2+1=7 • group has 4+2+1=7 • other has 4+2+1=7 13

Changing Permissions • CHMOD= change modify $ chmod <filename> XXX $ chmod test. txt 764 • Owner- read, write, execute • Group- read, write • Other- read You must have permissions to update the permissions or must SU/SUDO to perform them: $ sudo chmod test. txt 764 14

Changing File Owner • CHOWN = change owner $ chown <newuser>: <group> <filename> $ chown jsmith: dba test. sh • You must have privileges or own the file to begin with to change the ownership. 15

Basics What Command Common Change Diretory cd cd, cd. . , cd /directory path List or Directory information ls ls, ls –lf, ls –la Locate program find <program name> find java, find sql Locate which installation used which <program name> which mssql Current Directory pwd Create Directory mkdir scripts mkdir /u 01/apps/scripts Create File touch <filename>, vim <filename> touch test. sh Edit File vim <filename>, vi <filename> vim touch. sh Remove rm <filename> rm touch. sh ** rmdir 16

Processes • Ps command $ ps –ef To Kill a process, (BECAREFUL!) $ kill <PID> 17

Editors • VI/VIM makes you cool- old school • Nano • e. Macs • Create a file easily by issuing the touch command $ touch <filename> 18

VI(M) Command line editor $ vi <filename> • • Go down a line: j Go up a line: k Go to the right: l Go to the left: h Insert: i Append: a Append to the end of a line: a Add a line and insert below your cursor: o Add a line and insert ABOVE your cursor: <shft> o Undo: u Quit without saving: : q Quit without saving to a read only file: : q! Write to a file: : w Write to a read only file, (override): : w! Write to a file and quit: : wq Write to a read only file, (override) and quit: : wq! 19

Redirecting Data Output to a File • $ <command> > <filename> $ ls > filelist. txt $ ls >> filelist. txt • If run again with the “>>”, the results will be appended to the first file’s contents. 20

Running an Update on Linux • Updates and Installations are done most often via Yum, APT-GET or Zypper • Using APT-GET • Update from repository, (stored in $ apt-get update • Install updates, (may require SUDO) $ apt-get install 21

Adding Utilities is Done via Packages • Common Installers for Linux • YUM • APT-GET • ZYPPER Installing a package with APT-GET $ sudo apt-get install –y <pkgname> Viewing package information: $apt-cache pkgname <pkgname> Display package information: $apt-cache show <pkgname> 22

Cheat Sheets for Common Installers • Yum: https: //access. redhat. com/articles/yum-cheat-sheet • Apt-Get https: //blog. packagecloud. io/eng/2015/03/30/apt-cheat-sheet/ • Zypper: https: //en. opensuse. org/images/1/17/Zypper-cheat-sheet-1. pdf 23

Mission Impossible • Initial Powershell for Linux: https: //github. com/Power. Shell • BASH, (Born again shell) • KSHELL, (Korn Shell) • CSHELL, (based off C) • Shell scripting is very similar in each of these languages and very different from Powershell. 24

Linux system-level diagnostics • • • CPU and Memory Process Network Traces Other useful Linux diagnostic utilities • Support recommended diagnostic tools • A great source of living technical information

Utilities to View Resource Usage • • • SAR SADC/SADF MPSTAT VMSTAT TOP 26

Common Arguments and Values • Some of the utilities aren’t standard and may need to be installed. • Yum and apt-get are your friend • You may require sudo, (super user) privileges to install • <command> -x(xx) • Arguments are cap sensitive, (as is everything in Unix world. ) • <command> -x 5 5 • First how many second sleep, Second value is how many results to return • <command> -h • Provides help information about the utility or command 27

What is SAR? • System Activity Report • Provides information about • • CPU Memory and virtual memory I/O Network • Provided by the SYSSTAT package • What if it’s missing? • Install it as root: $yum -y install sysstat $apt-get install sysstat 28

sar Utility • CPU utilization: sar -u • %usr, %sys, %idle • %nice • Intervals of CPU: sar 1 3 • 3 times, every 1 second • CPU run queue report: sar -q • Runq-sz • plist-sz and load averages • Context switching activity: sar -w • cswch/s • Virtual memory swapping: sar -W • swpin/s, swpot/s • Virtual memory paging: sar -B • pgpgin/s, pgpgot/s

sar Utility $ sar -u 5 5 Linux 2. 6. 32 -573. 22. 1. el 6. x 86_64 (linuxsource. delphix. local) 11/28/2017 _x 86_64_ (2 CPU) 06: 15: 31 PM CPU %user %nice %system %iowait %steal %idle 06: 15: 36 PM all 0. 20 0. 00 0. 20 48. 58 0. 00 51. 01 06: 15: 41 PM all 0. 30 0. 00 0. 20 47. 98 0. 10 51. 41 06: 15: 46 PM all 0. 30 0. 00 0. 20 46. 92 0. 00 52. 58 06: 15: 51 PM all 0. 20 0. 00 0. 20 47. 77 0. 00 51. 82 06: 15: 56 PM all 0. 20 0. 00 0. 20 47. 78 0. 10 51. 72 Average: all 0. 24 0. 00 0. 20 47. 81 0. 04 51. 71

sar Utility $ sar -q 5 5 Linux 2. 6. 32 -573. 22. 1. el 6. x 86_64 (linuxsource. delphix. local) CPU) 06: 16: 45 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 06: 16: 50 PM 0 190 1. 37 1. 28 1. 30 06: 16: 55 PM 0 190 1. 34 1. 28 1. 29 06: 17: 00 PM 0 190 1. 31 1. 27 1. 29 06: 17: 05 PM 0 190 1. 36 1. 29 1. 30 06: 17: 10 PM 0 190 1. 34 1. 28 1. 30 Average: 0 190 1. 34 1. 28 1. 30 11/28/2017 _x 86_64_ (2

What is SADC/SADF? • sadc: back-end system activity data collector utility behind sar • Collects specified system data at configured intervals • Saves data to specified binary files • sadf: system activity data formatter • • report on data collected by sadc/sar output to be consumed by other programs like awk, MS-Excel, etc Options for translating and displaying dates, numbers, text Output to plain text or XML

mpstat utility Processor Level Statisitcs $ mpstat –P ALL Linux 2. 6. 32 -573. 22. 1. el 6. x 86_64 (linuxsource. delphix. local) 06: 18: 10 PM CPU %usr %nice %sys %iowait 8. 01 _x 86_64_ %irq %soft %steal %guest %idle 06: 18: 10 PM all 1. 42 0. 00 0. 19 06: 18: 10 PM 0 1. 38 0. 00 0. 21 15. 10 0. 00 0. 02 0. 00 83. 29 06: 18: 10 PM 1 1. 45 0. 00 0. 17 0. 00 0. 02 0. 00 97. 42 0. 94 0. 00 11/28/2017 0. 00 0. 02 0. 00 90. 36 (2 CPU)

vmstat Utility Virtual Memory Statistics vmstat 5 5 procs ------memory----- ---swap-- -----io---- --system-- -----cpu----r b swpd free buff cache si so bi bo in cs us sy id wa st 0 2 28612 131668 255192 6766272 0 0 0 148 0 1 28612 234736 255204 6766424 0 0 24 1480 1485 2737 2 1 48 49 0 0 2 28612 234752 255208 6766536 0 0 0 1511 1365 2632 0 0 50 50 0 1 1 28612 234752 255208 6766664 0 0 0 1399 1353 2626 0 0 50 50 0 0 2 28612 218164 255216 6766784 0 0 18 1310 1337 2591 0 0 49 51 0 1 3 1 0 90 8 0

top Utility Tasks: 165 total, 1 running, 164 sleeping, 0 stopped, 0 zombie Cpu(s): 0. 3%us, 0. 2%sy, 0. 0%ni, 53. 5%id, 46. 0%wa, 0. 0%hi, 0. 0%st Mem: 8057984 k total, 7827928 k used, 230056 k free, 255324 k buffers Swap: 311292 k total, PID USER 28612 k used, 282680 k free, 6771660 k cached PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 9888 delphix 20 0 636 m 43 m 16 m S 0. 7 0. 5 51: 40. 68 mssql 9892 delphix 20 0 636 m 43 m 16 m D 0. 7 0. 5 51: 18. 86 mssql 9886 delphix 20 0 652 m 43 m 16 m S 0. 3 0. 6 51: 19. 84 mssql 1 root 20 0 19232 976 820 S 0. 0 0: 26. 11 delcli 2 root 20 0 0 S 0. 0 0: 00. 00 kthreadd 3 root RT 0 0 S 0. 0 0: 09. 92 migration/0

What is UPTIME? • uptime displays current time and up-time • Also displays the average number of processes in run-queue over the past 1 minute, 5 minutes, and 15 minutes (a. k. a. load average) 36

uptime and w Utilities $ uptime 18: 20: 41 up 41 days, 17 min, 1 user, load average: 1. 17, 1. 28, 1. 30 • w displays same info plus info about logged-in UNIX users $w 18: 21: 05 up 41 days, 17 min, 1 user, load average: 1. 25, 1. 29, 1. 30 USER TTY delphix pts/0 FROM LOGIN@ IDLE JCPU PCPU WHAT c-67 -164 -182 -219 18: 05 0. 00 s 0. 02 s sshd: delphix [priv]

Process Diagnostics • ps • Process status • pmap • Process mapping • http: //dbakevlar. com/2017/12/sql-server-2017 -linux-processes/ • dstat • Disk, cpu and network monitoring • nmon • Color coded display of cpu, disk and memory statistics

ps Utility • Sys. V version with many versions and variations: $ ps -eaf % ps -aux # ps –eo opt[, opt…] • Provides info about individual processes • Status, PID, PPID, user, command text and parameters • Cumulative and recent CPU usage • Memory (virtual, resident)

ps Utility • An easy home-grown “top” command $ ps –eaf | sort –n | tail root 689 2 0 Oct 18 ? root 723 2 0 Oct 18 ? 00: 01: 34 [kauditd] root 781 2 0 Oct 18 ? 00: 18 [flush-253: 0] root 8 2 0 Oct 18 ? 00: 00 [stopper/1] root 9 2 0 Oct 18 ? 00: 06 [ksoftirqd/1] root 99 2 0 Oct 18 ? 00: 00 [kdmremove] rpc 1101 2 0 Oct 18 ? 1 0 Oct 18 ? 00: 00 [ext 4 -dio-unwrit] 00: 12 [migration/1] 00: 01 rpcbind

ps Utility • Another home-grown “top” command • Also displays memory consumption in Kbytes $ ps -eo user, pid, pcpu, vsz, rss, comm | sort -n | tail root 689 0. 0 root 723 0. 0 0 0 kauditd root 781 0. 0 0 0 flush-253: 0 root 8 0. 0 0 0 stopper/1 root 9 0. 0 0 0 ksoftirqd/1 root 99 0. 0 0 0 kdmremove rpc 0 0 0 ext 4 -dio-unwrit 0 migration/1 1101 0. 0 18980 588 rpcbind

ps Utility • Displaying environment variable values within a process $ ps -eaf | grep jar delphix 11313 1 0 Oct 18 ? 00: 23: 10 /usr/java/jdk 1. 7. 0_51/bin/java -Djava. util. logging. config. file=/u 02/app/apachetomcat-7. 0. 42/conf/logging. properties -Djava. util. logging. manager=org. apache. juli. Class. Loader. Log. Manager Djava. endorsed. dirs=/u 02/app/apache-tomcat-7. 0. 42/endorsed -classpath /u 02/app/apache-tomcat 7. 0. 42/bin/bootstrap. jar: /u 02/app/apache-tomcat-7. 0. 42/bin/tomcat-juli. jar -Dcatalina. base=/u 02/app/apache-tomcat-7. 0. 42 Dcatalina. home=/u 02/app/apache-tomcat-7. 0. 42 -Djava. io. tmpdir=/u 02/app/apache-tomcat-7. 0. 42/temp org. apache. catalina. startup. Bootstrap start $ ps eww 11313 PID TTY STAT TIME COMMAND 11313 ? Sl 23: 10 /usr/java/jdk 1. 7. 0_51/bin/java -Djava. util. logging. config. file=/u 02/app/apache-tomcat 7. 0. 42/conf/logging. properties -Djava. util. logging. manager=org. apache. juli. Class. Loader. Log. Manager Djava. endorsed. dirs=/u 02/app/apache-tomcat-7. 0. 42/endorsed -classpath /u 02/app/apache-tomcat 7. 0. 42/bin/bootstrap. jar: /u 02/app/apache-tomcat-7. 0. 42/bin/tomcat-juli. jar -Dcatalina. base=/u 02/app/apache-tomcat-7. 0. 42 Dcatalina. home=/u 02/app/apache-tomcat-7. 0. 42 -Djava. io. tmpdir=/u 02/app/apache-tomcat-7. 0. 42/temp org. apache. catalina. startup. Bootstrap start SHELL=/bin/bash TERM=xterm USER=delphix PATH=/sbin: /usr/sbin: /usr/bin PWD=/ HOME=/home/delphix SHLVL=3 LOGNAME=delphix _=/usr/java/jdk 1. 7. 0_51/bin/java

What is FINDMNT? • A [somewhat] directory tree view of Linux • To Install: $ yum install findmnt –y $ apt-get install findmnt 43

findmnt Utility 44

findmnt Utility • findmnt -m 45

What is NCDU? • Quick utility to gather info on space usage in a directory • To install: $ yum install ncdu –y $ apt-get install ncdu Simply execute ncdu command in directory that you wish to gather detailed file/directory sizes on 46

ncdu Utility 47

Why Screen Linux Screen allows you to: • Multiple shell windows from a single secure shell, (SSH) session. • Keep a shell active even through network disruptions. • Disconnect and re-connect to a shell sessions from different locations/workstations. • Run a long running process without maintaining an active shell session. 48

Do You Have Screen? # which screen /usr/bin/screen If not, install it: # sudo apt-get install screen Issue a screen: # screen List Active: # screen -ls If disconnected # screen –r <screen#> 49

50

Linux for the DBA Summary Learn to walk before you run Command line is essential Utilities are your friends A few will get you far, choose which ones provide you with what you need. Build a knowledge of command line editing tools, such as VIM or Nano and Shell scripting to enhance utilities for full coverage. • Tough at first, but will become second nature- I promise! • • • 51

Thank you! 52