Re: [HACKERS] avoiding WAL logging in 8.3
On Tue, 2007-07-24 at 13:04 +0900, Tatsuo Ishii wrote: I noticed in 8.3 there are chances where we can avoid WAL logging. For example, 8.3's pgbench was modified to use TRUNCATE right before COPY. Is there any documentation which describes that kind of techniques? If there's none, I would volunteer the work to create such a documentation since I think this is valuable information for DBAs who wish to migrate to 8.3. The Performance Tips section has been modified to describe this. Would you like me to add something elsewhere also? Multiple entry points to information helps everybody, so I'll happily add more. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] avoiding WAL logging in 8.3
I noticed in 8.3 there are chances where we can avoid WAL logging. For example, 8.3's pgbench was modified to use TRUNCATE right before COPY. Is there any documentation which describes that kind of techniques? If there's none, I would volunteer the work to create such a documentation since I think this is valuable information for DBAs who wish to migrate to 8.3. The Performance Tips section has been modified to describe this. Would you like me to add something elsewhere also? Multiple entry points to information helps everybody, so I'll happily add more. Thanks for pointing out. I found following: COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. Sounds great! BTW, I noticed that COPY, CLUSTER, B-Tree split logging improvements in Josh's presentation in Tokyo. Are they just internal changes and are nothing to do with DBA's job? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] avoiding WAL logging in 8.3
On Tue, 2007-07-24 at 18:45 +0900, Tatsuo Ishii wrote: I noticed in 8.3 there are chances where we can avoid WAL logging. For example, 8.3's pgbench was modified to use TRUNCATE right before COPY. Is there any documentation which describes that kind of techniques? If there's none, I would volunteer the work to create such a documentation since I think this is valuable information for DBAs who wish to migrate to 8.3. The Performance Tips section has been modified to describe this. Would you like me to add something elsewhere also? Multiple entry points to information helps everybody, so I'll happily add more. Thanks for pointing out. I found following: COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. Sounds great! BTW, I noticed that COPY, CLUSTER, B-Tree split logging improvements in Josh's presentation in Tokyo. Are they just internal changes and are nothing to do with DBA's job? Cluster is also mentioned lower down http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PITR The b-tree split logging is an algorithmic reduction in WAL, so isn't user visible or optional in any way. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] avoiding WAL logging in 8.3
On Jul 23, 2007, at 11:30 PM, Simon Riggs wrote: On Tue, 2007-07-24 at 13:04 +0900, Tatsuo Ishii wrote: I noticed in 8.3 there are chances where we can avoid WAL logging. For example, 8.3's pgbench was modified to use TRUNCATE right before COPY. Is there any documentation which describes that kind of techniques? If there's none, I would volunteer the work to create such a documentation since I think this is valuable information for DBAs who wish to migrate to 8.3. The Performance Tips section has been modified to describe this. Would you like me to add something elsewhere also? Multiple entry points to information helps everybody, so I'll happily add more. I would mention it in the documentation for each affected command (COPY, TRUNCATE, etc). I suspect a lot of folks end up only using the SQL reference section. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] avoiding WAL logging in 8.3
Hi, I noticed in 8.3 there are chances where we can avoid WAL logging. For example, 8.3's pgbench was modified to use TRUNCATE right before COPY. Is there any documentation which describes that kind of techniques? If there's none, I would volunteer the work to create such a documentation since I think this is valuable information for DBAs who wish to migrate to 8.3. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
