Re: iBatis 6x slower, what am I overlooking?
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 killingd...@hotmail.com wrote: I was in the process of stresstesting my app and found out that my
Re: iBatis 6x slower, what am I overlooking?
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?
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 killingd...@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?
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 killingd...@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.