Re: Troubleshooting Stalls
Dan Buettner wrote: 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. mytop found the issue it turns out. i have no idea why show full processlist did not display queries, but mytop did. We need to optimize our queries and I think that this is just work to do on our side. There are the current queries, we have to divide them into smaller sub-queries i suppose, but this is out of my expertise. SELECT COUNT(*) AS total FROM messages, users sender, users receiver, user_messages linker WHERE linker.message_id=messages.id AND messages.sender_id=sender.id AND linker.receiver_id=receiver.id AND receiver.address LIKE '[EMAIL PROTECTED]' AND messages.unix_timestamp >= 1165212000 AND messages.reason != 'Archive' ORDER BY messages.received DESC SELECT messages.id, messages.subject, sender.address as sender, messages.body_hash, messages.received, messages.reason, receiver.address , linker.id as link_id, linker.time_of_read FROM messages, users sender, users receiver, user_messages linker WHERE linker.message_id=messages.id AND messages.sender_id=sender.id AND linker.receiver_id=receiver.id AND receiver.address LIKE '[EMAIL PROTECTED]' AND messages.unix_timestamp >= 1165212000 AND messages.reason != 'Archive' ORDER BY messages.received DESC LIMIT 0,200 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]
Re: Troubleshooting Stalls
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]
Re: Troubleshooting Stalls
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]
Re: Troubleshooting Stalls
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 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]
Troubleshooting Stalls
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]
