Wanted to quickly review network ports that are being used by our DB/2 Instance.
netstat -anp | grep LISTEN | grep -i DB2
lsof
lsof -i | grep -i 'TCP' | grep 'LISTEN'
lsof 2>&1 | head -n 1
Trying to explore the various avenues for finding binaries in Linux & MS Windows.
locate reads one or more databases prepared by updatedb and writes
file names matching to standard output, one per line.
By default, locate does not check whether files found in database still
exist. locate can never report files created after the most recent
update of the relevant database.
locate [command]
locate ilist
Which takes one or more arguments. For each of its arguments it prints
to stdout the full path of the executables that would have been exe-
cuted when this argument had been entered at the shell prompt. It does
this by searching for an executable or script in the directories listed
in the environment variable PATH using the same algorithm as bash.
which [command]
which ilist
Displays the location of files that match the search pattern.
By default, the search is done along the current directory and in the paths specified by the PATH environment variable.
where [command]
where ilist
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.
The “top” command lists the processes running on a Linux box.
top stands for “Table of Processes“.
Here are sample outputs from a MySQL Box.
To review or change the ordering please press the Shift-O button.
The “Current Sort Field” window appears.
Please review the available sort fields and press the corresponding character.
The sar common stands for “System Activity Report“.
| 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 |
Get Stats for current day.
sar
Get Stats for the 7th day of the current Month
sar -p -f /var/log/sa/sa07
Get Stats for current day.
sar -P ALL
Get Stats for current day.
sar -r
Get Swap Space for current day.
sar -S
Get I/O Transfer rates and read and writes blocks.
sar -d -f /var/log/sa/sa07
Get I/O Transfer rates and read and writes blocks and in print friendly mode.
sar -dp -f /var/log/sa/sa07
Get I/O Transfer rates and read and writes blocks.
sar -b
This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.
sar -q
This reports the run queue size and load average of last 1 minute, 5 minutes, and 15 minutes.
sar -n DEV -f /var/log/sa/sa07 -s 18:00:00 -e 23:59:00 | grep 'IFACE\|eth0'
Display amount of free and used memory in the system.
Review Memory usage.
free
free -h
Reports IO Statistics.
Review IO Statistics on each device.
iostat -N
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
powershell -C "Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux"
powershell -C "systeminfo | Select-String 'OS' "
As our Build Number ( 17134 ) is greater than 16215, we can install via “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:
We chose to go with SE Linux.
![AddYourMicrosoftAccountToMicrosoftStore_20180610_0921PM [brushedup].PNG](https://danieladeniji.files.wordpress.com/2018/06/addyourmicrosoftaccounttomicrosoftstore_20180610_0921pm-brushedup.png?w=700)
Get Linux Version
uname -r
cat /etc/passwd
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.
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.
Or better still SpaceSniffer.
I spoke about SpaceSniffer here.
I know I am avoiding talking about Linux.
And, so let us talk about Linux a bit.
I suppose du means disk utilization.
First let us get a quick overview of what du does and the options it offers.
du --help
What does it do?
Summarize disk usage of each FILE, recursively for directories.
| 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 |
du -a -B 1 /dbbackup | grep "sql"
du -a -B 1024 /dbbackup | grep "sql"
du -a -h *.sql
du -a --exclude=*.gz
du --max-depth=1 -h /dbbackup
du --max-depth=4 -h /dbbackup
du --max-depth=4 /dbbackup
There is an error for “permission denied“.
Let us silence it for now.
du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied'
Let us order by size.
du --max-depth=4 /dbbackup 2>1 | grep -v 'permission denied' | sort -nr
Let us quickly discuss using “Disk Utilization“, du, to list disk utilization on a Linux System.
BTW, we will be targeting folder level information.
| 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 |
du -h * 2>/dev/null
du -c db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more
du -s db2inst1/sqllib/acs db2inst1/sqllib/db2tss 2>/dev/null | more
du -s * 2>/dev/null | more
du -s * 2>/dev/null | sort -nr
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.
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).
As all Databases that run on Linux have pretty much the system administrative steps, here is a condense straightforward list:
ps aux | grep 'sql' | grep -v 'grep'
We are interested in the actual daemon and so we will target mysql.
sudo su - [account]
sudo su - mysql
Output
Entered password
env | grep "USER"
mysql -h localhost
show global variables like '%log%'
| 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 |
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'
);
tail /logs/mysql/err_log/mysql-err.log
