Re: [PERFORM] High CPU load when 'free -m' shows low 'free' memory even though large 'cached' memory still available
On Thu, Jun 5, 2014 at 8:47 AM, Tom Lane wrote: > Vince Lasmarias writes: >> For the past few days, we've been seeing unexpected high CPU spikes in our >> system. > > Recent reports have suggested that disabling transparent huge page > management in your kernel can help with this. If the excess CPU > load is mostly "system" time not "user" time then this is probably > the culprit. OP double posted this (OP: please refrain from doing that). I'm not sure if THP is the issue here (although it is definitely a major bugaboo if not a disaster IMNSHO) -- see commentary on the 'other thread'. http://www.postgresql.org/message-id/1401928047200-5806122.p...@n5.nabble.com melrin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load when 'free -m' shows low 'free' memory even though large 'cached' memory still available
Vince Lasmarias writes: > For the past few days, we've been seeing unexpected high CPU spikes in our > system. Recent reports have suggested that disabling transparent huge page management in your kernel can help with this. If the excess CPU load is mostly "system" time not "user" time then this is probably the culprit. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] High CPU load when 'free -m' shows low 'free' memory even though large 'cached' memory still available
For the past few days, we've been seeing unexpected high CPU spikes in our system. We observed the following: - every single CPU spike was preceded by low 'free' memory even though 'cached' is quite high - as soon as we shut down any of our applications which is occupying some DB connections (e.g., pgpool), the 'free' memory usage goes up and CPU load immediately drops (please see below) - we saw instances when the ‘free’ memory did reach low values but CPU remained OK I understand how running out of memory could cause various issues with the DB, but in this case, we had plenty of memory in the ‘cached’ portion. Why would CPU load go up when there's still plenty of room in the 'cached' memory? Here's the session: 04:58:37 up 31 days, 23:41, 0 users, load average: 2.37, 1.91, 1.68 total used free sharedbuffers cached Mem: 31720 31188532 0 90 22852 (…) 05:00:37 up 31 days, 23:43, 1 user, load average: 5.51, 2.66, 1.95 total used free sharedbuffers cached Mem: 31720 31452268 0 77 22267 (…) 05:00:58 up 31 days, 23:44, 1 user, load average: 21.44, 6.52, 3.24 total used free sharedbuffers cached Mem: 31720 31482237 0 77 21704 (…) 05:01:18 up 31 days, 23:44, 1 user, load average: 42.98, 12.36, 5.22 total used free sharedbuffers cached Mem: 31720 31477243 0 77 21061 (…) 05:01:38 up 31 days, 23:44, 1 user, load average: 63.38, 18.99, 7.56 total used free sharedbuffers cached Mem: 31720 31454266 0 77 20410 (…) 05:03:20 up 31 days, 23:46, 1 user, load average: 110.10, 47.85, 19.07 total used free sharedbuffers cached Mem: 31720 31326394 0 76 19290 At this point, pgpool and apache were shut down: 05:03:40 up 31 days, 23:46, 1 user, load average: 113.51, 52.66, 21.26 total used free sharedbuffers cached Mem: 31720 29835 1885 0 76 19291 (…) 05:04:00 up 31 days, 23:47, 1 user, load average: 82.49, 49.53, 20.90 total used free sharedbuffers cached Mem: 31720 26082 5638 0 76 19300 (…) 05:04:20 up 31 days, 23:47, 1 user, load average: 60.37, 46.62, 20.56 total used free sharedbuffers cached Mem: 31720 24701 7019 0 76 19311 (…) 05:04:40 up 31 days, 23:47, 1 user, load average: 43.63, 43.70, 20.15 total used free sharedbuffers cached Mem: 31720 24797 6923 0 76 19320 (…) 05:05:00 up 31 days, 23:48, 1 user, load average: 31.70, 40.96, 19.75 total used free sharedbuffers cached Mem: 31720 24947 6773 0 76 19326 (…) 05:05:20 up 31 days, 23:48, 1 user, load average: 23.12, 38.41, 19.36 total used free sharedbuffers cached Mem: 31720 25036 6684 0 76 19334 (…) 05:05:40 up 31 days, 23:48, 1 user, load average: 17.12, 36.05, 18.99 total used free sharedbuffers cached Mem: 31720 25197 6523 0 76 19340 (…) 05:06:00 up 31 days, 23:49, 1 user, load average: 12.84, 33.84, 18.63 total used free sharedbuffers cached Mem: 31720 25316 6404 0 76 19367 (…) 05:06:20 up 31 days, 23:49, 1 user, load average: 9.85, 31.80, 18.28 total used free sharedbuffers cached Mem: 31720 24728 6992 0 76 18839 (…) 05:06:40 up 31 days, 23:49, 1 user, load average: 7.61, 29.86, 17.93 total used free sharedbuffers cached Mem: 31720 24835 6885 0 76 18847 (…) 05:07:00 up 31 days, 23:50, 1 user, load average: 5.74, 27.99, 17.57 total used free sharedbuffers cached Mem: 31720 24971 6749 0 76 18852 (…) 05:07:20 up 31 days, 23:50, 1 user, load average: 4.48, 26.26, 17.22 total used free sharedbuffers cached Mem: 31720 25133 6587 0 76 18861 (…) 05:07:40 up 31 days, 23:50, 2 users, load average: 3.83, 24.70, 16.90 total used free sharedbuffers cached Mem: 31720 25351 6
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
On Fri, Oct 9, 2009 at 3:11 AM, Shiva Raman wrote: > 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. The 'big picture' issues: *) Test your postgresql.conf first. Some settings have changed or have been removed (like fsm). *) Many implicit casts to text were removed. Essentially the server is less tolerant of sql that many would consider buggy *) autovacuum is now on by default and, most importantly: *) sit back and enjoy the speed :-) regarding 8.3/8.4, it's a tough call. 8.4 has a better chance of being supported by in place upgrade in the future, so i'd start there. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>>
>>
>
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
As suggested, i had changed the log_statement='ddl' and now it is logging only the ddl statements . thanks for the tip. Can i delete the old log files in pg_log after backing up as zip archive ? is it neccesary to keep those log files ? Regards Shiva Raman > > 2009/9/25 Grzegorz Jaśkiewicz > > On Fri, Sep 25, 2009 at 9:06 AM, Shiva Raman wrote: > >> Hi Gerhard >> I also found the pg_log has 73 G of data . >> >> clusternode2:/var/lib/pgsql/data # du -sh pg_log/ >> 73G pg_log/ >> >> Is it necessary to keep this Log files? Can i backup the logs and delete >> it from the original directory ? Is this logs files necessary in case any >> data recovery to be done ? >> I am database dumps every day . >> > you're probably logging too much. Change level of logging (log_statement to > ddl for instance), and do 'pg_ctl reload' > > > > -- > GJ >
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
2009/9/25 Shiva Raman > As suggested, i had changed the log_statement='ddl' and now it is logging > only > the ddl statements . thanks for the tip. > Can i delete the old log files in pg_log after backing up as zip archive ? > is it neccesary to keep those log files ? > they're yours, you can do whatever you wish with em. pg_logs are just textual log files. pg_xlogs on the other hand, you should never touch (unless using logs storage/shipment for backups/replication). -- GJ
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
On Fri, Sep 25, 2009 at 9:06 AM, Shiva Raman wrote: > Hi Gerhard > I also found the pg_log has 73 G of data . > > clusternode2:/var/lib/pgsql/data # du -sh pg_log/ > 73G pg_log/ > > Is it necessary to keep this Log files? Can i backup the logs and delete it > from the original directory ? Is this logs files necessary in case any data > recovery to be done ? > I am database dumps every day . > you're probably logging too much. Change level of logging (log_statement to ddl for instance), and do 'pg_ctl reload' -- GJ
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Hi Gerhard I also found the pg_log has 73 G of data . clusternode2:/var/lib/pgsql/data # du -sh pg_log/ 73G pg_log/ Is it necessary to keep this Log files? Can i backup the logs and delete it from the original directory ? Is this logs files necessary in case any data recovery to be done ? I am database dumps every day . pg_xlog and pg_clog has nearly less than 25 Mb of data only. Regds Shiva Raman
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Hi Gerhard Thanks for the mail On Thu, Sep 24, 2009 at 7:19 PM, Gerhard Wiesinger wrote: > Hello Shiva, > > What I see from top (0.0%wa) you don't have any I/O problem but a major CPU > problem. But this is contrast to iostat where up to 50% of iowait is there > (sometimes). > > I think you have 2 problems: > 1.) Client applications which don't close the connection. If the > applications wants persistent connections (for performance reasons), then > idle postgresql processes are ok. A better approach would be some kind of > connection pool. What programming language do you use on the web tier? > I am using connection pooling on Tomcat Web Server . Total of 500 connections are configured to be handled in the connection pool. > 2.) Find out queries which produce the high CPU load. (e.g. pg_top). I > guess there are some very suboptimal queries there. (I guess some indexes > are missing). > You could e.g. set > log_min_duration_statement = 50 # 50ms, all slower queries are logged > > I enabled the min duration statement and i found that allmost ninety percent of queries are logged which has duration more thatn 50. Most of the queries ranges between 50 and 500. Certain Select queuries duration are between 1000 and 2500. And for report queries with more than 3 lakh and 1 lakh rows , the queries takes more than 6000 ms. And: Idle connection don't take any I/O and CPU, just memory resources (and > very small network resources). > > And IHMO killing database processes isn't a solution to your problem. > Database server should nearly never be restarted. > > Ciao, > Gerhard > Regards Shiva Raman > > >
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Dave Dutcher wrote: >> From: Shiva Raman >> Subject: Re: [PERFORM] High CPU load on Postgres Server during Peak >> > not explicitly committed. > >> We have started updating the code on this. >> > > 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. > > Dave > > Dave is correct. A SELECT without a BEGIN in front of it will not begin a transaction. Atomic SELECTs (that is, those not intended to return rows that will then be updated or deleted, etc.) does not need and should NOT have a BEGIN in front of it. Any block of statements that must act in an atomic fashion must have a BEGIN/COMMIT or BEGIN/ROLLBACK block around them to guarantee atomic results across statements; any time you issue a BEGIN you MUST issue either a ROLLBACK or COMMIT. Exiting SOUNDS safe (and if the connection is truly dropped it is as that will implicitly roll back any uncommitted transaction) BUT in a pooled connection environment it leads to exactly what you're seeing here. It is a serious mistake to leave open transactions active in a session as that leaves multiple copies of rows and the support data necessary to handle them either in memory, on disk or both. When the working set of all postgresql instances reaches the physical memory limit and the system starts to page performance will go straight in the toilet. -- Karl <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
>From: Shiva Raman >Subject: Re: [PERFORM] High CPU load on Postgres Server during Peak times > >Andy Colson Wrote : , >>Eww. I think that's bad. A connection that has a transaction open will cause lots of row versions, >>which use up ram, and make it slower to step through the table (even with an index). You really need >>to fix up your code and make sure you commit transactions. (any statement (select, insert, update) will >>start a new transaction that you need to explicitly commit). > >With reference to this suggestion by Andy Colson, we checked the application code and found that only >INSERT, UPDATE has COMMIT and SELECT has no commit, We are using a lot of "Ajax Suggest" in the all >the forms accessed for fetching the data using SELECT statements which are not explicitly committed. >We have started updating the code on this. 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. Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Andy Colson wrote: > Shiva Raman wrote: >> Hi >> >> Today the load observed very high load . I am pasting the top. >> >> _*TOP *_ >> top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84, >> 33.13, 25.84 >> Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie >> Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, >> 0.1%si, 50.0%st >> Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers >> Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached >> >> /usr/bin/wc -l ` -gt 20 && /usr/bin/pkill -o -f 'idle in transaction' >> >> and this is the link where the script was provided. >> >> http://wiki.dspace.org/index.php/Idle_In_Transaction_Problem >> >> I tried it run it as test in the server, but the script is not >> executing. Even i see many of the "Idle in transaction " PIDs are >> showing R (RUnning status) , but most of them are showing S(Sleep ) >> status. Please suggest anyway i can resolve this idle transaction issue. > > fixing up the code to commit selects will make the "idle in trans." go > away. I'm with Praveen, fix the code, avoid the scripts. > > Is there anything else running on this box? You said previously "The > PowerPC cpu is having some virtual layer that is shown in the Steal > value.". I'm not sure what that means. Are you in a virtual machine? > Or running other vm's? Based on the top you posted (this one and the > very first one) you are loosing half your cpu to the vm. (unless I'm > totally reading this wrong... I don't have experience with vm's so > please someone jump in here and correct me if I'm wrong) > "idle in transaction" processes will DESTROY throughput over time. Don't kill them - find out how they're happening. They should NOT happen. If you take an exception in an application it is essential that the application NOT leave pending transactions open. If your middleware between application and Postgres doesn't take care of this cleanup on exit on its own (or if it would if you left through an "approved" path but you're doing something like SEGVing out of a compiled app or calling exit() without closing open connections, etc) you need to figure out where you're getting these exceptions from and fix them. Hacks like killing "idle in transaction" processes will eventually bite you by killing a process that is TEMPORARILY idle while waiting for some resource but the check "catches it" at exactly the wrong time, whacking a perfectly good change. At best this returns an error to the user; at worst, especially in a web-based application, it can result in a silently-lost transaction. -- Karl <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Shiva Raman wrote: Hi Today the load observed very high load . I am pasting the top. _*TOP *_ top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84, 33.13, 25.84 Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.1%si, 50.0%st Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached _*OUTPUT OF IOSTAT 1 5 (is SAN becoming a bottleneck,shows 50% CPU usage?) *_ clusternode2:~ # iostat 1 5 Linux 2.6.16.46-0.12-ppc64 (clusternode2) 09/24/2009 _ppc64_ (4 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 16.000.000.680.61 10.72 71.99 avg-cpu: %user %nice %system %iowait %steal %idle 29.460.000.250.007.43 62.87 avg-cpu: %user %nice %system %iowait %steal %idle 46.170.000.990.00 38.52 14.32 avg-cpu: %user %nice %system %iowait %steal %idle 48.880.000.990.00 49.880.25 avg-cpu: %user %nice %system %iowait %steal %idle 47.860.002.140.00 50.000.00 Both top and iostat show no wait time for io. However, they both show wait time on the vm. You have 50% user and 50% steal, and zero% io. you said: "SAN becoming a bottleneck,shows 50% CPU usage?" I'm not sure what you are looking at. SAN is like HD right? I assume waiting on the SAN would show up as %iowait... yes? Andy Colson Wrote : , /Eww. I think that's bad. A connection that has a transaction open will cause lots of row versions, which use up ram, and make it slower to step through the table (even with an index). You really need to fix up your code and make sure you commit transactions. (any statement (select, insert, update) will start a new transaction that you need to explicitly commit). /With reference to this suggestion by Andy Colson, we checked the application code and found that onlyINSERT, UPDATE has COMMIT and SELECT has no commit, We are using a lot of "Ajax Suggest" in the all the forms accessed for fetching the data using SELECT statements which are not explicitly commited. We have started updating the code on this. Thanks for this suggestion. Again thanks to suggestion of Scott Marlowe in reducing the number of connections. This was now reducted to 500 . As i mentioned in the mail, i am restarting the database every 30 minutes. I found a shell script in the wiki which could the idle in transaction pids. This is the code. The code will kill all old pids in the server. This is the script /usr/bin/test `/usr/bin/pgrep -f 'idle in transaction' | \ /usr/bin/wc -l ` -gt 20 && /usr/bin/pkill -o -f 'idle in transaction' and this is the link where the script was provided. http://wiki.dspace.org/index.php/Idle_In_Transaction_Problem I tried it run it as test in the server, but the script is not executing. Even i see many of the "Idle in transaction " PIDs are showing R (RUnning status) , but most of them are showing S(Sleep ) status. Please suggest anyway i can resolve this idle transaction issue. fixing up the code to commit selects will make the "idle in trans." go away. I'm with Praveen, fix the code, avoid the scripts. Is there anything else running on this box? You said previously "The PowerPC cpu is having some virtual layer that is shown in the Steal value.". I'm not sure what that means. Are you in a virtual machine? Or running other vm's? Based on the top you posted (this one and the very first one) you are loosing half your cpu to the vm. (unless I'm totally reading this wrong... I don't have experience with vm's so please someone jump in here and correct me if I'm wrong) -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
For 'idle in transaction' issues, you have to fix your code. I faced this issue couple of months back. How good is your exception handling? Are you rollingback/comitting your transactions while exceptions are thrown, during the course of db operations? Honestly I wouldn't go for these scripts which kill processes. On Thu, Sep 24, 2009 at 6:20 PM, Shiva Raman wrote: > Hi > > Today the load observed very high load . I am pasting the top. > > *TOP * > top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84, 33.13, > 25.84 > Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie > Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.1%si, > 50.0%st > Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers > Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ > COMMAND > 4152 postgres 17 0 2436m 176m 171m R 16 1.1 0:03.09 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4122 postgres 17 0 2431m 20m 17m R 12 0.1 0:06.38 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4007 postgres 16 0 2434m 80m 75m R 11 0.5 0:26.46 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 3994 postgres 16 0 2432m 134m 132m R 10 0.9 0:43.40 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4166 postgres 16 0 2433m 12m 8896 R9 0.1 0:02.71 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4110 postgres 15 0 2436m 224m 217m S8 1.4 0:06.83 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4061 postgres 16 0 2446m 491m 473m R8 3.1 0:17.32 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4113 postgres 16 0 2432m 68m 65m R8 0.4 0:11.03 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4071 postgres 16 0 2435m 200m 194m R7 1.3 0:13.69 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4169 postgres 15 0 2436m 122m 117m R7 0.8 0:00.93 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4178 postgres 16 0 2432m 77m 75m R7 0.5 0:00.56 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4108 postgres 16 0 2437m 301m 293m R6 1.9 0:11.94 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4155 postgres 16 0 2438m 252m 244m S5 1.6 0:02.80 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4190 postgres 15 0 2432m 10m 8432 R5 0.1 0:00.71 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 3906 postgres 16 0 2433m 124m 119m R5 0.8 0:57.28 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 3970 postgres 16 0 2442m 314m 304m R5 2.0 0:16.43 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4130 postgres 17 0 2433m 76m 72m R5 0.5 0:03.76 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4179 postgres 16 0 2432m 105m 102m R5 0.7 0:01.11 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4125 postgres 17 0 2436m 398m 391m R4 2.5 0:05.62 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4162 postgres 16 0 2432m 125m 122m R4 0.8 0:01.01 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 217m S8 1.4 0:06.83 postgres: postgres dbEnterpriser_09_10 > 192.168.10. dbEnterpriser_09_10 192.168.10. > 4061 postgres 16 0 2446m 491m 473m R8 3.1 0:17.32 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4113 postgres 16 0 2432m 68m 65m R8 0.4 0:11.03 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4071 postgres 16 0 2435m 200m 194m R7 1.3 0:13.69 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4169 postgres 15 0 2436m 122m 117m R7 0.8 0:00.93 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4178 postgres 16 0 2432m 77m 75m R7 0.5 0:00.56 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4108 postgres 16 0 2437m 301m 293m R6 1.9 0:11.94 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4155 postgres 16 0 2438m 252m 244m S5 1.6 0:02.80 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4190 postgres 15 0 2432m 10m 8432 R5 0.1 0:00.71 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 3906 postgres 16 0 2433m 124m 119m R5 0.8 0:57.28 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 3970 postgres 16 0 2442m 314m 304m R5 2.0 0:16.43 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4130 postgres 17 0 2433m 76m 72m R5 0.5 0:03.76 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4179 postgres 16 0 2432m 105m 102m R5 0.7 0:01.11 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4125 postgres 17 0 2436m 398m 391m R4 2.5 0:05.62 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4162 postgres 16 0 2432m 125m 122m R4 0.8 0:01.01 postgres: > postgres dbEnterpriser_09_10 192.168.10. > 4185 postgres 1 > >
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Hi Today the load observed very high load . I am pasting the top. *TOP * top - 12:45:23 up 79 days, 14:42, 1 user, load average: 45.84, 33.13, 25.84 Tasks: 394 total, 48 running, 346 sleeping, 0 stopped, 0 zombie Cpu(s): 49.2%us, 0.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.1%si, 50.0%st Mem: 16133676k total, 14870736k used, 1262940k free, 475484k buffers Swap: 14466492k total, 124k used, 14466368k free, 11423616k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4152 postgres 17 0 2436m 176m 171m R 16 1.1 0:03.09 postgres: postgres dbEnterpriser_09_10 192.168.10. 4122 postgres 17 0 2431m 20m 17m R 12 0.1 0:06.38 postgres: postgres dbEnterpriser_09_10 192.168.10. 4007 postgres 16 0 2434m 80m 75m R 11 0.5 0:26.46 postgres: postgres dbEnterpriser_09_10 192.168.10. 3994 postgres 16 0 2432m 134m 132m R 10 0.9 0:43.40 postgres: postgres dbEnterpriser_09_10 192.168.10. 4166 postgres 16 0 2433m 12m 8896 R9 0.1 0:02.71 postgres: postgres dbEnterpriser_09_10 192.168.10. 4110 postgres 15 0 2436m 224m 217m S8 1.4 0:06.83 postgres: postgres dbEnterpriser_09_10 192.168.10. 4061 postgres 16 0 2446m 491m 473m R8 3.1 0:17.32 postgres: postgres dbEnterpriser_09_10 192.168.10. 4113 postgres 16 0 2432m 68m 65m R8 0.4 0:11.03 postgres: postgres dbEnterpriser_09_10 192.168.10. 4071 postgres 16 0 2435m 200m 194m R7 1.3 0:13.69 postgres: postgres dbEnterpriser_09_10 192.168.10. 4169 postgres 15 0 2436m 122m 117m R7 0.8 0:00.93 postgres: postgres dbEnterpriser_09_10 192.168.10. 4178 postgres 16 0 2432m 77m 75m R7 0.5 0:00.56 postgres: postgres dbEnterpriser_09_10 192.168.10. 4108 postgres 16 0 2437m 301m 293m R6 1.9 0:11.94 postgres: postgres dbEnterpriser_09_10 192.168.10. 4155 postgres 16 0 2438m 252m 244m S5 1.6 0:02.80 postgres: postgres dbEnterpriser_09_10 192.168.10. 4190 postgres 15 0 2432m 10m 8432 R5 0.1 0:00.71 postgres: postgres dbEnterpriser_09_10 192.168.10. 3906 postgres 16 0 2433m 124m 119m R5 0.8 0:57.28 postgres: postgres dbEnterpriser_09_10 192.168.10. 3970 postgres 16 0 2442m 314m 304m R5 2.0 0:16.43 postgres: postgres dbEnterpriser_09_10 192.168.10. 4130 postgres 17 0 2433m 76m 72m R5 0.5 0:03.76 postgres: postgres dbEnterpriser_09_10 192.168.10. 4179 postgres 16 0 2432m 105m 102m R5 0.7 0:01.11 postgres: postgres dbEnterpriser_09_10 192.168.10. 4125 postgres 17 0 2436m 398m 391m R4 2.5 0:05.62 postgres: postgres dbEnterpriser_09_10 192.168.10. 4162 postgres 16 0 2432m 125m 122m R4 0.8 0:01.01 postgres: postgres dbEnterpriser_09_10 192.168.10. 217m S8 1.4 0:06.83 postgres: postgres dbEnterpriser_09_10 192.168.10. dbEnterpriser_09_10 192.168.10. 4061 postgres 16 0 2446m 491m 473m R8 3.1 0:17.32 postgres: postgres dbEnterpriser_09_10 192.168.10. 4113 postgres 16 0 2432m 68m 65m R8 0.4 0:11.03 postgres: postgres dbEnterpriser_09_10 192.168.10. 4071 postgres 16 0 2435m 200m 194m R7 1.3 0:13.69 postgres: postgres dbEnterpriser_09_10 192.168.10. 4169 postgres 15 0 2436m 122m 117m R7 0.8 0:00.93 postgres: postgres dbEnterpriser_09_10 192.168.10. 4178 postgres 16 0 2432m 77m 75m R7 0.5 0:00.56 postgres: postgres dbEnterpriser_09_10 192.168.10. 4108 postgres 16 0 2437m 301m 293m R6 1.9 0:11.94 postgres: postgres dbEnterpriser_09_10 192.168.10. 4155 postgres 16 0 2438m 252m 244m S5 1.6 0:02.80 postgres: postgres dbEnterpriser_09_10 192.168.10. 4190 postgres 15 0 2432m 10m 8432 R5 0.1 0:00.71 postgres: postgres dbEnterpriser_09_10 192.168.10. 3906 postgres 16 0 2433m 124m 119m R5 0.8 0:57.28 postgres: postgres dbEnterpriser_09_10 192.168.10. 3970 postgres 16 0 2442m 314m 304m R5 2.0 0:16.43 postgres: postgres dbEnterpriser_09_10 192.168.10. 4130 postgres 17 0 2433m 76m 72m R5 0.5 0:03.76 postgres: postgres dbEnterpriser_09_10 192.168.10. 4179 postgres 16 0 2432m 105m 102m R5 0.7 0:01.11 postgres: postgres dbEnterpriser_09_10 192.168.10. 4125 postgres 17 0 2436m 398m 391m R4 2.5 0:05.62 postgres: postgres dbEnterpriser_09_10 192.168.10. 4162 postgres 16 0 2432m 125m 122m R4 0.8 0:01.01 postgres: postgres dbEnterpriser_09_10 192.168.10. 4185 postgres 1 *OUTPUT OF IOSTAT 1 5 (is SAN becoming a bottleneck,shows 50% CPU usage?) * clusternode2:~ # iostat 1 5 Linux 2.6.16.46-0.12-ppc64 (clusternode2) 09/24/2009 _ppc64_ (4 CPU) avg-cpu: %user %nice %system %iowait %steal %idle 16.000.000.680.61 10.72 71.99 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 1.08 1.9622.54 13505448 155494808 sdb 0.00 0.20 0.4514101793099920 sdc 0.00 0.
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
On Wed, Sep 23, 2009 at 12:25 PM, Shiva Raman wrote: First let me say that upgrading to a later version is likely going to help as much as anything else you're likely to pick up from this discussion. Not that this discussion isn't worthwhile, it is. > If you run a 'ps ax|grep post' do you see anything that says 'idle in > transaction'? (I hope that old of version will show it. my processes show up > as postgres not postmaster) > > Lots of requests shows as 'idle in transaction'. > > Currently i am restarting the database using a cron job every 30 minutes > during offpeak time > > and every 15 minutes during the peak time. Wow. It'd be way better if you could fix your application / connection layer to not do that. > As per the concurrency of 300 to 400 users, the following parameters are > changed in > > postgresql conf based on the calculation provided in the postgresql > documentation. > > Max connections = 1800 ( Too much open connections will result in unwanted > memory wastage) This is very high. If you only need 400 users, you might want to consider setting this to 500 or so. > Shared Buffers = 375 000 ( 375000 * 8 * 1024 /100 = 3072 MB ) # proposed > value is 1/4 the actual memory Reasonable, but don't just blindly use 1/4 memory. For transactional loads smaller is often better. For reporting dbs, larger is often better. Test it to see what happens with your load and varying amounts of shared_buffers > Effective Cache Size = 266000 ( 266000 * 8 * 1024 /100 = 2179 MB ) # > proposed value is 1/3 memory after OS Allocation Better to add the cache / buffer amount of OS and shared_buffers to get it. Which would be much higher. Generally it's in the 3/4 of memory on most machines. > work_mem = 3000 ( 3000 * max connections * 1024 = 3000 * 1800 * 1024 = 5529 > MB ( this is the working memory for postgres) ) This is the max work_mem per sort or hash aggregate. Note that if all of your maximum backends connected and each did 2 sorts and one hash aggregate at once, you could use max_connections * 3 * work_mem memory at once. Machine swaps til it dies. Assuming this is 3000 8k blocks that 24Meg which is high but not unreasonable. > max_fsm_pages = 2 ( This has to be analyzed and can be increased to > 4, this can be done after one or two day observation) To see what you need here, log into the postgres database as a superuser and issue the command: vacuum verbose; and see what the last 5 or so lines have to say. They'll look like this: INFO: free space map contains 339187 pages in 18145 relations DETAIL: A total of 623920 page slots are in use (including overhead). 623920 page slots are required to track all free space. Current limits are: 1000 page slots, 50 relations, using 109582 kB. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Fernando Hevia wrote: User Access Total Number of Users is 500 Maximum number of Concurrent users will be 500 during peak time Off Peak time the maximum number of concurrent user will be around 150 to 200. >>> A connection pooler like pgpool or pgbouncer would considerably reduce the >>> burden on your system. >>> >>> >> I am already using connection pooling in tomcat web server, so installing >> > pgpool > >> will help enhancing the performance ?Any changes i have to do in my >> > application to > >> include pgpool? >> >> > > There shouldn't be need for another pooling solution. > Anyway, you probably dont want 1800 concurrent connections on your database > server, nor even get near that number. > > Check the number of actual connections with: > select count(*) from pg_stat_activity; > > A vmstat run during high loads could provide a hindsight to if the number of > connections is straining your server. > > If the number of connections is high (say over 200-300), try reducing the > pool size in Tomcat and see what happens. > You possibly could do fine with something between 50 and 100 connections. > > I can second this - I have an EXTREMELY busy forum system using pgpool and during peak hours it runs very well within around 100 connections in use. -- Karl <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
>>> >>> User Access >>> Total Number of Users is 500 >>> Maximum number of Concurrent users will be 500 during peak time >>> Off Peak time the maximum number of concurrent user will be >>> around 150 to 200. >>> >> >>A connection pooler like pgpool or pgbouncer would considerably reduce the >>burden on your system. >> > >I am already using connection pooling in tomcat web server, so installing pgpool >will help enhancing the performance ?Any changes i have to do in my application to >include pgpool? > There shouldn't be need for another pooling solution. Anyway, you probably dont want 1800 concurrent connections on your database server, nor even get near that number. Check the number of actual connections with: select count(*) from pg_stat_activity; A vmstat run during high loads could provide a hindsight to if the number of connections is straining your server. If the number of connections is high (say over 200-300), try reducing the pool size in Tomcat and see what happens. You possibly could do fine with something between 50 and 100 connections. Regards, Fernando. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Shiva Raman wrote: /If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster)/ Lots of requests shows as 'idle in transaction'. Eww. I think that's bad. A connection that has a transaction open will cause lots of row versions, which use up ram, and make it slower to step through the table (even with an index). You really need to fix up your code and make sure you commit transactions. (any statement (select, insert, update) will start a new transaction that you need to explicitly commit). Currently i am restarting the database using a cron job every 30 minutes during offpeak time and every 15 minutes during the peak time. do you get lots of update/deletes? Or are there mostly selects? If its mostly update/delete then the 'idle in transactions' is killing you. If you have mostly selects then its probably something else. work_mem = 3000 ( 3000 * max connections * 1024 = 3000 * 1800 * 1024 = 5529 MB ( this is the working memory for postgres) ) work_mem is per connection. If you changed this to get a better query plan then ok, but dont change it just for the sake of changing it. Ick... I just went back and checked, you have 16G of ram... this probably isn't a problem. Nevermind. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Hi Thanks for your mail. *Some quick advice:* * * *>* *> clusternode2:~ # rpm -qa | grep postgres* *> postgresql-devel-8.1.9-1.2* *> postgresql-8.1.9-1.2* *> postgresql-docs-8.1.9-1.2* *> postgresql-server-8.1.9-1.2* *> postgresql-libs-64bit-8.1.9-1.2* *> postgresql-libs-8.1.9-1.2* *> postgresql-jdbc-8.1-12.2* *> postgresql-contrib-8.1.9-1.2* *>* *>* 8.1 is quite old. Consider upgrading as newer versions are faster. Current Postgres version is 8.4. > *> High Availability Cluster with two IBM P Series Server and* *> one DS4700 Storage* *>* *> IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card* *> , 36 MB L3 Cache ,16 GB of RAM,* *> 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System .* *>* * * *Sounds you are underpowered on cpu for 500 concurrent users.* *Of course this really depends on what they are doing.* * * *>* *> IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10)* *> Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage* *> partitions (One holding Jakarata tomcat* *> application server and other holding Postgresql Database) .* *> Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN .* *> Hard disk rotational speed is 73 GB 15K IBM 2 GB Fibre channel* *>* * * *A more suitable partitioning for an OLTP database would be:* * * *2 x 73.4 GB RAID 1 for App Server + Postgresql and pg_xlog* *8 x 73.4 GB RAID 10 for pgdata* * * *RAID 5 is strongly discouraged.* *- Show quoted text -* * * *>* *> Following is the output of TOP command during offpeak time.* *>* *>* *> top - 18:36:56 up 77 days, 20:33, 1 user, load average:* *> 12.99, 9.22, 10.37* *> Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie* *> Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi,* *> 0.1%si, 42.9%st* *> Mem: 16133676k total, 13657396k used, 2476280k free,* *> 450908k buffers* *> Swap: 14466492k total, 124k used, 14466368k free,* *> 11590056k cached* *>* *> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+* *> COMMAND* *> 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49* *> postmaster* *> 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44* *> postmaster* *> 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78* *> postmaster* *> 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73* *> postmaster* *> 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52* *> postmaster* *> 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46* *> postmaster* *> 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11* *> postmaster* *> 22485 postgres 16 0 2439m 230m 222m R 7 1.5 0:05.72* *> postmaster* *> 22481 postgres 15 0 2436m 175m 169m S 7 1.1 0:04.44* *> postmaster* *> 22435 postgres 17 0 2438m 371m 361m R 6 2.4 1:17.92* *> postmaster* *> 22440 postgres 17 0 2445m 497m 483m R 5 3.2 1:44.50* *> postmaster* *> 22486 postgres 17 0 2432m 84m 81m R 4 0.5 0:00.76* *> postmaster* *>* * * *Are you running several Postgres clusters on this hardware?* *Please post Top output showing cmd line arguments (press 'c')* NO Only single Postgres instance > > User Access > Total Number of Users is 500 > Maximum number of Concurrent users will be 500 during peak time > Off Peak time the maximum number of concurrent user will be > around 150 to 200. > * * *A connection pooler like pgpool or pgbouncer would considerably reduce the* *burden on your system.* I am already using connection pooling in tomcat web server, so installing pgpool will help enhancing the performance ?Any changes i have to do in my application to include pgpool? Regds Shiva raman
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Hi Thanks a lot for the reply. *I see you are on a pretty old version of pg. Are you vacuuming regularly?* Yes, Vaccuuming is done every day morning at 06 am It is running perfectly fine. * * *If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster)* Lots of requests shows as 'idle in transaction'. Currently i am restarting the database using a cron job every 30 minutes during offpeak time and every 15 minutes during the peak time. The top looks like you are cpu bound. *Have you tried enabling logging slow queries? (again, I hope your version supports that) It could be you have a query or two that are not using indexes, and slowing everything down.* Exactly right, thanks for the tip. I indexed few tables frequently accessed which are not indexed. After indexing the load has come down to 50 % during Peak time its between 10 and 20 and during offpeak its between 4 and 8 . The PowerPC cpu is having some virtual layer that is shown in the Steal value. *Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a load of 13. Load usually means somebody is waiting for something. But you have a little cpu idle time... and you have very low disk waits... you are using very little swap. hum... odd...* As per the concurrency of 300 to 400 users, the following parameters are changed in postgresql conf based on the calculation provided in the postgresql documentation. Max connections = 1800 ( Too much open connections will result in unwanted memory wastage) Shared Buffers = 375 000 ( 375000 * 8 * 1024 /100 = 3072 MB ) # proposed value is 1/4 the actual memory Effective Cache Size = 266000 ( 266000 * 8 * 1024 /100 = 2179 MB ) # proposed value is 1/3 memory after OS Allocation work_mem = 3000 ( 3000 * max connections * 1024 = 3000 * 1800 * 1024 = 5529 MB ( this is the working memory for postgres) ) max_fsm_pages = 2 ( This has to be analyzed and can be increased to 4, this can be done after one or two day observation) Postgresql.conf --- hba_file = '/var/lib/pgsql/data/pg_hba.conf' listen_addresses = '*' port = 5432 max_connections = 1800 shared_buffers = 30 max_fsm_relations = 1000 effective_cache_size = 20 log_destination = 'stderr' redirect_stderr = on log_rotation_age = 0 log_rotation_size = 10240 silent_mode = onlog_line_prefix = '%t %d %u ' autovacuum = on datestyle = 'iso, dmy' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' Any modifications i have to do in this values ? Regds Shiva Raman .
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
> -Mensaje original- > De: Shiva Raman > Enviado el: Martes, 22 de Septiembre de 2009 10:55 > Para: pgsql-performance@postgresql.org > Asunto: [PERFORM] High CPU load on Postgres Server during > Peak times > > Dear all > > I am having a problem of high cpu loads in my postgres > server during peak time. Some quick advice: > > clusternode2:~ # rpm -qa | grep postgres > postgresql-devel-8.1.9-1.2 > postgresql-8.1.9-1.2 > postgresql-docs-8.1.9-1.2 > postgresql-server-8.1.9-1.2 > postgresql-libs-64bit-8.1.9-1.2 > postgresql-libs-8.1.9-1.2 > postgresql-jdbc-8.1-12.2 > postgresql-contrib-8.1.9-1.2 > > 8.1 is quite old. Consider upgrading as newer versions are faster. Current Postgres version is 8.4. > > High Availability Cluster with two IBM P Series Server and > one DS4700 Storage > > IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card > , 36 MB L3 Cache ,16 GB of RAM, > 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . > Sounds you are underpowered on cpu for 500 concurrent users. Of course this really depends on what they are doing. > > IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10) > Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage > partitions (One holding Jakarata tomcat > application server and other holding Postgresql Database) . > Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN . > Hard disk rotational speed is 73 GB 15K IBM 2 GB Fibre channel > A more suitable partitioning for an OLTP database would be: 2 x 73.4 GB RAID 1 for App Server + Postgresql and pg_xlog 8 x 73.4 GB RAID 10 for pgdata RAID 5 is strongly discouraged. > > Following is the output of TOP command during offpeak time. > > > top - 18:36:56 up 77 days, 20:33, 1 user, load average: > 12.99, 9.22, 10.37 > Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie > Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, > 0.1%si, 42.9%st > Mem: 16133676k total, 13657396k used, 2476280k free, > 450908k buffers > Swap: 14466492k total, 124k used, 14466368k free, > 11590056k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ > COMMAND > 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 > postmaster > 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 > postmaster > 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 > postmaster > 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 > postmaster > 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 > postmaster > 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 > postmaster > 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 > postmaster > 22485 postgres 16 0 2439m 230m 222m R7 1.5 0:05.72 > postmaster > 22481 postgres 15 0 2436m 175m 169m S7 1.1 0:04.44 > postmaster > 22435 postgres 17 0 2438m 371m 361m R6 2.4 1:17.92 > postmaster > 22440 postgres 17 0 2445m 497m 483m R5 3.2 1:44.50 > postmaster > 22486 postgres 17 0 2432m 84m 81m R4 0.5 0:00.76 > postmaster > Are you running several Postgres clusters on this hardware? Please post Top output showing cmd line arguments (press 'c') > > User Access > Total Number of Users is 500 > Maximum number of Concurrent users will be 500 during peak time > Off Peak time the maximum number of concurrent user will be > around 150 to 200. > A connection pooler like pgpool or pgbouncer would considerably reduce the burden on your system. Regards, Fernando. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Andy Colson wrote: Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37 Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si, 42.9%st Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached First off, nice report. I see you are on a pretty old version of pg. Are you vacuuming regularly? If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster) The top looks like you are cpu bound. Have you tried enabling logging slow queries? (again, I hope your version supports that) It could be you have a query or two that are not using indexes, and slowing everything down. Also on the top, it has this: 42.9%st. Are you in a vm? or running vm's on the box? Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a load of 13. Load usually means somebody is waiting for something. But you have a little cpu idle time... and you have very low disk waits... you are using very little swap. hum... odd... -Andy Looks like I missed an important point. You said this was top during off peak time. So ignore my high load ramblings. But... if this is off peak, and you only have 6% idle cpu... I'd say your cpu bound. (I'm still not sure what the 42.9%st is, so maybe I'm off base with the 6% idle too) -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
Shiva Raman wrote: Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . *Following is the output of TOP command during offpeak time.* top - 18:36:56 up 77 days, 20:33, 1 user, load average: 12.99, 9.22, 10.37 Tasks: 142 total, 12 running, 130 sleeping, 0 stopped, 0 zombie Cpu(s): 46.1%us, 1.9%sy, 0.0%ni, 6.1%id, 3.0%wa, 0.0%hi, 0.1%si, 42.9%st Mem: 16133676k total, 13657396k used, 2476280k free, 450908k buffers Swap: 14466492k total, 124k used, 14466368k free, 11590056k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 22458 postgres 19 0 2473m 477m 445m R 40 3.0 0:15.49 postmaster 22451 postgres 15 0 2442m 447m 437m S 33 2.8 0:30.44 postmaster 22464 postgres 17 0 2443m 397m 383m R 28 2.5 0:13.78 postmaster 22484 postgres 16 0 2448m 431m 412m S 20 2.7 0:02.73 postmaster 22465 postgres 17 0 2440m 461m 449m R 15 2.9 0:03.52 postmaster 22452 postgres 16 0 2450m 727m 706m R 13 4.6 0:23.46 postmaster 22476 postgres 16 0 2437m 413m 405m S 13 2.6 0:06.11 postmaster 22485 postgres 16 0 2439m 230m 222m R7 1.5 0:05.72 postmaster 22481 postgres 15 0 2436m 175m 169m S7 1.1 0:04.44 postmaster 22435 postgres 17 0 2438m 371m 361m R6 2.4 1:17.92 postmaster 22440 postgres 17 0 2445m 497m 483m R5 3.2 1:44.50 postmaster 22486 postgres 17 0 2432m 84m 81m R4 0.5 0:00.76 postmaster First off, nice report. I see you are on a pretty old version of pg. Are you vacuuming regularly? If you run a 'ps ax|grep post' do you see anything that says 'idle in transaction'? (I hope that old of version will show it. my processes show up as postgres not postmaster) The top looks like you are cpu bound. Have you tried enabling logging slow queries? (again, I hope your version supports that) It could be you have a query or two that are not using indexes, and slowing everything down. Also on the top, it has this: 42.9%st. Are you in a vm? or running vm's on the box? Its weird, you have 6.1% idle and 3.0% waiting for disk and yet you have a load of 13. Load usually means somebody is waiting for something. But you have a little cpu idle time... and you have very low disk waits... you are using very little swap. hum... odd... -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] High CPU load on Postgres Server during Peak times!!!!
On Tue, Sep 22, 2009 at 9:54 AM, Shiva Raman wrote: > Dear all > > I am having a problem of high cpu loads in my postgres server during peak > time. Following are the > details of my setup (details as per the postgres wiki) . > > * PostgreSQL version > o Run "select pg_version();" in psql or PgAdmin III and provide the > full, exact output. > > > clusternode2:~ # rpm -qa | grep postgres > postgresql-devel-8.1.9-1.2 > postgresql-8.1.9-1.2 > postgresql-docs-8.1.9-1.2 > postgresql-server-8.1.9-1.2 > postgresql-libs-64bit-8.1.9-1.2 > postgresql-libs-8.1.9-1.2 > postgresql-jdbc-8.1-12.2 > postgresql-contrib-8.1.9-1.2 > > > * A description of what you are trying to achieve and what results you > expect. > > To keep the CPU Load below 10 , Now during peak times the load is nearing to > 40 > At that time , it is not possible to access the data. > > * The EXACT text of the query you ran, if any > > > * The EXACT output of that query if it's short enough to be reasonable to > post > o If you think the output is wrong, what you think should've been > produced instead > > * The EXACT error message you get, if there is one > > As of now , i am unable to locate the exact query, the load shoots up > abnormally during > peak time is the main problem . > > > * What program you're using to connect to PostgreSQL > > Jakarta Tomcat - Struts with JSP > > > * What version of the ODBC/JDBC driver you're using, if any > > postgresql-jdbc-8.1-12.2 > > * What you were doing when the error happened / how to cause the error. > Describe in as much detail as possible, step by step, including command > lines, SQL output, etc. > > When certain tables with more than 3 lakh items are concurrently accessed by > more than 300 > users, the CPU load shoots up . > > * Is there anything remotely unusual in the PostgreSQL server logs? > o On Windows these are in your data directory. On a default > PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log > (assuming you're using 8.4) > > The log file /var/log/postgresql has no data . > > o On Linux this depends a bit on distro, but you'll usually find > them in /var/log/postgresql/. > * Operating system and version > o Linux users: > + Linux distro and version > + Kernel details (run "uname -a" on the terminal) > > SLES 10 SP3 > clusternode2:~ # uname -a > Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007 > ppc64 ppc64 ppc64 GNU/Linux > > > > * What kind of hardware you have. > o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 > Duo" > o Amount and size of RAM installed, eg "2GB RAM" > > High Availability Cluster with two IBM P Series Server and one DS4700 > Storage > > IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card , 36 MB L3 > Cache ,16 GB of RAM, > 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . > > > > o Storage details (important for performance and corruption > questions) > + Do you use a RAID controller? If so, what type of > controller? eg "3Ware Escalade 8500-8" > # Does it have a battery backed cache module? > # Is write-back caching enabled? > + Do you use software RAID? If so, what software and what > version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686 > REGPARM gcc-4.1". > # In the case of Linux software RAID you can get the > details from the "modinfo md_mod" command > + Is your PostgreSQL database on a SAN? > # Who made it, what kind, etc? Provide what details you > can. > + How many hard disks are connected to the system and what > types are they? You need to say more than just "6 disks". At least give > maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS > disks". > + How are your disks arranged for storage? Are you using > RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks / > disk sets? What file system(s) are in use? > # eg: "Two disks in RAID 1, with all PostgreSQL data > and programs stored on one ext3 file system." > # eg: "4 disks in RAID 5 holding the pg data directory > on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the > temporary tablespace, and the sort scratch space, also on ext3.". > # eg: "Default Windows install of PostgreSQL" > + In case of corruption data reports: > # Have you had any unexpected power loss lately? > # Have you run a file system check? (chkdsk / fsck) > # Are there any error messages in the system logs? > (unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control > Panel -> Administrative Tools ) > > > IBM SAN DS4700 Storage with Fibr
[PERFORM] High CPU load on Postgres Server during Peak times!!!!
Dear all I am having a problem of high cpu loads in my postgres server during peak time. Following are the details of my setup (details as per the postgres wiki) . ** PostgreSQL version o Run "select pg_version();" in psql or PgAdmin III and provide the full, exact output.* clusternode2:~ # rpm -qa | grep postgres postgresql-devel-8.1.9-1.2 postgresql-8.1.9-1.2 postgresql-docs-8.1.9-1.2 postgresql-server-8.1.9-1.2 postgresql-libs-64bit-8.1.9-1.2 postgresql-libs-8.1.9-1.2 postgresql-jdbc-8.1-12.2 postgresql-contrib-8.1.9-1.2 * *A description of what you are trying to achieve and what results you expect.* To keep the CPU Load below 10 , Now during peak times the load is nearing to 40 At that time , it is not possible to access the data. ** The EXACT text of the query you ran, if any * The EXACT output of that query if it's short enough to be reasonable to post o If you think the output is wrong, what you think should've been produced instead * The EXACT error message you get, if there is one* As of now , i am unable to locate the exact query, the load shoots up abnormally during peak time is the main problem . ** What program you're using to connect to PostgreSQL* Jakarta Tomcat - Struts with JSP ** What version of the ODBC/JDBC driver you're using, if any* postgresql-jdbc-8.1-12.2 * *What you were doing when the error happened / how to cause the error. Describe in as much detail as possible, step by step, including command lines, SQL output, etc.* When certain tables with more than 3 lakh items are concurrently accessed by more than 300 users, the CPU load shoots up . ** Is there anything remotely unusual in the PostgreSQL server logs? o On Windows these are in your data directory. On a default PostgreSQL install that'll be in C:\Program Files\PostgreSQL\8.4\data\pg_log (assuming you're using 8.4) * The log file /var/log/postgresql has no data . * o On Linux this depends a bit on distro, but you'll usually find them in /var/log/postgresql/. * Operating system and version o Linux users: + Linux distro and version + Kernel details (run "uname -a" on the terminal) * SLES 10 SP3 clusternode2:~ # uname -a Linux clusternode2 2.6.16.46-0.12-ppc64 #1 SMP Thu May 17 14:00:09 UTC 2007 ppc64 ppc64 ppc64 GNU/Linux * * What kind of hardware you have. o CPU manufacturer and model, eg "AMD Athlon X2" or "Intel Core 2 Duo" o Amount and size of RAM installed, eg "2GB RAM" * High Availability Cluster with two IBM P Series Server and one DS4700 Storage IBM P series P52A with 2-core 2.1 Ghz POWER5+ Processor Card , 36 MB L3 Cache ,16 GB of RAM, 73.4 GB 10,000 RPM Ultra320 SCSI Drive for Operating System . * o Storage details (important for performance and corruption questions) + Do you use a RAID controller? If so, what type of controller? eg "3Ware Escalade 8500-8" # Does it have a battery backed cache module? # Is write-back caching enabled? + Do you use software RAID? If so, what software and what version? eg "Linux software RAID (md) 2.6.18-5-686 SMP mod_unload 686 REGPARM gcc-4.1". # In the case of Linux software RAID you can get the details from the "modinfo md_mod" command + Is your PostgreSQL database on a SAN? # Who made it, what kind, etc? Provide what details you can. + How many hard disks are connected to the system and what types are they? You need to say more than just "6 disks". At least give maker, rotational speed and interface type, eg "6 15,000rpm Seagate SAS disks". + How are your disks arranged for storage? Are you using RAID? If so, what RAID level(s)? What PostgreSQL data is on what disks / disk sets? What file system(s) are in use? # eg: "Two disks in RAID 1, with all PostgreSQL data and programs stored on one ext3 file system." # eg: "4 disks in RAID 5 holding the pg data directory on an ext3 file system. 2 disks in RAID 1 holding pg_clog, pg_xlog, the temporary tablespace, and the sort scratch space, also on ext3.". # eg: "Default Windows install of PostgreSQL" + In case of corruption data reports: # Have you had any unexpected power loss lately? # Have you run a file system check? (chkdsk / fsck) # Are there any error messages in the system logs? (unix/linux: "dmesg", "/var/log/syslog" ; Windows: Event Viewer in Control Panel -> Administrative Tools ) * IBM SAN DS4700 Storage with Fibre Channel HDD (73.4 GB * 10) Two Partitions - 73.4 GB * 3 RAID 5 - 134 GB storage partitions (One holding Jakarata tomcat application server and other holding Postgresql Database) . Four Hard disk RAID 5 with ext3 file systems hold the pgdata on SAN
Re: [PERFORM] High CPU Load
Hi All, I reply to me, we solved a CPU Load problem. We had an external batch who used an expensive SQL view and took 99% of the CPU. Thanks all for you help ! --- I started the HAPlatform open-source project is a part of Share'nGo Project, this goal is define all documentation and scripts required to install and maintain High Available platform. Tow platform are targeted : * LAPJ : Linux Apache PostgreSQL Java * LAMP : Linux Apache MySQL PHP The first documentation is here (it's my postgres configuration) : http://sharengo.org/haplatform/docs/PostgreSQL/en/html_single/index.html Cheers, Jérôme. -- Open-Source : http://www.sharengo.org Corporate : http://www.argia-engineering.fr Le vendredi 22 septembre 2006 à 09:43 +0200, Jérôme BENOIS a écrit : > Hi, Markus, > > Le mardi 19 septembre 2006 à 15:09 +0200, Markus Schaber a écrit : > > Hi, Jerome, > > > > Jérôme BENOIS wrote: > > > > >>> Now i Have 335 concurrent connections, i decreased work_mem parameter to > > >>> 32768 and disabled Hyper Threading in BIOS. But my CPU load is still > > >>> very important. > > >> What are your settings for commit_siblings and commit_delay? > > > It default : > > > > > > #commit_delay = 01 # range 0-10, inmicroseconds > > > #commit_siblings = 5 # range 1-1000 > > > > You should uncomment them, and play with different settings. I'd try a > > commit_delay of 100, and commit_siblings of 5 to start with. > > > > > I plan to return to previous version : 7.4.6 in and i will reinstall all > > > in a dedicated server in order to reproduce and solve the problem. > > > > You should use at least 7.4.13 as it fixes some critical buts that were > > in 7.4.6. They use the same on-disk format and query planner logic, so > > they should not have any difference. > > > > I don't have much more ideas what the problem could be. > > > > Can you try to do some profiling (e. G. with statement logging) to see > > what specific statements are the one that cause high cpu load? > > > > Are there other differences (besides the PostgreSQL version) between the > > two installations? (Kernel, libraries, other software...) > nothing. > > I returned to the previous version 7.4.6 in my production server, it's > work fine ! > > And I plan to reproduce this problem in a dedicated server, and i will > send all informations in this list in the next week. > > I hope your help for solve this problem. > > Cheers, > Jérôme. > > > HTH, > > Markus signature.asc Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi, Markus,
Le mardi 19 septembre 2006 à 15:09 +0200, Markus Schaber a écrit :
> Hi, Jerome,
>
> Jérôme BENOIS wrote:
>
> >>> Now i Have 335 concurrent connections, i decreased work_mem parameter to
> >>> 32768 and disabled Hyper Threading in BIOS. But my CPU load is still
> >>> very important.
> >> What are your settings for commit_siblings and commit_delay?
> > It default :
> >
> > #commit_delay = 01 # range 0-10, inmicroseconds
> > #commit_siblings = 5 # range 1-1000
>
> You should uncomment them, and play with different settings. I'd try a
> commit_delay of 100, and commit_siblings of 5 to start with.
>
> > I plan to return to previous version : 7.4.6 in and i will reinstall all
> > in a dedicated server in order to reproduce and solve the problem.
>
> You should use at least 7.4.13 as it fixes some critical buts that were
> in 7.4.6. They use the same on-disk format and query planner logic, so
> they should not have any difference.
>
> I don't have much more ideas what the problem could be.
>
> Can you try to do some profiling (e. G. with statement logging) to see
> what specific statements are the one that cause high cpu load?
>
> Are there other differences (besides the PostgreSQL version) between the
> two installations? (Kernel, libraries, other software...)
nothing.
I returned to the previous version 7.4.6 in my production server, it's
work fine !
And I plan to reproduce this problem in a dedicated server, and i will
send all informations in this list in the next week.
I hope your help for solve this problem.
Cheers,
Jérôme.
> HTH,
> Markus
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi, Jerome, Jérôme BENOIS wrote: >>> Now i Have 335 concurrent connections, i decreased work_mem parameter to >>> 32768 and disabled Hyper Threading in BIOS. But my CPU load is still >>> very important. >> What are your settings for commit_siblings and commit_delay? > It default : > > #commit_delay = 01 # range 0-10, inmicroseconds > #commit_siblings = 5 # range 1-1000 You should uncomment them, and play with different settings. I'd try a commit_delay of 100, and commit_siblings of 5 to start with. > I plan to return to previous version : 7.4.6 in and i will reinstall all > in a dedicated server in order to reproduce and solve the problem. You should use at least 7.4.13 as it fixes some critical buts that were in 7.4.6. They use the same on-disk format and query planner logic, so they should not have any difference. I don't have much more ideas what the problem could be. Can you try to do some profiling (e. G. with statement logging) to see what specific statements are the one that cause high cpu load? Are there other differences (besides the PostgreSQL version) between the two installations? (Kernel, libraries, other software...) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High CPU Load
Markus,
Le mardi 19 septembre 2006 à 11:53 +0200, Markus Schaber a écrit :
> Hi, Jerome,
>
> Jérôme BENOIS wrote:
>
> > Now i Have 335 concurrent connections, i decreased work_mem parameter to
> > 32768 and disabled Hyper Threading in BIOS. But my CPU load is still
> > very important.
>
> What are your settings for commit_siblings and commit_delay?
It default :
#commit_delay = 01 # range 0-10, inmicroseconds
#commit_siblings = 5 # range 1-1000
> > Tomorrow morning i plan to add 2Giga RAM ... But I don't understand why
> > my database server worked good with previous version of postgres and
> > same queries ...
>
> I don't think any more that it's the RAM, as you told you don't go into
> swap. It has to be something else.
Yes, i agree with you.
>
> Could you try logging which are the problematic queries, maybe they have
> bad plans for whatever reason.
>
> > I used already database pool on my application and when i decrease
> > number of connection my application is more slow ;-(
>
> Could you just make sure that the pool really uses persistent
> connections, and is not broken or misconfigured, always reconnect?
Yes it's persistent.
I plan to return to previous version : 7.4.6 in and i will reinstall all
in a dedicated server in order to reproduce and solve the problem.
Jérôme.
> HTH,
> Markus
>
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi, Jerome, Jérôme BENOIS wrote: > Now i Have 335 concurrent connections, i decreased work_mem parameter to > 32768 and disabled Hyper Threading in BIOS. But my CPU load is still > very important. What are your settings for commit_siblings and commit_delay? > Tomorrow morning i plan to add 2Giga RAM ... But I don't understand why > my database server worked good with previous version of postgres and > same queries ... I don't think any more that it's the RAM, as you told you don't go into swap. It has to be something else. Could you try logging which are the problematic queries, maybe they have bad plans for whatever reason. > I used already database pool on my application and when i decrease > number of connection my application is more slow ;-( Could you just make sure that the pool really uses persistent connections, and is not broken or misconfigured, always reconnect? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] High CPU Load
On 9/18/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote: Tomorrow morning i plan to add 2Go RAM in order to test difference with my actual config. I don't think more RAM will change anything if you don't swap at all. You can try to set shared_buffers lower (try 32768 and 16384) but I don't think it will change anything in 8.1. The only thing left IMHO is that 8.1 is choosing a bad plan which consumes a lot of CPU for at least a query. When you analyze your logs, did you see a particularly slow query? Can you compare query log analysis from your old server and your new one? -- Guillaume ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High CPU Load
Hi Markus,
Le vendredi 15 septembre 2006 à 11:43 +0200, Markus Schaber a écrit :
> Hi, Jérôme,
>
> Jérôme BENOIS wrote:
>
> > max_connections = 512
>
> Do you really have that much concurrent connections? Then you should
> think about getting a larger machine, probably.
>
> You will definitely want to play with commit_delay and commit_siblings
> settings in that case, especially if you have write access.
>
> > work_mem = 65536
> > effective_cache_size = 131072
>
> hmm, 131072*8*1024 + 512*65536*1024 = 35433480192 - thats 33 Gig of
> Memory you assume here, not counting OS usage, and the fact that certain
> queries can use up a multiple of work_mem.
Now i Have 335 concurrent connections, i decreased work_mem parameter to
32768 and disabled Hyper Threading in BIOS. But my CPU load is still
very important.
Tomorrow morning i plan to add 2Giga RAM ... But I don't understand why
my database server worked good with previous version of postgres and
same queries ...
> Even on amachine that big, I'd be inclined to dedicate more memory to
> caching, and less to the backends, unless specific needs dictate it. You
> could try to use sqlrelay or pgpool to cut down the number of backends
> you need.
I used already database pool on my application and when i decrease
number of connection my application is more slow ;-(
>
> > My Server is Dual Xeon 3.06GHz
>
> For xeons, there were rumours about "context switch storms" which kill
> performance.
I disabled Hyper Threading.
> > with 2 Go RAM and good SCSI disks.
>
> For 2 Gigs of ram, you should cut down the number of concurrent backends.
>
> Does your machine go into swap?
No, 0 swap found and i cannot found pgsql_tmp files in $PG_DATA/base/...
>
> Markus
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi Guillaume,
Now i disable Hyper Threading in BIOS, and "context switch storms"
disappeared. (when i look with command sar -t)
I decreased work_mem parameter to 32768. My CPU load is better. But it
is still too high, in example :
top - 16:27:05 up 9:13, 3 users, load average: 45.37, 43.43, 41.43
Tasks: 390 total, 26 running, 363 sleeping, 0 stopped, 1 zombie
Cpu(s): 89.5% us, 9.8% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.2% hi,
0.4% si
Mem: 2076404k total, 2039552k used,36852k free,40412k buffers
Swap: 1954312k total, 468k used, 1953844k free, 1232000k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
30907 postgres 16 0 537m 51m 532m R 20.4 2.5 1:44.73 postmaster
25631 postgres 16 0 538m 165m 532m R 17.4 8.2 8:43.76 postmaster
29357 postgres 16 0 537m 311m 532m R 17.4 15.3 0:26.47 postmaster
32294 postgres 16 0 535m 86m 532m R 14.9 4.3 0:04.97 postmaster
31406 postgres 16 0 536m 180m 532m R 14.4 8.9 0:22.04 postmaster
31991 postgres 16 0 535m 73m 532m R 14.4 3.6 0:08.21 postmaster
30782 postgres 16 0 536m 205m 532m R 14.0 10.1 0:19.63 postmaster
Tomorrow morning i plan to add 2Go RAM in order to test difference with
my actual config.
Have you another ideas ?
Best Regards,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
Le vendredi 15 septembre 2006 à 00:24 +0200, Guillaume Smet a écrit :
> On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote:
> > Yes i have a lot of users ;-)
>
> So your work_mem is probably far too high (that's what I told you in
> my first message) and you probably swap when you have too many users.
> Remember that work_mem can be used several times per query (and it's
> especially the case when you have a lot of sorts).
> When your load is high, check your swap activity and your io/wait. top
> gives you these information. If you swap, lower your work_mem to 32 MB
> for example then see if it's enough for your queries to run fast (you
> can check if there are files created in the $PGDATA/base/ database oid>/pg_tmp) and if it doesn't swap. Retry with a
> lower/higher value to find the one that fits best to your queries and
> load.
>
> > I agree but by moment DB Server is so slow.
>
> Yep, that's the information that was missing :).
>
> > what's means "HT" please ?
>
> Hyper threading. It's usually not recommended to enable it on
> PostgreSQL servers. On most servers, you can disable it directly in
> the BIOS.
>
> --
> Guillaume
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Jérôme, How many concurrent connections do you have? Because You've got only 2GB of ram this is important! Postgres process takes some bytes in memory =) .. I don't exactly how many, but thinking if it is about 2Mb you'll get about 1Gb of ram used only by postgres' processes (for 512 connections)! Don't forget about your 512Mb shared memory setting, postgres shared libraries and the OS filesystem cache... I hope your postgres binaries are not statically linked? Try using connection pooling in your software, or add some RAM, it's cheap. And I think that work_mem of 65536 is too high for your system... On Thu, 14 Sep 2006 17:09:25 +0200 Jérôme BENOIS <[EMAIL PROTECTED]> wrote: > Hi Dave, > Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit : > > > -Original Message- > > > From: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED] On Behalf Of > > > Jérôme BENOIS > > > > > explain analyze select distinct > > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_ > > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from ((( > > > select distinct ei_id as EIID from mpng2_ei_attribute as > > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE > > > ilike '' and ei_id in ( select distinct ei_id as EIID from > > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0 > > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct > > > ei_id as EIID from mpng2_ei_attribute as reqin3 where > > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as > > > req0 join mpng2_ei_attribute on req0.eiid = > > > mpng2_ei_attribute.ei_id order by ei_id asc; > > > > > > That is a lot of distinct's. Sorts are one thing that can really use up > > CPU. This query is doing lots of sorts, so its not surprising the CPU usage > > is high. > > > > On the subqueries you have a couple of cases where you say "... in (select > > distinct ...)" I don’t think the distinct clause is necessary in that case. > > I'm not a hundred percent sure, but you might want to try removing them and > > see if the query results are the same and maybe the query will execute > > faster. > > Thanks for your advice, but the load was good with previous version of > postgres -> 7.4.6 on the same server and same datas, same application, > same final users ... > > So we supect some system parameter, but which ? > > With vmstat -s is showing a lot of "pages swapped out", have you an > idea ? > > Thanls a lot, -- Evgeny Gridasov Software Engineer I-Free, Russia ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] High CPU Load
On 9/15/06, Markus Schaber <[EMAIL PROTECTED]> wrote: For xeons, there were rumours about "context switch storms" which kill performance. It's not that much a problem in 8.1. There are a few corner cases when you still have the problem but on a regular load you don't have it anymore (validated here with a quad Xeon MP and a dual Xeon). -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] High CPU Load
Hi, Jérôme, Jérôme BENOIS wrote: > max_connections = 512 Do you really have that much concurrent connections? Then you should think about getting a larger machine, probably. You will definitely want to play with commit_delay and commit_siblings settings in that case, especially if you have write access. > work_mem = 65536 > effective_cache_size = 131072 hmm, 131072*8*1024 + 512*65536*1024 = 35433480192 - thats 33 Gig of Memory you assume here, not counting OS usage, and the fact that certain queries can use up a multiple of work_mem. Even on amachine that big, I'd be inclined to dedicate more memory to caching, and less to the backends, unless specific needs dictate it. You could try to use sqlrelay or pgpool to cut down the number of backends you need. > My Server is Dual Xeon 3.06GHz For xeons, there were rumours about "context switch storms" which kill performance. > with 2 Go RAM and good SCSI disks. For 2 Gigs of ram, you should cut down the number of concurrent backends. Does your machine go into swap? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] High CPU Load
>Hyper threading. It's usually not recommended to enable it on >PostgreSQL servers. On most servers, you can disable it directly in >the BIOS. Maybe for specific usage scenarios, but that's generally not been my experience with relatively recent versions of PG. We ran some tests with pgbench, and averaged 10% or more performance improvement. Now, I agree pgbench isn't the most realistic performance, but we did notice a slight improvement in our application performance too. Also, here's some benchmarks that were posted earlier by the folks at tweakers.net also showing hyperthreading to be faster: http://tweakers.net/reviews/646/10 I'm not sure if it's dependent on OS- our tests were on BSD 5.x and PG 7.4 and 8.0/8.1 and were several months ago, so I don't remember many more specifics than that. So, not saying it's a best practice one way or another, but this is pretty easy to test and you should definitely try it out both ways for your workload. - Bucky ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] High CPU Load
On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote: Yes i have a lot of users ;-) So your work_mem is probably far too high (that's what I told you in my first message) and you probably swap when you have too many users. Remember that work_mem can be used several times per query (and it's especially the case when you have a lot of sorts). When your load is high, check your swap activity and your io/wait. top gives you these information. If you swap, lower your work_mem to 32 MB for example then see if it's enough for your queries to run fast (you can check if there are files created in the $PGDATA/base//pg_tmp) and if it doesn't swap. Retry with a lower/higher value to find the one that fits best to your queries and load. I agree but by moment DB Server is so slow. Yep, that's the information that was missing :). what's means "HT" please ? Hyper threading. It's usually not recommended to enable it on PostgreSQL servers. On most servers, you can disable it directly in the BIOS. -- Guillaume ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] High CPU Load
Hi Guillaume,
Le jeudi 14 septembre 2006 à 23:22 +0200, Guillaume Smet a écrit :
> Jérôme,
>
> Perhaps it's a stupid question but are your queries slower than
> before? You didn't tell it.
No, it's not stupid question !
Yes queries speed but when the load average exceeds 40 all queries are slower
than before.
> IMHO, it's not a problem to have a high load if you have a lot of
> users and your queries are fast (and with 8.1, they should be far
> faster than before).
Yes i have a lot of users ;-)
>
> To take a real example, we had a problem with a quad xeon running
> postgres 7.4 and even when there were a lot of queries, the load was
> always lower than 4 and suddenly the queries were really slow and the
> database was completely unusable.
> When we upgraded to 8.1, on very high load, we had a far higher cpu
> load but queries were far faster even with a high cpu load.
I agree but by moment DB Server is so slow.
> Considering your top output, I suspect you use HT and you should
> really remove it if it's the case.
what's means "HT" please ?
> --
> Guillaume
If you want, my JabberId : jerome.benois AT gmail.com
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi Evgeny,
Le jeudi 14 septembre 2006 à 20:47 +0400, Evgeny Gridasov a écrit :
> Jérôme,
>
> How many concurrent connections do you have?
I have between 300 and 400 concurrent connections.
> Because You've got only 2GB of ram this is important! Postgres process
> takes some bytes in memory =) .. I don't exactly how many,
> but thinking if it is about 2Mb you'll get about 1Gb of ram used only by
> postgres' processes (for 512 connections)!
> Don't forget about your 512Mb shared memory setting,
> postgres shared libraries and the OS filesystem cache...
>
> I hope your postgres binaries are not statically linked?
no, i not use static binaries
> Try using connection pooling in your software, or add some RAM, it's cheap.
> And I think that work_mem of 65536 is too high for your system...
I already use connection pool but i have many servers in front of database
server.
Ok i will test new lower work_mem tomorrow.
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
> On Thu, 14 Sep 2006 17:09:25 +0200
> Jérôme BENOIS <[EMAIL PROTECTED]> wrote:
>
> > Hi Dave,
> > Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit :
> > > > -Original Message-
> > > > From: [EMAIL PROTECTED]
> > > > [mailto:[EMAIL PROTECTED] On Behalf Of
> > > > Jérôme BENOIS
> > > >
> > > explain analyze select distinct
> > > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((
> > > > select distinct ei_id as EIID from mpng2_ei_attribute as
> > > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE
> > > > ilike '' and ei_id in ( select distinct ei_id as EIID from
> > > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0
> > > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct
> > > > ei_id as EIID from mpng2_ei_attribute as reqin3 where
> > > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as
> > > > req0 join mpng2_ei_attribute on req0.eiid =
> > > > mpng2_ei_attribute.ei_id order by ei_id asc;
> > >
> > >
> > > That is a lot of distinct's. Sorts are one thing that can really use up
> > > CPU. This query is doing lots of sorts, so its not surprising the CPU
> > > usage
> > > is high.
> > >
> > > On the subqueries you have a couple of cases where you say "... in (select
> > > distinct ...)" I don’t think the distinct clause is necessary in that
> > > case.
> > > I'm not a hundred percent sure, but you might want to try removing them
> > > and
> > > see if the query results are the same and maybe the query will execute
> > > faster.
> >
> > Thanks for your advice, but the load was good with previous version of
> > postgres -> 7.4.6 on the same server and same datas, same application,
> > same final users ...
> >
> > So we supect some system parameter, but which ?
> >
> > With vmstat -s is showing a lot of "pages swapped out", have you an
> > idea ?
> >
> > Thanls a lot,
>
>
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Jérôme, Perhaps it's a stupid question but are your queries slower than before? You didn't tell it. IMHO, it's not a problem to have a high load if you have a lot of users and your queries are fast (and with 8.1, they should be far faster than before). To take a real example, we had a problem with a quad xeon running postgres 7.4 and even when there were a lot of queries, the load was always lower than 4 and suddenly the queries were really slow and the database was completely unusable. When we upgraded to 8.1, on very high load, we had a far higher cpu load but queries were far faster even with a high cpu load. Considering your top output, I suspect you use HT and you should really remove it if it's the case. -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] High CPU Load
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <[EMAIL PROTECTED]> writes: > Le jeudi 14 septembre 2006 =C3=A0 10:56 -0500, Scott Marlowe a =C3=A9crit : >> I'm gonna make a SWAG here and guess that maybe your 7.4 db was initdb'd >> with a locale of C and the new one is initdb'd with a real locale, like >> en_US. Can J=C3=A9r=C3=B4me confirm or deny this? > The locale used to run initdb is : > su - postgres > :~$ locale > LANG=POSIX It'd be more convincing if "show lc_collate" etc. display C or POSIX. The fact that postgres' current default environment is LANG=POSIX doesn't prove much about what initdb saw. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] High CPU Load
Hi Scott,
Le jeudi 14 septembre 2006 à 10:56 -0500, Scott Marlowe a écrit :
> On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote:
> > > -Original Message-
> > > From: [EMAIL PROTECTED]
> > > [mailto:[EMAIL PROTECTED] On Behalf Of
> > > Jérôme BENOIS
> > >
> > explain analyze select distinct
> > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((
> > > select distinct ei_id as EIID from mpng2_ei_attribute as
> > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE
> > > ilike '' and ei_id in ( select distinct ei_id as EIID from
> > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0
> > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct
> > > ei_id as EIID from mpng2_ei_attribute as reqin3 where
> > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as
> > > req0 join mpng2_ei_attribute on req0.eiid =
> > > mpng2_ei_attribute.ei_id order by ei_id asc;
> >
> >
> > That is a lot of distinct's. Sorts are one thing that can really use up
> > CPU. This query is doing lots of sorts, so its not surprising the CPU usage
> > is high.
>
> I'm gonna make a SWAG here and guess that maybe your 7.4 db was initdb'd
> with a locale of C and the new one is initdb'd with a real locale, like
> en_US. Can Jérôme confirm or deny this?
>
The locale used to run initdb is :
su - postgres
:~$ locale
LANG=POSIX
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
Cheers,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
On Thu, 2006-09-14 at 10:02, Dave Dutcher wrote: > > -Original Message- > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On Behalf Of > > Jérôme BENOIS > > > explain analyze select distinct > > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_ > > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from ((( > > select distinct ei_id as EIID from mpng2_ei_attribute as > > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE > > ilike '' and ei_id in ( select distinct ei_id as EIID from > > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0 > > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct > > ei_id as EIID from mpng2_ei_attribute as reqin3 where > > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as > > req0 join mpng2_ei_attribute on req0.eiid = > > mpng2_ei_attribute.ei_id order by ei_id asc; > > > That is a lot of distinct's. Sorts are one thing that can really use up > CPU. This query is doing lots of sorts, so its not surprising the CPU usage > is high. I'm gonna make a SWAG here and guess that maybe your 7.4 db was initdb'd with a locale of C and the new one is initdb'd with a real locale, like en_US. Can Jérôme confirm or deny this? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] High CPU Load
Hi Dave,
Le jeudi 14 septembre 2006 à 10:02 -0500, Dave Dutcher a écrit :
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] On Behalf Of
> > Jérôme BENOIS
> >
> explain analyze select distinct
> > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_
> > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from (((
> > select distinct ei_id as EIID from mpng2_ei_attribute as
> > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE
> > ilike '' and ei_id in ( select distinct ei_id as EIID from
> > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0
> > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct
> > ei_id as EIID from mpng2_ei_attribute as reqin3 where
> > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as
> > req0 join mpng2_ei_attribute on req0.eiid =
> > mpng2_ei_attribute.ei_id order by ei_id asc;
>
>
> That is a lot of distinct's. Sorts are one thing that can really use up
> CPU. This query is doing lots of sorts, so its not surprising the CPU usage
> is high.
>
> On the subqueries you have a couple of cases where you say "... in (select
> distinct ...)" I don’t think the distinct clause is necessary in that case.
> I'm not a hundred percent sure, but you might want to try removing them and
> see if the query results are the same and maybe the query will execute
> faster.
Thanks for your advice, but the load was good with previous version of
postgres -> 7.4.6 on the same server and same datas, same application,
same final users ...
So we supect some system parameter, but which ?
With vmstat -s is showing a lot of "pages swapped out", have you an
idea ?
Thanls a lot,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Jérôme BENOIS > explain analyze select distinct > INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_ > VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from ((( > select distinct ei_id as EIID from mpng2_ei_attribute as > reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE > ilike '' and ei_id in ( select distinct ei_id as EIID from > mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0 > AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct > ei_id as EIID from mpng2_ei_attribute as reqin3 where > reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as > req0 join mpng2_ei_attribute on req0.eiid = > mpng2_ei_attribute.ei_id order by ei_id asc; That is a lot of distinct's. Sorts are one thing that can really use up CPU. This query is doing lots of sorts, so its not surprising the CPU usage is high. On the subqueries you have a couple of cases where you say "... in (select distinct ...)" I dont think the distinct clause is necessary in that case. I'm not a hundred percent sure, but you might want to try removing them and see if the query results are the same and maybe the query will execute faster. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] High CPU Load
Hello, Le jeudi 14 septembre 2006 à 09:21 -0500, Scott Marlowe a écrit : > On Thu, 2006-09-14 at 09:17, Jérôme BENOIS wrote: > > Hi Tom, > > > > Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit : > > > =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <[EMAIL PROTECTED]> writes: > > > >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > > > > completely full, by moment load average > 40 > > > > > > Did you remember to ANALYZE the whole database after reloading it? > > > pg_dump/reload won't by itself regenerate statistics. > > > > > > regards, tom lane > > I tested, dump + restore + vaccumdb --analyze on all databases but no > > change ... > > > OK, set your db to log queries that take more than a few seconds to > run. Execute those queries by hand with an explain analyze in front and > post the output here. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend i tested all queries, but she used indexes ... an example : explain analyze select distinct INTEGER_VALUE,DATE_VALUE,EI_ID,VALUE_TYPE,FLOAT_VALUE,ID,TEXT_VALUE,CATEGORY_ID,STRING_VALUE,CATEGORYATTR_ID,NAME from ((( select distinct ei_id as EIID from mpng2_ei_attribute as reqin1 where reqin1.CATEGORYATTR_ID = 0 AND reqin1.TEXT_VALUE ilike '' and ei_id in ( select distinct ei_id as EIID from mpng2_ei_attribute as reqin2 where reqin2.CATEGORYATTR_ID = 0 AND reqin2.TEXT_VALUE ilike '' and ei_id in ( select distinct ei_id as EIID from mpng2_ei_attribute as reqin3 where reqin3.NAME = '' AND reqin3.STRING_VALUE = '' ) ) ) ) ) as req0 join mpng2_ei_attribute on req0.eiid = mpng2_ei_attribute.ei_id order by ei_id asc; QUERY PLAN Unique (cost=758.53..762.19 rows=122 width=233) (actual time=0.191..0.191 rows=0 loops=1) -> Sort (cost=758.53..758.84 rows=122 width=233) (actual time=0.182..0.182 rows=0 loops=1) Sort Key: mpng2_ei_attribute.ei_id, mpng2_ei_attribute.integer_value, mpng2_ei_attribute.date_value, mpng2_ei_attribute.value_type, mpng2_ei_attribute.float_value, mpng2_ei_attribute.id, mpng2_ei_attribute.text_value, mpng2_ei_attribute.category_id, mpng2_ei_attribute.string_value, mpng2_ei_attribute.categoryattr_id, mpng2_ei_attribute.name -> Nested Loop (cost=365.83..754.31 rows=122 width=233) (actual time=0.126..0.126 rows=0 loops=1) -> Unique (cost=365.83..374.34 rows=1 width=4) (actual time=0.116..0.116 rows=0 loops=1) -> Nested Loop (cost=365.83..374.34 rows=1 width=4) (actual time=0.108..0.108 rows=0 loops=1) -> Unique (cost=350.22..354.69 rows=1 width=4) (actual time=0.097..0.097 rows=0 loops=1) -> Nested Loop (cost=350.22..354.69 rows=1 width=4) (actual time=0.089..0.089 rows=0 loops=1) -> Unique (cost=334.60..335.03 rows=1 width=4) (actual time=0.080..0.080 rows=0 loops=1) -> Sort (cost=334.60..334.82 rows=86 width=4) (actual time=0.072..0.072 rows=0 loops=1) Sort Key: reqin3.ei_id -> Bitmap Heap Scan on mpng2_ei_attribute reqin3 (cost=2.52..331.84 rows=86 width=4) (actual time=0.056..0.056 rows=0 loops=1) Recheck Cond: (((name)::text = ''::text) AND ((string_value)::text = ''::text)) -> Bitmap Index Scan on mpng2_ei_attribute_name_svalue (cost=0.00..2.52 rows=86 width=0) (actual time=0.043..0.043 rows=0 loops=1) Index Cond: (((name)::text = ''::text) AND ((string_value)::text = ''::text)) -> Bitmap Heap Scan on mpng2_ei_attribute reqin2 (cost=15.61..19.63 rows=1 width=4) (never executed) Recheck Cond: ((reqin2.ei_id = "outer".ei_id) AND (reqin2.categoryattr_id = 0)) Filter: (text_value ~~* ''::text) -> BitmapAnd (cost=15.61..15.61 rows=1 width=0) (never executed) -> Bitmap Index Scan on mpng2_ei_attribute_ei_id (cost=0.00..2.43 rows=122 width=0) (never executed) Index Cond: (reqin2.ei_id = "outer".ei_id) -> Bitmap Index Scan on mpng2_ei_attribute_cat
Re: [PERFORM] High CPU Load
On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 15667 postgres 25 0 536m 222m 532m R 98.8 11.0 1:39.29 postmaster 19533 postgres 25 0 535m 169m 532m R 92.9 8.3 0:38.68 postmaster 16278 postgres 25 0 537m 285m 532m R 86.3 14.1 1:37.56 postmaster Enable stats_command_string and see which queries are running on these backends by selecting on pg_stat_activity. Do the queries finish? Do you have them in your query log? -- Guillaume ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] High CPU Load
On Thu, 2006-09-14 at 09:17, Jérôme BENOIS wrote: > Hi Tom, > > Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit : > > =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <[EMAIL PROTECTED]> writes: > > >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > > > completely full, by moment load average > 40 > > > > Did you remember to ANALYZE the whole database after reloading it? > > pg_dump/reload won't by itself regenerate statistics. > > > > regards, tom lane > I tested, dump + restore + vaccumdb --analyze on all databases but no change > ... OK, set your db to log queries that take more than a few seconds to run. Execute those queries by hand with an explain analyze in front and post the output here. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] High CPU Load
On Thu, 2006-09-14 at 09:00, Jérôme BENOIS wrote: > Hi Guillaume, > > Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit : > > On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote: > > >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > > > completely full, by moment load average > 40 > > > All queries analyzed by EXPLAIN, all indexes are used .. IO is > > > good ... > > > > What is the bottleneck? Are you CPU bound? Do you have iowait? Do you > > swap? Any weird things in vmstat output? > the load average goes up and goes down between 1 and 70, it's strange. > IO wait and swap are good. I have just very high CPU load. And it's user > land time. > > top output : > > top - 15:57:57 up 118 days, 9:04, 4 users, load average: 8.16, 9.16, > 15.51 > Tasks: 439 total, 7 running, 432 sleeping, 0 stopped, 0 zombie > Cpu(s): 87.3% us, 6.8% sy, 0.0% ni, 4.8% id, 0.1% wa, 0.2% hi, > 0.8% si > Mem: 2076404k total, 2067812k used, 8592k free,13304k buffers > Swap: 1954312k total, 236k used, 1954076k free, 1190296k cached > > PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 15667 postgres 25 0 536m 222m 532m R 98.8 11.0 1:39.29 postmaster > 19533 postgres 25 0 535m 169m 532m R 92.9 8.3 0:38.68 postmaster > 16278 postgres 25 0 537m 285m 532m R 86.3 14.1 1:37.56 postmaster > 18695 postgres 16 0 535m 171m 532m S 16.1 8.5 0:14.46 postmaster > 18092 postgres 16 0 544m 195m 532m R 11.5 9.7 0:31.87 postmaster > 16896 postgres 15 0 534m 215m 532m S 6.3 10.6 0:27.13 postmaster Somewhere, the query planner is likely making a really bad decision. Have you analyzed your dbs? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] High CPU Load
Hi Tom,
Le jeudi 14 septembre 2006 à 10:13 -0400, Tom Lane a écrit :
> =?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <[EMAIL PROTECTED]> writes:
> >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
> > completely full, by moment load average > 40
>
> Did you remember to ANALYZE the whole database after reloading it?
> pg_dump/reload won't by itself regenerate statistics.
>
> regards, tom lane
I tested, dump + restore + vaccumdb --analyze on all databases but no change ...
Cheers,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
signature.asc
Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
=?ISO-8859-1?Q?J=E9r=F4me?= BENOIS <[EMAIL PROTECTED]> writes: >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is > completely full, by moment load average > 40 Did you remember to ANALYZE the whole database after reloading it? pg_dump/reload won't by itself regenerate statistics. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] High CPU Load
Hi All, I migrated Postgres server from 7.4.6 to 8.1.4, But my server is completely full, by moment load average > 40 All queries analyzed by EXPLAIN, all indexes are used .. IO is good ... My configuration is correct ? - default configuration and se + somes updates : max_connections = 512 superuser_reserved_connections = 2 shared_buffers = 65536 work_mem = 65536 effective_cache_size = 131072 log_destination = 'syslog' redirect_stderr = off log_directory = '/var/log/pgsql' log_min_duration_statement = 100 silent_mode = on log_statement = 'none' default_with_oids = on My Server is Dual Xeon 3.06GHz with 2 Go RAM and good SCSI disks. Best Regards, Jérôme BENOIS. signature.asc Description: Ceci est une partie de message numériquement signée
Re: [PERFORM] High CPU Load
Hi Guillaume,
Le jeudi 14 septembre 2006 à 15:46 +0200, Guillaume Smet a écrit :
> On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote:
> >I migrated Postgres server from 7.4.6 to 8.1.4, But my server is
> > completely full, by moment load average > 40
> > All queries analyzed by EXPLAIN, all indexes are used .. IO is good
> > ...
>
> What is the bottleneck? Are you CPU bound? Do you have iowait? Do you
> swap? Any weird things in vmstat output?
the load average goes up and goes down between 1 and 70, it's strange.
IO wait and swap are good. I have just very high CPU load. And it's user
land time.
top output :
top - 15:57:57 up 118 days, 9:04, 4 users, load average: 8.16, 9.16,
15.51
Tasks: 439 total, 7 running, 432 sleeping, 0 stopped, 0 zombie
Cpu(s): 87.3% us, 6.8% sy, 0.0% ni, 4.8% id, 0.1% wa, 0.2% hi,
0.8% si
Mem: 2076404k total, 2067812k used, 8592k free,13304k buffers
Swap: 1954312k total, 236k used, 1954076k free, 1190296k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
15667 postgres 25 0 536m 222m 532m R 98.8 11.0 1:39.29 postmaster
19533 postgres 25 0 535m 169m 532m R 92.9 8.3 0:38.68 postmaster
16278 postgres 25 0 537m 285m 532m R 86.3 14.1 1:37.56 postmaster
18695 postgres 16 0 535m 171m 532m S 16.1 8.5 0:14.46 postmaster
18092 postgres 16 0 544m 195m 532m R 11.5 9.7 0:31.87 postmaster
16896 postgres 15 0 534m 215m 532m S 6.3 10.6 0:27.13 postmaster
4835 postgres 15 0 535m 147m 532m S 2.6 7.3 1:27.20 postmaster
4836 postgres 15 0 536m 154m 532m S 2.0 7.6 1:26.07 postmaster
4833 postgres 15 0 535m 153m 532m S 1.0 7.6 1:26.54 postmaster
4839 postgres 15 0 535m 148m 532m S 1.0 7.3 1:25.10 postmaster
15083 postgres 15 0 535m 44m 532m S 1.0 2.2 0:16.13 postmaster
Vmstat output :
procs ---memory-- ---swap-- -io --system--
cpu
r b swpd free buff cache si sobibo incs us sy
id wa
4 0236 13380 13876 119203600 0 01 1 19
6 70 5
4 0236 13252 13876 11920360010 00 0 92
8 0 0
16 0236 13764 13884 11920960052280 0 91
9 0 0
4 0236 11972 13904 119282400 320170 0 92
8 0 0
4 0236 12548 13904 11928920016 00 0 92
8 0 0
9 0236 11908 13912 119288400 4380 0 91
9 0 0
8 0236 8832 13568 119567600 6975 1400 0 91
9 0 0
8 0236 10236 13588 11932080082180 0 93
7 0 0
6 0236 9532 13600 11932640076180 0 92
8 0 0
10 1236 11060 13636 11934320054 1580 0 91
9 0 0
6 0236 10204 13636 119343200 8 00 0 92
8 0 0
8 1236 10972 13872 11927200028 3160 0 91
9 0 0
6 0236 11004 13936 119272400 4900 0 92
8 0 0
7 0236 10300 13936 119299600 150 00 0 92
8 0 0
11 0236 11004 13944 11929880016 60 0 91
8 0 0
17 0236 10732 13996 119320800 118940 0 91
9 0 0
6 0236 10796 13996 119382000 274 00 0 91
9 0 0
24 0236 9900 13996 119382000 8 00 0 92
8 0 0
13 0236 9420 14016 119400400 100980 0 92
8 0 0
8 0236 9276 13944 11889760042 00 0 92
8 0 0
3 0236 14524 13952 118896800 0380 0 77
8 16 0
3 0236 15164 13960 11891640092 60 0 65
7 28 0
3 0236 16380 13968 118915600 8360 0 57
7 36 0
1 0236 15604 14000 11892600038370 0 39
6 54 1
1 0236 16564 14000 118932800 0 00 0 38
5 57 0
1 1236 14900 14024 11893720028 1400 0 47
7 46 0
1 1236 10212 14100 119528000 2956 1220 0 21
3 71 5
5 0236 13156 13988 119240000 534 60 0 19
3 77 1
0 0236 8408 13996 119701600 4458 2000 0 18
2 78 2
1 0236 9784 13996 11955880082 00 0 16
3 81 0
0 0236 10728 14028 11955560030 1180 0 11
2 87 1
Thanks for your help,
--
Jérôme,
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in '[EMAIL PROTECTED]'.split('@')])"
> > My configuration is correct ?
> > work_mem = 65536
>
> If you have a lot of concurrent queries, it's probably far too much.
> That said, if you don't swap, it's probably not the problem.
>
> --
> Guillaume
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire
Re: [PERFORM] High CPU Load
On 9/14/06, Jérôme BENOIS <[EMAIL PROTECTED]> wrote: I migrated Postgres server from 7.4.6 to 8.1.4, But my server is completely full, by moment load average > 40 All queries analyzed by EXPLAIN, all indexes are used .. IO is good ... What is the bottleneck? Are you CPU bound? Do you have iowait? Do you swap? Any weird things in vmstat output? My configuration is correct ? work_mem = 65536 If you have a lot of concurrent queries, it's probably far too much. That said, if you don't swap, it's probably not the problem. -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
