Archive

Archive for January, 2017

jmeter – Variable Name must not be null in JDBC Request

January 6th, 2017

So Jmeter seems super cool.

I’ve only used it a little bit but it does seem a bit touchy about somethings (like spaces in input fields) and the errors are often less than obvious and I’m not finding that much out there on google for the errors.

Today I ran into the error

Variable Name must not be null in JDBC Request

 screen-shot-2017-01-06-at-12-01-10-pm

screen-shot-2017-01-06-at-12-00-50-pm

and Googling it didn’t turn up anything.

I’m pretty sure I ran into this same error a few weeks ago when I was first starting with Jmeter, so blogging here to document it.

I was trying something new – running a procedure – instead of a regular sql statement and I think that threw me off.

The error sounded to me like I needed to define an input or output variable.

I tried both of those, until finally I saw its not the input or output variable but the name of the JDBC connection pool that was missing

 

screen-shot-2017-01-06-at-11-51-15-am

 

 

This was the closest google hit I could find

http://stackoverflow.com/questions/36741446/add-summary-report-results-to-database-in-jmeter

In googling around, I did turn up

http://stackoverflow.com/questions/36741446/add-summary-report-results-to-database-in-jmeter

which sounds cool – loading up the results into a table after the Jmeter run

And thanks to Ivan Rancati who answered this same question of mine 4 weeks ago on user@jmeter.apache.org

 

Followup

Another problem I had today was “”The column index is out of range”

I was doing a

“INSERT INTO authors (id,name,email) VALUES(nextval(‘serial’),’Priya’,’p@gmail.com’);”

The JDBC Request worked when it was just

“INSERT INTO authors (id,name,email) VALUES(2,’Priya’,’p@gmail.com’);”

Turns out I had set “Parameter values” and “Parameter types”. When I took them out it worked. What confuses me, and what I’ll have to look back into, is the whole reason I added the  Parameters was because the nextval wasn’t work. Forgot what that original error was.

Uncategorized

jmeter – getting started

January 5th, 2017

jmeter

This blog post is just a start at documenting some of my experiences with jmeter. As far as load testing tools go, jmeter looks the most promising to me. It has an active community, supports many different databases and looks quite flexible as far as architecting different work loads goes.

The flexibility of jmeter also makes it hard to use. One can use jmeter for many other things besides databases so the initial set up is a bit oblique and there look to be many paths to similar results. As such, my understand and method for doing things will probably change considerably as I start to use jmeter more and more.

I’m installing it on a mac and using RDS instances.

installing jmeter

brew install jmeter

see

Database Driver download (I’m using the following)

Created a test table

  • CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
  • INSERT INTO authors (id,name,email) VALUES(2,’foo’,’foo@foo.com’);

Startup up jmeter

    $ which jmeter 
      /usr/local/bin/jmeter 
    $ jmeter 
      Writing log file to: /Users/kylelf/jmeter.log

brings up a graphic window

screen-shot-2017-01-03-at-2-40-06-pm

 

Add your path to the database drivers at the bottom of the screen by clicking “Browse …” and going to your driver file and selecting it.

screen-shot-2017-01-04-at-1-48-19-pm

 

We are going to create the following (minimum setup for an example)

  1. create test: Thread group named ‘Database Users’
  2. db connection: Config element of type JDBC Connection Configuration
  3. query to run: Sampler of type JDBC Request
  4. results output: Listener of type “View Results Tree”

1. First to do is add a “Thread Group”

(right click on “Test Plan”)

Define how many connections to make and how many loops to make of the workload

thread_group

 

screen-shot-2017-01-03-at-2-45-25-pm

interesting parts here are

  • “Number of Threads (users)” : can set the number of database connections
  • “Loop Count ” : can set the number of iterations of the test query

2. Add a Config Element of type JDBC Connection Configuration

 

Define what database to connect to

screen-shot-2017-01-03-at-2-46-28-pm

For Oracle make sure and change “Select 1″ to “Select 1 from dual” or you’ll get non-obvious error.

Name the pool. For example I call mine “orapool”

and fill out all the connection information

  • Database machine, port and SID of form: jdbc:oracle:thin:@yourmachine:1521:ORCL
  • JDBC Driver Class: oracle.jdbc.OracleDriver
  • Username
  • Password

screen-shot-2017-01-03-at-3-03-46-pm

3. Sampler of type JDBC Request

Define a SQL statement to run

screen-shot-2017-01-03-at-3-35-47-pm

screen-shot-2017-01-03-at-3-39-24-pm

 

Make sure and include the name of the thread pool created above. In my case it’s called “orapool”

add a SQL statement to run

screen-shot-2017-01-03-at-3-53-39-pm

4. Listener of type “View Results Tree”

create a widget to see the output

screen-shot-2017-01-03-at-3-55-36-pm

Final setup looks like

run your load and look at the output

screen-shot-2017-01-03-at-4-42-49-pmNow you hit the run button, the green triangle.

Then click on “View Results Tree” to see the output.

screen-shot-2017-01-03-at-4-02-54-pm

I clicked on “View Results Tree” and then clicked on “JDBC Request” in red.

Then I’ll see some output. I choose “Response data” because it’s a bit more succinct and see the error. In this case there is an extra space ” ” at the end of “oracle.jdbc.OracleDriver “. Jmeter is sensitive to spaces. I’ve gotten a lot of errors because of spaces in fields such as variable names and such.

Correcting that it runs

screen-shot-2017-01-03-at-4-52-52-pm

 

