Storing Images in MySql Database
Mat Anthony
Ranch Hand
Posts: 261
posted 1 year ago
Hi All,
I have developed an application that is deployed on openshift. I have a web front end that allows the users to upload images into a MySQL db.
Most of the images are taken from users phones. The site has not gone live yet, just testing. Is it better to store images in opneshift dir or within a database.
Mat
I have developed an application that is deployed on openshift. I have a web front end that allows the users to upload images into a MySQL db.
Most of the images are taken from users phones. The site has not gone live yet, just testing. Is it better to store images in opneshift dir or within a database.
Mat
posted 1 year ago
I'd say it depends on number of images and how those are used in the system.
If you have a small number of small images (100s) and these are served with low frequency (for example, only when user wants to edit their profile), then you can get away with storing in db and retrieving them
along with other fields of user.
But for most typical usage of images, storing on filesystem brings less overhead and more deployment flexibility compared to storing on db.
If you store images in db, your java web or application server - in addition to its regular backend logic - has to be involved in serving images too because it's the one with the DB connection pool.
There are also more processing layers (so more CPU) and more caches (so more memory usage) in this flow, because of the JDBC driver and that of the (remote) database server itself.
If you want to bulk process your images (like watermarking, resizing, histogram equalization, thumbnailing), you have to change your java web application code for each use case because it's the only one capable of reading and writing to your DB.
Any scalability and performance problems with images require deep knowledge of how blobs are handled by the DB.
Even any scalability and performance problems not directly related to images may still require this knowledge because the blobs themselves may be causing those problems as a side effect.
Backups and replications of database will be longer because of the blobs.
On the other hand, storing on a filesystem means you get all the advantages of OS, kernel and filesystem performance optimizations.
You get the flexibility to deploy your images separately behind a more efficient web server dedicated to serving just the images, like nginx or httpd, and serve them directly from filesystem instead of involving your java web server.
Bulk image processing can be done directly on the files.
Database and file backups can happen in parallel.
File backup tools like rsync can be used.
Horizontal scalability for image storage is easier.
But if you anticipate lots of images (100s of 1000s to millions), then avoid both db and filesystems and store them directly in scalable cloud storage like Amazon S3.
You can put a CDN service like CloudFront if your users are spread around the world so that they receive the images more quickly.
If you have a small number of small images (100s) and these are served with low frequency (for example, only when user wants to edit their profile), then you can get away with storing in db and retrieving them
along with other fields of user.
But for most typical usage of images, storing on filesystem brings less overhead and more deployment flexibility compared to storing on db.
If you store images in db, your java web or application server - in addition to its regular backend logic - has to be involved in serving images too because it's the one with the DB connection pool.
There are also more processing layers (so more CPU) and more caches (so more memory usage) in this flow, because of the JDBC driver and that of the (remote) database server itself.
If you want to bulk process your images (like watermarking, resizing, histogram equalization, thumbnailing), you have to change your java web application code for each use case because it's the only one capable of reading and writing to your DB.
Any scalability and performance problems with images require deep knowledge of how blobs are handled by the DB.
Even any scalability and performance problems not directly related to images may still require this knowledge because the blobs themselves may be causing those problems as a side effect.
Backups and replications of database will be longer because of the blobs.
On the other hand, storing on a filesystem means you get all the advantages of OS, kernel and filesystem performance optimizations.
You get the flexibility to deploy your images separately behind a more efficient web server dedicated to serving just the images, like nginx or httpd, and serve them directly from filesystem instead of involving your java web server.
Bulk image processing can be done directly on the files.
Database and file backups can happen in parallel.
File backup tools like rsync can be used.
Horizontal scalability for image storage is easier.
But if you anticipate lots of images (100s of 1000s to millions), then avoid both db and filesystems and store them directly in scalable cloud storage like Amazon S3.
You can put a CDN service like CloudFront if your users are spread around the world so that they receive the images more quickly.
|