Store images in db or folder?

#61

Jacek Krysztofik wrote:

Quote:

On 26.09.2011 18:50, The Natural Philosopher wrote:
Or 10s of requests per minute.
I would guess that depends on how much bandwidth you have.

Theres not many NICs can keep up with a big CPU and lots of ran,,,
Therefore, if you send files slowly and the new requests build up, DB
connections and buffers follow suit.
yes, but it doesn't matter, because the files cant crawl out any faster

anyway..

#62

On 2011-09-26, The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:

Quote:

Axel Schwenke wrote:
The Natural Philosopher <tnp (AT) invalid (DOT) invalid> wrote:
Axel Schwenke wrote:

[snip]

That was worth the reading. Thank you.
..and also suggest ways in which one aspect of MySQL COULD be improved..

It's not terribly much what you can change shorthand.

- the server/client protocol cannot be changed without breaking
compatibility to third-party connectors

- the storage engine API cannot be changed without breaking
compatibility to third-party storage engines

- the extra copy in the buffer pool cannot be avoided at all

however there is some light at the end of the tunnel:

There is support for SHM transport if you use Windoze. I guess
this is mostly due to the abominable performance of other IPC
methods in Windoze <eg

When server-side prepared statements were introduced (in 5.0)
a new BLOP API call was added to send a BLOB to the server in
pieces. This not only lifts the max_allowed_packet limit, it
also makes communication more memory efficient. Unfortunately
the same was not implemented for reading BLOBs (please don't
ask me why, it sooo makes no sense). Still there is hope that
the missing piece will be implemented some time.

If one thinks about the problem in more depth, then you come to
the conclusion that the POSIX file I/O API is close to perfect
and you want to duplicate it for access to BLOBs. Specifically
you need seek() and the ability to access only parts of a BLOB.
sendfile() and splice() would then be the whipped cream on top

And of course not of that is news for the people working at
MySQL AB^W^W Sun Micr^W^W Oracle:

http://forge.mysql.com/worklog/task.php?id=1042
http://forge.mysql.com/worklog/task.php?id=3583

yerss.

SO it looks like PROVIDED the blobs are below full 'in memory' size its
not a huge overhead...but once they start to exhaust RAM you are in deep
shit.

as in "Practical limit is some 256-512M on 32bit Boxes"

Good enough for images, but not large multipage PDFS with embedded images.

Yep, that was exactly my case. It covered about two PDFs out of 4800,
but those just happen to not work with MySQL.

i

#63

On 26.09.2011 20:27, The Natural Philosopher wrote:

Quote:

connections and buffers follow suit.
yes, but it doesn't matter, because the files cant crawl out any faster
anyway..
Er... actually, for this exact reason it does matter. You fill up the

memory with lingering buffers for hundreds of files.

#64

On 2011-09-26, Jacek Krysztofik <default (AT) tlen (DOT) pl> wrote:

Quote:

On 26.09.2011 20:27, The Natural Philosopher wrote:
connections and buffers follow suit.
yes, but it doesn't matter, because the files cant crawl out any faster
anyway..
Er... actually, for this exact reason it does matter. You fill up the
memory with lingering buffers for hundreds of files.

Sadly, it is true, but for files up to a few megs it does not matter
that much.

i

#65

Jacek Krysztofik wrote:

Quote:

On 26.09.2011 20:27, The Natural Philosopher wrote:
connections and buffers follow suit.
yes, but it doesn't matter, because the files cant crawl out any faster
anyway..
Er... actually, for this exact reason it does matter. You fill up the
memory with lingering buffers for hundreds of files.

same happens serving em off disk..

#66

On 26.09.2011 21:15, The Natural Philosopher wrote:

Quote:

same happens serving em off disk..

Not even nearly on that scale. Read what Axel wrote in this here thread
2011-09-26@14:31UTC.

#67

Jacek Krysztofik wrote:

Quote:

On 26.09.2011 21:15, The Natural Philosopher wrote:
same happens serving em off disk..

Not even nearly on that scale. Read what Axel wrote in this here thread
2011-09-26@14:31UTC.
I did.

#68

Jacek Krysztofik <default (AT) tlen (DOT) pl> wrote:

Quote:

On 26.09.2011 21:15, The Natural Philosopher wrote:

same happens serving em off disk..

Not even nearly on that scale.

Exactly.

Quote:

Read what Axel wrote in this here thread

The point is, that the MySQL server has 3 copies of the row before even
the first byte is sent to the MySQL client (web server). The fourth and
maybe fifth copy of the data exists before even a single byte is sent
to the HTTP client (browser).

And those are *full* copies of the BLOB. If a file is served off disk,
it can be streamed. There will be small buffers involved (8-16K typ.)
but at no time a full copy of the file is needed in memory.

XL

#69

On 27.09.2011 09:13, Axel Schwenke wrote:

Quote:

The point is, that the MySQL server has 3 copies of the row before even
the first byte is sent to the MySQL client (web server). The fourth and
maybe fifth copy of the data exists before even a single byte is sent
to the HTTP client (browser).
So in high load conditions it's better to ask MySQL for a number of

short datasets, that is - fetch a set of rows from primary table with
joined _conditions_ and then fetch rows from joined tables with /FK
IN(...)/. This way the memory is freed up quickly.

Is that correct?

JK

#70

Jacek Krysztofik <default (AT) tlen (DOT) pl> wrote:

Quote:

On 27.09.2011 09:13, Axel Schwenke wrote:

The point is, that the MySQL server has 3 copies of the row before even
the first byte is sent to the MySQL client (web server). The fourth and
maybe fifth copy of the data exists before even a single byte is sent
to the HTTP client (browser).

So in high load conditions it's better to ask MySQL for a number of
short datasets, that is - fetch a set of rows from primary table with
joined _conditions_ and then fetch rows from joined tables with /FK
IN(...)/. This way the memory is freed up quickly.
Is that correct?

I don't think that you got my point. For technical reasons the MySQL
server keeps at least two copies of each result row in memory. One copy
is the network buffer (up to max_allowed_packet) and the other is in
the communication buffers between SQL- and storage engine layer.

Those buffers will hurt when you transfer big BLOBs. They will hurt
even more when your result set is just a single row because then the
whole result is buffered twice in memory. They will hurt ultimately
when due to high load all allowed connections into the MySQL server
are busy fetching such BLOBs.

The third copy in the InnoDB buffer pool is a different story because
the size of the buffer pool can be configured explicitly. But many
people don't realize that there is also a potential memory usage of
(max_connections * max_allowed_packet * 2) for network buffers. And
(table_open_cache * max. BLOB size) for temporary BLOB buffers.

The network buffers will be deallocated when the connection is closed
(now go figure if connection pooling is a good idea). The table cache
can be flushed with FLUSH TABLES.

For normal workload none of that is a big problem. But then again
storing big BLOBs is not regarded normal. Not for MySQL.

XL