Slick 3.0 performance with PostgreSQL

[Alexey Pismenskiy]

Hello, 

I'm new to Slick and I've tried a little proof-of-concept of Play2 application with Slick 3.0

My goal was to compare the performance of the new Slick API with a plain JDBC prepared statements. 

The source code is at https://github.com/apismensky/play-reactive-slick which I forked from https://github.com/gupta-himanshu/play-reactive-slick.

The database I used was PostgreSQL 9.4 with ~ 10000 records in the main table, running in my localhost.

I've added REST API endpoints to this application for Slick 3.0 (v1)  and plain JDBC (v2), so for example 

GET /api/v1/employees controllers.EmployeeControllerSlick.list(p:Int ?= 0, s:Int ?= 2, f ?= "") // uses a Slick API

GET /api/v2/employees controllers.EmployeeControllerJdbc.list(p:Int ?= 0, s:Int ?= 2, f ?= "") // uses plain JDBC

Then I ran a set of load test against both APIs, changing the global configuration variable (v1 or v2) in the script. JMeter script is here: https://github.com/apismensky/play-reactive-slick/blob/master/loadtest.jmx

With all different settings (number of concurrent threads, with/without connection pool, different connection pool settings JDBC performance is always better. 

With 500 concurrent threads JDBC is ~ 25% faster (see attachment). Moreover, with 2000 concurrent threads error rate of Slick API is ~40% but JBDC has around 2.5%

Maybe I'm doing something wrong - I'll be happy if you point it out. 

The reason I'm doing this is the advertisement of a new reactive concept claims that all calls to external systems are done asynchronously, meaning that the thread should not be blocked on waiting for external system to complete, while in JDBC calls the thread is used until it finishes the execution. Ultimately it should give some performance win for reactive system, but I don't see it in my case.

Thanks in advance.

[Stefan Zeiger]

On 2015-08-11 21:57, Alexey Pismenskiy wrote:

My goal was to compare the performance of the new Slick API with a plain JDBC prepared statements. 

The source code is at https://github.com/apismensky/play-reactive-slick which I forked from https://github.com/gupta-himanshu/play-reactive-slick.

The database I used was PostgreSQL 9.4 with ~ 10000 records in the main table, running in my localhost.

I've added REST API endpoints to this application for Slick 3.0 (v1)  and plain JDBC (v2), so for example 

GET /api/v1/employees controllers.EmployeeControllerSlick.list(p:Int ?= 0, s:Int ?= 2, f ?= "") // uses a Slick API

GET /api/v2/employees controllers.EmployeeControllerJdbc.list(p:Int ?= 0, s:Int ?= 2, f ?= "") // uses plain JDBC

The Slick DAO uses Lifted Embedding queries and doesn't precompile them, so there's a considerable overhead (order of magnitude 1ms for compiling a low- to medium-complexity query in 3.0). It also doesn't use bind variables (unlike the JDBC DAO) and will probably generate more convoluted SQL code (in 3.0 -- it should be fine in 3.1) that has to be parsed each time by the database. If you want to compare the overhead and scalability of Slick's asynchronous API, it's best to stick to the Plain SQL API so that you can use the exact same SQL statements as with JDBC.

With all different settings (number of concurrent threads, with/without connection pool, different connection pool settings JDBC performance is always better. 

With 500 concurrent threads JDBC is ~ 25% faster (see attachment). Moreover, with 2000 concurrent threads error rate of Slick API is ~40% but JBDC has around 2.5%

Don't use 500 or 2000 concurrent threads with Slick's asynchronous executor. That's completely missing the point of the new design where the goal is to keep the number of blocked threads to a reasonable level and handle load more efficiently through queuing. You won't see any advantages compared to JDBC if you use the same number of threads, as both need to be tuned differently. It would make more sense to find the best performance tuning settings for each scenario independently and then compare the results.

What kind of errors do you get? Do they come from the DB from putting it under too much load? Are they generated by Slick because the DB execution queue is full? Then you can increase the queue size (thus allowing more latency in case of bursts of high activity).

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger