pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedEquivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
17+ messages / 10 participants
[nested] [flat]
* Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
@ 2007-04-03 18:59 Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:08 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 17+ messages in thread
From: Arnau @ 2007-04-03 18:59 UTC (permalink / raw)
To: pgsql-performance
Hi all,
In MySQL when you create a table you can define something like:
CREATE TABLE `sneakers` (
`sneaker_id` char(24) NOT NULL,
`sneaker_time` int(10) unsigned NOT NULL default '0',
`sneaker_user` int(10) unsigned NOT NULL default '0',
UNIQUE KEY `sneaker_id` (`sneaker_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1000;
MySQL manual says:
"The MEMORY storage engine creates tables with contents that are stored
in memory. As indicated by the name, MEMORY tables are stored in memory.
They use hash indexes by default, which makes them very fast, and very
useful for creating temporary tables. However, when the server shuts
down, all rows stored in MEMORY tables are lost. The tables themselves
continue to exist because their definitions are stored in .frm files on
disk, but they are empty when the server restarts.
MAX_ROWS can be used to determine the maximum and minimum numbers of rows"
Is there anything similar in PostgreSQL? The idea behind this is how I
can do in PostgreSQL to have tables where I can query on them very often
something like every few seconds and get results very fast without
overloading the postmaster.
Thank you very much
--
Arnau
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
@ 2007-04-03 19:05 ` Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-04 08:50 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
1 sibling, 2 replies; 17+ messages in thread
From: Josh Berkus @ 2007-04-03 19:05 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau,
> Is there anything similar in PostgreSQL? The idea behind this is how I
> can do in PostgreSQL to have tables where I can query on them very often
> something like every few seconds and get results very fast without
> overloading the postmaster.
If you're only querying the tables every few seconds, then you don't
really need to worry about performance.
--Josh Berkus
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
@ 2007-04-03 19:16 ` A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
1 sibling, 1 reply; 17+ messages in thread
From: A.M. @ 2007-04-03 19:16 UTC (permalink / raw)
To: pgsql-performance
Indeed... I looked through the official TODO list and was unable to
find an entry for global temporary tables- such a thing would be
ideal for any transient data such as web sessions or materialized
views. Is there any reason why global temp tables shouldn't be
implemented? (And, no, I'm not simply referring to "in-memory"
tables- they can simply be handled with a ram disk.)
-M
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
@ 2007-04-03 19:39 ` "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
0 siblings, 1 reply; 17+ messages in thread
From: "C. Bergström" @ 2007-04-03 19:39 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
A.M. wrote:
> Indeed... I looked through the official TODO list and was unable to
> find an entry for global temporary tables- such a thing would be ideal
> for any transient data such as web sessions or materialized views. Is
> there any reason why global temp tables shouldn't be implemented?
> (And, no, I'm not simply referring to "in-memory" tables- they can
> simply be handled with a ram disk.)
Not exactly what you're looking for and a simple API, but the
performance is very nice and has a lot of potential.
http://pgfoundry.org/projects/pgmemcache/
Implementing a cleaner more transparent sql wrapper would be even nicer.
http://tangent.org/index.pl?lastnode_id=478&node_id=506
Just sharing/tossing some ideas around..
C.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
@ 2007-04-03 19:47 ` A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 1 reply; 17+ messages in thread
From: A.M. @ 2007-04-03 19:47 UTC (permalink / raw)
To: pgsql-performance
On Apr 3, 2007, at 15:39 , C. Bergström wrote:
> A.M. wrote:
>> Indeed... I looked through the official TODO list and was unable
>> to find an entry for global temporary tables- such a thing would
>> be ideal for any transient data such as web sessions or
>> materialized views. Is there any reason why global temp tables
>> shouldn't be implemented? (And, no, I'm not simply referring to
>> "in-memory" tables- they can simply be handled with a ram disk.)
> Not exactly what you're looking for and a simple API, but the
> performance is very nice and has a lot of potential.
>
> http://pgfoundry.org/projects/pgmemcache/
I would like to use transactional semantics over tables that can
disappear whenever the server fails. memcached does not offer that.
Cheers,
M
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
@ 2007-04-03 20:00 ` Alan Hodgson <ahodgson@simkin.ca>
2007-04-03 20:06 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
0 siblings, 1 reply; 17+ messages in thread
From: Alan Hodgson @ 2007-04-03 20:00 UTC (permalink / raw)
To: pgsql-performance
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?
--
Ginsberg's Theorem:
1) You can't win.
2) You can't break even.
3) You can't quit the game.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Alan Hodgson <ahodgson@simkin.ca>
@ 2007-04-03 20:06 ` A.M. <agentm@themactionfaction.com>
2007-04-04 12:46 ` Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Dimitri <dimitrik.fr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: A.M. @ 2007-04-03 20:06 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 17+ messages in thread
* Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Alan Hodgson <ahodgson@simkin.ca>
2007-04-03 20:06 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
@ 2007-04-04 12:46 ` Dimitri <dimitrik.fr@gmail.com>
2007-04-05 18:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 17+ messages in thread
From: Dimitri @ 2007-04-04 12:46 UTC (permalink / raw)
To: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Alan Hodgson <ahodgson@simkin.ca>
2007-04-03 20:06 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-04 12:46 ` Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Dimitri <dimitrik.fr@gmail.com>
@ 2007-04-05 18:05 ` Josh Berkus <josh@agliodbs.com>
2007-04-05 19:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Dimitri <dimitrik.fr@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Josh Berkus @ 2007-04-05 18:05 UTC (permalink / raw)
To: pgsql-performance
Dimitri,
> Probably another helpful solution may be to implement:
>
> ALTER TABLE LOGGING OFF/ON;
>
> just to disable/enable WAL?
Actually, a patch similar to this is currently in the queue for 8.3.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Alan Hodgson <ahodgson@simkin.ca>
2007-04-03 20:06 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" A.M. <agentm@themactionfaction.com>
2007-04-04 12:46 ` Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Dimitri <dimitrik.fr@gmail.com>
2007-04-05 18:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
@ 2007-04-05 19:00 ` Dimitri <dimitrik.fr@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Dimitri @ 2007-04-05 19:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Wow, it's excellent! :))
probably the next step is:
ALTER TABLE CACHE ON/OFF;
just to force keeping any table in the cache. What do you think?...
Rgds,
-Dimitri
On 4/5/07, Josh Berkus <josh@agliodbs.com> wrote:
> Dimitri,
>
> > Probably another helpful solution may be to implement:
> >
> > ALTER TABLE LOGGING OFF/ON;
> >
> > just to disable/enable WAL?
>
> Actually, a patch similar to this is currently in the queue for 8.3.
>
> --
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
@ 2007-04-04 08:50 ` Arnau <arnaulist@andromeiberica.com>
2007-04-04 11:08 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
1 sibling, 1 reply; 17+ messages in thread
From: Arnau @ 2007-04-04 08:50 UTC (permalink / raw)
To: pgsql-performance
Hi Josh,
Josh Berkus wrote:
> Arnau,
>
>> Is there anything similar in PostgreSQL? The idea behind this is how I
>> can do in PostgreSQL to have tables where I can query on them very often
>> something like every few seconds and get results very fast without
>> overloading the postmaster.
>
> If you're only querying the tables every few seconds, then you don't
> really need to worry about performance.
Well, the idea behind this is to have events tables, and a monitoring
system polls that table every few seconds. I'd like to have a kind of
FIFO stack. From "the events producer" point of view he'll be pushing
rows into that table, when it's filled the oldest one will be removed to
leave room to the newest one. From "the consumer" point of view he'll
read all the contents of that table.
So I'll not only querying the tables, I'll need to also modify that tables.
--
Arnau
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-04 08:50 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
@ 2007-04-04 11:08 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2007-04-04 11:51 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 17+ messages in thread
From: Ansgar -59cobalt- Wiechers @ 2007-04-04 11:08 UTC (permalink / raw)
To: pgsql-performance
On 2007-04-04 Arnau wrote:
> Josh Berkus wrote:
>>> Is there anything similar in PostgreSQL? The idea behind this is how
>>> I can do in PostgreSQL to have tables where I can query on them very
>>> often something like every few seconds and get results very fast
>>> without overloading the postmaster.
>>
>> If you're only querying the tables every few seconds, then you don't
>> really need to worry about performance.
>
> Well, the idea behind this is to have events tables, and a monitoring
> system polls that table every few seconds. I'd like to have a kind of
> FIFO stack. From "the events producer" point of view he'll be pushing
> rows into that table, when it's filled the oldest one will be removed
> to leave room to the newest one. From "the consumer" point of view
> he'll read all the contents of that table.
>
> So I'll not only querying the tables, I'll need to also modify that
> tables.
Ummm... this may be a dumb question, but why are you trying to implement
something like a FIFO with an RDBMS in the first place? Wouldn't it be
much easier to implement something like that as a separate program or
script?
Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-04 08:50 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-04 11:08 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
@ 2007-04-04 11:51 ` Arnau <arnaulist@andromeiberica.com>
2007-04-12 17:54 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 17+ messages in thread
From: Arnau @ 2007-04-04 11:51 UTC (permalink / raw)
To: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>; +Cc: pgsql-performance
Hi Ansgar ,
> On 2007-04-04 Arnau wrote:
>> Josh Berkus wrote:
>>>> Is there anything similar in PostgreSQL? The idea behind this is how
>>>> I can do in PostgreSQL to have tables where I can query on them very
>>>> often something like every few seconds and get results very fast
>>>> without overloading the postmaster.
>>> If you're only querying the tables every few seconds, then you don't
>>> really need to worry about performance.
>> Well, the idea behind this is to have events tables, and a monitoring
>> system polls that table every few seconds. I'd like to have a kind of
>> FIFO stack. From "the events producer" point of view he'll be pushing
>> rows into that table, when it's filled the oldest one will be removed
>> to leave room to the newest one. From "the consumer" point of view
>> he'll read all the contents of that table.
>>
>> So I'll not only querying the tables, I'll need to also modify that
>> tables.
>
> Ummm... this may be a dumb question, but why are you trying to implement
> something like a FIFO with an RDBMS in the first place? Wouldn't it be
> much easier to implement something like that as a separate program or
> script?
Well, the idea is have a table with a maximum number of rows. As the
number of queries over this table will be very high, I'd like to keep it
as small as possible and without indexes and so on that could make the
update slower.
Maybe it's the moment to change my question, is there any trick to get a
table that can be modified/queried very fast and with the minimum of
overhead? This table will have several queries every second and I'd like
to do this as fast as possible
Thanks
--
Arnau
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Josh Berkus <josh@agliodbs.com>
2007-04-04 08:50 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-04 11:08 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2007-04-04 11:51 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
@ 2007-04-12 17:54 ` Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 17+ messages in thread
From: Robert Treat @ 2007-04-12 17:54 UTC (permalink / raw)
To: pgsql-performance; arnaulist@andromeiberica.com; +Cc: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
On Wednesday 04 April 2007 07:51, Arnau wrote:
> Hi Ansgar ,
>
> > On 2007-04-04 Arnau wrote:
> >> Josh Berkus wrote:
> >>>> Is there anything similar in PostgreSQL? The idea behind this is how
> >>>> I can do in PostgreSQL to have tables where I can query on them very
> >>>> often something like every few seconds and get results very fast
> >>>> without overloading the postmaster.
> >>>
> >>> If you're only querying the tables every few seconds, then you don't
> >>> really need to worry about performance.
> >>
> >> Well, the idea behind this is to have events tables, and a monitoring
> >> system polls that table every few seconds. I'd like to have a kind of
> >> FIFO stack. From "the events producer" point of view he'll be pushing
> >> rows into that table, when it's filled the oldest one will be removed
> >> to leave room to the newest one. From "the consumer" point of view
> >> he'll read all the contents of that table.
> >>
> >> So I'll not only querying the tables, I'll need to also modify that
> >> tables.
> >
> > Ummm... this may be a dumb question, but why are you trying to implement
> > something like a FIFO with an RDBMS in the first place? Wouldn't it be
> > much easier to implement something like that as a separate program or
> > script?
>
> Well, the idea is have a table with a maximum number of rows. As the
> number of queries over this table will be very high, I'd like to keep it
> as small as possible and without indexes and so on that could make the
> update slower.
>
> Maybe it's the moment to change my question, is there any trick to get a
> table that can be modified/queried very fast and with the minimum of
> overhead? This table will have several queries every second and I'd like
> to do this as fast as possible
>
If you're wedded to the FIFO idea, I'd suggest reading this:
http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-...
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
@ 2007-04-03 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2007-04-03 19:08 UTC (permalink / raw)
To: arnaulist@andromeiberica.com; +Cc: pgsql-performance
Arnau <arnaulist@andromeiberica.com> writes:
> MySQL manual says:
> "The MEMORY storage engine creates tables with contents that are stored
> in memory. As indicated by the name, MEMORY tables are stored in memory.
> Is there anything similar in PostgreSQL?
As long as you have shared_buffers large enough (or temp_buffers if
you're dealing with temp tables), everything will stay in memory anyway.
Don't sweat it.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
@ 2007-04-04 12:35 Arnau <arnaulist@andromeiberica.com>
2007-04-18 20:04 ` Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 1 reply; 17+ messages in thread
From: Arnau @ 2007-04-04 12:35 UTC (permalink / raw)
To: Thor-Michael Støre <thor-michael.store@webvelocity.no>; +Cc: pgsql-performance
Hi Thor,
Thor-Michael Støre wrote:
> On 2007-04-04 Arnau wrote:
>> Josh Berkus wrote:
>>> Arnau,
>>>
>>>> Is there anything similar in PostgreSQL? The idea behind this
>>>> is how I can do in PostgreSQL to have tables where I can query
>>>> on them very often something like every few seconds and get
>>>> results very fast without overloading the postmaster.
>>> If you're only querying the tables every few seconds, then you
>>> don't really need to worry about performance.
>
>> Well, the idea behind this is to have events tables, and a
>> monitoring system polls that table every few seconds. I'd like to
>> have a kind of FIFO stack. From "the events producer" point of view
>> he'll be pushing rows into that table, when it's filled the oldest
>> one will be removed to leave room to the newest one. From "the
>> consumer" point of view he'll read all the contents of that table.
>
>> So I'll not only querying the tables, I'll need to also modify that
>> tables.
>
> Please try to refrain from doing this. This is the "Database as an
> IPC" antipattern (Antipatterns are "commonly-reinvented bad solutions
> to problems", I.E. you can be sure someone has tried this very thing
> before and found it to be a bad solution)
>
> http://en.wikipedia.org/wiki/Database_as_an_IPC
>
> Best solution is (like Ansgar hinted at) to use a real IPC system.
>
> Ofcourse, I've done it myself (not on PostgreSQL though) when working
> at a large corporation where corporate politics prevented me from
> introducing any new interdependency between systems (like having two
> start talking with eachother when they previously didn't), the only
> "common ground" for systems that needed to communicate was a
> database, and one of the systems was only able to run simple SQL
> statements and not stored procedures.
First of all, thanks for your interested but let me explain what I
need to do.
We have a web application where customers want to monitor how it's
performing, but not performing in terms of speed but how many customers
are now browsing in the application, how many have payed browsing
sessions, how many payments have been done, ... More or less is to have
a control panel. The difference is that they want that the information
displayed on a web browser must be "real-time" that is a query every
1-10 seconds.
Then, I haven't read yet the article but I'll do it, how you'd do
what I need to do?
Thanks
--
Arnau
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
2007-04-04 12:35 Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
@ 2007-04-18 20:04 ` Hannes Dorbath <light@theendofthetunnel.de>
0 siblings, 0 replies; 17+ messages in thread
From: Hannes Dorbath @ 2007-04-18 20:04 UTC (permalink / raw)
To: Arnau <arnaulist@andromeiberica.com>
Arnau wrote:
> Hi Thor,
>
> Thor-Michael Støre wrote:
>> On 2007-04-04 Arnau wrote:
>>> Josh Berkus wrote:
>>>> Arnau,
>>>>
>>>>> Is there anything similar in PostgreSQL? The idea behind this
>>>>> is how I can do in PostgreSQL to have tables where I can query
>>>>> on them very often something like every few seconds and get
>>>>> results very fast without overloading the postmaster.
>>>> If you're only querying the tables every few seconds, then you
>>>> don't really need to worry about performance.
>>
>>> Well, the idea behind this is to have events tables, and a
>>> monitoring system polls that table every few seconds. I'd like to
>>> have a kind of FIFO stack. From "the events producer" point of view
>>> he'll be pushing rows into that table, when it's filled the oldest
>>> one will be removed to leave room to the newest one. From "the
>>> consumer" point of view he'll read all the contents of that table.
>>
>>> So I'll not only querying the tables, I'll need to also modify that
>>> tables.
>>
>> Please try to refrain from doing this. This is the "Database as an
>> IPC" antipattern (Antipatterns are "commonly-reinvented bad solutions
>> to problems", I.E. you can be sure someone has tried this very thing
>> before and found it to be a bad solution)
>>
>> http://en.wikipedia.org/wiki/Database_as_an_IPC
>>
>> Best solution is (like Ansgar hinted at) to use a real IPC system.
>>
>> Ofcourse, I've done it myself (not on PostgreSQL though) when working
>> at a large corporation where corporate politics prevented me from
>> introducing any new interdependency between systems (like having two
>> start talking with eachother when they previously didn't), the only
>> "common ground" for systems that needed to communicate was a
>> database, and one of the systems was only able to run simple SQL
>> statements and not stored procedures.
>
>
> First of all, thanks for your interested but let me explain what I
> need to do.
>
> We have a web application where customers want to monitor how it's
> performing, but not performing in terms of speed but how many customers
> are now browsing in the application, how many have payed browsing
> sessions, how many payments have been done, ... More or less is to have
> a control panel. The difference is that they want that the information
> displayed on a web browser must be "real-time" that is a query every
> 1-10 seconds.
Though that has been suggested earlier, but why not use pgmemcache and
push each event as a new key? As memcached is FIFO by design that is
exacly what you ask for. Besides that memcached is so fast that your OS
is more busy with handling all that TCP connections than running memcached.
And in case you'd like to display statistical data and not tailing
events, let PG push that to memcached keys as well. See memcached as a
materialized view in that case.
As middleware I'd recommend lighttpd with mod_magnet.
You should be able to delivery that admin page way more than 5000 times
/ sec with some outdated desktop hardware. If that's not enough read up
on things like
http://blog.lighttpd.net/articles/2006/11/27/comet-meets-mod_mailbox
--
Best regards,
Hannes Dorbath
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2007-04-18 20:04 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 18:59 Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-03 19:05 ` Josh Berkus <josh@agliodbs.com>
2007-04-03 19:16 ` A.M. <agentm@themactionfaction.com>
2007-04-03 19:39 ` "C. Bergström" <cbergstrom@netsyncro.com>
2007-04-03 19:47 ` A.M. <agentm@themactionfaction.com>
2007-04-03 20:00 ` Alan Hodgson <ahodgson@simkin.ca>
2007-04-03 20:06 ` A.M. <agentm@themactionfaction.com>
2007-04-04 12:46 ` Dimitri <dimitrik.fr@gmail.com>
2007-04-05 18:05 ` Josh Berkus <josh@agliodbs.com>
2007-04-05 19:00 ` Dimitri <dimitrik.fr@gmail.com>
2007-04-04 08:50 ` Arnau <arnaulist@andromeiberica.com>
2007-04-04 11:08 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2007-04-04 11:51 ` Arnau <arnaulist@andromeiberica.com>
2007-04-12 17:54 ` Robert Treat <xzilla@users.sourceforge.net>
2007-04-03 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-04 12:35 Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000" Arnau <arnaulist@andromeiberica.com>
2007-04-18 20:04 ` Hannes Dorbath <light@theendofthetunnel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox