Linux – Profiling System Resource Utilization – Day 1

Background

Since we are already talking about profiling Applications, let us quickly highlight some of the available tools for measuring Application Resource Utilization on a Linux box.

Terminology

  1. Memory
    • buffer
      • Amount of memory that is buffered. In simple words, buffers are used for caching of filesystem metadata (permissions, location, etc.) and tracking in-flight pages. ( Link )
    •  cache
      • Amount of memory that is cached. Cache contains data that has already been read from the disk and is kept in memory for possible future use, f.e a pdf file or web browser pages. ( Link )

Outline

  1. top ( Link )
  2. sar ( Link )
  3. free ( Link )
  4. iostat ( Link )

top

The “top” command lists the processes running on a Linux box.

top stands for “Table of Processes“.

Sample Reading

MySQL

Here are sample outputs from a MySQL Box.

Sample #1
Image

top_linux_mysql_20180812_0531PM.PNG

Output
  1. Our box has been up for 14 days
  2. CPU
    • User :- 0.2%
    • System :- 0.5%
    • Nice :- 0%
    • Idle :- 99.2%
  3. Memory
    • Mem: 1872716k total, 1598392k used, 274324k free, 204700k buffers
      • Total :- 1872716k ( 1872 MB / 1.82 GB )
      • Used :- 1598392k ( 1598 MB / 1.60 GB )
      • Free :- 274324k ( 274 MB )
  4. Swap
    • Swap: 1048572k total, 87176k used, 961396k free, 578420k cached
      • Total :-1048572K ( 1048.572 MB / 1.05 GB )
      • Used :- 87176k ( 87 MB  )
      • Free :- 961396k ( 961 MB )
  5. Processes
    • Process /MySQL
      • PID ( Process ID )
      • USER
        • MySQL
      • PR ( User Priority )
        • 20
      • NI ( Nice )
        • 0
      • VIRT ( Virtual Memory )
        • 1789 MB
          • Our database server, MYSQL, requested 1800 MB
      • RES ( Resident Memory )
        • 568 MB
          • Our database server, MYSQL, is using actual memory of 568 MB
      • SHR ( How much of Total Virtual Memory this process is sharing with others)
        • 6048
        • What is the measurement?
      • S ( Process State )
      • %CPU
      • %MEM
      • TIME+
      • COMMAND

Functionalities

Outline

  1. Order By

Order By

To review or change the ordering please press the Shift-O button.

The “Current Sort Field” window appears.

Image

top_sort_20180813_0316AM

Please review the available sort fields and press the corresponding character.

sar

Definition

The sar common stands for “System Activity Report“.

Options

Memory-rMemory free, usedsar -r

Objective Option Description Sample
Processor Cumulative -p Processor Cumulative sar -P ALL
Processor Detail -P Process All or Specific sar -P ALL
Processor Run Queue and Load Average -q Reports run queue and load average sar -q
Swap Space Utilization -S Swap space utilization sar -S
Storage I/O -b Storage IO Transactions ( Count & Bytes) sar -b
Storage I/O @ Block Device Level -d IO Transactions ( Count & Bytes) sar -d
Network -n Network sar -n DEV

Scenarios

Review current Days report.

Outline

Get Stats for current day.

Code

sar

Output

linux_db2_20180813_0338AM.PNG

Explanation
  1. CPU
    • All available CPU
  2. %user
    • User applications CPU Percentile usage
  3. %nice
    • CPU Percentile usage attributed to applications running under “nice” mode
  4. %System
    • % CPU by system applications ( Kernel, device drivers )
  5. %iowait
    • % CPU Idle waiting for I/O completion
    • References
      • Red Hat Enterprise Linux – What Is the Meaning of %iowait as Reported by Utilities Such as sar or top?
        Link

Review past reports

Outline

Get Stats for the 7th day of the current Month

Code

sar -p -f /var/log/sa/sa07

Output

linux_db2_20180812_0957PM

Review CPU Utilization full and individualized

Outline

Get Stats for current day.

Code

sar -P ALL 

Output

linux_db2_20180814_0341AM

Explanation
  1. CPU
    • All available CPU
  2. %user
    • User applications CPU Percentile usage
  3. %nice
    • CPU Percentile usage attributed to applications running under “nice” mode
  4. %System
    • % CPU by system applications ( Kernel, device drivers )
  5. %iowait
    • % CPU Idle waiting for I/O completion
    • References
      • Red Hat Enterprise Linux – What Is the Meaning of %iowait as Reported by Utilities Such as sar or top?
        Link

