Oracle Orion on Windows

A IOPS benchmarking tool for Oracle database. It is not only measuring the IOs but simulating as well.

Create a text file call mytest.lun then insert the datafile you would like to test againts.

C:\APP\ADMIN\ORADATA\CS\VCLOUD.DBF


Then run the the command from DOS. Make sure you run it as Administrator that has privide to write CVS file to the folder.

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Windows\system32>cd C:\vpoc

C:\vpoc>C:\app\ADMIN\product\11.2.0\dbhome_2\BIN\orion -run simple -testname mytest -num_disks 1


ORION: ORacle IO Numbers -- Version 11.2.0.1.0
mytest_20141230_1713
Calibration will take approximately 9 minutes.
Using a large value for -cache_size may take longer.

The Result. All the CVS got created in this folder as well.

Example of mytest


ORION VERSION 11.2.0.1.0

Command line:
-run simple -testname mytest -num_disks 1 

These options enable these settings:
Test: mytest
Small IO size: 8 KB
Large IO size: 1024 KB
IO types: small random IOs, large random IOs
Sequential stream pattern: one LUN per stream 
Writes: 0%
Cache size: not specified
Duration for each data point: 60 seconds
Small Columns:,      0
Large Columns:,      0,      1,      2
Total Data Points: 8

Name: C:\APP\ADMIN\ORADATA\\CS\VCLOUD.DBF Size: 3202228224
1 files found.

Maximum Large MBPS=49.10 @ Small=0 and Large=2
Maximum Small IOPS=156 @ Small=5 and Large=0
Minimum Small Latency=10009.27 usecs @ Small=1 and Large=0

Installing ioping

Sometimes, I am using this tool as a real time IOPS monitoring tool. It is a lightweight version of Orion from the database perspective.

Download ioping. 

https://code.google.com/p/ioping/downloads/list

Logon as root and download the ioping tool

[root@Cloud2 ioping]# wget https://ioping.googlecode.com/files/ioping-0.8.tar.gz
--2014-12-30 16:05:17--  https://ioping.googlecode.com/files/ioping-0.8.tar.gz
Resolving ioping.googlecode.com... 74.125.25.82, 2607:f8b0:400e:c03::52
Connecting to ioping.googlecode.com|74.125.25.82|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 10590 (10K) [application/x-gzip]
Saving to: `ioping-0.8.tar.gz'

100%[================================================================================>] 10,590      62.2K/s   in 0.2s

2014-12-30 16:05:18 (62.2 KB/s) - `ioping-0.8.tar.gz' saved [10590/10590]

Extract the tool

[root@Cloud2 ioping]# tar -zxvf ioping-0.8.tar.gz

ioping-0.8/version

ioping-0.8/ioping.c

ioping-0.8/ioping.1

ioping-0.8/README

ioping-0.8/changelog

ioping-0.8/ioping.spec

ioping-0.8/Makefile

[root@Cloud2 ioping]# ls -lrt

total 24

-rw-r--r-- 1 root root 10590 Dec 30  2013 ioping-0.8.tar.gz

drwxr-xr-x 2 root root  4096 Dec 30 16:06 ioping-0.8

[root@Cloud2 ioping]# cd ioping-0.8

[root@Cloud2 ioping-0.8]# ls -lrt

total 80

-rw-r--r-- 1 1000 1000  1232 Jan 25  2013 ioping.spec

-rw-r--r-- 1 1000 1000   421 Dec 29  2013 README

-rw-r--r-- 1 1000 1000   477 Dec 29  2013 changelog

-rw-r--r-- 1 1000 1000  4331 Dec 29  2013 ioping.1

-rw-r--r-- 1 1000 1000 23033 Dec 29  2013 ioping.c

-rw-r--r-- 1 1000 1000  1493 Dec 29  2013 Makefile

-rw-r--r-- 1 1000 1000     4 Dec 30  2013 version

Build the tool

[root@Cloud2 ioping-0.8]# make ioping
cc -std=gnu99 -g -Wall -Wextra -pedantic -DVERSION=\"0.8\" -c -o ioping.o ioping.c
cc -o ioping ioping.o -std=gnu99 -g -Wall -Wextra -pedantic  -lm
[root@Cloud2 ioping-0.8]# ls -las
total 192
 8 drwxr-xr-x 2 root root  4096 Dec 30 16:09 .
 8 drwxr-xr-x 3 root root  4096 Dec 30 16:06 ..
 8 -rw-r--r-- 1 1000 1000   477 Dec 29  2013 changelog
44 -rwxr-xr-x 1 root root 39311 Dec 30 16:09 ioping
12 -rw-r--r-- 1 1000 1000  4331 Dec 29  2013 ioping.1
28 -rw-r--r-- 1 1000 1000 23033 Dec 29  2013 ioping.c
52 -rw-r--r-- 1 root root 47200 Dec 30 16:09 ioping.o
 8 -rw-r--r-- 1 1000 1000  1232 Jan 25  2013 ioping.spec
 8 -rw-r--r-- 1 1000 1000  1493 Dec 29  2013 Makefile
 8 -rw-r--r-- 1 1000 1000   421 Dec 29  2013 README
 8 -rw-r--r-- 1 1000 1000     4 Dec 30  2013 version
[root@Cloud2 ioping-0.8]#

Run the tool

[root@Cloud2 ioping-0.8]# ./ioping -c 10 -s 1M /tmp

1.0 MiB from /tmp (ext3 /dev/root): request=1 time=53.7 ms

1.0 MiB from /tmp (ext3 /dev/root): request=2 time=68.7 ms

1.0 MiB from /tmp (ext3 /dev/root): request=3 time=53.7 ms

1.0 MiB from /tmp (ext3 /dev/root): request=4 time=61.5 ms

1.0 MiB from /tmp (ext3 /dev/root): request=5 time=53.2 ms

1.0 MiB from /tmp (ext3 /dev/root): request=6 time=57.3 ms

1.0 MiB from /tmp (ext3 /dev/root): request=7 time=54.4 ms

Read the manual and see what's interest you.

https://code.google.com/p/ioping/wiki/man

Starting new PostgreSQL blog

Today, I decided to start a Postgres blog as I started to learn about it. I have had exposure to Postgres since a 3 years back where our company started designing Postgres as backend database for more than half of our products with 8.4 then 9.1 but I have never shown any interests on picking it up and getting serious about it. That's may be due to I am too occupied with Oracle database. In addition, the demand for Postgres job market is always on the weak side. With that, Postgres is not very appealing to me. I hardly seen any companies hiring a Fulltime Postgres DBA. They always comes in the skills that good to have but not the primary skill set that companies are looking for. Market share wise, Postgres ranked 4th after Oracle, MSSQL and MySQL based on this article http://db-engines.com/en/ranking . From my experience, the market share in the link is about right.

VMware Snapshot causing database connection to error out "Connection reset by peer"

Every time my customer performs VMware Snapshot on VMs connecting to the Oracle database in a Linux physical box, it triggers Oracle database connection resets. After a few weeks of running RDA, OSWatcher, listener.log and etc, Customer administrator found out that someone set the net_ipv4.tcp_retries2 parameter to a very low value. Default value is 15 which is roughly 13 - 30 minutes of timeout. In this case, someone setting the tcp_retries2 to 3, that translates into 3-5 minutes before connection is reset and if VMware Snapshot taking longer than that period of time due to "stun", it would reset the connection to the database.

There is absolutely no reason to set the tcp_retries2 to a really low number.

Installing TimeTen database as a test.

As the title says, I am installing Oracle TimesTen database on top of my Oracle 11G database.

Download

http://www.oracle.com/technetwork/database/database-technologies/timesten/downloads/index.html

Extract and Install

[oracle@Aigamenate downloads]$ tar -xvf timesten112260.linux8664
linux8664/
linux8664/uninst.sh
linux8664/install.pl
linux8664/README.html
linux8664/LINUX8664/
linux8664/LINUX8664/ttpatchinst
linux8664/LINUX8664/unzip
linux8664/LINUX8664/common.tar.bz2
linux8664/LINUX8664/ttclient.tar.bz2
linux8664/LINUX8664/perl
linux8664/LINUX8664/manifest
linux8664/LINUX8664/timesten.tar.bz2
linux8664/LINUX8664/bzip2
linux8664/LINUX8664/ttserver.tar.bz2
linux8664/3rdparty/
linux8664/3rdparty/ant-1.6.2-bin.tar.bz2
linux8664/3rdparty/jms-1_1-fr-apidocs.tar.bz2
linux8664/doc/
linux8664/doc/doc.zip
linux8664/setup.sh
[oracle@Aigamenate downloads]$

[oracle@Aigamenate downloads]$ cd linux8664
[oracle@Aigamenate linux8664]$ pwd
/home/oracle/downloads/linux8664

[oracle@Aigamenate linux8664]$ ls -lrt
total 464
-rwxr-xr-x 1 oracle oracle  34886 Nov 21 20:24 uninst.sh
-rwxr-xr-x 1 oracle oracle   5623 Nov 21 20:24 setup.sh
-rwxr--r-- 1 oracle oracle 128225 Nov 21 20:24 README.html
-rwxr-xr-x 1 oracle oracle 248855 Nov 21 20:24 install.pl
drwxrwxr-x 2 oracle oracle   4096 Nov 21 20:26 doc
drwxr-xr-x 2 oracle oracle   4096 Nov 21 20:26 3rdparty
drwxr-xr-x 2 oracle oracle   4096 Nov 21 20:26 LINUX8664

[oracle@Aigamenate linux8664]$ ./setup.sh

ERROR: The /etc/TimesTen directory needs to be created for the instance registry
       and its ownership and permissions set appropriately.
       Please refer to the installation guide for assistance.

http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21632/install.htm [oracle@Aigamenate linux8664]$ su - Password: [root@Aigamenate ~]# groupadd ttadmin [root@Aigamenate ~]# groupadd ttadmin groupadd: group ttadmin exists [root@Aigamenate ~]# mkdir /etc/TimesTen [root@Aigamenate ~]# chgrp -R ttadmin /etc/TimesTen [root@Aigamenate ~]# chmod 770 /etc/TimesTen/ [root@Aigamenate ~]# chmod 660 /etc/TimesTen/* chmod: cannot access `/etc/TimesTen/*': No such file or directory

