CODEWITHSUNDEEP
phpmysqlperformancegcloud
Adrian Everardo Segura Garcia
Adrian Everardo Segura Garcia

Reputation: 35

My Google Cloud SQL instance connection and/or queries are too slow

In my job we have the task to see if we can migrate our App database from our current server to a Google Cloud SQL instance. I created my GCP, configured the instance and imported the database.

The issue is that I have already connected from my project (a CakePHP localhost App) to the database in gcloud and after ran some queries I noticed that they are taking a lot of time and the render of the view is very slow. I have already upgraded the storage of the instance (to have a better read/write speed) and the cores/memory with no results.

Here are some queries and the time they took:

Query.                                             Time.    Affected.

GCloud
SELECT * FROM clients WHERE company_id = 3711;     50ms.    3rows.

Localhost
SELECT * FROM clients WHERE company_id = 3711;     1ms.     3rows.

And also the CakePHP DebugKit timer response in one of my views:

GCloud    - Controller Action ---- 317.55 ms.
Localhost - Controller Action ---- 7.44   ms.

Another view took 3+ seconds using GCloud and 164 ms from my localhost.

I used the us-central1 zone, already upgraded the SSD, cores, RAM, and used small queries like this one to no avail. I think that maybe it has something to do with the latency, but I don't have the slightiest idea.

Im querying from Mexico, to be specific from Jalisco.

My instance is a 2 core with 13gb of ram db-n1-highmem-2

in my localhost SELECT @@log_binreturns a 0.

Files:

php.ini

my.cnf

SHOW GLOBAL STATUS

SHOW GLOBAL VARIABLES

SHOW FULL PROCESSLIST

EXPLAIN SELECT SQL_NO_CACHE * FROM clients WHERE company_id = 3711;

1   SIMPLE  clients NULL    ref find_by_id,clients_modified,company_id_3,clients_by_creator,clients_by_owner    find_by_id  4   const   3   100.00  Using where

SHOW INDEX FROM clients;

Table   non_unique  key_name            seq_in_index    column_name         collation   cardinality sub_part    packed  null    index_type
clients 0           PRIMARY             1               id                  A           685571      NULL        NULL            BTREE       
clients 0           PRIMARY             2               user_id             A           685571      NULL        NULL            BTREE       
clients 0           PRIMARY             3               company_id          A           685571      NULL        NULL            BTREE       
clients 0           find_by_id          1               company_id          A           1159        NULL        NULL            BTREE       
clients 0           find_by_id          2               id                  A           685571      NULL        NULL            BTREE       
clients 1           clients_modified    1               company_id          A           1587        NULL        NULL            BTREE       
clients 1           clients_modified    2               modified            A           512088      NULL        NULL    YES     BTREE       
clients 1           company_id_3        1               company_id          A           1254        NULL        NULL            BTREE       
clients 1           company_id_3        2               prospectus          A           3642        NULL        NULL            BTREE       
clients 1           company_id_3        3               last_update         A           303012      NULL        NULL    YES     BTREE       
clients 1           clients_by_creator  1               company_id          A           1503        NULL        NULL            BTREE       
clients 1           clients_by_creator  2               user_id             A           3577        NULL        NULL            BTREE       
clients 1           clients_by_owner    1               company_id          A           1423        NULL        NULL            BTREE       
clients 1           clients_by_owner    2               user_assigned_id    A           3949        NULL        NULL            BTREE       
clients 1           company_id_3        3               last_update         A           303012      NULL        NULL    YES     BTREE       
clients 1           clients_by_creator  1               company_id          A           1503        NULL        NULL            BTREE       
clients 1           clients_by_creator  2               user_id             A           3577        NULL        NULL            BTREE       
clients 1           clients_by_owner    1               company_id          A           1423        NULL        NULL            BTREE       
clients 1           clients_by_owner    2               user_assigned_id    A           3949        NULL        NULL            BTREE

Upvotes: 3

Views: 412

Answers (1)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Rate Per Second=RPS - Suggestions to consider for your Google Cloud Database flags - my.cnf [mysdld] section

innodb_lru_scan_depth=100  # from 2048 to conserve 95% CPU cycles used for function
innodb_flush_neighbors=0  # from 2 with SSD storage no need to look for neighbors
innodb_buffer_pool_size=4G  # from ~ 10G to support less than 1G of data, reduce mgmt cycles
innodb_old_blocks_time=20000  # from 1000 to reduce select_scan RPhr of 1,395

Provide feedback, please after implementation + 24 hours of use.

Upvotes: 1

Related Questions