mysql async vs jdbc in execute blocking

[ad...@cs.miami.edu]

Hi,

In my tests, I seem to be getting better performance with jdbc+pool (placed in execute blocking) as opposed to mysql-async.

My test includes small select queries (that take a few milliseconds)  JDBC out performs mysql-async heavily with 300 clients.

I could try to optimize the async version more (and likely will).  But my question to the community:  do you generally find JDBC to be faster than the async-mysql?  In what types of workloads might the async-mysql be more performant or appropriate?

Thanks,

-Adam

[Paulo Lopes]

Hi,

It depends on the test you're doing I guess. For example with the techempower benchmark we can observe the opposite. Note however that the benchmark is very naive, it selects a single row with 2 columns by a integer primary key.

Also you will see that for such workloads the default pool size is quite small.

[ad...@cs.miami.edu]

>>It depends on the test you're doing I guess. 

I am making an http database proxy.  At my org we had a  need to access JVM databases (Derby and H2) from non-JVM languages.  Instead of fighting against drivers that don't exist or are partially implemented, we decided to expose the databases over HTTP.  Vert.x was a nice match.  

I got interested in the project, and made it into a personal project to make it into a more general database proxy (http requests with sql - in a json payload - are passed through to the database, and the json response back to http client).  There are a few OpenSource projects that do something similar, but were not exactly what I was looking for.  I will probably open source this project in a few weeks.

When I expanded it out to be a more general tool, I allow the admin to choose between JDBC and async-MYSQL, if using mysql.  I expected the async-Mysql to smoke the old JDBC (in execute blocking), but it was the opposite.

I think there are some workloads that work better for async-Mysql  than JDBC, I guess I just have not found that yet (will keep exploring).

Best,

-Adam 

[Tim Fox]

On Thursday, 20 April 2017 03:53:22 UTC+1, ad...@cs.miami.edu wrote:

>>It depends on the test you're doing I guess. 

I am making an http database proxy.  At my org we had a  need to access JVM databases (Derby and H2) from non-JVM languages.  Instead of fighting against drivers that don't exist or are partially implemented, we decided to expose the databases over HTTP.  Vert.x was a nice match.  

I got interested in the project, and made it into a personal project to make it into a more general database proxy (http requests with sql - in a json payload - are passed through to the database, and the json response back to http client).  There are a few OpenSource projects that do something similar, but were not exactly what I was looking for.  I will probably open source this project in a few weeks.

When I expanded it out to be a more general tool, I allow the admin to choose between JDBC and async-MYSQL, if using mysql.  I expected the async-Mysql to smoke the old JDBC (in execute blocking), but it was the opposite.

It really depends on the workload but I'd expect performance between async and sync to be roughly similar for most workloads assuming the DB is fully loaded. If there's a massive difference it probably implies a setup issue.

[ad...@cs.miami.edu]

>> If there's a massive difference it probably implies a setup issue.

I don't have a massive differnce, but a noticalbe difference across localhost.  I think I figured out the reason why.

It looks like the vertx-async-mysql client contacts the mysql server when an existing connection is requested from the pool.  It appears that every(?) query requires two round trips to the MYSQL server: one for a health check on the connection, and a second one for the actual query.  I am not 100% sure if this is always the case, but it looks to be what I am observing.

On the other hand, the JDBC pool I was using does not do a health check, and instead simply reconnects if an exception is thrown while performing the query itself.  This seems to lead to less round trips to the MYSQL server.

So, I might have been comparing two completely different setups.

-Adam

[Jochen Mader]

Would it be possible to share a reproducer?

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+unsubscribe@googlegroups.com.
Visit this group at https://groups.google.com/group/vertx.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/72168d01-e419-466d-9a38-4b6255e8f088%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Jochen Mader | Lead IT Consultant

codecentric AG | Elsenheimerstr. 55a | 80687 München | Deutschland
tel: +49 89 215486633 | fax: +49 89 215486699 | mobil: +49 152 51862390
www.codecentric.de | blog.codecentric.de | www.meettheexperts.de

Sitz der Gesellschaft: Düsseldorf | HRB 63043 | Amtsgericht Düsseldorf
Vorstand: Michael Hochgürtel . Rainer Vehns
Aufsichtsrat: Patric Fedlmeier (Vorsitzender) . Klaus Jäger . Jürgen Schütz

[ad...@cs.miami.edu]

>>Would it be possible to share a reproducer?

I am starting to think I was wrong with my hypothesis that the vertx-async-mysql client was making multiple round trips to the mysql server.

Essentially this is what I did.

[1] I ran a bunch of concurrent vertx-async-mysql requests to get some clients in the pool.

[2] shutdown mysql (or otherwise make it inaccessible )

[3] Request a connection from the pool... the request will fail with error... which made me think that simply requesting a client from the pool was performing some type of network test to make sure it was a live connection, or why else the error when simply requesting a connection from the pool? 

--------------

I browsed the code in the vertx github and I could not find a "health check" on the connection, and it is possible that there is another explanation... perhaps once the connection goes off the network the client is invalidated real-time, not at client request from the pool.

-Adam

On Wednesday, April 19, 2017 at 12:34:55 AM UTC-4, ad...@cs.miami.edu wrote:

[Julien Viet]

Wireshark supports mysql and postgres protocols, so you can use it to easily understand what is going on.

-- 
You received this message because you are subscribed to the Google Groups "vert.x" group.

To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.

Visit this group at https://groups.google.com/group/vertx.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/12135ed4-c850-464c-9bfa-926236ea2e4a%40googlegroups.com.

[ad...@cs.miami.edu]

>>Wireshark supports mysql and postgres protocols, so you can use it to easily understand what is going on.

Thanks for the pointer. Very useful tool for this.  But, I did not see any traffic going to MySQL at the time a client is requested from the pool... so my hypothesis seemed to be wrong.  I will continue to dig to see why my JDBC performance is higher than the vertx-msyql-async..

-Adam