Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Advise on storage

Posted on 2015-02-11
Medium Priority
Last Modified: 2015-03-04
Ive upgrade an application I worked on a long time ago, which is working fine but storage is running out of control.

When I took over a few years ago files were stored in a database but kept falling over when the database file hit 2Gb, so I stored the files in a folder instead (sub folder for each record as one record could hold 0-~ files). After a few years the storage has gone from 2Gb being an issue to now 780Gb, however its using 10.2Tb of disc space due to the cluster size of the server and the majority of the files being very small (1-3Kb in size).

I cant do anything about the cluster size on the server, so need to look into alternative ways.

So I'm revisiting the idea of storing the files in a database, and looking at using SQLite to store the files in however slightly worried about the write locking (when SQLite writes it locks the database). I've been looking at splitting the file into segments and over multiple database files. so I can keep the the SQLite files around 200Mb each which I think would be a good size and reducing the loss of storage in the clusters.

Does anyone have any advise or previous experience in storing files for web apps?

Thank you
Question by:tonelm54
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 34

Expert Comment

ID: 40603719
Not sure what to recomend for this? I use SQLite all the time, and you can have problems if you try and use it like MySQL for an entire site, all pages, every thing, Database storage. BUT if you use it on a Per Page (or web site section) database, where for a single page (or just 3, 4, 5, or 6 pages) uses a separate data file, then there is never the traffic to block effective usage. But the SQLite, can also suffer from conjestion for file storage, and is recommended that files are stored on disk.
It sounds to me like you should try and use one of PHP file compression for storage.These can be fast for file collections of less than a thousand or so files. But with 780Gb o files, this might get less than easy to find a balance between speed and disk space?

Author Comment

ID: 40612136
I've been reading up on this using this guide 'http://www.dummies.com/how-to/content/storing-data-with-php-flat-file-or-database.html' however there doesn't seem any down side to using the files in a database.

Any reason why I cant store in database

Thank you
LVL 34

Accepted Solution

Slick812 earned 2000 total points
ID: 40612477
OK, I will give you some of my opinions, and there are other opinions I have read.
 There are several many considerations when someone considers disk file storage, and or database file storage. I never said that you can not put disk files into a data base.
When using DB engines like MySQL, MSSQL, , they store each TABLE and its data inserts in a separate disk file from other Table files, they MUST have all columns defined as a specific amount of Bytes, if you place disk files in the table as some sort of "Blob" column with undefined byte size, then another file is created and used for storage of all "Blob" type columns, and this works, you can place any files in a table that you like. And if you have little site traffic, and not much data base activity, then this works well for storing and getting the files. BUT, if you have moderate or heavy site traffic and are pushing these DB stored files through the PHP to Database connection transfer, then your site can become SLOW in response time for any page that uses the Database, even if it does not get any files. The PHP to Database connection can only transfer so many packets per second, this transfer DB rate is usually much slower than the Operating System disk file access speed. So it is recommended that you store files on disk by PHP methods, and just place the file path, as a defined byte size column in the Database, so you do not overload the database connection transfers with large "Blob" byte size transfers.

As to using SQLite, it does NOT use a separate Database engine and data transfer connection, so the database files for SQLite are not divided in to separate Table files, but any and ALL data for the DB file is in one single file. SQLite works very well (speed of DB) up to a certain point of database usage, but when there are many, very many php pages using the same SQLite DB file every second, then SQLite can become Slow. The newer SQLite3 is more able to handle Loads than the old SQLite2, which had a reputation for only being able to do light weight data traffic loads.

Your idea of making enough separate SQLite DB files at about 200-300 megs, for file storage, just may work for you, I have never tried anything at all like that strategy for file storage, so I can not say much one way or another about it. But I can say that direct PHP flat-File access times, are faster, and take less operations than using a SQLite DB access for files, and so it is recommended to not place many files in the SQLite.

That's all I have to inform you on database storage, but the web is super loaded with info about MySQL,  SQLite, and PHP.
Unfortunately, there's going to be some difficulties in dealing with 780Gb of of file size Data, no matter what you do, and I have no experience in dealing with that much items to deal with.
I suppose you have considered a way to Archive half of those 780Gb, that are less or never accessed files to compressed files?

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question