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.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.
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]
