Обсуждение: Low CPU Usage

brauagustin-susc@yahoo.com.ar wrote:
> Recently I have installed a brand new server with a Pentium IV 3.2 GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with
PostgreSQL8.2.4 (previously a 8.1.9). 
> I have other similar server with an IDE disk, Red Hat EL 4 and PostgreSQL 8.2.3
>
> I have almost the same postgresql.conf in both servers, but in the new one (I have more work_mem than the other one)
thingsgo really slow.  I began to monitor i/o disk and it's really ok, I have test disk with hdparm and it's 5 times
fasterthan the IDE one. 
> Running the same queries in both servers in the new one it envolves almost 4 minutes instead of 18 seconds in the old
one.
> Both databases are the same, I have vacuum them and I don't know how to manage this issue.

Have you ANALYZEd all tables?

Try running EXPLAIN ANALYZE on the query on both servers, and see if
they're using the same plan.

> The only weird thing is than in the older server running the query it uses 30% of CPU instead of 3 o 5 % of the new
one!!!

That implies that it's doing much more I/O on the new server, so the CPU
just sits and waits for data to arrive from the disk.

That does iostat say about disk utilization on both servers?

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

On Wed, 2007-09-19 at 10:38 -0700, brauagustin-susc@yahoo.com.ar wrote:
> Hi all.
> Recently I have installed a brand new server with a Pentium IV 3.2
> GHz, SATA Disk, 2GB of Ram in Debian 4.0r1 with PostgreSQL 8.2.4
> (previously a 8.1.9).
> I have other similar server with an IDE disk, Red Hat EL 4 and
> PostgreSQL 8.2.3
>
> I have almost the same postgresql.conf in both servers, but in the new
> one (I have more work_mem than the other one) things go really slow.
> I began to monitor i/o disk and it's really ok, I have test disk with
> hdparm and it's 5 times faster than the IDE one.
> Running the same queries in both servers in the new one it envolves
> almost 4 minutes instead of 18 seconds in the old one.
> Both databases are the same, I have vacuum them and I don't know how
> to manage this issue.
> The only weird thing is than in the older server running the query it
> uses 30% of CPU instead of 3 o 5 % of the new one!!!
> What's is happening with this server? I upgrade from 8.1.9 to 8.2.4
> trying to solve this issue but I can't find a solution.
>
> Any ideas?

It could be a planning issue.  Have you analyzed the new database to
gather up-to-date statistics?  A comparison of EXPLAIN ANALYZE results
for an example query in both systems should answer that one.

Another possibility because you're dealing with lower-end drives is that
you have a case of one drive lying about fsync where the other is not.
If possible, try running your test with fsync=off on both servers.  If
there's a marked improvement on the new server but no significant change
on the old server then you've found your culprit.

-- Mark Lewis

On 9/19/07, brauagustin-susc@yahoo.com.ar <brauagustin-susc@yahoo.com.ar> wrote:
>
>
> No, changing to fsync off didn't improve performance at all.
>
> Settings
> work_mem = 64MB
> max_stack_depth = 7MB #in the old server is 8MB but if I set in here give me
> the ulimit error
> max_fsm_pages = 204800
> effective_cache_size = 512MB
> Atuvacuum is off.
>
> I have run vacuum full and vacuum analyze. The databases are freezed (there
> are no insert, update or delete operations)!!!
> In both servers the query plans are identical with the same costs too!!!
>
> It's really weird, I don't see high values monitoring disk. Cpu usage is
> about 5% and sometimes a tips of 40 % of disk usage when the query is
> finishing (the first 3 minutes there are some tips of 11 to 16%).

(please don't top post)

This sounds like you've got problems somewhere in your I/O system.
hdparm etc may or may not be seeing the issue.  What do you see in
/var/log/messages or dmesg?

On 9/19/07, brauagustin-susc@yahoo.com.ar <brauagustin-susc@yahoo.com.ar> wrote:
>
> This is my dmesg file, I see there are some errors but I don't know how to
> manage!!!

nothing too horrible.  Just wanted to make sure you weren't getting
lots of bad sectors or timeouts.

Nothing too bad looking there.

> What do you mean with don't top post?

It means when you put your post at the top of mine.  The way I'm
answering provides context.  I.e. this paragraph I'm writing goes with
the paragraph you wrote that I'm answering.  It makes it easier to
keep track of long busy conversations.

> Sorry but I'm new with this kind of mailing list and I don't want to botter
> some others.
> Sorry my bad English too.

No bother, and your english is way better than my Spanish.  or
Russian, or Japanese, etc...

brauagustin-susc@yahoo.com.ar wrote:
> Hola Beto.
> I have no idea where to look for that configuration or settings.

In postgreSQL, the main settings are in .../pgsql/data/postgresql.conf

> Yesterday I red about some drivers problems with SATA disk working
> togheter with IDE devices with DMA.
>
> Mi server server is a Pentium VI 3.3 with hyper threading (enabled in
> BIOS), HP Proliant ML 110.
>
> Then I entered to the BIOS and saw in IDE Configuration:
>   ATA/IDE Configuration                    [Enhanced]
>           Configure SATA as                   [IDE]  => it has RAID
> option too
>
> I have any idea how to continue!!! I don't know if this a SATA problem,
> a configuration problem or what else. I have installed several servers
> beggining with postgres 6.4 and I've neved had this kind of problems
> (always with IDE disks). I think this is a problem with SATA disk i/o,
> but I don't see how to measure that (I have already set postgresql.conf).

Are you sure you are really having a problem with insufficient CPU time
being devoted to your program(s)? When I run postgreSQL and do the initial
populating of my database, which takes several hours due to the nature of
the input data, it runs just 25% to 50% of one CPU, even though I have two
3.06 GHz hyperthreaded Xeon processors and six 10,000 rpm Ultra/320 SCSI
hard drives on two SCSI controllers. If I look at the results of the Linux
top command, and iostat and vmstat, I see that I am in io-wait state 100% of
the time. The transfer rate to the hard drives averages about 2
Megabytes/second even though I have seen 90 Megabytes/second at times (when
doing a database restore). So the IO system can be quite fast when it is not
waiting (for seeks, no doubt). If the postgreSQL processes wanted more CPU
time, they could have it as the machine does not do much else most of the
time. Actually, it runs a four BOINC processes, but they run at nice level
19, so they run only if no other process wants processing time. When I do a
database backup, it will run more than 100% of a CPU (remember I have two or
four processors, depending on how you count them) for extended periods, so
the OS is certainly capable of supplying CPU power when I need it. And
postgreSQL runs multiple processes at once, so in theory, they could gert
400% of a processor if they needed it. They do not seem to need to do this
for me.
>
> Regards
> Agustin
>
>
> ----- Mensaje original ----
> De: Norberto Meijome <beto@octantis.com.au>
> Para: brauagustin-susc@yahoo.com.ar
> CC: pgsql-performance@postgresql.org
> Enviado: jueves 20 de septiembre de 2007, 7:53:05
> Asunto: Re: [PERFORM] Low CPU Usage
>
> On Wed, 19 Sep 2007 12:13:33 -0700 (PDT)
> brauagustin-susc@yahoo.com.ar wrote:
>
>> max_stack_depth = 7MB #in the old server is 8MB but if I set in here
> give me the ulimit error
>
> Hola Agustin :)
> otro argentino en el extranjero x aca ;)
>
> anyway, back to English ;)
>
> a long shot but...
>
> check if you have any limits set on the host for CPU usage... you may be
> limited to x number of secs / % by the OS scheduler. When you query your
> CPU,
> it will say u are only using 5% or so...
>


--
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 08:15:01 up 6 days, 42 min, 1 user, load average: 4.24, 4.25, 4.14

>>> On Fri, Sep 21, 2007 at 12:30 PM, in message
<121711.47531.qm@web30006.mail.mud.yahoo.com>, <brauagustin-susc@yahoo.com.ar>
wrote:

