RE: SQL performance with Local journalling -- MIDRANGE-L

- First and foremost: Is the SQL statement typical of the workload they
run?  If not, then the performance impact of journalling the SQL
statement doesn't matter.  The ONLY thing that matters is journalling's
impact on their typical workload.
- How tuned is the system in terms of memory pools, cache, etc.?
- How many disk arms & what kind of controllers are at work?
- Any RAID or mirroring?
- Is the journal going to a separate ASP?
- How balanced is the disk usage (see STRASPBAL)?

I'm sure there are several other factors but these sprang to mind pretty
quickly.


John A. Jones, CISSP
Americas Information Security Officer
Jones Lang LaSalle, Inc.
V: +1-630-455-2787  F: +1-312-601-1782
john.jones@xxxxxxxxxx

-----Original Message-----
From: Evan Harris [mailto:spanner@xxxxxxxxxx] 
Sent: Wednesday, February 23, 2005 12:46 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL performance with Local journalling

Hi all

I have a customer that is starting out on a HA implementation who
wondered about the additional overhead of journaling.

To test the impact they wrote a simple SQL update statement and ran it
on a journaled file, then ran it again after turning journaling off. The
impact was significantly different (in the order of 1000%) and although
they didn't process a whole lot of records (a couple of thousand) the
result is enough to have them concerned.

After I spoke to them a couple of days later I had them run the
statement again, but asked them to run the SQL statement while the file
was not journaled first to see if the order of execution had had any
effect on the relative performance of the two operations. This had
essentially the same result.

I am not overly concerned about the likely impact of journaling on their
system as the hardware should handle it and my experience is that it
will not add anywhere near the overhead that they are seeing in their
admittedly limited testing, however, I am curious as to what could cause
such a weird result.

The SQL itself was selecting approximately 2000 records via the relative
record number and performing a simple update on a field in the record
layout. The file was created by doing a copy file and there were no
logicals over the new file. Relative record was uses to avoid using a
key for selection. The SQL was submitted to batch in both cases via some
kind of RUNSQL command they have access to. Essentially they were trying
to assess the raw impact of journaling by removing all the variables
that might have influenced the performance of the SQL.

Is there anything that anyone has seen that could account for this
result ? 
Is there any relationship between SQL and journaling that I should take
into account ?

Regards
Evan Harris 

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.




This email is for the use of the intended recipient(s) only.  If you have 
received this email in error, please notify the sender immediately and then 
delete it.  If you are not the intended recipient, you must not keep, use, 
disclose, copy or distribute this email without the author's prior permission.  
We have taken precautions to minimize the risk of transmitting software 
viruses, but we advise you to carry out your own virus checks on any attachment 
to this message.  We cannot accept liability for any loss or damage caused by 
software viruses.  The information contained in this communication may be 
confidential and may be subject to the attorney-client privilege. If you are 
the intended recipient and you do not wish to receive similar electronic 
messages from us in future then please respond to the sender to this effect.