Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
Josh Berkus 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
Josh Berkus 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
H. > > 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 > = 200" 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 ---(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] Why would writes to pgsql_tmp bottleneck at 1mb/s?
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 = 200" out of my config file, and it was running with the default 1024K -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Why would writes to pgsql_tmp bottleneck at 1mb/s?
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: ttymd15 sd1 sd2 sd3cpu tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id 0 84 22526 12111 1024 150 00 5634 3371 30 8 0 61 0 242 24004 13371 1024 150 00 6007 3551 33 8 0 59 0 85 22687 12771 1024 150 00 5656 3221 31 8 0 62 0 85 20876 10991 1024 290 00 5185 2921 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
