pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy 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