slow mysql inserts
I'm currently doing some preliminary performance testing of db inserts
with various technologies. The db server itself is nothing special
but what surprised me was the difference in INSERT speeds between
Clojure and the other solutions I've tried. Clearly there is
something I'm missing so perhaps I can get some insight.
Java code INSERT was about 500 rows/sec. The Clojure code was about
30 rows/sec (both ClojureQL and with-connection versions). Perl DBI
and ObjC were both around 500 rows/sec. I'm comparing relative speed
-- the 'mysql server' is just a mac-mini running Mysql 5.0. While I
recognize this is a pretty simplistic seat-of-the-pants test I wasn't
expecting 16x slower.
ClojureQL code:
(def test (sql/table db :test))
(doseq [x (range 2000)]
@(sql/conj! test [{:value x}]))
Vanilla clojure code I tried as well.
(doseq [x (range 2000)]
(with-connection db
(insert-values :test
[:value] [x])))
db is the com.mysql.jdbc.Driver connection string.
Pure Java:
for(int i = 2000 ; i>0 ; i--){
try {
java.sql.Statement s = conn.createStatement();
s.executeUpdate("insert into test (value) values(" + i + ")");
}
catch (Exception e) {
System.out.println(e);
System.exit(0);
}
}
where conn here is again my com.mysql.jdbc.Driver connection string.
I used the same mysql-connector-java jar for all tests on the JVM.
Initially my tests were run in the cake-swank JVM. To try to
eliminate any variables I also created a jar with lein and tested
that. The pure java code was compiled and run command line.
I don't believe for a second this is an issue with clojure, but to be
honest I'm not clear where to start troubleshooting.
What sort of things can I play with to figure out where the bottleneck
is?
Thanks.
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
Re: slow mysql inserts
This was it. The overhead was in creating the handle. When I moved the doseq inside the with-connection it solved the problem. Thanks On Jan 19, 2011, at 7:24 AM, Meikel Brandmeyer wrote: > Hi, > > On 18 Jan., 19:32, rygorr wrote: > >> (doseq [x (range 2000)] >> (with-connection db >>(insert-values :test >> [:value] [x]))) >> db is the com.mysql.jdbc.Driver connection string. > > Move the with-connection outside the doseq. > > (with-connection db > (doseq [x (range 2000)] >(insert-values :test [:value] [x]))) > > Sincerely > Meikel > > -- > You received this message because you are subscribed to the Google > Groups "Clojure" group. > To post to this group, send email to clojure@googlegroups.com > Note that posts from new members are moderated - please be patient with your > first post. > To unsubscribe from this group, send email to > clojure+unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/clojure?hl=en -- You received this message because you are subscribed to the Google Groups "Clojure" group. To post to this group, send email to clojure@googlegroups.com Note that posts from new members are moderated - please be patient with your first post. To unsubscribe from this group, send email to clojure+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/clojure?hl=en
