Re: [PERFORM] Very poor read performance, query independent

On Wed, Jul 12, 2017 at 3:04 AM, Charles Nadeau wrote: > Jeff, > > Here are the 2 EXPLAINs for one of my simplest query: > It looks like dstexterne and flowcompact are both views over flow. Can you share the definition of those views? I think the iowait > 12.5% is

Re: [PERFORM] Postgres Dump - Creating index never stops

Hans Braxmeier writes: > After restarting postgres (even with a new cluster) and creating a new > database, postgres is hanging while extracting the dump: gunzip -c pixabay.gz > | psql pixabay > The log file shows that the autovacuum task is running (almost)

[PERFORM] Postgres Dump - Creating index never stops

Hello Experts, we have created a postgres dump using this command: pg_dump pixabay | gzip > pixabay.gz After restarting postgres (even with a new cluster) and creating a new database, postgres is hanging while extracting the dump: gunzip -c pixabay.gz | psql pixabay The log file shows

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Charles Nadeau Sent: Wednesday, July 12, 2017 6:05 AM To: Jeff Janes > Cc:

Re: [PERFORM] Very poor read performance, query independent

From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Charles Nadeau Sent: Wednesday, July 12, 2017 6:05 AM To: Jeff Janes Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query

Re: [PERFORM] Very poor read performance, query independent

On Wed, Jul 12, 2017 at 12:30 AM, Charles Nadeau wrote: > > I use noop as the scheduler because it is better to let the RAID > controller re-arrange the IO operation before they reach the disk. Read > ahead is set to 128: > > charles@hpdl380g6:~$ cat

Re: [PERFORM] Very poor read performance, query independent

On Wed, Jul 12, 2017 at 9:38 AM, Charles Nadeau wrote: > Rick, > > Should the number of page should always be correlated to the VmPeak of the > postmaster or could it be set to reflect shared_buffer or another setting? > Thanks! > > The documentation implies that you

Re: [PERFORM] Very poor read performance, query independent

From: Charles Nadeau [mailto:charles.nad...@gmail.com] Sent: Wednesday, July 12, 2017 3:21 AM To: Igor Neyman Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent Igor, I set shared_buffers to 24 GB and

Re: [PERFORM] Very poor read performance, query independent

Rick, Should the number of page should always be correlated to the VmPeak of the postmaster or could it be set to reflect shared_buffer or another setting? Thanks! Charles On Mon, Jul 10, 2017 at 5:25 PM, Rick Otten wrote: > Although probably not the root cause, at

Re: [PERFORM] Very poor read performance, query independent

Jeff, Here are the 2 EXPLAINs for one of my simplest query: flows=# SET track_io_timing = on; LOG: duration: 24.101 ms statement: SET track_io_timing = on; SET flows=# explain (analyze, timing off) SELECT DISTINCT flows-#srcaddr, flows-#dstaddr, flows-#dstport, flows-#COUNT(*)

Re: [PERFORM] Very poor read performance, query independent

Joshua, I use noop as the scheduler because it is better to let the RAID controller re-arrange the IO operation before they reach the disk. Read ahead is set to 128: charles@hpdl380g6:~$ cat /sys/block/sdc/queue/read_ahead_kb 128 charles@hpdl380g6:~$ cat /sys/block/sdc/queue/scheduler [noop]

Re: [PERFORM] Very poor read performance, query independent

Igor, I set shared_buffers to 24 GB and effective_cache_size to 64GB and I can see that the queries are faster due to the fact that the index are used more often. Knowing I have 72GB of RAM and the server is exclusively dedicated to Postgresql, what could be the maximum value for effective_cache?