Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.mysql > #596 > unrolled thread
| Started by | "Robert Crandal" <rcranz143101@gmail.com> |
|---|---|
| First post | 2011-04-23 23:47 -0700 |
| Last post | 2011-04-25 16:46 +0200 |
| Articles | 20 on this page of 103 — 13 participants |
Back to article view | Back to comp.databases.mysql
Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-23 23:47 -0700
Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 09:23 +0200
Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 01:19 -0700
Re: Can MySql database store images? "J.O. Aho" <user@example.net> - 2011-04-24 11:13 +0200
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 11:17 +0100
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 13:06 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 08:42 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 14:45 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:27 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:04 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:44 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 20:51 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:03 -0400
Re: Can MySql database store images? Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2011-04-24 23:18 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 19:55 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:30 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:32 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:04 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:18 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:32 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:09 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:17 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:29 -0400
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:30 +0100
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:59 +0100
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:10 +0100
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:40 -0400
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:05 -0400
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 02:57 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:59 +0100
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-25 21:41 -0400
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 06:20 -0400
Re: Can MySql database store images? dougatmilmacdotcom@example.com (Doug Miller) - 2011-04-26 14:10 +0000
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-26 22:24 -0400
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 08:24 +0100
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-27 06:41 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 12:32 +0100
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-27 15:07 +0100
Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 07:11 -0500
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 13:34 +0100
Re: Can MySql database store images? "Peter H. Coffin" <hellsop@ninehells.com> - 2011-04-27 09:25 -0500
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-27 16:32 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 10:28 -0400
Re: Can MySql database store images? Norman Peelman <npeelman@cfl.rr.com> - 2011-04-24 16:43 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:36 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:34 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:50 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:20 -0400
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:42 +0100
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:27 +0200
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-24 15:38 +0100
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 16:41 +0200
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 19:07 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:46 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-24 21:01 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:10 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:23 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:39 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 12:00 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:27 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:42 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:12 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:23 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:31 -0400
Re: Can MySql database store images? "Mr. B-o-B" <mr.chew.baka@gmail.com> - 2011-04-25 20:30 -0500
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:21 +0100
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:46 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 13:23 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 20:31 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 14:48 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-24 21:11 +0200
Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:24 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:34 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:04 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:40 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 11:48 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:28 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 13:36 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:17 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 14:30 +0100
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 18:13 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:37 -0400
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 19:35 -0400
Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 13:13 -0700
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:35 -0400
Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-24 21:29 +0200
Re: Can MySql database store images? "Robert Crandal" <rcranz143101@gmail.com> - 2011-04-24 14:37 -0700
Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:41 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:16 -0400
Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 12:12 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 07:31 -0400
Re: Can MySql database store images? Tim Watts <tw@dionic.net> - 2011-04-25 08:43 +0100
Re: Can MySql database store images? Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2011-05-01 09:59 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:03 -0400
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 17:39 -0400
Re: Can MySql database store images? Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-25 00:46 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-24 20:18 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 12:13 +0200
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 06:43 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 13:40 +0200
Re: Can MySql database store images? The Natural Philosopher <tnp@invalid.invalid> - 2011-04-25 12:43 +0100
Re: Can MySql database store images? Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-25 09:19 -0400
Re: Can MySql database store images? Luuk <Luuk@invalid.lan> - 2011-04-25 16:46 +0200
Page 3 of 6 — ← Prev page 1 2 [3] 4 5 6 Next page →
| From | "Peter H. Coffin" <hellsop@ninehells.com> |
|---|---|
| Date | 2011-04-27 09:25 -0500 |
| Message-ID | <slrnirg9ps.2mh.hellsop@nibelheim.ninehells.com> |
| In reply to | #701 |
On Wed, 27 Apr 2011 13:34:31 +0100, Tim Watts wrote: > Peter H. Coffin wrote: > >> On Wed, 27 Apr 2011 08:24:16 +0100, The Natural Philosopher wrote: >>> Norman Peelman wrote: >>>> Doug Miller wrote: >>>>> In article <ip57sl$rc5$1@dont-email.me>, Norman Peelman >>>>>> 460 * 5.3kb >>>>>> >>>>> You wrote above "460 images ... with an average size of 5393kb" . >>>>> 5393kb is 5.4 MEGAbytes, not 5.3kb. >>>> >>>> Yes, my fingers were going faster than my brain. >>>> >>>> Average of 5393 bytes (5kb) >>>> Max of 10240 bytes (10kb) >>>> >>>> ...these are small images. >>>> >>>> Dump file (w/images) = 29.8MB >>>> Dump file zipped = 1.7MB >>>> >>>> >>> It is seldom possible to compress images more than they are already >>> compressed. >>> >>> So I still think you have made a mistake. >> >> Dump files can (intentionally and with malice aforethought) export >> binary columns in hexidecimal text, which is rather compressible. It's >> also very safe from things like people fussing with it with text >> editors, being copied and pasted into emails for demonstrative purposes, >> and other kinds of mistreatment. >> > > I think the point that is being overlooked, is that the text, whilst > compressible, is itself a re-encoding of an already highly compressed bit of > data. Some parts of the file are highly-compressed data. Well, fairly-highly-compressed, anyway. The actual compression used in, for example JFIF/.jpeg is Huffman. More of the initial 'compression' in those comes from not actual compression but rather lossy tricks to make an image that looks about the same as the original to the eye, but isn't itself 'compression' in the sense that the data inside itself isn't necessarily further incompressible. What this means is that fairly small images don't have a lot of "compressed data" in them in the first place, and the overhead for graphics with small fields of image data might be easily half overhead. > if anything, the intermidate text encoding should make things worse overall, > not better. One would think so at first glance, but text is really easy to compress. > We are still talking about 2.2MB compressed image data mixed up with other > stuff in an exploded ASCII form being, somehow, recompressed down to a total > which is less than the sum of the original images alone. That's the key to what I think is happening here. See, one image may be compressible for some small gains. But many images, especially with very similar information in the overhead portions of the formats, like they're mostly all the same sizes, or use similar color pallets, end up being compressable by being able to compress duplicate information *between* the images as well as within the image itself. > It would make me want to double check the dumps to see they really had > everything... Always a worthwhile step. But if the dump restores okay, the size alone isn't necessarily a warning that something else is wrong. -- Judging by this particular thread, many people in this group spent their school years taking illogical, pointless orders from morons and having their will to live systematically crushed. And people say school doesn't prepare kids for the real world. -- Rayner, in the Monastery
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-27 16:32 +0100 |
| Message-ID | <hgpl88-ifi.ln1@squidward.dionic.net> |
| In reply to | #704 |
Peter H. Coffin wrote: > On Wed, 27 Apr 2011 13:34:31 +0100, Tim Watts wrote: >> Peter H. Coffin wrote: >> >>> On Wed, 27 Apr 2011 08:24:16 +0100, The Natural Philosopher wrote: >>>> Norman Peelman wrote: >>>>> Doug Miller wrote: >>>>>> In article <ip57sl$rc5$1@dont-email.me>, Norman Peelman >>>>>>> 460 * 5.3kb >>>>>>> >>>>>> You wrote above "460 images ... with an average size of 5393kb" . >>>>>> 5393kb is 5.4 MEGAbytes, not 5.3kb. >>>>> >>>>> Yes, my fingers were going faster than my brain. >>>>> >>>>> Average of 5393 bytes (5kb) >>>>> Max of 10240 bytes (10kb) >>>>> >>>>> ...these are small images. >>>>> >>>>> Dump file (w/images) = 29.8MB >>>>> Dump file zipped = 1.7MB >>>>> >>>>> >>>> It is seldom possible to compress images more than they are already >>>> compressed. >>>> >>>> So I still think you have made a mistake. >>> >>> Dump files can (intentionally and with malice aforethought) export >>> binary columns in hexidecimal text, which is rather compressible. It's >>> also very safe from things like people fussing with it with text >>> editors, being copied and pasted into emails for demonstrative purposes, >>> and other kinds of mistreatment. >>> >> >> I think the point that is being overlooked, is that the text, whilst >> compressible, is itself a re-encoding of an already highly compressed bit >> of data. > > Some parts of the file are highly-compressed data. Well, > fairly-highly-compressed, anyway. The actual compression used in, for > example JFIF/.jpeg is Huffman. More of the initial 'compression' in > those comes from not actual compression but rather lossy tricks to make > an image that looks about the same as the original to the eye, but isn't > itself 'compression' in the sense that the data inside itself isn't > necessarily further incompressible. What this means is that fairly small > images don't have a lot of "compressed data" in them in the first place, > and the overhead for graphics with small fields of image data might be > easily half overhead. > >> if anything, the intermidate text encoding should make things worse >> overall, not better. > > One would think so at first glance, but text is really easy to compress. > >> We are still talking about 2.2MB compressed image data mixed up with >> other stuff in an exploded ASCII form being, somehow, recompressed down >> to a total which is less than the sum of the original images alone. > > That's the key to what I think is happening here. See, one image may be > compressible for some small gains. But many images, especially with very > similar information in the overhead portions of the formats, like > they're mostly all the same sizes, or use similar color pallets, end up > being compressable by being able to compress duplicate information > *between* the images as well as within the image itself. > >> It would make me want to double check the dumps to see they really had >> everything... > > Always a worthwhile step. But if the dump restores okay, the size alone > isn't necessarily a warning that something else is wrong. > Nice explanation Peter. That makes sense (in particular the commonality between images). Cheers Tim -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-24 10:28 -0400 |
| Message-ID | <ip1c2s$dde$2@dont-email.me> |
| In reply to | #603 |
On 4/24/2011 9:45 AM, Tim Watts wrote: > Jerry Stuckle wrote: > > >> Also, file systems don't do well with thousands of users and hundreds of >> thousands of images - they just weren't made to handle that many >> individual files. RDBs such as MySQL, OTOH, easily handles millions of >> users and hundreds of millions of images. >> >> Remember- a file system is just a non-relational database. It's good >> for some things but not everything. > > That rather depends on the filesystem. > > XFS will happily handle 13TB with over 10 million files - that is from > personal experience. > > Obviously VFAT would be a bad idea, and putting *all* the files on one > directory *may* be a bad idea (depends on directory hashing support in the > FS) - but with a sensible layout (one dir per user, possibly with a further > sublayer of user's first letters a-z) it will be fine. > > Couple of points IMO - and I cannot speak for MySQL in particular - but what > will it do for backups? > > With a small database and a big filesystem of images, the DB dump will be > fine and the FS can be backed up with an rsync type methodology. > > With a huge number of images in a DB, I suspect the possibilities of doing > incremental backups are vastly reduced and your DB dump file is ging to be > huge. > > Just my opinion, based on Postgresql - but the principles mostly apply to > MySQL unless it has some particular features to help with the problems I > posed. > > Cheers, > > Tim Oh, and BTW - the script to serve images from the server does not cause any noticeable slowdown - and in fact can be faster than retrieving from the file system. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Norman Peelman <npeelman@cfl.rr.com> |
|---|---|
| Date | 2011-04-24 16:43 -0400 |
| Message-ID | <ip221m$cb8$2@dont-email.me> |
| In reply to | #606 |
Jerry Stuckle wrote: > On 4/24/2011 9:45 AM, Tim Watts wrote: >> Jerry Stuckle wrote: >> >> >>> Also, file systems don't do well with thousands of users and hundreds of >>> thousands of images - they just weren't made to handle that many >>> individual files. RDBs such as MySQL, OTOH, easily handles millions of >>> users and hundreds of millions of images. >>> >>> Remember- a file system is just a non-relational database. It's good >>> for some things but not everything. >> >> That rather depends on the filesystem. >> >> XFS will happily handle 13TB with over 10 million files - that is from >> personal experience. >> >> Obviously VFAT would be a bad idea, and putting *all* the files on one >> directory *may* be a bad idea (depends on directory hashing support in >> the >> FS) - but with a sensible layout (one dir per user, possibly with a >> further >> sublayer of user's first letters a-z) it will be fine. >> >> Couple of points IMO - and I cannot speak for MySQL in particular - >> but what >> will it do for backups? >> >> With a small database and a big filesystem of images, the DB dump will be >> fine and the FS can be backed up with an rsync type methodology. >> >> With a huge number of images in a DB, I suspect the possibilities of >> doing >> incremental backups are vastly reduced and your DB dump file is ging >> to be >> huge. >> >> Just my opinion, based on Postgresql - but the principles mostly apply to >> MySQL unless it has some particular features to help with the problems I >> posed. >> >> Cheers, >> >> Tim > > Oh, and BTW - the script to serve images from the server does not cause > any noticeable slowdown - and in fact can be faster than retrieving from > the file system. > And will be cached by MySQL (depending on settings) anyway, so every request won't go to the disk every time. -- Norman Registered Linux user #461062 -Have you been to www.mysql.com yet?-
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-25 08:36 +0100 |
| Message-ID | <pskf88-451.ln1@squidward.dionic.net> |
| In reply to | #623 |
Norman Peelman wrote: > And will be cached by MySQL (depending on settings) anyway, so every > request won't go to the disk every time. > And to be fair, a decent FS on decent OS will do the same :) -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-25 06:34 -0400 |
| Message-ID | <ip3ioo$f0q$2@dont-email.me> |
| In reply to | #642 |
On 4/25/2011 3:36 AM, Tim Watts wrote: > Norman Peelman wrote: > > >> And will be cached by MySQL (depending on settings) anyway, so every >> request won't go to the disk every time. >> > > And to be fair, a decent FS on decent OS will do the same :) > Except a file systems has to cache every file accessed, while a database only caches the data accessed by the database - a much smaller amount of data. With the tuning of the database buffers, etc. available, there is a much greater likelihood the data will be available in the database's cache than the file system's cache. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-25 11:50 +0100 |
| Message-ID | <p80g88-ece.ln1@squidward.dionic.net> |
| In reply to | #650 |
Jerry Stuckle wrote: > On 4/25/2011 3:36 AM, Tim Watts wrote: >> Norman Peelman wrote: >> >> >>> And will be cached by MySQL (depending on settings) anyway, so every >>> request won't go to the disk every time. >>> >> >> And to be fair, a decent FS on decent OS will do the same :) >> > > Except a file systems has to cache every file accessed, while a database > only caches the data accessed by the database - a much smaller amount of > data. With the tuning of the database buffers, etc. available, there is > a much greater likelihood the data will be available in the database's > cache than the file system's cache. > No, a *nix filesystem will cache *blocks* accessed which may or may not include entire files. Linux also maintains the dcache, a specific cache of pathname lookups which further increase speed. Like most RDBMSs, Linux's use of FS cache is tunable. -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-25 07:20 -0400 |
| Message-ID | <ip3len$141$2@dont-email.me> |
| In reply to | #656 |
On 4/25/2011 6:50 AM, Tim Watts wrote: > Jerry Stuckle wrote: > >> On 4/25/2011 3:36 AM, Tim Watts wrote: >>> Norman Peelman wrote: >>> >>> >>>> And will be cached by MySQL (depending on settings) anyway, so every >>>> request won't go to the disk every time. >>>> >>> >>> And to be fair, a decent FS on decent OS will do the same :) >>> >> >> Except a file systems has to cache every file accessed, while a database >> only caches the data accessed by the database - a much smaller amount of >> data. With the tuning of the database buffers, etc. available, there is >> a much greater likelihood the data will be available in the database's >> cache than the file system's cache. >> > > No, a *nix filesystem will cache *blocks* accessed which may or may not > include entire files. > > Linux also maintains the dcache, a specific cache of pathname lookups which > further increase speed. Like most RDBMSs, Linux's use of FS cache is > tunable. > Exactly. And it needs to potentially cache every disk request - so on a normal system, it has to handle a lot more data than a database - which only handles database requests. Therefore its cache will be used a lot more and there is less chance of the data being available there. And the Linux FS is nowhere hear as configurable as any decent RDBMS. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | The Natural Philosopher <tnp@invalid.invalid> |
|---|---|
| Date | 2011-04-25 12:42 +0100 |
| Message-ID | <ip3mm9$4lc$1@news.albasani.net> |
| In reply to | #623 |
Norman Peelman wrote: > Jerry Stuckle wrote: >> On 4/24/2011 9:45 AM, Tim Watts wrote: >>> Jerry Stuckle wrote: >>> >>> >>>> Also, file systems don't do well with thousands of users and >>>> hundreds of >>>> thousands of images - they just weren't made to handle that many >>>> individual files. RDBs such as MySQL, OTOH, easily handles millions of >>>> users and hundreds of millions of images. >>>> >>>> Remember- a file system is just a non-relational database. It's good >>>> for some things but not everything. >>> >>> That rather depends on the filesystem. >>> >>> XFS will happily handle 13TB with over 10 million files - that is from >>> personal experience. >>> >>> Obviously VFAT would be a bad idea, and putting *all* the files on one >>> directory *may* be a bad idea (depends on directory hashing support >>> in the >>> FS) - but with a sensible layout (one dir per user, possibly with a >>> further >>> sublayer of user's first letters a-z) it will be fine. >>> >>> Couple of points IMO - and I cannot speak for MySQL in particular - >>> but what >>> will it do for backups? >>> >>> With a small database and a big filesystem of images, the DB dump >>> will be >>> fine and the FS can be backed up with an rsync type methodology. >>> >>> With a huge number of images in a DB, I suspect the possibilities of >>> doing >>> incremental backups are vastly reduced and your DB dump file is ging >>> to be >>> huge. >>> >>> Just my opinion, based on Postgresql - but the principles mostly >>> apply to >>> MySQL unless it has some particular features to help with the problems I >>> posed. >>> >>> Cheers, >>> >>> Tim >> >> Oh, and BTW - the script to serve images from the server does not >> cause any noticeable slowdown - and in fact can be faster than >> retrieving from the file system. >> > > And will be cached by MySQL (depending on settings) anyway, so every > request won't go to the disk every time. > The whole issue of performance is entirely application and location and usage dependent. you cannot say in any *absolute* terms which way will be better. One way may use more bandwidth, more CPU, but less RAM..in a given setting. My own preference is to put lots of rarely used images in the database, and the few one's that appear on every page, that you want the browser to cache - logos and the like - in flat file systems. I trade database size for a CPU performance hit but a faster user experience by rescaling images to thumbnails on the fly. Smaller data as no thumbnails are stored, better user experience because I don't use the browser to rescale so don't waste user bandwidth downloading more image size than is needed, but more CPU as the rescaling is processor intensive. This particular approach works well on a low traffic server with a LOT of data. It might be completely inappropriate on a server designed for an entirely different purpose.
[toc] | [prev] | [next] | [standalone]
| From | Luuk <Luuk@invalid.lan> |
|---|---|
| Date | 2011-04-24 16:27 +0200 |
| Message-ID | <4db4336c$0$81484$e4fe514c@news.xs4all.nl> |
| In reply to | #602 |
On 24-04-2011 14:42, Jerry Stuckle wrote: > On 4/24/2011 2:47 AM, Robert Crandal wrote: >> I just started learning about MySql in order to start building >> a website that stores a database of user information. I'm thinking >> that I want my database to keep track of user accounts, user profiles, >> and user information, etc.. etc... >> >> I am planning to allow each user to upload as many photos as >> they want into their personal account space, but my problem is >> that I don't know WHERE to store an unknown number of images >> in each user account. Can I store a collection of images in the >> MySql database somewhere? How? Also, what are some other >> ways to do this? >> >> Thank you! >> >> > > People have been using relational databases to store images for over 25 > years that I'm aware of - we were doing it at IBM in the mid-80's. They > store images just fine, and the more images you have, the better they > do. Just use a BLOB datatype. People are useing filesystems to store images even longer than this 25 years..... > > The only downside is you can't serve the image directly from the > database, but it just takes a short script do do so. Not a real big thing. > > There are a lot of advantages to using a database. For instance, if you > just store the filename in the database, what happens when someone > deletes the image? Or what if the filename is deleted from the database > but the file still exists (orphaned file). That has nothing to do with filesystems or databases, but more with security, What is someone does run a SQL script on your database like: DELETE FROM table_with_images; > > Also, file systems don't do well with thousands of users and hundreds of > thousands of images - they just weren't made to handle that many > individual files. RDBs such as MySQL, OTOH, easily handles millions of > users and hundreds of millions of images. > > Remember- a file system is just a non-relational database. It's good > for some things but not everything. I dont think there is a relation between the images, ...? so storing them in a database wont give any advantage. -- Luuk
[toc] | [prev] | [next] | [standalone]
| From | The Natural Philosopher <tnp@invalid.invalid> |
|---|---|
| Date | 2011-04-24 15:38 +0100 |
| Message-ID | <ip1ckl$lfu$1@news.albasani.net> |
| In reply to | #605 |
Luuk wrote: > On 24-04-2011 14:42, Jerry Stuckle wrote: >> On 4/24/2011 2:47 AM, Robert Crandal wrote: >>> I just started learning about MySql in order to start building >>> a website that stores a database of user information. I'm thinking >>> that I want my database to keep track of user accounts, user profiles, >>> and user information, etc.. etc... >>> >>> I am planning to allow each user to upload as many photos as >>> they want into their personal account space, but my problem is >>> that I don't know WHERE to store an unknown number of images >>> in each user account. Can I store a collection of images in the >>> MySql database somewhere? How? Also, what are some other >>> ways to do this? >>> >>> Thank you! >>> >>> >> People have been using relational databases to store images for over 25 >> years that I'm aware of - we were doing it at IBM in the mid-80's. They >> store images just fine, and the more images you have, the better they >> do. Just use a BLOB datatype. > > People are useing filesystems to store images even longer than this 25 > years..... > >> The only downside is you can't serve the image directly from the >> database, but it just takes a short script do do so. Not a real big thing. >> >> There are a lot of advantages to using a database. For instance, if you >> just store the filename in the database, what happens when someone >> deletes the image? Or what if the filename is deleted from the database >> but the file still exists (orphaned file). > > That has nothing to do with filesystems or databases, but more with > security, > What is someone does run a SQL script on your database like: > DELETE FROM table_with_images; > >> Also, file systems don't do well with thousands of users and hundreds of >> thousands of images - they just weren't made to handle that many >> individual files. RDBs such as MySQL, OTOH, easily handles millions of >> users and hundreds of millions of images. >> >> Remember- a file system is just a non-relational database. It's good >> for some things but not everything. > > > I dont think there is a relation between the images, ...? > so storing them in a database wont give any advantage. > Its a definite advantage to have all your data in a database where one backup regime can take care of it. The overheads of pulling the image out of a database rather than a file are often LESS..due to the rather better indexing on databases than on flat file systems. You still need a process - whether it be webserver pushing file, or web server pushing script connected to database, to output the image.. The bandwidth used is the same in either case. images in databases allow the use of the same security measures to deal with images as normal database accesses. The only advantages of a flat file system are that any incompetent programmer can make it work.
[toc] | [prev] | [next] | [standalone]
| From | Luuk <Luuk@invalid.lan> |
|---|---|
| Date | 2011-04-24 16:41 +0200 |
| Message-ID | <4db43694$0$81484$e4fe514c@news.xs4all.nl> |
| In reply to | #607 |
On 24-04-2011 16:38, The Natural Philosopher wrote: > The only advantages of a flat file system are that any incompetent > programmer can make it work. and some programmers have even problems with that ;) -- Luuk
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-24 19:07 +0100 |
| Message-ID | <bf5e88-fve.ln1@squidward.dionic.net> |
| In reply to | #607 |
The Natural Philosopher wrote: > The only advantages of a flat file system are that any incompetent > programmer can make it work. It may also be the case that you are using a shared/hosted MySQL host which is not designed for bulk data storage but you do have an additional filsesystem to one side. -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-24 14:46 -0400 |
| Message-ID | <ip1r5j$5pd$2@dont-email.me> |
| In reply to | #611 |
On 4/24/2011 2:07 PM, Tim Watts wrote: > The Natural Philosopher wrote: > > >> The only advantages of a flat file system are that any incompetent >> programmer can make it work. > > It may also be the case that you are using a shared/hosted MySQL host which > is not designed for bulk data storage but you do have an additional > filsesystem to one side. > > > I have never seen a MySQL host which isn't designed for storing data. And I've never seen a shared host with additional filesystems. You get what you've been given. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-24 21:01 +0100 |
| Message-ID | <k6ce88-3ra.ln1@squidward.dionic.net> |
| In reply to | #614 |
Jerry Stuckle wrote: > On 4/24/2011 2:07 PM, Tim Watts wrote: >> The Natural Philosopher wrote: >> >> >>> The only advantages of a flat file system are that any incompetent >>> programmer can make it work. >> >> It may also be the case that you are using a shared/hosted MySQL host >> which is not designed for bulk data storage but you do have an additional >> filsesystem to one side. >> >> >> > > I have never seen a MySQL host which isn't designed for storing data. Please quote me accurately Jerry, - I said "bulk data" not "data". In two of my previous jobs, we had a shared db host for lots of people. The DB host had one DB instance per user, but the host itself was shared amongst 500 people in one case (students) and in another case, 100 people (general ad hoc research). The host was well managed (automated backups etc) but it was specifically *not* its purpose to store more than a few hundred MB per user as the total capacity of the machine was not capable. I could see this being an issue with some genral purpose hosting solutions. > And I've never seen a shared host with additional filesystems. Conversely it's pretty standard where I work. > You get > what you've been given. > True - which is why the OP should consider both sides. But the debate is useful. Cheers, Tim -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-24 17:10 -0400 |
| Message-ID | <ip23kq$q5c$1@dont-email.me> |
| In reply to | #620 |
On 4/24/2011 4:01 PM, Tim Watts wrote: > Jerry Stuckle wrote: > >> On 4/24/2011 2:07 PM, Tim Watts wrote: >>> The Natural Philosopher wrote: >>> >>> >>>> The only advantages of a flat file system are that any incompetent >>>> programmer can make it work. >>> >>> It may also be the case that you are using a shared/hosted MySQL host >>> which is not designed for bulk data storage but you do have an additional >>> filsesystem to one side. >>> >>> >>> >> >> I have never seen a MySQL host which isn't designed for storing data. > > Please quote me accurately Jerry, - I said "bulk data" not "data". > OK, I've never seen a MySQL host which isn't designed for storing BULK DATA. That is the purpose of a database. > In two of my previous jobs, we had a shared db host for lots of people. The > DB host had one DB instance per user, but the host itself was shared amongst > 500 people in one case (students) and in another case, 100 people (general > ad hoc research). > > The host was well managed (automated backups etc) but it was specifically > *not* its purpose to store more than a few hundred MB per user as the total > capacity of the machine was not capable. > > I could see this being an issue with some genral purpose hosting solutions. > You can have the same problem with files. A poor host is no reflection on the tools. It is only a reflection on the host. >> And I've never seen a shared host with additional filesystems. > > Conversely it's pretty standard where I work. > I guess I use more reliable hosting companies. But then I don't use the cheapest around. I pay a little more and use reliable hosting. And none of them have an "additional file system" on the side. You have your storage. You could have access to another machine for backup purposes, but that is almost never (at least on a well-managed server) available to the internet, and not directly accessible. >> You get >> what you've been given. >> > > True - which is why the OP should consider both sides. But the debate is > useful. > > Cheers, > > Tim > > -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-25 08:23 +0100 |
| Message-ID | <o3kf88-l1u.ln1@squidward.dionic.net> |
| In reply to | #626 |
Jerry Stuckle wrote: > You can have the same problem with files. A poor host is no reflection > on the tools. It is only a reflection on the host. It is not a case of a "poor host" - it is a case of what that host was designed for. That's like saying a train is a poor car because it doesn't allow for door to door or flexible timings and the personal space is cramped. It does however carry hundreds of people effieciently. Just because a host was designed and installed to provide a 100 odd MB of DB storage to 100's users who also have 1-2GB of pure filestore each does not make it poor - it makes it a suitable solution for that job. If one user wanted a DB to store 100's GB, they could have that, but their research funds would be paying a few 1000 pounds for a suitable host for it. -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-25 06:39 -0400 |
| Message-ID | <ip3j1i$q59$1@dont-email.me> |
| In reply to | #639 |
On 4/25/2011 3:23 AM, Tim Watts wrote: > Jerry Stuckle wrote: > > >> You can have the same problem with files. A poor host is no reflection >> on the tools. It is only a reflection on the host. > > It is not a case of a "poor host" - it is a case of what that host was > designed for. > Nope. Anyone trying to run that much on one machine is a poor host. It doesn't matter what the design was. > That's like saying a train is a poor car because it doesn't allow for door > to door or flexible timings and the personal space is cramped. It does > however carry hundreds of people effieciently. > Sounds more like 10 clowns in a volkswagon, to me. > Just because a host was designed and installed to provide a 100 odd MB of DB > storage to 100's users who also have 1-2GB of pure filestore each does not > make it poor - it makes it a suitable solution for that job. > They are seriously overloading the server with the number of users which can run on it. I've seen hosts like this before - too cheap to buy the proper equipment. Fortunately they don't generally last long. > If one user wanted a DB to store 100's GB, they could have that, but their > research funds would be paying a few 1000 pounds for a suitable host for it. > Such sites are available all over the world for a lot less than "a few 1000 pounds". Under $100 is more like it. Even if they bought all the hardware themselves they're talking well under $1000. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
| From | Tim Watts <tw@dionic.net> |
|---|---|
| Date | 2011-04-25 12:00 +0100 |
| Message-ID | <hq0g88-pgh.ln1@squidward.dionic.net> |
| In reply to | #652 |
Jerry Stuckle wrote: > On 4/25/2011 3:23 AM, Tim Watts wrote: >> Jerry Stuckle wrote: >> >> >>> You can have the same problem with files. A poor host is no reflection >>> on the tools. It is only a reflection on the host. >> >> It is not a case of a "poor host" - it is a case of what that host was >> designed for. >> > > Nope. Anyone trying to run that much on one machine is a poor host. It > doesn't matter what the design was. > >> That's like saying a train is a poor car because it doesn't allow for >> door to door or flexible timings and the personal space is cramped. It >> does however carry hundreds of people effieciently. >> > > Sounds more like 10 clowns in a volkswagon, to me. > >> Just because a host was designed and installed to provide a 100 odd MB of >> DB storage to 100's users who also have 1-2GB of pure filestore each does >> not make it poor - it makes it a suitable solution for that job. >> > > They are seriously overloading the server with the number of users which > can run on it. I've seen hosts like this before - too cheap to buy the > proper equipment. Fortunately they don't generally last long. > >> If one user wanted a DB to store 100's GB, they could have that, but >> their research funds would be paying a few 1000 pounds for a suitable >> host for it. >> > > Such sites are available all over the world for a lot less than "a few > 1000 pounds". Under $100 is more like it. Even if they bought all the > hardware themselves they're talking well under $1000. > With respect, have you ever specificed equipment for and run a full sized machine room? (For this pupose, we are talking about a few hundred servers, around 100kW total power). The budget has to be spread over many competing requirements. $US 1000 does not buy a decent enterprise quality host and disks for high end use. It might buy something bottom end with a SATA drive or two. Now go and price a decent multicore machine with 4 SAS disks or at least 10-15k RPM SATAs minimum - 2 RAID1's, one for the OS + RDBMS journal/WAL and the other for the database backend files (except journal). That's the minimum sensible configuration I would run for a serious host expected to do a lot of work. Are you seriously suggesting that a university that provides a teaching database for all undergrads in a dept should spec a high end cluster so every UG can use it in a wasteful manner or specify one decent server that will serve several hundred concurrent users doing sensible things? Ditto research: The group who really needs a high end dedicated database system will be paying for it themselves. The general purpose host is a dept funded catchall for the groups who need a modest shared resource. Which bit of that does not count as good design and sensible allocation of resources? Cheers tim -- Tim Watts
[toc] | [prev] | [next] | [standalone]
| From | Jerry Stuckle <jstucklex@attglobal.net> |
|---|---|
| Date | 2011-04-25 07:27 -0400 |
| Message-ID | <ip3lr0$f67$1@dont-email.me> |
| In reply to | #657 |
On 4/25/2011 7:00 AM, Tim Watts wrote: > Jerry Stuckle wrote: > >> On 4/25/2011 3:23 AM, Tim Watts wrote: >>> Jerry Stuckle wrote: >>> >>> >>>> You can have the same problem with files. A poor host is no reflection >>>> on the tools. It is only a reflection on the host. >>> >>> It is not a case of a "poor host" - it is a case of what that host was >>> designed for. >>> >> >> Nope. Anyone trying to run that much on one machine is a poor host. It >> doesn't matter what the design was. >> >>> That's like saying a train is a poor car because it doesn't allow for >>> door to door or flexible timings and the personal space is cramped. It >>> does however carry hundreds of people effieciently. >>> >> >> Sounds more like 10 clowns in a volkswagon, to me. >> >>> Just because a host was designed and installed to provide a 100 odd MB of >>> DB storage to 100's users who also have 1-2GB of pure filestore each does >>> not make it poor - it makes it a suitable solution for that job. >>> >> >> They are seriously overloading the server with the number of users which >> can run on it. I've seen hosts like this before - too cheap to buy the >> proper equipment. Fortunately they don't generally last long. >> >>> If one user wanted a DB to store 100's GB, they could have that, but >>> their research funds would be paying a few 1000 pounds for a suitable >>> host for it. >>> >> >> Such sites are available all over the world for a lot less than "a few >> 1000 pounds". Under $100 is more like it. Even if they bought all the >> hardware themselves they're talking well under $1000. >> > > With respect, have you ever specificed equipment for and run a full sized > machine room? (For this pupose, we are talking about a few hundred servers, > around 100kW total power). The budget has to be spread over many competing > requirements. > Ah, a small datacenter, for sure. But not well designed or managed, from what you've already indicated. > $US 1000 does not buy a decent enterprise quality host and disks for high > end use. It might buy something bottom end with a SATA drive or two. Now go > and price a decent multicore machine with 4 SAS disks or at least 10-15k RPM > SATAs minimum - 2 RAID1's, one for the OS + RDBMS journal/WAL and the other > for the database backend files (except journal). That's the minimum sensible > configuration I would run for a serious host expected to do a lot of work. > Sure it does. You just need to know where to go, which you make obvious you have no idea. But that's also off topic here. > Are you seriously suggesting that a university that provides a teaching > database for all undergrads in a dept should spec a high end cluster so > every UG can use it in a wasteful manner or specify one decent server that > will serve several hundred concurrent users doing sensible things? > I'm saying a university should know better than to try to cram so many users into one server. > Ditto research: The group who really needs a high end dedicated database > system will be paying for it themselves. The general purpose host is a dept > funded catchall for the groups who need a modest shared resource. > > Which bit of that does not count as good design and sensible allocation of > resources? > > Cheers > > tim > To start with, hundreds of users on one server, especially in a university environment. But that's also off topic here. -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ==================
[toc] | [prev] | [next] | [standalone]
Page 3 of 6 — ← Prev page 1 2 [3] 4 5 6 Next page →
Back to top | Article view | comp.databases.mysql
csiph-web