Skip site navigation (1) Skip section navigation (2)

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

From: Craig James <craig_james(at)emolecules(dot)com>
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
Cc: Dave Dutcher <dave(at)tridecap(dot)com>, 'Shiva Raman' <raman(dot)shivag(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: High CPU load on Postgres Server during Peak times!!!!
Date: 2009-09-25 15:22:16
Message-ID: 4ABCE028.606@emolecules.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>>
> 


In response to

Responses

pgsql-performance by date

Next:From: Scott CareyDate: 2009-09-25 15:53:00
Subject: Re: PG 8.3 and large shared buffer settings
Previous:From: Aidan Van DykDate: 2009-09-25 13:33:09
Subject: Re: PG 8.3 and large shared buffer settings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group