Review Memory Utilization

Outline

Get Stats for current day.

Code

sar -r 

Output

sar-r-20180814-0347AM.PNG

Explanation
  1. kbmemfree
    • Free Memory in KB
  2. kbmemused
    • Used Memory in KB
  3. %memused
    • Percentile ( %) of Memory Used
  4. kbbuffers
    • Buffers for disk writes
  5. kbcached
    • Used as cached for disk reads
  6. kbcommit
    • Amount of Memory needed for current workload.  Total Memory needed to ensure that we do not run out of Memory.
  7. %commit
    • % Total Memory that is actually in use per current workload

Review Swap Space Utilization

Outline

Get Swap Space for current day.

Code

sar -S 

Output

sar-S-20180814-0755AM.PNG

Explanation
  1. kbswpfree
    • Free Swap Space in KB
  2. kbswpused
    • Used Swap Space in KB
  3. %swpused
    • Percentile ( %) of Swap Space Used
  4. kbswpcad
    • Amount of cached swap memory in kilobytes. This is memory that once was swapped out, is swapped back in but still also is in the swap area (if memory is needed it doesn’t need to be swapped out again because it is already in the swap area. This saves I/O). ( Link )
  5. %swpcad
    • Percentage of cached swap memory in relation to the amount of used swap space. ( Link )

Review I/O Transfer Rates

Outline

Get I/O Transfer rates and read and writes blocks.

Code

sar -d -f /var/log/sa/sa07 

