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]
