Increased I/O / Writes

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

Em 08/05/2016 23:07, drum.lucas@gmail.com escreveu:

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of drum.lucas@gmail.com
Sent: Sunday, May 08, 2016 10:07 PM
To: Postgres General <pgsql-general@postgresql.org>
Subject: [GENERAL] Increased I/O / Writes

Hi all,

I've recently an increase IO for writes on my DB. I'm currently using PostgreSQL 9.2.
[Inline images 1]

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

So, what’s wrong with using pg_stat_statements?
It has a set of columns pertaining to IO.

Regards,
Igor Neyman

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

<https://sourceforge.net/projects/pgbadger/&gt;

On Tue, May 10, 2016 at 1:31 PM, Joshua D. Drake <jd@commandprompt.com>
wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

I highly recommend you look into using PgBadger. It gives you a great deal
of info about your queries, including I/O, above and beyond pg_stats.
PgBadger info <https://dalibo.github.io/pgbadger/&gt; PgBadger download
<https://sourceforge.net/projects/pgbadger/&gt;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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 causing that 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is my postgresql.conf at the moment:

shared_buffer(51605MB) +
effective_cache_size(96760MB) +
work_mem(32MB) +
max_connections(200)

*= 148397.08 MB*

My server has 128GB of RAM

So, I'm using more RAM that I have. (not good at all)
I'm glad that it wasn't me who put those confs in there :)

Anyway...
I was thinking about decrease the shared_buffer to something like 1/8 of
total RAM = 16GB

What do u guys think about it?

Cheers
Lucas

Hi

Why not use https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/ to determine your optimal shared_buffers settings? ;-)

Cheers
Johnny

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Lucas Possamai
Sent: Dienstag, 17. Mai 2016 06:12
To: Sergey Konoplev
Cc: Postgres General
Subject: Re: [GENERAL] Increased I/O / Writes

This is my postgresql.conf at the moment:

shared_buffer(51605MB) +
effective_cache_size(96760MB) +
work_mem(32MB) +
max_connections(200)

= 148397.08 MB

My server has 128GB of RAM

So, I'm using more RAM that I have. (not good at all)
I'm glad that it wasn't me who put those confs in there :)

Anyway...
I was thinking about decrease the shared_buffer to something like 1/8 of total RAM = 16GB

What do u guys think about it?

Cheers
Lucas

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Following
https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/

and http://pgtune.leopard.in.ua/ and
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
I changed the shared_buffer from 51GB to 35GB.

Now, I'm getting spikes every 15 minutes.

*FATAL*: sorry, too many clients already

The change have been made 3 hours ago.

- we rebooted the server as well

Might be the cache warming up again?

On 5/17/2016 3:56 PM, Lucas Possamai wrote:

*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

Just an update about this...

As you know, the server was rebooted.

The spikes were happening because, after the reboot, the HUGE PAGES were
enabled.

After disabling them, all got back to normal.

Cheers

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 24 May 2016 at 09:40, Kevin Grittner <kgrittn@gmail.com> wrote:

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.

Yep.. you're right, Kevin.
Transparent huge pages...

We do have a script that disables it when the system is rebooted, but that
did not work.

Anyway, the problem was solved..
Cheers
Lucas