Re: [HACKERS] Transaction Speed and real time database
[please use reply to all, otherwise you'll have what you just had: the guy who you write goes home for the weekend and all the rest of the people on the list who would answer you won't know there is soemthing to answer...] On Fri, 2006-07-21 at 13:39, moises wrote: Sorry if I can't explain me clear. I want to use an extension of postgres, (because I'm very clear that postgres is not a real time database.) I want to modify some modules of postgres for this convenience, for example, add some time constrains to transactions, I know that is a very hard work, Our Applications are 100% real time works, controlling industrial process, and not OLAP or OLTP applications. My question is how many fast are postgres in transaction actually? Because maybe we don't need to work, in looking for more speed, just constrains and planners. I have to admit RT is for me just a vague idea I still remember from some courses, but I'm not sure if RT and transactional is compatible at all... do you really need a transactional data base for RT applications ? Cause postgres is just that, an excellent transactional DB, which BTW is fast enough for transactional work (where you expect that sometimes operations fail due to others working in parallel). I'm not sure in industrial RT applications you could afford failures due to concurrency... and in that case you would be better off using something non-transactional - but this is just my limited understanding of the problem. Others on this list might know better... Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Transaction Speed and real time database
Hi, On Monday 24 July 2006 10:33, Csaba Nagy wrote: [please use reply to all, otherwise you'll have what you just had: the guy who you write goes home for the weekend and all the rest of the people on the list who would answer you won't know there is soemthing to answer...] On Fri, 2006-07-21 at 13:39, moises wrote: Sorry if I can't explain me clear. I want to use an extension of postgres, (because I'm very clear that postgres is not a real time database.) I want to modify some modules of postgres for this convenience, for example, add some time constrains to transactions, I know that is a very hard work, Our Applications are 100% real time works, controlling industrial process, and not OLAP or OLTP applications. My question is how many fast are postgres in transaction actually? Because maybe we don't need to work, in looking for more speed, just constrains and planners. I have to admit RT is for me just a vague idea I still remember from some courses, but I'm not sure if RT and transactional is compatible at all... do you really need a transactional data base for RT applications ? Cause postgres is just that, an excellent transactional DB, which BTW is fast enough for transactional work (where you expect that sometimes operations fail due to others working in parallel). I'm not sure in industrial RT applications you could afford failures due to concurrency... and in that case you would be better off using something non-transactional - but this is just my limited understanding of the problem. Others on this list might know better... Cheers, Csaba. As someone 'from the industry' I have to chime in here. IMHO there is no way to make PostgreSQL itself RT capable - not for a long time. The following items (at least) are roadblocks: - index tree growth (undeterministic in time and duration) - need to vacuum (for FIFO applications) - database file growth (also undeterministic, but can probably be worked around by initializing the database with the max. no. of records, then deleting and vacuuming without -f. But this requires FSM to be large enough, and still there is the need for frequent vacuum). OTOH, one has to be very careful to not mix terms here. In industrial (production floor) applications, the term 'real time database' refers to soemthing completely different than a relational, transactional DB. A 'real time database' in this scenario is something which keeps aggregates of data points and events over time, and this is usually the only type of data retrieval they allow for: 'give me channels x,y,z from start to end time'. The prime property of such an RTDB is constant-time inserts, and priorization of access paths. Normally they work in embedded controllers or on real-time OSes. There are RTDBs which can also be seen as relational databases, but the design of these is usually the RT part with a relational 'addon'. Because of the features of a full-fledged relational database engine, engineers often wish they had one of those instead ;-). Usually, we solve this with some sort of streaming 'frontend', which buffers the data flow (usually to disk) until it's inserted into the database. This lowers the real-time requirement to 'has to be fast enough on average'. We have several of these types of applications in production at various customers, some for 6+ years continuously (using PostgreSQL 7.0!). Hope this clears up the issues somewhat for 'normal' database people. Greetings, Jörg -- Leiter Softwareentwicklung - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Transaction Speed and real time database
[snip] OTOH, one has to be very careful to not mix terms here. In industrial (production floor) applications, the term 'real time database' refers to soemthing completely different than a relational, transactional DB. But relational and transactional are orthogonal, they don't imply/require each other... most of the roadblocks you mentioned (including vacuum) is part of postgres transactional design and a non-transactional DB won't have that overhead. Your input enforces my thinking that the transactionality of the DB is the real roadblock... which means postgres will never really be an RT application in the proper sense of the word. Because of the features of a full-fledged relational database engine, engineers often wish they had one of those instead ;-). Usually, we solve this with some sort of streaming 'frontend', which buffers the data flow (usually to disk) until it's inserted into the database. This lowers the real-time requirement to 'has to be fast enough on average'. We have several of these types of applications in production at various customers, some for 6+ years continuously (using PostgreSQL 7.0!). This sounds the most reasonable approach :-) Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Transaction Speed and real time database
Hi, On Monday 24 July 2006 11:26, Csaba Nagy wrote: [snip] OTOH, one has to be very careful to not mix terms here. In industrial (production floor) applications, the term 'real time database' refers to soemthing completely different than a relational, transactional DB. But relational and transactional are orthogonal, they don't imply/require each other... most of the roadblocks you mentioned (including vacuum) is part of postgres transactional design and a non-transactional DB won't have that overhead. Your input enforces my thinking that the transactionality of the DB is the real roadblock... which means postgres will never really be an RT application in the proper sense of the word. [...] Yes, the terms are orthogonal. But most relational databases I know of are also transactional - because it just makes sense. The roadblocks I metioned were specific to PG. The storage manager is as it is, no way around it. So you need vacuum, you can have index growth, and you will have table space growth ;-) Greetings, Jörg -- Leiter Softwareentwicklung - S.E.A GmbH Mail: [EMAIL PROTECTED] WWW: http://www.sea-gmbh.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Transaction Speed and real time database
Thanks for your answer, I have experience with postgres and I know perfectly that not a TOY. But some concepts of real time system dont based only in speed. Real time databases needs some other kinds of semantics and features that postgres dont have. Postgres dont supports real time constrains semantics in transactions. In other hands the concurrent transactions dont wok well based on priorities of task. The program scheduler of transaction based on real time mechanism like system how RTAI, or RTlinux, and much more. POstrgres need to be extending for real time databases. I want to know, in a hypothetical server, how many transaction postgres support for a first approximation. I found this data of MySQL and DB4o data bases but I can´t find any of Postgres. A mite that postgres is slow, but I need some numbers, insertion speed, and update speed is the more important part? Thanks M. De: Adnan DURSUN [mailto:[EMAIL PROTECTED] Enviado el: jueves, 20 de julio de 2006 23:05 Para: moises; pgsql-hackers@postgresql.org Asunto: Re: [HACKERS] Transaction Speed This depends on your server capability and performance.. You can use PostgreSQL as real time database. It is real not a toy :-) Adnan DURSUN ASRIN Bilisim Ltd. Turkey - Original Message - From: moises To: pgsql-hackers@postgresql.org Sent: Thursday, July 20, 2006 3:36 PM Subject: [HACKERS] Transaction Speed Can any body talk me how many transactions make postgres in a second? For example Inserts, Update, delete, etc. Im very interesting in this data, because I want to use postgres for a real time database for process control. Thanks and regards M.Sc.Moisés Herrera Vázquez Especialista Superior en Automática Empresa de Automatización Integral, CEDAI Sucursal Villa Clara. Teléfono: (53) 42 -203311 ___
Re: [HACKERS] Transaction Speed and real time database
Real time databases needs some other kinds of semantics and features that postgres don't have. Postgres don't supports real time constrains semantics in transactions. In other hands the concurrent transactions don't wok well based on priorities of task. The program scheduler of transaction based on real time mechanism like system how RTAI, or RTlinux, and much more. POstrgres need to be extending for real time databases. PostgreSQL ist no program for real time applications. Period. Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Transaction Speed and real time database
On Fri, Jul 21, 2006 at 09:38:41AM +0200, moises wrote: I want to know, in a hypothetical server, how many transaction postgres support for a first approximation. I found this data of MySQL and DB4o data bases but I can´t find any of Postgres. I think you're asking the wrong question. I think what you want is what performence does postgresql get on benchmark X with hardware Y. I don't beleive you actually get performence numbers for other databases without a whole list of constraining factors. You need to be a lot more specific about what you want to know. You can find benchmarks on the web, but really you need to evaluate whether they're testing what you want to know. But ok, here's a number: I can insert 100,000 rows per second on this machine next to me. How does that help you? It doesn't: you don't know the machine or the software or how it's configured or what I'm inserting. You don't even know if this machine exists (hint: it doesn't). Also, insert/update speeds are useless measurements. If all you want is that, please look at berkley DB. PostgreSQL is a SQL compliant database. Anyway, here are some sites to help you formulate your question better: http://www.geocities.com/mailsoftware42/db/ http://www.devx.com/dbzone/Article/29480?trk=DXRSS_DB Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Transaction Speed and real time database
Hello, Thanks for your answer. Maybe I asking a wrong question, but I just need some ideas like you say that posgres can insert 100,000 rows per second, is a good approximation... In other hand if you visit some links like http://developer.db4o.com/forums/post/25599.aspx You will find something like this in Spanish You can store 250 millions objects with just constant 40 MB RAM and constant insertions about 8.000 objects per second -- these measures had been take in a show HDD 5600 rpm. It had been stored 100.000 objects with less than constant 2MB RAM. Db4o can store in 10 millions of objects in 400 milliseconds. How can you see, this numbers are just I want but for postgres. I know that hardware is very important, join to OS and config of Shared Memory, System V, etc, but I just looking for an Idea. Suppose that every body say me that POStgres is to slow for real time databases, then I will be very full trying to resolve this problems with postgres, don't think that? Thanks again. Moises -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Martijn van Oosterhout Enviado el: viernes, 21 de julio de 2006 16:19 Para: moises CC: 'Adnan DURSUN'; pgsql-hackers@postgresql.org Asunto: Re: [HACKERS] Transaction Speed and real time database On Fri, Jul 21, 2006 at 09:38:41AM +0200, moises wrote: I want to know, in a hypothetical server, how many transaction postgres support for a first approximation. I found this data of MySQL and DB4o data bases but I can´t find any of Postgres. I think you're asking the wrong question. I think what you want is what performence does postgresql get on benchmark X with hardware Y. I don't beleive you actually get performence numbers for other databases without a whole list of constraining factors. You need to be a lot more specific about what you want to know. You can find benchmarks on the web, but really you need to evaluate whether they're testing what you want to know. But ok, here's a number: I can insert 100,000 rows per second on this machine next to me. How does that help you? It doesn't: you don't know the machine or the software or how it's configured or what I'm inserting. You don't even know if this machine exists (hint: it doesn't). Also, insert/update speeds are useless measurements. If all you want is that, please look at berkley DB. PostgreSQL is a SQL compliant database. Anyway, here are some sites to help you formulate your question better: http://www.geocities.com/mailsoftware42/db/ http://www.devx.com/dbzone/Article/29480?trk=DXRSS_DB Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Transaction Speed and real time database
[snip] Suppose that every body say me that POStgres is to slow for real time databases, then I will be very full trying to resolve this problems with postgres, don't think that? I think you didn't understand correctly: postgres is not slow, it is just not suitable for real RT applications because of a few reasons, which in fact make other data bases also not suitable for this purpose. The main concern is that a RT application usually needs predictable response times, possibly with guaranties for upper bounds of response times... and most data bases which are transactional and offer concurrent access won't give you such guaranties, due to locking issues. The question is, your application is really RT in the proper sense of the word, or it is just an OLTP application which needs to be fast but won't cause a nuclear explosion if one response in 100 will be slower than expected... in that case postgres might be good for you. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Transaction Speed and real time database
What you are asking is essentially the equivalent of asking How long is a piece of string? The question is meaningless and so will be any answer. The fact that there are web sites which are happy to supply you with meaningless information doesn't mean we should follow suit. And frankly, I would be very dubious about using PostgreSQL or just about any other RDBMS in a Real Time System, hard or soft. cheers andrew moises wrote: Hello, Thanks for your answer. Maybe I asking a wrong question, but I just need some ideas like you say that posgres can insert 100,000 rows per second, is a good approximation... In other hand if you visit some links like http://developer.db4o.com/forums/post/25599.aspx You will find something like this in Spanish You can store 250 millions objects with just constant 40 MB RAM and constant insertions about 8.000 objects per second -- these measures had been take in a show HDD 5600 rpm. It had been stored 100.000 objects with less than constant 2MB RAM. Db4o can store in 10 millions of objects in 400 milliseconds. How can you see, this numbers are just I want but for postgres. I know that hardware is very important, join to OS and config of Shared Memory, System V, etc, but I just looking for an Idea. Suppose that every body say me that POStgres is to slow for real time databases, then I will be very full trying to resolve this problems with postgres, don't think that? Thanks again. Moises -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] En nombre de Martijn van Oosterhout Enviado el: viernes, 21 de julio de 2006 16:19 Para: moises CC: 'Adnan DURSUN'; pgsql-hackers@postgresql.org Asunto: Re: [HACKERS] Transaction Speed and real time database On Fri, Jul 21, 2006 at 09:38:41AM +0200, moises wrote: I want to know, in a hypothetical server, how many transaction postgres support for a first approximation. I found this data of MySQL and DB4o data bases but I can´t find any of Postgres. I think you're asking the wrong question. I think what you want is what performence does postgresql get on benchmark X with hardware Y. I don't beleive you actually get performence numbers for other databases without a whole list of constraining factors. You need to be a lot more specific about what you want to know. You can find benchmarks on the web, but really you need to evaluate whether they're testing what you want to know. But ok, here's a number: I can insert 100,000 rows per second on this machine next to me. How does that help you? It doesn't: you don't know the machine or the software or how it's configured or what I'm inserting. You don't even know if this machine exists (hint: it doesn't). Also, insert/update speeds are useless measurements. If all you want is that, please look at berkley DB. PostgreSQL is a SQL compliant database. Anyway, here are some sites to help you formulate your question better: http://www.geocities.com/mailsoftware42/db/ http://www.devx.com/dbzone/Article/29480?trk=DXRSS_DB Hope this helps, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Transaction Speed and real time database
Ühel kenal päeval, R, 2006-07-21 kell 13:29, kirjutas Andrew Dunstan: What you are asking is essentially the equivalent of asking How long is a piece of string? The question is meaningless and so will be any answer. The fact that there are web sites which are happy to supply you with meaningless information doesn't mean we should follow suit. And frankly, I would be very dubious about using PostgreSQL or just about any other RDBMS in a Real Time System, hard or soft. It would probably be possible to get constant-time inserts into an indexless table, once checkpoints are spread out over the whole checpoint interval, as described by the NTT staff at the conference, but this is probably the only RT compatible scenario you can expect from an RDBMS. Another way to get RT is just use long required completion times and light enough load on db that you always meet your time limit. Remember, RT does not neccesarily mean Fast it just needs to be Predictable! -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(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
Re: [HACKERS] Transaction Speed and real time database
Hannu Krosing wrote: Ühel kenal päeval, R, 2006-07-21 kell 13:29, kirjutas Andrew Dunstan: What you are asking is essentially the equivalent of asking How long is a piece of string? The question is meaningless and so will be any answer. The fact that there are web sites which are happy to supply you with meaningless information doesn't mean we should follow suit. And frankly, I would be very dubious about using PostgreSQL or just about any other RDBMS in a Real Time System, hard or soft. It would probably be possible to get constant-time inserts into an indexless table, once checkpoints are spread out over the whole checpoint interval, as described by the NTT staff at the conference, but this is probably the only RT compatible scenario you can expect from an RDBMS. Another way to get RT is just use long required completion times and light enough load on db that you always meet your time limit. Remember, RT does not neccesarily mean Fast it just needs to be Predictable! Increasing required time constraints only lowers the likelihood that you will fail to meet RT requirements, rather than eliminating it. I suspect that for genuine RT performance we'd need to build in some sort of preemption mechanism (although I freely confess this isn't my area of expertise). cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
