Month: December 2018

As a DBA, sometime we need know how the disk IO performs  in Linux when database hits performance issue. Particularity SQLs have high IO waitings.

1)  Discard caches before testing :

# sync; echo 3 >  /proc/sys/vm/drop_caches

The sync command allows the kernel write as many dirty cache pages to disk as it can.
To clear PageCache only run: # sync; echo 1 > /proc/sys/vm/drop_caches
To clear dentries (Also called as Directory Cache) and inodes run:  # sync; echo 2 > /proc/sys/vm/drop_caches

Page cache is memory held after reading files. Linux kernel prefers to keep unused page cache assuming files being read once will most likely to be read again in the near future, hence avoiding the performance impact on disk IO.

dentry and inode_cache are memory held after reading directory/file attributes, such as open() and stat(). dentry is common across all file systems, but inode_cache is on a per-file-system basis. Linux kernel prefers to keep this information assuming it will be needed again in the near future, hence avoiding disk IO.

2) To measure server throughput (write speed) :

$dd if=/dev/zero of=/tmp/test1.dmp bs=1024k count=1024 oflag=dsync

1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 3.87519 s, 277 MB/s

3) To measure server latency ( read speed ):

$ dd of=/dev/zero if=/tmp/test1.dmp bs=1024k count=1024 oflag=dsync

• oflag=dsync (oflag=dsync) : Use synchronised I/O for data. Do not skip this option. This option get rid of caching and gives you good and accurate results.
• conv=fdatasyn: Again, this tells dd to require a complete “sync” once, right before it exits. This option is equivalent to oflag=dsync.

4) File checksum:

-- Md5sum

$ time /usr/bin/md5sum /tmp/test1.dmp
cd573cfaace07e7949bc0c46028904ff /tmp/test1.dmp

real 0m1.861s
user 0m1.609s
sys 0m0.243s

-- Sha1 

$ time sha1sum  /tmp/test1.dmp
2a492f15396a6768bcbca016993f4b4c8b0b5307  /tmp/test1.dmp

real    0m1.296s
user    0m1.050s
sys     0m0.241s

-- Sha256

$ time sha256sum  /tmp/test1.dmp
49bc20df15e412a64472421e13fe86ff1c5165e18b2afccf160d4dc19fe68a14 
/tmp/test1.dmp

real    0m2.520s
user    0m2.244s
sys     0m0.258s