monitoring linux performance for the sql admin - pptx...technical assistance if you require...
TRANSCRIPT
-
MonitoringLinuxPerformancefortheSQLServerAdminAnthonyNocentino,EnterpriseArchitect,Centino Systems
-
TechnicalAssistance
If you require assistance during the session, type your inquiry into the question pane on the right side.
Maximize your screen with the zoom button on the top of the presentation window.
Please fill in the short evaluation following the session. It will appear in your web browser.
-
ThankYoutoOurSponsors
SUSEisapioneerinopensourcesoftware.Theyprovidereliable,software-definedinfrastructureandapplicationdeliverysolutionsthatgiveenterprisecompanies
greatercontrolandflexibility.Formorethan25yearsthey’vebeendeliveringengineeringexcellence,exceptional
serviceandanunrivaledpartnerecosystemtohelptheircustomersmanagecomplexity,reducecost,andconfidentlydelivermission-criticalservices.
Formoreinformationvisit:https://www.suse.com/partners/alliance/microsoft/sql-server-on-linux/
-
LEARN MORE
Career Centerhttp://www.pass.org/Careers.aspx
-
AnthonyE.NocentinoEnterpriseArchitect,CentinoSystems
ConsultantandTrainer
FounderandPresidentofCentino Systems
Specializeinsystemarchitectureandperformance
ComputerScience,M.S.andB.S.
MicrosoftMVP- DataPlatform
FriendofRedgate
LinuxFoundationCertifiedEngineer
MicrosoftCertifiedProfessional
Otherplacesonline…
Blog- www.centinosystems.com/blog
PluralsightAuthor
/nocentino @nocentino
-
• LinuxSystemArchitecture• SQLonLinuxArchitecture• SystemComponents
• CPU/Processes• Memory/Pages• Disk/FileSystems
• MonitoringTools
Agenda
-
• LinuxOSconcepts,howitworks!• Toolstoviewperformancedata• What’sgoodandwhat’sbad
Thingswe’regoingtocover
Thingswe’reNOTgoingtocover• SQLServerinternals• Performancetroubleshooting
-
LinuxArchitecture
Kernel Resource Management and AccessProcess, Pages and File
Systems
ShellExecutes Your
Commands…Your Interface to the Kernel
Commands, Editors…any User Program
Hardware Physical Resources CPU, Memory and Disk
Users Interact with the Shell Cause Problems :)
Kern
el
Spac
eUs
er S
pace
-
SQLonLinuxArchitecture- Drawbridge
From:https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/
-
SQLOS
PlacingtasksintoworkersandgettingaccesstotheCPU
Controllingaccesstosystemresources
SchedulingofI/Obothnetworkanddisk
Allocationofmemorytovarioussystemobjects
Scheduling Synchronization I/OMemoryManagement
PrimaryfunctionisresourcemanagementspecifictoRDBMS
“AnewplatformlayerinSQLServer2005toexploitnewhardwarecapabilitiesandtheirtrends”S.Oks
“OperatingSystemsupportforDatabaseManagement”M.Stonebraker
-
SQLonLinuxArchitecture- ProcessLayout
From: https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/
-
SQLonLinuxArchitecture- SQLPAL
From:https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/
AllotherCode
Win32HostedAPIs(LibOS) SQLOSAPI
SQLOSv2
HostExtension(Memory,Threads/Scheduling,I/O)
PerformanceSensitiveCode
LinuxKernelviaSystemCalls
-
• CalltablemapsWin32APIsemanticstoLinuxSystemcalls• ~45ABICalls• MemoryManagement• ThreadsandScheduling• SynchronizationPrimitives• I/ONetworkandDisk
• Wecarealotabouthostextensions…it’smorecode
SQLonLinuxArchitecture- HostExtensions
-
• Processvirtualization(notmachine)• Presentinganotherenvironmentinsidetheprocess’contextthat’sdifferentthan
thatofthehardware’soperatingenvironment
• ButtheenvironmentispurposebuiltforSQLServer
• WeneedtounderstandthatthisisahybridWin32/Linuxprocessandhaveafirmgraspof• ResourceallocationandmanagementinSQLPAL• HowthatturnsintoLinuxOSperformance• Debugging
Shhhhhh- SQLPALisVirtualization;)
-
CPUandProcesses
-
• Process• Executingprogram,programcode,memoryandresources
• Thread(LWP)• Sharedaccesstoresources
• ProcessandThreadCreation• fork/exec - parentprocessyieldsachildprocesswithanewPID• clone - sameaddressspaceasthreadcreator,cheapandfast!
• ProcessTree• Thehierarchyofparentandit’schildprocesses
WhatisaProcess
-
• Process States
WhatisaProcess(con’t)
Running
Runnable
ZombieStopped
Sleeping
-
• Signals• Methods of process control• kill and killall
• Niceness• Set the execution priority• nice and renice• Default 20, lower is less “nice”
ControllingProcesses
-
• Contextswitching• KernelversusUserMode• CPUScheduling• HowisaSQLOSWorkerscheduledontotheCPU?• Createsathreadviapthreadandthat’spushedintothescheduler
• pthreads?
MoreonProcesses…
-
• Unitofschedulingisthethread• DefaultschedulerisSCHED_OTHER/SCHED_NORMAL
• Timesharingscheduler• Preemptive• Dynamicprioritylist,basedonniceness• Calculatedquantumlengthbasedonpriority
Process/ThreadScheduling
-
• Unitofschedulingisthethread• DefaultschedulerisSCHED_OTHER/SCHED_NORMAL
• Timesharingscheduler• Preemptive• Dynamicprioritylist,basedonniceness• Calculatedquantumlengthbasedonpriority• kernel.sched_min_granularity_ns = 10000000 (10ms)- default• kernel.sched_wakeup_granularity_ns = 15000000 (15ms)- default
• NUMAAware,but…• kernel.numa_balancing = 0 - default
Process/ThreadScheduling
-
• Percentageofwhat?• Loadaverage• RunqueuelengthandI/Owaits• Spikesaren’tbad• Longwaits• User• I/O- disklatencywilleffectaccesstotheCPU• System
CPU- Whattolookfor?
-
• top/htop• ps• mpstat/pidstat• dstat• procfs
Toolstouseforprocessmonitoring
-
• Processesandthreads• RunloadaverageunderCPUsaturation• Exploringprocfs
Demos
-
MemoryandPages
-
• MemoryLayoutandArchitecture• PhysicalandVirtualMemory• NUMA- freelistspernode• Pages(Anonymous)• DemandPaging• Swapout• TimeandPressure
• Swapin,MajorPageFault• Allocation,MinorPageFault
• FileSystemCacheandswappiness - http://red.ht/2cHg9Vk
Memory
-
• Regularpages- 4KB• Transparenthugepages- 2MB• IncreasesmemoryI/ObydecreasingTLBcachemisses
• SQLOSv2• CanrequestlargepagesinsideSQLServer…withtraceflag834• SQLwillallocatememoryonstartup• WhenSQLPALexposes8GB+toSQLServer
• Asoftoday,nolockedpages…butTF835ison?
Pages
-
• Highconsumersofspace• Physical• Virtual
• ExternalmemorypressureonSQLServer• Excessiveswapping• swappingin/out
Memory- Whattolookfor?
-
• /proc/meminfo• free• top/htop• ps• vmstat• pidstat
Toolstouseformemorymonitoring
-
• Memorylayout• Isolatingamemoryhog• Identifyingexternalmemorypressure• ExternalmemorypressureonSQLServer
• Excessiveswapping• Swappingin/Swappingout
Demos
-
DisksandFileSystems
-
• Sectors(physical)• Actualstorageunitofthedisk,512Bor4KB
• Blocks(logical)• FundamentalunitofI/O,allocation
• Diskshavefiniteperformancecharacteristics• Bandwidth- howmuchdata• Latency- howfast
• StorageInterconnects• Internal• External
Disks
-
• XFS• Defaultfilesystem- http://red.ht/2dBXccx
• EXT4• Blocksize• Impactutilizationandperformancenominally• 4KBdefaultblocksize
• Mounttimeoptions• Accesstimes- noatime
FileSystems
-
• XFSandEXT4essentiallythesame• Files• i-nodes• Extents• Blocks
BlockAllocationinLinux
-
• StreamI/OviaNTUM
• FastI/Oviathehostextension• KernelasynchronousIO(kaio)• io_submit()• Returnstocallerimmediately,completionpollingisinuserspace
• O_DIRECT - bypassespagecacheandI/Ostaysinusermode• fsync()• “probablydesignedbyaderangedmonkeyonsomeseriousmind-controllingsubstances.”- Linus• man 2 open
I/OunderSQLPAL
-
• SaturateddisksandI/Osubsystems• Swapping• Baseline!
Disks- Whattolookfor?
-
• iostat• iotop• pidstat• dstat
Toolstousefordiskmonitoring
-
• FindinghighI/Oprocesses• Measuringdisklatency(DMVsandcmdlinetools)• sys.dm_io_virtual_file_stats
Demos
-
MonitoringTools
-
• Nearlyeverythingwe’vetalkedaboutsofarhasbeenpointintime…whataboutbaselining?
• sar - SystemActivityReporter• dstat - writestoCSV
BaseliningTools
-
• Youhaveallofthesametoolsyou’reusedtoforSQLServer• BecauseofSQLOSweget• DMVs• ExtendedEvents
ToolsforMonitoringSQLServer
-
• NewDMVs
• PSSDiag• https://blogs.msdn.microsoft.com/sqlcat/2017/08/11/collecting-performance-data-with-
pssdiag-for-sql-server-on-linux/• DBFS• https://github.com/Microsoft/dbfs• http://www.centinosystems.com/blog/sql/dbfs-command-line-access-to-sql-server-dmvs/
• Grafana• https://blogs.msdn.microsoft.com/sqlcat/2017/07/03/how-the-sqlcat-customer-lab-is-
monitoring-sql-on-linux/
NewToolsAvailableforSQLonLinux
-
• Don’tjustlistentome…here’swhatMicrosoftisinterestedin• CPU- mpstat,pidstat• Disk- iostat,iotop• Memory- free,sar• Network- sar• DMVData• Systemloginformation
MetricsCapturedbyPSSDiag
-
• LinuxSystemArchitecture• SQLonLinuxArchitecture• SystemComponents
• CPU/Processes• Memory/Pages• Disk/FileSystems
• MonitoringTools
Review
-
Needmoredata?Blogwww.centinosystems.com/blog
PluralsightUnderstandingandUsingEssentialToolsforEnterpriseLinux7Linuxbasics,systemarchitecture,fileanddirectorymanagement
LFCE:AdvancedNetworkandSystemAdministrationsystemd,PerformanceandTools,remotefilesystemsandKerberos
SQLServeronLinuxAdministrationFundamentalsInstallation,configurationandmanagement
-
References
Manyofthemanpages
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-performance-best-practices
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/performance_tuning_guide/index
https://www.kernel.org/doc/Documentation/
https://ext4.wiki.kernel.org/index.php/Clarifying_Direct_IO%27s_Semantics
-
Questions?
-
SQLServeronLinux:DBAFocusedLessonsLearnedfromEarlyDeployments• Denzil Ribeiro
Comingupnext….
-
Thankyouforattending!