List:Cluster« Previous MessageNext Message »
From:Andrew Morgan Date:March 15 2013 7:38am
Subject:RE: Alternative to BLOBs in disk based tables.
View as plain text  

> -----Original Message-----
> From: Puneet Sachdev [mailto:puneet.sachdev@stripped]
> Sent: 15 March 2013 05:32
> To: Andrew Morgan; Wagner Bianchi
> Cc: cluster@stripped
> Subject: RE: Alternative to BLOBs in disk based tables.
> 
> Hi Andrew and Wanger,
> 
> 
> 
> It seems that there is certainly a lack of clarification in the documentation.
> We will need to do a lot of testing to check whether setting the parameter
> [ndbinfo.diskpagebuffer] will work or not. I will surely share my results of the
> same with you and if any of you get a chance to analyze the performance of
> the database cluster with the same parameter set at various values kindly
> share your results.

[AM] The model is fairly straightforward. You configure the size of the cache by setting
DiskPageBuffer for the data nodes (through MCM or in your config.ini file); the memory is
then allocated and used as an LRU for the disk data as it's accessed. You can then use
ndbinfo.diskpagebuffer or the graph in MySQL Enterprise Monitor to monitor how effective
it's being.
 
> 
> By the time we do that, I would like to know something related to shifting
> the images from database disk to the NAS server.
> 
> Andrew said that: " If you really need the data to come via. the database you
> could use a UDF to pull the blob data from the file based on the filename in
> the record and return it as part of a row."

[AM] I believe that this came from elsewhere. Having said that, storing media outside of
the database is typically the best approach for applications such as content management
stores, media stores etc. (this is true whether using MySQL Cluster or other databases).
I'm not familiar with the approach of using UDFs to make it transparent to the
application - a bit of Googling unearthed some examples but I wouldn't want to point to
one as being better than another.

