On Tue, Jun 4, 2013 at 12:57 AM, Ben Zeev, Lior lior.ben-z...@hp.com wrote:
No matter how I try to redesign the schema the indexes consume large amount
of memory,
About 8KB per index.
8KB per index -- is that a typo? that doesn't seem like a lot to me.
merlin
--
Sent via pgsql-hackers
No it isn't a typo,
All the tables are empty and all the indexes are empty
-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com]
Sent: Tuesday, June 04, 2013 16:10
To: Ben Zeev, Lior
Cc: Atri Sharma; Stephen Frost; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process
, but it doesn't log the catcache statistcs
Lior
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 16:44
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
* Ben Zeev, Lior (lior.ben-z...@hp.com
Sharma [mailto:atri.j...@gmail.com]
Sent: Monday, May 27, 2013 17:24
To: Stephen Frost
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
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
On Mon, May 27, 2013 at 10:23 AM, Atri Sharma atri.j...@gmail.com wrote:
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
On Mon, May 27, 2013 at 7:29 AM, Stephen Frost sfr...@snowman.net wrote:
* 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
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 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
...@gmail.com]
Sent: Monday, May 27, 2013 13:19
To: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
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
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
* 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?
* 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.
of memory
What is the reason to consume so much memory for empty indexes?
Thanks,
Lior
-Original Message-
From: Stephen Frost [mailto:sfr...@snowman.net]
Sent: Monday, May 27, 2013 15:16
To: Atri Sharma
Cc: Ben Zeev, Lior; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory
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
...@snowman.net]
Sent: Monday, May 27, 2013 15:43
To: Ben Zeev, Lior
Cc: Atri Sharma; Pg Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
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
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
Hackers
Subject: Re: [HACKERS] PostgreSQL Process memory architecture
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
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
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
38 matches
Mail list logo