> This is the plan for the old server:
> Hash Join  (cost=449.55..8879.24 rows=136316 width=904) (actual
> time=50.734..1632.491 rows=136316 loops=1)
. . .
> Total runtime: 2022.293 ms

> And this is the plan for the new server:
> Hash Join  (cost=412.86..9524.13 rows=136316 width=905) (actual
> time=9.421..506.376 rows=136316 loops=1)
. . .
> Total runtime: 553.619 ms

> I see an "outer" join in the plan for the new server. This is weird!!! There
> are the same databases in both servers.

That's just a matter of labeling the tables with role rather than alias.
The plans look the same to me.

> The old one runs this query for about 37 seconds and for the new one for
> about 301 seconds.

That's not what it looks like based on the EXPLAIN ANALYZE output.
It looks like run time dropped from two seconds to half a second.

It seems as though you either have a network delay delivering the results,
or your application is slow to read them.

Exactly how are you arriving at those timings you're reporting to us?

-Kevin

In response to brauagustin-susc@yahoo.com.ar:

> > That's not what it looks like based on the EXPLAIN ANALYZE output.
> > It looks like run time dropped from two seconds to half a second.
>
> > It seems as though you either have a network delay delivering the results,
> > or your application is slow to read them.
>
> > Exactly how are you arriving at those timings you're reporting to us?
>
> I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40.
>
> Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big
tablesand then compare disk access. 
> SELECT *
> FROM fact_ven_renta fvr, dim_producto_std_producto dpp
> WHERE
>   fvr.producto_std_producto_sk = dpp.producto_sk
>
> fact_ven_renta has 136316 rows
> dim_producto_std_producto has 3669 rows

Run the tests from psql on the same server.  As Kevin pointed out, the
_server_ is faster, but it appears as if the connection between PGadmin
and this new server is slower somehow.

Are you sure of your speed/duplex settings on the network side?  That's
the most common cause of this kind of thing in my experience.  Try doing
a raw FTP transfer between the client and server and see if you get the
speed you should.

