Re: CPU intensive query
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
Re: CPU intensive query
Seems hard to answer without asking more questions, but some basic questions first: Is complex_id indexed in both tables? (SHOW INDEX FROM aptreviews) Have you tried EXPLAIN'ing the query to see if maybe the index isn't being used? 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. 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. What's the output of ``mysqladmin status''? 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
RE: CPU intensive query
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 |
