Category Archives: performance

Hacking SLOB to run on Solaris

Kevin Closson’s Silly Little Oracle Benchmark https://kevinclosson.net/slob/ is not ported to Solaris. This means that to get it working is not as easy as it should be. Kevin himself suggests that you use a small linux host to run the tool if your database is running on a non-supported operating system.

I’m going to track the changes I make here.

setup.sh

First problem – grep!

./setup.sh test 16
SLOB 2.4.0
FATAL : 2018.05.15-14:40:57 : Usage : ./setup.sh.orig: <tablespace name> <number of SLOB schemas to create and load>
FATAL : 2018.05.15-14:40:57 : Option 2 must be an integer

This is caused by the function f_is_int requiring the gnu grep command behaviour. Simplest way to this is to change the grep to point to /usr/gnu/bin/grep in this script.

Next problem..

Once this change was made, I was then able to create the first schema ( yay!) but then when it came to make the remaining 15, it failed.

NOTIFY : 2018.05.15-14:37:27 : Waiting for background batch 1. Loading up to user11
FATAL : 2018.05.15-14:37:29 : 
FATAL : 2018.05.15-14:37:29 : f_flag_abort: Triggering abort
FATAL : 2018.05.15-14:37:29 : 
FATAL : 2018.05.15-14:37:30 : 
FATAL : 2018.05.15-14:37:30 : f_flag_abort: Triggering abort
FATAL : 2018.05.15-14:37:30 : 
FATAL : 2018.05.15-14:37:40 : 
FATAL : 2018.05.15-14:37:40 : f_flag_abort: Triggering abort
FATAL : 2018.05.15-14:37:40 : 
FATAL : 2018.05.15-14:37:40 : 
FATAL : 2018.05.15-14:37:40 : f_check_abort_flag: discovered abort flag
FATAL : 2018.05.15-14:37:40 : 
FATAL : 2018.05.15-14:37:40 : Aborting SLOB setup. See /export/home/oracle/mel/SLOB/cr_tab_and_load.out

 

Handily there is an error message in the logfile

ALTER TABLE cf1 MINIMIZE RECORDS_PER_BLOCK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00039: error during periodic action
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT




Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
FATAL : 2018.05.15-14:37:40 :
FATAL : 2018.05.15-14:37:40 : f_setup: Failed to load user4 SLOB table
FATAL : 2018.05.15-14:37:40 :

I’m going to assume the clue is in the error and increase the PGA aggregate limit

SQL> alter system set pga_aggregate_limit=36G scope=both sid='*';

After this change, the script ran to the end successfully.

 

$ diff setup.sh setup.sh.orig
30,31d29
< 
< 
86c84
< if ( ! echo $s | /usr/gnu/bin/grep -q "^-\?[0-9]*$" )
---
> if ( ! echo $s | grep -q "^-\?[0-9]*$" )
250c248
< if ( echo "$cdb" | /usr/gnu/bin/grep -q "YES" > /dev/null 2>&1 )
---
> if ( echo "$cdb" | grep -q "YES" > /dev/null 2>&1 )
254c252
< if ( echo "$cdb" | /usr/gnu/bin/grep -q "NO" > /dev/null 2>&1 )
---
> if ( echo "$cdb" | grep -q "NO" > /dev/null 2>&1 )
410c408
< done | sqlplus -s "$constring" 2>&1 | tee -a $fname | /usr/gnu/bin/grep -i "dropped" | wc -l | while read num_processed
---
> done | sqlplus -s "$constring" 2>&1 | tee -a $fname | grep -i "dropped" | wc -l | while read num_processed

runit.sh

So – we already know that the scripts need gnu grep, especially in the function f_is_int. Safest option will be  to change every occurrence of grep with /usr/gnu/bin/grep.

Try running it with a single schema

 ./runit.sh 1

The duration of the run is based on the slob.conf variable RUN_TIME. The default is 300 seconds, but I am dropping it to 90 while I am doing debugging.

This ran successfully (I think!). Certainly AWR reports were created.

There was one obvious failure – the mpstat output was not generated.

$ cat mpstat.out 
mpstat: -P expects a number
Usage: mpstat [-aqm] [-A core|soc|bin] [-k key1,[key2,...]] [-o num] [-p | -P processor_set] [-T d|u] [-I statfile | -O statfile ] [interval [count]]

Ok – so this is the line causing the problem.

 ( mpstat -P ALL 3 > mpstat.out 2>&1) &

On Linux mpstat without the -P flag will give you summary output, -P separates the output by processor.  On Solaris the default is to show one line per CPU, so I just need to change the line to

 ( mpstat 3 > mpstat.out 2>&1) &

