Re: Blob implementation question

[Adam Ilardi]

Engine:  MyISAM?  InnoDB?  other?
InnoDB

Let's see the SELECT.
select bytes from table_name where id = %d

If InnoDB, let's see the transaction, if it is part of such.
It's a single query

If InnoDB, which (COMPACT, etc) are you using.
Not sure

You are asking about a single row with the 500MB, correct?
Yes

Thanks for the additional details


On Wed, Mar 13, 2013 at 1:00 PM, Rick James rja...@yahoo-inc.com wrote:

 A lot of details are missing...
 Engine:  MyISAM?  InnoDB?  other?
 Let's see the SELECT.
 If InnoDB, let's see the transaction, if it is part of such.
 If InnoDB, which (COMPACT, etc) are you using.
 You are asking about a single row with the 500MB, correct?

 In general, each request will ask for the same row, and will be blocked at
 some level.  The data will be fetched from disk and cached (radically
 differently, depending on the Engine).  Each request will be satisfied --
 perhaps sequentially, perhaps simultaneously.

 The resultset will need to be built at some point.  This will probably
 take up 500MB+ of extra RAM.  This might lead to swapping or running out of
 RAM.

 If the SELECT needs to build a temp table, it will be MyISAM, and it will
 be on disk.  But not all SELECTs need to build a temp table.  This, for
 example, won't:
 SELECT myblob FROM mytable WHERE id=123;
 This probably will (if foo is not indexed):
 SELECT myblob FROM mytable ORDER BY foo;

  -Original Message-
  From: Adam Ilardi [mailto:mastaskill...@gmail.com]
  Sent: Wednesday, March 13, 2013 9:16 AM
  To: mysql
  Subject: Blob implementation question
 
  Hello All,
 
  I'm trying to grok the blob implementation. This scenario is contrived
  to understand blobs please don't suggest I shouldn't do this. If you
  have a theoretical machine. Ubuntu with 4 cores/4gb of ram and we'll
  say once mysql is running 500mb of free disk space.
 
  I have a 500mb blob stored in a table and 30 concurrent requests come
  in to select the blob's bytes. How does mysql handle this situation @
  an implementation level?
 
  Would mysql buffer the blob data to the disk?
  Would mysql keep 30 large in memory buffers for the data?
 
  I'd like to know when I would be @ risk of either filling up the disk
  or running out of ram in this situation. I'm also curious as to the
  code level details about how blobs are read and transmitted to a
  client.
 
 
  Thanks,
  Adam

RE: Blob implementation question

[Rick James]

A lot of details are missing...
Engine:  MyISAM?  InnoDB?  other?
Let's see the SELECT.
If InnoDB, let's see the transaction, if it is part of such.
If InnoDB, which (COMPACT, etc) are you using.
You are asking about a single row with the 500MB, correct?

In general, each request will ask for the same row, and will be blocked at some 
level.  The data will be fetched from disk and cached (radically differently, 
depending on the Engine).  Each request will be satisfied -- perhaps 
sequentially, perhaps simultaneously.

The resultset will need to be built at some point.  This will probably take 
up 500MB+ of extra RAM.  This might lead to swapping or running out of RAM.

If the SELECT needs to build a temp table, it will be MyISAM, and it will be on 
disk.  But not all SELECTs need to build a temp table.  This, for example, 
won't:
SELECT myblob FROM mytable WHERE id=123;
This probably will (if foo is not indexed):
SELECT myblob FROM mytable ORDER BY foo;

 -Original Message-
 From: Adam Ilardi [mailto:mastaskill...@gmail.com]
 Sent: Wednesday, March 13, 2013 9:16 AM
 To: mysql
 Subject: Blob implementation question
 
 Hello All,
 
 I'm trying to grok the blob implementation. This scenario is contrived
 to understand blobs please don't suggest I shouldn't do this. If you
 have a theoretical machine. Ubuntu with 4 cores/4gb of ram and we'll
 say once mysql is running 500mb of free disk space.
 
 I have a 500mb blob stored in a table and 30 concurrent requests come
 in to select the blob's bytes. How does mysql handle this situation @
 an implementation level?
 
 Would mysql buffer the blob data to the disk?
 Would mysql keep 30 large in memory buffers for the data?
 
 I'd like to know when I would be @ risk of either filling up the disk
 or running out of ram in this situation. I'm also curious as to the
 code level details about how blobs are read and transmitted to a
 client.
 
 
 Thanks,
 Adam

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql