Category: Miscellaneous

Load Testing Oracle Stored Procedures Using Apache JMeter

Last week I was asked to load test few oracle stored procedures created by another team. These stored procedures are invoked by java services and the delivery team wanted to test them in the performance environment before pushing them to production. I was asked to use an Open Source tool for this testing since the project doesn’t have budget to buy a commercial tool.

Since Apache JMeter is a free tool, we decided to use it for our testing. The stored procedure takes one INTEGER value as input and returns an INTEGER value and a cursor as out put. The input value came from a CSV file which has 100K records that we mined from the database. I have detailed the steps we used to create a database test plan, our biggest challenge was to receive cursor output from the stored procedure. Details on how we achieved this can be found below. I have also explained how you can download the necessary tools and configure them.

**Please note that I have simulated below tests in my laptop since the actual code belongs to my client. I have no authority to publish the actual code here, but the configurations used in both cases are same. **

First, Lets download the latest version of Apache JMeter.

http://jmeter.apache.org/download_jmeter.cgi

jmeter_download_page

JMeter needs a JDBC driver to connect to oracle database, You can find the JDBC connect for Oracle at the Oracle Technology Network (OTN) website.

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

jdbc_driver_download

First, download JMeter and unzip to the folder where you want to save it. Go to /bin folder and you can find windows batch executable (jmeter.bat), double-click on it and it will start the JMeter GUI. If you are in Linux there is a jmeter.sh script in the bin directory which invokes jmeter UI.

Now, download the JDBC driver from OTN and move the .jar file to /lib folder. It will be automatically detected and used by JMeter.

You can also download a lot of JMeter plugins, which will make your testing easy and will help you present your results in beautiful graphs.

Go to the JMeter UI.

jmeter_home

I had created a stored procedures in my local oracle instance, which will be tested by JMeter. This stored procedure accepts number value as input and return a number and a cursor.

stored_proc_code

Go back to the JMeter and create a new thread group, which will monitor and control our testing.

create_thread_group

Change the thread group name, you can give whatever name you want. Here I gave “OracleLoadTest_Sproc” for my thread group. Also we can set the number parallel threads and ramp up period in this screen.

thread_grp_details

After this we have to configure our database connection. JMeter can connect to Oracle database using a JDBC driver.

Right click on thread group and add a new JDBC connection configuration.

db_connection_1

Now configure the connection settings, Here I have specified the server name as localhost since it is installed on my laptop. You can find your DB server details in TNSNAMES.ORA file or can get it from your DBA. You also have to set your database username and password for establishing the connection.

db_connection_2

Our input data is coming from a CSV file and we usually test with 100K input fields. Here I have used a small file just to make sure the setup is working.

csv_config1

You have to configure the source file name and column names here. These column names are used later as the input parameter for the stored procedure.

csv_config2

Now we have to add a constant throughput time which helps us to main a constant throughput through out our test window.

throughput_timer_1

Here I had set the timer as 60 threads/minute (1 Thread/Sec).

throughput_timer_2

Let us add the component that calls stored procedure. Add a JDBC request to the thread group and include stored procedure details in it.

sproc_jdbc_call_1

We have to set the query type as Callable Statement and use the CALL command to invoke the stored procedure. We can also pass the values from CSV file to the stored procedure. Just to make sure the type for cursor variable is-10 for the stored procedure.

sproc_jdbc_call_2

Now add few listeners which monitor the execution and summarize the output. This will help us to present the final results more clearly to the client. You can add as many listeners as you want, We are adding just two here.

summary_report

summary_report_final

resultstree

We are all set here, lets start the execution and collect the test results.

test_start

Once the test is complete, listeners provide test summary and other execution details.

summary_report_final

We can see the stored procedure invocation details (input values, output values etc.) also from the result tree screen.

sproc_output_2

sproc_output_1

One important factor is JMeter is a Java application and could use a lot of heap memory. By default JMeter 3.1 uses 512 MB of heap memory. You will definitely need more data to run tests in GUI mode. Edit the jemeter.bat using any good text editor like UltraEdit and add more memory based on your hardware configuration.

You can run JMeter from the command prompt if you have a memory constraint and save the results to a jtl file.

Advertisements

Import data from MySQL to hadoop using SQOOP

As a part of our job we import/move a lot of data from relational databases (Mainly from Oracle and MySQL) to Hadoop. Most of our data stores are in Oracle with a few internal data stores running on MySQL.

SQOOP (SQL for Hadoop) is an Apache tool to import data from relational databases (There are separate drivers for each database) to hadoop. Here in this blog we will try to import data from a MySQL table to Hadoop file system.

Here, I have a MySQL instance running on the local machine on which my Hadoop cluster also running. You will have to download and place the driver in appropriate directory for SQOOP to connect to that database. Drivers are already present in my machine as SQOOP offers a very extensive support for MySQL.

Below link will give you a list of available drivers and their locations if you are using a different database.

https://sqoop.apache.org/docs/1.4.0-incubating/SqoopUserGuide.html#id1773570

First let me login to the primary node in my 3 node cluster (Virtual/Created by Vagrant and VirtualBox).

vagrant ssh node1

Let us check the connection and data in the MySQL database.

mysql -u root -h localhost -p
Enter password: ********
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.04 sec)

use my_test;

MariaDB [my_test]> show tables;
+-------------------+
| Tables_in_my_test |
+-------------------+
| name_data |
| name_data2 |
+-------------------+
2 rows in set (0.02 sec)

Now, lets check the data.

select count(*) from name_data;

MariaDB [my_test]> select count(*) from name_data;
+----------+
| count(*) |
+----------+
| 1858689 |
+----------+

MariaDB [my_test]> select * from name_data limit 3;
+------+--------+-------+
| Name | Gender | count |
+------+--------+-------+
| Mary | F | 7065 |
| Anna | F | 2604 |
| Emma | F | 2003 |
+------+--------+-------+

Now we are sure that we have data in MySQL table, lets check our HADOOP home directory.

hadoop fs -ls /user/vagrant/

[vagrant@node1 ~]$ hadoop fs -ls /user/vagrant
Found 5 items
drwx------ - vagrant hdfs 0 2016-12-20 02:56 /user/vagrant/.Trash
drwxr-xr-x - vagrant hdfs 0 2016-10-26 04:46 /user/vagrant/.hiveJars
drwx------ - vagrant hdfs 0 2016-11-13 23:44 /user/vagrant/.staging
drwxr-xr-x - vagrant hdfs 0 2016-12-06 04:13 /user/vagrant/test_files

Now we wants to move the data from MySQL to the /users/vagrant/name_data directory. Below is th sqoop command to move import data.

[vagrant@node1 ~]$ sqoop import –connect jdbc:mysql://localhost/my_test –username root –password ******* –table name_data –m 1 –target-dir /user/vagrant/my_data

Once this command is completed, data will be present in /user/vagrant/my_data.

[vagrant@node1 ~]$ hadoop fs -ls /user/vagrant/my_data
Found 2 items
-rw-r--r-- 3 vagrant hdfs 0 2016-12-20 03:20 /user/vagrant/my_data/_SUCCESS
-rw-r--r-- 3 vagrant hdfs 22125615 2016-12-20 03:20 /user/vagrant/my_data/part-m-00000

[vagrant@node1 ~]$ hadoop fs -cat /user/vagrant/my_data/part-m-00000| wc -l
1858689
[vagrant@node1 ~]$

[vagrant@node1 ~]$ hadoop fs -cat /user/vagrant/my_data/part-m-00000| head -3
Mary,F,7065
Anna,F,2604
Emma,F,2003

We can also create a config file and store the commands in it for re-usability.

[vagrant@node1 ~]$ cat sqoop_test_config.cnf
import
--connect
jdbc:mysql://localhost/my_test
--username
root

[vagrant@node1 ~]$ sqoop --options-file ./sqoop_test_config.cnf --password ***** --m 1 --table name_data --target-dir /user/vagrant/my_data

This also does the same job, but now we have the flexibility to save, edit and reuse the commands.

Delete lines from multiple files (recursively) in Linux

We had a requirement to delete a line which matches a particular pattern from multiple ksh files. These lines of code was used to log execution status and we no longer needed it after an architecture change.

Opening hundreds of files and deleting the lines manually was a painful task, We achieved this by combing find and sed commands.

find . -name “*.ksh” -type f | xargs sed -i -e ‘/Search String/d’

Find command searches for ksh files recursively in the current directory and lists them. The second part, xargs and sed commands searches for the pattern in each file and delete it.

You can refer the manual pages if you need more information on these commands.

Deleting thousands of files from Linux directory

Our project team uses a script which creates thousands of files in a folder and we sometime has to manually clean up all those files.

rm command failed saying the list is too long and we had to find another method to do this. rather than writing s shell script and delete the files one by one we used the find command.

Here how we had done it.

>>>ls -lrt| wc -l
 250,002

We have 250K files in this directory and we tried removing them using the rm command.

>>> rm *.env
 ksh: rm: /bin/rm: cannot execute [Argument list too long]

This issue can be easily solved by the find command and we noticed find runs faster in such situations.

>>>find . -name "*.env" -delete

Above listed find command deleted all .env files in the current directory.

Configuring SSH with putty

Recently i joined a project where i had to access multiple servers. We had almost 20 linux boxes which include Development, QA and production databases. I had to frequently login and out of these servers and entering the username and password everytime has become a pain. I use PuTTy tool to access these servers and i have the latest version of PuTTy installed in my widows 7 laptop. The laptop was a company issued one and most of the time i connect these servers through LAN or VPN.

Then i realized i can enable SSH authentication on my PuTTy and to make that work i had to make a few changes on my laptop and remote machines i wanted to login. Here are the steps to enable SSH authentication from my laptop to remote linux servers.

1. At first i had to install PuTTy on my laptop. PuTTy installation for windows can be dowloaded from Putty website, http://www.putty.org/

putty1

2. Once you downloaded the windows installer, double click on it and install the software.

putty2

3. Click on run and start the installation and specify where you want to install the software. I chose the defalut location, which is C:\Program Files (x86)\PuTTY.

putty3

4.  You can opt for a start menu folder as well.

putty5

5.  Create shortcuts/quick menu items if you want.

putty6

6.  Complete the installation by clicking on Finish.

putty7

7. Now you have putty installed on your machine and ready to use.

8. SSH works on puplic key encrtyption and we have to create a public key and a private key. This can be done by using the puttygen.exe tool. For this first navigae to the putty installation folder and for me it is C:\Program Files (x86)\PuTTY.

9. Open puttygen.exe

putty8

10. click on generate button and move your mouse on the designated area to generate random key.

putty9-1

11. At the top of the screen we can see the public key which can be copied to all the servers we want to login. We can use save private key and save it to the local computer somewhere others don’t have access. I kept it in my documents folder so that noone else can access.

putty10

12. Puttygen asks for a passphrase to protect the private key, I didn’t give any because I’m planning to use it in my company laptop which no one else has access.

putty11

13. Once you are done with that , you can save the private key in a secure location.

putty12

14. Now we have the public and private keys and we have to deploy the pucblic key on all the remote hosts. for that open the putty tool and enter the host details. Please make sure you enter your hostname as username@hostname to prevent putty from asking your user id every time.

putty13

15. Once you are done with your host details and saved the details, go to the SSH menu on the left side and then click on Auth sub menu. In the Auth window we have an option to select the private key and select the private key we just saved. Dont forget to go back to the sessions menu and save again.

putty14

16. Now we have to configure the remote server to accept our connection. For that, login to the remote server and run the following commands in your home directory.

$ mkdir .ssh

$ chmod 700 .ssh

$ cd .ssh

$ touch authorized_keys

$ chmod 700 authorized_keys

then open the authorized_keys in vi editor,

vi authorized_keys

Once youy are in the vi screen press ‘o’ (small oh) and then paste the contents of the public key. The press escape and then ‘:wq’ to save the contents.

putty16

17. Now you can exit from the remote connections and open the putty again. Load the connections and click on open. “Whola” you can login without a password and you have a secure login.

 

My first blog..

Well, this my first blog. I have been thinking about blogging for some time and finally decided to give it a try. I consider this more of a journal than a blog.  I’m a database performance engineer and i face many performance issues in my work, i want to chronicle all of them for my future reference. I will be really happy if my blog helps some one. After all “Knowledge grows when shared.”