MySQL Forums
Forum List  »  MySQL Administrator

Large table issue
Posted by: omkar prabhu
Date: October 24, 2009 02:44AM

I have a table growing immensely every day , currently it has around 18777410 records.
Our site is available 24x7 and we are using mysql replication to distrubute load on the database server ( a master and single slave combination).
It is almost two years we running the application on mysql 5.0.45 everything seems to fine, but now we are facing problem with maintaining this huge table.
Following are key problem areas:
a) Running aggregate queries takes more than 5 minutes, which is not acceptable with respect to site performane
b) Adding index is not possible as it takes more than 3/4 hours which result in site downtime
c) command like optimize and analyze cant be run as it is online application and it will result in locking of this important table.


Can any one suggest possible approach to solve this problem, we are thinking of trying following approach
a) Have seperate table for having archive data,so query requiring complete data, will need to be fire two queries or have union on archive data and live data
b) Upgrade to mysql 5.1 and see having partitioning on this table helps
c) have mutliple database slave server to hit queries on most used use cases on this table.

Let me know if anyone faced similar problem and how to go about solving it

Options: ReplyQuote


Subject
Written By
Posted
Large table issue
October 24, 2009 02:44AM


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.