pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why would writes to pgsql_tmp bottleneck at 1mb/s?
8+ messages / 3 participants
[nested] [flat]

* Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-08 20:18  Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2005-03-08 20:18 UTC (permalink / raw)
  To: pgsql-performance

PG Hackers,

What follows is iostat output from a TPC-H test on Solaris 10.    The machine 
is creating indexes on a table which is 50G in size, so it needs to use 
pgsql_tmp for internal swapping:

   tty        md15          sd1           sd2           sd3            cpu
 tin tout kps tps serv  kps tps serv  kps tps serv  kps tps serv   us sy wt id
   0   84 22526 1211    1  1024   1    5    0   0    0  5634 337    1   30  8  
0 61
   0  242 24004 1337    1  1024   1    5    0   0    0  6007 355    1   33  8  
0 59
   0   85 22687 1277    1  1024   1    5    0   0    0  5656 322    1   31  8  
0 62
   0   85 20876 1099    1  1024   2    9    0   0    0  5185 292    1   28  7  
0 64

md15 is WAL (pg_xlog).   
sd3 is PGDATA.   
sd1 i pgsql_tmp.

As you can see, we're getting a nice 23mb/s peak for WAL (thanks to 
forcedirectio) and database writes peak at 6mb/s.  However, pgsql_tmp, which 
is being used heavily, hovers around 1mb/s, and never goes above 1.5mb/s.   
This seems to be throttling the whole system.

Any suggestions on why this should be?   Do we have a performance bug in the 
pg_tmp code?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 00:17  Josh Berkus <josh@agliodbs.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2005-03-09 00:17 UTC (permalink / raw)
  To: pgsql-performance

People:

> As you can see, we're getting a nice 23mb/s peak for WAL (thanks to
> forcedirectio) and database writes peak at 6mb/s.  However, pgsql_tmp,
> which is being used heavily, hovers around 1mb/s, and never goes above
> 1.5mb/s. This seems to be throttling the whole system.

Never mind, I'm a dork.    I accidentally cut the "SET maintenance_work_mem = 
2000000" out of my config file, and it was running with the default 
1024K ....

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 00:44  Josh Berkus <josh@agliodbs.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Josh Berkus @ 2005-03-09 00:44 UTC (permalink / raw)
  To: pgsql-performance

Hmmmm.....

> > As you can see, we're getting a nice 23mb/s peak for WAL (thanks to
> > forcedirectio) and database writes peak at 6mb/s.  However, pgsql_tmp,
> > which is being used heavily, hovers around 1mb/s, and never goes above
> > 1.5mb/s. This seems to be throttling the whole system.
>
> Never mind, I'm a dork.    I accidentally cut the "SET maintenance_work_mem
> = 2000000" out of my config file, and it was running with the default 1024K

Maybe I'm not an idiot (really!)  even with almost 2GB of maintenance_mem, PG 
still writes to pgsql_tmp no faster than 2MB/s.    I think there may be an 
artificial bottleneck there.  Question is, PostgreSQL, OS or hardware?

Suggestions?

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 01:13  Aaron Birkland <birkie@gmail.com>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Aaron Birkland @ 2005-03-09 01:13 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

> Maybe I'm not an idiot (really!)  even with almost 2GB of maintenance_mem, PG
> still writes to pgsql_tmp no faster than 2MB/s.    I think there may be an
> artificial bottleneck there.  Question is, PostgreSQL, OS or hardware?

I'm curious: what is your cpu usage while this is happening?  I've
noticed similar slow index creation behaviour, but I did not make any
connection to pgsql_temp (because it was not on a separate partition).
 I was indexing an oid field of a 700GB table and it took about four
days on a 1.2GHz UltraSparcIII (solaris 9, 8GB core).  I noticed that
the one CPU that was  pegged at near 100%, leading me to believe it
was CPU bound.  Odd thing is that the same operation on a 2GHz Pentium
IV box (Linux) on the same data took about a day.    Truss showed that
a great majority of that time was in userland.

    -Aaron



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 04:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-03-09 04:27 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
> Maybe I'm not an idiot (really!)  even with almost 2GB of maintenance_mem, PG
> still writes to pgsql_tmp no faster than 2MB/s.    I think there may be an 
> artificial bottleneck there.  Question is, PostgreSQL, OS or hardware?

AFAIR that's just fwrite() ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 04:37  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2005-03-09 04:37 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> > Maybe I'm not an idiot (really!)  even with almost 2GB of
> > maintenance_mem, PG still writes to pgsql_tmp no faster than 2MB/s.    I
> > think there may be an artificial bottleneck there.  Question is,
> > PostgreSQL, OS or hardware?
>
> AFAIR that's just fwrite() ...

Well, are there any hacks to speed it up?   It's about doubling the amount of 
time it takes to create an index on a very large table.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 04:37  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2005-03-09 04:37 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> AFAIR that's just fwrite() ...

> Well, are there any hacks to speed it up?   It's about doubling the amount of
> time it takes to create an index on a very large table.

Huh?  Doubled compared to what?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
@ 2005-03-09 07:00  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Josh Berkus @ 2005-03-09 07:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

> Huh?  Doubled compared to what?

Compared to how much data writing I can do to the database when pgsql_tmp 
isn't engaged.

In other words, when pgsql_tmp isn't being written, database writing is 9mb/s.    
When pgsql_tmp gets engaged, that drops to 4mb/s.

Alternatively, the WAL drive, which is the same hardware, will write at 
10mb/s.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-03-09 07:00 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-08 20:18 Why would writes to pgsql_tmp bottleneck at 1mb/s? Josh Berkus <josh@agliodbs.com>
2005-03-09 00:17 ` Josh Berkus <josh@agliodbs.com>
2005-03-09 00:44   ` Josh Berkus <josh@agliodbs.com>
2005-03-09 01:13     ` Aaron Birkland <birkie@gmail.com>
2005-03-09 04:27     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-09 04:37       ` Josh Berkus <josh@agliodbs.com>
2005-03-09 04:37         ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-09 07:00           ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox