So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time.
I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great..... -- Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
