Dear All,

I am not a DBA, or an expert. Recently i have installed Mysql5 on Redhat 5.0 on IBM P series have 2GB of RAM. there was a team who worked on Mysql regarding table and database creation. My question is "we are using this database for reporting only, and we have 500MB of daily data that is added to this database.
Current size of DB is 120GB. i want to improve the performance of database, while retriving the data to generate the report. Is it possible to archive the old records. or to improve the performance any other way.

thanks.

If your not using InnoDB as the engine, I highly recommend it for a database of this size, you should see less Disk I/O which is usually the bottleneck when using MyISAM table engines.

Then of course, good indexing always plays a good role in performance along with good SQL queries. If queries are taking exceptionally long, have a DBA or someone with good database skills look at the query itself to find improvements.

And yes, archiving also will eliminate the need to reproduce reports on data you've already created, etc.

Thanks for reply,

Can you please help me how do i archive mysql database.

thanks.

You have to have a clear definition of what defines an 'expired' record ie DB row.
It might be something as simple as any row whose creation date < today -30 days (ie more than 1 mth old).
On the other hand, if you have dependent recs ie parent-child relationships where one of the recs doesn't have a suitable date, it gets a bit more complex.
I've written Perl code in the past to deal with exactly these kinds of things, but each prog was custom built.
If you can clearly define (ask the data owner) 'expired', I can likely help.

Yeah, I agree with chrism01, not knowing your database and how it's used, stored records, if it's transactional and not knowing what you need to archive or keep makes it impossible to tell you how to archive data.

In my experience though, the most commonly used archiving method is by the date, if the table has a date column, etc. But honestly if your database stores such data that needs to be archived because it grows tremendously, usually this should be developed by the developers to safely archive the data that can be archived.

When we were faced with database archiving issues for Tier 1 storages, we carried out an internal analysis on the data value of present enterprise data stored in the application databases. We realized that proactive data archiving and migration to Tier 2 storages will work in favor of improving application performance. Also, database archiving helped sort out regulatory compliance issues faced by the organization.