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.
Subject
Views
Written By
Posted
4148
April 01, 2005 02:06PM
Re: MySQL Performance Improvement Needed Urgently
2099
April 01, 2005 09:20PM
2606
April 02, 2005 05:54AM
1989
April 02, 2005 07:04AM
2188
April 02, 2005 07:22AM
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.