also check for other invocations of mpstat

Next - try with 2 sessions

./runit.sh 2

Arrgh. That fails with

: List of monitored sqlplus PIDs written to /tmp/.SLOB.2018.05.15.153524/13174.f_wait_pids.out.
usage: ps [ -aAdefHlcjLPyZ ] [ -o format ] [ -t termlist ]
 [ -u userlist ] [ -U userlist ] [ -G grouplist ]
 [ -p proclist ] [ -g pgrplist ] [ -s sidlist ] [ -z zonelist ] [-h lgrplist]
 'format' is one or more of:
 user ruser group rgroup uid ruid gid rgid pid ppid pgid sid taskid ctid
 pri opri pcpu pmem vsz rss rssprivate rssshared osz nice class time etime stime zone zoneid env
 f s c lwp nlwp psr tty addr wchan fname comm args projid project pset lgrp

This wasn’t obvious where the fault was happening. So lets add the -x flag to the first line of our script to try and find the line causing our problems.

++ sed /PID/d
++ wc -l
+++ cat /tmp/.SLOB.2018.05.15.153852/15451.f_wait_pids.out
++ sed 's/[^0-9]//g'
++ ps -p 32526 32527
usage: ps [ -aAdefHlcjLPyZ ] [ -o format ] [ -t termlist ]
 [ -u userlist ] [ -U userlist ] [ -G grouplist ]
 [ -p proclist ] [ -g pgrplist ] [ -s sidlist ] [ -z zonelist ] [-h lgrplist]
 'format' is one or more of:
 user ruser group rgroup uid ruid gid rgid pid ppid pgid sid taskid ctid
 pri opri pcpu pmem vsz rss rssprivate rssshared osz nice class time etime stime zone zoneid env
 f s c lwp nlwp psr tty addr wchan fname comm args projid project pset lgrp
++ return 0
+ tmp=0

So there is something with the ps -p command that is not happy in solaris. I  can reproduce the problem at my command line, by providing ps -p with multiple pids.


# ps -p 7 8
usage: ps [ -aAdefHlcjLPyZ ] [ -o format ] [ -t termlist ].....

If we look at the ps man page, on solaris it says

Some options accept lists as arguments. Items in a list can be either
separated by commas or else enclosed in quotes and separated by commas
or spaces. Values for proclist and grplist must be numeric.

so this means the following will work, but not the format within runit.sh

# ps -p 7,8
 PID TTY TIME CMD
 7 ? 127:47 zpool-rp
 8 ? 1:39 kmem_tas
# ps -p "7 8"
 PID TTY TIME CMD
 7 ? 127:47 zpool-rp
 8 ? 1:39 kmem_tas

 

So – I need to find the line with ps command, and try to change the format presented.

while ( ps -p $pidstring > /dev/null 2>&1 )

 ps -fp $pidstring

both occur in function f_wait_pids

but, they are actually being presented with the list of pids built up in line 1440

sqlplus_pids="${sqlplus_pids} $!"

So – how do I go about getting either a comma or a quote in the string that is passed to the function f_wait_pids?

Well, rather than destroying something that Kevin may rely on later, I add a line below 1440 to generate the pid list with commas

 melsqlplus_pids="${melsqlplus_pids},$!"

This is pretty ugly, as it means my string leads with a comma. However it seems that Solaris doesn’t care about this.

Now I need to add this to the function call on line 1493

if ( ! f_wait_pids "$(( SCHEMAS * THREADS_PER_SCHEMA ))" "$RUN_TIME" "$WORK_LOOP" "$sqlplus_pids"  "$melsqlplus_pids")

Of course I need to add something to pick up that new 5th argument to the function, so within the function itself

local melpidstring="$5"

while ( ps -p "$melpidstring" > /dev/null 2>&1 )

ps -fp "$melpidstring"

So this all seems ok.. but then I discover some more little rats in f_count_pid.

So once again – create my own custom usage of the pid list. There probably is a MUCH simpler way to do this, I’m just building it up as I go along. So – here are the changes I have made to runit.sh to get it to execute without error

 

