Re: RAM usage of PostgreSql
Prasad wrote: > I have installed PostgreSQL 9.4 (open source) version on my CentOS > Linux Red Hat 7 production server and kept default parameters which > are in postgresql.conf file.So my basic question is, once I start > using postgres how much RAM the postgres processes consumes > (postgres related processes only). > > There are lot of allocations in postgresql.conf file, for example > shared_buffers, work_mem...etc. > > As per my knowledge, all postgres processes should not consume the > RAM more than the value assigned in shared_buffers.Please clarify > and let me know if I misunderstand the concept.. shared_buffers only determines the shared memory cache, each database process still needs private memory. As a rule of thumb, start with shared_buffers set to 1/4 of your available RAM, but no more than 8GB. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: monitoring options for postgresql under AWS/RDS?
Hello Eric, To start with, you can set log_min_duration_statement to 1500ms and log_statement to the required one which will give you the statement that ran for more than 1.5 s. Then you know what to do! For tools: 1. pgcluu2. PoWA Best Regards,Rijo Roy On Thursday, 4 April, 2019, 11:07:35 pm IST, Mamet, Eric (GfK) wrote: Hi there, I would like to monitor our postgresql instance under AWS-RDS to get some alert (or log) if any query runs over a certain amount of time, like 1.5 seconds. I would like to know which query took over that time (and how long), when and which parameters it used. The exact parameters are important because the amount of data retrieved varies a lot depending on parameters. I would like to know when it happened to be able to correlate it with the overall system activity. I came across ·pg_stat_statements is very useful BUT it gives me stats rather than specific executions. In particular, I don’t know the exact time it happened and the parameters used ·log_statement but this time I don’t see how I would filter on “slow” queries and it seems dumped into the RDS log… not very easy to use and maybe too heavy for a production system ·pg_hero is great but looks like an interactive tool (unless I missed something) and I don’t think it gives me the exact parameters and time (not sure…) Is there a tool I could use to achieve that? Thanks Eric
Re: RAM usage of PostgreSql
Hi, |Cc: pgsql-nov...@postgresql.org, pgsql-performa...@postgresql.org Please don't cross post to multiple lists. On Thu, Apr 04, 2019 at 08:18:01PM +0530, Prasad wrote: > There are lot of allocations in postgresql.conf file, for example > shared_buffers, work_mem...etc. > > As per my knowledge, all postgres processes should not consume the RAM more > than the value assigned in shared_buffers.Please clarify and let me know if > I misunderstand the concept.. shared_buffers is what's *reserved* for postgres and unavailable for other processes whenever PG is running. work_mem is what each postgres process might use, if needed. When complete, that's returned to the OS. Note that an expensive query might actually use some multiple of work_mem (it's per sort/hash node and also per parallel process, and also hash aggregate can sometimes use more than work_mem). Justin
monitoring options for postgresql under AWS/RDS?
Hi there, I would like to monitor our postgresql instance under AWS-RDS to get some alert (or log) if any query runs over a certain amount of time, like 1.5 seconds. I would like to know which query took over that time (and how long), when and which parameters it used. The exact parameters are important because the amount of data retrieved varies a lot depending on parameters. I would like to know when it happened to be able to correlate it with the overall system activity. I came across * pg_stat_statements is very useful BUT it gives me stats rather than specific executions. In particular, I don't know the exact time it happened and the parameters used * log_statement but this time I don't see how I would filter on "slow" queries and it seems dumped into the RDS log... not very easy to use and maybe too heavy for a production system * pg_hero is great but looks like an interactive tool (unless I missed something) and I don't think it gives me the exact parameters and time (not sure...) Is there a tool I could use to achieve that? Thanks Eric
Re: Commit(?) overhead
On Thu, Apr 4, 2019 at 3:42 AM Duncan Kinnear wrote: > > the EXPLAIN (ANALYSE, TIMING TRUE) of this query gives: > > Update on next_id (cost=0.14..8.16 rows=1 width=36) (actual > time=0.057..0.057 rows=0 loops=1) >-> Index Scan using next_id_pk on next_id (cost=0.14..8.16 rows=1 > width=36) (actual time=0.039..0.040 rows=1 loops=1) > Index Cond: ((id)::text = 'Session'::text) > Planning Time: 0.083 ms > Execution Time: 0.089 ms > > which is significantly less than 50ms. > The EXPLAIN ANALYZE doesn't include the time needed to fsync the transaction logs. It measures only the update itself, not the implicit commit at the end. DBeaver is seeing the fsync-inclusive time. 50ms is pretty long, but some file systems and OSes seem to be pretty inefficient at this and take several disk revolutions to get the data down. > > Now, if I point DBeaver to a VM server on the same gigabit network switch, > running version: >9.5.3 on i386-pc-solaris2.11, compiled by cc: Sun C 5.10 SunOS_i386 > Patch 142363-07 2010/12/09, 64-bit > then the same query executes in about 2-3ms > That machine probably has hardware to do a fast fsync, has fsync turned off, or is lying about the safety of its data. Cheers, Jeff
RAM usage of PostgreSql
Hi, I have installed PostgreSQL 9.4 (open source) version on my CentOS Linux Red Hat 7 production server and kept default parameters which are in postgresql.conf file.So my basic question is, once I start using postgres how much RAM the postgres processes consumes (postgres related processes only). There are lot of allocations in postgresql.conf file, for example shared_buffers, work_mem...etc. As per my knowledge, all postgres processes should not consume the RAM more than the value assigned in shared_buffers.Please clarify and let me know if I misunderstand the concept.. -- Thanks, Venkata Prasad
Commit(?) overhead
We have a very simple table, whose DDL is as follows: CREATE TABLE public.next_id ( id varchar(255) NOT NULL, next_value int8 NOT NULL, CONSTRAINT next_id_pk PRIMARY KEY (id) ); The table only has about 125 rows, and there are no indexes apart from the primary key constraint. In DBeaver I am executing the following UPDATE query: UPDATE next_id SET next_value=next_value+1 WHERE id='Session'; If I point DBeaver to a server (localhost) running version: 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20181127, 64-bit it executes on average in about 50ms. the EXPLAIN (ANALYSE, TIMING TRUE) of this query gives: Update on next_id (cost=0.14..8.16 rows=1 width=36) (actual time=0.057..0.057 rows=0 loops=1) -> Index Scan using next_id_pk on next_id (cost=0.14..8.16 rows=1 width=36) (actual time=0.039..0.040 rows=1 loops=1) Index Cond: ((id)::text = 'Session'::text) Planning Time: 0.083 ms Execution Time: 0.089 ms which is significantly less than 50ms. Now, if I point DBeaver to a VM server on the same gigabit network switch, running version: 9.5.3 on i386-pc-solaris2.11, compiled by cc: Sun C 5.10 SunOS_i386 Patch 142363-07 2010/12/09, 64-bit then the same query executes in about 2-3ms The EXPLAIN output when executing the query on this server is: Update on next_id (cost=0.27..8.29 rows=1 width=36) (actual time=0.062..0.062 rows=0 loops=1) -> Index Scan using next_id_pkey on next_id (cost=0.27..8.29 rows=1 width=36) (actual time=0.025..0.026 rows=1 loops=1) Index Cond: ((id)::text = 'Session'::text) Planning time: 0.083 ms Execution time: 0.096 ms which you will see is virtually identical to the slower version. Why is the query taking so much longer on the localhost server? Not that the localhost machine is significantly faster in other metrics (CPU, file system, etc.) I have also tried the query on another server on the same network switch running version: 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.0, 64-bit and the timings are very similar to those for 'localhost'. That is, approx 50ms on average. Now, if I run the following FOR LOOP query: do $$ begin for i in 1..1 loop update NEXT_ID set next_value=next_value+1 where id='Session'; end loop; end; $$; Then this completes in about the same time on ALL of the servers - approximately 1.7s - which makes sense as 10,000 times the above plan/execute times is approx 1.7s. So, to me this feels like some kind of COMMIT overhead of approx 50ms that the version 10 and version 11 servers are experiencing. But I have no idea where to look to try and find where this time is being spent. Note that the schemas of the databases on the 3 servers involved are virtually identical. The schema for this table is exactly the same. Hoping that someone can give me an idea about where to go looking. Regards, Duncan Kinnear Floor 1, 100 McLeod St, Hastings 4120, New Zealand PO Box 2006, Hastings 4153, New Zealand P: +64 6 871 5700 F: +64 6 871 5709 E: duncan.kinn...@mccarthy.co.nz
