Thread: Increased I/O / Writes

On 05/10/2016 09:50 AM, Igor Neyman wrote:

> Please, if anyone can share anything.. Thanks a lot!
>
> Lucas
>
> So, what’s wrong with using pg_stat_statements?
>
> It has a set of columns pertaining to IO.
>

You could use iotop to determine which postgres pid is eating the IO,
then use statement logging with PID (or pg_stat_activity/statement) to
see what the system is actually doing.

JD


> Regards,
>
> Igor Neyman
>


--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

I had a similar problem few days ago.

Point is, I've (by mistaken), deleted a index, and this increased the table scan, increasing overall I/O.

Atenciosamente,

Edson Carlos Ericksson Richter

Hi all,

I've recently an increase IO for writes on my DB. I'm currently using PostgreSQL 9.2.

I know that much improvements can be done (as I'm using SATA disks), but my question is:

Is there a way to detect the queries that are causing that?

I can use pg_stat_statements to get the most usage queries, but I was wondering how can I find the queries that are causing that much IO?

Please, if anyone can share anything.. Thanks a lot!

Lucas

On Sun, May 8, 2016 at 7:07 PM, drum.lucas@gmail.com
<drum.lucas@gmail.com> wrote:
> Is there a way to detect the queries that are causing that?
>
> I can use pg_stat_statements to get the most usage queries, but I was wondering how can I find the queries that are
causingthat much IO? 

Take a look at this tool:

https://github.com/grayhemp/pgcookbook/blob/master/statement_statistics_collecting_and_reporting.md

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
https://github.com/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru@gmail.com

On Tue, May 17, 2016 at 12:11 AM, Lucas Possamai <drum.lucas@gmail.com> wrote:
> This is my postgresql.conf at the moment:
>
> shared_buffer(51605MB) +
> effective_cache_size(96760MB) +
> work_mem(32MB) +
> max_connections(200)
>
> = 148397.08 MB

You are comparing some very dissimilar settings.  effective_cache_size
does not allocate memory, it tells the planner how much cache you have
allocated (i.e., the sum of shared_buffers and the OS cache).
work_mem can be allocated zero to a large number of times per active
query.  Every open connection will use some RAM, but the amount is
hard to predict exactly.

You might want to go over this page:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

... and then read the documentation of any setting you are thinking of
adjusting.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

FATAL:  sorry, too many clients already

I believe that error means you've exceeded max_connections.     query pg_stat_activity and see how many connections are in the various 'states', like..

    select state, count(*) from pg_stat_activity group by state;

-- 
john r pierce, recycling bits in santa cruz

On Mon, May 23, 2016 at 4:32 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:

> The spikes were happening because, after the reboot, the HUGE PAGES were
> enabled.
>
> After disabling them, all got back to normal.

Since you said earlier you were on PostgreSQL version 9.2, you must
be talking about transparent huge pages.  Yeah, be sure those are
configured to be disabled in a way that "sticks" on your OS.  When
you get to version 9.4 you will notice that we support huge pages
directly.  That would be expected to work without problems even
though TRANSPARENT huge pages are debilitating.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company