Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My MySQL 5.1 bases Intranet system is facing very bad performance during high load. When observe through MonYog, I found below thread states: enter image description here

We can simply understand the different thread state by its name. Can someone provide the

  • differences among these states?
  • Why "sending data" is taking 76%?
share|improve this question
    
Sending data is not a very accurate description, since it means a lot more things than actually/only "sending data," as Rolando's answer, below, points out: "the thread is reading and processing rows." A full table scan will also display this state, even though the server may not actually be "sending" anything at all. Have you examined the slow query log for queries that need optimization? –  Michael - sqlbot Feb 11 at 22:10
    
Yes Michael, I have examined the slow query log. During this period, multiple queries are getting inserted in slow query logs. In normal time the query taking 1 second, taking >=20 seconds during this period. Also, a lot of long sleep connections are there. Most strange is that - tables involved in these queries are using indices properly. Even, taking too much time. –  ursitesion Feb 12 at 5:43

1 Answer 1

up vote 0 down vote accepted

differences among these states?

The MySQL Documentation provides a list of those states.

From your MONYog graph, here are the states:

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Copying to tmp table

The server is copying to a temporary table in memory.

Sorting result

For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.

freeing items

The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up.

Why "sending data" is taking 76%?

As the Documentation says, data is being sent to the client.

What would attribute to the high load ?

  • Queries that return lots of rows
  • Lots of queries

Queries that return lots of rows

If you have queries returning lots of rows, tune your queries to return less data, perhaps adding effective WHERE and LIMIT clauses to SELECTs.

Lots of queries

If you do not have queries returning lots of rows, then it must be lots of queries. You may find this surprising, but MONYog queries mysqld for the global status variables with either

SHOW GLOBAL VARIABLES;

or

SELECT * FROM information_schema.global_status;

These constitute queries as well.

Perhaps you could configure MONYog to retrieve status information less often.

share|improve this answer
    
Thanks Rolando: Your answers are always awesome. –  ursitesion Feb 12 at 6:23
    
I usually collects global variables values during this period. I will certainly look on the options provided by you - Queries that return lots of rows and Lots of queries. Do you think that high users load also affect the bad performance. Rolando, Why there are too many sleep connections and taking too much time? –  ursitesion Feb 12 at 6:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.