Postgres queries slow

Questions / Help
1

Hey, I’m having super slow Postgres interactions.

It’s a remix app and remix uses Prisma ORM. So a query like that:

prisma.event.findUniqueOrThrow({
    where: { id: '1' },
  })

which translates into an SQL query like that:

select * from event where id = '1'

That locally takes a few mills, but in the fly.io deployed app takes like 2s.

The database event table barely has 5 entries.

Any clues?

2

Hey,

Is this still an issue? If it is, can you share the output of fly checks list -a <app-name> here?

3

Yes, it is still.

Here you go, the output you requested:

$ fly checks list -a table-planner-a28b-staging
Health Checks for table-planner-a28b-staging
  NAME                             | STATUS  | ALLOCATION | REGION | TYPE | LAST UPDATED         | OUTPUT                                                               
-----------------------------------*---------*------------*--------*------*----------------------*----------------------------------------------------------------------
  03833b6def760b24d9962af66e7ec077 | passing | 157b304a   | mad    | HTTP | 7s ago               | HTTP GET http://172.19.6.194:8080/healthcheck: 200 OK Output: OK[✓]  
                                   |         |            |        |      |                      |                                                                      
                                   |         |            |        |      |                      |                                                                      
  3df2415693844068640885b45074b954 | passing | 157b304a   | mad    | TCP  | 2022-12-07T17:38:09Z | TCP connect 172.19.6.194:8080: Success[✓]                            
                                   |         |            |        |      |                      |                                                                      
                                   |         |            |        |      |                      |

And this is for the attached db:

$ fly checks list -a table-planner-a28b-staging-db
WARN The running flyctl agent (v0.0.435) is older than the current flyctl (v0.0.437).
WARN The out-of-date agent will be shut down along with existing wireguard connections. The new agent will start automatically as needed.
Health Checks for table-planner-a28b-staging-db
  NAME | STATUS  | MACHINE        | LAST UPDATED         | OUTPUT                                                                   
-------*---------*----------------*----------------------*--------------------------------------------------------------------------
  pg   | passing | 591851e1c67d83 | 2022-12-07T21:36:43Z | [✓] transactions: read/write (211.19µs)                                  
       |         |                |                      | [✓] connections: 11 used, 3 reserved, 300 max (3.27ms)                   
-------*---------*----------------*----------------------*--------------------------------------------------------------------------
  role | passing | 591851e1c67d83 | 2022-11-21T13:57:40Z | leader                                                                   
-------*---------*----------------*----------------------*--------------------------------------------------------------------------
  vm   | passing | 591851e1c67d83 | 20h35m ago           | [✓] checkDisk: 818.38 MB (82.8%) free space on /data/ (70.54µs)          
       |         |                |                      | [✓] checkLoad: load averages: 0.02 0.09 0.14 (106.42µs)                  
       |         |                |                      | [✓] memory: system spent 0s of the last 60s waiting on memory (75.66µs)  
       |         |                |                      | [✓] cpu: system spent 924ms of the last 60s waiting on cpu (59.43µs)     
       |         |                |                      | [✓] io: system spent 1.23s of the last 60s waiting on io (67.83µs)       
-------*---------*----------------*----------------------*--------------------------------------------------------------------------

I was suspecting it was because the db is in LHR region and the app seems to be in MAD. But I don’t really know how to change the app region to LHR too, or if it’s just depending on where the user is.

4

Yeah, you should probably have both in the same region. Here on how to migrate your primary to a new region: High Availability & Global Replication · Fly Docs. Or you could migrate your app’s only instance to lr instead:

  1. fly regions add lhr --app <app-name>
  2. fly scale count 2 --app <app-name>
  3. fly regions remove mad --app <app-name>
  4. If you only want to run one instance scale down with fly scale count 1 --app <app-name>
1 Like
5

Thanks, that’s exactly what I wanted to do, but it’s quite confusing. Let’s see if that improves the speed!

EDIT: Yep it was that, now It’s blazing fast as it should :slight_smile: