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: dkasak@stripped
website: www.nusconsulting.com
