2

Why might MySQL not execute a query? Why might it freeze? It simply sits there and does nothing. Show processlist gives a result of "sending data" for as many hours as it takes for me to give up and restart MySQL after which the query runs in a fraction of a second. The log yields no errors. Explain shows the joining tables and the keys they are matched on.

act and cls are indexed primary on L_ID and normal s_ID

MySQL freezes when attempting to run a query unless I restart MySQL before running it. I hope someone here might be able to give me an idea as to why or better still what to do about it.

I have a local host running XAMPP and a query that crunches data locally that looks like this:

INSERT INTO table
SELECT
    act.P_ID,
    act.L_ID,
    act.L_Price,
    act.A,
    act.C,
    round(((count(*) / sum(1/(cls.S_Price / cls.Sq))* act.Sq)+avg(cls.S_Price))/2) AS abc,
    STDDEV((cls.S_Price / cls.Sq))/avg((cls.S_Price / cls.Sq)) AS std_dev, 
    count(*) as `samp`,
    GROUP_CONCAT(DISTINCT cls.L_ID) as comps,
    @datenow as `Timestamp`,
    '1' AS B_score
FROM
    act_table AS act
JOIN cls_table AS cls ON cls.S_ID = act.S_ID
AND cls.R = "val"
AND cls.S = "val"
AND cls.Sq BETWEEN act.Sq *.2 AND act.Sq * 1.8
AND cls.YB BETWEEN act.YB - 10 AND act.Year_Built + 12
AND cls.pv = act.pv
AND cls.PP = act.PP
AND cls.Bd = act.Bd
AND cls.th = act.th
AND cls.C_D > now()-interval 3 month
AND act.s_ID !=""
GROUP BY
    act.P_ID
HAVING `samp` > 1
AND std_dev <= .1;

I can run it one time. If I need to run it again I have to restart the MySQL server in XAMPP. The cls table has around 40,000 records and act has around 7,000. After restarting MySQL it executes in 0.674s.

I run the query from Navicat, a graphical database management software.

SHOW ENGINE InnoDB STATUS

Gives a result of:

=====================================
2016-01-20 07:49:30 2aa0 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1893 srv_active, 0 srv_shutdown, 225772 srv_idle
srv_master_thread log flush and writes: 227665
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 52114
OS WAIT ARRAY INFO: signal count 95463
Mutex spin waits 264459, rounds 1248653, OS waits 21862
RW-shared spins 93765, rounds 1229580, OS waits 21549
RW-excl spins 93314, rounds 612670, OS waits 7456
Spin rounds per wait: 4.72 mutex, 13.11 RW-shared, 6.57 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 15954141
Purge done for trx's n:o < 15954137 undo n:o < 0 state: running but idle
History list length 588
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 163, OS thread handle 0x2aa0, query id 7705288 optimus-PC 10.10.102.114 jemima init
SHOW ENGINE InnoDB STATUS
---TRANSACTION 15954140, ACTIVE 5 sec fetching rows
mysql tables in use 6, locked 6
392 lock struct(s), heap size 27512, 27219 row lock(s)
MySQL thread id 161, OS thread handle 0x2ef4, query id 7705282 optimus-PC 10.10.102.114 jemima Sending data
INSERT INTO table
SELECT
    act.P_ID,
    act.L_ID,
    act.L_Price,
    act.A,
    act.C,
    round(((count(*) / sum(1/(cls.S_Price / cls.Sq))* act.Sq)+avg(cls.S_Price))/2) AS abc,
    STDDEV((cls.S_Price / cls.Sq))/avg((cls.S_Price / cls.Sq)) AS std_dev, 
    count(*) as `samp`,
    GROUP_CONCAT(DISTINCT cls.L_ID) as comps,
    @datenow as `Timestamp`,
    '1' AS B_score
FROM
    act_table AS act
JOIN cls_table AS cls ON cls.S_ID = act.S_ID
AND cls.R = "val"
AND cls.S = "val"
AND cls.Sq BETWEEN act.Sq *.2 AND act.Sq * 1.8
AND cls.YB BETWEEN act.YB - 10 AND act.Year_Built + 12
AND cls.pv = act.pv
AND cls.PP = act.PP
AND cls.Bd = act.Bd
AND cls.th = act.th
AND cls.C_D > now()-interval 3 month
AND act.s_ID !=""
GROUP BY
    act.P_ID
HAVING `samp` > 1
AND std_dev <= .1;
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
2810151 OS file reads, 733114 OS file writes, 63302 OS fsyncs
54.08 reads/s, 16384 avg bytes/read, 13.32 writes/s, 1.76 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 7149, seg size 7151, 7251 merges
merged operations:
 insert 122683, delete mark 1668046, delete 28597
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2212699, node heap has 881 buffer(s)
309907.24 hash searches/s, 5169.03 non-hash searches/s
---
LOG
---
Log sequence number 3963944279169
Log flushed up to   3963944279169
Pages flushed up to 3963944279169
Last checkpoint at  3963944279169
0 pending log writes, 0 pending chkp writes
21912 log i/o's done, 0.68 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 544210944; in additional pool allocated 0
Dictionary memory allocated 1038260
Buffer pool size   32768
Free buffers       1024
Database pages     30862
Old database pages 11372
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1155251, not young 48421006
32.20 youngs/s, 7462.94 non-youngs/s
Pages read 2798392, created 408832, written 667825
54.08 reads/s, 0.64 creates/s, 12.20 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 7 / 1000
Pages read ahead 33.28/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 30862, unzip_LRU len: 0
I/O sum[746]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 3936, state: sleeping
Number of rows inserted 4309763, updated 8216503, deleted 12560, read 4268362560
42.60 inserts/s, 0.00 updates/s, 0.00 deletes/s, 321801.25 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
  • (Unrelated) The JOIN inflates the number of rows, then the GROUP BY deflates it, but meanwhile the AVG, STD_DEV, etc, are computed from the inflated list, thereby coming up with potentially skewed values. – Rick James Feb 3 '16 at 21:07
  • Also, seems like 0.1 feels like a very small stdev. – Rick James Feb 3 '16 at 21:08
  • Thanks Rick. I appreciate your interest. The act.P_ID is a unique id so when grouped on, the average is calculated on the cls.S_Price of the ones that match the unique act.P_ID. I've verified that no additional records are duplicated from the comparison. – Aunt Jemima Feb 6 '16 at 2:25
  • std_dev is on a percentage basis. If the standard deviation is 1 and the average is 10 it would be a std_dev of .1. The reason is that the prices can be very diverse and we want to know how tight the sample is. – Aunt Jemima Feb 6 '16 at 2:28
  • If I am not mistaken, in a "normal distribution", only 8% of points are within 0.1 standard deviation of the mean. – Rick James Feb 6 '16 at 3:45
2
  • Replace @datenow with CURRENT_DATE().

  • Add composite INDEX(R, S, C_D) to cls.

  • Do not "restart" -- look at the log; look at SHOW PROCESSLIST; look at SHOW ENGINE InnoDB STATUS.

  • Thanks for responding. There's nothing in the log. Show processlist simply states sending data for hours. I'll check SHOW ENGINE InnoDB STATUS, although it's funny you correctly assumed the engine type. I should have stated it in my question. – Aunt Jemima Jan 20 '16 at 4:11
  • How many rows in each table? How many rows before the GROUP BY and HAVING shrink the resultset? – Rick James Feb 3 '16 at 21:10

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.