Re: Corrupt index = mysqld freeze?
On Thu, Aug 14, 2003 at 11:35:18AM -0700, Jennifer Goodie wrote: Setting skip-name-resolve will avoid this code path and the bug. It looks like some DNS funkyness... I've implented this and so far I can't get the box to hang in authentication no matter how mean I am to it. I feel so dumb, for not thinking of this before, I swore I had already done it about a year ago when all of the other boxes were set up to skip name-resolve. I'll post if I can get it to lock up again (let's hope I can't). I'll be impressed if you can. :-) I spent a while poking around in the code and couldn't find any other *obvious* ways it'd get stuck there. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 16 days, processed 847,688,622 queries (597/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 09:07:41PM -0500, Dan Nelson wrote: In the last episode (Aug 07), Jeremy Zawodny said: I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. The pstack command (ports/sysutils/pstack) knows about FreeBSD's libc_r threads, so you can run that to get a snapshot of mysql's thread state. Just caught one. We have nealy 300 stuck connections and they all look like this right now: 45855: /home/y/libexec/mysqld - thread 0 (running) - 683f4de8 _sigsuspend (8e0ffbc0, 4, 8e0ff9c4, 683644de, 68373e64, 8dfff9e8) + 8 68364534 __pthread_suspend_old (8e0ffbc0, 0, 8e0ffa04, 6836183a, 68373e64, 3ce3500c) + 64 683619b6 __pthread_alt_lock (8366b04, 0, 8e0ffa34, 68364c5a, 0, 3ce3500c) + 18a 68364db2 pthread_mutex_lock (8366af4, 3ce35504, 0, 826a3b9, 3ce35504, 3ce3500c) + 166 8078c01 ip_to_hostname__FP7in_addrPUi (3ce35504, 8e0ffa9c, 8e0ffb24, 807d490, 3ce1d280, 3ce35504) + f1 807d4a0 check_connections__FP3THD (3ce35000, 0, 1, 807d7dc, 68373e64, 8e0ffbc0) + c4 807d8b2 handle_one_connection (3ce35000, 2, 8e0ffc78, 68365e77, 68373e64, 8e0ffbb8) + 11a 68365f2d _init (8e0ffbc0, 8e0ffbc0, 0, 0, 0, 0) + f119 Now, to figure out what that's telling us... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 250,137,018 queries (400/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 03:58:37PM -0700, Jennifer Goodie wrote: One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) A crash was recreated by running a specific query. Oh. You didn't mention crashes in your first note. That changes everything. When myisamchk ran upon restart it said the index file for the table that was being queried was corrupt. After careful observation, it was discovered that this is often the case, indexes for tables mentioned in the update log right before a crash were corrupt upon restart. I'm more inclined to believe that they are corrupt due to us killing mysqld with the tables still open, since we can't authenticate to shutdown. We also get a lot of table handler errors from myisamchk after a crash and kill, go figure. Yeah, if you're killing MySQL by force, you really ought to check all tables and repir broken ones. Otherwise it's a craps shoot. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. At least not the obvious ones. :-) We've found that on moderately busy machines here, upgrading to a LinuxThreads-based MySQL reduced CPU utiliization by 30% or so. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. Ugh. In my opinion, for us it definitely happens when an expensive query is run on an active table. Looking at the logs, there's always a bunch of disconnects all at once right before connections stop. Hmm. I hadn't noticed that yet. But I hadn't thought to look at disconnect rates either. I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 213,656,247 queries (397/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
Setting skip-name-resolve will avoid this code path and the bug. It looks like some DNS funkyness... I've implented this and so far I can't get the box to hang in authentication no matter how mean I am to it. I feel so dumb, for not thinking of this before, I swore I had already done it about a year ago when all of the other boxes were set up to skip name-resolve. I'll post if I can get it to lock up again (let's hope I can't). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
In the last episode (Aug 07), Jeremy Zawodny said: I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. The pstack command (ports/sysutils/pstack) knows about FreeBSD's libc_r threads, so you can run that to get a snapshot of mysql's thread state. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Fri, Aug 08, 2003 at 04:18:51PM -0700, Jeremy Zawodny wrote: On Thu, Aug 07, 2003 at 09:07:41PM -0500, Dan Nelson wrote: In the last episode (Aug 07), Jeremy Zawodny said: I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. I don't know how to do this with pthreads but with LT, I'd like to identify a few of the pids for the struck threads and then get a snapshot of the call stack to see where they're waiting. The pstack command (ports/sysutils/pstack) knows about FreeBSD's libc_r threads, so you can run that to get a snapshot of mysql's thread state. Just caught one. We have nealy 300 stuck connections and they all look like this right now: 45855: /home/y/libexec/mysqld - thread 0 (running) - 683f4de8 _sigsuspend (8e0ffbc0, 4, 8e0ff9c4, 683644de, 68373e64, 8dfff9e8) + 8 68364534 __pthread_suspend_old (8e0ffbc0, 0, 8e0ffa04, 6836183a, 68373e64, 3ce3500c) + 64 683619b6 __pthread_alt_lock (8366b04, 0, 8e0ffa34, 68364c5a, 0, 3ce3500c) + 18a 68364db2 pthread_mutex_lock (8366af4, 3ce35504, 0, 826a3b9, 3ce35504, 3ce3500c) + 166 8078c01 ip_to_hostname__FP7in_addrPUi (3ce35504, 8e0ffa9c, 8e0ffb24, 807d490, 3ce1d280, 3ce35504) + f1 807d4a0 check_connections__FP3THD (3ce35000, 0, 1, 807d7dc, 68373e64, 8e0ffbc0) + c4 807d8b2 handle_one_connection (3ce35000, 2, 8e0ffc78, 68365e77, 68373e64, 8e0ffbb8) + 11a 68365f2d _init (8e0ffbc0, 8e0ffbc0, 0, 0, 0, 0) + f119 Now, to figure out what that's telling us... Setting skip-name-resolve will avoid this code path and the bug. It looks like some DNS funkyness... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 250,298,423 queries (400/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) A crash was recreated by running a specific query. When myisamchk ran upon restart it said the index file for the table that was being queried was corrupt. After careful observation, it was discovered that this is often the case, indexes for tables mentioned in the update log right before a crash were corrupt upon restart. I'm more inclined to believe that they are corrupt due to us killing mysqld with the tables still open, since we can't authenticate to shutdown. We also get a lot of table handler errors from myisamchk after a crash and kill, go figure. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. In my opinion, for us it definitely happens when an expensive query is run on an active table. Looking at the logs, there's always a bunch of disconnects all at once right before connections stop. I've been on the box at the mysql prompt quite a few times when it has happened and there was always a large amount of threads waiting for a lock to clear, and as soon as they went through nothing could connect, but this doesn't happen everytime we have a large queue, so there must be something else in the mix. If you think any info I have might help you, let me know. I'd love to hear any ideas you have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Corrupt index = mysqld freeze?
A crash was recreated by running a specific query. Oh. You didn't mention crashes in your first note. That changes everything. Sorry, I'm a dork. Buy crash I mean all new connections getting stuck in authentication mode. As the day wears on my Jennifer Vernacular - English translater is starting to buggy. :) Yeah, if you're killing MySQL by force, you really ought to check all tables and repir broken ones. Otherwise it's a craps shoot. My thoughts exactly. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. We aren't running Linux threads. We didn't seem to be experiencing any of the issues it helps. At least not the obvious ones. :-) We've found that on moderately busy machines here, upgrading to a LinuxThreads-based MySQL reduced CPU utiliization by 30% or so. This box sits around 1% all day unless something crazy's going on, but we've got another mysql box (more qps, more tables, but way less data) that could really benefit from this, thanks for the tip. For a while we'd only have this happen once a month, then it was once a week. Lately it has been a few times a day, but everyone is messing with box. Ugh. Yeah. Don't get me started, I can go on for days. Hmm. I hadn't noticed that yet. But I hadn't thought to look at disconnect rates either. It could be nothing, it's just the only pattern I have noticed. Unfortunately my systems knowledge isn't very strong, so I don't know if my suggestions are completely insane. The one thing I was thinking was it's has something to with releasing a lot of resources at once and not signalling that they are available again so the request just waits and waits. For some reason I'm thinking semaphores and starvation, but the only experience I really have is one OS theory course a few years ago. I don't know how to do this with threads but with LT, I'd like to identify a few of the pads for the struck threads and then get a snapshot of the call stack to see where they're waiting. I'll run this by the systems guys, thanks for the suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thursday, Aug 7, 2003, at 13:41 America/Los_Angeles, Jennifer Goodie wrote: I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 In addition to Jeremy's suggestion of setting skip-name-resolve (and changing host names in the mysql database to IP addresses) you might want to consider upgrading FreeBSD to something more recent than 4.1 (unless that was a typo). 4.1 is over 3 years old. If skip-name-resolve doesn't help then it might be worthwhile to consider converting the tables to InnoDB to see if that changes anything. - ask -- http://www.askbjoernhansen.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
Jennifer Goodie wrote: I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. I'm not sure how related this is, but we have some relatively small tables ( 50,000 rows ) that exhibited the same behaviour when using MyISAM and MS Access front-ends. My solution was to change all affected tables to InnoDB. I hadn't noticed anything about indexes as you described, but then I wasn't looking. When it used to happen to us, there would be one update or insert process that mysqladmin claimed was 'locked' ( I think - this was a while ago ), and then a backlog of other user processes - often in unrelated tables - would start appearing. mysqladmin shutdown didn't work ( but mysqladmin processlist did - go figure ) - I had to kill -9 all mysql processes and restart the server. I always put the problem down to MS Access and it's record-locking 'style'. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt index = mysqld freeze?
On Thu, Aug 07, 2003 at 01:41:57PM -0700, Jennifer Goodie wrote: I have a stand alone database server. It is a RAID5 running mySQL 3.23.55 on FreeBSD 4.1 and has 768MB of RAM, I'm not sure on the processor speed, but I think it's a P3 1GHz. It has several tables with 20-40 million rows and a ton of smaller tables with less than 1 million rows. All tables are MyISAM and we have fewer than 10 queries per second. The super large tables are write only for the most part, with most reads taking place in off peak hours (a cron to generate aggregate data). The smaller tables are read/write. We've been experiencing a problem where mysqld stops responding to new connections. Any active connection is fine and can run any query it wants, but all new connections get stuck in the authenticating user phase. CPU and load drop to about zero when this happens, so I don't think it's the notorious threading issue. This freeze happens when more than 10-12 connections drop at the same time, usually when a queue caused by a table lock clears out. Sounds familiar. One of my coworkers insists that this is due to corrupt indexes, stating that if an index points to a location outside of the record set mysql gets confused and hangs. Does he have any evidence whatsoever for that? I'm 99% sure he's wrong--at least in *our* cases. :-) It has also been stated that multicolumn indexes are a problem, especially if they contain more than 3 columns. This goes against everything I know about mysql. In my experience if there is file corruption an error gets returned promptly. Right. MySQL can detect most corruption. I also believe multicolumn indexes are a valuable feature. I have been told that I need to get rid of all multicolumn indexes in order to make the server stable. Needless to say, I am not very happy with this solution and don't have a lot of faith in it working. Has anyone else experienced anything similar to this, and if so what did you do to fix it? Anyone want to weigh in on the index theory because it doesn't really sound right to me, but I'm not exactly an expert. We've seen that happen too on more recent FreeBSD versions with LinuxThreads. So far it's not happening all that often and it seems that the chance of it happening is much greater right after MySQL has been [re]started. I haven't had much luck in tracking it down further. But I have a few more ideas next time I see it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 6 days, processed 212,550,116 queries (399/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
