Обсуждение: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
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
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
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
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
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.
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
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.
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
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
Re: Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
От
Ansgar -59cobalt- Wiechers
Дата:
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
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
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 >
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
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 >
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-version.html -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL