2

Having a hard time understanding why PostgreSQL from google cloud services is taking > 400 ms for any simple query (~4-row table) from my PHP code using PDO or pg driver. IP ping averages around 60 ms so it seems it's not a distance problem. Using both PGAdmin and PHP yield around a 400+ ms response time.

$before_conn1 = microtime(true);
$result = pg_query($connector->conn(),$query);
$after_conn1 = microtime(true);

$test = $after_conn1 - $before_conn1;
echo $test;return;

// response time 0.46275 unix timestamp

query looks like this:

SELECT id,clearance from table

literally the table has 4 rows and 2 columns.

Jose Alvarado
  • 31
  • 3
  • What is the time for `select 123;` query? Also, maybe you should edit and post your exact query here, but change names (to prevent security leak). – Top-Master Apr 19 '21 at 18:55
  • 1
    @Top-Master, thanks for the prompt response. Select 123 took 0.4780 – Jose Alvarado Apr 19 '21 at 19:12
  • Then it seems to be an intentional limitation (from Google side), maybe see [another post about this same matter](https://stackoverflow.com/a/54503560/8740349) – Top-Master Apr 19 '21 at 19:21
  • What is $connector->conn() doing? Does it make a fresh connection? If so, do you use a connection pool? – Frank Heikens Apr 19 '21 at 19:31
  • Maybe try again and exclude the time of `$connector->conn()` method (by storing it's result in a variable, before `$before_conn1 = ...` line). – Top-Master Apr 19 '21 at 19:36
  • @FrankHeikens the conn() uses a normal pg_connect call: public function conn(){ $password = "x"; $user = "x"; return pg_connect("x dbname=x user = $user password = $password"); } and also I have a PDO connection setup: $password = "x"; $user = "x"; $this->connection = new PDO("pgsql:host=x;dbname=x;user = $user;password = $password"); both yield same response time. – Jose Alvarado Apr 19 '21 at 19:40
  • First, don't create a new connection for each query, just do it once and reuse that connection. But second, and most important, start using a connection pool. – Frank Heikens Apr 19 '21 at 19:43
  • @FrankHeikens thanks for the observation , I'm using the same variable to query to avoid opening new uneeded connections. Not sure if connection pool work with PHP but I will definitely read more about it. On the other hand Im testing on a single query PHP file and through pgadmin which yield the same response time of ~ 0.5 secs per query "select 123" :/ I'm a bit desperate since this is for a custom ERP website Im developing and requires several back and forth requests to the DB and the wait time can be up to 10 secs for each employee info request... – Jose Alvarado Apr 19 '21 at 19:51

1 Answers1

1

Seems the lag was caused by the PDO driver. I'm not sure exactly why but ended up using pg_query for a single query and pg_send_query for batch selects with the asynchronous response which worked really well! Hope this helps others.

Jose Alvarado
  • 31
  • 3