mysql going to sleep/sbwait while executing slow query

firestorm · December 30, 2020, 4:18pm

We use mysql for creating inverted index files for our small scientific search engine.

We have a lot of XML files, indexate them to one big (50mln) mysql table.

Then we create tables and files using group by similar rows
(for example:
insert ignore into table (field1, fieldCount) select field,count(*) from table_temp group by field order by field)

So sometimes this query doesn’t work with no errors:
Using command top and iostat I see, that mysql is working, but without using processor (memory and HDD) resources.
MySQL is in state sbwait (waiting for data!!??)

Mysql command show processlist shows, that NULL query is executing.

Mysql logs are ok - no problems.

Parent process (php script, that executed query) is working, also without using resources.

Does everybody know, where is the problem?

Peter · December 30, 2020, 4:27pm

If NULL query is executing in means no query is executing. Perhaps MySQL is waiting for your loader application to submit yet another query.

And your application stalls for some reason and does not do it.

firestorm · December 30, 2020, 4:27pm

Thank you for your answer, Peter!

I know exactly, that executed query is really hard, so query’s execution time should be about 5 hours. - and mysql going to sleep after 10 seconds…
(I see sleeping NULL query while using show processlist and sbwait while using top)

If I send query using mysql from shell I see the same. So it’s not a communication problem between 2 applications…

How can it be? WORKING Null process…

firestorm · December 30, 2020, 4:27pm

Sometimes I see “–” state of process.
And this process really works!

Peter · December 30, 2020, 4:27pm

Are you seeing it in SHOW PROCESSLIST ?

Why do you think it “works” ?

If it is multiple statement operation it you may see this stage while it is going but just when response is sent to the client but client did not yet send second query.

Basically if you do not see the query in the processlist the query is not running or you found very hard bug in MySQL )

Note - the query could be terminated for some reason so even if it is very complex it does not tell much.

firestorm · December 30, 2020, 4:27pm

it’s simple query:
insert ignore into table (field1, fieldCount) select field,count(*) from table_temp group by field1 order by field1)

one special thing: field1 has SET type.

Equal char fields work fine!
(those queries are without “ignore”).

Some other thing: if I don’t use “ignore”, mysql shows

Error Code : 1062
Duplicate entry ‘a,b,c’ for key 1
(1594 ms taken)

Interesting - there is NO ANY KEYS in destination table.

Also I can say, that result is small - something like about 700 records - I need to have unique set combinations from 70mln table (the same operation is used to create very large inverted-index files).

If I send this query using small table (600.000 records) it works fine… - I see query in processlist, everything is fine.

Maybe problem is with small temp table?.. But I see SLEEPING process when the same query works fine on small dataset…

show processlist result:

firestorm · December 30, 2020, 4:27pm

wait_timeout has value 10 - if I’m correct, if query is just sleeping, it should be stopped after 10 seconds.

But in practice, query is executing - execution time is much more then 10 seconds

Peter · December 30, 2020, 4:27pm

The state is “SLEEP” so that query was inactive for over 400 seconds.

You can enable general query log in MySQL and see what application does th the database, also logging queries as they are executed in the applications and returned error codes may be helpful. Error codes are not stored in MySQL Error log file.

wait_timeout may not work for some reason or might be you do not set it correctly.