Re: Is PostgreSQL for this?

Hi everybody,
I'm looking for a database system for a SCADA system. The major
probles I think it's on performance because the application it's going
to poll about 4k variables per second from hardware and has to register
the values on the procces table.
I heard that PostgreSQL provides a bulk loading mechanism called COPY,
which takes tab-delimited or CSV input from a file. Where COPY can be
used instead of hundreds or thousands of INSERTS, it can cut execution
time.
I'm less than a novice so I'll thank any piece of advice.
Sorry for my poor english.
F.

am Wed, dem 27.12.2006, um 13:41:24 -0300 mailte lopezf(at)adinet(dot)com(dot)uy folgendes:
> Hi everybody,
> I'm looking for a database system for a SCADA system. The major

I don't know, what is a 'SCADA' System?

> I heard that PostgreSQL provides a bulk loading mechanism called COPY,
> which takes tab-delimited or CSV input from a file. Where COPY can be
> used instead of hundreds or thousands of INSERTS, it can cut execution
> time.

Right.

http://www.postgresql.org/docs/current/static/sql-copy.html

> I'm less than a novice so I'll thank any piece of advice.
> Sorry for my poor english.

No problem, i'm not a native english speaker, but i can read your mail
without problems.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

2006/12/27, lopezf(at)adinet(dot)com(dot)uy <lopezf(at)adinet(dot)com(dot)uy>:
> Hi everybody,
> I'm looking for a database system for a SCADA system. The major
> probles I think it's on performance because the application it's going
> to poll about 4k variables per second from hardware and has to register
> the values on the procces table.
> I heard that PostgreSQL provides a bulk loading mechanism called COPY,
> which takes tab-delimited or CSV input from a file. Where COPY can be
> used instead of hundreds or thousands of INSERTS, it can cut execution
> time.
> I'm less than a novice so I'll thank any piece of advice.

I believe you could easily simulate the load in a small
fake-SCADA-program and see how the hardware at your disposal handles
it with postgresql, a different RDBMS or simply a flat file. Make a
small program which will generate a set of 4k random values and send
them asynchronously over the network to your data acquisition
application which should store the data in the database. Measure how
fast you can send the data and still record everything.

If data acquisition speed is your primary concern (as it seems to be),
you might want to use a simple .csv file: you'll probably beat the
performance of any database management system. You could periodically
move the saved data from the .csv files into a database (say,
postgresql) where you could (I assume) analyze it. You might want to
use a separate machine for the database management system so as to
remove any unnecessary CPU and I/O disturbances from the primary data
storage machine.
I don't think your load (32 kBps if your variables are double
precision float values) is a challenge, but running any kind of
analysis on a basically real-time-response-constrained machine might
cost you data losses and I don't know if you can afford those.

Cheers,
t.n.a.

On Wed, 2006-12-27 at 18:13 +0000, Tomi N/A wrote:
> 2006/12/27, lopezf(at)adinet(dot)com(dot)uy <lopezf(at)adinet(dot)com(dot)uy>:
> > Hi everybody,
> > I'm looking for a database system for a SCADA system. The major
> > probles I think it's on performance because the application it's going
> > to poll about 4k variables per second from hardware and has to register
> > the values on the procces table.
> > I heard that PostgreSQL provides a bulk loading mechanism called COPY,
> > which takes tab-delimited or CSV input from a file. Where COPY can be
> > used instead of hundreds or thousands of INSERTS, it can cut execution
> > time.
> > I'm less than a novice so I'll thank any piece of advice.
>
> I believe you could easily simulate the load in a small
> fake-SCADA-program and see how the hardware at your disposal handles
> it with postgresql, a different RDBMS or simply a flat file. Make a
> small program which will generate a set of 4k random values and send
> them asynchronously over the network to your data acquisition
> application which should store the data in the database. Measure how
> fast you can send the data and still record everything.

If you are going to have a live feed you would probably benefit from the
multi insert capability of 8.2:

insert into foo values () () ()

Sincerely,

Joshua D. Drake

>
> If data acquisition speed is your primary concern (as it seems to be),
> you might want to use a simple .csv file: you'll probably beat the
> performance of any database management system. You could periodically
> move the saved data from the .csv files into a database (say,
> postgresql) where you could (I assume) analyze it. You might want to
> use a separate machine for the database management system so as to
> remove any unnecessary CPU and I/O disturbances from the primary data
> storage machine.
> I don't think your load (32 kBps if your variables are double
> precision float values) is a challenge, but running any kind of
> analysis on a basically real-time-response-constrained machine might
> cost you data losses and I don't know if you can afford those.
>
> Cheers,
> t.n.a.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate