[Solved] Mysql exceeds system ram when doing update select

EverSQL Database Performance Knowledge Base

Mysql exceeds system ram when doing update select

Database type:

I am running a mysql server on a mac pro, 64GB of ram, 6 cores. Table1 in my schema has 330 million rows. Table2 has 65,000 rows. (I also have several other tables with a combined total of about 1.5 billion rows, but they are not being used in the operation I am attempting, so I don't think they are relevant).

I am trying to do what I would have thought was a relatively simple update statement (see below) to bring some data from Table2 into Table1. However, I am having a terrible time with mysql blowing through my system ram, forcing me into swaps, and eventually freezing up the whole system so that mysql becomes unresponsive and I need to restart my computer. My update statement is as below:

UPDATE Table1, Table2 
SET 
  Table1.Column1 = Table2.Column1, 
  Table1.Column2 = Table2.Column2, 
  Table1.Column3 = Table2.Column3, 
  Table1.Column4 = Table2.Column4 
WHERE 
  (Table1.Column5 = Table2.Column5) AND 
  (Table1.Column6 = Table2.Column6) AND 
  (Table1.Column7 = Table2.Column7) AND 
  (Table1.id between 0 AND 5000000);

Ultimately, I want to perform this update for all 330 million rows in Table1. I decided to break it up into batches of 5 million lines each though because

Here are some more relevant details about the situation:

Therefore, I am wondering:

  1. Is the syntax of how I am writing this update statement somehow horribly bad and inefficient such that it would explain the horrible performance and problems?
  2. Are there some kind of parameters beyond the innodb_buffer_pool_size that I should be configuring, either to put a firmer cap on the ram mysql uses or to get it to more effectively use resources?
  3. Are there other sorts of diagnostics I should be running to try to detect problems with my tables, schema, etc.?
  4. What is a "reasonable" amount of time to expect an update like this to take?

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 `Table1` ADD INDEX `table1_idx_column5_column6_column7_id` (`Column5`,`Column6`,`Column7`,`id`);
ALTER TABLE `Table2` ADD INDEX `table2_idx_column5_column6_column7` (`Column5`,`Column6`,`Column7`);
The optimized query:
SELECT
        Table1.Column1 
    FROM
        Table1,
        Table2 
    WHERE
        (
            Table1.Column5 = Table2.Column5
        ) 
        AND (
            Table1.Column6 = Table2.Column6
        ) 
        AND (
            Table1.Column7 = Table2.Column7
        ) 
        AND (
            Table1.id BETWEEN 0 AND 5000000
        )

Related Articles



* original question posted on StackOverflow here.