Thread: wal-debug
i want to disable WAL.
1. an it be done?
2. What lines do i have to modify in postgresql.conf and how?
Thank you in advance
On Mon, Mar 03, 2003 at 01:43:36AM -0800, Munteanu Gabriel wrote: > i want to disable WAL. > 1. an it be done? > 2. What lines do i have to modify in postgresql.conf and how? No, it can't be done. WAL is *good*. WAL stops you from losing data if the machine crashes. WAL can improve performance. Why are you asking. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
I am asking to improve performace. Can you explain me how WAL improves performance. ex: a query lasts 1 second when WAL would be disabled if it is enabled how WAL improves performance? What does it do? Gabi ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Munteanu Gabriel" <gmunteanu@rdslink.ro> Cc: "postgres-general" <pgsql-general@postgresql.org> Sent: Monday, March 03, 2003 4:48 AM Subject: Re: [GENERAL] wal-debug No, it can't be done. WAL is *good*. WAL stops you from losing data if the machine crashes. WAL can improve performance. Why are you asking.
On Monday 03 Mar 2003 4:41 pm, you wrote: > I am asking to improve performace. > Can you explain me how WAL improves performance. > ex: > a query lasts 1 second when WAL would be disabled > if it is enabled how WAL improves performance? > What does it do? I suggest you read postgresql admin guide about how WAL works. Furthermore select queries are not affected by having or not having WAL. WAL deals with insert/update/deletes. I suggest you read the admin guide, start to end, even if you don't have any other problems. That would asve great deal of time for you. Shridhar
On Mon, Mar 03, 2003 at 03:11:37AM -0800, Munteanu Gabriel wrote: > I am asking to improve performace. > Can you explain me how WAL improves performance. > ex: > a query lasts 1 second when WAL would be disabled > if it is enabled how WAL improves performance? > What does it do? Please read this: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=wal.html The upshot that it doesn't need to write as much to disk to commit a transaction and those write are sequential instead of scattered across the disk. What are you doing that makes you think WAL is bad? -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
in the URL you sent me, i read the following: Write Ahead Logging (WAL) is a standard approach to transaction logging. Its detailed description may be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged - that is, when log records have been flushed to permanent storage. When we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages will first be redone from the log records (this is roll-forward recovery, also known as REDO) and then changes made by uncommitted transactions will be removed from the data pages (roll-backward recovery - UNDO). So, WAL is about logging. it logs every transaction that is made to the database. I want to disable logging of the transactions. i don't need undo' s and redo's. I don't want to know if someone has inserted something in the databse or not. AND, ESPECIALLY, i don't want any other writing in RAM or later on disk because of the logging. (WAL introduces an overhead, it writes in a buffer in RAM some data about the transactions and when the buffer is full or some other event occurs it writes it to disk). I am not doing something specially, I want to increase performance, that's all. and wanted to disable WAL. ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Munteanu Gabriel" <gmunteanu@rdslink.ro> Cc: "postgres-general" <pgsql-general@postgresql.org> Sent: Monday, March 03, 2003 2:28 PM Subject: Re: [GENERAL] wal-debug Please read this: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=wal.html The upshot that it doesn't need to write as much to disk to commit a transaction and those write are sequential instead of scattered across the disk. What are you doing that makes you think WAL is bad?
On Mon, Mar 03, 2003 at 01:46:39PM -0800, Munteanu Gabriel wrote: > in the URL you sent me, i read the following: > Write Ahead Logging (WAL) is a standard approach to transaction logging. Its > detailed description may be found in most (if not all) books about > transaction processing. Briefly, WAL's central concept is that changes to [snip] Well, it can disperse the writes so they are spread more efficiently, but yes it can be more writes. > I am not doing something specially, I want to increase performance, that's > all. > > and wanted to disable WAL. Well, you can't disable WAL. But since you don't mind the stability of your data you could just get an older version (pre-WAL) say 7.0 or 7.1. You can also turn off fsync. Then it might disappear from your disk cache instead of being written. Either way you're putting your data at risk. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
ok, Thanx ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Munteanu Gabriel" <gmunteanu@rdslink.ro> Cc: "postgres-general" <pgsql-general@postgresql.org> Sent: Monday, March 03, 2003 4:00 PM Subject: Re: [GENERAL] wal-debug [snip] Well, it can disperse the writes so they are spread more efficiently, but yes it can be more writes. > I am not doing something specially, I want to increase performance, that's > all. > > and wanted to disable WAL. Well, you can't disable WAL. But since you don't mind the stability of your data you could just get an older version (pre-WAL) say 7.0 or 7.1. You can also turn off fsync. Then it might disappear from your disk cache instead of being written. Either way you're putting your data at risk. --
On Tuesday 04 Mar 2003 3:16 am, you wrote: > I am not doing something specially, I want to increase performance, that's > all. > > and wanted to disable WAL. If you are shooting for raw performance, it would be better if you define what level of performance you are aiming for. Say you want 200 transaction per second and want to achieve it, then it is a good starting point. Optimising for performance without any aim is not that useful in the end run. You can spend that time/energy someplace else. Shridhar
The following SQL is a simple join of two tables used to create a view
with an integer depth value & explicit centroid assigned to each cell.
(Cells are box datatype)
This works fine.
SELECT chat_cells.cell_id,
chat_cells.cell,
(@@ chat_cells.cell) AS centre,
int4(id_depth.depth) AS depth,
chat_cells.substrate
FROM chat_cells,
id_depth
WHERE (chat_cells.cell_id = id_depth.depth_id);
What I want to do now is save the data as a table, using select into.
The modified query (as I read the docs) should be:
SELECT chat_cells.cell_id,
chat_cells.cell,
(@@ chat_cells.cell) AS centre,
int4(id_depth.depth) AS depth,
chat_cells.substrate
INTO table1
FROM chat_cells,
id_depth
WHERE (chat_cells.cell_id = id_depth.depth_id);
When I run this, I get the error message:
PostgreSQL Error Message:
ERROR: DECLARE CURSOR must not specify INTO
Can anyone tell me what I'm doing wrong & how to select the data into a
new table?
Thanks,
Brent Wood
Brent Wood <b.wood@niwa.co.nz> writes:
> What I want to do now is save the data as a table, using select into.
> The modified query (as I read the docs) should be:
> SELECT chat_cells.cell_id,
> chat_cells.cell,
> (@@ chat_cells.cell) AS centre,
> int4(id_depth.depth) AS depth,
> chat_cells.substrate
> INTO table1
> FROM chat_cells,
> id_depth
> WHERE (chat_cells.cell_id = id_depth.depth_id);
Looks fine to me.
> When I run this, I get the error message:
> PostgreSQL Error Message:
> ERROR: DECLARE CURSOR must not specify INTO
You had better let us into the secret of what client-side code you're
using. There's no cursor in the stated query, so something is
editorializing on your query...
regards, tom lane