You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jeff P <ki...@hotmail.com> on 2009/03/23 03:13:16 UTC
iBatis 6x slower, what am I overlooking?
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.
Re: iBatis 6x slower, what am I overlooking?
Posted by Jeff P <ki...@hotmail.com>.
Also, a detail that might be of interest is the following:
Supersmack allows the user to specify the amount of connections. When I
configure 1 user to stresstest, the queries per second are arround 50K. When
I configure 2 users to stresstest, the queries per second go to 70-80K. This
is logical, since It's a dual-core server.
However, when I start the custom stresstest, it goes to 10K queries per
second. When I start another instance (thus having the test run twice) the
queries per second stay on 10K!
Again, this looks like some kind of database setting.. but how would that
explain that supersmack runs perfectly?
--
View this message in context: http://www.nabble.com/iBatis-6x-slower%2C-what-am-I-overlooking--tp22653299p22691110.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: iBatis 6x slower, what am I overlooking?
Posted by Jeff P <ki...@hotmail.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 < >
>
> 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 <ki...@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.
Re: iBatis 6x slower, what am I overlooking?
Posted by Clinton Begin <cl...@gmail.com>.
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 < >
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 <ki...@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.
>
>
Re: iBatis 6x slower, what am I overlooking?
Posted by Kengkaj Sathianpantarit <ke...@gmail.com>.
I suggest that when you think something is slow, find WHAT is slow.
For example, maybe you don't use connection pooling when use iBATIS, the
culprit is get connection code :).
Kengkaj
On Mon, Mar 23, 2009 at 9:13 AM, Jeff P <ki...@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.
>
>