Re: [GENERAL] Issue with pg_toast tables
Hello 2012/11/26 Ranjeet Dhumal : > Hi All , > > Am facing one problem related to pg_toast table , its grown very huge upto > 31GB , even am vacuuming(not full) it daily , my fsm parameters are default, > can anyone tell how to decrease the size , if am firing any query on > gen_bulk_20121126 its response time is very slow as compared to few days > before . > For changing fsm parameters it will need restart , can anyone suggest me any > other way for decreasing the size of this pg_toast tables. > lazy vacuum doesn't decrease size Regards Pavel Stehule > > nspname |relname > |size | refrelname | > relidxrefrelname | relfilenode| relkind | reltuples| > relpages > -+++---+---+--+---++-- > pg_toast| pg_toast_123049508 > | 31 GB |gen_bulk_20121126| > | 123049512 | t | 16340229 | 4051494 > > > -- > --Regards > Ranjeet R. Dhumal > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Issue with pg_toast tables
Hi All , Am facing one problem related to pg_toast table , its grown very huge upto 31GB , even am vacuuming(not full) it daily , my fsm parameters are default, can anyone tell how to decrease the size , if am firing any query on gen_bulk_20121126 its response time is very slow as compared to few days before . For changing fsm parameters it will need restart , can anyone suggest me any other way for decreasing the size of this pg_toast tables. nspname |relname |size | refrelname | relidxrefrelname | relfilenode| relkind | reltuples| relpages -+++---+---+--+---++-- pg_toast| pg_toast_123049508 | 31 GB |gen_bulk_20121126| | 123049512 | t | 16340229 | 4051494 -- --Regards Ranjeet R. Dhumal
Re: [GENERAL] Regarding getting source from 9.2.0beta1 and 9.2beta1.
Hari Babu writes: > When I was trying get the source code from ftp source, I found that > 9.2.0beta1 and 9.2beta1 are pointing to > 9.2.0beta1 source code. Is it intentional or Is there any source code > difference between 9.2.0beta1 and 9.2beta1? We do not use version strings like "9.2.0beta1". Not sure where you found that. "9.2beta1" was the version string for that beta release, and then "9.2.0" was the first official release in the 9.2 series. In bygone days this sort of thing was somewhat dependent on the whims of whoever packaged a particular release tarball; but for the last few years we've used src/tools/version_stamp.pl, which is intentionally quite anal-retentive about what spellings it will allow. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regarding getting source from 9.2.0beta1 and 9.2beta1.
Hi, When I was trying get the source code from ftp source, I found that 9.2.0beta1 and 9.2beta1 are pointing to 9.2.0beta1 source code. Is it intentional or Is there any source code difference between 9.2.0beta1 and 9.2beta1? Regards, Hari babu.
Re: [GENERAL] High SYS CPU - need advise
RAID10 -- vlad On 11/24/2012 3:17 PM, Gavin Flower wrote: Curious, what is your RAID configuration? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
2012/11/25 Bexley Hall : > Hi Pavel, > > On 11/24/2012 9:47 PM, Pavel Stehule wrote: >> >> Hello >> >> you can try use plperl as cache >> >> >> http://okbob.blogspot.cz/2007/12/using-shared-as-table-cache-in-plperl.html > > > But how is this any different than just creating a named/shared > table manually? access to memory is faster than access to table - but it is limited. > > And, how do further/additional accesses (by other clients or > the same client) *augment* the shared table? > > In terms of my "application": > - Assume client A does a query that evaluates expensive_function() > for rows 1, 5 and 93 > - Client B does a query that evaluates expensive_function() for > rows 3, 5 and 97 > - Client C does a query that evaluates expensive_function() for > rows 93, 95 and 97 > (no one alters any of the data on which expensive_function() relies > in this time interval) > > Then, A should bear the cost of computing the results for 1, 5 and 93. > B should bear the cost of computing 3 and 97 -- but should be able to > benefit from A's computation of 5. C should bear the cost of computing > 95 but benefit from the previous computations of 93 and 97. > depends on implementation - probably you cannot to design a generic solution, but for some not wide defined tasks, you can find effective solutions. Regards Pavel > Thx, > --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large INSERT leads to "invalid memory alloc"
Stefan Froehlich writes:
> On Sun, Nov 25, 2012 at 06:57:22PM +0100, Pavel Stehule wrote:
>>> | INSERT INTO a (id, fkid, displayorder, name, description, internal,
>>> mimetype, mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar', E'Seefeld',0,
>>> E'application/octet-stream',decode('5261...0700', 'hex'),311484587);
>> Attention - BYTEA is not BLOB and although physical limit is 1G - real
>> limit is significantly less - depends on RAM - 7years ago we found so
>> practical limit is about 20MB.
> Oops, that's not too much. In the docs I've seen a 4-byte length
> descriptor, thus expected a size limit of 4 GB and felt quit safe
> with a maximum size of 300 MB.
I replicated this case and found that the immediate cause of the problem
is addlit() in the scanner, which is trying to double its work buffer
size until it's larger than the literal string --- so even though the
string is "only" 600MB, it tries to make a 1GB buffer.
We could fix that particular case but there are doubtless others.
It's not really a good idea to be pushing query texts of hundreds of
megabytes through the system. Quite aside from whether you'd hit the
1GB-per-alloc hard limit, the system is likely to make quite a few
copies of any constant value in the process of parsing/planning a query.
You might have better luck if you treated the large value as an
out-of-line parameter instead of a literal constant. Aside from dodging
the costs of a very large query string and a large Const value, you
could send the parameter value in binary and avoid hex-to-binary
conversion costs.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
Hi Kevin, On 11/25/2012 8:10 AM, Kevin Grittner wrote: Bexley Hall wrote: Specifically, I have several computationally expensive functions that derive their results from specific values of these base types. *Solely*. (For example, area() when applied to a given "circle" always yields the same result... though this is a trivial/inexpensive function, by comparison). I can define the base types to set aside space to store these results and cache them *in* the base type. Then, serve up these cached results when they are needed, again. With plan caching, this should (?) reduce the cost of repeated queries significantly without the need/benefit for caching the actual query results. (Is that true?) To guard against future enhancements to the server (e.g., if query caching is ever implemented, etc.), I assume that all such functions should declare themselves as IMMUTABLE? Or, does my update of the internal representation of the data values (i.e., to include the cached results of each of these functions) conflict with this declaration? As long as a call to a given function with a specific set of arguments always returns the same result, and there are no *user visible* side effects of the internal caching, I don't see a problem with declaring the functions immutable. OK. Out of curiosity, are you planning on using a process-local cache (which would start empty for each new connection) or are you planning to allocate shared memory somehow and coordinate access to that? I was planning on writing back the results of each successful function evaluation into the data type's internal representation. Ideally, back into PostgreSQL's "master copy" of the data (though I would settle for hiding it in an anonymous table behind a view, etc.) The point is NEVER to have to RE-evaluate any of these functions for the data on which they are evaluated once they have been evaluated (assuming the data themselves do not change). And, in doing so, make the results of each evaluation available to other clients regardless of the query which caused them to be evaluated. Thx, --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large INSERT leads to "invalid memory alloc"
2012/11/25 Stefan Froehlich :
> On Sun, Nov 25, 2012 at 06:57:22PM +0100, Pavel Stehule wrote:
>> > | INSERT INTO a (id, fkid, displayorder, name, description, internal,
>> > mimetype, mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar',
>> > E'Seefeld',0, E'application/octet-stream',decode('5261...0700',
>> > 'hex'),311484587);
>
>> Attention - BYTEA is not BLOB and although physical limit is 1G - real
>> limit is significantly less - depends on RAM - 7years ago we found so
>> practical limit is about 20MB.
>
> Oops, that's not too much. In the docs I've seen a 4-byte length
> descriptor, thus expected a size limit of 4 GB and felt quit safe
> with a maximum size of 300 MB.
>
you didn't read well - it a 4byte header - but some bites are
reserved. so theoretical limit is 1G
>> If you need more, use blobs instead or you can divide value to more blocks
>> http://www.fuzzy.cz/en/articles/storing-files-in-a-postgresql-database/
>
> Yes, storing large data objects in the file system is advisable for
> several reasons - we've had the same discussion times ago with mysql as
> well. But the decision was made to keep it in the database (and there is
> only one object of this size anyways). Rewriting the framework is not an
> option at the moment.
It highly depends on RAM and on used API - if you use prepared
statements and binary transmission, you probably significantly reduce
memory usage.
But I think so +/- 50MB is practical - and LO interface will be faster
and better.
Regards
Pavel
>
> If I fail to migrate this into postgresql, we'd rather cancel the
> transition.
>
> Stefan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
Hi Pavel, On 11/24/2012 9:47 PM, Pavel Stehule wrote: Hello you can try use plperl as cache http://okbob.blogspot.cz/2007/12/using-shared-as-table-cache-in-plperl.html But how is this any different than just creating a named/shared table manually? And, how do further/additional accesses (by other clients or the same client) *augment* the shared table? In terms of my "application": - Assume client A does a query that evaluates expensive_function() for rows 1, 5 and 93 - Client B does a query that evaluates expensive_function() for rows 3, 5 and 97 - Client C does a query that evaluates expensive_function() for rows 93, 95 and 97 (no one alters any of the data on which expensive_function() relies in this time interval) Then, A should bear the cost of computing the results for 1, 5 and 93. B should bear the cost of computing 3 and 97 -- but should be able to benefit from A's computation of 5. C should bear the cost of computing 95 but benefit from the previous computations of 93 and 97. Thx, --don -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large INSERT leads to "invalid memory alloc"
On Sun, Nov 25, 2012 at 06:57:22PM +0100, Pavel Stehule wrote:
> > | INSERT INTO a (id, fkid, displayorder, name, description, internal,
> > mimetype, mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar', E'Seefeld',0,
> > E'application/octet-stream',decode('5261...0700', 'hex'),311484587);
> Attention - BYTEA is not BLOB and although physical limit is 1G - real
> limit is significantly less - depends on RAM - 7years ago we found so
> practical limit is about 20MB.
Oops, that's not too much. In the docs I've seen a 4-byte length
descriptor, thus expected a size limit of 4 GB and felt quit safe
with a maximum size of 300 MB.
> If you need more, use blobs instead or you can divide value to more blocks
> http://www.fuzzy.cz/en/articles/storing-files-in-a-postgresql-database/
Yes, storing large data objects in the file system is advisable for
several reasons - we've had the same discussion times ago with mysql as
well. But the decision was made to keep it in the database (and there is
only one object of this size anyways). Rewriting the framework is not an
option at the moment.
If I fail to migrate this into postgresql, we'd rather cancel the
transition.
Stefan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What happens to a primary key b-tree index when a table tuple is deleted by a transaction?
On 25 November 2012 18:03, Luby Liao wrote: > If the b-tree changes for the transaction, would it not become broken for > other transactions? > Can anyone tell me how Postgres handles this? Thank you, Luby Unfortunately, that book is a little out of date. Even with a unique index, there can simultaneously be "duplicate" row versions (I emphasize the distinction between logical rows and physical row versions), provided that no two duplicate values are simultaneously visible to a snapshot - they cannot exist at the same "time". MVCC doesn't modify rows in place; in creates new row versions. So, just as with tables, btree indexes will have multiple row versions for the same logical row. There is one notable exception to this, though. There was an optimisation added to PostgreSQL 8.3 called HOT, or heap-only tuples. This optimisation allows Postgres to use clever tricks to get away with only having a new row version in tables, and not in each index, if and only if the UPDATE statement only affects non-indexed columns. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What happens to a primary key b-tree index when a table tuple is deleted by a transaction?
Hello 2012/11/25 Luby Liao : > Bruce Momjian's book says that (p109) > >> When POSTGRESQL updates a row, it keeps the old copy of the row in the >> table file and writes a new one. The old row is marked as expired, and used >> by other transactions still viewing the database in its prior state. >> Deletions are similarly marked as expired, but not removed from the table >> file. > > > If the b-tree changes for the transaction, would it not become broken for > other transactions? > Can anyone tell me how Postgres handles this? Thank you, Luby What I know - PostgreSQL doesn't modify btree when tuples are deleted. Regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What happens to a primary key b-tree index when a table tuple is deleted by a transaction?
Bruce Momjian's book says that (p109) When POSTGRESQL updates a row, it keeps the old copy of the row in the > table file and writes a new one. The old row is marked as expired, and used > by other transactions still viewing the database in its prior state. > Deletions are similarly marked as expired, but not removed from the table > file. If the b-tree changes for the transaction, would it not become broken for other transactions? Can anyone tell me how Postgres handles this? Thank you, Luby
Re: [GENERAL] large INSERT leads to "invalid memory alloc"
Hello
2012/11/25 Stefan Froehlich :
> While converting a mysql database into postgres, I stumbled over the
> following problem:
>
> | INSERT INTO a (id, fkid, displayorder, name, description, internal,
> mimetype, mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar', E'Seefeld',0,
> E'application/octet-stream',decode('5261...0700', 'hex'),311484587);
>
> As the value for "filesize" suggests, this is a very large BYTEA
> (formerly: LONGBLOB) entry with about 300 MB. This is untypical, all
> other fields are about a couple of MB and don't make any problems.
> This very line leads to:
usually you need 2-3 times memory than is query size for parsing and
execution - and you probably raise a internal check of max allocation
- it expects so any alloc over 1G is strange.
Attention - BYTEA is not BLOB and although physical limit is 1G - real
limit is significantly less - depends on RAM - 7years ago we found so
practical limit is about 20MB.
If you need more, use blobs instead or you can divide value to more blocks
http://www.fuzzy.cz/en/articles/storing-files-in-a-postgresql-database/
Regards
Pavel Stehule
>
> | sfroehli@host:~$ psql dbname < statement.sql
> | Password:
> | ERROR: invalid memory alloc request size 1073741824
>
> I have not found any configuration directive similar to mysqls
> "max_allowed_packet" to increase the buffer size. And besides, I
> don't understand, why postgres wants to allocate 1 GB to store
> 300 MB (which take 600 MB of ASCII text in the decode()-string).
>
> Any idea how to put this into the target database?
>
> Stefan
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] large INSERT leads to "invalid memory alloc"
While converting a mysql database into postgres, I stumbled over the
following problem:
| INSERT INTO a (id, fkid, displayorder, name, description, internal, mimetype,
mimedata, filesize) VALUES (73,6,5, E'Seefeld.rar', E'Seefeld',0,
E'application/octet-stream',decode('5261...0700', 'hex'),311484587);
As the value for "filesize" suggests, this is a very large BYTEA
(formerly: LONGBLOB) entry with about 300 MB. This is untypical, all
other fields are about a couple of MB and don't make any problems.
This very line leads to:
| sfroehli@host:~$ psql dbname < statement.sql
| Password:
| ERROR: invalid memory alloc request size 1073741824
I have not found any configuration directive similar to mysqls
"max_allowed_packet" to increase the buffer size. And besides, I
don't understand, why postgres wants to allocate 1 GB to store
300 MB (which take 600 MB of ASCII text in the decode()-string).
Any idea how to put this into the target database?
Stefan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query caching absent "query caching"
Bexley Hall wrote: > Specifically, I have several computationally expensive > functions that derive their results from specific values of > these base types. *Solely*. (For example, area() when > applied to a given "circle" always yields the same result... > though this is a trivial/inexpensive function, by comparison). > > I can define the base types to set aside space to store > these results and cache them *in* the base type. Then, serve > up these cached results when they are needed, again. With > plan caching, this should (?) reduce the cost of repeated > queries significantly without the need/benefit for caching the > actual query results. (Is that true?) > > To guard against future enhancements to the server (e.g., if > query caching is ever implemented, etc.), I assume that all > such functions should declare themselves as IMMUTABLE? Or, > does my update of the internal representation of the data > values (i.e., to include the cached results of each of these > functions) conflict with this declaration? As long as a call to a given function with a specific set of arguments always returns the same result, and there are no *user visible* side effects of the internal caching, I don't see a problem with declaring the functions immutable. Out of curiosity, are you planning on using a process-local cache (which would start empty for each new connection) or are you planning to allocate shared memory somehow and coordinate access to that? -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Revoke "drop database" even for superusers?
On Fri, 2012-11-23 at 23:56 -0200, Edson Richter wrote: > Wordeful! > > Guillaume, Thanks. > > I"ll give a try for few weeks in the development and test databases > before put in production. > Make sure you test it thoroughly. As I said, it's more an example code, than a production-ready code. If you find any issues with it, please tell me so that I can fix the code. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
