Hi,
I have a question regarding the memory consumption per process in PostgreSQL 9.2
Does each PostgreSQL process allocating in its own memory (Not shared memory) a
cache of all the database catalog which it access during the SQL execution?
I mean does each process holds all the catalog indexes
Does each PostgreSQL process allocating in its own memory (Not shared
memory) a cache of all the database catalog which it access during the SQL
execution?
I mean does each process holds all the catalog indexes data which it
accessed, all the catalog index statistics etc’ accessed
AFAIK,
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer cr...@2ndquadrant.com wrote:
On 05/25/2013 05:39 PM, Simon Riggs wrote:
- Switching to single-major-version release numbering. The number of
people who say PostgreSQL 9.x is amazing; even *packagers* get this
wrong and produce postgresql-9
Hello,
while playing with 9.3 Beta 1 on windows, I've found following small issue:
create table t as select 'a' from generate_series (1,20)
the warning is returned more than once:
WARNUNG: Spalte ?column? hat Typ unknown
DETAIL: Relation wird trotzdem erzeugt.
WARNUNG:
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
Hi Atri,
Thanks for your answer!
Do you have idea what may be the reason that PostgreSQL process consume more
memory when there are more partial indexes on the DB table?
Well, I am not too sure, but indexes always
Thanks Atri!
Do you know why PostgreSQL store the indexes in memory per process and not in
the shared memory?
Is there a way to prevent it store the indexes data per process, and force it
storing it in the shared memory?
Lior
-Original Message-
From: Atri Sharma
On 26 May 2013 16:35, Heikki Linnakangas hlinnakan...@vmware.com wrote:
My attempts to fix that look pretty ugly, so I'm not even going to
post them. I can stop the error on binary by causing errors on csv and
text, obviously not a fix. Any grammar based fix looks like it would
restrict the
On 26 May 2013 17:10, Tom Lane t...@sss.pgh.pa.us wrote:
Heikki Linnakangas hlinnakan...@vmware.com writes:
More readable would be to invent an intermediate nonterminal falling
between ColId and ColLabel, whose expansion would be IDENT |
unreserved_keyword | col_name_keyword |
On Monday, May 27, 2013 3:07 PM Marc Mamin wrote:
Hello,
while playing with 9.3 Beta 1 on windows, I've found following small
issue:
create table t as select 'a' from generate_series (1,20)
the warning is returned more than once:
WARNUNG: Spalte ?column? hat Typ unknown
On Mon, May 27, 2013 at 3:55 PM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
Thanks Atri!
Do you know why PostgreSQL store the indexes in memory per process and not in
the shared memory?
Is there a way to prevent it store the indexes data per process, and force it
storing it in the shared
On Mon, May 27, 2013 at 7:25 PM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
Thanks Atri!
Do you know why PostgreSQL store the indexes in memory per process and not in
the shared memory?
Is there a way to prevent it store the indexes data per process, and force it
storing it in the shared
An index is built in backend process's local memory, but, when
accessing, index pages are stored in shared memory. That is, for
example, when an index scan is performed, index pages are brought into
shared memory and accessed from there.
Yes, brought into the shared disk buffers and
On Sun, May 26, 2013 at 09:18:41PM -0400, Stephen Frost wrote:
* Josh Berkus (j...@agliodbs.com) wrote:
and it's entirely possible that we'll be able to implement SMs without
breaking pgupgrade.
I'd certainly hope so.. It's certainly not obvious, to me at least,
why a new SM or
* Atri Sharma (atri.j...@gmail.com) wrote:
Does each PostgreSQL process allocating in its own memory (Not shared
memory) a cache of all the database catalog which it access during the SQL
execution?
This information is pulled into a backend-local cache, but it should
only be cached while
* Atri Sharma (atri.j...@gmail.com) wrote:
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
Do you have idea what may be the reason that PostgreSQL process consume
more memory when there are more partial indexes on the DB table?
It might use a bit more, but it
AFAIK, the shared disk buffers are the only part shared between the
processes.
There's a bit of other information shared, but disk buffers are
certainly the bulk of it.
The other information being locks?
Regards,
Atri
--
Regards,
Atri
l'apprenant
--
Sent via pgsql-hackers mailing
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
Do you know why PostgreSQL store the indexes in memory per process and not in
the shared memory?
The disk blocks from an index are not stored per-process, they are kept
in shared memory. When building an index, PG can only use one process
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
Does each PostgreSQL process allocating in its own memory (Not shared memory)
a cache of all the database catalog which it access during the SQL execution?
PG will look up and cache the catalog information regarding all of the
relations
* Atri Sharma (atri.j...@gmail.com) wrote:
If your index is big/you have too many indexes in your database, it
should affect *all* backends accessing that specific database.
More indexes will require more disk space, certainly, but tablespaces
can be used to seperate databases, or tables, or
A better place to look would be the documentation for the release of PG
which you are on, or the latest release otherwise, which is:
http://www.postgresql.org/docs/9.2/static/storage.html
Oops,yes,sorry about that.
Thanks a ton for pointing that out.
Regards,
Atri
--
Regards,
Atri
This is not generally a reason to avoid indexes. Indexes require more
disk space and must be kept up to date, making them expensive to
maintain due to increased disk i/o. Building an index uses as much
memory as it's allowed to- it uses maintenance_work_mem to limit itself.
Yes, too many
On Mon, May 27, 2013 at 9:16 PM, Atri Sharma atri.j...@gmail.com wrote:
AFAIK, the shared disk buffers are the only part shared between the
processes.
There's a bit of other information shared, but disk buffers are
certainly the bulk of it.
The other information being locks?
* Bruce Momjian (br...@momjian.us) wrote:
If I had to _guess_, I would say users who are using the default storage
manager would still be able to use pg_upgrade, and those using
non-default storage managers perhaps can't.
That would make sense.
But, again, this is all so hypothetical that it
* Atri Sharma (atri.j...@gmail.com) wrote:
There's a bit of other information shared, but disk buffers are
certainly the bulk of it.
The other information being locks?
Depends, but yes. Per-row locks are actually in the disk cache portion
of shared buffers, but heavyweight locks have
* Atri Sharma (atri.j...@gmail.com) wrote:
Yes, too many indexes wont hurt much.BTW,wont making too many indexes
on columns that probably dont have as many values as to deserve
them(so,essentially,indiscriminately making indexes) hurt the
performance/memory usage?
I'd expect the performance
I'd expect the performance issue would be from planner time more than
memory usage- but if there is a serious memory usage issue here, then
it'd be valuable to have a test case showing what's happening. We may
not be releasing the sys cache in some cases or otherwise have a bug in
this area.
Hi Stephen,
The case which I'm seeing is that I have an empty table without any rows,
Create table test (
Num Integer,
C1 character varying(512),
C2 character varying(512),
C3 character varying(512));
I create several partial indexes on this table:
Create index(index_1_c1) on test(c1)
On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
Hi Stephen,
The case which I'm seeing is that I have an empty table without any rows,
Create table test (
Num Integer,
C1 character varying(512),
C2 character varying(512),
C3 character varying(512));
I
Hi Atri,
But TOAST only occur if the tuple size exceed 2KB, doesn't it?
Lior
-Original Message-
From: Atri Sharma [mailto:atri.j...@gmail.com]
Sent: Monday, May 27, 2013 15:39
To: Ben Zeev, Lior
Cc: Stephen Frost; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
The case which I'm seeing is that I have an empty table without any rows,
Create table test (
Num Integer,
C1 character varying(512),
C2 character varying(512),
C3 character varying(512));
I create several partial indexes on
* Atri Sharma (atri.j...@gmail.com) wrote:
It is just a hunch, but all of your attributes are character varying.
Could TOAST be an issue here?
TOAST tables are only created when needed. In addition, I believe
Lior's concerned about memory utilization and not disk usage; memory
utilization
On 05/27/2013 01:25 PM, Ben Zeev, Lior wrote:
Thanks Atri!
Do you know why PostgreSQL store the indexes in memory per process and not in
the shared memory?
From shared_buffers point of view tables and indexes are identical, both
use the
same shared memory in (usually) 8KB pages
Is there a
Hi Stephen,
Yes, The memory utilization per PostgreSQL backend process is when running
queries against this tables,
For example: select * from test where num=2 and c2='abc'
When It start it doesn't consume to much memory,
But as it execute against more and more indexes the memory consumption
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
Yes, The memory utilization per PostgreSQL backend process is when running
queries against this tables,
For example: select * from test where num=2 and c2='abc'
When It start it doesn't consume to much memory,
But as it execute against
Hi all
I know this topic was discussed before, but there doesn't seem to be
any conclusion.
The lack of unsigned integer types is one of the biggest sources of
grief in my daily work with pgsql.
Before I go and start hacking, I'd like to discuss few points:
1. Is there a strong objection
On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote:
* Bruce Momjian (br...@momjian.us) wrote:
If I had to _guess_, I would say users who are using the default storage
manager would still be able to use pg_upgrade, and those using
non-default storage managers perhaps can't.
On 05/26/2013 06:18 PM, Josh Berkus wrote:
Not sure which ones Simon meant, but at least any new/better
storage manager would seem to me to be requiring
a non-pg_upgrade upgrade path unless we require the storage manager
to also include a parallel implementation of pg_upgrade.
Isn't this a
Hi Stephen,
Each query is running in a separate transaction.
Why does portioning is done better rather than using partial index?
Thanks,
Lior
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 16:15
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg
Lior,
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
Yes, The memory utilization per PostgreSQL backend process is when running
queries against this tables,
For example: select * from test where num=2 and c2='abc'
When It start it doesn't consume to much memory,
But as it execute against
Great, Thanks !!!
I will try and let you update
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 16:29
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
Lior,
* Ben Zeev, Lior
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote:
Each query is running in a separate transaction.
Interesting. You might also compile with CATCACHE_STATS (and not
CATCACHE_FORCE_RELEASE, or perhaps with and without) and then check out
your logs after the process ends (you might need to increase
Maciej Gajewski wrote:
I know this topic was discussed before, but there doesn't seem to be
any conclusion.
The lack of unsigned integer types is one of the biggest sources of
grief in my daily work with pgsql.
Before I go and start hacking, I'd like to discuss few points:
1. Is there
Maciej Gajewski maciej.gajews...@gmail.com writes:
The lack of unsigned integer types is one of the biggest sources of
grief in my daily work with pgsql.
Before I go and start hacking, I'd like to discuss few points:
1. Is there a strong objection against merging this kind of patch?
We may still be able to do better than what we're doing
today, but I'm still suspicious that you're going to run into other
issues with having 500 indexes on a table anyway.
+1. I am suspicious that the large number of indexes is the problem
here,even if the problem is not with book keeping
Marc Mamin m.ma...@intershop.de writes:
while playing with 9.3 Beta 1 on windows, I've found following small issue:
create table t as select 'a' from generate_series (1,20)
the warning is returned more than once:
I can't duplicate this either.
Abfrage war erfolgreich durchgeführt:
On Mon, May 27, 2013 at 1:42 AM, Gurjeet Singh gurj...@singh.im wrote:
Joking about 640K aside, it doesn't seem reasonable to expect a truly
enormous query as is generated by the broken forms of this logic to turn
out happily. I'd rather fix Slony (as done in the above patch).
Yes, by
Bruce Momjian br...@momjian.us writes:
On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote:
That said, many discussions and ideas do get shut down, perhaps too
early, because of pg_upgrade considerations. If we had a plan to have
an incompatible release in the future, those ideas
Simon Riggs si...@2ndquadrant.com writes:
On 26 May 2013 17:10, Tom Lane t...@sss.pgh.pa.us wrote:
More readable would be to invent an intermediate nonterminal falling
between ColId and ColLabel, whose expansion would be IDENT |
unreserved_keyword | col_name_keyword | type_func_name_keyword,
It's been brought to my attention that I forgot to email hackers about us
adding new committers to the project, as I planned to do when I wrote my
blog post about it.
This is the same people as were announced during the pgcon closing session
- Jeff Davis, Stephen frost, fujii masao and Noah
Hi Tom
You are right: UNION ALL is correct in terms of contents (tables
contents are disjunct) and of performance (no separate sort required
theoretically).
In my specific case even with UNION ALL the planner still chose a Seq Scan.
Note that there is a KNN index with ORDER BY ... - ... involved.
Michael Paquier escribió:
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer cr...@2ndquadrant.com wrote:
On 05/25/2013 05:39 PM, Simon Riggs wrote:
- Switching to single-major-version release numbering. The number of
people who say PostgreSQL 9.x is amazing; even *packagers* get this
wrong
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Yes, we should be collecting things we want to do for a pg_upgrade break
so we can see the list all in one place.
Precisely. We've said right along that we reserve the right to have a
non-upgradable disk format change whenever
On Mon, May 27, 2013 at 09:17:50AM -0400, Bruce Momjian wrote:
That said, many discussions and ideas do get shut down, perhaps too
early, because of pg_upgrade considerations. If we had a plan to have
an incompatible release in the future, those ideas and discussions might
be able to
On 27 May 2013 15:36, Tom Lane t...@sss.pgh.pa.us wrote:
Bruce Momjian br...@momjian.us writes:
On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote:
That said, many discussions and ideas do get shut down, perhaps too
early, because of pg_upgrade considerations. If we had a plan to
which makes me think that you're using some other client application
Hello,
Tom is right ( as always :-) :
http://postgresql.1045698.n5.nabble.com/bug-repeated-messages-in-pgadmin-1-18-0-Alpha-1-query-tool-messages-pane-td5755749.html
sorry for the disturbance.
Marc Maminm
Bruce Momjian br...@momjian.us writes:
On Mon, May 27, 2013 at 09:17:50AM -0400, Bruce Momjian wrote:
Yes, we should be collecting things we want to do for a pg_upgrade break
so we can see the list all in one place.
OK, I have added a section to the TODO list for this:
Desired changes
Bruce Momjian wrote:
OK, I have added a section to the TODO list for this:
Desired changes that would prevent upgrades with pg_upgrade
32-bit page checksums
Are there any others?
I would have each data segment be self-identifying, i.e. have a magic
number at the
On Mon, May 27, 2013 at 10:32 AM, Magnus Hagander mag...@hagander.net wrote:
It's been brought to my attention that I forgot to email hackers about us
adding new committers to the project, as I planned to do when I wrote my
blog post about it.
This is the same people as were announced during
On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera
alvhe...@2ndquadrant.comwrote:
Michael Paquier escribió:
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer cr...@2ndquadrant.com
wrote:
On 05/25/2013 05:39 PM, Simon Riggs wrote:
- Switching to single-major-version release numbering. The
On Tue, May 28, 2013 at 07:39:35AM +0900, Michael Paquier wrote:
On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera
alvhe...@2ndquadrant.comwrote:
Michael Paquier escribió:
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer cr...@2ndquadrant.com
wrote:
On 05/25/2013 05:39 PM, Simon
Michael Paquier escribió:
On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera
alvhe...@2ndquadrant.comwrote:
You do -- they are used for minor releases, i.e. 10.1 would be a bugfix
release for 10.0. If we continue using the current numbering scheme,
10.1 would be the major version after
On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote:
Well, it's easy.
Instead of PLyFloat_FromNumeric[0], you can make a
PLyDecimal_FromNumeric.
Please send a patch. This would be a welcome addition.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make
On Tue, May 28, 2013 at 7:52 AM, David Fetter da...@fetter.org wrote:
What's been proposed before that wouldn't break previous applications
is a numbering system like this:
10.0.0
10.0.1
10.0.2
10.0.3
...
11.0.0
11.0.1
i.e. only change the most-major version number and always leave
On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut pete...@gmx.net wrote:
On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote:
Well, it's easy.
Instead of PLyFloat_FromNumeric[0], you can make a
PLyDecimal_FromNumeric.
Please send a patch. This would be a welcome addition.
I can write
On 05/27/2013 05:45 PM, Michael Paquier wrote:
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer cr...@2ndquadrant.com wrote:
On 05/25/2013 05:39 PM, Simon Riggs wrote:
- Switching to single-major-version release numbering. The number of
people who say PostgreSQL 9.x is amazing; even *packagers*
On 05/28/2013 07:22 AM, Michael Paquier wrote:
On Tue, May 28, 2013 at 7:52 AM, David Fetter da...@fetter.org wrote:
What's been proposed before that wouldn't break previous applications
is a numbering system like this:
10.0.0
10.0.1
10.0.2
10.0.3
...
11.0.0
11.0.1
i.e. only change
On Mon, 2013-05-27 at 20:43 -0300, Claudio Freire wrote:
On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut pete...@gmx.net wrote:
On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote:
Well, it's easy.
Instead of PLyFloat_FromNumeric[0], you can make a
PLyDecimal_FromNumeric.
On 05/28/2013 12:41 AM, Simon Riggs wrote:
I'm happy with that.
I was also thinking about collecting changes not related just to disk
format, if any exist.
Any wire protocol or syntax changes?
I can't seem to find a things we want to do in wire protocol v4 doc in
the wiki but I know I've seen
On 02/11/2013 07:27 AM, Jeff Janes wrote:
I created doBenchMarkConnect() to segregate bench-marking connections from
utility connections. At first I thought of adding the startup code to only
the normal path and leaving support for -C in the wind, but decided that
was just lazy.
That sounds
On 05/03/2013 07:09 AM, Andres Freund wrote:
We've got that in 9.3 which is absolutely fabulous! But that's not
related to doing DMA which you cannot (and should not!) do from
userspace.
You can do zero-copy DMA directly into userspace buffers. It requires
root (or suitable capabilities that
On 05/27/2013 04:58 PM, Craig Ringer wrote:
On 05/28/2013 12:41 AM, Simon Riggs wrote:
I'm happy with that.
I was also thinking about collecting changes not related just to disk
format, if any exist.
Any wire protocol or syntax changes?
I can't seem to find a things we want to do in wire
On 28/05/13 11:48, Craig Ringer wrote:
On 05/27/2013 05:45 PM, Michael Paquier wrote:
On Mon, May 27, 2013 at 2:01 PM, Craig Ringercr...@2ndquadrant.com wrote:
On 05/25/2013 05:39 PM, Simon Riggs wrote:
- Switching to single-major-version release numbering. The number of
people who say
On 05/28/2013 09:39 AM, Gavin Flower wrote:
Yes, I hate the Firefox style number inflation.
I was arguing *for* it ;-)
I don't like it much either, but (a) we do about one release a year, not
one every few weeks and (b) it's very clear from a quick look at Stack
Overflow or first-posts to
On 05/02/2013 12:56 AM, Greg Smith wrote:
On 5/1/13 4:57 AM, Fabien COELHO wrote:
The use case of the option is to be able to generate a continuous gentle
load for functional tests, eg in a practice session with students or for
testing features on a laptop.
If you add this to
On 05/27/2013 06:53 PM, Craig Ringer wrote:
On 05/28/2013 09:39 AM, Gavin Flower wrote:
Yes, I hate the Firefox style number inflation.
I was arguing *for* it ;-)
I don't like it much either, but (a) we do about one release a year, not
one every few weeks and (b) it's very clear from a
On 05/16/2013 01:44 AM, Josh Berkus wrote:
I'll also say:
* we need to assign CF managers at least 2 weeks in advance of each CF *
we need to replace them if they get too busy to follow-through,
* and the last CF needs two managers.
Strong +1 on both of those.
I tried to pick up a CF that was
On 05/17/2013 11:38 AM, Robert Haas wrote:
maybe with a bit of modest pre-extension.
When it comes to pre-extension, is it realistic to get a count of
backends waiting on the lock and extend the relation by (say) 2x the
number of waiting backends?
Getting a list of lock waiters is always a racey
On 05/18/2013 03:15 AM, Josh Berkus wrote:
The drawback to this is whatever size we choose is liable to be wrong
for some users. Users who currently have a lot of 16K tables would see
their databases grow alarmingly.
This only becomes a problem for tables that're tiny, right? If your
table is
On 05/11/2013 03:25 AM, Robert Haas wrote:
Not really. We could potentially fix it by extending the wire
protocol to allow the server to respond to the client's startup packet
with a further challenge, and extend libpq to report that challenge
back to the user and allow sending a response.
On 28 May 2013 01:55, Peter Eisentraut pete...@gmx.net wrote:
On Mon, 2013-05-27 at 20:43 -0300, Claudio Freire wrote:
On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut pete...@gmx.net
wrote:
On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote:
Well, it's easy.
Instead of
80 matches
Mail list logo