> 
> Can you please tell me something more about this procedure? A brief
> clarification will be very useful to me.
> 
> 
> 
> Thanks.
> 
> 
> 
> ---
> 
> Regards
> 
> Puneet Sachdev
> 
> Software Developer
> 
> Northern Lights Studio Pvt. Ltd.
> 
> E-mail: puneet.sachdev@stripped
> 
> Mob: +91-9406928799
> 
> 
> 
> 
> 
> From: Andrew Morgan [mailto:andrew.morgan@stripped]
> Sent: 14 March 2013 23:30
> To: Wagner Bianchi
> Cc: Puneet Sachdev; cluster@stripped
> Subject: RE: Alternative to BLOBs in disk based tables.
> 
> 
> 
> Hi Wagner,
> 
> 
> 
> I'm not aware of any document that goes into any great detail but I believe
> that it's a fairly straight-forward LRU cache where the size of the cache is
> controlled using the DiskPageBuffer parameter. The easiest way to see how
> well your caching is performing is via MySQL Enterprise Monitor as described
> here.
> http://www.clusterdb.com/mysql-cluster/further-mysql-cluster-additions-
> to-my
> sql-enterprise-monitor/
> 
> 
> 
> Regards, Andrew.
> 
> 
> 
> From: Wagner Bianchi [mailto:wagnerbianchijr@stripped]
> Sent: 14 March 2013 15:17
> To: Andrew Morgan
> Cc: Puneet Sachdev; cluster@stripped
> Subject: Re: Alternative to BLOBs in disk based tables.
> 
> 
> 
> Morgan,
> 
> 
> 
> Geetting a ride on this note, could you share a doc or link which explains
> about the cluster's cache-hit flow?
> 
> 
> 
> I will appreciated that, cheers.
> 
> 
> 
> 
> Best wishes,
> 
> --
> 
> Wagner Bianchi, Oracle ACE - wagnerbianchi.com
> <http://wagnerbianchi.com/>
> 
> Mobile: +55 (31) 8654 - 9510
> 
> Twitter: @wagnerbianchijr
> 
> Skype: wbianchijr / @wbcon
> 
> --
> 
> Blog: wagnerbianchi.com/blog
> 
> LinkedIn: http://br.linkedin.com/in/wagnerbianchi
> 
> Lattes: http://lattes.cnpq.br/2041067758113940
> 
> Oracle ACE: http://tinyurl.com/cyltkyz
> 
> Company: http://wbconsulting.com.br
> 
> 
> 
> 
> 
> 
> 
> 2013/3/14 Andrew Morgan <andrew.morgan@stripped>
> 
> Hi Puneet,
> 
>  It's certainly a trade-off. Note that you can use the [ndbinfo.diskpagebuffer
> table]( http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbinfo-
> diskpagebuffer.
> html) to monitor how effective the caching is. If you have MySQL Enterprise
> Monitor [download from e-delivery](http://www.edelivery.oracle.com)
> then you can see the cache-hit ration in a nice graph.
> 
> Regards, Andrew.
> 
> > -----Original Message-----
> > From: Puneet Sachdev [mailto:puneet.sachdev@stripped]
> > Sent: 14 March 2013 13:28
> > To: Andrew Morgan
> > Subject: RE: Alternative to BLOBs in disk based tables.
> >
> > No, actually I haven't tried it yet. I will try it in my test
> > environment though. But don't you think this will affect the response
> > time of the application. By response time I mean that if we limit the
> > RAM usage for disk data(i.e. blobs), then the RAM will exhaust sooner
> > than before and the waiting time for a customer to fetch his desired
> > data will decrease?
> >
> >
> > -----Original Message-----
> > From: Andrew Morgan [mailto:andrew.morgan@stripped]
> > Sent: 14 March 2013 18:33
> > To: Puneet Sachdev
> > Subject: RE: Alternative to BLOBs in disk based tables.
> >
> > In general, you can control the size of the memory cache used for disk
> > data using
> >
> [DiskPageBufferMemory](http://dev.mysql.com/doc/refman/5.5/en/mysql-
> > cluster-
> > ndbd-definition.html#ndbparam-ndbd-diskpagebuffermemory). I confess
> > that I haven't tried it with disk-based Blobs - have you?
> >
> > Andrew.
> >
> > > -----Original Message-----
> > > From: Puneet Sachdev [mailto:puneet.sachdev@stripped]
> > > Sent: 14 March 2013 12:53
> > > To: Andrew Morgan
> > > Subject: RE: Alternative to BLOBs in disk based tables.
> > >
> > > Hi Andrew,
> > >
> > > Yes, we have made the necessary changes to store the blobs on the
> > > storage disk and this have been working pretty well till now. But
> > > the problem which we suppose will now arise is, the image size which
> > > we were storing was 12-20 kb but will now increase to almost 60kb
> > > (even after compression). It means that even after using the
> > > mechanism of storing the blobs on the disk, there is still a chance
> > > that the RAM
> > is
> > > extensively used while indexing the image requests and it may crash
> > > the system.
> > >
> > > Do you know any alternate so that while fetching the images from the
> > > database, they will not be brought on to the RAM of the database
> > > server, so that we can prevent the extensive use of RAM and prevent
> > > the system from crashing.
> > >
> > > Is there any other possible work around to prevent the RAM from
> > > exhausting.
> > >
> > > Thanks in advance.
> > >
> > > -----Original Message-----
> > > From: Andrew Morgan [mailto:andrew.morgan@stripped]
> > > Sent: 14 March 2013 18:12
> > > To: Puneet Sachdev; cluster@stripped
> > > Subject: RE: Alternative to BLOBs in disk based tables.
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Puneet Sachdev [mailto:puneet.sachdev@stripped]
> > > > Sent: 14 March 2013 10:50
> > > > To: cluster@stripped
> > > > Subject: Re: Alternative to BLOBs in disk based tables.
> > > >
> > > > Unfortunately there is no alternative to this yet with MySQL
> > Cluster.
> > > I
> > > > believe there is work in progress to improve blob handling but I
> > > cannot
> > > > say whether this would help in your application. I would recommend
> > > > alternative methods such as storing the blobs as files on SAN/NFS
> > > > and the filenames for the blobs in the database. If you really
> > > > need the data to come via. the database you could use a UDF to
> > > > pull the blob data from the file based on the filename in the
> > > > record and return it
> > > as
> > > > part of a row.
> > > >
> > > > On a related note if you are using mysqld for your API nodes it
> > > > would be a good idea to wait for 7.0.8 to be released. There are a
> > > > few
> > > leaks
> > > > and late memory release problems related to blobs fixed in mysqld
> > in
> > > > this version.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "I would recommend alternative methods such as storing the blobs
> > > > as files on SAN/NFS and the filenames for the blobs in the database."
> > > >
> > > > Can you help me with the procedure of how to implement it?
> > > >
> > > >
> > > >
> > > > " If you really need the data to come via. the database you could
> > > > use
> > > a
> > > > UDF to pull the blob data from the file based on the filename in
> > the
> > > > record and return it as part of a row."
> > > > Can you please elaborate its procedure a bit?
> > > >
> > > >
> > > > "On a related note if you are using mysqld for your API nodes it
> > > would
> > > > be a good idea to wait for 7.0.8 to be released. There are a few
> > > leaks
> > > > and late memory release problems related to blobs fixed in mysqld
> > in
> > > > this version."
> > > >
> > > > Since 7.1.X series is currently in use, is there any improvement
> > > > in
> > > the
> > > > BLOB storage mechanism. The architecture we are currently
> > > implementing
> > > > is still consuming a lot of RAM (even after using BLOB storage).
> > > >
> > >
> > > Have you setup your tables such that the BLOBs will be stored on
> > > disk (by default they'll be in RAM. This section of the Cluster docs
> > > explains how to configure disk-based data (whether it be for BLOBs,
> > > other columns or entire (excluding indexed columns) tables....
> > > http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-disk-data.html
> > >
> > > > ---
> > > >
> > > > Regards
> > > >
> > > > Puneet Sachdev
> > > >
> > > > Software Developer
> > > >
> > > > Northern Lights Studio Pvt. Ltd.
> > > >
> > > > E-mail: puneet.sachdev@stripped
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> >
> 
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster
> 
> 
> 
Thread
Re: Alternative to BLOBs in disk based tables.Puneet Sachdev14 Mar
  • RE: Alternative to BLOBs in disk based tables.Andrew Morgan14 Mar
RE: Alternative to BLOBs in disk based tables.Andrew Morgan14 Mar
  • Re: Alternative to BLOBs in disk based tables.Wagner Bianchi14 Mar
    • RE: Alternative to BLOBs in disk based tables.Andrew Morgan14 Mar
      • RE: Alternative to BLOBs in disk based tables.Puneet Sachdev15 Mar
        • RE: Alternative to BLOBs in disk based tables.Andrew Morgan15 Mar