All the setup might sound like a bit of a pain but once it’s set up, it’s easy to click through and make modifications.

All the setup is available in a text .jmx file and if you are brave you can edit directly there.

Here is the above example .jmx file on github.

Look for “my” and replace

  • myinstance.rds.amazonaws.com
  • myuser
  • mypassword

The above example is more or less pointless – sort of a “Hello World”.

From here though you can increase the number of threads, increase the number of loops, add more SQL statements.

Jmeter allows a lot of customization so you can add .cvs files for input values, capture output values into variables and use them in input values, have different types of loops with different users running concurrently etc.

More to come.

Christian Antognini gave a presentation at Oaktable World SF in Sept 2016. He was gracious enough to send along his functionally rich .jmx file and I’ll blog on that soon.

 

Uncategorized

Graphics for SQL Optimization

January 4th, 2017

Dan Tow, in his book SQL Tuning, lays out a simple method of tuning SQL queries. The method is

  • Draw a diagram of each table in the query with Children above Parents
  • Draw join lines between each join (many-to-many, one-to-many)
  • Mark each table with a predicate filter and calculate the amount of table filtered out

Then to find a great optimal optimization path candidate

  1. Start at the table with the strongest predicate filter (the filter that returns the fewest % of the table)
  2. join down to children (if multiple children join to child with strongest predicate filter)
  3. If you can’t join to children, join up to parent

The basics are pretty simple and powerful. Of course there are many cases that get more complex and Dan goes into these complex cases in his book.

What about indexes? Well the method will point out joins that should happen and if those joins are missing indexes then it indicates that indexes should be created.

What about join type? I generally leave this to the optimizer. The join type can be important but generally order of joins and indexes are more important. I look at join type as the final optimization.

Let’s take an example query:

SELECT COUNT (*)
FROM   a,
       b,
       c
WHERE
       b.val2 = 100 AND
       a.val1 = b.id AND
       b.val1 = c.id;

There are  indexes on b.id and c.id.  Diagramming the query in DB Optimizer gives

sql1

The red lines with crows feet mean that as far as the definitions go, the relations could be many to many.

Question is “what is the optimal execution path for this query?”

One of  the best execution plans is to

  1. start at the most selective filter table
  2. join to children  if possible
  3. else join to parent

There is one filter in the diagram, represented by the green F on table B. Table B has a filter criteria in the query “b.val2=100″.

Ok, table B is where we start the query. Now where do we go from B? Who is the parent and who is the child? It’s not defined in the constraints nor indexes on these tables so it’s hard for us to know. Guess what ? It’s also hard for Oracle to figure it out. Well, what does Oracle decide to do? This is where the cool part of DB Optimizer  comes in.

The super cool thing with DB Optimizer is we can overlay the diagram with the actual execution path (I think this is awesome)

sql2

For the digram we can see Oracle starts with B and joins to A. The result if this is joined to C. Is this the optimal path?

Well, let’s keep the same indexes and just add some constraints:

alter table c add constraint c_pk_con unique (id);
alter table b add constraint b_pk_con unique (id);

Now let’s diagram the query with DB Optimizer:

sql3

We can now see who the parent and child is, so we can determine the optimal query path which is to start at B, the only filter and  join to the child C then to the parent A.  Now what does Oracle do with the added constraint info:

sql4

Guess what? The execution plan has now changed with the addition of constraints and now Oracle’s execution path goes from a suboptimal plan to  the optimal path. Moral of the story is to make sure and define constraint information because it helps the optimizer, but what I wanted to show here was the explain plan overlay on the diagram which makes comparing execution plans much easier. Putting the queries VST diagrams side by side along with the overlay of execution path we can clearly and quickly see the differences:

sql5sql6

I plan to blog more about this awesome feature. It’s really cool.

Here is an example from an article by Jonathan Lewis

http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/

The query Jonathan discusses is

SELECT order_line_data
FROM
         customers cus
         INNER JOIN
         orders ord
         ON ord.id_customer = cus.id
         INNER JOIN
         order_lines orl
         ON orl.id_order = ord.id
         INNER JOIN
         products prd1
         ON prd1.id = orl.id_product
         INNER JOIN
         suppliers sup1
         ON sup1.id = prd1.id_supplier
   WHERE
         cus.location = 'LONDON' AND
         ord.date_placed BETWEEN '04-JUN-10' AND '11-JUN-10' AND
         sup1.location = 'LEEDS' AND
    EXISTS (SELECT NULL
            FROM
                 alternatives alt
                 INNER JOIN
                 products prd2
                 ON prd2.id = alt.id_product_sub
                 INNER JOIN
                 suppliers sup2
                 ON sup2.id = prd2.id_supplier
           WHERE
                  alt.id_product = prd1.id AND
                  sup2.location != 'LEEDS')

which diagrammed looks like

sql7

There are multiple filters, so we need to know which one is the most selective to know where to start, so we ask DB Optimizer to display the statistics as well  (blue below a table is the filter %, green above is # of rows in table and numbers on join lines are rows returned by a join of just those two tables)

sql8

Now that we can determine a candidate for best optimization path, does Oracle take it?

sql9

Can you find the optimization error?

Dark green is where execution starts. There are two starts: one for the main query body and one for the subquery.

The red is where query execution ends.

PS a big part of this work is by the lead developer Matt Vegh. Many thanks to Matt for this awesome work.

 

PPS another example from Karl Arao

sql10

The dark green nodes are starts, so there are 4 separate starts. We can see how the result sets from each start are joined with each successive table join set. The red is the final step.

Uncategorized