← Back to team overview

drizzle-discuss team mailing list archive

Re: MEMORY Engine

 

The most common places I have seen people use explicit heap tables is
to get around some speed limitation in other engines (wanting hash
index). If the other engines can implement the index types then the
heap engine is really only interesting for resolving queries using
order/group by (explain 'Using temporary'). Making the process of
reading a row from a storage engine, unpacking it, then repacking it
into heap much faster should be a big win. The second win is
eliminating disk based internal temporary tables when a query has a
blob/text column. A lot of the time disk based temp tables are used
when the memory limit isn't hit simply because of a text/blob column.

I don't know the internals of mysql very well around this process to
feel free to correct me.

-Eric

On Mon, Apr 27, 2009 at 2:01 PM, Tim Soderstrom
<tim@xxxxxxxxxxxxxxxxxxxxx> wrote:
> Well, I mean memcached is awesome but, to be fair, it's not DB-centric and
> you have to do a bit of work to hook it up to your application. MEMORY
> tables are a nice middle road between a full-blown memcached setup and not
> using anything. Besides, you can join stuff together with a MEMORY table.
>
> For the Facebooks, Diggs, Craigslists, etc. of the world, yes, you would
> want to use memcached. But for the smaller guys that don't need to hit the
> gas that hard that soon, the MEMORY engine has some good benefits.
>
> I deal with a lot of customers who don't even know what memcached is and so,
> for them, MEMORY can provide some benefits that they otherwise didn't have.
>
> Is memcached better? Well, in many cases hell yeah, but it's not the only
> tool nor is it always going to be the right tool.
>
> *shrug*
>
> Tim S.
>
> On Apr 27, 2009, at 3:41 PM, Jeremy Zawodny wrote:
>
>> This doesn't directly answer the question, but here's my 2 cents from a
>> few
>> high-volume MySQL/Web shops on the memory engine...
>>
>> The decision tree is often like this:
>>
>> If you need the data to persist, put it in MySQL (probably InnoDB) and
>> replicate it.
>>
>> If you need FAST access, put it in memcached.
>>
>> If you need FAST access and persistence, use MySQL behind memcached.  You
>> probably didn't need to do SQL queries anyway.
>>
>> Now I know that's an over-simplification, but it's something I've seen
>> time
>> and again.
>>
>> If the world suddenly had lots of 128GB servers, and a memory engine that
>> scaled *really well* on multi-core, and could handle key lookups and range
>> scans, *and* (this is the kicker) had an on-disk journal or some sort of
>> built-in persistence... that might be different.
>>
>> It's interesting that a couple of the new appliance vendors at the MySQL
>> Conference used the same basic hardware to build BOTH memcached and
>> MySQL/InnoDB appliances.
>>
>> Makes you wonder what'd happen if drizzle had a memcached protocol plugin
>> and was running on a box with SSD storage, etc... :-)
>>
>> Jeremy
>>
>> On Mon, Apr 27, 2009 at 11:51 AM, Brian Aker <brian@xxxxxxxxxxx> wrote:
>>
>>> Hi!
>>>
>>> Has anyone ever done any sort of benchmarks on the B-tree indexes on the
>>> Memory engine? Are people using them or are they just sticking to the
>>> default HASH indexes?
>>>
>>> Two reasons I am asking:
>>> 1) It would be simple to improve performance for the HEAP indexes, but
>>> the
>>> B-tree are much more problematic.
>>> 2) Are there any outstanding users of this feature? Or is this untested
>>> at
>>> large?
>>>
>>> We badly need a new memory engine, it is high on my list. We can get a
>>> bit
>>> more out of the current design but it would mean dropping
>>> functionality/etc.
>>>
>>> Cheers,
>>>      -Brian
>>>
>>>
>>> _______________________________________________
>>> Mailing list:
>>> https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss>
>>> Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe :
>>> https://launchpad.net/~drizzle-discuss<https://launchpad.net/%7Edrizzle-discuss>
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~drizzle-discuss
>> Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~drizzle-discuss
>> More help   : https://help.launchpad.net/ListHelp
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~drizzle-discuss
> Post to     : drizzle-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~drizzle-discuss
> More help   : https://help.launchpad.net/ListHelp
>



-- 
Eric Bergen
eric.bergen@xxxxxxxxx
http://www.ebergen.net



Follow ups

References