MySQL Forums :: Database Design & Data Modelling :: Large table designissue


Advanced Search

Large table designissue
Posted by: omkar prabhu ()
Date: October 24, 2009 02:46AM

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 designissue omkar prabhu 10/24/2009 02:46AM
Re: Large table designissue Rick James 10/25/2009 09:11AM
Re: Large table designissue omkar prabhu 10/29/2009 09:36PM
Re: Large table designissue Rick James 10/29/2009 10:34PM
Re: Large table designissue omkar prabhu 11/13/2009 01:05PM
Re: Large table designissue Rick James 11/18/2009 12:15AM
Re: Large table designissue omkar prabhu 11/19/2009 12:38PM
Re: Large table designissue omkar prabhu 11/19/2009 12:55PM
Re: Large table designissue Rick James 11/24/2009 11:49PM
Re: Large table designissue omkar prabhu 11/25/2009 11:23AM


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.