SHOW PROCESSLIST should show local queries as well as remote - in my experience, it has been utterly reliable in terms of showing all current queries, regardless of origin.
InnoDB by default uses a single "tablespace", which some people do find inefficient because it never gets smaller even after deleting data or dropping tables. You can work around this by using the innodb_file_per_table option. ISTR that it was an unstable option until recently, so if you're using an older release you might want to consider/research carefully. A couple of other things I thought of that may help you improve performance: - using HIGH_PRIORITY for your SELECTs - http://dev.mysql.com/doc/refman/5.0/en/select.html - if you are experiencing problems due to long-running UPDATEs, evaluating your indices may help. Indices can be as helpful for updates and deletes as they are for selects. Can you post the output of SHOW PROCESSLIST during a stall? That would help us help you, I think. Dan On 1/3/07, Michael Katz <[EMAIL PROTECTED]> wrote:
Dan Buettner wrote: > Michael - > > You should see all running queries in the output of SHOW PROCESSLIST. > > MyISAM is a multiple-reader/single-writer design, also known as table > locking design - that is to say, you can have lots of threads reading > simultaneously, but only one writing. Also, when one is writing, > reading threads block and wait for it. The simplicity lends itself to > speedy operations in many cases, but sometimes you can run into > trouble with it, especially in high volume situations - as you have. > > I'd suggest looking for an UPDATE/INSERT/DELETE operation in the > output of SHOW PROCESSLIST next time you see a stall - that is likely > your culprit. > > Some workarounds include: > - using INSERT DELAYED syntax > - using LOW PRIORITY UPDATEs > - switching storage engine to InnoDB > > HTH, > Dan Thank You Dan. We have tried InnoDB but it is very inefficient in disk usage and we can not use it many cases, since we install on customer supplied hardware. Does show full processlist show local connections and queries as well? We only see remote queries in the list. Thank You > > > On 1/2/07, Michael Katz <[EMAIL PROTECTED]> wrote: >> I have a large database, about a million records in some tables, and I >> am trying to troubleshoot some stalls in our application. We have >> assumed that slow query speed causes the stalls, however, when my web >> app stalls I do not see the query in process list. What we see is the >> web page stall with a wait indicator and all other queries to the same >> tables are in a locked state (using MyISAM) in process list, but our >> query is not present in the list. After 30sec to 1 minute the web page >> will render and the locked queries will quickly complete. My questions >> are as follows: >> >> Should a query that takes a long time show up in show full processlist >> while it is executing? >> >> If the query is not causing the stalls any clues where to look in the >> connection code that could be causing table locks? >> >> Thank You >> Mike >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
