MySQL Performance: MySQL => mysql going to sleep/sbwait while executing slow query

Members

Help

Home

Home » Performance » MySQL » mysql going to sleep/sbwait while executing slow query

Show: Today's Messages :: Show Polls :: Message Navigator

mysql going to sleep/sbwait while executing slow query [message #472]

Mon, 18 December 2006 09:04

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

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?

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #475 is a reply to message #472 ]

Mon, 18 December 2006 12:19

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #482 is a reply to message #472 ]

Tue, 19 December 2006 15:19

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

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...

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #483 is a reply to message #472 ]

Tue, 19 December 2006 15:30

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

Sometimes I see "--" state of process.
And this process really works!

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #484 is a reply to message #483 ]

Tue, 19 December 2006 16:21

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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 Smile

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

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #485 is a reply to message #472 ]

Tue, 19 December 2006 17:26

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

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:

+----+------------------+-----------+---------+---------+--- ---+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------------+-----------+---------+---------+--- ---+-------+------------------+
| 4 | user | localhost | russian | Sleep | 455 | | NULL |
| 16 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------------------+-----------+---------+---------+--- ---+-------+------------------+

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #486 is a reply to message #484 ]

Tue, 19 December 2006 17:37

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

> Why do you think it "works" ?
I interpreted NULL query with increasing work time and "" state as working query. Is that correct? (I understand, that it can be waiting time, so maybe I'm not exact)

> Note - the query could be terminated for some reason
How can be seen this reason? Sad

I cannot find anyting in logs Sad

Logs show that everyting is ok..

A magical error that is appearing with large datasets and SET fields...

[Updated on: Tue, 19 December 2006 17:42]

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #487 is a reply to message #472 ]

Tue, 19 December 2006 18:03

firestorm
Messages: 9
Registered: September 2006
Location: Moscow

Junior Member

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

Report message to a moderator

Re: mysql going to sleep/sbwait while executing slow query [message #490 is a reply to message #472 ]

Wed, 20 December 2006 04:41

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Degree of Separation Calculation
Next Topic:	Need advise of tuning mysql performance for fast transaction time

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 7 15:06:17 EST 2009

Total time taken to generate the page: 0.01945 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.