Re: [HACKERS] Help me recovering data
On 17 Feb 2005, Greg Stark wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: > > > We do ~4000 txn/minute so in 6 month you are screewd up... > > Sure, but if you ran without vacuuming for 6 months, wouldn't you notice the > huge slowdowns from all those dead tuples before that? Most people that we have seen on irc that run into the problem do vacuum some tables, but forget to vacuum all. Then their tables work fine but suddenly some system tables like pg_databases and pg_shadow become empty since they never was vacuumed... -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Design notes for BufMgrLock rewrite
On Wed, Feb 16, 2005 at 11:42:11AM -0600, Kenneth Marshall wrote: > I have seen this algorithm described as a more generalized clock type > algorithm. As the size of the counter increases, up to the number of > buffers, the clock algorithm becomes LRU. One bit is the lightest > weight approximation. Increasing the number of bits or a count makes > the clock algorithm more closely approximate LRU. You need to balance > how long it takes to find a free buffer. That time increases as the > count size increases. Yeah, the trick to this seems to be how you tweak the rate at which stuff 'falls out' of the active list. I can think of 3 ways to accomplish this: 1) Change the maximum value to count to (or the value at which a buffer is considered no longer in use). This has the disadvantage of changing how effective the count is. In an extreme situation, it would retuce back to a single bit. It also won't affect buffers that already have higher counts, meaning older data is more likely to stay in buffer than newer data. 2) Change the amount the counter is incremented by on each use (and/or the amount it's decremented by). An example of this might be having the clock decrement by 10. Under a light to medium load, the system might increment by 10 on each use, but if the system starts getting pressed for free buffers, that could be reduced. A downside of this would be that it potentially requires more space in each header to store a larger value. An advatage is that it allows more flexability than #1. For example, if the decrement value is increased in order to speed up reclaiming of buffers, it won't create a difference in how buffers are weighted based on when they were accessed like #1 will. 3) Change the speed of the clock. This is what BSD effectively does. The OS periodically checks to see how many pages are available on the free list, as well as how many were removed since the last check. This information is used to decide how many pages the clock algorithm should attempt to free in the next time period (which can be 0). If a two-hand algorithm is used, the distance between the two hands can also be varied. I think #3 probably means you'd need a seperate process to handle the clock and moving buffers to a free list. Or perhaps this gets tied in with the background writer. This might mean more overhead, but it could improve contention if it means only one process needs to aquire some of these locks. So much for a simple design discussion. :) Fortunately, #1 and #2 should be easy to test. #3 will certainly require more code, but it would probably be simpler to implement than having multiple backends running the clock algorithm (which I think is the current plan). Something else I thought of; by using a counter instead of a bit, you can also 'pre-seed' buffers based on why they were populated. For example, pages brought in from an index might start with a value of 4; heap pages 3, heap pages from a seqscan 2, and pages from vacuum, 1, or maybe even 0. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
Gaetano Mendola <[EMAIL PROTECTED]> writes: > We do ~4000 txn/minute so in 6 month you are screewd up... Sure, but if you ran without vacuuming for 6 months, wouldn't you notice the huge slowdowns from all those dead tuples before that? -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Apple disabled fsync??
Found on a certain other DBMS' mailing list: * InnoDB: When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the operating system version at run time and use the `fcntl()' file flush method on Mac OS X versions 10.3 and later. Apple had disabled `fsync()' in Mac OS X for internal disk drives, which caused corruption at power outages. If that claim is true then we are vulnerable to data corruption on power failure on OS X. Comments, test cases? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
Gaetano Mendola <[EMAIL PROTECTED]> writes: > BTW, why not do an automatic vacuum instead of shutdown ? At least the > DB do not stop working untill someone study what the problem is and > how solve it. No, the entire point of this discussion is to whup the DBA upside the head with a big enough cluestick to get him to install autovacuum. Once autovacuum is default, it won't matter anymore. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
Tom Lane wrote: > Bruno Wolff III <[EMAIL PROTECTED]> writes: > >>I don't think there is much point in making it configurable. If they knew >>to do that they would most likely know to vacuum as well. > > > Agreed. > > >>However, 100K out of 1G seems too small. Just to get wrap around there >>must be a pretty high transaction rate, so 100K may not give much warning. >>1M or 10M seem to be better. > > > Good point. Even 10M is less than 1% of the ID space. Dunno about you, > but the last couple cars I've owned start flashing warnings when the gas > tank is about 20% full, not 1% full... BTW, why not do an automatic vacuum instead of shutdown ? At least the DB do not stop working untill someone study what the problem is and how solve it. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help me recovering data
Stephan Szabo wrote: > On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > > >>>Once autovacuum gets to the point where it's used by default, this >>>particular failure mode should be a thing of the past, but in the >>>meantime I'm not going to panic about it. >> >>I don't know how to say this without sounding like a jerk, (I guess that's >>my role sometimes) but would you go back and re-read this sentence? >> >>To paraphrase: "I know this causes a catestrophic data loss, and we have >>plans to fix it in the future, but for now, I'm not going panic about it." > > > Do you have a useful suggestion about how to fix it? "Stop working" is > handwaving and merely basically saying, "one of you people should do > something about this" is not a solution to the problem, it's not even an > approach towards a solution to the problem. Is not a solution but between loose data and shutdown the postmaster I prefer the shutdown. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help me recovering data
Greg Stark wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>
>
>>Christopher Kings-Lynne wrote:
>>
>>
>>>I wonder if I should point out that we just had 3 people suffering XID
>>>wraparound failure in 2 days in the IRC channel...
>>
>>I have had half a dozen new customers in the last six months that have
>>had the same problem. Nothing like the phone call:
>
>
> How are so many people doing so many transactions so soon after installing?
>
> To hit wraparound you have to do a billion transactions? ("With a `B'") That
> takes real work. If you did 1,000 txn/minute for every minute of every day it
> would still take a couple years to get there.
We do ~4000 txn/minute so in 6 month you are screewd up...
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
Richard Huxton writes: > I seem to remember some subtle problems with dropped columns and plpgsql > functions - could be one of those still left. For instance: regression=# create table usno (ra real, dec real, bmag real, rmag real,ipix int8); CREATE TABLE regression=# [ create Sergey's functions ] regression=# insert into usno values(1,2,3,4); INSERT 1132435 1 regression=# select * from yyy(); ra | dec | bmag | rmag | ipix +-+--+--+-- 1 | 2 |3 |4 | (1 row) regression=# alter table usno add column errbox box; ALTER TABLE regression=# select * from yyy(); ra | dec | bmag | rmag | ipix | errbox +-+--+--+--+ 1 | 2 |3 |4 | | (1 row) regression=# alter table usno drop column errbox; ALTER TABLE regression=# select * from yyy(); ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "yyy" line 8 at return next regression=# It looks like the code that handles returning a RECORD variable doesn't cope with dropped columns in the function result rowtype. (If you instead declare rec as usno%rowtype, you get a different set of misbehaviors after adding/dropping columns, so that code path isn't perfect either :-() regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
> Does starting a new backend session make the problem go away? No ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
Tom Lane wrote: [EMAIL PROTECTED] writes: Maybe I'm missing something, but shouldn't the prospect of data loss (even in the presense of admin ignorance) be something that should be unacceptable? Certainly within the realm "normal PostgreSQL" operation. Once autovacuum gets to the point where it's used by default, this particular failure mode should be a thing of the past, but in the meantime I'm not going to panic about it. Which I hope will be soon. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > But concerning to the added columns, I can say that before the column errbox > didn't, and I revealed the problems began after adding it (together with > index creation on that column). Does starting a new backend session make the problem go away? regards, tom lane ---(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: [HACKERS] how to make table inherits another ?
On Wed, 16 Feb 2005, Tom Lane wrote: Oleg Bartunov writes: I know this. I need to create inheritance for already created tables. There is no way to do this using alter table, so I tried to define it by hand :) Did you remember to set relhassubclass for the parent table? AFAIR, all that you really need are that and the pg_inherits row. Aha, that works. Thanks, Tom. It'd be a good idea to install a pg_depend entry, and to modify the child's pg_attribute rows to show the columns as inherited (attislocal and attinhcount) but I believe the latter would only bite you if you tried to do ALTER commands on the tables later. I'll save this info. regards, tom lane Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] how to make table inherits another ?
Oleg Bartunov writes: > I know this. I need to create inheritance for already created tables. > There is no way to do this using alter table, so I tried to > define it by hand :) Did you remember to set relhassubclass for the parent table? AFAIR, all that you really need are that and the pg_inherits row. It'd be a good idea to install a pg_depend entry, and to modify the child's pg_attribute rows to show the columns as inherited (attislocal and attinhcount) but I believe the latter would only bite you if you tried to do ALTER commands on the tables later. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
I think the people who've managed to shoot themselves in the foot this way are those who decided to "optimize" their cron jobs to only vacuum their user tables, and forgot about the system catalogs. So it's probably more of a case of "a little knowledge is a dangerous thing" than never having heard of VACUUM at all. I too don't see that you could possibly get to 2 billion transactions without having found out that Postgres requires regular VACUUMing. I have had two new customers in the last year who didn't do any vacuums that had a rollover. The database sat in a backroom and did processing. It just worked so they didn't worry about it. It took one of them almost two years to get there but it does happen. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
Sergey E. Koposov wrote: For the real functions which I use, instead of query = ''SELECT * FROM usno''; I have query = my_C_function(some_args); Oh? I'd make a small side bet that the underlying error is in your C function --- possibly it's tromping on some data structure and the damage doesn't have an effect till later. If you can demonstrate the problem without using any custom C functions then I'd be interested to see a test case. I want to clarify, that I have a problem even without my C functions!! And show the full exact(but long) test case, which I performed just now specially. I begin from table usno with 500 millions records wsdb=# \d usno Table "public.usno" Column | Type | Modifiers ++--- ra | real | dec| real | bmag | real | rmag | real | ipix | bigint | errbox | box| Indexes: "box_ind" rtree (errbox) "ipix_ind" btree (ipix) "radec_ind" btree (ra, "dec") This is just a shot in the dark, but I don't suppose you've dropped or modified any columns in "usno" have you? I seem to remember some subtle problems with dropped columns and plpgsql functions - could be one of those still left. It'd look like tablesize was the problem because of course no-one's got time to test with 500 million test rows. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
On Wed, 16 Feb 2005, Richard Huxton wrote: > This is just a shot in the dark, but I don't suppose you've dropped or > modified any columns in "usno" have you? > > I seem to remember some subtle problems with dropped columns and plpgsql > functions - could be one of those still left. It'd look like tablesize > was the problem because of course no-one's got time to test with 500 > million test rows. 1) I have static tables. I dont modify them! 2) My test table is q3c (with 2 rows), and the table with 500 millions of rows is not test table, it is the table with data :-) > -- >Richard Huxton >Archonet Ltd > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] how to make table inherits another ?
On Wed, 16 Feb 2005, elein wrote: You specify the inheritance on the creation of the child table. See CREATE TABLE I know this. I need to create inheritance for already created tables. There is no way to do this using alter table, so I tried to define it by hand :) --elein On Wed, Feb 16, 2005 at 11:48:54PM +0300, Oleg Bartunov wrote: Hi there, is't possible to make table to be inherited from another table in case both tables already exist. I tried to insert record to pg_inherits, but it doesn't helped. openfts=# select * from pg_inherits; inhrelid | inhparent | inhseqno --+---+-- 2617283 | 2417279 |1 did I miss something ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] how to make table inherits another ?
You specify the inheritance on the creation of the child table. See CREATE TABLE --elein On Wed, Feb 16, 2005 at 11:48:54PM +0300, Oleg Bartunov wrote: > Hi there, > > is't possible to make table to be inherited from another table in case > both tables already exist. I tried to insert record to pg_inherits, > but it doesn't helped. > > openfts=# select * from pg_inherits; > inhrelid | inhparent | inhseqno > --+---+-- > 2617283 | 2417279 |1 > > did I miss something ? > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(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: [HACKERS] Help me recovering data
Greg Stark <[EMAIL PROTECTED]> writes:
> How are so many people doing so many transactions so soon after installing?
> To hit wraparound you have to do a billion transactions? ("With a `B'") That
> takes real work. If you did 1,000 txn/minute for every minute of every day it
> would still take a couple years to get there.
> And most databases get a mix of updates and selects. I would expect it would
> be pretty hard to go that long with any significant level of update activity
> and no vacuums and not notice the performance problems from the dead tuples.
I think the people who've managed to shoot themselves in the foot this
way are those who decided to "optimize" their cron jobs to only vacuum
their user tables, and forgot about the system catalogs. So it's
probably more of a case of "a little knowledge is a dangerous thing"
than never having heard of VACUUM at all. I too don't see that you
could possibly get to 2 billion transactions without having found out
that Postgres requires regular VACUUMing.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] how to make table inherits another ?
Hi there, is't possible to make table to be inherited from another table in case both tables already exist. I tried to insert record to pg_inherits, but it doesn't helped. openfts=# select * from pg_inherits; inhrelid | inhparent | inhseqno --+---+-- 2617283 | 2417279 |1 did I miss something ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
> > For the real functions which I use, instead of
>
> > query = ''SELECT * FROM usno'';
>
> > I have
>
> > query = my_C_function(some_args);
>
> Oh? I'd make a small side bet that the underlying error is in your C
> function --- possibly it's tromping on some data structure and the
> damage doesn't have an effect till later. If you can demonstrate the
> problem without using any custom C functions then I'd be interested to
> see a test case.
I want to clarify, that I have a problem even without my C functions!!
And show the full exact(but long) test case, which I performed just now
specially.
I begin from table usno with 500 millions records
wsdb=# \d usno
Table "public.usno"
Column | Type | Modifiers
++---
ra | real |
dec| real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box|
Indexes:
"box_ind" rtree (errbox)
"ipix_ind" btree (ipix)
"radec_ind" btree (ra, "dec")
The declaration of the functions:
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM usno'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF usno AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 10 at return next
We see the error
#
Now with q3c table instead of unso
wsdb=# \d q3c
Table "public.q3c"
Column | Type | Modifiers
++---
ra | real |
dec| real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box|
Indexes:
"box_ind1" rtree (errbox)
"ipix_ind1" btree (ipix)
"radec_ind1" btree (ra, "dec")
That table is not empty but filled by random numbers
wsdb=# select * from q3c;
ra | dec | bmag | rmag | ipix | errbox
+-+--+--+--+-
3 | 3 |4 |5 | 55 | (5,6),(3,4)
4 | 5 |6 |5 | 33 | (3,4),(1,2)
(2 rows)
Now the changed functions (notice, the only difference is
replacing all occurencies of "usno" to "q3c")
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM q3c'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF q3c AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
wsdb=# drop FUNCTION yyy();
DROP FUNCTION
wsdb=# \i q3c.sql
CREATE FUNCTION
CREATE FUNCTION
wsdb=# select * from yyy();
ra | dec | bmag | rmag | ipix | errbox
+-+--+--+--+-
3 | 3 |4 |5 | 55 | (5,6),(3,4)
4 | 5 |6 |5 | 33 | (3,4),(1,2)
(2 rows)
We don't see the error. But the only change was the change from one big
table to a smaller one with the precisely same structure.
###
---(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: [HACKERS] Work on Table Inheritance
That the children tables do not inherit these items is mostly by design, for better or worse. Many people take advantage of the indexes distribution across the tables and the ability to have triggers and keys different across the inheritance can be useful. This is a controversial subject. You should be familiar with the original intentions of the UC postgres project with regards to table inheritances as well as the well trodden arguments for and against pushing all of the features in the parent table into the children tables. For example, the is a Stonebraker not a Date implementation of table inheritance. --elein [EMAIL PROTECTED] On Wed, Feb 16, 2005 at 07:22:39AM -0800, Eliot Simcoe wrote: > Hello everyone, > > I'm working on a project which makes extensive use of the PostgreSQL table > inheritance features and am interested in fixing some bugs I have come > across. To my knowledge, triggers, primary keys, index, foreign key and > unique constraints are not inherited. I am more than willing to put the time > into learning the PgSQL source tree and creating/submitting patches to > correct the above issues, but I need some help getting started. If there is > anyone on the list familiar with this topic who could point me in the right > direction, I'll start asap. > > Thanks, > Eliot Simcoe > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > Concerning to the exact form of my functions (using cursors, but still > collecting all the data in the memory). As I understand this is the only one > way (or just the simplest way ???) > to execute fully dynamic queries returned by C function in PL/SQL. > For the real functions which I use, instead of > query = ''SELECT * FROM usno''; > I have > query = my_C_function(some_args); Oh? I'd make a small side bet that the underlying error is in your C function --- possibly it's tromping on some data structure and the damage doesn't have an effect till later. If you can demonstrate the problem without using any custom C functions then I'd be interested to see a test case. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Christopher Kings-Lynne wrote:
>
> > I wonder if I should point out that we just had 3 people suffering XID
> > wraparound failure in 2 days in the IRC channel...
>
> I have had half a dozen new customers in the last six months that have
> had the same problem. Nothing like the phone call:
How are so many people doing so many transactions so soon after installing?
To hit wraparound you have to do a billion transactions? ("With a `B'") That
takes real work. If you did 1,000 txn/minute for every minute of every day it
would still take a couple years to get there.
And most databases get a mix of updates and selects. I would expect it would
be pretty hard to go that long with any significant level of update activity
and no vacuums and not notice the performance problems from the dead tuples.
What am I missing. Is there a significant percentage of the user base that's
doing nothing but loading huge static databases and then performing massive
loads (like thousands of queries per second) of purely read-only queries
against them?
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] win32 performance - fsync question
> looking for the way how to increase performance at Windows XP box, I found > the parameters > > #fsync = true # turns forced synchronization on or off > #wal_sync_method = fsync# the default varies across platforms: > # fsync, fdatasync, open_sync, or > open_datasync > > I have no idea how it works with win32. May I try fsync = false, or it is > dangerous? Which of wal_sync_method may I try at WinXP? wal_sync_method does nothing on XP. The fsync option will tremendously increase performance on writes at the cost of possible data corruption in the event of a expected server power down. The main performance difference between win32 and various unix systems is that fsync() takes much longer on win32 than linux. Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > LOOP > FETCH cur into rec; > RETURN NEXT rec; > EXIT WHEN NOT FOUND; > END LOOP; > RETURN; Don't you think you should have the EXIT *above* the RETURN NEXT? I would expect this to emit a bogus row of nulls after the last row returned by the cursor. (At least that's what I get with current sources. Pre-8.0 it might return the last row twice.) Running it on a 500-million-row table would quite possibly run out of memory or disk space, too, because RETURN NEXT accumulates all the results before the function is actually exited. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Design notes for BufMgrLock rewrite
On Wed, Feb 16, 2005 at 12:33:38PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > The advantage of using a counter instead of a simple active > > bit is that buffers that are (or have been) used heavily will be able to > > go through several sweeps of the clock before being freed. Infrequently > > used buffers (such as those from a vacuum or seq. scan), would get > > marked as inactive the first time they were hit by the clock hand. > > Hmm. It would certainly be nearly as easy to adjust a counter as to > manipulate the RECENTLY_USED flag bit that's in the patch now. (You > could imagine the RECENTLY_USED flag bit as a counter with max value 1.) > > What I'm envisioning is that pinning (actually unpinning) a buffer > increments the counter (up to some limit), and the clock sweep > decrements it (down to zero), and only buffers with count zero are taken > by the sweep for recycling. That could work well, but I think the limit > needs to be relatively small, else we could have the clock sweep having > to go around many times before it finally frees a buffer. Any thoughts > about that? Anyone seen any papers about this sort of algorithm? > I have seen this algorithm described as a more generalized clock type algorithm. As the size of the counter increases, up to the number of buffers, the clock algorithm becomes LRU. One bit is the lightest weight approximation. Increasing the number of bits or a count makes the clock algorithm more closely approximate LRU. You need to balance how long it takes to find a free buffer. That time increases as the count size increases. Ken ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Work on Table Inheritance
Hello everyone, I'm working on a project which makes extensive use of the PostgreSQL table inheritance features and am interested in fixing some bugs I have come across. To my knowledge, triggers, primary keys, index, foreign key and unique constraints are not inherited. I am more than willing to put the time into learning the PgSQL source tree and creating/submitting patches to correct the above issues, but I need some help getting started. If there is anyone on the list familiar with this topic who could point me in the right direction, I'll start asap. Thanks, Eliot Simcoe ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Strange RETURN NEXT behaviour in Postgres 8.0
Hi All
I have a quite strange problem with RETURN NEXT statement.
I have a big table with 500 millions of rows running on Postgres 8.0.
Table "public.usno"
Column | Type | Modifiers
++---
ra | real |
dec| real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box|
Indexes:
"box_ind" rtree (errbox)
"ipix_ind" btree (ipix)
"radec_ind" btree (ra, "dec")
I actually wrote some procedures in PL/SQL using dynamical queries,
and once I obtained the following error.
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "yyy" line 8 at return next
To solve the problem, I used just the following simple PL/SQL functions, and
a query "select * from yyy()"
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM usno'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF usno AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
RETURN NEXT rec;
EXIT WHEN NOT FOUND;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
I was quite surprised by this errors, because I have tried the same
functions on rather same (but smaller table) on Postgres 7.4.6 on my laptop
without any problems.
For debugging purposes, I just have created by hand on Postgres 8.0 machine
the small table "q3c" with just two rows, but same structure as usno table.
Table "public.q3c"
Column | Type | Modifiers
++---
ra | real |
dec| real |
bmag | real |
rmag | real |
ipix | bigint |
errbox | box|
Indexes:
"box_ind1" rtree (errbox)
"ipix_ind1" btree (ipix)
"radec_ind1" btree (ra, "dec")
And, after replacing "usno"->"q3c" in the xxx() and yyy(), the query
"select * from yyy()" worked without problems!!!
So, how can it be, that my PL/SQL functions works fine on one(smaller)
table, but fails on another(bigger) table.
Thanks in advance for any ideas.
Sergey
PS
I have tried my code replacing the declaration
"rec record;" by "rec TABLE_NAME%ROWTYPE", and it worked for both (big and
small table), but I don't understand, why it doesn't work with the type
"record".
Sergey E. Koposov
Sternberg Astronomical Institute, Moscow University (Russia)
Max-Planck Institute for Astronomy (Germany)
Internet: [EMAIL PROTECTED], http://lnfm1.sai.msu.su/~math/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Urgent problem: Unicode characters greater than or equal to 0x10000 are not supported
Hi all, When I try to input a unicode caracter which code is greater than U+2, phpPgAdmin returns the following error message : ERROR: Unicode characters greater than or equal to 0x1 are not supported Could someone fix this problem ? If yes, would you please tell me where can i download the new postgre debian package. Actually, i need this limitation to be pushed at least to U+2F800, but 10FFFB would even be better (it looks to be the last unicode character, according the gnome charmap). I use Debian "testing" with psql 7.4.7 Also, I have experienced the same problem with MySQL 4.1.9, that's what made me try PostGre. Thanks for your help, Gilles Vigner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] UTF8 or Unicode
On Feb 14, 2005, at 9:27 PM, Abhijit Menon-Sen wrote: I know UTF8 is a type of unicode but do we need to rename anything from Unicode to UTF8? I don't know. I'll go through the documentation to see if I can find anything that needs changing. It's not the documentation that is wrong. Specifying the database "encoding" as "Unicode" is simply a bug (see initdb). What if postgresql supports UTF-16 in the future? What would you call it? Also, the backend protocol also uses "UNICODE" when specifying the encoding. All the other encoding names are specified correctly AFAICS. I brought this up before: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00811.php We could make UTF8 the canonical form in the aliasing mechanism, but beta 4 is a bit late to come up with this kind of idea. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
Bruno Wolff III <[EMAIL PROTECTED]> writes: > I don't think there is much point in making it configurable. If they knew > to do that they would most likely know to vacuum as well. Agreed. > However, 100K out of 1G seems too small. Just to get wrap around there > must be a pretty high transaction rate, so 100K may not give much warning. > 1M or 10M seem to be better. Good point. Even 10M is less than 1% of the ID space. Dunno about you, but the last couple cars I've owned start flashing warnings when the gas tank is about 20% full, not 1% full... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
Stephan Szabo <[EMAIL PROTECTED]> writes: > All in all, I figure that odds are very high that if someone isn't > vacuuming in the rest of the transaction id space, either the transaction > rate is high enough that 100,000 warning may not be enough or they aren't > going to pay attention anyway and the howitzer might not be bad. Yeah. It's easy to imagine scenarios where the majority of the warnings go into the bit bucket (because they are going to noninteractive client applications that just ignore NOTICE messages). So I think it's appropriate to be delivering the warnings for a good long time, in hopes that someone at least occasionally fires up psql and happens to actually see them. Something like 100K or 1M transactions feels about right to me. Pulling the failure trigger with 100K transactions still to go is surely overly conservative, but compared to the size of the ID space it is not worth noticing. As far as the actual implementation, I was envisioning adding a limiting XID variable and a database name variable to shared memory (protected by the same LWLock that protects the nextXID counter). These would be computed and loaded during the bootstrap process, right after we finish WAL replay if any. It would probably cost us one XID to do this (though maybe it could be done without running a real transaction? This ties in with my thoughts about replacing GetRawDatabaseInfo with a flat file...), but one XID per postmaster start attempt is hopefully not gonna kill us. Subsequently, any VACUUM that updates a datfrozenxid entry in pg_database would update these variables to reflect the new safe limit and the name of the database with the currently oldest datfrozenxid. This would allow a very cheap comparison during GetNewTransactionId to see if we are near enough to generate a warning: WARNING: database "foo" must be vacuumed within 58372 transactions or past the limit and generate an error: ERROR: database is shut down to avoid wraparound data loss in database "foo" HINT: Stop the postmaster and use a standalone backend to VACUUM in "foo". In the error case, we could error out *without* advancing nextXID, so that even automated clients continually retrying failed transactions couldn't blow past the safety margin. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help me recovering data
Tom Lane wrote: Maybe (a) within 200,000 transactions of wrap, every transaction start delivers a WARNING message; (b) within 100,000 transactions, forced shutdown as above. This seems sound enough, but if the DBA and/or SA can't be bothered reading the docs where this topic features quite prominently, I suspect the warning messages won't have much effect either. Basically ISTM we're talking about people who *need* a clue howitzer. This will possibly hit us more now we have the Windows port (or maybe not, if the Windows servers are regularly rebooted ;-) ) cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Help me recovering data
Stephan Szabo wrote: On Wed, 16 Feb 2005, Tom Lane wrote: Stephan Szabo <[EMAIL PROTECTED]> writes: (a) within 200,000 transactions of wrap, every transaction start delivers a WARNING message; (b) within 100,000 transactions, forced shutdown as above. This seems reasonable, although perhaps the former could be something configurable. I'm not sure there's a good reason to allow the latter to change unless there'd ever be a case where 100,000 transactions wasn't enough to vacuum or something like that. All in all, I figure that odds are very high that if someone isn't vacuuming in the rest of the transaction id space, either the transaction rate is high enough that 100,000 warning may not be enough or they aren't going to pay attention anyway and the howitzer might not be bad. How would people feel about stopping after the first 100 transactions too? Pro: Teaches the lesson straight away. Con: Irritating Con: Might not be enough time for automated installers -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
On Wed, Feb 16, 2005 at 09:38:31 -0800, Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Wed, 16 Feb 2005, Tom Lane wrote: > > > (a) within 200,000 transactions of wrap, every transaction start > > delivers a WARNING message; > > > > (b) within 100,000 transactions, forced shutdown as above. > > This seems reasonable, although perhaps the former could be something > configurable. I'm not sure there's a good reason to allow the latter to > change unless there'd ever be a case where 100,000 transactions wasn't > enough to vacuum or something like that. I don't think there is much point in making it configurable. If they knew to do that they would most likely know to vacuum as well. However, 100K out of 1G seems too small. Just to get wrap around there must be a pretty high transaction rate, so 100K may not give much warning. 1M or 10M seem to be better. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] win32 performance - fsync question
Hi, looking for the way how to increase performance at Windows XP box, I found the parameters #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync I have no idea how it works with win32. May I try fsync = false, or it is dangerous? Which of wal_sync_method may I try at WinXP? Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Help me recovering data
On Wed, 16 Feb 2005, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > Right, but since the how to resolve it currently involves executing a > > query, simply stopping dead won't allow you to resolve it. Also, if we > > stop at the exact wraparound point, can we run into problems actually > > trying to do the vacuum if that's still the resolution technique? > > We'd have to do something with a fair amount of slop. The idea I was > toying with just now involved a forcible shutdown once we get within > say 100,000 transactions of a wrap failure; but apply this check only > when in interactive operation. This would allow the DBA to perform > the needed VACUUMing manually in a standalone backend. > > The real question here is exactly how large a cluestick do you want to > hit the DBA with. I don't think we can "guarantee" no data loss with > anything less than forced shutdown, but that's not so much a cluestick > as a clue howitzer. > > Maybe > > (a) within 200,000 transactions of wrap, every transaction start > delivers a WARNING message; > > (b) within 100,000 transactions, forced shutdown as above. This seems reasonable, although perhaps the former could be something configurable. I'm not sure there's a good reason to allow the latter to change unless there'd ever be a case where 100,000 transactions wasn't enough to vacuum or something like that. All in all, I figure that odds are very high that if someone isn't vacuuming in the rest of the transaction id space, either the transaction rate is high enough that 100,000 warning may not be enough or they aren't going to pay attention anyway and the howitzer might not be bad. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Design notes for BufMgrLock rewrite
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > The advantage of using a counter instead of a simple active > bit is that buffers that are (or have been) used heavily will be able to > go through several sweeps of the clock before being freed. Infrequently > used buffers (such as those from a vacuum or seq. scan), would get > marked as inactive the first time they were hit by the clock hand. Hmm. It would certainly be nearly as easy to adjust a counter as to manipulate the RECENTLY_USED flag bit that's in the patch now. (You could imagine the RECENTLY_USED flag bit as a counter with max value 1.) What I'm envisioning is that pinning (actually unpinning) a buffer increments the counter (up to some limit), and the clock sweep decrements it (down to zero), and only buffers with count zero are taken by the sweep for recycling. That could work well, but I think the limit needs to be relatively small, else we could have the clock sweep having to go around many times before it finally frees a buffer. Any thoughts about that? Anyone seen any papers about this sort of algorithm? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help me recovering data
Stephan Szabo <[EMAIL PROTECTED]> writes: > Right, but since the how to resolve it currently involves executing a > query, simply stopping dead won't allow you to resolve it. Also, if we > stop at the exact wraparound point, can we run into problems actually > trying to do the vacuum if that's still the resolution technique? We'd have to do something with a fair amount of slop. The idea I was toying with just now involved a forcible shutdown once we get within say 100,000 transactions of a wrap failure; but apply this check only when in interactive operation. This would allow the DBA to perform the needed VACUUMing manually in a standalone backend. The real question here is exactly how large a cluestick do you want to hit the DBA with. I don't think we can "guarantee" no data loss with anything less than forced shutdown, but that's not so much a cluestick as a clue howitzer. Maybe (a) within 200,000 transactions of wrap, every transaction start delivers a WARNING message; (b) within 100,000 transactions, forced shutdown as above. regards, tom lane ---(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: [HACKERS] Help me recovering data
> > On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > >> > On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: >> > >> >> > >> >> > Once autovacuum gets to the point where it's used by default, this >> >> > particular failure mode should be a thing of the past, but in the >> >> > meantime I'm not going to panic about it. >> >> >> >> I don't know how to say this without sounding like a jerk, (I guess >> >> that's >> >> my role sometimes) but would you go back and re-read this sentence? >> >> >> >> To paraphrase: "I know this causes a catestrophic data loss, and we >> have >> >> plans to fix it in the future, but for now, I'm not going panic about >> >> it." >> > >> > Do you have a useful suggestion about how to fix it? "Stop working" >> is >> > handwaving and merely basically saying, "one of you people should do >> > something about this" is not a solution to the problem, it's not even >> an >> > approach towards a solution to the problem. >> >> Actually, it is not a solution to the problem of losing data. It is a >> drop >> dead last ditch failsafe that EVERY PRODUCT should have before losing >> data. > > Let's try again. Saying, "one of you people should do something about > this" is not a solution to the problem or an approach thereto. "Stop > working" is handwaving since I see no approach therein that allows the > user to actually recover the data. > Well, it is sort of the the Hockey strike, now that it seems like stoping normal operation is better than losing billions of rows of data. We can decide who to do it and how to correct it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Help me recovering data
> Stephan Szabo <[EMAIL PROTECTED]> writes: >> Right, but since the how to resolve it currently involves executing a >> query, simply stopping dead won't allow you to resolve it. Also, if we >> stop at the exact wraparound point, can we run into problems actually >> trying to do the vacuum if that's still the resolution technique? > > We'd have to do something with a fair amount of slop. The idea I was > toying with just now involved a forcible shutdown once we get within > say 100,000 transactions of a wrap failure; but apply this check only > when in interactive operation. This would allow the DBA to perform > the needed VACUUMing manually in a standalone backend. > > The real question here is exactly how large a cluestick do you want to > hit the DBA with. I don't think we can "guarantee" no data loss with > anything less than forced shutdown, but that's not so much a cluestick > as a clue howitzer. I think a DBA or accidental DBA would prefer stating in a meeting: "Yea, the database shut down because I didn't perform normal maintenence, its fixed now and we have a script in place so it won't happen again" Over "Yea, the database lost all its data and we have to restore from our last backup because I didn't perform normal maintenence." One gets a "boy are you lucky" over a "you're fired." > > Maybe > > (a) within 200,000 transactions of wrap, every transaction start > delivers a WARNING message; > > (b) within 100,000 transactions, forced shutdown as above. I agree. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
> > On Wed, 16 Feb 2005, Joshua D. Drake wrote: > >> >> >Do you have a useful suggestion about how to fix it? "Stop working" is >> >handwaving and merely basically saying, "one of you people should do >> >something about this" is not a solution to the problem, it's not even >> an >> >approach towards a solution to the problem. >> > >> > >> I believe that the ability for PostgreSQL to stop accepting >> queries and to log to the file or STDERR why it stopped working >> and how to resolve it is appropriate. > > Right, but since the how to resolve it currently involves executing a > query, simply stopping dead won't allow you to resolve it. Also, if we > stop at the exact wraparound point, can we run into problems actually > trying to do the vacuum if that's still the resolution technique? If so, > how far in advance of wraparound must we stop to guarantee it will > succeed? It's not rocket science, but figuring such things out is part of I would say, have a GUC parameter set at 1000 transactions. When fewer than this number are available, postmaster will not run and issue a message "Transaction wrap-around error! You must run vacuum in stingle user postgres mode to correct it, to avoid this message run the vacuum command more frequently" Hell, why not block all the PostgreSQL processes and run vacuum? But, for now, versions of PostgreSQL should stop before losing data. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Design notes for BufMgrLock rewrite
On Sun, Feb 13, 2005 at 06:56:47PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > Tom Lane wrote: > >> One thing I realized quickly is that there is no natural way in a clock > >> algorithm to discourage VACUUM from blowing out the cache. I came up > >> with a slightly ugly idea that's described below. Can anyone do better? > > > Uh, is the clock algorithm also sequential-scan proof? Is that > > something that needs to be done too? > > If you can think of a way. I don't see any way to make the algorithm > itself scan-proof, but if we modified the bufmgr API to tell ReadBuffer > (or better ReleaseBuffer) that a request came from a seqscan, we could > do the same thing as for VACUUM. Whether that's good enough isn't > clear --- for one thing it would kick up the contention for the > BufFreelistLock, and for another it might mean *too* short a lifetime > for blocks fetched by seqscan. Is there anything (in the buffer headers?) that keeps track of buffer access frequency? *BSD uses a mechanism to track roughly how often a page in memory has been accessed, and uses that to determine what pages to free. In 4.3BSD, a simple 2 hand clock sweep is used; the first hand sets a not-used bit in each page, the second hand (which sweeps a fixed distance behind the 1st hand) checks this bit and if it's still clear moves the page either to the inactive list if it's dirty, or to the cache list if it's clean. There is also a free list, which is generally fed by the cache and inactive lists. Postgresql has a big advantage over an OS though, in that it can tolerate much more overhead in buffer access code than an OS can in it's vm system. If I understand correctly, any use of a buffer at all means a lock needs to be aquired on it's buffer header. As part of this access, a counter could be incremented with very little additional cost. A background process would then sweep through 'active' buffers, decrementing this counter by some amount. Any buffer that was decremented below 0 would be considered inactive, and a candidate for being freed. The advantage of using a counter instead of a simple active bit is that buffers that are (or have been) used heavily will be able to go through several sweeps of the clock before being freed. Infrequently used buffers (such as those from a vacuum or seq. scan), would get marked as inactive the first time they were hit by the clock hand. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > > On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > > > >> > > >> > Once autovacuum gets to the point where it's used by default, this > >> > particular failure mode should be a thing of the past, but in the > >> > meantime I'm not going to panic about it. > >> > >> I don't know how to say this without sounding like a jerk, (I guess > >> that's > >> my role sometimes) but would you go back and re-read this sentence? > >> > >> To paraphrase: "I know this causes a catestrophic data loss, and we have > >> plans to fix it in the future, but for now, I'm not going panic about > >> it." > > > > Do you have a useful suggestion about how to fix it? "Stop working" is > > handwaving and merely basically saying, "one of you people should do > > something about this" is not a solution to the problem, it's not even an > > approach towards a solution to the problem. > > Actually, it is not a solution to the problem of losing data. It is a drop > dead last ditch failsafe that EVERY PRODUCT should have before losing > data. Let's try again. Saying, "one of you people should do something about this" is not a solution to the problem or an approach thereto. "Stop working" is handwaving since I see no approach therein that allows the user to actually recover the data. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
> On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > >> > >> > Once autovacuum gets to the point where it's used by default, this >> > particular failure mode should be a thing of the past, but in the >> > meantime I'm not going to panic about it. >> >> I don't know how to say this without sounding like a jerk, (I guess >> that's >> my role sometimes) but would you go back and re-read this sentence? >> >> To paraphrase: "I know this causes a catestrophic data loss, and we have >> plans to fix it in the future, but for now, I'm not going panic about >> it." > > Do you have a useful suggestion about how to fix it? "Stop working" is > handwaving and merely basically saying, "one of you people should do > something about this" is not a solution to the problem, it's not even an > approach towards a solution to the problem. Actually, it is not a solution to the problem of losing data. It is a drop dead last ditch failsafe that EVERY PRODUCT should have before losing data. > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
On Wed, 16 Feb 2005, Joshua D. Drake wrote: > > >Do you have a useful suggestion about how to fix it? "Stop working" is > >handwaving and merely basically saying, "one of you people should do > >something about this" is not a solution to the problem, it's not even an > >approach towards a solution to the problem. > > > > > I believe that the ability for PostgreSQL to stop accepting > queries and to log to the file or STDERR why it stopped working > and how to resolve it is appropriate. Right, but since the how to resolve it currently involves executing a query, simply stopping dead won't allow you to resolve it. Also, if we stop at the exact wraparound point, can we run into problems actually trying to do the vacuum if that's still the resolution technique? If so, how far in advance of wraparound must we stop to guarantee it will succeed? It's not rocket science, but figuring such things out is part of actually making a workable solution. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Help me recovering data
Christopher Kings-Lynne wrote: At this point we have a known critical bug. Usually the PostgreSQL community is all over critical bugs. Why is this any different? It sounds to me that people are just annoyed that users don't RTFM. Get over it. Most won't. If users RTFM more often, it would put most support companies out of business. I wonder if I should point out that we just had 3 people suffering XID wraparound failure in 2 days in the IRC channel... I have had half a dozen new customers in the last six months that have had the same problem. Nothing like the phone call: Uhmmm I am a new customer, help I can't see my databases. Sincerely, Joshua D. Drake Chris -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Help me recovering data
Do you have a useful suggestion about how to fix it? "Stop working" is handwaving and merely basically saying, "one of you people should do something about this" is not a solution to the problem, it's not even an approach towards a solution to the problem. I believe that the ability for PostgreSQL to stop accepting queries and to log to the file or STDERR why it stopped working and how to resolve it is appropriate. Also it is probably appropriate to warn ahead of time... WARNING: Only 50,000 transactions left before lock out or something like that. J ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
At this point we have a known critical bug. Usually the PostgreSQL community is all over critical bugs. Why is this any different? It sounds to me that people are just annoyed that users don't RTFM. Get over it. Most won't. If users RTFM more often, it would put most support companies out of business. I wonder if I should point out that we just had 3 people suffering XID wraparound failure in 2 days in the IRC channel... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Help me recovering data
in the foot. We've seen several instances of people blowing away pg_xlog and pg_clog, for example, because they "don't need log files". Or how about failing to keep adequate backups? That's a sure way for an ignorant admin to lose data too. There is a difference between actively doing something stupid and failing to realize a maintenence task is required. PostgreSQL should stop working. When the admin tries to understand why, they can read a troubleshooting FAQ and say "oops, I gotta run this vacuum thingy." That is a whole lot better than falling off a cliff you didn't even know was there. There is another way to look at this as lends itself to mohawksoft's argument. More often than not DBAs and Sysadmins are neither one. They are people that get shoved into the job because they happen to mention around the water cooler that they "once" installed linux/freebsd -- whatever. Maybe it is an executive that has some of his brains left after sitting behind a desk all day for the last 10 years. One day he/she gets a thought in his head to create a new project named "foo". He does not want to waste his internal resources so said executive decides he will do it himself as a hobby. For some reason, the project actually succeeds (I have seen this many times) and the company starts using it. Well guess what... it uses PostgreSQL. The guy isn't a DBA, heck he is even really a programmer. He had know idea about this "vacuum" thing. He had never heard of other databases having to do it. So they run for a year, and then all of a sudden **BOOM** the world ends. Do you think they are going to care that we "documented" the issue? Uhmmm no they won't. Chances are they will drop kick PostgreSQL and bad talk it to all their other executive friends. In short, this whole argument has the mark of irresponsibility on both parties but it is is the PostgreSQL projects responisbility to make reasonable effort to produce a piece of software that doesn't break. We are not talking about a user who ran a query: delete from foo; At this point we have a known critical bug. Usually the PostgreSQL community is all over critical bugs. Why is this any different? It sounds to me that people are just annoyed that users don't RTFM. Get over it. Most won't. If users RTFM more often, it would put most support companies out of business. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(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: [HACKERS] Help me recovering data
On Wed, 16 Feb 2005 [EMAIL PROTECTED] wrote: > > > > Once autovacuum gets to the point where it's used by default, this > > particular failure mode should be a thing of the past, but in the > > meantime I'm not going to panic about it. > > I don't know how to say this without sounding like a jerk, (I guess that's > my role sometimes) but would you go back and re-read this sentence? > > To paraphrase: "I know this causes a catestrophic data loss, and we have > plans to fix it in the future, but for now, I'm not going panic about it." Do you have a useful suggestion about how to fix it? "Stop working" is handwaving and merely basically saying, "one of you people should do something about this" is not a solution to the problem, it's not even an approach towards a solution to the problem. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Help me recovering data
> [EMAIL PROTECTED] writes: >> Maybe I'm missing something, but shouldn't the prospect of data loss >> (even >> in the presense of admin ignorance) be something that should be >> unacceptable? Certainly within the realm "normal PostgreSQL" operation. > > [ shrug... ] The DBA will always be able to find a way to shoot himself > in the foot. We've seen several instances of people blowing away > pg_xlog and pg_clog, for example, because they "don't need log files". > Or how about failing to keep adequate backups? That's a sure way for an > ignorant admin to lose data too. There is a difference between actively doing something stupid and failing to realize a maintenence task is required. PostgreSQL should stop working. When the admin tries to understand why, they can read a troubleshooting FAQ and say "oops, I gotta run this vacuum thingy." That is a whole lot better than falling off a cliff you didn't even know was there. > > Once autovacuum gets to the point where it's used by default, this > particular failure mode should be a thing of the past, but in the > meantime I'm not going to panic about it. I don't know how to say this without sounding like a jerk, (I guess that's my role sometimes) but would you go back and re-read this sentence? To paraphrase: "I know this causes a catestrophic data loss, and we have plans to fix it in the future, but for now, I'm not going panic about it." What would you do if the FreeBSD group or Linux kernel group said this about a file system? If you failed to run fsck after 100 mounts, you loose your data? I thought PostgreSQL was about "protecting your data." How many times have we smugly said, "yea, you can use MySQL if you don't care about your data." Any data loss caused by postgresql should be seen as unacceptable. It's funny, while I've known about this for a while, and it has always seemed a sort of distant edge condition that is easily avoided. However, with todays faster machines and disks, it is easier to reach this limitation than ever before. All PostgreSQL needs is one or two VERY UPSET mainstream users who lose data to completely reverse the momemntum that it is gaining. No amount of engineering discussion about it not being the fault of postgresql will be lost, and rightfully so, IMHO. Sorry. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Help me recovering data
[EMAIL PROTECTED] writes: > Maybe I'm missing something, but shouldn't the prospect of data loss (even > in the presense of admin ignorance) be something that should be > unacceptable? Certainly within the realm "normal PostgreSQL" operation. [ shrug... ] The DBA will always be able to find a way to shoot himself in the foot. We've seen several instances of people blowing away pg_xlog and pg_clog, for example, because they "don't need log files". Or how about failing to keep adequate backups? That's a sure way for an ignorant admin to lose data too. Once autovacuum gets to the point where it's used by default, this particular failure mode should be a thing of the past, but in the meantime I'm not going to panic about it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Help me recovering data
>> The checkpointer is entirely incapable of either detecting the problem >> (it doesn't have enough infrastructure to examine pg_database in a >> reasonable way) or preventing backends from doing anything if it did >> know there was a problem. > > Well, I guess I meant 'some regularly running process'... > >>>I think people'd rather their db just stopped accepting new transactions >>>rather than just losing data... >> >> Not being able to issue new transactions *is* data loss --- how are you >> going to get the system out of that state? > > Not allowing any transactions except a vacuum... > >> autovacuum is the correct long-term solution to this, not some kind of >> automatic hara-kiri. > > Yeah, seems like it should really happen soon... > > Chris Maybe I'm missing something, but shouldn't the prospect of data loss (even in the presense of admin ignorance) be something that should be unacceptable? Certainly within the realm "normal PostgreSQL" operation. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS]
> Question 1: Is your writeback cache really disabled in Linux, on the > harddrive? Windows fsync will *write through the disk write cache* if > the driver is properly implemented. AFAIK, on Linux if write cache is > enabled on the drive, fsync will only get into the cache. > 800tps sounds unreasonably high on a notebook. > > Question 2: Please try disabling the stats connector and see if that > helps. Merlin Moncure reported some scalability issues with the stats > collector previously. > > > > Several yeas ago (about 1997-1998) Oleg Bartunov and me had > > the same performance results (Linux vs Windows NT + cygwin). > > It was the discussion at this list with resume that the > > reason is the implementation of shared memory under Windows. > > Every IPC operation results the HDD access. > > It shouldn't in 8.0 - at least not on the native win32. Don't know about > cygwin. The price on win32 for row level stats collector is fairly high. Also the stats collector resets randomly under very high loads. However I don't think this is what's going on here. Also, IPC is out. The win32 IPC implementation is fine, if somewhat slower than linux implementation. It's all about syncing, IMO. Merlin ---(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: [HACKERS]
> What kind of performance difference can be expected between > Linux and Windows? Not really known, as the native win version is a bit too new for that. Expect linux performance to be better, though, that much is sure. //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS]
What kind of performance difference can be expected between Linux and Windows? Benjamin Arai [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.benjaminarai.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Magnus Hagander Sent: Tuesday, February 15, 2005 10:03 AM To: E.Rodichev Cc: pgsql-hackers@postgresql.org; oleg@sai.msu.su Subject: Re: [HACKERS] >>> I've tested the performance of 8.0.1 at my dual-boot notebook (Linux >>> and Windows XP). >>> >>> I installed 8.0.1 for Linux and Windows XP, and run pgbench -c 1 -t >>> 1000 Under Linux (kernel 2.6.10) I got about 800 tps, and under >>> Windows XP - about 20-24 tps. >>> >>> Next I switched off virtual memory under Windows (as it was >>> recommended in posting >>> http://www.pgsql.ru/db/mw/msg.html?mid=2026070). It does not help. >>> Without virtual memory I got 15-17 tps. >> >> >> Question 1: Is your writeback cache really disabled in Linux, on the >> harddrive? Windows fsync will *write through the disk write cache* if >> the driver is properly implemented. AFAIK, on Linux if write cache is >> enabled on the drive, fsync will only get into the cache. > >Difficult to say concerning writeback cache... I have 2.6.10 without >any additional tuning, file system is ext2. From dmesg: > >hda: TOSHIBA MK8026GAX, ATA DISK drive >hda: max request size: 128KiB >hda: 156301488 sectors (80026 MB), CHS=65535/16/63, UDMA(100) >hda: cache flushes supported Run: hdparm -I /dev/hda If you get a line like: Commands/features: Enabled Supported: *READ BUFFER cmd *WRITE BUFFER cmd *Host Protected Area feature set *Look-ahead *Write cache ... (last line is what matters here) you have write cacheing enabled. To turn it of, run hdparm -W0 /dev/hda Not sure if you need to reboot, I don'tt hink so. Then re-run the benchmark on linux. >> 800tps sounds unreasonably high on a notebook. > >Yes, I also was surprized. The same test at Xeon 2.4GHz server >indicates about 700 tps. But it is another issue. The CPU probably has nothing to do with this, it's probably all I/O. >> Question 2: Please try disabling the stats connector and see if that >> helps. Merlin Moncure reported some scalability issues with the stats >> collector previously. > >Sorry, what is "stats connector"? That's supposed to be stats collector, as you realised in your other mail. Sorry. >>> Several yeas ago (about 1997-1998) Oleg Bartunov and me had the same >>> performance results (Linux vs Windows NT + cygwin). >>> It was the discussion at this list with resume that the reason is >>> the implementation of shared memory under Windows. >>> Every IPC operation results the HDD access. >> >> It shouldn't in 8.0 - at least not on the native win32. >Don't know about >> cygwin. > >Yes, I also expected that the performance for native implementation >will be more reasonable. In fact, during pgbench test under Windows and >under Linux HDD LED lights continiously, so looks like under Windows >there are much more disk operations compared with Linux. That would be consistent with the theory that write-back caching is enabled on linux and not on windows. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
