Re: Updating large tables without dead tuples

2018-02-23 Thread Stephen Frost
Greetings, * l...@laurent-hasson.com (l...@laurent-hasson.com) wrote: > This was done during a maintenance window, and that table is read-only except > when we ETL data to it on a weekly basis, and so I was just wondering why I > should pay the "bloat" penalty for this type of transaction. Is

Updating large tables without dead tuples

2018-02-23 Thread l...@laurent-hasson.com
Hello I work with a large and wide table (about 300 million rows, about 50 columns), and from time to time, we get business requirements to make some modifications. But sometimes, it's just some plain mistake. This has happened to us a few weeks ago where someone made a mistake and we had to

Re: Performance

2018-02-23 Thread Andreas Kretschmer
Am 23.02.2018 um 20:29 schrieb Daulat Ram: We have the following requirements in single query or any proper solution. Please help on this. How many sessions are currently opened. ask pg_stat_activity, via select * from pg_stat_activity -and if opened then how many queries have

Re: effective_io_concurrency on EBS/gp2

2018-02-23 Thread Rick Otten
On Thu, Feb 8, 2018 at 11:40 AM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > Anyway, there are still some strange things happening when > effective_io_concurrency is non-zero. > > ... > > Vitaliy > > I was researching whether I could optimize a concatenated lvm2 volume when I have

Re: effective_io_concurrency on EBS/gp2

2018-02-23 Thread Vitaliy Garnashevich
I noticed that the recent round of tests being discussed never mentioned the file system used.  Was it XFS?  Does changing the agcount change the behaviour? It was ext4. Regards, Vitaliy

Re: need advice to tune postgresql

2018-02-23 Thread Laurenz Albe
Darius Pėža wrote: > I have issue that update queries is slow, I need some advice how improve > speed. I don't have much control to change queries. But I can change > postresql server configuration > > query example: > > UPDATE "project_work" SET "left" = ("project_work"."left" + 2) WHERE >

Please help

2018-02-23 Thread Daulat Ram
Hello team, I need help how & what we can monitor the Postgres database via Nagios. I came to know about the check_postgres.pl script but we are using free ware option of postgres. If its Ok with freeware then please let me know the steps how I can implement in our environment. Regards,

Performance

2018-02-23 Thread Daulat Ram
Hello experts, We have the following requirements in single query or any proper solution. Please help on this. How many sessions are currently opened. -and if opened then how many queries have executed on that session. -and also we have to trace how much time each query is taking. -and

need advice to tune postgresql

2018-02-23 Thread Darius Pėža
I have issue that update queries is slow, I need some advice how improve speed. I don't have much control to change queries. But I can change postresql server configuration query example: UPDATE "project_work" SET "left" = ("project_work"."left" + 2) WHERE ("project_work"."left" >= 8366)

Re: need advice to tune postgresql

2018-02-23 Thread MichaelDBA
What caught my eye is the update count can be up to 10K. That means if autovacuum is not keeping up with this table, bloat may be increasing at a high pace leading to more page I/O which causes degraded performance. If the table has become bloated, you need to do a blocking VACUUM FULL on it