Обсуждение: Transaction Speed

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">Can any body talk me how many transactions make postgres in a second?</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial">For example Inserts, Update, delete, etc.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">I’m very interesting in this data, because I want to use postgres for a real time database
forprocess control.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">Thanks and regards</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-GB"style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">________________________________________</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">M.Sc.Moisés Herrera Vázquez</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Especialista Superior en Automática</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Empresa de Automatización Integral, CEDAI</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">Sucursal Villa Clara.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Teléfono: (53) 42 -203311</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">_______________________________________</span></font><p class="MsoNormal"><font face="Times New
Roman"size="3"><span style="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font></div>

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

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] or PostgreSQL's temporary
table[2] 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

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.

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.

> [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:
> 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,
>   

Ü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