Also, remember that MySQL only uses one index per per table in a query. So if there are some columns in your table that are indexed, but,
1) Have poor cardinality (number of distinct values - low cardinality means there aren't many distinct values)
2) Are only used in a where clause with another column that has good cardinality
then they are an excellent candidate for removal.
While "EXPLAIN" is great for queries, it won't help much with an insert; it might be useful for figuring out what indexes are used, and which ones aren't.
Use "show innodb status" to get an idea of what's going on (Gleb suggested it in the link to the innodb monitor).
You should also post the relevant parts of your my.cnf file; have you seen this equation before:
Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB
Use it to calculate how much memory you are using.
Finally, read up on phantom reads: http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Next-key_locking.html
This might be what's happening.
David
Gleb Paharenko 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