Output
sar-d_20180814_0924AM.PNG
Explanation
  1. DEV
  2.  tps
    • Total number of transfers per second that were issued to physical devices. A transfer is an I/O request to a physical device. Multiple logical requests can be combined into a single I/O request to the device. A transfer is of indeterminate size. ( Link )
  3. rd_sec/s
    • Number of read requests per second
  4. wr_sec/s
    • Number of writes per second
  5. avgrq-sz
    • The average size (in sectors) of the requests that were issued to the device. ( Link )
  6. avgqu-sz
    • Average Queue Size
    • The average queue length of the requests that were issued to the device. ( Link )
  7. await
    • The average time (in milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them. ( Link )
  8. svctm
    • The average service time (in milliseconds) for I/O requests that were issued to the device. Warning! Do not trust this field any more. This field will be removed in a future sysstat version. Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this value is close to 100%. ( Link )
  9. %util
    • Percentage of CPU time during which I/O requests were issued to the device (bandwidth utilization for the device). Device saturation occurs when this value is close to 100%. ( Link )
Outline

Get I/O Transfer rates and read and writes blocks and in print friendly mode.

  1. sar -dp
    • Option
      • d is disk
      • p is print friendly
    • Crediting
      • Community Home Servers and Operating Systems Operating System – Linux System Administration “sar -d” devices – how to map to /dev/sdNNN device…
        Link
Code

sar -dp -f /var/log/sa/sa07 

Output

sar-dp_20180814_1140AM.PNG

Review I/O Transfer Rates

Outline

Get I/O Transfer rates and read and writes blocks.

Code

sar -b 

Output
sar-b-20180814-0810AM.PNG
Explanation
  1. tps
    • Total number of transfers per second that were issued to physical devices. A transfer is an I/O request to a physical device. Multiple logical requests can be combined into a single I/O request to the device. A transfer is of indeterminate size. ( Link )
  2. rtps
    • Number of read requests per second
  3. wtps
    • Number of writes per second
  4. bread/s
    • Number of sectors read per second.  A sector is 512 bytes  ( Link )
  5. bwrtn/s
    • Number of sectors written per second.  A sector is 512 bytes ( Link )

Reports run queue and load average (sar -q)

Outline

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.

Code

sar -q 

Output

sar-q_linux_db2_20180814_1217PM.PNG

Explanation
  1. runq-sz :- Run Queue Size
  2. plist-sz :- Process List
  3. ldavg-1 :- Load Average Size taken 1 minute ago
  4. ldavg-5 :- Load Average Size taken 5 minutes ago
  5. ldavg-15 :- Load Average Size taken 15 minutes ago
  6. blocked :- Number of blocked tasks

Reports run queue and load average (sar -n)

Outline

This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.

Code

sar -n DEV -f /var/log/sa/sa07 -s 18:00:00 -e 23:59:00 | grep 'IFACE\|eth0'

Output

sar-n-DEV-linux_db2_20180814_1250PM

Explanation
  1. IFACE
    • Interface
  2. rxpck/s
    • Receive packets
  3. txpck/s
    • Transmit packets
  4. rxkB/s
    • Receive Kb/s
  5. txkB/s
    • Transmit Kb/s
  6. rxcmp/s
  7. txcmp/s
  8. rxmcst/s


Free

Definition

Display amount of free and used memory in the system.

Outline

Review Memory usage.

Usage

Usage – Raw Data

Code

free

Output

free_linux_db2_20180814_04432AM.PNG

Explanation
  1. total
    • Total Memory
  2. used
    • Used Memory
  3. free
    • Free Memory
  4. shared
    • Shared Memory
  5. buffers
    • Memory allotted for writing
  6. cached
    • Memory allotted for reading

Usage – Human Readable

Code

free -h

Output

free_linux_db2_-h_20180814_0732AM.PNG

iostat

Definition

Reports IO Statistics.

Outline

Review IO Statistics on each device.

Usage

Usage – Raw Data

Code

iostat -N

Output

iostat_db2_linux_20180815_0506PM.PNG

 

Explanation
  1. Summary
    • Average CPU
      • User
        • 7.4%
      • System
        • 2.51%
  2. Detail
    • Device
      • data1-db2data–11
        • tps :- 226.42
        • Blk_read/s :- 23254.73
        • Blk_wrtn/s :- 347.39
        • Blk_read :- 35060914332
        • Blk_wrtn :- 523763058
      • data1-dbbackup–11
        • tps :- 796.31
        • Blk_read/s :- 5785.45
        • Blk_wrtn/s :- 6187.70
        • Blk_read :- 8722664698
        • Blk_wrtn :- 9329126392

References

  1. BooleanWorld
    • Top Command
      • A Guide to the Linux Top Command
        Link
  2. Lily Feng
    • Top Command
      • The difference among VIRT, RES, and SHR in top output
        Link
  3. nixCraft
    • How do I Find Out Linux CPU Utilization?
      Link
  4. Ramesh Natarajan
    • The Geek Stuff
      • 10 Useful Sar (Sysstat) Examples for UNIX / Linux Performance Monitoring
        Link
  5. Neeraj Bhatia
    • Linux Memory Utilization
      Link
  6. Lisenet
    • Tomas
      • Measure and Troubleshoot Linux Memory Resource Usage
        Link
  7. HowtoForge
    • Linux Free Command Explained for Beginners (6 Examples)
      Link
  8.  stackExchange
    • top
      • Top – what does Virtual memory size mean? …linux/ubuntu
        Link
      • Process ‘niceness’ vs. ‘priority’
        Link
  9. serverfault
    • top
      • Memory Usage Numbers In top/htop
        Link
  10. Quora
    • top
      • What is the difference between the NI and PR values in the top(1) command’s output? I know NI is the nice value, which ranges from -19 to 20, but what is the significance of PR value?
        Link

Windows 10 – Installing Linux SubSystem

Background

Availing Linux on MS Windows 10.

Here is the guide that we will be using:

Windows 10 Installation Guide
Install the Windows Subsystem for Linux
Link

Outline

  1. Enable Windows Feature
    • Microsoft Windows Subsystem Linux
  2. Review OS Version
    • Issue SystemInfo and get OS Version and Build Number
  3. Choose Install Location, based on Build Number
    • If Build Number > 16215
      • We can install from Windows Store
    • Else
      • Enable Windows Developer Mode
      • Start Bash or use “lxrun /install
  4. Register Product
  5. Create Linux User
  6. Set root user’s password

 

Install Steps

Enable Windows Optional Features

Microsoft-Windows-Subsystem-Linux

Code

powershell -C "Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux"

System Info

Code


powershell -C "systeminfo | Select-String 'OS' "

Output

version_20180610_0902PM

Explanation

As our Build Number ( 17134 ) is greater than 16215, we can install via “Microsoft Store“.

Microsoft Store

Visit Microsoft Store and search for Linux.

https://www.microsoft.com/en-us/search/result.aspx?q=linux

Here is the result of that search:

MicrosoftStore_Linux.PNG

 

SUSE Linux Enterprise Server 12

We chose to go with SE Linux.

SUSE Linux Enterprise Server 12 – Get

SUSELinuxEnterprise_20180610_0905PM.PNG

Use across your devices

UseAcrossYourDevices_20180610_0906PM.PNG

Add your Microsoft account to Microsoft Store

AddYourMicrosoftAccountToMicrosoftStore_20180610_0921PM [brushedup].PNG

Enter Password

AddYourMicrosoftAccountToMicrosoftStore_EnterPassword_20180610_0923PM.PNG

Installing

Installing_20180610_0911PM

SUSE Linux Enterprise Server 12 – Registration +User

registration_20180610_0926PM.PNG

SUSE Linux Enterprise Server 12 – Registration +User +Password

registrationUserAndPassword_20180610_0927PM.PNG

Validation

Bash

uname

Get Linux Version

Code


uname -r

Output

bash_uname_20180610_0938PM

List Users

Code


cat /etc/passwd

Output

security_users_20180610_0947PM.PNG

References

  1. Microsoft
    • Install the Windows Subsystem for Linux
      • Install the Windows Subsystem for Linux
        Link
    • Rich Turner
      • Bash on Ubuntu on Windows – Download Now!
        Link

Linux – Disk Utilization using du

Background

End of Year, Beginning of a new year, and end of Month are often good times to stress a computer system.

We have had our systems stressed these last couple of months.

Often it is running low on disk space, a lot of higher than normal database backup and log backups.

 

MS Windows

Microsoft Tools

On Windows I will often use Microsoft’s own Computer Management to review disks logical size and utilization.

Or File Explorer and filter by size looking for huge ones.

Space Sniffer

Or better still SpaceSniffer.

I spoke about SpaceSniffer here.

Linux

I know I am avoiding talking about Linux.

And, so let us talk about Linux a bit.

du command

Introduction

I suppose du means disk utilization.

Help

First let us get a quick overview of what du does and the options it offers.

Syntax


du --help

Output

Explanation

What does it do?

Summarize disk usage of each FILE, recursively for directories.

Options Tabulated

Option Meaning Usage
-a List files as well, not just directories Usage is du -a
-B Block Size -b 1 is default
-b 1024 means to divide the actual size by 1 K
-h Human readable  usage is du -h
–exclude=PATTERN Exclude files based on certain filename or extension du -a -B 1 -h –exclude=*.gz /dbbackup
 –max-depth  Our many folders down to go  du –max-depth=4 /dbbackup

 

Sample

Block Size ( -B)
Sample ( Block Size at 1 )

du -a -B 1 /dbbackup | grep "sql"

Output

Explanation
  1. /dbbackup/gregory/export.sql is 4096

 

Sample ( Block Size at 1024 )

du -a -B 1024 /dbbackup | grep "sql"

Output

Explanation
  1. /dbbackup/gregory/export.sql
    • was at 4096
    • But, now is 4
      • 4096/1024 => 4

 

Human Readable ( -h)
Sample ( Block Size at 1024 )

du -a -h *.sql

Output

Explanation
  1. /dbbackup/gregory/export.sql
    • was at 4096
    • But, now a more readable 4.0 K

 

exclude ( -exclude=filter)
Sample ( Exclude files already compressed via gzip )

du -a --exclude=*.gz

max-depth=N
Depth to show = 1

du --max-depth=1 -h /dbbackup

Output
 

Depth to show = 4

du --max-depth=4 -h /dbbackup

Output

Explanation
  1. In our first sample:
    • We progressed down only one folder tree from our asking node ( /dbbackup )
  2. In our second example:
    • We went down 4 levels from our root from /dbbackup

 

Use case scenario

Use case #1

Get Folder Size for our backup folder ( /dbbackup)

Baseline

Sample

du --max-depth=4 /dbbackup

Output

 

Silence Errors

There is an error for “permission denied“.

Let us silence it for now.

Sample

du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied'

Output

Explanation
  1. Using 2>1
    • We redirect errors such as permission denied to standard output
  2. Using grep -v
    • We filter out ‘permission denied’

 

Ordered Results

Let us order by size.

Sample

du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied' | sort -nr

Output

 

References

  1. StackOverflow
    • Exclude all permission denied messages from “du”
      Link

Linux – Disk Utilization [ du ]

Background

Let us quickly discuss using “Disk Utilization“, du, to list disk utilization on a Linux System.

BTW, we will be targeting folder level information.

Options

Tabulated

Option Option Short Form Option Long Form Sample
Human Readable -h –human-readable K – Kilobytes
M – Megabytes
G – Gigabytes
T – Terabytes
Total -c –total du –total
Summarize -s –summarize du -s
du –summarize

Sample

Human Readable

Command


du -h *  2>/dev/null

Output

Total

Objective

  1. List sizes for the following folders and at the tail end list aggregated size, as well
    • db2inst1/sqllib/acs
    • db2inst1/sqllib/db2tss

Command


du -c db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more

Output

Summarize

Summarize for specific folders

Objective
  1. Summarize sized for the following folders
    • db2inst1/sqllib/acs
    • db2inst1/sqllib/db2tss
Command

du -s db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more

Output

Summarize for all folders

Objective
  1. Summarize all sub-folders
Command

du -s * 2>/dev/null | more

Output

Summarize for all folders and Ordered

Objective
  1. Summarize all sub-folders
  2. And, order the results
Command

du -s * 2>/dev/null | sort -nr

Output

References

  1. Lifewire
    • How To Find How Much Disk Space A File Or Folder Uses In Linux
      Link
  2. Stack Exchange
    • Listing directories based on size from largest to smallest on single line
      Link

MySQL on Linux – Checking Log files

Background

I am am On-Call this week.

As we have a System Reconfiguration slated for late hours when changes that includes system downtime are often scheduled to occur, my manager checked-in with me to see whether I was OK being assigned the Task.

I said Yes, and so here I find myself gauging my actual readiness.

 

On-Call Check Preparedness

Our team lead has written a very nice detailed document that covers each database platform( DB/2, Oracle, MySQL on Linux and SQL Server on Windows).

 

Outline

As all Databases that run on Linux have pretty much the system administrative steps, here is a condense straightforward list:

  1. Initiate SSH to Linux Host
  2. Authenticate
  3. sudo to DB Engine User
  4. Connect to DB Instance
  5. Change context to problematic database
  6. Review Error Logs
  7. Review System Metrics ( Disk / Memory / and CPU )

 

MySQL on Linux

SSH to Linux

Putty

  1. Hostname = Hostname
  2. Port = 22
  3. Connection Type :- SSH

Image

 

Authenticate

Steps

  1. Login as :- <username>
  2. Password :- <password>

Image

Authenticated

Image

 

Confirm user running mysql

Code


ps aux | grep 'sql' | grep -v 'grep'

Output

Explanation

  1. There are two mysql processes
    • /usr/bin/mysql_safe
      • user :- root
    • /usr/sbin/mysqld
      • user :- mysql
      • daemon process :- mysqld
      • basedir :- /usr
      • datadir :- /data/mysql
      • user :- myssql/err_log/mysql-err.log
      • port :- 3306
  2. What is the difference between the two processes
    • /usr/bin/mysql_safe
      • It is the bootstrap process
      • It ensures that mysqld is running and restarted if knocked down
    • /usr/sbin/mysqld
      • Actual mysql daemon process

sudo to DB Engine User

We are interested in the actual daemon and so we will target mysql.

sudo command

Syntax

sudo su - [account]

Code

sudo su - mysql

Output

Entered password

 

validate sudo user

Syntax

env | grep "USER"

Output

Connect to mysql

Code


mysql -h localhost

Output

 

Get Configuration Information – log file

Code


show global variables like '%log%'

Output

Explanation

variable_name value meaning
general_log OFF Debug Mode. Should only be used during brief troubleshooting sessions.  Logs all connections and query submissions.
general_log_file /logs/mysql/mysqld.log  Log file
log OFF  In MySQL v5.1.29, deprecated in favor of general-log
log_error  /logs/mysql/err_log/mysql-err.log Errors and Startup messages
log_output FILE This option determines the destination for general query log and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. ( Link )
log_queries_not_using_indexes OFF To include queries that do not use indexes for row lookups in the statements written to the slow query log
log_throttle_queries_not_using_indexes OFF  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexessystem variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.
( Link )
log_slow_queries OFF  Slow query log.
Deprecated in favor of slow_query_log ( 5.1.29 ).
Please see here and here.
log_warnings 2  Print out warnings such as Aborted connection… to the error log. This option is enabled (1) by default. To disable it, use –log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.
( Link )
long_query_time 2  If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively.
slow_query_log OFF Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log.

The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

slow_query_log_file /logs/mysql/slow-queries.log  File name to log Slow query

 

 

More Concise Queries

Query -01

show global variables where variable_name in 
( 
      'general_log'
    , 'general_log_file'
    , 'log_error'
    , 'log_warnings'
    , 'long_query_time'
    , 'slow_query_log'
    , 'slow_query_log_file'
);

Output -01

View Log files

mysql_err.log

tail


tail /logs/mysql/err_log/mysql-err.log

output

Explanation

  1. Not much bad going on

 

References

  1. mysql
    • Show Variables
      • Log Destinations
        Link
      • Show Variables
        Link
    • Server System Variables
      • System Server Variables
        Link
    • log Warnings
      • MySQL 5.7 Reference Manual / .. / Communication Errors and Aborted Connections // B.5.2.11 Communication Errors and Aborted Connections
        Link
    • General Query & Slow Query Log Output
      • 5.4.1 Selecting General Query and Slow Query Log Output Destinations
        Link
    • log_slow_queries
      • server-options.html#option_mysqld_log-slow-queries
        Link
    • Slow Query Log
      • MySQL 5.7 Reference Manual /Slow Query Log
        Link
  2. mySQL VS mysqld
    • How to start mysqld through mysqld_safe
      Link
  3. pontikis.net
    • How and When To Enable MySQL Logs
      Link
  4. Unix Commands
    • ps
      • nixcraft
        • Warning: bad syntax, perhaps a bogus ‘-‘? See /usr/share/doc/procps-3.2.7/FAQ
          Link
        • Quick Shell Tip: Remove grep command while grepping something using ps command
          Link
    • sudo
      • StackExchange
        • Where does sudo get the currently logged in username from?
          Link

SQL Server – Analysis Services – Cannot Connect ( Microsoft.AnalysisServices.AdomdClient )

Background

Users are having problems connecting to the Analysis Services Server.

 

Error Message

Image

Text


TITLE: Connect to Server
------------------------------

Cannot connect to ....sql01.

------------------------------
ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, 
or established connection failed because connected host has failed to respond z.y.76.188:2383 (System)


Text


===================================

Cannot connect to d-isql01.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------
Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond xx.xx.xx.xx:2383 (System)

------------------------------
Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)