Better to just install as root. I am not sure if I suppose to install TimesTen with root. You should read the documentation. I was doing this as a quick validation test.

[root@Aigamenate linux8664]# ./setup.sh

WARNING: You are about to install TimesTen as root. TimesTen daemon processes will
         run with root privileges. See the TimesTen Installation Guide for additional
         information.

Are you sure that you want to install as root? [ no ] yes

Are you sure that you want to install as root? [ no ] yes

NOTE: Each TimesTen installation is identified by a unique instance name.
      The instance name must be a non-null alphanumeric string, not longer
      than 255 characters.

Please choose an instance name for this installation? [ tt1122 ]
Instance name will be 'tt1122'.
Is this correct? [ yes ]

Of the three components:

  [1] Client/Server and Data Manager
  [2] Data Manager Only
  [3] Client Only

Which would you like to install? [ 1 ]

Of the following options :

  [1] /opt
  [2] /home/oracle/downloads
  [3] Specify a location
  [q] Quit the installation

Where would you like to install the tt1122 instance of TimesTen? [ 1 ]

The daemon logs will be located in /opt/TimesTen/tt1122/info
Would you like to specify a different location for the daemon logs? [ no ]
Installing into /opt/TimesTen/tt1122 ...
Creating /opt/TimesTen/tt1122 ...
Uncompressing ...


NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
      daemon port number must be the same across all TimesTen installations
      managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
      port number that is set at installation time. The daemon port number can
      be verified by running 'ttVersion'.

The default port number is 53396.

Do you want to use the default port number for the TimesTen daemon? [ yes ]


The daemon will run on the default port number (53396).

NOTE: For security, we recommend that you restrict access to the
      TimesTen installation to members of a single OS group. Only members of
      that OS group will be allowed to perform direct mode connections to
      TimesTen, and only members of that OS group will be allowed to perform
      operations that access TimesTen data stores, TimesTen files and shared
      memory. The OS group defaults to the primary group of the instance
      administrator. You can default to this group, choose another OS group
      or you can make this instance world-accessible. If you choose to make
      this instance world-accessible, all database files and shared memory
      are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'root'? [ yes ]

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ]

In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using <install_dir>/bin/ttModInstall.



Please enter a value for TNS_ADMIN (s=skip)? [  ] /home/oracle/app/oracle/product/11.2.0/ora11204/network/admin/

TNS_ADMIN will be set to /home/oracle/app/oracle/product/11.2.0/ora11204/network/admin
You can change TNS_ADMIN later by running <install_dir>/bin/ttmodinstall.



NOTE: It appears that you are running version 4 or higher of the g++
      compiler. TimesTen ships with multiple sets of client libraries and server
      binaries : one built for compatibility with g++ 3.4.6 and one with
      g++ 4.1.0. The installer has created links to the 4.1.0 library in the
      <install_dir>/lib directory and to the 4.1.0 server binary in the
      <install_dir>/bin directory. If you want to use a different compiler,
      please modify the links to point to the desired library and server binary.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53397 ]

Do you want to install QuickStart and the TimesTen Documentation? [ no ]
Would you like to install the documentation (without QuickStart)? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /opt/TimesTen/tt1122/doc ]
The TimesTen documentation has been installed in /opt/TimesTen/tt1122/doc.
Installing client components ...

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]
The 11.2.2.6 Release Notes are located here :
  '/opt/TimesTen/tt1122/README.html'

Starting TimesTen Daemon : [  OK  ]
End of TimesTen installation.
[root@Aigamenate linux8664]#

[root@Aigamenate linux8664]# ps -ef|grep Times
root     24321     1  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/timestend -initfd 13
root     24325 24321  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/timestensubd -verbose -userlog /opt/TimesTen/tt1122/info/tterrors.log -supportlog /opt/TimesTen/tt1122/info/ttmesg.log -id 1000000 -facility user
root     24326 24321  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/timestensubd -verbose -userlog /opt/TimesTen/tt1122/info/tterrors.log -supportlog /opt/TimesTen/tt1122/info/ttmesg.log -id 1000001 -facility user
root     24327 24321  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/timestensubd -verbose -userlog /opt/TimesTen/tt1122/info/tterrors.log -supportlog /opt/TimesTen/tt1122/info/ttmesg.log -id 1000002 -facility user
root     24328 24321  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/timestensubd -verbose -userlog /opt/TimesTen/tt1122/info/tterrors.log -supportlog /opt/TimesTen/tt1122/info/ttmesg.log -id 1000003 -facility user
root     24330 24321  0 10:08 ?        00:00:00 /opt/TimesTen/tt1122/bin/ttcserver -verbose -userlog /opt/TimesTen/tt1122/info/tterrors.log -supportlog /opt/TimesTen/tt1122/info/ttmesg.log -id 1000004 -p 53397 -facility user -group root
root     24412 20792  0 10:10 pts/1    00:00:00 grep Times

The installation itself was pretty easy aside from not quite sure if I should install this with root or grand a user to root.