Re: High CPU load on Postgres Server during Peak times!!!!

Dear all
with reference to the discussions and valuable suggestions i got from the
list, the code has been reviewed and updated with explicit commit . There is
a good improvement in performance .I am also planning to upgrade the
database from 8.1 to 8.3 /8.4 .
My current OS is SLES 10 SP3 default ships with postgresql 8.1 .
The latest version of SLES 11 ships with postgresql 8.3 version.
I will be upgrading the Postgersql on my SLES 10 SP3 for PPC only.
I have not seen any prebuilt RPMS of Postgresql 8.3 or 8.4 version for SLES
10 PPC architecture .
When I tried to build the PPC RPM from Source in the PowerPC, it shows lot
of dependancies.
I have decided to install from source - Postgres 8.3 / Postgresql-8.4.
tar.gz .

Is there any major changes/updates in my 8.1 database i have to take care
while upgrading to postgresql 8.3/ 8.4 ? Is 8.3 or 8.4 the right version
to upgrade from 8.1 ?

Please let me know your suggestions.

Regards

Shiva Raman .

On Fri, Sep 25, 2009 at 8:52 PM, Craig James <craig_james(at)emolecules(dot)com>wrote:

> Gerhard Wiesinger wrote:
>
>> Hello Craig,
>>
>> Are you sure this is correct?
>>
>> The test program (see below) with autocommit=0 counts up when an insert is
>> done in another session and there is no commit done.
>>
>> I think with each new select a new implicit transaction is done when no
>> explicit "BEGIN" has been established.
>>
>
> Sorry, I should have been more specific. A transaction starts when you do
> something that will alter data in the database, such as insert, update,
> alter table, create sequence, and so forth. The Perl DBI won't start a
> transaction for a select.
>
> But my basic point is still valid: Some languages like Perl can implicitely
> start a transaction, so if programmers aren't familiar with this behavior,
> they can accidentally create long-running transactions.
>
> Craig
>
>
>
> Can one confirm this behavior?
>>
>> Thnx.
>>
>> Ciao,
>> Gerhard
>>
>> # Disable autocommit!
>> my $dbh = DBI->connect($con, $dbuser, $dbpass, {RaiseError => 1,
>> AutoCommit=>0}) || die "Unable to access Database '$dbname' on host
>> '$dbhost' as user '$dbuser'. Error returned was: ". $DBI::errstr ."";
>>
>> my $sth = $dbh->prepare('SELECT COUNT(*) FROM employee;');
>>
>> for (;;)
>> {
>> $sth->execute();
>> my ($count) = $sth->fetchrow();
>> print "count=$count\n";
>> $sth->finish();
>> # $dbh->commit;
>> sleep(3);
>> }
>>
>> $dbh->disconnect;
>>
>> --
>> http://www.wiesinger.com/
>>
>>
>> On Thu, 24 Sep 2009, Craig James wrote:
>>
>> Dave Dutcher wrote:
>>>
>>>> You need a COMMIT for every BEGIN. If you just run a SELECT statement
>>>> without first beginning a transaction, then you should not end up with a
>>>> connection that is Idle in Transaction. If you are beginning a
>>>> transaction,
>>>> doing a select, and then not committing, then yes that is a bug.
>>>>
>>>
>>> The BEGIN can be hidden, though. For example, if the application is
>>> written in Perl,
>>>
>>> $dbh = DBI->connect($dsn, $user, $pass, {AutoCommit => 0});
>>>
>>> will automatically start a transaction the first time you do anything.
>>> Under the covers, the Perl DBI issues the BEGIN for you, and you have to do
>>> an explicit
>>>
>>> $dbh->commit();
>>>
>>> to commit it.
>>>
>>> Craig
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (
>>> pgsql-performance(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>>
>>
>