TroubleShooting

Server

Resource Monitor

If the server has Microsoft’s Resource Monitor built-in let us use to it to review Network Ports.

Image

Explanation

  1. Listening Ports
    • Image :-
      • msmdsrv.exe
    • Address :-
      • IPV4 unspecified
      • IPV6 unspecified
    • Port :-
      • 2383
    • Protocol
      • TCP
    • Firewall Status
      • Allowed, not restricted

 

Client

netstat

Script


netstat -an | find "SYN_SENT"

Sample Output

Explanation

  1. Request was sent to port 2383
  2. Status is SYN_SENT
    • SYN_SENT means waiting on a reply

Remediation

Open up TCP Port 2383.

 

Review

Once the Network Port, 2383, is opened up retry access with SSMS.

 

Network Flow

Also, it makes sense to review the Network Traffic to get a full list of ports to be opened.

WireShark

Output

 

Explanation

Seems Analysis Services exclusively relies on port 2383.

Linux – Finding Text in Log Files – Day 1

Background

Don’t really spend much time in the Linux World.

But, there are many Application Servers that sit on Linux Servers and rely on SQL Server on Windows.

 

Log Files

When error occurs a good Application Server write these errors in Log Files.

After a while needs develop to trend the type of DB Connectivity and more serious hard errors.

 

Sample Log Files

As we discussed in a recent post, we copied over SQL Server Error Log files over to Linux.

 

Text Search

grep

Let us use grep to perform simple Text Searches.

Scenario

Look for Text Matching Manufacturer

Script

grep -w -i -l -n  "Manufacturer" ERRORLOG

Output

Explanation
  1. No matches

 

Remediation

Attempted Workarounds

We tried the following workarounds

  1. Worked
    • Created a much simple text file and was able to grep against it
  2. Did Not Help
    • Modified copy over mode to binary and back to text
    • Command Issued
      • cat ERRORLOG | grep -i “manufacturer”

 

Worked

Code


cat ERRORLOG | tr -d '\000' | grep -i "manufacturer"

Output

Why does it work

  1. Cat
    • Outputs a file’s content
  2. tr – stands for translate
    • tr -d ‘\000’
      • -d means delete
      • \000 means null character

Crediting

What makes grep consider a file to be binary?
Link

When you are top 13% on stackexchange, you are one of the Big Boys and Girls.