High CPU usage on DB host?

Its Unix 101 statement, but I have heard it wrong so many time, so decided to put a blog for it.

So:
If in OLTP environment, per ‘top’ %wa (IOWAIT) is major contributor for CPU busy, adding CPU would not help. There is no need for more CPU. !Period!
%wa IS PERCENTAGE WHAT COUNT TO CPU IDLE ******************

Simple test.

Push some IO:

user1@myhost:~$ dd if=/dev/zero of=/tmp/file1 conv=notrunc  bs=1000 count=3000000 &
[1] 31240
user1@myhost:~$ dd if=/dev/zero of=/tmp/file2 conv=notrunc  bs=1000 count=3000000 &
[2] 31241
user1@myhost:~$ dd if=/dev/zero of=/tmp/file3 conv=notrunc  bs=1000 count=3000000 &
[3] 31242
user1@myhost:~$ dd if=/dev/zero of=/tmp/file4 conv=notrunc  bs=1000 count=3000000 &
[4] 31243

top looks:

user1@myhost:~$ top -b -i

top - 23:05:42 up  8:37, 12 users,  load average: 4.36, 3.91, 6.28
Tasks: 239 total,   5 running, 230 sleeping,   0 stopped,   4 zombie
Cpu(s):  3.1%us, 20.5%sy,  0.0%ni, 12.9%id, 63.3%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   4080460k total,  3809420k used,   271040k free,     1580k buffers
Swap:  4145148k total,   104240k used,  4040908k free,  1824928k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
31240 amoseyev  20   0  4376  588  496 D    2  0.0   0:12.29 dd
31241 amoseyev  20   0  4376  588  500 D    2  0.0   0:12.32 dd
31242 amoseyev  20   0  4376  592  500 D    2  0.0   0:12.38 dd
31243 amoseyev  20   0  4376  592  500 D    1  0.0   0:11.50 dd

%wa is high.

iostat consistently giving write performance about 44MB/sec:

user1@myhost:~$ iostat 1 1000

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.76    0.00   12.09   50.13    0.00   36.02

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda            2111.00      8352.00     45668.00       8352      45668

As shown above, dd process what causes I/O load almost always in “D” state, what is “uninterruptible sleep”.
“uninterruptible” comes from fact what they cant be killed, as process is in kernel mode (IO call has to be done in kernel mode).
Its uninterruptible but still SLEEP. Its idle process. It does not block CPU. If any other thread would require CPU (either for number cranching or for another I/O call), schedule would put it on CPU while dd is in sleep.
But if no other CPU load is available, top counts next idle CPU cycle as %wa.

Now push some real CPU load:

user1@myhost:~$ cat /dev/urandom  > /dev/null &
[1] 31224
user1@myhost:~$ cat /dev/urandom  > /dev/null &
[2] 31225
user1@myhost:~$ cat /dev/urandom  > /dev/null &
[3] 31229
user1@myhost:~$ cat /dev/urandom  > /dev/null &
[4] 31231

user1@myhost:~$
user1@myhost:~$ top -b -i

top - 23:19:16 up  8:50, 12 users,  load average: 7.84, 7.15, 7.10
Tasks: 239 total,   6 running, 229 sleeping,   0 stopped,   4 zombie
Cpu(s):  0.8%us, 98.1%sy,  0.0%ni,  0.0%id,  0.5%wa,  0.0%hi,  0.6%si,  0.0%st
Mem:   4080460k total,  3838860k used,   241600k free,     2168k buffers
Swap:  4145148k total,   104240k used,  4040908k free,  2264144k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
31279 amoseyev  20   0  4220  544  456 R   96  0.0   0:16.22 cat
31281 amoseyev  20   0  4220  544  456 R   87  0.0   0:13.96 cat
31280 amoseyev  20   0  4220  544  456 R   83  0.0   0:15.20 cat
31278 amoseyev  20   0  4220  540  456 R   80  0.0   0:16.01 cat
31241 amoseyev  20   0  4376  588  500 D    2  0.0   0:14.29 dd
31242 amoseyev  20   0  4376  592  500 D    2  0.0   0:14.26 dd
31240 amoseyev  20   0  4376  588  496 D    1  0.0   0:14.17 dd
31243 amoseyev  20   0  4376  592  500 D    1  0.0   0:13.34 dd

%wa went to almost 0. %sy is close to 100%.
So when CPU spends its cycles on real load, it does not addup to %wa. And same time, I/O throughput did not change with CPU been 100% busy:

user1@myhost:~$ iostat sda 1 1000

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.50    0.00   98.50    0.00    0.00    0.00

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda            1415.00      5376.00     44500.00       5376      44500

What again proves what OLTP I/O does not need that much CPU.

The “D” process also add up to “load avarage”, so its its also not the best value to judge how busy CPU is.

All said above would be applicable for for OLTP, A lot of small I/Os. NAS, SAN. RAW/block devices, or file system. All of them.

If we are talking about some crazy 1+GB/sec full table scans in OLAP/dw world, CPU probably would be affected, especially if its NFS (and not direct NFS). But it will be mostly on %sy and %si (not %wa), as ethernet traffic would be handled thru soft interrupts, and with high throughput, its CPU intensive. Context switches may also add up to CPU on some platforms, where they still used for switching between user/kernel modes.

Check us out on http://avmconsulting.net

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements
This entry was posted in Linux, Oracle, Systems, Uncategorized and tagged , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s