Utility Guide for each OS for Problem Analysis#
Overview#
When providing technical support for a DBMS, the performance views that Altibase provides are sometimes not enough to solve a problem. In such cases, several commands provided by the operating system can supply the missing information. This document describes how to use those commands.
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
Info
This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.
The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.
Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.
Common Commands#
This section describes commands that are available on all operating systems.
netstat#
Use netstat to check the network configuration and whether error packets have occurred.
$ netstat -in
Kernel Interface table
Iface MTU RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg
enp5s0 1500 11116829 0 126934 0 74209 0 0 0 BMRU
enp7s0 1500 0 0 0 0 0 0 0 0 BMU
lo 65536 4709762 0 0 0 4709762 0 0 0 LRU
When a problem occurs in sending or receiving packets, values such as RX-ERR, TX-ERR, RX-DRP, TX-DRP, RX-OVR, and TX-OVR increase. If these values increase, the network may have a problem, so ask the network administrator to check it.
vmstat#
vmstat shows the memory, disk, swap in/out, and CPU status of the system at an interval that the user specifies.
$ vmstat 1 5 # print 5 times at 1-second intervals
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 14407272 377356 472884 0 0 0 1 1 1 0 0 100 0 0
1 0 0 14405904 377356 472884 0 0 0 0 150 381 0 0 99 0 0
1 0 0 14406332 377356 472884 0 0 0 0 56 111 0 0 100 0 0
0 0 0 14406332 377356 472884 0 0 0 0 36 67 0 0 100 0 0
0 0 0 14407200 377356 472884 0 0 0 16 45 104 0 0 100 0 0
The main indicators to check are as follows.
| Item | Description |
|---|---|
| r of procs | The number of threads waiting for the CPU. A large value indicates a CPU bottleneck. |
| free of memory | The free space in physical memory. |
| si, so of swap | An increase means that disk I/O is occurring between the swap disk and memory. |
| All items of cpu | Observe the trend of each item. |
Linux#
CPU Usage by Thread#
On Linux, the top command with the -H option shows CPU usage by thread. However, this requires procps 3.2.7 or later. If the installed procps version is older, use the ps command as follows. This method may not work properly on very old versions either.
$ ps -LFm -p 7153
UID PID PPID LWP C NLWP SZ RSS PSR STIME TTY TIME CMD
altibase 7153 1 - 8 47 921763 896248 - 16:53 ? 00:10:50 /home/lim272/altibase_home/bin/altibase -p b
altibase - - 7153 0 - - - 2 16:53 - 00:00:00 -
altibase - - 7154 0 - - - 0 16:53 - 00:00:00 -
altibase - - 7163 0 - - - 7 16:53 - 00:00:10 -
altibase - - 7164 0 - - - 1 16:53 - 00:00:10 -
altibase - - 7165 2 - - - 1 16:53 - 00:03:20 -
The main items to check are as follows.
| Item | Description |
|---|---|
| LWP | The unique number of the thread |
| C | The CPU usage of the thread |
pstack#
After finding the threads that use the most CPU with ps, use pstack to check what each of those threads is currently doing. Note that on some older kernels, pstack shows only the stack of the specified thread.
$ pstack 7153
Thread 45 (Thread 0x7f8d27794700 (LWP 7164)):
#0 0x00007f8d2e8669b3 in epoll_wait () from /lib64/libc.so.6
#1 0x0000000000f5e761 in cmnDispatcherDetectSOCKEpoll(cmnDispatcher, iduList, unsigned int, PDL_Time_Value) ()
#2 0x0000000000f49dcb in cmiSelectDispatcher(cmnDispatcher, iduList, unsigned int, PDL_Time_Value) ()
#3 0x000000000042a4fc in mmtServiceThread::findReadyTask(PDL_Time_Value*) ()
#4 0x000000000042cfb7 in mmtServiceThread::run() ()
#5 0x0000000000f80da2 in idtContainer::staticRunner(void*) ()
#6 0x00007f8d2f796df3 in start_thread () from /lib64/libpthread.so.0
#7 0x00007f8d2e8663dd in clone () from /lib64/libc.so.6
Thread 44 (Thread 0x7f8d26bf3700 (LWP 7165)):
#0 0x0000000000e6660e in smnnSeq::fetchNext(smnnIterator, void const) ()
#1 0x0000000000ec70ce in smiTableCursor::readRow(void const, scGRID, unsigned int) ()
#2 0x00000000006b83f2 in qmnSCAN::readRow(qcTemplate, qmncSCAN, qmndSCAN, int) ()
#3 0x00000000006b851b in qmnSCAN::doItNext(qcTemplate, qmnPlan, int*) ()
#4 0x00000000006b710b in qmnSCAN::doIt(qcTemplate, qmnPlan, int*) ()
#5 0x0000000000887a1a in qmnJOIN::doItRight(qcTemplate, qmnPlan, int*) ()
#6 0x0000000000887c3f in qmnJOIN::doIt(qcTemplate, qmnPlan, int*) ()
#7 0x0000000000881b14 in qmnHSDS::doItDependent(qcTemplate, qmnPlan, int*) ()
#8 0x0000000000881c4f in qmnHSDS::doIt(qcTemplate, qmnPlan, int*) ()
#9 0x00000000006bb2a4 in qmnPROJ::doIt(qcTemplate, qmnPlan, int*) ()
#10 0x00000000004d27cf in qci::moveNextRecord(qciStatement, smiStatement, idBool*) ()
#11 0x000000000043f682 in mmtServiceThread::fetch(cmiProtocolContext, mmcSession, mmcStatement*, unsigned short, unsigned short, unsigned short, unsigned int) ()
#12 0x00000000004402ad in mmtServiceThread::fetchProtocol(cmiProtocolContext, cmpProtocol, void, void) ()
#13 0x0000000000f4b477 in cmiRecv(cmiProtocolContext, void, PDL_Time_Value, void) ()
#14 0x000000000042a7ef in mmtServiceThread::executeTask_READY(mmcTask, mmcTaskState) ()
#15 0x000000000042c355 in mmtServiceThread::executeTask() ()
#16 0x000000000042c922 in mmtServiceThread::multiplexingAsShared() ()
#17 0x000000000042cfb7 in mmtServiceThread::run() ()
#18 0x0000000000f80da2 in idtContainer::staticRunner(void*) ()
#19 0x00007f8d2f796df3 in start_thread () from /lib64/libpthread.so.0
#20 0x00007f8d2e8663dd in clone () from /lib64/libc.so.6
pstack prints a stack for each thread. Split the output by thread, and read each thread's stack from the bottom frame up. Reading Thread 44 (LWP 7165) in the example above from the bottom up gives the following.
| Frame | Function | Meaning |
|---|---|---|
| #20 ~ #17 | clonestart_threadidtContainer::staticRunnermmtServiceThread::run |
Creates and starts the service thread. |
| #16 ~ #14 | mmtServiceThread::multiplexingAsSharedmmtServiceThread::executeTaskmmtServiceThread::executeTask_READY |
The service thread runs a task of the session it handles. |
| #13 | cmiRecv |
Receives the request sent by the client. |
| #12 ~ #10 | mmtServiceThread::fetchProtocolmmtServiceThread::fetchqci::moveNextRecord |
Handles the client's fetch request and gets the next record. |
| #9 ~ #4 | qmnPROJ::doItqmnHSDS::doItqmnJOIN::doItqmnSCAN::doIt |
Executes the query. |
| #3 ~ #0 | qmnSCAN::readRowsmiTableCursor::readRowsmnnSeq::fetchNext |
Reads records by sequentially scanning (full scan) the table. |
In other words, Thread 44 is processing a fetch of a SELECT statement, and is sequentially scanning a table. By combining the output of ps and pstack in this way, you can find out what the thread using the most CPU is doing, and narrow down the SQL statement that causes the problem among many statements.
Checking the List of Files in Use#
If a separate utility such as lsof is not installed, use the /proc file system to check the files that a process is using.
$ ls -l /proc/7153/fd
lrwx------ 1 altibase altibase 64 Aug 14 18:56 44 -> socket:[130274146]
lrwx------ 1 altibase altibase 64 Aug 14 18:56 46 -> /home/altibase/altibase_home/logs/logfile21
lrwx------ 1 altibase altibase 64 Aug 14 18:56 47 -> /home/altibase/altibase_home/logs/logfile22
lrwx------ 1 altibase altibase 64 Aug 14 18:56 48 -> /home/altibase/altibase_home/logs/logfile23
lrwx------ 1 altibase altibase 64 Aug 14 18:56 49 -> /home/altibase/altibase_home/logs/logfile24
Linux provides various information about a process under the /proc/<pid> directory, so refer to it as needed.
System Log#
Check the files in the /var/log/ directory. Usually, check the messages file.
AIX#
Some commands may not be supported on versions earlier than AIX 5.1.
ps#
Use the ps command with the -m option to check information by thread.
$ ps -mo THREAD -p 1802540
USER PID PPID TID S CP PRI SC WCHAN F TT BND COMMAND
altibase 1802540 1 - A 0 60 40 * 40001 - - /home/altibase/altibase_home/bin/altibase -p boot
- - - 860211 S 0 60 1 - 418400 - - -
- - - 1409101 S 0 60 1 - 410400 - - -
- - - 1462489 S 0 60 1 - 410400 - 12 -
- - - 1482935 S 0 60 1 - 410400 - 8 -
TID is the unique number of the thread, and the CP column is the CPU usage of the thread.
procstack#
Use procstack to get the same kind of output as pstack on Linux.
$ procstack 1802540
---------- tid# 6901809 (pthread ID: 258) ----------
0x0900000000062a14 write(??, ??, ??) + 0x1c8
0x00000001000b9a60 cmnSockSend(cmbBlock,cmnLinkPeer,int,PDL_Time_Value*,idvStatIndex)() + 0x308
0x00000001000b8d38 cmnLinkPeerSendTCP(cmnLinkPeer,cmbBlock)() + 0x30
0x000000010007f28c cmiWriteBlock(cmiProtocolContext*,idBool)() + 0x24c
0x000000010007ccc0 cmiFlushProtocol(cmiProtocolContext*,idBool)() + 0xa8
0x00000001000cd838 mmtServiceThread::executeTask()() + 0xc1c
0x00000001000cbb80 mmtServiceThread::multiplexingAsShared()() + 0x84
0x00000001000cc594 mmtServiceThread::run()() + 0x4c4
0x0000000100077bd4 idtBaseThread::staticRunner(void*)() + 0x28
0x09000000004a44f4 _pthread_body(??) + 0xdc
Interpret the output in the same way as pstack: split it by thread based on tid#, and read each stack from the bottom up. The example above shows a service thread sending the result of a query to the client after executing it.
procfiles#
Use procfiles to check the list of files that a process has open.
$ procfiles -n 1802540
1802540 : /home/altibase/altibase_home/bin/altibase -p boot from admin
Current rlimit: 100 file descriptors
0: S_IFREG mode:0200 dev:53,1 ino:2731329 uid:222 gid:1 rdev:0,0 O_WRONLY | O_APPEND size:451248 name:/home/altibase/altibase_home/trc/altibase_boot.log
1: S_IFREG mode:0222 dev:53,1 ino:2731337 uid:222 gid:1 rdev:0,0 O_WRONLY | O_APPEND size:3014040 name:/home/altibase/altibase_home/trc/altibase_sm.log
Specify the -n option to see the file names as well.
System Log#
Use errpt to check whether an error has occurred on the system. The system log records disk device errors, network device errors, abnormal process terminations, and so on, so always check it when you need to look for the cause of a failure on the system side.
$ errpt -a | more
---------------------------------------------------------------------------
LABEL: CORE_DUMP
IDENTIFIER: C69F5C9B
Date/Time: Thu Feb 25 03:59:12 KORST 2010
Sequence Number: 23893
Machine Id: 00C76BFD4C00
Node Id: aix53-p5
Class: S
Type: PERM
Resource Name: SYSPROC
Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED
Probable Causes
SOFTWARE PROGRAM
Solaris#
The commands in this section are based on Solaris 10 (SunOS 5.10).
prstat#
Altibase runs as a multithreaded process, so you sometimes need to find out which threads are using a lot of CPU. In that case, use the -L option of prstat to check CPU usage by thread (LWP).
$ prstat -L -p 22951 1 # view process 22951 at 1-second intervals
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/LWPID
22951 altibase 502M 106M sleep 59 0 0:22:50 1.7% altibase/5
22951 altibase 502M 106M sleep 59 0 0:23:49 0.3% altibase/4
22951 altibase 502M 106M sleep 59 0 0:00:10 0.1% altibase/12
22951 altibase 502M 106M sleep 59 0 0:00:05 0.1% altibase/47
22951 altibase 502M 106M sleep 59 0 0:25:28 0.0% altibase/6
22951 altibase 502M 106M sleep 59 0 0:22:19 0.0% altibase/9
22951 altibase 502M 106M sleep 59 0 0:23:13 0.0% altibase/8
pstack#
After finding the threads that use the most CPU with prstat, use pstack to check what each of those threads is currently doing.
$ pstack -F 22951 | c++filt
----------------- lwp# 7 / thread# 7 --------------------
ffffffff7e1d9ce8 pollsys (ffffffff7b4ffa50, 0, ffffffff7b4ffb10, 0)
ffffffff7e173c44 pselect (0, ffffffff7b4ffa50, ffffffff7e344710, ffffffff7e344710, ffffffff7b4ffb10, 0) + 1f0
ffffffff7e173fe8 select (0, 106eea070, 0, 0, ffffffff7b4ffbd8, fffc00) + a0
00000001006f6df4 IDE_RC cmnDispatcherSelectSOCK(cmnDispatcher,iduList,unsigned,PDL_Time_Value) (106eea040, ffffffff7b4ffd88, 0, 106cc5818, ffffffff, 106eea070) + 50
00000001006f089c IDE_RC cmiSelectDispatcher(cmnDispatcher,iduList,unsigned,PDL_Time_Value) (106eea040, ffffffff7b4ffd88, 0, 106cc5818, ffffffffffffffff, 1006f6da4) + 1c
0000000100140790 void mmtServiceThread::findReadyTask() (106cc56d8, 105966f18, 101000, 0, 2710, 0) + 14
000000010013f878 void mmtServiceThread::run() (106cc56d8, 1, ffffffffffffffff, 2ec6bb9, 101329000, 10132b000) + 560
0000000100717034 voididtBaseThread::staticRunner(void) (106cc56d8, 100000, 0, 0, 100a120c8, 10013f318) + 14
ffffffff7e1d609c _lwp_start (0, 0, 0, 0, 0, 0)
c++filt restores C++ function names that the compiler has transformed (mangled) to their original form. Without c++filt, function names are printed in a form that is hard to read, so use it whenever possible. c++filt is usually located in the directory of the compiler executables (for example, /opt/SUNWspro/bin/). Interpret the output in the same way as pstack on Linux: split it by thread based on lwp#, and read each stack from the bottom up.
pfiles#
Use pfiles to check the list of files that a process has open.
$ pfiles -F 22951
22951: /home2/altibase/work/altibase_home/bin/altibase -p boot from admin
Current rlimit: 65535 file descriptors
0: S_IFREG mode:0644 dev:118,38 ino:3309572 uid:124 gid:1 size:202128
O_WRONLY|O_APPEND|O_CREAT|O_LARGEFILE
/home2/altibase/work/altibase_home/trc/altibase_boot.log
1: S_IFREG mode:0644 dev:118,38 ino:3309573 uid:124 gid:1 size:1413256
O_WRONLY|O_APPEND|O_CREAT|O_LARGEFILE
/home2/altibase/work/altibase_home/trc/altibase_sm.log
pfiles shows the file descriptor limit (Current rlimit) and all files that the process is using, in file descriptor order.
If you run pfiles while several sessions are connected, it shows not only data files, trace log files, and transaction log files but also the list of network connections (sockets).
321: S_IFSOCK mode:0666 dev:329,0 ino:63732 uid:0 gid:0 size:0
O_RDWR|O_NONBLOCK
SOCK_STREAM
SO_REUSEADDR,SO_KEEPALIVE,SO_SNDBUF(65536),SO_RCVBUF(32788),IP_NEXTHOP(0.0.128.20)
sockname: AF_INET 127.0.0.1 port: 27584
peername: AF_INET 127.0.0.1 port: 42567
System Log#
When you need to look for the cause of a failure on the system side, check the /var/adm/messages.* files. Solaris writes the log of the current week to the messages file, and keeps older logs by week in files such as messages.0 and messages.1.
$ vi /var/adm/messages
Feb 24 18:08:24 v880 Corrupt label; wrong magic number
Feb 24 18:08:24 v880 scsi: [ID 107833 kern.warning] WARNING: /pci@9,700000/fibre-channel@4/fp@0,0/ssd@w210000d023041a42,7 (ssd13):
Feb 24 18:08:24 v880 Corrupt label; wrong magic number
Feb 24 18:08:24 v880 scsi: [ID 107833 kern.warning] WARNING: /pci@9,700000/fibre-channel@4/fp@0,0/ssd@w210000d023041a42,7 (ssd13):
Feb 24 18:08:24 v880 Corrupt label; wrong magic number
System logs are hard to understand precisely unless you are a vendor expert. Even so, if there is a meaningful log at the time of a failure, be sure to check it.
HP-UX#
HP-UX systems are divided into PA-RISC and Itanium by CPU type, and some commands may not be supported on PA-RISC systems.
CPU Usage by Thread with glance#
On HP-UX, use the glance monitoring tool to check CPU usage by thread. After starting glance, press the s key and enter the ID of the target process, then press the G key to see the CPU usage of each thread of that process.
pstack#
Use pstack to get the same kind of output as pstack on Solaris.
$ pstack 3486011
-------------------------------- lwpid : 3486042 -------------------------------
0: c000000000446910 : (unknown) () (unknown)
1: c0000000001a75a0 : (unknown) () (unknown)
2: c0000000000e1130 : (unknown) () (unknown)
3: c0000000000e40c0 : (unknown) () (unknown)
4: 4000000001330fd0 : rpxSender::sleepForNextConnect()() + 0x3b0 (/home/ckh0618/altibase_home/bin/altibase)
5: 4000000001340cc0 : rpxSender::attemptHandshake(idBool*)() + 0x4c0 (/home/ckh0618/altibase_home/bin/altibase)
6: 4000000001326f80 : rpxSender::run()() + 0x1a0 (/home/ckh0618/altibase_home/bin/altibase)
7: 4000000001e108a0 : idtBaseThread::staticRunner(void*)() + 0x60 (/home/ckh0618/altibase_home/bin/altibase)
8: c0000000000fa220 : (unknown) () (unknown)
lwpid is the unique number of the thread. As on other operating systems, split the output by thread based on lwpid, and read each stack from the bottom up. In the example above, the replication sender thread is trying to connect to the remote server.
pfiles#
Use pfiles to get the same kind of output as pfiles on Solaris.
$ pfiles 3486011
0: S_ISREG mode:666 dev:64,65537 ino:8490324 uid:124 gid:20 size:530024 flags = O_WRONLY|O_APPEND|O_LARGEFILE file = /home/ckh0618/altibase_home/trc/altibase_boot.log
1: S_ISREG mode:666 dev:64,65537 ino:8490373 uid:124 gid:20 size:8466361 flags = O_WRONLY|O_APPEND|O_LARGEFILE file = /home/ckh0618/altibase_home/trc/altibase_sm.log
System Log#
On HP-UX, check the system log in the /var/adm/syslog/syslog.log file.
$ vi /var/adm/syslog/syslog.log
Feb 24 10:32:07 rx5670 vmunix: System Console is on the Built-In Serial Interface
Feb 24 10:32:07 rx5670 vmunix: igelan0: INITIALIZING HP A6794-60001 PCI 1000Base-T at hardware path 0/1/1/0/4/0
Feb 24 10:32:07 rx5670 vmunix: Logical volume 64, 0x3 configured as ROOT
Feb 24 10:32:07 rx5670 vmunix: Logical volume 64, 0x2 configured as SWAP
Feb 24 10:32:07 rx5670 vmunix: Logical volume 64, 0x2 configured as DUM