Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (nike.apache.org: domain of lists@nabble.com designates
 216.139.236.158 as permitted sender)
Message-ID: <22682539.post@talk.nabble.com>
Date: Tue, 24 Mar 2009 08:10:24 -0700 (PDT)
From: Jeff P <killingdjef@hotmail.com>
To: user-java@ibatis.apache.org
Subject: Re: iBatis 6x slower, what am I overlooking?
In-Reply-To: <16178eb10903222033p13ea6dcfvec1786a14c2ad3da@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
References: <22653299.post@talk.nabble.com>
 <16178eb10903222033p13ea6dcfvec1786a14c2ad3da@mail.gmail.com>


Let me first say "THANK YOU". I've been puzzling with iBatis, and while I
don't mind finding stuff out for myself, the effort you made for my question
was very kind of you and beyond what I would've expected.

I've ran your version and can confirm that ibatis and JDBC are practically
just as fast. With the 100,000 select test, the finishing time is usually 1
- 1,5 second in advantage of JDBC. Im guessing this is normal. It's an
overhead of 0.010 - 0.015ms per query. (On my dedicated SQL Server its even
below 1 second, with 100,000 selects, meaning ~0.005 ms).

Did you know by the way, that there is a big performance difference when
using a connector? Using MySQL Connector/j 5.1.17 makes iBatis run twice as
slow, while 5.1.16 gives me near identical results. 

To answer two points
 
 * Not sure why you're using such an odd query for the benchmark.
 * Not sure why you're using an empty database for the benchmark. 

My database holds track of values that can be updated by a client. Whenever
a value is updated, the last_sync column is updated with current_timestamp.
When a client connects it will run the query every 5 seconds to see if there
are updates. I won't get into too much detail, why we're not pushing updates
from the server rather than a request-reply model, but as it is, this is the
situation.

To sum it up, the client will run a 
"Select count(*) from table where last_sync => (now-5seconds) and < now". 
and do stuff when the count is greater than 0

The reason why i've used a fixed date was because I am benching the exact
same query with 
Super Smack (http://jeremy.zawodny.com/mysql/super-smack/). You can edit the
select-key.smack file and replace the executed query with your own. Now
comes my problem, when I run the query i've used with JDBC and iBatis test,
Super Smack tells me it gets executed ~50,000-~80,000 times per second (!)
while, running it with JDBC/iBatis no more than 10,000 are executed per
second. The test I wrote should simulate Super smack. What super smack does
is open a connection, execute the queries in a loop and then close the
connection. I have no idea where this delay is coming from but I am
suspecting the MySQL Connector.

What comes to mind first is ofcourse, the database settings. However, since
supersmack uses the same database and table as the Java app, that makes no
sense at all. To take doubt away I've tweaked the DB, used indexes where
needed and made sure there are no slow queries. (Slow log and explain). The
tweaking gave me a boost but still Supersmack stays a factor 5-10 faster.
JDBC/Ibatis improved, but so did Supersmack.

I've checked MySQL administrator (GUI) and monitored the sql threads. It
appears that when running the java app, the threads sleep often while
stresstesting. This is not the case when running supersmack. Does the MySQL
connector use something that slows traffic down to the server? Or is it a
setting I've missed? The fact that the problem occurs with JDBC as well as
iBatis probably means it isn't iBatis related. However, I do hope this might
ring a bell.

Also, the empty table was a mistake, I should've added a few inserts. My
bad.

Again, thanks for the effort!


Clinton Begin wrote:
> 
> A few things
> 
>   * You were sharing the connection and the statement in the JDBC
> tests.  But in the iBATIS tests you were outside of a
> session/transaction scope.  So to make it fair, you either need to
> open/close the connection and create/close the statement each time.
>   * You were using Statement vs. iBATIS' PreparedStatement
>   * You were too creative with the timings... you should not attempt
> to sum up a bunch of individual times when benchmarking -- the OS
> isn't that accurate.  So you should just time the entire execution of
> all of the iterations.
>   * You should code your tests carefully, it will help clear up a lot
> of things that are hard to see when code is hacked out.  Use
> interfaces and a common timer class to make things clear.
>   * Not sure why you're using such an odd query for the benchmark.
>   * Not sure why you're using an empty database for the benchmark.
> 
> There were a couple of other things unrelated to the benchmarking.
> You don't have to use individual CDATA sections for <  and > in the
> SQL.  Just wrap the whole statement unless you're using Dynamic SQL,
> in which case you can block out just the conditional, or use &lt; &gt;
> 
> That's all I can remember. :-)
> 
> Here's an updated test that scopes the iBATIS calls in a
> session/transaction and both the JDBC and iBATIS versions execute
> 100,000 iterations around 16 seconds on my crappy HP laptop.  :-)
> 
> http://clintonbegin.com/downloads/jdbc_ibatis_benchmark_0.3.zip
> 
> I think you'll find that even if there are a few places where iBATIS
> is slower, it's not slow enough to worry about.   We're talking
> milliseconds usually.
> 
> Cheers,
> Clinton
> 
> On Sun, Mar 22, 2009 at 8:13 PM, Jeff P <killingdjef@hotmail.com> wrote:
>>
>> I was in the process of stresstesting my app and found out that my count
>> queries weren't optimal. I've produced the following JDBC test and iBatis
>> test and the test says iBatis is 6 times slower.
>>
>> I know iBatis should be just as fast as JDBC, so I refuse to accept that
>> outcome. However I can't figure out what i'm overlooking. I've re-read
>> the
>> manual pdf two times to find out performance steps which I might have
>> missed
>> (had good hopes for caching) but alas, didn't make any change.
>>
>> I've made an, as small as possible, sample which reproduces my
>> mini-benchmark. The attached file includes SQL creation code, JDBC
>> benchmark
>> and iBatis benchmark which should all run without any problem. Only
>> prerequisite is to have MySQL installed and the xml files configured
>> correctly.
>>
>> I hope someone can push me in the right direction.
>> Thank you in advance.
>>
>> http://www.nabble.com/file/p22653299/jdbc_ibatis_benchmark_0.2.rar
>> jdbc_ibatis_benchmark_0.2.rar
>>
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/iBatis-6x-slower%2C-what-am-I-overlooking--tp22653299p22653299.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
> 
> 

-- 
View this message in context: http://www.nabble.com/iBatis-6x-slower%2C-what-am-I-overlooking--tp22653299p22682539.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.