Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.

This data is only used when loading single record (like article or blog post) and is updated very rarely.

Is there a reason to save text data to plain files and metadata to database, or let the db engine handle it all?

share|improve this question
add comment

2 Answers

You might want to consider moving the data outside the database if:

  • You are having problems with backup durations
  • It causes disk or network throughput problems on your database server
  • You have a requirement to access the data without using the database

In general, I'd suggest keeping it in the database unless you can come up with a good reason not to.

There's some overlap between this question and "Files - in the database or not?". You might find some useful thoughts there, though things that are true for binary files don't necessarily hold true for text data.

MySQL offers some useful features where text is stored in the database, such as opportunities to use full-text indexing, and network compression.

share|improve this answer
add comment

Here are some of my inputs to stand "FOR OUTSIDE DB"

It purely depends upon your need. If your business wants to keep them always available and recoverable with no down time then you might have to think a proper option to keep them available 24/7, but keeping them in Database the capacity management of DB server will be in question.

If no priority for your need then I would say better to keep raw files in appserver, just the location of the file in DB server in terms of varchar would help and adding index to it would benefit to get the right file location quicker even the number of rows are more. Imagine you have each field consisting of 1GB text and for 100 rows the size of the table would go more than 100GB. Sounds weird for me when I need to take care of everything like how DB performs when it comes to time taken for recovering the Database in-case of shutdown / backup / High Availability and over all performance of DB server.

I haven't got an opportunity to design such a requirement. But if given

  • I would keep them in app server as each files in file system.
  • Keep the file locations in tables as varchar.
  • Let the application logic to generate the file locations in the order of the design pattern you want according to your server infrastructure.
  • Keep the raw file mount point hard link to another server with same file naming nomenclature, in-case of disaster of your files use the fail over server IP for the application to point.

By this way DB performance will look good, quick recovery and data availability 24/7 and less time spent in-case of any maintenance activities.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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