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.

I'm 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

_______________________________________

moises wrote:

Can any body talk me how many transactions make postgres in a second?

It depends on many things
1) speed of hardware/OS/number of disks/type of disks, if you use RAID
or not ...
2) number concurrent access
3) size of processed data in one transaction
4) database model
...

It not possible determine. You must take some machine and perform some
own test.

I�m very interesting in this data, because I want to use postgres for a
real time database for process control.

I'm not sure if postgres is good database for real time application. You
need know maximal response time for realtime application and I afraid
that postgres is not good choice for RTA. (MySQL, Firebird, Oracle ...
have same limitation ...)

Zdenek

Thanks for your answer,

I have experience with postgres and I know perfectly that not a TOY.

But some concepts of real time system don’t based only in speed.

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.

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:a_dursun@hotmail.com]
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 <mailto:moises@cedaivc.co.cu>

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.

I’m 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

_______________________________________

Hi,

On Thu, Jul 20, 2006 at 02:36:53PM +0200, moises wrote:

For example Inserts, Update, delete, etc.

If you need a storage for structured data, database servers are good to
go. If you need a very fast "flow" of not-so-needed and
okay-to-miss-we-can-regenerate type of data storage you can go with
in-memory storage such as memcache[1]http://www.danga.com/memcached/ or PostgreSQL's temporary
table[2]http://www.postgresql.org/docs/8.1/static/sql-createtable.html -- Enver feature. I think the latter would slightly perform better.

Enjoy,

[1]: http://www.danga.com/memcached/
[2]: http://www.postgresql.org/docs/8.1/static/sql-createtable.html -- Enver
--
Enver

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: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] 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.

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

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/

[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.

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:

Ü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

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

[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.

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: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com

[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.

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: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com