Data Purging and Archiving

[hindraj]

How to archive old data and purging them to keep performance at its best!
Any feature for it?
Or is it in the work?

Thanks!

[TimSchofield]

Hard disc space is so cheap these days, that archiving data is really not necessary. Mysql can handle very large tables (>1 trillion records) very comfortably. If we have a feature that performs badly when there is a lot of data then it is more likely we have an issue with the database design that we need to look at. Do you have an example?

Tim

[hindraj]

There are limitations: we don't live in an ideal world... We need to keep hardware cost minimum, users gets uncomfortable with thousand of inactive items/raw materials... etc.

In short, we want all those old stuff to be cleaned, or moved to, maybe an archive database?

But thanks for your view, I appreciate it.

Hard disc space is so cheap these days, that archiving data is really not necessary. Mysql can handle very large tables (>1 trillion records) very comfortably. If we have a feature that performs badly when there is a lot of data then it is more likely we have an issue with the database design that we need to look at. Do you have an example?

Tim

[TimSchofield]

You can set part codes to be obsolete if you no longer require them.

Tim

[PakRicard]

Hi all:

I agree bytes are cheap these days, but those of us having webERP running in a shared hosting environment might have to deal with seconds of CPU restrictions. I found some of our scripts just run out of execution time, and I'm not allowed to modify this PHP setting.

Also backing up and restoring large DB are a problem in shared hosting environment as scripts simply run out of time.

Reducing DB size should help. I was thinking to reduce number of old records in accounting tables (specially gltrans) and stockmoves as those 2 tables are almost 55% of my DB records.

Regarding stockmoves: I don't think we will never need the movements of an obsolete item of 10 years ago.
Regarding gltrans is quite tricky, as we need to keep accounting consistency. Probably grouping all the records per period and GL account should work, but still working on it and thinking of "collateral damages" it might create.

Of course, moving to a dedicated server is also a solution, but probably too expensive compared to delete some old bytes we are not interested at.

Will love to know any more opinions about it :-)

I have this task on my "to-do" list, so probably will code it once it's on my "urgent-to-solve-now" list. I'll keep you posted on any advance.

[Exsonqu_Qu]

Dear all,

We've encounter the same problem of 4 G gltrans data due to the bug in BOM. It choked the server. I've filtered some data out which solve the problem. There is also a bug in Payment scripts which choked the process due to above problem. Those fixes will be committed to trunk when I return back to office.
Thanks and best regards!
Exson

[TimSchofield]

If a large table is causing a script to run slowly then there is an issue with the way the table is designed and we should really fix that. I would be very interested in details of what is actually happening, to what script.

To find a particular record in a properly indexed table of one trillion (1,000,000,000,000) records using a binary search algorithm (mysql actually uses more efficient algorithms) would take a maximum of 41 lookups, which mysql could do quickly enough that nobody would notice.

Tim

[PakRicard]

Hi Tim:

I have custom scripts to measure performance and general control of the company, crushing thousands of records and join tables everytime they are run. I think that when these scripts deal with large tables they will take more CPU time than if they work with small tables.

But, as I pointed out the most critical point is backup and restore execution time, as we are backing up and restoring a lot of obsolete information to the shared

I also understand this issue is not related to "mainstream" webERP, probably just for large installations.

[TimSchofield]

If the tables are correctly indexed, and the scripts written with large data sets in mind then you shouldn't have any noticeable drop in performance as tables get bigger. Mysql is written with querying large data sets in mind, and is very good at it.

As for the backups, I take it from what you say that you are using phpMyAdmin to do your backups? Does your provider not give you any other options for this? I have customers on Godaddy for instance and they allow direct backup of databases from their database management tool, and also for running scripts as cron jobs on their Linux servers. I have a script that runs as a cron job, backs up a very large database, zips it, and then moves it off site. This just on Godaddy's basic package.

Tim

[cyberideaz]

My GoDaddy hosting has a limitation of 1GB MySQL database size. What to do in this situation? Is there any way to purge and archive old data?