Not sure if this helps, but we've had a situation like this, and the cause was the network. We found that a query from the mysql client on the machine was really fast, but from anywhere else it was slow. We finally traced the problem down to a duplex conflict between the machine and the switch.
--shak Tauren Mills wrote: >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 | [EMAIL PROTECTED] >>Managed Services | 212 946-1038 >> > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Shakeel Sorathia Systems Administrator (626) 660-3502 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
