Actually you bring up a good point. A huge behind-the-scenes use for
the MEMORY engine is for temporary tables. Having BLOB/TEXT support
for these would be really nice. Pointing tmpdir to a ramdisk always
leaves a bad taste in my mouth. I'd almost think that would be the
first feature we should be talking about, even above HASH/BTREE
(since, correct me if I'm wrong, internal temporary tables use neither).
Tim
On Apr 27, 2009, at 4:33 PM, Eric Bergen wrote:
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
<[email protected]> 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 <[email protected]>
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 : [email protected]
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 : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
--
Eric Bergen
[email protected]
http://www.ebergen.net
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
