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
>
