0

I'm not a DBA, I'm a DevOps guy trying to manage a MySQL database which is ever growing and desperately needs some archiving. My question is not how to archive. I think I have a pretty good idea and have SO for anything I might not know yet.

I'm trying to figure out "what" to archive. I don't have a clue what our apps need, or how often they access the records. I can tell you our DB has 410 tables in it. Our largest tables has 4 million records in it. I don't have the time to go through each and every one of those, ask how much they get used, or accessed, etc. I'm trying to trim down our development environments database size. It's slow to refresh them, and our RDS bill is massive. I know for certain our development environments do not need all this data. We also run a sanitizing script which rips out PII and other real world sensitive information. I don't think it is necessary to sanitize 8+ years or possibly older of records just for a development environment database.

Goal: Have a smaller developer environment database to A) trim down bill B) reduce time for data refresh.

Question: What are common things you look at when you are making decisions on what to archive, and how much to archive? Are there tools that help you identify good candidates? Are there common queries that you run to help you make this decision?

  • Complete backup is always better than partial. For I'm not a DBA complete backup is the only option. Additionally - recovering from 2 partial backups needs twice time. At the best.. – Akina Nov 20 at 4:36
  • Is there any tips or tricks or queries you might recommend in which help you when you are trying to make decisions on what to archive? Monitoring INFORMATION_SCHEMA.TABLES.UPDATE_TIME (for example, check it daily) may help. – Akina Nov 20 at 4:39
  • @Akina Yes, you are correct. Though this is only lower environments that get wrecked by devs and qa, and refreshed frequently. I'm sure our dev environments do not need the full set up data. It takes around 3 hours to refresh from a full backup. That does not seem reasonable for a data refresh just to get wrecked by devs and qa. Not to mention that 4TB of data for over 40 lower environment db's is costing us a fortune. It's more than all our other cloud provider costs combined. I'm sure that is a way to trim some fat by giving dev environments a subset of data. – protobyte Nov 20 at 23:28
1

Typically this kind of decisions are taken with product managers. They are the ones who can say things like "customers should be able to read 2 years old information, but not necessarily older than that".

MySQL (or any other DBMS, as far as I know) doesn't provide a way to find out when a row was last read.

But there are ways to find out related information. This won't solve your problem, but hopefully you'll find useful indications.

  • Some tables have a column with the timestamp of the last update. Usually the default value is CURRENT_TIMESTAMP, but check: if there is no default value, I wouldn't consider the column reliable. You can't be sure that the application always updates the column.
  • You can temporarily add triggers AFTER UPDATE and AFTER DELETE. Triggers can write the value of the primary key into a separate table, using INSERT IGNORE. Keep them going for some time, and then check the oldest row that was updated/deleted. Writing such triggers manually is impossible, but this can easily be automated.
  • If you use Percona Server or MariaDB, you can enable the user_statistics plugin. It tells you how many times each table and index is read/written. If you're lucky, you'll find out that some tables are never read and can be archived. Even if not, the number of read rows per hour could possibly give you a hint (or not... this is highly dependent on your database logic, so I can't give more advice on this).
  • If you suspect that a portion of rows is never read, try a trick: read them with a SELECT three times. If the first query was sensibly slower than the second and third, those rows were not cached. Repeat the experiment at different days. It's a very empirical and unreliable test, use it as a hint and nothing more.
  • If the slow log is enabled, it logs the queries that take more than long_query_time seconds. You can set long_query_time=0 to log all the queries (this is a good practice for many reasons). You can then grep all queries that mention a certain table. You can make a script to run them all, and remember the oldest read row. Careful, you'll need to run SELECTs but not INSERT, UPDATE, etc.
| improve this answer | |
  • Thank you. This helps a lot. – protobyte Nov 24 at 0:20
  • @protobyte glad to hear that. I've added one point. – Federico Razzoli Nov 24 at 11:15
0

Disk is cheap. Archive nothing.

Or are you talking about "backup"? Or maybe "clone"?

How big will your data eventually be? Do you intend to "archive" just "old" rows? How "old" is "old"?

Don't use a backup for loading a copy, use an LVM snapshot. Or, if you use a cloud service, they effectively do that for you -- near-zero effort, near-zero elapsed time. (Maybe 3 minutes, not 3 hours.)

If you set up replication or clustering, you can always have an up-to-the-minute copy available for various purposes.

I think you need to explain the purpose before discussing specific solutions.

More...

  • Consider segregating the PII data from the data that does not need sanitizing.
  • Take the above to the point of moving PII data to another database, thereby making it easy to dump non-sensitive data easily. (At the expense of JOINs, or more likely, LEFT JOINs. In the long run this will help you when your system is subjected to a privacy audit.
  • See if datatypes can be shrunk to avoid how big the data is (in order to shrink RDS bill)
  • How are hiding the sensitive data from? Developors? Outside world?
  • Because of the privacy concerns, you must bite the bullet and get enough budget and find the time to do it cleanly.
| improve this answer | |
  • 1
    It may be, but in my case, our RDS bill is 80% of our AWS bill. I am talking about a clone or production which is from a snapshot. Then archive old data for development environments. Right now we have records that go back 8+ years. For developers, they do not need all of this. "How old is old?"; that is what I'm trying to figure out. How can I determine this? How would you suggest? The 3 hrs is because we have to sanitize so much data. If I could sanitize less data, it would take less time for the db to get restored from this snapshot. – protobyte Nov 22 at 18:07
  • @protobyte - I added more. – Rick James Nov 22 at 20:45
  • TY. Separating the PII is a good suggestion for security purposes. However it doesn't really answer my original question. It is sanitized to keep devs from accessing it. Even if it is separate, we'd still have to sanitize all of it, unless we select a subset of records. Which is why I'm trying to calculate what to archive and what is needed in dev environments. I will look into shrinking data types, though I still feel giving dev environment 8+ years of records is not necessary. Plus it would shrink it even more. How does budget gives us more privacy? Prod expenses is only ~1% of dev expenses. – protobyte Nov 24 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.