2

I'm having trouble with a big INSERT...SELECT in my DB. For context, the targeted table already have around a million entries and my SELECT query return approximately 2 millions. On a production server, the query execute in 20min.

First question: is it slow or average considering the size of the base?

Second question (if slow): here the request, is there room for improvement (I think there is)? I'm considering using chunking but I'm not sure how.

INSERT INTO output 
(id_people, id_product) 

SELECT p.id_people, 146 
FROM people p 
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46') 
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c 
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46')) 
INNER JOIN company co
  ON (co.id_company = c.id_company)

WHERE g.type = 'OD' 
AND g.start < '2017-08-09 13:29:12.46' 
AND g.end > '2018-08-09 13:29:12.46'

ON DUPLICATE KEY UPDATE 

Any hint will be very welcome :)

  • Queries are slow because they do not fit memory buffers and performed via on-disk temporary tables. Queries are often has no appropriate index(es) that cause filesort or brute-force search that also cause huge memory consumption and fallback to the disk i/o. – Kondybas Aug 10 '17 at 13:25
  • For best results, provide the output of EXPLAIN PLAN, and a description of key table elements (CREATE TABLE statements, all indexes on the tables, any triggers that may be on output). In addition, it may help to run the SELECT on its own, instead of as part of the INSERT, to see how long that takes. If that takes 19 minutes, then the SELECT is probably the source of the issue - if the SELECT on its own finishes in 5 minutes, then it's probably something about the INSERT. – RDFozz Aug 10 '17 at 15:09
1

You may not be aware of this, but did you know that MySQL allocates a distinct join buffer for each additional JOIN clause ?

According to the MySQL Documentation for join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.

When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.

The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).

For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

This is also declared in Block Nested-Loop Join Algorithm

MySQL join buffering has these characteristics:

Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or range. Use of buffering is also applicable to outer joins, as described in Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

  • A join buffer is never allocated for the first nonconstant table, even if it would be of type ALL or index.

  • Only columns of interest to a join are stored in its join buffer, not whole rows.

  • The join_buffer_size system variable determines the size of each join buffer used to process a query.

  • One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers.

  • A join buffer is allocated prior to executing the join and freed after the query is done.

Based on this, you must increase the join_buffer_size in your session just before running your INSERT ... SELECT. From the look of the join's ON clauses, I don't foresee indexes ever being selected by the query optimizer.

I cannot tell you how big to make the join_buffer_size. You will have to experiment with it and run the EXPLAIN plan to see if the SELECT will avoid writing to disk.

WARNING : According to the last paragraph of MySQL Documentation on Nested-Loop Join Algorithms:

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.

This why you should experiment with join_buffer_size.

HOW TO EXPERIMENT TO join_buffer_size

You can change the join_buffer_size one MB at a time. Then, run the SELECT and time it.

STEP 01 : Set an initial size of 0 and initial runtime of 48 hours (172800 seconds)

SET @jbsize = 0;
SET @prev_runtime = 172800;

STEP 02 : Increment 1MB

SET @jbsize = jbsize + 1;
SET SESSION join_buffer_size = @jbsize * 1048576;

STEP 03 : Time the SELECT

SET @t1 = UNIX_TIMESTAMP();
SELECT p.id_people, 146 
FROM people p 
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46') 
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c 
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46')) 
INNER JOIN company co
  ON (co.id_company = c.id_company)
WHERE g.type = 'OD' 
AND g.start < '2017-08-09 13:29:12.46' 
AND g.end > '2018-08-09 13:29:12.46'
SET @t2 = UNIX_TIMESTAMP();

STEP 04 : Compute Time

SET @curr_runtime = @t2 - @t1;
SET @diff_runtime= @prev_runtime - @curr_runtime;
SELECT
    sec_to_time(@prev_runtime)     Previous_Running_Time,
    sec_to_time(@curr_runtime)      Current_Running_Time,
    sec_to_time(@diff_runtime) Improvement_From_Last_Run
;

STEP 05 : Decision Time

If Improvement_From_Last_Run is big in terms of hours, minutes, and seconds and you feel making join_buffer_size bigger would improve the run time, loop back to STEP 02. Keep doing this until Improvement_From_Last_Run is 00:00:00.

GIVE IT A TRY !!!

0
group:  INDEX(type, date_cancel)
sell:   INDEX(id_group)
people: INDEX(id_sell)
company -- remove from the query since it seems to be unused.

Think about getting rid of redundant information such as status: CANCELED and date_cancel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.