Re: [HACKERS] Support Parallel Query Execution in Executor
I am working with Solaris on SPARC almost exclusively and I believe Josh said that Sun was the one who found the bursty behavior with scans. Has it been confirmed that this is the case on all/most platforms? Myron Scott -Original Message- From: Tom Lane <[EMAIL PROTECTED]> Subj: Re: [HACKERS] Support Parallel Query Execution in Executor Date: Sun Apr 9, 2006 11:48 am Size: 1K To: Gregory Maxwell <[EMAIL PROTECTED]> cc: pgsql-hackers@postgresql.org "Gregory Maxwell" <[EMAIL PROTECTED]> writes: > On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> So before we go inventing complicated bits of code with lots of added >> overhead, we should first find out exactly why the system doesn't >> already work the way it's supposed to. > But is that really the behavior we should expect? Certainly. If the OS has readahead logic at all, it ought to think that a seqscan of a large table qualifies. Your arguments seem to question whether readahead is useful at all --- but they would apply *just as well* to an app doing its own readahead, which is what is really getting proposed in this thread. Before we go replacing a standard OS-level facility with our own version, we need to have a much clearer idea of why the OS isn't getting the job done for us. Otherwise we're likely to write a large amount of code and find out that it doesn't work very well either. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Database corruption help
Originally in psql-admin, but copied here at the request of Tom to.. Story so far, transaction log archiving went wrong causing the transaction log disk to fill up. Foolishly i deleted the unarchived transaction logs (early monday morning) which required a pg_resetxlog to get the db up and running again. Since then we've had some minor db corruption which has been fixed (mainly duplicate primary keys) except for the pg_class table. If i do a vacuum full on pg_class i get something like this: INFO: vacuuming "pg_catalog.pg_class"INFO: "pg_class": found 37 removable, 1845 nonremovable row versions in 18905 pages DETAIL: 27 dead row versions cannot be removed yet. Nonremovable row versions range from 160 to 229 bytes long. There were 933834 unused item pointers. Total free space (including removable row versions) is 150368692 bytes. 18839 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO: index "pg_class_oid_index" now contains 1813 row versions in 7 pages DETAIL: 56 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages DETAIL: 63 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING: index "pg_class_relname_nsp_index" contains 1818 row versions, but table contains 1813 row versions HINT: Rebuild the index with REINDEX.INFO: analyzing "pg_catalog.pg_class"INFO: "pg_class": scanned 3000 of 18905 pages, containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 estimated total rows Total query runtime: 4469 ms. As you can see there are non-removable dead rows (which slowly grows) and the table size is also increasing in size.. A reindex on the indexes mentions also succeeds but another vacuum reports the same thing... In the log files the relevant bits are: GMT LOG: relation "pg_class" TID 15538/4: dead HOT-updated tuple --- cannot shrink relation 2009-02-12 21:06:40 GMT STATEMENT: VACUUM FULL VERBOSE ANALYZE pg_class 2009-02-12 21:06:40 GMT WARNING: index "pg_class_relname_nsp_index" contains 1818 row versions, but table contains 1813 row versions and finally the last message in the psql-admin thread suggested dumping the above page, which is attached before. Hmm. The comments in vacuum.c about this case suppose that it could only be a transient condition, ie the tuple became dead while we were looking at the page. Evidently it's persistent for you, which means that for some reason heap_page_prune() is failing to remove an already-dead tuple. I suspect this implies a corrupt HOT chain, but maybe the data is okay and what you've got is really a bug in heap_page_prune. Could you send a dump of page 15538 of pg_class, using pg_filedump? The source code for it is here: http://sources.redhat.com/rhdb/utilities.html Best would be -i -f format, ie pg_filedump -i -f -R 15538 $PGDATA/base/something/1259 regards, tom lane Any help would be appreciated as the pg_class table is constantly growing which i'm guessing is going to start to affect performance fairly soon. I'd like to avoid a full restore from backup if possible. Thanks JOHN *** * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 * * File: /mnt/data/postgresql/8.3/base/16392/1259 * Options used: -i -f -R 15538 * * Dump created on: Thu Feb 12 22:30:43 2009 *** Block 15538 - Block Offset: 0x07964000 Offsets: Lower 220 (0x00dc) Block: Size 8192 Version4Upper5632 (0x1600) LSN: logid428 recoff 0xbce5c3a0 Special 8192 (0x2000) Items: 49 Free Space: 5412 TLI: 0x0001 Prune XID: 0x Flags: 0x0001 (HAS_FREE_LINES) Length (including item array): 220 : ac01 a0c3e5bc 01000100 dc16 0010: 00200420 . . 0020: 609f4001 c09e4001 209e4001 ..@...@. @. 0030: 809d4001 e09c4001 @...@. 0040: 409c4001 a09b4001 009b4001 609a4001 @@...@@.`.@. 0050: c0994001 20994001 80984001 @. @...@. 0060: e0974001 40974001 a0964001 ..@.@@...@. 0070: 00964001 @. 0080: 0090: 00a0: 00b0: 00c0:
Re: [HACKERS] Database corruption help
>Please send to pgsql-hackers --- I'd like to get more eyeballs on this. >There's no personally identifiable information here except that you've >got a table named temp_queue that you've repeatedly TRUNCATEd or >CLUSTERed or some such (likely the former since the reltuples counts >are all zero). It'd be useful to know exactly what you were doing >with it, though. > > regards, tom lane More info as requested TEMP_QUEUE is used continously throughout the day (for some reason which i chose not to use a normal temp table) data is grouped and inserted from other tables, before being processed, and you are right the table is then truncated before repeating - this happens at least once a minute throughout the day.. After restarted following the pg_resetxlog, i noticed that the temp_queue table was missing (had been created a day ago). I couldn't select from it and had problems trying to recreate it (bits seemed to exist already). I couldn't see it in pg_class or the other catalogs, but after selecting to ignore the presumably corrupt indexes it appeared so i manually removed the references (I've since learned about the -P option to the server which may have helped here). The corruption in pg_class manifested itself as multiple rows (for this and another table) with the same OIDs So not sure at what point what if anything got corrupted and what my subsequent actions did to compound this.. Thanks John Lister wrote: Originally in psql-admin, but copied here at the request of Tom to.. Story so far, transaction log archiving went wrong causing the transaction log disk to fill up. Foolishly i deleted the unarchived transaction logs (early monday morning) which required a pg_resetxlog to get the db up and running again. Since then we've had some minor db corruption which has been fixed (mainly duplicate primary keys) except for the pg_class table. If i do a vacuum full on pg_class i get something like this: INFO: vacuuming "pg_catalog.pg_class"INFO: "pg_class": found 37 removable, 1845 nonremovable row versions in 18905 pages DETAIL: 27 dead row versions cannot be removed yet. Nonremovable row versions range from 160 to 229 bytes long. There were 933834 unused item pointers. Total free space (including removable row versions) is 150368692 bytes. 18839 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO: index "pg_class_oid_index" now contains 1813 row versions in 7 pages DETAIL: 56 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages DETAIL: 63 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING: index "pg_class_relname_nsp_index" contains 1818 row versions, but table contains 1813 row versions HINT: Rebuild the index with REINDEX.INFO: analyzing "pg_catalog.pg_class"INFO: "pg_class": scanned 3000 of 18905 pages, containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 estimated total rows Total query runtime: 4469 ms. As you can see there are non-removable dead rows (which slowly grows) and the table size is also increasing in size.. A reindex on the indexes mentions also succeeds but another vacuum reports the same thing... In the log files the relevant bits are: GMT LOG: relation "pg_class" TID 15538/4: dead HOT-updated tuple --- cannot shrink relation 2009-02-12 21:06:40 GMT STATEMENT: VACUUM FULL VERBOSE ANALYZE pg_class 2009-02-12 21:06:40 GMT WARNING: index "pg_class_relname_nsp_index" contains 1818 row versions, but table contains 1813 row versions and finally the last message in the psql-admin thread suggested dumping the above page, which is attached before. Hmm. The comments in vacuum.c about this case suppose that it could only be a transient condition, ie the tuple became dead while we were looking at the page. Evidently it's persistent for you, which means that for some reason heap_page_prune() is failing to remove an already-dead tuple. I suspect this implies a corrupt HOT chain, but maybe the data is okay and what you've got is really a bug in heap_page_prune. Could you send a dump of page 15538 of pg_class, using pg_filedump? The source code for it is here: http://sources.redhat.com/rhdb/utilities.html Best would be -i -f format, ie pg_filedump -i -f -R 15538 $PGDATA/base/something/1259 regards, tom lane Any help would be appreciated as the pg_class table is constantly growing which i'm guessing is going to start to affect performance fairly so
Re: [HACKERS] Database corruption help
Cheers, i'll give it ago. I'm probably going to do a full restore over the weekend while i can shut things down without too many complaints... I can save any of the files if you are interested in them later on... JOHN Tom Lane wrote: John Lister writes: Any help would be appreciated as the pg_class table is constantly growing which i'm guessing is going to start to affect performance fairly soon. I'd like to avoid a full restore from backup if possible. BTW, what I would recommend as a recovery action is to zero out that page of pg_class while the postmaster is stopped. We know that none of those rows are useful to you, and there shouldn't be any index entries pointing at them (since they're all HOT tuples), so at least in theory that won't cause any damage. Then you can try another VACUUM FULL and see if there are any more pages with, er, issues. If you're on a machine that has /dev/zero then something like this should work: dd bs=8k count=1 seek=15538 conv=notrunc if=/dev/zero of=$PGDATA/base/16392/1259 but it'd be a good idea to save a copy of the target file so you can try again if you mess up. Also, it'd really be prudent to do a dump, initdb, reload once you get to a point where pg_dump succeeds without complaints. We don't have any good way to know what other corruption might be lurking undetected. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
This is something the client code would request (or not). It would not be sensible to try to force it from the server side, since if the client doesn't request it it's likely that the client wouldn't understand the data format. Cheers for the quick reply, any chance of a pointer to the protocol where the client specifies the return type(s) so i can check the client code (mainly jdbc) to see what is going on? Thanks JOHN -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
Does libpqtypes pass the array "over the wire" as an array? Ideally i'd like to do this with jdbc, but might give me a pointer... Thanks - Original Message - From: "Andrew Chernow" To: "John Lister" Cc: Sent: Tuesday, April 07, 2009 8:15 PM Subject: Re: [HACKERS] Array types John Lister wrote: They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs... You need libpqtypes (client-side library). It requires a new 8.4 feature called libpq-events, but there is an 8.3 patch available. If you willing to patch your 8.3.5 libpq client, than this should meet your needs. Downloads: http://pgfoundry.org/projects/libpqtypes/ Documentation: http://libpqtypes.esilo.com/ For arrays, libpqtypes gives you a PGresult where each tuple is an array item. For composite arrays, each composite field is a PGresult field. This would seem much better in terms of performance, both size and speed(conversion). That is true. Our testing has proven this. It also reduces the overall coding effort. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Array types
Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays. They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs... This would seem much better in terms of performance, both size and speed(conversion). Thanks -- Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Re: [HACKERS] Array types
Cheers, nice to know it is possible... Now to see if i can get java/python to do the same :) or to use a modified libpq somehow... Merlin Moncure wrote: On Tue, Apr 7, 2009 at 3:35 PM, John Lister wrote: Does libpqtypes pass the array "over the wire" as an array? Ideally i'd like to do this with jdbc, but might give me a pointer... We send/receive the server's array format. This is not quite a C array, and is definitely not a java array. It's a packed postgres specific (network byte order) format. It is much faster and tighter than text in some cases however. We present an interface to copy C arrays to postgres style formats such as: PGarray a; PQgetf(res, tuple_num, "%int4[]", field_num, &a); This will 'pop' a result out of your result that presents the array internals (which you could then loop). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
brilliant i'll give it a go... Now to sort out java :) James Pye wrote: On Apr 7, 2009, at 12:54 PM, John Lister wrote: Cheers, nice to know it is possible... Now to see if i can get java/python to do the same :) or to use a modified libpq somehow... http://python.projects.postgresql.org will do it for Python. =D tho, only supports Python 3, which is still quite new. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
Following this up, is there any docs on the binary wire format for arrays? Thanks - Original Message - From: John Lister To: pgsql-hackers@postgresql.org Sent: Tuesday, April 07, 2009 7:54 PM Subject: [HACKERS] Array types Hi, using v8.3.5 and a number of client libraries (java, python, pgadmin) and playing about with arrays. They all return arrays as text, is it possible to configure postgresql to return an array in native form (does postgresql support such a thing)? This is using both the simple and extended query forms - i couldn't see a way to say what return type i wanted in the protocol docs... This would seem much better in terms of performance, both size and speed(conversion). Thanks -- Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/
Re: [HACKERS] Array types
Cheers for the pointers. Am i right in thinking that if i get an array of
arrays, the nested arrays are sent in wire format as well - it seems to be
from the docs.
Secondly, comments are a bit scarse in the code, but am i also right in
thinking that an array indexing can start at an arbitrary value? This seems
to be what the lbound value is for... or is this a addition to deal with
nulls eg, {null, null, null, 4} would have a lbound of 3 (or both)
Thanks
- Original Message -
From: "Andrew Chernow"
To: "John Lister"
Cc:
Sent: Wednesday, April 08, 2009 4:07 PM
Subject: Re: [HACKERS] Array types
Andrew Chernow wrote:
John Lister wrote:
Following this up, is there any docs on the binary wire format for
arrays?
None that I know of.
Check out the backend source: (array_recv() and array_send() functions)
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/arrayfuncs.c?rev=1.154
Or, look at libpqtypes array.c:
http://libpqtypes.esilo.com/browse_source.html?file=array.c
Forgot to mention, this is not as simple as understanding the array
format. You have to understand the wire format for all types that can be
array elements. The array wire format serializes its array elements as
[elem_len][elem_data]. elem_data is the wire format of the array element
type, like an int, timestamp, polygon, bytea, etc... So once you unravel
the array container format, you still have to demarshal the type data.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
No unfortunately not, it is a JDBC type 4 java which is entirely written in java. I've patched (as pointed out in another list) the base version to handle binary data (still a couple of issues that seem unfinished) which has given me clues, but the patch only supports simple types. I'm looking to create translator for arrays now.. I was hoping to use the java type handling for the internals of the array and throw an error on any "unknown" ones... I only use ints and floats in my arrays, so may leave it at that if it works and look at doing it properly later... On Wed, Apr 8, 2009 at 10:48 AM, John Lister wrote: Following this up, is there any docs on the binary wire format for arrays? Thanks Does java wrap libpq? If so, your best bet is probably going to be to go the libpqtypes route. If you want help doing that, you are more than welcome to ask (probably should move this thread to the libqptypes list). If not, you are headed for a 'much bigger than it looks on the surface' challenge...there are a lot of types...trust me on this one. If you want help with libpqtypes you can ask on our list on pgfoundry. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Array types
On Wed, Apr 8, 2009 at 4:11 PM, John Lister
wrote:
Cheers for the pointers. Am i right in thinking that if i get an array of
arrays, the nested arrays are sent in wire format as well - it seems to
be
from the docs.
No, you can't easily get an array of arrays in Postgres. You can get
multi-dimensional arrays but that's one big array with multiple
dimensions. The text output form does look like an array of arrays
but they don't behave like you might think they would:
Cheers, it wasn't clear if you have an array of arrays of which the nested
ones were of a different type. but it looks like all the values have to be
the same type,
eg
select (array[array[1,2,3,4],array['test']])
fails..
this makes life simpler :)
Secondly, comments are a bit scarse in the code, but am i also right in
thinking that an array indexing can start at an arbitrary value? This
seems
to be what the lbound value is for... or is this a addition to deal with
nulls eg, {null, null, null, 4} would have a lbound of 3 (or both)
No, nulls are handled using a bitmap inside the array data structure.
Array bounds don't have to start at 1, they can start below 1 or above 1.
postgres=# select ('[-2:-1][5:8]={{1,2,3,4},{5,6,7,8}}'::int[])[-2][5];
int4
--
Somehow missed the bounds in the docs. Cheers that has cleared that up...
JOHN
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
