MySQL Forums
Forum List  »  Performance

Re: MySQL Performance Improvement Needed Urgently
Posted by: James Day
Date: April 01, 2005 09:20PM

Which database engine is in use? MyISAM or InnoDB are most likely - InnoDB if you want transaction and ACID support. What else is happening while the slow insert is happening? SHOW PROCESSLIST and SHOW FULL PROCESSLIST will tell you this.

If it's InnoDB, beware of SELECT ...FOR UPDATE. I've seen a case where someone tried locking a whole table for update that way instead of just using LOCK TABLE. Better to lock the table instead of each record one at a time.

What is the write doing?

If it's an update, how is the write identifying the record to change? I've seen someone do a write which did a full table scan to find the record to change, instead of using an index. Adding a suitable index would help this.

Only some tables have indexes? I assume all have primary key/index, but no others? Sounds as though you're going to need to add some more indexes as part of tuning.

A routine question, just in case you're or they are new to MySQL. Is there a my.cnf file? Very common for people new to MySQL not to have one and it has a very great effect on query speed.

If you haven't done it yet, and the server isn't in production use, you might see if OPTIMIZE TABLE tablename for every table helps. Sometimes people end up with very inaccurate query optimizer statistics and analyze or optimize can help a lot.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL Performance Improvement Needed Urgently
1921
April 01, 2005 09:20PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.