Mysql – Improve speed of GCP SQL Failover

google-cloud-sqlMySQL

I've been trying out GCPs SQL high availability options. Is there a way to improve the fail over speed?

Attempting manual failovers through the UI takes literally over 5 minutes where mysql is completely unavailable.

I am using the smallest second generation 5.7 instances possible currently with no traffic hitting it.

It looks like it's actually stopping and starting instances. I'm having trouble seeing how this is an option for "HA" meaning minimal downtime.

Best Answer

Unfortunately you are at the mercy of the managed service provider there. GCPs managed database offering isn't that great, they lag quite far behind the others, they want to steer you onto BigTable, BigQuery and Cloud Spanner. If you are committed to GCP and have a hard requirement for HA then your best option is probably Spanner. – Gaius

Related Solutions

Mysql – How to improve speed of MYSQL query

While I don't do a lot of work in MySQL, at the least I can suggest a simplification of the query. In the FROM clause of the main query, JOIN the target tables directly, instead of making a subquery from each of the tables to join:

FROM
    caf_student_cards AS caf
    LEFT JOIN students AS student ON caf.user_id = student.user_id
    LEFT JOIN utilizations AS util ON caf.user_id = util.user_id
    LEFT JOIN system_users AS users ON caf.user_id = users.user_id

NOTE: You'll lose your renaming of util.resource_id, so you'll also need to change:

    util.room,

to:

    util.resource_id as room,

I can't think of any potential benefits of doing things the way you have (certainly not when your subquery is doing a SELECT *), and I've seen references that lead me to believe MySQL may be materializing the subqueries (meaning it's creating a copy of the table with the columns you list), which is likely to cause unnecessary extra work and resource consumption.

MySQL Performance – Does innodb_file_per_table Improve Alter Table Speed?

innodb_file_per_table is convenient option to manage disk space used by innodb, but it won't improve ALTER TABLE time.

As of 5.6 InnoDB supports online DDL for some kind of ALTERs. Check Online DDL page to see what's currently supported.

If not, you may consider using pt-online-schema-change or similar tool(https://github.com/github/gh-ost, https://github.com/facebookincubator/OnlineSchemaChange). It will run long time, but won't block the table.

Best Answer

Related Solutions

Mysql – How to improve speed of MYSQL query

MySQL Performance – Does innodb_file_per_table Improve Alter Table Speed?

Related Question