• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to improve select query speed on Spring with Hibernate and PostgreSQL?

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi everyone.

I've a Spring Boot project which I recently managed to deploy on a VPS. I was very surprised when the select queries that were running nearly instantly on my machine slowed down to a crawl there. I knew it would be slower, I just didn't expect by that much.

I've created a test project that reproduces the issue. It selects about 10 000 rows from a table that contains 100 000 rows. It takes about 100 μs to create the query and 0,5–2 s to run the query. I tried to test with 10× more rows, but it took a lot longer than that and eventually threw OutOfMemoryError.

I searched around and it seems it shouldn't take this long, specially since it seems to me like not a lot of data. I read and learned about indexes but even a query that is supposed to be a good example of index usage takes a lot of time.

If you want to test it, download the server and the client projects and follow the instructions there. I started from my real project and removed as much stuff as possible, but left it similar so it can be compared.

---

My VPS specs:

2×2,4 GHz CPU

2 GB RAM



Debian Jessie

Java 8 x64

PostgreSQL 9.4

Spring Boot 2.0.1.RELEASE

---

Thanks in advance.
 
Bartender
Posts: 667
14
TypeScript Fedora
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Out of memory error seems like that would have to do with the actual hardware on your computer hence the error part.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is it possible that on your machine, your code and the database were both running on the same machine? Whereas on your VPS, the database is running on a different machine and the query results have to be sent over the network?
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:Is it possible that on your machine, your code and the database were both running on the same machine? Whereas on your VPS, the database is running on a different machine and the query results have to be sent over the network?



I searched a little but I have no idea if that's possible or common.

Oh and I forgot to say: running the query on psql takes about 100 ms without an index and 20 ms with.
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you got logging in your system (both client and server)?
Do they show you exactly where the delay is in execution?
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Have you got logging in your system (both client and server)?
Do they show you exactly where the delay is in execution?



Only the logging I made myself, with System.nanoTime() to measure start and stop time and System.out.println() to show it.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, but what is it timing?
Is there enough granularity in the log statements to identify the line (or lines) causing the issue?

Just trying to make sure we aren't looking at the wrong issue here.
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:OK, but what is it timing?
Is there enough granularity in the log statements to identify the line (or lines) causing the issue?

Just trying to make sure we aren't looking at the wrong issue here.



I'm timing the time to run entityManager.createQuery("...") (non issue), the time to run repository.find...() (the one that is taking too long, includes the previous timing) and the time to send the request and receive the response (slightly longer than the previous timing).
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK.
Next step.
Can you access the database directly and run the equivalent find query on it?
You should be able to get it to provide stats for the query, rather than just stop watching it (though that would be handy to show if the VPS is slow).

Check both on your local test machine and on the one on the VPS.
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:OK.
Next step.
Can you access the database directly and run the equivalent find query on it?
You should be able to get it to provide stats for the query, rather than just stop watching it (though that would be handy to show if the VPS is slow).

Check both on your local test machine and on the one on the VPS.



Yes, I can.

Local development machine:

Seq Scan on scale_tests  (cost=0.00..15537.00 rows=19654 width=1014) (actual time=0.073..30.300 rows=19908 loops=1)
 Filter: (where_test > 0.8)
 Rows Removed by Filter: 80092
Planning time: 0.507 ms
Execution time: 30.731 ms

VPS:

Seq Scan on scale_tests  (cost=0.00..15536.00 rows=19983 width=1014) (actual time=0.016..105.193 rows=19967 loops=1)
 Filter: (where_test > 0.8)
 Rows Removed by Filter: 80033
Planning time: 0.251 ms
Execution time: 106.425 ms

It should be showing that the index is being used, but it's not. I remember it was being used when I tested before...
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And if you remove those execution times from the earlier times, how much of a discrepancy is left?

If this accounts for almost all of it, then it's the DB that's the issue for some reason.
If there's still a fair chunk of time difference, then you are probably looking at something to do with the VPS or networking (though by the sounds of it the DB and web app sit on the same VPS).
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:And if you remove those execution times from the earlier times, how much of a discrepancy is left?

If this accounts for almost all of it, then it's the DB that's the issue for some reason.
If there's still a fair chunk of time difference, then you are probably looking at something to do with the VPS or networking (though by the sounds of it the DB and web app sit on the same VPS).



There's a big difference between running the query directly and running it in Spring. So you think the VPS is the culprit? Simply upgrading it should do the trick?
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Guilherme Alan Ritter wrote:
There's a big difference between running the query directly and running it in Spring. So you think the VPS is the culprit? Simply upgrading it should do the trick?



Remember, we here are completely detached from what you see.

But if you are getting timings (made up for the sake of example) of something like:

              local machine     VPS
total time     100               1000
time in db    50                 500

then that would imply (to me) that the VPS is simply slower than your local machine.
If those numbers were more like:

              local machine     VPS
total time     100               1000
time in db    50                 850

then that would imply to me that the DB (for whatever reason) was at least pastially responsible.

Note that this is a real "finger in the air" thing, and I would find out exactly what the set up is for your VPS to see whether there is an obvious issue or not before upgrading.
For example, what else shares the VPS hardware.
 
Guilherme Alan Ritter
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:

Guilherme Alan Ritter wrote:
There's a big difference between running the query directly and running it in Spring. So you think the VPS is the culprit? Simply upgrading it should do the trick?



Remember, we here are completely detached from what you see.

But if you are getting timings (made up for the sake of example) of something like:

              local machine     VPS
total time     100               1000
time in db    50                 500

then that would imply (to me) that the VPS is simply slower than your local machine.
If those numbers were more like:

              local machine     VPS
total time     100               1000
time in db    50                 850

then that would imply to me that the DB (for whatever reason) was at least pastially responsible.

Note that this is a real "finger in the air" thing, and I would find out exactly what the set up is for your VPS to see whether there is an obvious issue or not before upgrading.
For example, what else shares the VPS hardware.



Seems to me, considering other tests I made, the second example is more likely to be the case.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah, right.
That was what I was asking above.
After removing the time in DB do you still see a large difference.
It sounds like "no".

So there's something about the DB then, at least in large part.

I don't really know Postgre, so beyond ensuring indices are set up correctly I couldn't say what the next step would be.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic