List:General Discussion« Previous MessageNext Message »
From:Tauren Mills Date:February 26 2002 12:48am
Subject:RE: CPU intensive query
View as plain text  
Thanks for the assistance!

> Seems hard to answer without asking more questions, but some
> basic questions first:
>
>     Is complex_id indexed in both tables? (SHOW INDEX FROM aptreviews)

Yes, they are:

mysql> SHOW INDEX FROM aptreviews;
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
| aptreviews |          0 | PRIMARY  |            1 | review_id   | A
|        NULL |     NULL | NULL   |         |
| aptreviews |          0 | PRIMARY  |            2 | complex_id  | A
|       15272 |     NULL | NULL   |         |
+------------+------------+----------+--------------+-------------+---------
--+-------------+----------+--------+---------+
2 rows in set (0.00 sec)

mysql> SHOW INDEX FROM aptcomplexes;
+--------------+------------+----------+--------------+-------------+-------
----+-------------+----------+--------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+--------------+------------+----------+--------------+-------------+-------
----+-------------+----------+--------+---------+
| aptcomplexes |          0 | PRIMARY  |            1 | complex_id  | A
|       35395 |     NULL | NULL   |         |
+--------------+------------+----------+--------------+-------------+-------
----+-------------+----------+--------+---------+
1 row in set (0.00 sec)

>     Have you tried EXPLAIN'ing the query to see if maybe the index
>     isn't being used?

mysql> EXPLAIN select count(*) from aptreviews, aptcomplexes where
aptreviews.complex_id = aptcomplexes.complex_id;
+--------------+--------+---------------+---------+---------+---------------
--------+-------+-------------+
| table        | type   | possible_keys | key     | key_len | ref
| rows  | Extra       |
+--------------+--------+---------------+---------+---------+---------------
--------+-------+-------------+
| aptreviews   | index  | NULL          | PRIMARY |      12 | NULL
| 15272 | Using index |
| aptcomplexes | eq_ref | PRIMARY       | PRIMARY |       8 |
aptreviews.complex_id |     1 | Using index |
+--------------+--------+---------------+---------+---------+---------------
--------+-------+-------------+
2 rows in set (0.00 sec)

> Questions:
>
>     That the queries are stuck on 'Sending data' seems to
> indicate that the
>     client isn't picking up the data, not necessarily that the server is
>     busy churning away at retrieving the count.

Ahhh... that helps.  They are probably doing this from a JDBC connection
(java object).  I'll explore the possibility that something is wrong with
their code as well.

>     What other queries are running? It seems strange that just two queries
>     in the 'Sending data' state would take up double digit cpu load on
>     Linux.

Yes, I agree.  Doing a "top" has shown only a couple mysql processes at the
top.  Doing a "mysqladmin processlist" shows only a couple queries in an
active state.  Yet this morning everything completely came to a halt with a
load of 44!

However, I have seen some other queries in this state, but I don't have them
written down.  I'll watch for them again.

>     What's the output of ``mysqladmin status''?

Uptime: 174824  Threads: 61  Questions: 2981844  Slow queries: 288  Opens:
7143  Flush tables: 1  Open tables: 64 Queries per second avg: 17.056

Thanks for the help!  Since doing the query in the mysql client is so fast,
I'm going to concentrate on a problem with the java connection to it for
now.  Unless what I've sent above raises any concerns for anyone on the
list.

Tauren


> On Mon, Feb 25, 2002 at 03:24:25PM -0800, Tauren Mills wrote:
> > I am hosting websites that use the mysql database.  One of my
> customers has
> > a query that they run occasionally that really bogs down the
> entire server.
> > The load sometimes jumps up into the double digits (on a Red Hat 6.2
> > server).
> >
> > Here is their query:
> >
> > select count(*) from aptreviews, aptcomplexes where
> aptreviews.complex_id =
> > aptcomplexes.complex_id
> >
> > When I execute this query in the mysql client, it returns
> instantaneously:
> >
> > mysql> select count(*) from aptreviews, aptcomplexes where
> > aptreviews.complex_id = aptcomplexes.complex_id;
> > +----------+
> > | count(*) |
> > +----------+
> > |    15257 |
> > +----------+
> > 1 row in set (0.18 sec)
> >
> > However, when checking on long-running processes with "mysqladmin
> > processlist", this query doesn't go away for a long time:
> >
> > | 18823 | webs | localhost.localdomain | webs_aptrate  | Query
>  | 1     |
> > Sending data | select count(*) from aptreviews, aptcomplexes where
> > aptreviews.complex_id = aptcomplexes.complex_id  |
> > | 18867 | webs | localhost.localdomain | webs_aptrate  | Query
>  | 1     |
> > Sending data | select count(*) from aptreviews, aptcomplexes where
> > aptreviews.complex_id = aptcomplexes.complex_id  |
> >
> > The tables that are used are somewhat large:
> >
> > mysql> select count(*) from aptreviews;
> > +----------+
> > | count(*) |
> > +----------+
> > |    15263 |
> > +----------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select count(*) from aptcomplexes;
> > +----------+
> > | count(*) |
> > +----------+
> > |    35395 |
> > +----------+
> > 1 row in set (0.00 sec)
> >
> > Any ideas what might be causing this?
> >
> > Here's the version:
> > [root@s2 tauren]# mysql -V
> > mysql  Ver 11.15 Distrib 3.23.40, for pc-linux-gnu (i686)
>
> --
> Michael Bacarella              | 545 Eighth Ave #401
>                                | New York, NY 10018
> Systems Analysis & Support     | mike@stripped
> Managed Services               | 212 946-1038
>

Thread
CPU intensive queryTauren Mills26 Feb
  • Re: CPU intensive queryMichael Bacarella26 Feb
    • RE: CPU intensive queryTauren Mills26 Feb
      • Re: CPU intensive queryShakeel Sorathia26 Feb