oracle@sc8avm-25:~/mel/SLOB$ diff runit.sh runit.sh.orig
1c1
< #!/bin/bash 
---
> #!/bin/bash
45c45
< if ( ! echo "$s" | /usr/gnu/bin/grep -q "^-\?[0-9]*$" ) 
---
> if ( ! echo "$s" | grep -q "^-\?[0-9]*$" ) 
275c275
< for string in 'iostat -xm 3' 'mpstat 3' 'vmstat 3'
---
> for string in 'iostat -xm 3' 'mpstat -P ALL 3' 'vmstat 3'
300c300
< ls -l /proc/${tmp}/fd | /usr/gnu/bin/grep deleted
---
> ls -l /proc/${tmp}/fd | grep deleted
416c416
< sqlplus $user/${user}${non_admin_connect_string} <<EOF 2>/dev/null | sed 's/^.* FATAL/FATAL/g' | /usr/gnu/bin/grep FATAL > $tmpfile
---
> sqlplus $user/${user}${non_admin_connect_string} <<EOF 2>/dev/null | sed 's/^.* FATAL/FATAL/g' | grep FATAL > $tmpfile
457c457
< if ( /usr/gnu/bin/grep FATAL "$tmpfile" > /dev/null 2>&1 )
---
> if ( grep FATAL "$tmpfile" > /dev/null 2>&1 )
485d484
< local melpidstring="$5"
488d486
< local meltmpfile="${SLOB_TEMPDIR}/${RANDOM}.MEL${FUNCNAME}.out"
502d499
< echo "$melpidstring" > $meltmpfile 2>&1
508d504
< f_msg NOTIFY "List of monitored sqlplus PIDs with commas written to ${meltmpfile}."
514c510
< tmp=`f_count_pids "$meltmpfile"`
---
> tmp=`f_count_pids "$tmpfile"`
542,544c538,539
< echo "This is the pidstring value $pidstring"
< echo "This is the melpidstring value $melpidstring"
< while ( ps -p "$melpidstring" > /dev/null 2>&1 )
---
> 
> while ( ps -p $pidstring > /dev/null 2>&1 )
551c546
< ps -fp "$melpidstring"
---
> ps -fp $pidstring
875c870
< if ( ! echo "$tmp" | /usr/gnu/bin/grep -q '\-' > /dev/null 2>&1 )
---
> if ( ! echo "$tmp" | grep -q '\-' > /dev/null 2>&1 )
1397c1392
< ( mpstat 3 > mpstat.out 2>&1) &
---
> ( mpstat -P ALL 3 > mpstat.out 2>&1) &
1446d1440
< melsqlplus_pids="${melsqlplus_pids},$!"
1496c1490,1491
< if ( ! f_wait_pids "$(( SCHEMAS * THREADS_PER_SCHEMA ))" "$RUN_TIME" "$WORK_LOOP" "$sqlplus_pids" "$melsqlplus_pids" )
---
> 
> if ( ! f_wait_pids "$(( SCHEMAS * THREADS_PER_SCHEMA ))" "$RUN_TIME" "$WORK_LOOP" "$sqlplus_pids" )

Tomorrow – time to get someone else to run it and see if it behaves how they expect.

 

Advertisements

Ways of monitoring ASM disk performance

I have the feeling I should call this post ‘part 1’ as I’m writing things as I discover new features and how to use them.

In 11G release 2 ASMCMD has a iostat feature that allows you to list the reads and writes per disk (either as I/O operation or bytes)

iostat [-et][–io] [–suppressheader] [–region] [-G diskgroup] [interval]

-e display error statistics (write/read)

-t display time statistics giving the total I/O time in hundredths of a second (requires TIMED_STATISTICS to be true)

-G diskgroup

interval – repeat the command every X seconds

As with most iostat commands, the first run in an interval is total stats up to now, and subsequent intervals cover the time since previous report.

ASMCMD> iostat -t -G data_upper 5
Group_Name  Dsk_Name                    Reads         Writes        Read_Time     Write_Time
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL12  368823115776  398765133824  15652.064264  115609.999195
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL13  360830513152  399665251328  15293.415546  108496.371997

Group_Name  Dsk_Name                    Reads     Writes    Read_Time  Write_Time

DATA_UPPER  DATA_DM01_CD_00_ED2HCELL12  0.00      0.00      0.00       0.00
DATA_UPPER  DATA_DM01_CD_00_ED2HCELL13  0.00      6553.60   0.00       0.00

This information is extracted from the V$ASM_DISK_IOSTAT dynamic performance view.

 

 

Using Oracle 10.2.0.3 on SPARC?

There is a really useful patch if you’ve got db_block_checksum=TRUE (the default) to reduce the CPU utilization of the checksum routine. The patch number is 6814520 – annoyingly the bug report is hidden in metalink, but if you do a ‘simple’ search for the patch, provide the number, you’ll be able to download the patch.

The patch is rolled into 10.2.0.4, but it’s not documented as part of the patch notes.