Thanks! Explain and InnoDB monitor were exactly what I needed to diagnose and fix the problem! In case you were curious, the issue was that the statement I was expecting to run was not the statement that was running, but the first hundred and some-odd characters in both were the same. Using the monitor I was able to see that the wrong thing was running.
Some SELECTs are still taking longer than they should, but I have some new tools at my disposal, which makes me very happy. -- Joe On 5/4/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > Hello. > > > > 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..... > > Use EXPLAIN to determine how efficient your indexes are. Using a lot of > > keys could slow down the INSERT operations but fasten the SELECTs. > > InnoDB monitors might be helpful in your case as well. See: > > http://dev.mysql.com/doc/mysql/en/explain.html > > http://dev.mysql.com/doc/mysql/en/innodb-monitor.html > > > Joseph Cochran <[EMAIL PROTECTED]> wrote: > > > 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 > > > > > -- > For technical support contracts, goto https://order.mysql.com/?ref=ensita > This email is sponsored by Ensita.NET http://www.ensita.net/ > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > <___/ www.mysql.com > > > -- > 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]
