1 / 79

DB-3: Tips for Database Administrators

DB-3: Tips for Database Administrators. Gus Bj örklund. Wizard, Progress Software Corporation. Ask questions as we go if I am not clear about something. Warning: there is a mistake in these slides. Topics. Tip Nr. 0x00 … Tip Nr. 0x16. Tip Number 0x00. If it is not broken, don’t fix it !.

les
Download Presentation

DB-3: Tips for Database Administrators

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB-3: Tips for Database Administrators Gus Björklund Wizard, Progress Software Corporation

  2. Ask questions as we goif I am not clear about something.Warning: there is a mistake in these slides. DB-3: Tips for Database Administrators

  3. Topics • Tip Nr. 0x00 • … • Tip Nr. 0x16 DB-3: Tips for Database Administrators

  4. Tip Number 0x00 If it is not broken, don’t fix it ! DB-3: Tips for Database Administrators

  5. Tip Number 0x01 If you do fix something, test your fix ! DB-3: Tips for Database Administrators

  6. Tip Number 0x02 Read the man pages DB-3: Tips for Database Administrators

  7. Read the man pages • man man • man [ -s section ] name • Read the man pages! DB-3: Tips for Database Administrators

  8. Tip Number 0x03 SSH is your friend!Like Linux, more great stuff fromFinland DB-3: Tips for Database Administrators

  9. SSH • SSH replaces telnet, rlogin, rcp, rsh, etc. • All connections are encrypted • Use for remote access over Internet DB-3: Tips for Database Administrators

  10. SSH • Don’t need username, password login • Can also use certificates for authentication • Can send output of • local commands to remote host • remote commands to local host • Can do secure file transfer • scp and sftp • Can tunnel other protocols (e.g. X windows) DB-3: Tips for Database Administrators

  11. Simple remote command execution gus@dirac:gus $ ssh bespin ls gus@bespin's password: 101arkt 90ajdk.txt GSI Mail News a.out a7 adb.man gus@dirac:gus $ Nothing much to see here DB-3: Tips for Database Administrators

  12. Remote command output to local host gus@dirac:gus $ ssh bespin "ls; echo DONE” >ls.dat gus@bespin's password: gus@dirac:gus $ cat ls.dat 101arkt 90ajdk.txt GSI Mail News a.out a7 adb.man DONE gus@dirac:gus $ DB-3: Tips for Database Administrators

  13. Local output piped to remote host gus@dirac:gus $ cd 4gl; tar -cf - *.p | ssh bespin "cd foo; tar -xf -" gus@bespin's password: gus@dirac:4gl $ DB-3: Tips for Database Administrators

  14. Remote backup piped to local file gus@dirac:4gl $ ssh bespin "cd pdr; tar -cvf - ." >backup.tar gus@bespin's password: a ./ 0K a ./Makefile 1K a ./pdrcache.c 131K a ./pdrproc.c 23K a ./pdrtrig.c 175K a ./dsmcon.c 94K a ./dsmrec.c 17K a ./dsmseq.c 15K gus@dirac:4gl $ DB-3: Tips for Database Administrators

  15. Turning off sshd password authentication in the file: /etc/ssh/sshd_config, do this: # Protocol 1,2 Protocol 2 . . . # Authentication: PasswordAuthentication no then save changes. restart daemon: # service sshd restart DB-3: Tips for Database Administrators

  16. Enabling SSH certificate authentication gus@dirac:gus $ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/Users/gus/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /Users/gus/.ssh/id_rsa. Your public key has been saved in /Users/gus/.ssh/id_rsa.pub. The key fingerprint is: 3f:a9:2c:a6:74:5e:36:59:39:7f:ed:3f:5d:e3:a6:ee gus@dirac.bedford.progress.com gus@dirac:gus $ gus@dirac:gus $ ssh-copy-id -i id_rsa gus@bespin gus@dirac:gus $ DB-3: Tips for Database Administrators

  17. Enabling SSH certificate authentication 2 gus@dirac:gus $ cat /Users/gus/.ssh/id_rsa.pub. ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEApwswruyeETRbQ/T7ZHdNxtXIC5J41XpnJwWND4HB3WTuXPLx/Qf2S83Y3VPSgGoc+9YqmLOc/hfs/gQry+TQC9jGIPVYtW/E5PPvhh/HxTIKVc0eYMmpKlxFCjfcv0KyDcAwnd7r/wczPqw2TpurzZcXfA0c2upqufJZbnweXZtDrcs7bUwdQXskrqDj1EDBxACuH1/omZa2M/PdfFzyrRtbRMeyrfIWAfoL5RNOFsPYYOxWJxfp11tuSF7OiBUQtcbXAfS8cdHsQkJNNzSZqM6B69LvK28mlHZ8m2b5+cXICA27aUyDZfsSlMLR6rl+Ao3UByL8G+ZQBLUmhMsuRQ== gus@dirac.bedford.progress.com gus@dirac:gus $ DB-3: Tips for Database Administrators

  18. More about SSH www.openssh.com http://www.ietf.org/rfc/rfc4251.txt DB-3: Tips for Database Administrators

  19. Tip Number 0x04 Windows can do SSH too DB-3: Tips for Database Administrators

  20. SSH for Windows Client: use Putty http://www.chiark.greenend.org.uk/~sgtatham/putty/ Server: get Cygwin http://www.cygwin.com DB-3: Tips for Database Administrators

  21. Tip Number 0x05 After-image journalling is your friend! DB-3: Tips for Database Administrators

  22. After Imaging • Do you use after-image journalling? • YOU SHOULD • AI records all database changes • Intended for • disaster recovery • hot standby on another machine DB-3: Tips for Database Administrators

  23. AI Basics • Add AI extents to database • Back up the database • Enable after-imaging • Archive ai extents as they become full • 10.1 has an automatic archiver • OpenEdge DBA guide has details DB-3: Tips for Database Administrators

  24. AI basics 2 • A failure occurs and you lose your database ….. • Fix the failure • replace disk, memory, machine, etc. • Restore the backup copy of your database • Roll forward all ai extents you archived • You do know where you put them, don’t you? • I hope they weren’t on the failed disk • When roll forward done, start database • Now you are back to where you were DB-3: Tips for Database Administrators

  25. Tip Number 0x06 Specify scratch space for index rebuild DB-3: Tips for Database Administrators

  26. Index rebuild scratch space Command line looks like this: proutil dbname -C idxbuild table customer \ -SS dbname.srt -TB 31 -TM 32 -B 1000 Scratch space spec file (dbname.srt) looks like this: 300 /user2/db1/first/ 400 /user3/junk/ 0 /user4/last/ note multiple drives in this example DB-3: Tips for Database Administrators

  27. Tip Number 0x07 Tools you should know about DB-3: Tips for Database Administrators

  28. Tools • uptime • find • top • lsof • ifconfig • vmstat • iostat • netstat • bzip2 DB-3: Tips for Database Administrators

  29. Tools: uptime $ uptime 11:26am up 17 day(s), 9:16, 163 users, load average: 3.09, 3.36, 3.76 $ DB-3: Tips for Database Administrators

  30. Tools: top gus@dirac:gus $ top -l 1 Processes: 65 total, 3 running, 62 sleeping... 183 threads 10:51:24 Load Avg: 0.04, 0.06, 0.07 CPU usage: 11.1% user, 44.4% sys, 44.4% idle SharedLibs: num = 170, resident = 37.0M code, 5.30M data, 6.44M LinkEdit MemRegions: num = 6460, resident = 166M + 16.3M private, 164M shared PhysMem: 161M wired, 758M active, 879M inactive, 1.76G used, 248M free VM: 9.47G + 102M 230499(0) pageins, 16746(0) pageouts PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE 606 top 0.0% 0:00.05 1 17 19 276K 684K 748K 26.9M 586 bash 0.0% 0:00.02 1 14 16 224K 1.05M 844K 27.1M 585 login 0.0% 0:00.01 1 16 40 172K 768K 636K 26.9M 583 Terminal 0.0% 0:01.56 5 130 197 2.64M 11.9M 8.93M 364M 563 TextEdit 0.0% 0:00.23 1 67 149 2.05M 8.94M 5.80M 355M 558 TeXShop 0.0% 0:03.18 2 109 238 4.49M 20.2M 25.6M 365M 467 firefox-bi 0.0% 1:31.55 6 96 389 48.1M 29.4M 67.4M 428M DB-3: Tips for Database Administrators

  31. Tools:find • find files modified more than 30 days ago • find . -mtime +30 -print • delete files modified more than 30 days ago • find . -mtime +30 -exec rm {} \; • find files bigger than 16k • find . -size +16384c -print • find . -size +32 -print • delete files bigger than 16k • find . -size +16384c -print | xargs -i rm \;Read the man page! DB-3: Tips for Database Administrators

  32. Tools:lsof _progres 26240 bleicher /usr/lib/librt.so.1 _progres 26240 bleicher /usr/lib/libsocket.so.1 _progres 26240 bleicher /usr/lib/libintl.so.1 _progres 26240 bleicher /usr/lib/ld.so.1 _progres 26240 bleicher /usr/lib/libdl.so.1 . . . _progres 26240 bleicher /tools/solaris/dlc100b/promsgs _progres 26240 bleicher /tools/solaris/dlc100b/bin/_progres ftp://vic.cc.purdue.edu/pub/ DB-3: Tips for Database Administrators

  33. Tools:ifconfig 1-bespin-gus-> /usr/sbin/ifconfig -a lo0: flags=1000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1 inet 127.0.0.1 netmask ff000000 ce0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index 2 inet 172.16.7.168 netmask ffff0000 broadcast 172.16.255.255 DB-3: Tips for Database Administrators

  34. Tools:ifconfig gus@dirac:gus $ ifconfig en0 en0: flags=8863<UP,BROADCAST,SMART,RUNNING,SIMPLEX,MULTICAST> mtu 1500 tunnel inet --> inet6 fe80::217:f2ff:fecc:b686%en0 prefixlen 64 scopeid 0x4 inet 172.16.114.99 netmask 0xffff0000 broadcast 172.16.255.255 ether 00:17:f2:cc:b6:86 media: autoselect (1000baseT <full-duplex,flow-control>) status: active supported media: autoselect 10baseT/UTP <half-duplex> 10baseT/UTP <full-duplex>10baseT/UTP <full-duplex,hw-loopback> 10baseT/UTP <full-duplex flow-control> 100baseT<half-duplex> 100baseTX <full-duplex> 100baseTX <full-duplex,hw-loopback> 100baseTX <full-duplex,flow-control> 1000baseT <full-duplex> 1000baseT <full-duplex,hw-loopback> 1000baseT <full-duplex,flow-control> none vlan: 0 parent interface: <none> bond interfaces: <none> DB-3: Tips for Database Administrators

  35. Tools:netstat 1-bespin-gus-> netstat TCP: IPv4 Local Address Remote Address Swind Send-Q Rwind Recv-Q State -------------------- -------------------- ----- ------ ----- ------ ------- bespin.1023 rdlserv.nfsd 26280 0 49640 116 ESTABLISHED bespin.32880 bespin.6015 49152 0 49152 0 ESTABLISHED bespin.32879 bespin.6014 49152 0 49152 0 ESTABLISHED bespin.6014 bespin.32879 49152 0 49152 0 ESTABLISHED bespin.6015 bespin.32880 49152 0 49152 0 CLOSE_WAIT . . . DB-3: Tips for Database Administrators

  36. Tools: netstat -s gus@dirac:4gl $ netstat -s tcp: 794419 packets sent 37468 data packets (22991001 bytes) 51 data packets (11242 bytes) retransmitted 0 resends initiated by MTU discovery 430311 ack-only packets (196294 delayed) 0 URG only packets 0 window probe packets 322074 window update packets 4516 control packets 1308690 packets received 33675 acks (for 22967429 bytes) 2270 duplicate acks 0 acks for unsent data 1248341 packets (1521010141 bytes) received in-sequence 385 completely duplicate packets (343882 bytes) 0 old duplicate packets 41 packets with some dup. data (28512 bytes duped) 33202 out-of-order packets (38094369 bytes) 1 packet (376 bytes) of data after window DB-3: Tips for Database Administrators

  37. vmstat vmstat 3 50 procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 0 7 3656 226920 16528 5294120 0 0 360 636 281 823 4 4 49 42 0 6 3656 205504 16584 5302288 0 0 2688 903 1066 4675 7 6 11 75 2 0 3656 186088 16584 5310512 0 0 2656 684 1010 4687 6 6 8 80 22 28 3656 165196 16600 5319748 0 0 2887 871 1096 4623 8 6 8 78 0 94 3656 138524 16648 5331008 0 0 3909 815 1292 3455 8 6 6 79 . . . DB-3: Tips for Database Administrators

  38. iostat gus@dirac:gus $ iostat 5 5 disk0 cpu KB/t tps MB/s us sy id 18.07 4 0.07 7 3 90 0.00 0 0.00 2 1 97 0.00 0 0.00 4 1 95 0.00 0 0.00 2 2 96 0.00 0 0.00 3 1 95 DB-3: Tips for Database Administrators

  39. Tools:bzip2 $ ls -l tips_for_dbas_00.ppt -rw-r--r-- 1 gus staff 644096 Apr 23 09:18 tips_for_dbas_00.ppt $ bzip2 tips_for_dbas_00.ppt $ ls -l tips_for_dbas_00.ppt.bz2 -rw-r--r-- 1 gus staff 246356 Apr 23 09:18 tips_for_dbas_00.ppt.bz2 DB-3: Tips for Database Administrators

  40. Tools: Windows tabbed console DB-3: Tips for Database Administrators

  41. Tools: windows tabbed console http://sourceforge.net/projects/console/ DB-3: Tips for Database Administrators

  42. Tools: More Windoze Stuff http://www.microsoft.com/technet/sysinternals Some useful stuff you can get there: Handle: Who has what files open Process Monitor: Lots of information about processes and disk activity Autorun: what programs get started automatically Regmon: monitors registry activity Tcpview: shows all active TCP and UDP endpoints PsTools: list processes, system info, who is logged on Zoomit: zoom in on parts of screen Lots more DB-3: Tips for Database Administrators

  43. Tools: Still More Windoze Stuff http://www.baremetalsoft.com/baretail DB-3: Tips for Database Administrators

  44. Tools: Solaris pldd List the dynamic libraries linked into each process pfiles Report fstat(2) and fcntl(2) information for all open files in each process. pmap print information about the address space of a process plimit get or set the resource limits of running processes pargs print process arguments, environment variables preap force a defunct process to be reaped by its parent pkginfo list what packages are installed DB-3: Tips for Database Administrators

  45. Tools: AIX svmon svmon -G -I size inuse free pin virtual memory 1048576 425275 623301 66521 159191 pg space 262144 31995 work pers clnt pin 46041 0 0 in use 129600 275195 0 PageSize PoolSize inuse pgsp pin virtual s 4 KB - 404795 31995 46041 159191 L 16 MB 5 0 0 5 0 List top 15 memory consumers svmon -Pt15 | perl -e ’while(<>){print if($.==2||$&&&!$s++);$.=0 if(/^-+$/)}’ DB-3: Tips for Database Administrators

  46. Tools: AIX vmstat vmstat -v 1048576 memory pages 1002054 lruable pages 478136 free pages 1 memory pools 95342 pinned pages 80.1 maxpin percentage 20.0 minperm percentage 80.0 maxperm percentage 36.1 numperm percentage 362570 file pages . . . Use vmo command to adjust minperm, maxperm, numperm as needed DB-3: Tips for Database Administrators

  47. Tip Number 0x08 Windows scripting DB-3: Tips for Database Administrators

  48. Windows scripting Windows .bat scripts are brain damaged • Cygwin • provides lots of Unix tools and a shell for Windows. Also SSH server • Download from: www.cygwin.com • Windows Services for UNIX Version 3.5 • From Micro$oft, but free • Lots of goodies: Korn, C, bash shells, 350 UNIX commands, NFS, NFS gateway, telent, telnetd • Download from http://technet.microsoft.com/en-us/interopmigration/bb380242.aspx DB-3: Tips for Database Administrators

  49. Tip Number 0x09 how to turn off Windows indexing service DB-3: Tips for Database Administrators

  50. Stop Windows indexing service • Go to the Start menu, pick run. • Type services.msc, click OK. • Services dialog will show. • Right-click on indexing service to show properties dialog, • if service running, click stop. • select disabled. • click apply • close. DB-3: Tips for Database Administrators

More Related