List:General Discussion« Previous MessageNext Message »
From:Jani Tolonen Date:April 9 1999 3:57am
Subject:RE: query locks up the database
View as plain text  
David Johnson writes:
 > Unfortunately that doesn't appear to be it.  Some more interesting facts are
 > that the query doesn't actually lock up the database but take over 8 seconds
 > to complete although all (foreign) keys (joins) are indexed.  I think that
 > is why when I run the query concurrently it seems to stop the database and
 > MySQL eventually times out the queries.
 > 
 > Also, if I remove the (log.seq) from the query, it's time drops to .77
 > seconds.  This is better, but still not too good considering the WEBSTATS
 > table has only 6000 rows, the AGUSER table only 100 and the SYNCLOG table
 > only 12000.
 > 
 > Any ideas on what am I missing?  I don't want to have to break up the
 > queries.
 > 
 > DJ
 > 
 > -----Original Message-----
 > From:	apachev@stripped [mailto:apachev@stripped] On Behalf
 > Of Sasha Pachev
 > Sent:	Thursday, April 08, 1999 6:03 PM
 > To:	davej@stripped
 > Cc:	mysql distribution lists
 > Subject:	Re: query locks up the database
 > 
 > David Johnson wrote:
 > >
 > > I'm running 3.22.16-gamma on FreeBSD,.  When I run the following query
 > > *concurrently* against my database it locks it up for a period of minutes:
 > >
 > > SELECT user.id, user.auth, user.nonce, user.syncstart, max(log.seq),
 > > sum(stats.outpagesize + stats.outimagesize) from AGUSER as user left join
 > > SYNCLOG as log on user.id = log.fk_aguser left join as stats on user.id =
 > > stats.fk_aguser and UNIX_TIMESTAMP(stats.syncstart) > UNIX_TIMESTAMP() -
 > > 86400 where user.uid = "davej" and user.deleted = 0 GROUP BY user.id"
 > >
 > > One user at a time works fine.  The table sizes and result sizes are all
 > > very small.  I have tried bumping the sort_buffer as well as the
 > > tmp_table_size up to 10M a piece but it didn't help.
 > >
 > > We have had other similar problems with other join/group by queries.  In
 > > fact another similar query causes the CPU to max out when it is run
 > > concurrently.  Any suggestions?
 > >
 > > DJ
 > >
 > 
 > check your disk space on the device where /tmp is mounted.
 > 
 > --
 > Sasha Pachev
 > http://www.sashanet.com
 > 
 > 
 > ---------------------------------------------------------------------
 > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
 > posting. To request this thread, e-mail mysql-thread1562@stripped
 > 
 > To unsubscribe, send a message to the address shown in the
 > List-Unsubscribe header of this message. If you cannot see it,
 > e-mail mysql-unsubscribe@stripped instead.

Dear David,

Please see section "How to report bugs or problems" from the manual.

---------

For speed-related bugs or problems with SELECT statements, you should
always include the output of EXPLAIN SELECT ..., and at least the
number of rows that the
SELECT statement produces. The more information you give about
your situation, the more likely it is that someone can help you! For
example, the following is an example of a
very good bug report (it should of course be posted with the
mysqlbug script): Example run under the mysql command line tool:

     mysql> SHOW VARIABLES;
     mysql> EXPLAIN SELECT ...
            <output-from-EXPLAIN>
     mysql> FLUSH STATUS;
     mysql> SELECT ...
            <A short version of the output from SELECT,
            including the time taken to run the query>
     mysql> SHOW STATUS;
            <output from SHOW STATUS>

----------

Yours,

Jani

-- 
+---------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___       ==  mysql@stripped         |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|       Jani Tolonen             |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|       mailto: jani@stripped      |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|       Helsinki                 |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____  Finland                  |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                           |
|             /*/             \*\                   Developers Team   |
+---------------------------------------------------------------------+
Thread
query locks up the databaseDavid Johnson9 Apr
  • Re: query locks up the databaseSasha Pachev9 Apr
    • RE: query locks up the databaseDavid Johnson9 Apr
      • RE: query locks up the databaseDavid Johnson9 Apr
      • RE: query locks up the databaseJani Tolonen9 Apr