[Solved] \"Lost connection to MySQL server during query\" in Google Cloud SQL

EverSQL Database Performance Knowledge Base

\"Lost connection to MySQL server during query\" in Google Cloud SQL

Database type:

I am having a weird, recurring but not constant, error where I get "2013, 'Lost connection to MySQL server during query'". These are the premises:

max_allowed_packet    1073741824
slow_query_log    on
log_output    TABLE
log_queries_not_using_indexes on
SELECT users.user_id, users.access_token, users.access_token_secret, users.screen_name, metadata.last_id
FROM users
LEFT OUTER JOIN metadata ON users.user_id = metadata.user_id
WHERE users.enabled = 1

Looking at the logs I see that each time this error presents itself the interval between the start of the query and the error is 15 minutes.

I've also enabled the slow query log and those query are registered like this:

    start_time: 2014-10-27 13:19:04
    query_time: 00:00:00
     lock_time: 00:00:00
     rows_sent: 760
 rows_examined: 1514
            db: foobar
last_insert_id: 0
     insert_id: 0
     server_id: 1234567
      sql_text: ...

Any ideas?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `metadata` ADD INDEX `metadata_idx_user_id` (`user_id`);
ALTER TABLE `users` ADD INDEX `users_idx_enabled` (`enabled`);
The optimized query:
SELECT
        users.user_id,
        users.access_token,
        users.access_token_secret,
        users.screen_name,
        metadata.last_id 
    FROM
        users 
    LEFT OUTER JOIN
        metadata 
            ON users.user_id = metadata.user_id 
    WHERE
        users.enabled = 1

Related Articles



* original question posted on StackOverflow here.