Probably another helpful solution may be to implement:
ALTER TABLE LOGGING OFF/ON;
just to disable/enable WAL?
First it may help in all cases of intensive data load while you slow
down other sessions with increasing WAL activity.
Then you have a way to implement MEMORY-like tables on RAM disk
tablespace (well, you still need to take care to drop them
auto-manually :))
However, if we speak about performance of MEMORY table - it should be
much better in Tom's solution with big temp buffers rather RAM disk...
The strong point in implementation of MEMORY table is it *knows* it
sits in RAM! and it changes completely all I/O kind logic...
BTW, before NDB was bough by MySQL we done a benchmark to rich a
highest possible TPS numbers with it. We got 1.500.000 TPS(!) (yes,
one million and half per second!) knowing all current TPC records are
measured in thousands of transactions per minute - you see impact...
And of course for my education I tried to do the same with other
database vendors running only SELECT queries and placing tablespaces
on RAM disk... After trying all possible combinations I was still
*very* far :))
MEMORY databases is something like a parallel world, very interesting,
but very different :))
Rgds,
-Dimitri
On 4/3/07, A.M. <agentm@themactionfaction.com> wrote:
>
> On Apr 3, 2007, at 16:00 , Alan Hodgson wrote:
>
> > On Tuesday 03 April 2007 12:47, "A.M."
> > <agentm@themactionfaction.com> wrote:
> >> On Apr 3, 2007, at 15:39 , C. Bergström wrote:
> >> I would like to use transactional semantics over tables that can
> >> disappear whenever the server fails. memcached does not offer that.
> >
> > How would temporary tables?
>
> The only difference between temporary tables and standard tables is
> the WAL. Global temporary tables would be accessible by all sessions
> and would be truncated on postmaster start. For a further potential
> speed boost, global temp tables could be put in a ramdisk tablespace.
>
> Well, that's at least how I envision them.
>
> Cheers,
> M
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>