1

We're designing an API Rest with the following technologies :

  • spring-boot 1.5.7
  • spring-data-jpa
  • MySQL 5.5.59

It's a Java REST API designs with spring-boot and connected to a MySQL database via a mysql-connector. Most of the queries in repositories are written in the JPA Query way. To compile it we use maven and we deploy it in fatjar running in a tomcat sever.

The fact is several tables and resources within can be very large for one user. So when a GET request is made with high offset, we can deal with slow queries.

We tried several solutions like :

  • set tomcat parameters for the mysql connection
  • use hikari but no available parameters to kill slow queries
  • deploy the rest api in a glassfish5 server but the spring data jpa queries throw an execption (could not extract result set), so it's a problem.

Moreover, a lot of people gonna say me "use pagination and check the offset in the request". The fact is that we're using that but the class PageRequest have a tricky behaviour. Indeed, the resource the API give you back with this class has the shape below : 

{
  "content": {},
  "links": [
    {
      "href": "string",
      "rel": "string",
      "templated": true
    }
  ],
  "page": {
    "number": 0,
    "size": 0,
    "totalElements": 0,
    "totalPages": 0
  }
}

As you can see you have an object page with a field totalElements. This is the problem if you have a lot of entries in a table for one user. Because spring data gonna do a "SELECT COUNT(*)" to have this totalElements information. Pagination is not the solution cause we arleady have thid.

So what we're asking you are the best practices and maybe the solution to handle and kill slow queries in a Restful API designs with spring-boot.

Thanks !

le_cug
  • 49
  • 10
  • REST API's should be stateless.. in theory that means it should not keep a state with a session or managing query executing times. – Raymond Nijland Mar 05 '18 at 16:08
  • @RaymondNijland Yes I agree but either if your API is stateless and you do a GET request on a very big table, the mysql is faced with a slow query and this is bad for the good behavior of your entire application. Therefore, is not very a question about the rest api itself but about how to have the best configuration of spring and the connection with mysql. – le_cug Mar 05 '18 at 16:17
  • There is a solution named paging. – Patrick Mar 05 '18 at 16:37
  • You mentioned offset which seems to be a parameter to your request. How about validating value of that parameter and rejecting requests with offset out of bounds? – Mateusz Mrozewski Mar 05 '18 at 17:57
  • @MateuszMrozewski Post edit about pagination ;) – le_cug Mar 06 '18 at 08:36
  • @Patrick Post edit about pagination :) – le_cug Mar 06 '18 at 08:36
  • The fact guys is that is not a problem about how we made and code our API. Even if you have strong check and pagination, your API is always subject to deal with slow queries caused by your database. I just want to know if there are best practices to handle these in the API and throw an exception if a slow query shows up. – le_cug Mar 06 '18 at 08:55

2 Answers2

1

Regarding the pagination have a look at this answer: Way to disable count query from PageRequest for getting total pages?

Basically return a List and not a Page. This of course will not give you the total so you will need to handle the situation differently. It might require one extra query for next page that will return an empty list but you will save the count on each page.

If you however still need the total for some reason you will have to do the count.

Regarding your comment "slow queries caused by your database": it is not the database that generates the slow queries. Slow queries are generate by you and your code. There might be solutions to your problems other than killing the running queries but you would need to provide more information about your database: structure, number of records, exact queries issues, what indexes are in place, etc. Maybe you could introduce caching?

If you still insist on killing the query you could run in inside a CompletableFuture and await for completion with timeout: https://docs.oracle.com/javase/8/docs/api/java/util/concurrent/CompletableFuture.html

Mateusz Mrozewski
  • 2,151
  • 1
  • 19
  • 28
  • You're right sory I misspoke about "slow queries caused by your database". To resume you the context, our customers send sms, so our biggest customers send like million of sms by week. Therefore, for example the table MESSAGES might be huge for them and the SELECT COUNT(*) of the PageRequest is slow. I think the best practice is like you said to just have informations about if there is a previous or next page. @M – le_cug Mar 06 '18 at 09:47
  • Sure, just wanted to clarify that to avoid misunderstandings :) Have a look at the answer if it could be useful. If not, more information would be needed from you. – Mateusz Mrozewski Mar 06 '18 at 09:49
  • Yes, it seems that you won't need an exact number of records, especially if you show records page by page. I guess it might be needed in some kind of a dashboard or reports, there you would need a count. You could also show an estimated count (update it every few minutes, won't be precise, but might be enough). – Mateusz Mrozewski Mar 06 '18 at 09:57
  • Even in the connected platform online, is not a problem if the table of messages has not the informations about totalElements and totalPages, just Previous and Next buttons are needed ! I have to fin a way to create a resource with the list of messages and add an object with the number of the next and previous page if there is one. – le_cug Mar 06 '18 at 10:13
1

After a lot of research, I found a solution to my problem simply by reading focusely the tomcat datasource documentation. I found the jdbc interceptors I have set in my application.yml as below : 

jdbc-interceptors: QueryTimeoutInterceptor(queryTimeout=20);SlowQueryReport(threshold=20000,logFailed=true)

So this do what I want, e.g that throws an exception if my query exceed 20 secs and kill it !

Thanks anyway for your help !

le_cug
  • 49
  • 10