Databases
StackOverflow2013
Postsdbo
POMySQL query randomly hangs on 'sending data' status?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query randomly hangs on 'sending data' status?
    primarykey
    data
    text
    <p>I am using a MySQL 5.5.25 server, and InnoDB for my databases.</p> <p>Quite often the CPU of the server is working at 100% due to a mysqld process for roughly a minute. Using SHOW PROCESSLIST:</p> <pre><code>Command | Time | State | Info Query | 100 | Sending data | SELECT a.prefix, a... Query | 107 | Sending data | SELECT a.prefix, a... Query | 50 | Sending data | SELECT a.prefix, a... </code></pre> <p>The problematic query is:</p> <pre><code>SELECT a.prefix, a.test_id, a.method_id, b.test_id FROM a LEFT JOIN b ON b.test_id = a.test_id AND user_id = ? AND year = ? </code></pre> <p>All these columns are INDEXED, thus this ain't the problem. Also, when I run the query in phpMyAdmin (with a sufficient LIMIT), it takes 0.05 seconds to complete. Also, it is quite frustrating that I find it to be impossible to reproduce this problem myself, even when executing this query twice simultaneously and spamming it only gives me spikes to 40% CPU.</p> <p>Prefixing the query with EXPLAIN results in:</p> <pre><code>Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1169 | 1 | SIMPLE | b | ref | user_id,year,test_id | test_id | 4 | db.a.test_id | 58 | </code></pre> <p>Unless I cannot see what is right in front of me, I am looking for ways to discover how to debug this kind of problem. I do already log all queries with their time to execute, and their values etc. But since I cannot reproduce it, I am stuck. What should I do to figure out what this problem is about?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USYeti
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COwhat is the size of your table in terms of number of rows. Disable query cache if it is enabled and then reproduce. What cpu you have, number of threads etc. exucute show engine innodb status\G, loog for deadlocks youngs/non youngs and then start digging into the problem...
      singulars
      1. POMySQL query randomly hangs on 'sending data' status?
      1. USMasood Alam
    2. COThanks for your comment, if it was an answer instead of a comment I would have accepted it.
      singulars
      1. POMySQL query randomly hangs on 'sending data' status?
      1. USYeti
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload