Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-20 Thread Jim C. Nasby
No, this is a single process. And there's known issues with context
storms on Xeons, so that might be what you're seeing.

On Tue, Apr 19, 2005 at 09:37:21PM -0700, Mischa Sandberg wrote:
 Quoting Tom Lane [EMAIL PROTECTED]: 
  
  Jim C. Nasby [EMAIL PROTECTED] writes: 
   A friend of mine has an application where he's copying in 4000 rows at a 
   time into a table that has about 4M rows. Each row is 40-50 bytes. This 
   is taking 25 seconds on a dual PIII-1GHz with 1G of RAM and a 2 disk 
   SATA mirror, running FBSD 4.10-stable. There's one index on the table. 
   
  If there's no hidden costs such as foreign key checks, that does seem 
  pretty dang slow. 
   
   What's really odd is that neither the CPU or the disk are being 
   hammered. The box appears to be pretty idle; the postgresql proces is 
   using 4-5% CPU. 
 --  
 This sounds EXACTLY like my problem, if you make the box to a Xeon 2.4GHz, 
 2GB 
 RAM ... with two SCSI drives (xlog and base); loading 10K rows of about 200 
 bytes each; takes about 20 secs at the best, and much longer at the worst. By 
 any chance does your friend have several client machines/processes trying to 
 mass-load rows at the same time? Or at least some other processes updating 
 that table in a bulkish way? What I get is low diskio, low cpu, even low 
 context-switches ... and I'm betting he should take a look at pg_locks. For 
 my 
 own problem, I gather that an exclusive lock is necessary while updating 
 indexes and heap, and the multiple processes doing the update can make that 
 pathological. 
  
 Anyway, have your friend check pg_locks. 
  
  
 Dreams come true, not free. -- S.Sondheim, ITW 
 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming, or what?

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-20 Thread Jim C. Nasby
No, he's using either COPY or \COPY.

On Wed, Apr 20, 2005 at 12:34:27AM -0400, Greg Stark wrote:
 Jim C. Nasby [EMAIL PROTECTED] writes:
 
  What's really odd is that neither the CPU or the disk are being
  hammered. The box appears to be pretty idle; the postgresql proces is
  using 4-5% CPU.
 
 Is he committing every row? In that case you would see fairly low i/o
 bandwidth usage because most of the time is being spent seeking and waiting
 for rotational latency.
 
 -- 
 greg
 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming, or what?

---(end of broadcast)---
TIP 8: explain analyze is your friend


[PERFORM] Slow copy with little CPU/disk usage

2005-04-19 Thread Jim C. Nasby
A friend of mine has an application where he's copying in 4000 rows at a
time into a table that has about 4M rows. Each row is 40-50 bytes. This
is taking 25 seconds on a dual PIII-1GHz with 1G of RAM and a 2 disk
SATA mirror, running FBSD 4.10-stable. There's one index on the table.

What's really odd is that neither the CPU or the disk are being
hammered. The box appears to be pretty idle; the postgresql proces is
using 4-5% CPU.

I seem to recall others running into this before, but I can't remember
what the issue was and I can't find it in the archives.

This is version 8.0, btw.
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: Where do you want to go today?
Linux: Where do you want to go tomorrow?
FreeBSD: Are you guys coming, or what?

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-19 Thread Tom Lane
Jim C. Nasby [EMAIL PROTECTED] writes:
 A friend of mine has an application where he's copying in 4000 rows at a
 time into a table that has about 4M rows. Each row is 40-50 bytes. This
 is taking 25 seconds on a dual PIII-1GHz with 1G of RAM and a 2 disk
 SATA mirror, running FBSD 4.10-stable. There's one index on the table.

If there's no hidden costs such as foreign key checks, that does seem
pretty dang slow.

 What's really odd is that neither the CPU or the disk are being
 hammered. The box appears to be pretty idle; the postgresql proces is
 using 4-5% CPU.

It's very hard to believe that *neither* disk nor CPU is maxed.
Can we see a reproducible test case, please?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-19 Thread Greg Stark
Jim C. Nasby [EMAIL PROTECTED] writes:

 What's really odd is that neither the CPU or the disk are being
 hammered. The box appears to be pretty idle; the postgresql proces is
 using 4-5% CPU.

Is he committing every row? In that case you would see fairly low i/o
bandwidth usage because most of the time is being spent seeking and waiting
for rotational latency.

-- 
greg


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PERFORM] Slow copy with little CPU/disk usage

2005-04-19 Thread Mischa Sandberg
Quoting Tom Lane [EMAIL PROTECTED]: 
 
 Jim C. Nasby [EMAIL PROTECTED] writes: 
  A friend of mine has an application where he's copying in 4000 rows at a 
  time into a table that has about 4M rows. Each row is 40-50 bytes. This 
  is taking 25 seconds on a dual PIII-1GHz with 1G of RAM and a 2 disk 
  SATA mirror, running FBSD 4.10-stable. There's one index on the table. 
  
 If there's no hidden costs such as foreign key checks, that does seem 
 pretty dang slow. 
  
  What's really odd is that neither the CPU or the disk are being 
  hammered. The box appears to be pretty idle; the postgresql proces is 
  using 4-5% CPU. 
--  
This sounds EXACTLY like my problem, if you make the box to a Xeon 2.4GHz, 2GB 
RAM ... with two SCSI drives (xlog and base); loading 10K rows of about 200 
bytes each; takes about 20 secs at the best, and much longer at the worst. By 
any chance does your friend have several client machines/processes trying to 
mass-load rows at the same time? Or at least some other processes updating 
that table in a bulkish way? What I get is low diskio, low cpu, even low 
context-switches ... and I'm betting he should take a look at pg_locks. For my 
own problem, I gather that an exclusive lock is necessary while updating 
indexes and heap, and the multiple processes doing the update can make that 
pathological. 
 
Anyway, have your friend check pg_locks. 
 
 
Dreams come true, not free. -- S.Sondheim, ITW 


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings