DB corruption
Hi, I have a query on DB corruption. Is there any way to recover from it without losing data ? Starting postgresql service: [ OK ] psql: FATAL: index "pg_authid_rolname_index" contains unexpected zero page at block 0 HINT: Please REINDEX it. psql: FATAL: "base/11564" is not a valid data directory DETAIL: File "base/11564/PG_VERSION" does not contain valid data. HINT: You might need to initdb. psql: FATAL: "base/11564" is not a valid data directory DETAIL: File "base/11564/PG_VERSION" does not contain valid data. HINT: You might need to initdb. psql: FATAL: "base/11564" is not a valid data directory DETAIL: File "base/11564/PG_VERSION" does not contain valid data. HINT: You might need to initdb. psql: FATAL: "base/11564" is not a valid data directory DETAIL: File "base/11564/PG_VERSION" does not contain valid data. HINT: You might need to initdb. With Best Regards Akshay =-=-= Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
Re: DB corruption
On Fri, Mar 23, 2018 at 01:29:35PM +0530, Akshay Ballarpure wrote: > I have a query on DB corruption. Is there any way to recover from it > without losing data ? Corrupted pages which need to be zeroed in order to recover the rest is data lost forever, except if you have a backup you can rollback to. Please see here for some global instructions about how to deal with such situations: https://wiki.postgresql.org/wiki/Corruption First take a deep breath, and take the time to read and understand it. > Notice: The information contained in this e-mail > message and/or attachments to it may contain > confidential or privileged information. If you are > not the intended recipient, any dissemination, use, > review, distribution, printing or copying of the > information contained in this e-mail message > and/or attachments to it are strictly prohibited. If > you have received this communication in error, > please notify us by reply e-mail or telephone and > immediately and permanently delete the message > and any attachments. Thank you This is a public mailing list. -- Michael signature.asc Description: PGP signature
Should from_collapse be switched off? (queries 10 times faster)
My queries get up to 10 times faster when I disable from_collapse (setting from_collapse_limit=1). After this finding, The pramatic solution is easy: it needs to be switched off. BUT: I found this perchance, accidentally (after the queries had been running for years). And this gives me some questions about documentation and best practices. I could not find any documentation or evaluation that would say that from_collapse can have detrimental effects. Even less, which type of queries may suffer from that. Since we cannot experimentally for all of our queries try out all kinds of options, if they might have significant (negative) effects, my understanding now is that, as a best practice, from_collapse should be switched off by default. And only after development it should be tested if activating it gives a positive improvement. Sadly, my knowledge does not reach into the internals. I can understand which *logical* result I should expect from an SQL statement. But I do not know how this is achieved internally. So, I have a very hard time when trying to understand output from EXPLAIN, or to make an educated guess on how the design of a query may influence execution strategy. I am usually happy when I found some SQL that would correctly produce the results I need. In short: I lack the experience to do manual optimization, or to see where manual optimization might be feasible. The manual section "Controlling the Planner with Explicit JOIN Clauses" gives a little discussion on the issue. But it seems only concerned about an increasing amount of cycles used for the planning activity, not about bad results from the optimization. Worse, it creates the impression that giving the planner maximum freedom is usually a good thing (at least until it takes too much cycles for the planner to evaluate all possibilities). In my case, planning uses 1 or 2% of the cycles needed for execution; that seems alright to me. And, as said above, I cannot see why my queries might be an atypical case (I don't think they are). If somebody would like to get a hands-on look onto the actual case, I'd be happy to put it online. rgds, PMc
Re: Should from_collapse be switched off? (queries 10 times faster)
Peter schrieb am 23.03.2018 um 11:03: > My queries get up to 10 times faster when I disable from_collapse > (setting from_collapse_limit=1). > > After this finding, The pramatic solution is easy: it needs to be > switched off. You should post some example queries together with the slow and fast plans. Ideally generated using "explain(analyze, buffers)" instead of a simple "explain" to see details on the execution Thomas
Re: Should from_collapse be switched off? (queries 10 times faster)
Peter wrote: > My queries get up to 10 times faster when I disable from_collapse > (setting from_collapse_limit=1). > > After this finding, The pramatic solution is easy: it needs to be > switched off. > > BUT: > I found this perchance, accidentally (after the queries had been > running for years). And this gives me some questions about > documentation and best practices. > > I could not find any documentation or evaluation that would say > that from_collapse can have detrimental effects. Even less, which > type of queries may suffer from that. https://www.postgresql.org/docs/current/static/explicit-joins.html states towards the end of the page that the search tree grows exponentially with the number of relations, and from_collapse_limit can be set to control that. > In my case, planning uses 1 or 2% of the cycles needed for > execution; that seems alright to me. > And, as said above, I cannot see why my queries might be an > atypical case (I don't think they are). > > If somebody would like to get a hands-on look onto the actual > case, I'd be happy to put it online. It seems like you are barking up the wrong tree. Your query does not take long because of the many relations in the FROM list, but because the optimizer makes a wrong choice. If you set from_collapse_limit to 1, you force the optimizer to join the tables in the order in which they appear in the query, and by accident this yields a better plan than the one generated if the optimizer is free to do what it thinks is best. The correct solution is *not* to set from_collapse_limit = 1, but to find and fix the problem that causes the optimizer to make a wrong choice. If you send the query and the output of EXPLAIN (ANALYZE, BUFFERS) SELECT ... we have a chance of telling you what's wrong. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Should from_collapse be switched off? (queries 10 times faster)
Laurenz Albe writes: > Peter wrote: >> I could not find any documentation or evaluation that would say >> that from_collapse can have detrimental effects. Even less, which >> type of queries may suffer from that. > https://www.postgresql.org/docs/current/static/explicit-joins.html > states towards the end of the page that the search tree grows > exponentially with the number of relations, and from_collapse_limit > can be set to control that. It's conceivable that the OP's problem is actually planning time (if the query joins sufficiently many tables) and that restricting the cost of the join plan search is really what he needs to do. Lacking any further information about the problem, we can't say. We can, however, point to https://wiki.postgresql.org/wiki/Slow_Query_Questions concerning how to ask this type of question effectively. regards, tom lane
Re: Should from_collapse be switched off? (queries 10 times faster)
On Fri, Mar 23, 2018 at 12:41:35PM +0100, Laurenz Albe wrote: ! https://www.postgresql.org/docs/current/static/explicit-joins.html ! states towards the end of the page that the search tree grows ! exponentially with the number of relations, and from_collapse_limit ! can be set to control that. Yes, I read that page. ! > In my case, planning uses 1 or 2% of the cycles needed for ! > execution; that seems alright to me. ! > And, as said above, I cannot see why my queries might be an ! > atypical case (I don't think they are). ! > ! > If somebody would like to get a hands-on look onto the actual ! > case, I'd be happy to put it online. ! ! It seems like you are barking up the wrong tree. ! ! Your query does not take long because of the many relations in the ! FROM list, but because the optimizer makes a wrong choice. Exactly! And I am working hard in order to understand WHY this happens. ! The correct solution is *not* to set from_collapse_limit = 1, but ! to find and fix the problem that causes the optimizer to make a ! wrong choice. ! ! If you send the query and the output of ! EXPLAIN (ANALYZE, BUFFERS) SELECT ... ! we have a chance of telling you what's wrong. Your viewpoint would be preferrable, only I am lacking any idea on where there could be such a problem that would make up a root cause. I will gladly follow Your suggestion; data is underway. P.
Re: Should from_collapse be switched off? (queries 10 times faster)
The problem appeared when I found the queries suddenly taking longer
than usual. Investigation showed that execution time greatly depends
on the way the queries are invoked.
Consider fn(x) simply a macro containing a plain SQL SELECT statement
returning SETOF (further detail follows below):
# SELECT fn(x);
-> 6.3 sec.
# SELECT a from fn(x) as a;
-> 1.3 sec.
Further investigation with auto_explain shows different plans being
chosen. The slower one uses an Index Only Scan, which seems to perform
bad. Slightly increasing random_page_cost solves this, but this seems
the wrong way, because we are on SSD+ZFS, where random_page_cost
actually should be DEcreased, as there is no difference if random or
sequential.
During this effort I accidentally came upon from_collapse_limit,
and setting it off significantly changed things:
# SET from_collapse_limit = 1;
# SELECT fn(x);
-> 0.6 sec.
# SELECT a from fn(x) as a;
-> 1.2 sec.
The plans look different now (obviousely), and again the difference
between the two invocations comes from an an Index Only Scan, but
this time the Index Only Scan is faster. So now we can reduce
random_page_cost in order to better reflect physical circumstances,
and then both invocations will be fast.
>From here it looks like from_collapse is the problem.
Now for the details:
VACUUM ANALYZE is up to date, and all respective configurations are as
default.
The query itself contains three nested SELECTS working all on the same
table. The table is 400'000 rows, 36 MB. (The machine is a pentium-3,
which is my router - so don't be surprized about the comparatively long
execution times.)
This is the (critical part of the) query - let $1 be something like
'2017-03-03':
SELECT MAX(quotes.datum) AS ratedate, aktkurs.*
FROM quotes, wpnames, places,
(SELECT quotes.datum, close, quotes.wpname_id, places.waehrung
FROM quotes, wpnames, places,
(SELECT MAX(datum) AS datum, wpname_id
FROM quotes
WHERE datum <= $1
GROUP BY wpname_id) AS newest
WHERE newest.datum = quotes.datum
AND newest.wpname_id = quotes.wpname_id
AND quotes.wpname_id = wpnames.id
AND wpnames.place_id = places.id) AS aktkurs
WHERE quotes.wpname_id = wpnames.id
AND wpnames.place_id = places.id AND places.platz = 'WAEHR'
AND wpnames.nummer = aktkurs.waehrung
AND quotes.datum <= aktkurs.datum
GROUP BY aktkurs.datum, aktkurs.close, aktkurs.wpname_id,
aktkurs.waehrung
Here are the (respective parts of the) tables:
CREATE TABLE public.quotes -- rows = 405466, 36 MB
(
id integer NOT NULL DEFAULT nextval('quotes_id_seq'::regclass),
wpname_id integer NOT NULL,
datum date NOT NULL,
close double precision NOT NULL,
CONSTRAINT quotes_pkey PRIMARY KEY (id),
CONSTRAINT fk_rails_626c320689 FOREIGN KEY (wpname_id)
REFERENCES public.wpnames (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE INDEX quotes_wd_idx -- 8912 kB
ON public.quotes
USING btree
(wpname_id, datum);
CREATE TABLE public.wpnames -- rows = 357, 40 kB
(
id integer NOT NULL DEFAULT nextval('wpnames_id_seq'::regclass),
place_id integer NOT NULL,
nummer text NOT NULL,
name text NOT NULL,
CONSTRAINT wpnames_pkey PRIMARY KEY (id),
CONSTRAINT fk_rails_18eae07552 FOREIGN KEY (place_id)
REFERENCES public.places (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
CREATE TABLE public.places -- rows = 11, 8192 b
(
id integer NOT NULL DEFAULT nextval('places_id_seq'::regclass),
platz text NOT NULL,
text text,
waehrung character varying(3) NOT NULL,
CONSTRAINT places_pkey PRIMARY KEY (id)
)
Hint: the quotes table contains daily stock quotes AND forex quotes,
and what the thing does is fetch the newest quotes before a given
date (inmost SELECT), fetch the respective currency ("waehrung") from
wpnames+places (next SELECT), and fetch the (date of the) respective
newest forex quote (last SELECT). (A final outermost fourth select
will then put it all together, but thats not part of the problem.)
Finally, the execution plans:
6 sec. index only scan with from_collapse:
https://explain.depesz.com/s/IPaT
1.3 sec. seq scan with from_collapse:
https://explain.depesz.com/s/Bxys
1.2 sec. seq scan w/o from_collapse:
https://explain.depesz.com/s/V02L
0.6 sec. index only scan w/o from_collapse:
https://explain.depesz.com/s/8Xh
Addendum: from the Guides for the mailing list, supplemental
information as requested. As this concerns planner strategy, which is
influenced by statistics, it appears difficult to me to create a
proper test-case, because I would need to know from where the planner
fetches the decision-relevant information - which is exactly my
question: how does it get the clue to choose the bad plans?
CPU: Intel Pentium III (945.02-MHz 686-class CPU)
avail memory = 2089263104 (1992 MB)
FreeBSD 11.1-RELEASE-p7
PostgreSQL 9.5.7 on i386-portbld-freebsd11.1, compiled
Re: DB corruption
Akshay Ballarpure writes: > I have a query on DB corruption. Is there any way to recover from it > without losing data ? You've already lost data, evidently. > Starting postgresql service: [ OK ] > psql: FATAL: index "pg_authid_rolname_index" contains unexpected zero page > at block 0 > HINT: Please REINDEX it. This is not good. It'd be possible to reindex that index, certainly, but the question is what other files have also been clobbered. > psql: FATAL: "base/11564" is not a valid data directory > DETAIL: File "base/11564/PG_VERSION" does not contain valid data. > HINT: You might need to initdb. Based on the OID I'm going to guess that this is from an attempt to connect to the "postgres" database. (I'm also going to guess that you're running 8.4.x, because any later PG version would have a higher OID for "postgres".) Can you connect to any other databases? If so, do their contents seem intact? If you're really lucky, meaning (a) the damage is confined to that DB and (b) you didn't keep any important data in it, then dropping and recreating the "postgres" DB might be enough to get you out of trouble. But pg_authid_rolname_index is a cluster-global index, not specific to the "postgres" DB, so the fact that it too seems to be damaged is not promising. TBH your best bet, if the data in this installation is valuable and you don't have adequate backups, is to hire a professional data recovery service --- there are several companies that specialize in getting as much out of a corrupted PG installation as possible. (See https://www.postgresql.org/support/professional_support/ for some links.) You should then plan on updating to some newer PG release; 8.4.x has been out of support for years, and there are lots of known-and-unfixed bugs in it. regards, tom lane
Re: Should from_collapse be switched off? (queries 10 times faster)
On Fri, Mar 23, 2018 at 10:14:19AM -0400, Tom Lane wrote: ! It's conceivable that the OP's problem is actually planning time ! (if the query joins sufficiently many tables) and that restricting ! the cost of the join plan search is really what he needs to do. Negative. Plnning time 10 to 27 ms. Execution time 600 to 6300 ms. ! Lacking any further information about the problem, we can't say. ! We can, however, point to ! https://wiki.postgresql.org/wiki/Slow_Query_Questions ! concerning how to ask this type of question effectively. I strongly hope the data that I sent as followup will now suffice Your expectations. rgds, PMc
Slow planning time for custom function
Hi,
I have a table api.issues that has a text column "body" with long texts (1000+
chars). I also wrote a custom function "normalizeBody" with plv8 that is a
simple Text -> Text conversion. Now I created an index applying the function to
the body column, so I can quickly run
SELECT * FROM api.issues WHERE normalizeBody(body) = normalizeBody($1)
The issue is, that the planning time is very slow (1.8 seconds). When I replace
"normalizeBody" with "md5", however, I get a planning time of 0.5ms.
Please note that row level security is enabled on the api.issues and most other
tables.
Thanks for your help,
Ben
Details below:
- Managed AWS Postgres with default settings, no replication
- PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120
(Red Hat 4.8.2-16), 64-bit
- Table api.issues has approx. 40 000 rows.
```
explain (analyze, buffers) select 1 from api.issues
where normalizeunidiff(body) = normalizeunidiff('');
QUERY PLAN
--
Index Scan using rejectedissues_normalized on issues (cost=0.00..218.80
rows=217 width=4) (actual time=0.160..0.204 rows=3 loops=1)
Index Cond: (normalizeunidiff(body) = ''::text)
Buffers: shared hit=5
Planning time: 1878.783 ms
Execution time: 0.230 ms
(5 rows)
```
```
explain (analyze, buffers) select 1 from api.issues
where md5(body) = md5('');
QUERY PLAN
---
Index Scan using rejectedissues_md5 on issues (cost=0.00..218.80 rows=217
width=4) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: (md5(body) = 'd41d8cd98f00b204e9800998ecf8427e'::text)
Buffers: shared hit=2
Planning time: 0.565 ms
Execution time: 0.043 ms
(5 rows)
```
```
CREATE OR REPLACE FUNCTION public.normalizeunidiff(
unidiff text)
RETURNS text
LANGUAGE 'plv8'
COST 100
IMMUTABLE STRICT PARALLEL SAFE
AS $BODY$
return unidiff
.replace(/[\s\S]*@@/m, '') // remove header
.split('\n')
.map(function (line) { return line.trim() })
.filter(function (line) { return line.search(/^[+-]/) >= 0 })
.join('\n')
.trim()
$BODY$;
```
The indices are created this way where md5 is normalizeunidiff for the second
one:
```
CREATE INDEX "rejectedissues_md5"
ON api.issues using hash
(md5(body));
```
Re: Slow planning time for custom function
Hi, On 2018-03-23 21:28:22 +0100, b...@e8s.de wrote: > I have a table api.issues that has a text column "body" with long texts > (1000+ chars). I also wrote a custom function "normalizeBody" with plv8 that > is a simple Text -> Text conversion. Now I created an index applying the > function to the body column, so I can quickly run > > SELECT * FROM api.issues WHERE normalizeBody(body) = normalizeBody($1) > > The issue is, that the planning time is very slow (1.8 seconds). When I > replace "normalizeBody" with "md5", however, I get a planning time of 0.5ms. How long does planning take if you repeat this? I wonder if a good chunk of those 1.8s is initial loading of plv8. Greetings, Andres Freund
Re: Slow planning time for custom function
On 24 March 2018 at 14:35, Andres Freund wrote:
> How long does planning take if you repeat this? I wonder if a good chunk
> of those 1.8s is initial loading of plv8.
Maybe, but it also could be the execution of the function, after all,
the planner does invoke immutable functions:
# explain verbose select lower('TEST');
QUERY PLAN
---
Result (cost=0.00..0.01 rows=1 width=32)
Output: 'test'::text
(2 rows)
Would be interesting to see what changes without the IMMUTABLE flag.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
functions: VOLATILE performs better than STABLE
Given an arbitrary function fn(x) returning numeric.
Question: how often is the function executed?
A.
select fn('const'), fn('const');
Answer:
Twice.
This is not a surprize.
B.
select v,v from fn('const') as v; [1]
Answer:
Once.
C.
select v.v,v.v from (select fn('const') as v) as v;
Answer:
Once if declared VOLATILE.
Twice if declared STABLE.
Now this IS a surprize. It is clear that the system is not allowed to
execute the function twice when declared VOLATILE. It IS ALLOWED to
execute it twice when STABLE - but to what point, except prolonging
execution time?
Over all, VOLATILE performs better than STABLE.
[1] I seem to remember that I was not allowed to do this when I coded
my SQL, because expressions in the from clause must return SETOF, not
a single value. Now it seems to work.
