pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWho is causing all this i/o?
12+ messages / 7 participants
[nested] [flat]
* Who is causing all this i/o?
@ 2011-05-20 21:14 Craig James <craig_james@emolecules.com>
2011-05-20 22:38 ` Re: Who is causing all this i/o? Joshua D. Drake <jd@commandprompt.com>
2011-05-20 23:08 ` Re: Who is causing all this i/o? Steve Crawford <scrawford@pinpointresearch.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 12+ messages in thread
From: Craig James @ 2011-05-20 21:14 UTC (permalink / raw)
To: pgsql-admin
Our development server (PG 8.4.4 on Ubuntu server) is constantly doing something, and I can't figure out what. The two production servers, which are essentially identical, don't show these symptoms. In a nutshell, it's showing 10K blocks per second of data going out, all the time, and essentially zero blocks per second of input.
To start with, if I turn off Postgres, everything is quiet:
$ /etc/init.d/postgresql stop
Stopping PostgreSQL: server stopped
ok
[radon:root] /emi/logs/londiste # vmstat 2
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 126140 1115944 112692 10466452 0 0 132 829 1 3 2 1 97 1
0 0 126140 1116068 112692 10466452 0 0 0 0 150 175 0 0 100 0
0 0 126140 1116184 112708 10466456 0 0 0 94 123 153 0 0 100 0
0 0 126140 1116432 112708 10466456 0 0 0 0 96 126 0 0 100 0
0 0 126140 1116928 112708 10466456 0 0 0 0 94 111 0 0 100 0
0 0 126140 1116688 112708 10466456 0 0 0 0 114 144 0 0 100 0
0 0 126140 1116364 112708 10466456 0 0 18 74 229 266 1 0 99 0
0 0 126140 1116488 112708 10466492 0 0 0 0 111 138 0 0 100 0
1 0 126140 1116868 112716 10466492 0 0 0 20 96 121 0 0 100 0
But when I restart Postgres, it looks like this, and runs this way 24/7. Notice the "bo" column:
$ /etc/init.d/postgresql start
Starting PostgreSQL: ok
$ vmstat 2
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 126140 1078020 112744 10480652 0 0 132 829 1 3 2 1 97 1
0 1 126140 1054320 112744 10487792 0 0 52 10714 900 976 2 1 97 0
0 1 126140 1015988 112760 10511060 0 0 1828 9620 1491 3527 5 2 87 6
0 1 126140 1008316 112768 10517936 0 0 672 10994 982 1209 1 2 86 11
0 0 126140 1017780 112776 10528824 0 0 1466 10746 1003 1428 2 1 89 8
0 0 126140 1013324 112784 10533680 0 0 0 9718 766 858 1 0 99 0
1 0 126140 991600 112784 10539220 0 0 0 10714 962 1127 2 1 97 0
0 0 126140 999924 112788 10544380 0 0 6 10726 918 1227 2 1 97 0
0 0 126140 995700 112800 10548816 0 0 0 9744 823 889 1 1 99 0
2 0 126140 964560 112804 10554048 0 0 2 10718 903 1196 3 1 96 0
That seems to be a lot of output data going somewhere, but there doesn't seem to be anything going on that should cause it:
postgres=# select datname, procpid, usename, substr(current_query,0,25) current_query, waiting from pg_stat_activity;
datname | procpid | usename | current_query | waiting
------------+---------+----------+--------------------------+---------
postgres | 12927 | postgres | select datname, procpid, | f
global | 12185 | postgres | <IDLE> | f
accounting | 12367 | postgres | <IDLE> | f
orders | 12225 | orders | <IDLE> | f
archive | 12267 | postgres | <IDLE> | f
(5 rows)
After a lot of digging around, I found this in the /postgres/pg_stat_tmp directory. If I list the directory including the i-nodes once every second, I find that a new 2MB file is being created roughly once every two seconds:
# ls -li
total 2384
69731459 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731258 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731459 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731459 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 3332
69731258 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
69731459 -rw------- 1 postgres postgres 970752 2011-05-20 14:07 pgstat.tmp
# ls -li
total 2384
69731459 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731258 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731258 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
# ls -li
total 2384
69731258 -rw------- 1 postgres postgres 2437957 2011-05-20 14:07 pgstat.stat
This is a development server, so there are no queries coming in from the web or other random activities. Nothing is going on.
The other two servers are configured identically. If I diff the configuration files, the only difference is the IP addresses for the "listen" section.
Can anyone tell me what's going on? Why is pgstat.stat being rewritten on this server constantly and not on the other two servers?
Thanks,
Craig
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-05-20 22:38 ` Joshua D. Drake <jd@commandprompt.com>
2011-05-20 22:50 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 12+ messages in thread
From: Joshua D. Drake @ 2011-05-20 22:38 UTC (permalink / raw)
To: pgsql-admin
> The other two servers are configured identically. If I diff the
> configuration files, the only difference is the IP addresses for the
> "listen" section.
>
> Can anyone tell me what's going on? Why is pgstat.stat being rewritten
> on this server constantly and not on the other two servers?
Do you have something monitoring the stats of the system aggressively?
(like pg_stat_user_tables?)
JD
>
> Thanks,
> Craig
>
>
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 22:38 ` Re: Who is causing all this i/o? Joshua D. Drake <jd@commandprompt.com>
@ 2011-05-20 22:50 ` Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 12+ messages in thread
From: Craig James @ 2011-05-20 22:50 UTC (permalink / raw)
To: pgsql-admin
On 5/20/11 3:38 PM, Joshua D. Drake wrote:
>
>> The other two servers are configured identically. If I diff the
>> configuration files, the only difference is the IP addresses for the
>> "listen" section.
>>
>> Can anyone tell me what's going on? Why is pgstat.stat being rewritten
>> on this server constantly and not on the other two servers?
>
> Do you have something monitoring the stats of the system aggressively? (like pg_stat_user_tables?)
No, there is nothing like that running on this server. Just Apache, and Apache can only be accessed from inside the company, and its logs showed no activity at all.
Thanks,
Craig
>
> JD
>
>
>>
>> Thanks,
>> Craig
>>
>>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-05-20 23:08 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 12+ messages in thread
From: Steve Crawford @ 2011-05-20 23:08 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-admin
On 05/20/2011 02:14 PM, Craig James wrote:
> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing
> something, and I can't figure out what. The two production servers,
> which are essentially identical, don't show these symptoms. In a
> nutshell, it's showing 10K blocks per second of data going out, all
> the time, and essentially zero blocks per second of input.
...
There are people on this list better equipped than I to get into the
guts of diagnosis, but a quick Google shows you aren't the only person
to see high IO with disk activity. There are suggestions that a db with
lots of temp-table creation and nothing triggering a vacuum could be
related. Another thread suggested that bloated system tables could be to
blame. Others look at the stats collectors. Groping a bit in the dark, I
would try...
Checking PG logs for info - especially anything indicating that vacuum
isn't running or isn't being triggered.
Manual vacuum.
Vacuum full of system tables (pg_catalog - especially pg_class and
pg_attribute) - possibly followed by a reindex all followed by a manual
vacuum. Note: IIRC, that file is shared across databases so you will
need to clean each one.
Reset stats collector at startup.
Check system after each change and report back if anything improves or
degrades the situation.
Cheers,
Steve
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-05-20 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2011-05-20 23:25 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-admin
On Fri, May 20, 2011 at 3:14 PM, Craig James <craig_james@emolecules.com> wrote:
> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing
> something, and I can't figure out what. The two production servers, which
> are essentially identical, don't show these symptoms. In a nutshell, it's
> showing 10K blocks per second of data going out, all the time, and
> essentially zero blocks per second of input.
> After a lot of digging around, I found this in the /postgres/pg_stat_tmp
> directory. If I list the directory including the i-nodes once every second,
> I find that a new 2MB file is being created roughly once every two seconds:
Have you got a lot of databases in your development environment? I
think that can sometimes cause a lot of pg_stat writes.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
@ 2011-05-20 23:41 ` Craig James <craig_james@emolecules.com>
2011-05-21 03:37 ` Re: Who is causing all this i/o? vequalsitimesz@gmail.com
2011-05-21 15:11 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Craig James @ 2011-05-20 23:41 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
On 5/20/11 4:25 PM, Scott Marlowe wrote:
> On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_james@emolecules.com> wrote:
>> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing
>> something, and I can't figure out what. The two production servers, which
>> are essentially identical, don't show these symptoms. In a nutshell, it's
>> showing 10K blocks per second of data going out, all the time, and
>> essentially zero blocks per second of input.
>> After a lot of digging around, I found this in the /postgres/pg_stat_tmp
>> directory. If I list the directory including the i-nodes once every second,
>> I find that a new 2MB file is being created roughly once every two seconds:
> Have you got a lot of databases in your development environment? I
> think that can sometimes cause a lot of pg_stat writes.
Yes. The production servers have a dozen or so databases, but the development server has a couple hundred databases. Does that count as "a lot of databases"?
We've had even more databases in the past (>500) and didn't see this sort of I/O activity.
The odd thing is that this activity is gradually growing, so slowly that you don't notice it right away. A month or two ago, nothing. A couple weeks ago, a constant stream of "grass" in xload's graph. Today, xload is showing a 50% all the time. I can only guess that the load will continue to increase.
Is there a way to tell Postgres not to do this, or to do it less? It's not a big disaster (yet), but it's annoying to have the system spewing a couple megabytes of pg_stat_tmp data every few seconds.
Thanks,
Craig
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-05-21 03:37 ` vequalsitimesz@gmail.com
1 sibling, 0 replies; 12+ messages in thread
From: vequalsitimesz@gmail.com @ 2011-05-21 03:37 UTC (permalink / raw)
To: pgsql-admin
I am tracking down possibly similar behavior on a production server and a
colleague introduced me to Dstat. I am using it right now to try and track
down the root of my problem. May or may not be helpful in figuring out
exactly whats causing this and then on to figuring out to fix it.
http://dag.wieers.com/home-made/dstat/#
then run something like:
dstat -t -l --cpu24 --sys --tcp --vm --nocolor --top-io --top-latency-avg
--output /tmp/dstat.csv
sincerely
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-05-21 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-22 01:27 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-06-17 18:51 ` Re: Who is causing all this i/o? Shianmiin <Shianmiin@gmail.com>
1 sibling, 2 replies; 12+ messages in thread
From: Tom Lane @ 2011-05-21 15:11 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
Craig James <craig_james@emolecules.com> writes:
> On 5/20/11 4:25 PM, Scott Marlowe wrote:
>> On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_james@emolecules.com> wrote:
>>> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing
>>> something, and I can't figure out what. The two production servers, which
>>> are essentially identical, don't show these symptoms. In a nutshell, it's
>>> showing 10K blocks per second of data going out, all the time, and
>>> essentially zero blocks per second of input.
>>> After a lot of digging around, I found this in the /postgres/pg_stat_tmp
>>> directory. If I list the directory including the i-nodes once every second,
>>> I find that a new 2MB file is being created roughly once every two seconds:
>> Have you got a lot of databases in your development environment? I
>> think that can sometimes cause a lot of pg_stat writes.
> Yes. The production servers have a dozen or so databases, but the development server has a couple hundred databases. Does that count as "a lot of databases"?
Yeah. I think what is happening is that the autovacuum launcher is
visiting every database, doing accesses to the system catalogs (and not
much more than that), which results in access-count updates in the stats
collector, which have to get written to disk.
What's not apparent however is why the stats collector is writing disk
so much. 8.4 does have the logic change to not write stats out unless
something is asking to see them. So either it's really pre-8.4, or you
have a monitoring task that is constantly asking to see stats.
One possible band-aid solution is to increase autovacuum_naptime. This
is defined as the AV cycle time *for each database*, so AV wakes up and
touches another database every naptime/#databases seconds. If your
number of databases has been growing over time, this would probably
explain why the problem is getting worse.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-21 15:11 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-05-22 01:27 ` Craig James <craig_james@emolecules.com>
1 sibling, 0 replies; 12+ messages in thread
From: Craig James @ 2011-05-22 01:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin
On 5/21/11 8:11 AM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com> writes:
>> On 5/20/11 4:25 PM, Scott Marlowe wrote:
>>> On Fri, May 20, 2011 at 3:14 PM, Craig James<craig_james@emolecules.com> wrote:
>>>> Our development server (PG 8.4.4 on Ubuntu server) is constantly doing
>>>> something, and I can't figure out what. The two production servers, which
>>>> are essentially identical, don't show these symptoms. In a nutshell, it's
>>>> showing 10K blocks per second of data going out, all the time, and
>>>> essentially zero blocks per second of input.
>>>> After a lot of digging around, I found this in the /postgres/pg_stat_tmp
>>>> directory. If I list the directory including the i-nodes once every second,
>>>> I find that a new 2MB file is being created roughly once every two seconds:
>>> Have you got a lot of databases in your development environment? I
>>> think that can sometimes cause a lot of pg_stat writes.
>> Yes. The production servers have a dozen or so databases, but the development server has a couple hundred databases. Does that count as "a lot of databases"?
> Yeah. I think what is happening is that the autovacuum launcher is
> visiting every database, doing accesses to the system catalogs (and not
> much more than that), which results in access-count updates in the stats
> collector, which have to get written to disk.
>
> What's not apparent however is why the stats collector is writing disk
> so much. 8.4 does have the logic change to not write stats out unless
> something is asking to see them. So either it's really pre-8.4,
$ ./postgres --version
postgres (PostgreSQL) 8.4.4
> or you
> have a monitoring task that is constantly asking to see stats.
Nothing like that. There's virtually nothing on this server but Postgres and Apache. There are some fast-cgi perl programs that stay connected to Postgres all the time, but the constant I/O starts up even if I kill Apache.
> One possible band-aid solution is to increase autovacuum_naptime. This
> is defined as the AV cycle time *for each database*, so AV wakes up and
> touches another database every naptime/#databases seconds.
I'll try this. Since it's a development system, it tends to get large bursts of loading followed by a bit of searching. Cutting down on vacuums should be fine.
> If your
> number of databases has been growing over time, this would probably
> explain why the problem is getting worse.
For the last few months the number of databases has been almost static. Maybe five added (of 200) in the last three months, and probably a few others dropped.
Thanks,
Craig
> regards, tom lane
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-21 15:11 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-06-17 18:51 ` Shianmiin <Shianmiin@gmail.com>
2011-06-19 15:35 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
1 sibling, 1 reply; 12+ messages in thread
From: Shianmiin @ 2011-06-17 18:51 UTC (permalink / raw)
To: pgsql-admin
Tom Lane-2 wrote:
>
> What's not apparent however is why the stats collector is writing disk
> so much. 8.4 does have the logic change to not write stats out unless
> something is asking to see them. So either it's really pre-8.4, or you
> have a monitoring task that is constantly asking to see stats.
>
We have a PostgreSQL 9.0.4 on CentOs for performance testing and we are
seeing the similar issue.
we have a "crazy" setup it has 1 database with 1000 identical schemas. There
are occasional I/O write storm
of over 100 MB/sec without any disk reads, and it could last for a couple of
minutes when the schemas/data are aggressively populated by pg_restore. All
the io writes seem to be on pgstat.tmp.
The I/O write storm seemed to be trigger by Vacuum.
The way we get around that is to point stats_temp_directory to ram disk
which eliminates the I/O write storm effectively.
Is it an issue of specific usage scenario or a general issue to be resolved?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Who-is-causing-all-this-i-o-tp4413558p4499692.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-21 15:11 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
2011-06-17 18:51 ` Re: Who is causing all this i/o? Shianmiin <Shianmiin@gmail.com>
@ 2011-06-19 15:35 ` Craig James <craig_james@emolecules.com>
2011-06-19 17:08 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Craig James @ 2011-06-19 15:35 UTC (permalink / raw)
To: pgsql-admin
On 6/17/11 11:51 AM, Shianmiin wrote:
> Tom Lane-2 wrote:
>> What's not apparent however is why the stats collector is writing disk
>> so much. 8.4 does have the logic change to not write stats out unless
>> something is asking to see them. So either it's really pre-8.4, or you
>> have a monitoring task that is constantly asking to see stats.
>>
> We have a PostgreSQL 9.0.4 on CentOs for performance testing and we are
> seeing the similar issue.
> we have a "crazy" setup it has 1 database with 1000 identical schemas. There
> are occasional I/O write storm
> of over 100 MB/sec without any disk reads, and it could last for a couple of
> minutes when the schemas/data are aggressively populated by pg_restore. All
> the io writes seem to be on pgstat.tmp.
>
> The I/O write storm seemed to be trigger by Vacuum.
Based on the advice I got from my original question, I changed autovacuum_naptime to "5min", and the problem completely disappeared. (I know that's a long interval, but this particular server gets maybe 5-10 heavy updates per week and is idle the rest of the time.)
select count(1) from pg_database ;
count
-------
267
It seems like there's a problem somewhere. Autovacuum has improved enormously in the last couple of years, but some change to its algorithm causes a lot of I/O thrashing when there are more than a few hundred separate databases.
Craig
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who is causing all this i/o?
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 23:25 ` Re: Who is causing all this i/o? Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-21 15:11 ` Re: Who is causing all this i/o? Tom Lane <tgl@sss.pgh.pa.us>
2011-06-17 18:51 ` Re: Who is causing all this i/o? Shianmiin <Shianmiin@gmail.com>
2011-06-19 15:35 ` Re: Who is causing all this i/o? Craig James <craig_james@emolecules.com>
@ 2011-06-19 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2011-06-19 17:08 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-admin
Craig James <craig_james@emolecules.com> writes:
> On 6/17/11 11:51 AM, Shianmiin wrote:
>> We have a PostgreSQL 9.0.4 on CentOs for performance testing and we are
>> seeing the similar issue.
>> we have a "crazy" setup it has 1 database with 1000 identical schemas. There
>> are occasional I/O write storm
>> of over 100 MB/sec without any disk reads, and it could last for a couple of
>> minutes when the schemas/data are aggressively populated by pg_restore. All
>> the io writes seem to be on pgstat.tmp.
> Based on the advice I got from my original question, I changed autovacuum_naptime to "5min", and the problem completely disappeared. (I know that's a long interval, but this particular server gets maybe 5-10 heavy updates per week and is idle the rest of the time.)
> select count(1) from pg_database ;
> count
> -------
> 267
> It seems like there's a problem somewhere. Autovacuum has improved enormously in the last couple of years, but some change to its algorithm causes a lot of I/O thrashing when there are more than a few hundred separate databases.
Well, if you have a lot of databases then you definitely need to
increase the naptime to keep autovac's demands for stats within bounds.
I don't find that surprising, though I do wonder if we ought to redefine
the way that GUC works: right now, you get one autovac wakeup every
naptime/databases seconds.
The OP seems to have some other issue, though, since he says he's only
got 1 database.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2011-06-19 17:08 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-20 21:14 Who is causing all this i/o? Craig James <craig_james@emolecules.com>
2011-05-20 22:38 ` Joshua D. Drake <jd@commandprompt.com>
2011-05-20 22:50 ` Craig James <craig_james@emolecules.com>
2011-05-20 23:08 ` Steve Crawford <scrawford@pinpointresearch.com>
2011-05-20 23:25 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-05-20 23:41 ` Craig James <craig_james@emolecules.com>
2011-05-21 03:37 ` vequalsitimesz@gmail.com
2011-05-21 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-22 01:27 ` Craig James <craig_james@emolecules.com>
2011-06-17 18:51 ` Shianmiin <Shianmiin@gmail.com>
2011-06-19 15:35 ` Craig James <craig_james@emolecules.com>
2011-06-19 17:08 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox