MySQL configuration. Query doesnt get returned result (DataGrip, DBeaver, PHP). Something times out [closed]

Question

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

Answer 1

(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

Then

Sx / N  AS mean

SQRT( (Sxx - Sx*Sx/N) / N )      AS pop_stdev
SQRT( (Sxx - Sx*Sx/N) / (N-1) )  AS sample_stdev

Answer 2

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