I am running a simple INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE ... query using either DataGrip or DBeaver or PHP and it always doesnt register the result.

The SELECT inside takes about 10 minutes and the query always completes (the table is updated) but the program / PHP process doesnt get the result and just waits.

Using SHOW FULL PROCESSLIST I can see the query being executed and after 10 minutes it transforms into "command: sleep" and just hangs there.

My MySQL version is 5.7.33-0ubuntu0.16.04.1 hosted on AWS EC2 and some relevant variables from SHOW VARIABLES:

Variable_name   Value
connect_timeout 3600
delayed_insert_timeout  300
have_statement_timeout  YES
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout    100
innodb_rollback_on_timeout  OFF
interactive_timeout 1800
lock_wait_timeout   31536000
net_read_timeout    3600
net_write_timeout   3600
rpl_stop_slave_timeout  31536000
slave_net_timeout   60
wait_timeout    28800

Any ideas what to change? Any missing configuration to be updated? Thank you

  • 1
    Shouldn't we work on speeding up the SELECT? Provide EXPLAIN and SHOW CREATE TABLE.
    – Rick James
    Sep 2, 2021 at 18:49
  • @RickJames the select could be optimized with a lot of work. its joining a 2+ billion row table with grouping. But my main issue is with the timeout. Its an issue also for other queries and should not be happening so i want to prevent it. i dont think its unreasonable to have 10min running queries when its once a day
    – honzaik
    Sep 2, 2021 at 22:10
  • @honzaik its quite helpful when asking for advice to listen. Frequently indexes of small amounts of rework can be done to solve the problem, if sanely structured. You can't make a query that touches 2+ billion or millions of row to work instantly, so yes. its unreasonable. Provide details of the query.
    – danblack
    Sep 2, 2021 at 22:58
  • Is the php client running on the same system as the DB? Sep 3, 2021 at 0:34

2 Answers 2


(I'll accept that a 2-billion row table could take 10 minutes. Or more.)

Plan A: Replication: Primary + Replica. Do the Selects on the Replica.

Plan B: Sounds like a Summary table might benefit things. With this, you do a not-so-big query each night from the day's data, adding new rows to the Summary Table in much less than 10 minutes. This also benefits the SELECT to get the 'report'. More: http://mysql.rjweb.org/doc.php/summarytables

Standard Deviation

To use a summary table to compute a standard deviation, tally this each day (or whatever unit of time):

COUNT(*)  AS ct
SUM(x)    AS sum_x
SUM(x*x)  AS sum_xx

Then the standard deviation over many days computed via

count:    SUM(ct)       AS N
sum(x):   SUM(sum_x)    AS Sx
sum(x*x): SUM(sum_xx)   AS Sxx


Sx / N  AS mean

SQRT( (Sxx - Sx*Sx/N) / N )      AS pop_stdev
SQRT( (Sxx - Sx*Sx/N) / (N-1) )  AS sample_stdev
  • thanks im already doing that. the problem is Im calculating a standard deviation of a column which is not possible to precalculate day by day (maybe its not true, I think it might be possible with a little work but still the problem stands).
    – honzaik
    Sep 3, 2021 at 8:44
  • it happens with a SELECT SLEEP(700) query as well. so its not a select problem
    – honzaik
    Sep 3, 2021 at 12:11
  • @honzaik - I added details for handling stdev() via summary tables. (Please verify the formulas; I haven't done such in a couple of decades.) If you are stuck with a 10-minute query, you may need a second Replica just for long-running queries.
    – Rick James
    Sep 3, 2021 at 16:05
  • thank you for the effort and the std computation. as far as my issue goes the timeout just disappeared and its working ok now. idk what happened it just fixed itself.
    – honzaik
    Sep 8, 2021 at 10:10

the timeout just disappeared and its working ok now. idk what happened it just fixed itself. no changes to the db have been made

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