[Fwd: [GENERAL] Performance]

Jason C. Leach wrote:

> I've been playing with pgsql for a few days now and am getting the hang
> of it.  I just did a loop that inserts a few thousand records into a
> table.  I did a statement, prepare, execute; it worked fine although pg
> seemed to access the hd for every insert.  Is there a way to cache

I am also rather new on postgreSQL, but I have worked with other databases
and transactional systems, so I could help make an educated guess.
Eg. in Oracle, nothing is committed by default. If you write something to
a database, then exit your pl shell, you won't find anything of your work
in your database.
Yesterday, I wrote a small Tcl/Tk application to enter names in a
database. As you say, every INSERT is committed.
Let me put it like this, if pg works like I think it should (since I am
only busy a couple of days), then uncommitted transactions wouldn't be
written to disk, but kept in memory. If you really had a large amount of
transactions, then your system would commit other things to swapspace.
What you would submit to the backend would be something like this :
    BEGIN
    ... Put all the work here
    COMMIT
More explanations can be found in the documentation, which should have
been installed if you compiled the sources.

>
> inserts and then write them all at once later.  I'm using Perl with
> DBD::Pg/DBI and  see with DBI there is a prepare_cached, and a commit.
> Not much in the way of docs for the modules though.

I presume that prepare and prepare_cached are used to compile SQL
statements beforehand. You will find something similar in ODBC and JDBC.
This would speed up execution of your SQL statements. It does not have
anything to do with the way data is accessed in your database however.

>
>
> Perhaps I should be doing statement, prepare, statement, prepare,
> commit?
>
> I'd also be curious where you start postgres with the -F option?  I did
> not see it as a postmaster option.

Yes, you can. 'postmaster' is only a symbolic link to 'postgres'. Such a
way of working is very dangerous though. You should only use it as a way
of loading large amounts of data quickly into tables, AFTER you have made
a serious backup of the existing database.

>
>
> This is just a curiosity, but some of the MS Certified people are being
> taught that MS SQL server requires something like a PIII with 512MB
> RAM.  That's pretty crazy, if you ask me, but I can see it as being
> dependent on how many requests you server will need to deal with. On
> equal machines, could pg handle more or less requests that MS SQL?

This is a question that I have been trying to solve since last year, but
for which very little information exists. In this particular case, let me
point out a few things.
PIII is a misleading term. Since the only difference between a PII and a
PIII is the expansion of the instruction set with extra multi-media
instructions, there won't be a difference between a PII and a PIII.
You use memory in a database server to speed up read-requests, buffering,
compilation and memory-bound processing. Ultimately, all requests must get
down to the IO channel, so this is at least an equal important factor.
When a user requests data, then things can speed up considerately.
However, once the user starts working on his data, inserting, updating and
deleting, the responsiveness of his applications will be more dependent
upon the load of the I/O channel. It's not possible either to put large
amounts of work in transactions, because you will need to lock records,
and someone else who wants to work on the same data will have to wait
until the other one's transaction are completed.

>
>
> Thanks,
>     Jason
>
> --
> .............
> ......... Jason C. Leach
> ...... University College of the Cariboo
> ... jcl@mail.ocis.net.
> .. http://www.ocis.net/~jcl
> .
>
> The Search for Extraterrestrial Intelligence from Home:
> http://setiathome.ssl.berkeley.edu
>
>                                                                 LINUX!
>
> ************

Jurgen Defurne
Flanders
Belgium