slow mysql inserts

[rygorr]

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.

[Christian Vest Hansen]

I suspect that ClojureQL and with-connection are opening and closing
the connection for every query, whereas the Java and Perl versions are
reusing their connections.

Try adding a connection pool to the mix.

> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clo...@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+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en

--
Venlig hilsen / Kind regards,
Christian Vest Hansen.

[Meikel Brandmeyer]

Hi,

On 18 Jan., 19:32, rygorr <ryg...@gmail.com> 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

[rygorr]

This was it. The overhead was in creating the handle.

When I moved the doseq inside the with-connection it solved the problem.

Thanks

[Michael Ossareh]

On Wed, Jan 19, 2011 at 09:15, rygorr <ryg...@gmail.com> wrote:

This was it.  The overhead was in creating the handle.

I can't remember who told me this: "Always blame the network".

In finer detail, the conversation was about things that use the network and are so very much slower than you anticipate.