>
>
>
> I have made all possible combinations pgadmin (running in the same server each query, in the old one, in the new
one),without difference  and I only retrieve the first 100 records (I didn't count the network time in any case). 
> But the weird thing is running the query in the new server the are many disk access and cpu usage. And with other
applicationsin the same server are a lot of disks access. 



--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

In response to brauagustin-susc@yahoo.com.ar:

> >> > That's not what it looks like based on the EXPLAIN ANALYZE output.
> >> > It looks like run time dropped from two seconds to half a second.
> >>
> >> > It seems as though you either have a network delay delivering the results,
> >> > or your application is slow to read them.
> >>
> >> > Exactly how are you arriving at those timings you're reporting to us?
> >>
> >> I have noticed this in a daly process I run which involves normally 45 minutes and with the new server takes 1:40.
> >>
> >> Some days ago I began to do some tests with no success, then I opened PgAdmin with this simply query to read 2 big
tablesand then compare disk access. 
> >> SELECT *
> >> FROM fact_ven_renta fvr, dim_producto_std_producto dpp
> >> WHERE
> >>   fvr.producto_std_producto_sk = dpp.producto_sk
> >>
> >> fact_ven_renta has 136316 rows
> >> dim_producto_std_producto has 3669 rows
>
> >Run the tests from psql on the same server.  As Kevin pointed out, the _server_ is faster, but it appears as if the
connectionbetween PGadmin and this new server is slower somehow. 
>
> It runs quickly!!! But I don't know how to compare because looks like it retrieve fields by demand, when I put
ctrl+end(go to the last record) it use a lot of CPU and disk, run quickly anyway. 

That's pretty odd.  If you use \timing in psql, you can get execution
time for each query, if it helps you track things down.

> Correct me if am I wrong but, executing PgAdmin in the same server there aren't networks delays!

Not network, no.  But the results of your explains seem to show that the
query is executing much faster on the new system than the old, so the
problem still becomes, "what is happening after the query completes that
is so slow?"  It's just that networking is ruled out.


--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

brauagustin-susc@yahoo.com.ar wrote:
>
> > That's pretty odd.  If you use \timing in psql, you can get execution
> > time for each query, if it helps you track things down.
>
> Yes, in the new server running with \timing it consumes 5.6 seconds
> and in the old server it consumes 25 seconds.
>
> > > Correct me if am I wrong but, executing PgAdmin in the same server
> there aren't networks delays!
>
> > Not network, no.  But the results of your explains seem to show that the
> > query is executing much faster on the new system than the old, so the
> > problem still becomes, "what is happening after the query completes that
> > is so slow?"  It's just that networking is ruled out.
>
> Is connected to full 100Mb, it transfers many things quick to clients.
> Is running Apache adn JBoss, transfer rate is good, I did scp to copy
> many archives and is as quick as the old server.
>
> I have no idea how to continue researching this problem. Now I'm going
> to do some networks tests.
>
>
See if this can give some help to you:
I was experienced some problems with networks with win98 and winXP
stations, the application was running with good performance almost of
the time,
but in suddenly the performance slow down. We noticed that the problem
was with the time to connect with the server, that was very slow.
The problem occurs also when the internet link down.
Well, I don't know why but when we exclude win98 stations from network,
the problem disappears.
I think that was some DNS problem (but not sure), because one time we
cleared nameserver clauses in the /etc/resolv.conf the performance
return to the normal.
But we reinstalled win98 machines with winXP too, so I don't know what
happened exactly.
The server OS was a Mandriva Linux running postgres ( 8.0, I guess) and
samba. Workstations connect via odbc (informing the IP of server or the
name to connect the problem persists).


--
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

>From:  brauagustin-susc@yahoo.com.ar
>Subject: Re: [PERFORM] Low CPU Usage
>
>I have no idea how to continue researching this problem. Now I'm going to
do some networks tests.


I would go back to the slow program and try to capture the slow queries in
the log file.  Once you have some queries which are running slow then you
can run EXPLAIN ANALYZE to see what the bottle neck is.

It seems like you've found pgAdmin is slow sending across the network, but
we don't know if that has anything to do with your original problems.

Just my 2 cents.

Dave

>>> On Fri, Sep 21, 2007 at  3:40 PM, in message <46F42C54.7070901@planit.com.br>,
"Luiz K. Matsumura" <luiz@planit.com.br> wrote:

> but in suddenly the performance slow down. We noticed that the problem
> was with the time to connect with the server, that was very slow.

> I think that was some DNS problem (but not sure), because one time we
> cleared nameserver clauses in the /etc/resolv.conf the performance
> return to the normal.

You may have it there.  In some versions of Java, on Windows, connection
times are horribly slow unless the machine's IP address has a reverse
DNS entry.  Perhaps the new machine lacks such an entry, or there's a
different version of Java in use?

-Kevin

Luiz K. Matsumura wrote:
>> Is connected to full 100Mb, it transfers many things quick to clients.
>> Is running Apache adn JBoss, transfer rate is good, I did scp to copy
>> many archives and is as quick as the old server.
>>
>> I have no idea how to continue researching this problem. Now I'm going
>> to do some networks tests.

Any chance this is your desktop machine, and you're also using it for audio?  Microsoft built in a feature (!) that
reducesnetwork speed by 90% when music is playing: 

  http://it.slashdot.org/article.pl?sid=07/08/26/1628200&from=rss

Craig

On Thu, 20 Sep 2007, brauagustin-susc@yahoo.com.ar wrote:

> Which other test can I do to find if this is a hardware, kernel o
> postgres issue?

The little test hdparm does is not exactly a robust hard drive benchmark.
If you want to rule out hard drive transfer speed issues, take at look at
the tests suggested at
http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm and
see how your results compare to the single SATA disk example I give there.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD