[nycphp-talk] LARGE mysql database performance
chsnyder at gmail.com
Thu Feb 2 12:01:49 EST 2006
On 1/31/06, Keith Richardson <keith at keithjr.net> wrote:
> So I am writing a "security camera" app at work, and using mysql/php. I have
> the cameras uploading snapshots via ftp to the server.
> Now for dealing with the images, and indexing them for easy searching, I
> want to throw the info into a mysql database. I wouldnt mind also sticking
> the image data in the table too, so that you HAD to go through the app to
> view the images instead of direct url linking, but was wondering, how does
> mysql react with a 20-30gb database? would this be very bad? I have run a
> forum that used vbulletin with about a 500mb database, but that did not have
> any issues at all.... but 20-30gb is quite the large database :p
> Any thoughts or experiences?
> Keith Richardson
As usual I'm late on the thread, but I was going to ask this very
question myself, so thanks for leading the way, Keith.
A number of the answers here echo this sentiment: "I wouldn't do it
myself, use the filesystem, but MySQL should be able to handle it."
Well WTF? There are *some* advantages to storing the data in the DB:
1) All application data in one place for backup and recovery
2) Easily scalable via replication, (somewhat less easily via) clustering
3) No need to build additional infrastructure like directory trees
4) Data readable by mysql user only
Plenty of advantages to storing on the filesystem, of course, and
number 2 above can be countered with some sort of NFS or a local cache
of a private webstore. But I like the ease of backup and the privacy
afforded by the BLOB.
That said, I've run into a limit in the amount of data that can be
read in a single query by PHP. I haven't had time to investigate (it
was somewhere in the 16-32MB range) but that's a consideration if
you're planning to store videos or print-quality photos.
More information about the talk