Re: Seeking performance advice and explanation for high I/O on 8.3

Would love to get some advice on how to change my conf settings / setup
to get better I/O performance.

Server Specs:

2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)

4GB RAM

4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored )

FreeBSD 6.4

Apache 2.2

PostgreSQL 8.3.6

PHP 5.2.9

~1500 databases w/ ~60 tables each

Total I/O (these number are pretty constant throughout the day):

Reads: ~ 100 / sec for about 2.6 Mb/sec

Writes: ~ 400 /sec for about 46.1Mb/sec

Conf settings:

listen_addresses = '*'

max_connections = 600

ssl = on

password_encryption = on

shared_buffers = 1GB

work_mem = 5MB

maintenance_work_mem = 256MB

max_fsm_pages = 2800000

max_fsm_relations = 160000

synchronous_commit = off

checkpoint_segments = 6

checkpoint_warning = 30s

effective_cache_size = 1GB

pg_stat_bgwriter:

checkpoints_timed: 16660

checkpoints_req: 1309

buffers_checkpoint: 656346

buffers_clean: 120922

maxwritten_clean: 1

buffers_backend: 167623

buffers_alloc: 472802349

This server also handles web traffic and PHP script processing.

Most of the SQL happening is selects - very little inserts, updates and
deletes comparatively.

I have noticed that most/all of the I/O activity is coming from the
stats collector and autovacuum processes. Would turning off the stats
collector and autovacuum be helpeful / recommended? Could I change my
checkpoint_* or bgwriter_* conf values to help?

Let me know if you need more information / stats.

Any help would be much appreciated.

Thanks,

Scott Otis

CIO / Lead Developer

Intand

www.intand.com

Scott Otis wrote:
> Would love to get some advice on how to change my conf settings / setup
> to get better I/O performance.
>
> Total I/O (these number are pretty constant throughout the day):
> Reads: ~ 100 / sec for about 2.6 Mb/sec
> Writes: ~ 400 /sec for about 46.1Mb/sec
>
>
> Most of the SQL happening is selects – very little inserts, updates and
> deletes comparatively.
>

Maybe I'm wrong, but those two don't seem to jive. You say its mostly selects, but you show higher writes per second.

Does freebsd have a vmstat or iostat? How did you get the numbers above? How's the cpu's look? (are they pegged?)

The io stats above seem low (reading 2 meg a second is a tiny fraction of what your system should be capable of). Have you tried a dd test?

-Andy

Scott Otis wrote:

> 2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)
> 4GB RAM
> 4x Seagate 73GB SAS HDD 10k RPM – in RAID ( stripped and mirrored )

> Would love to get some advice on how to change my conf settings / setup
> to get better I/O performance.

> ~1500 databases w/ ~60 tables each

This tells us nothing - size and complexity of databases is more
important than their number.

> Total I/O (these number are pretty constant throughout the day):
>
> Reads: ~ 100 / sec for about 2.6 Mb/sec
>
> Writes: ~ 400 /sec for about 46.1Mb/sec

Again, not enough information. How did you measure these? With iostat?
Are those random reads or sequential? (i.e. what was the IO transaction
size?) Caching can explain why you have 4x more writes than reads, but
it's still unusual, especially with the high write transfer rate you claim.

If random, you're doing ~~ 500 IOPS on a RAID10 array of 4 10 kRPM
drives, which is much more than you should - you're lucky you have the
performance you do.

By the way, why do you think your setup is slow? Is your application
slow and you think your database is the reason?

> shared_buffers = 1GB
>
> work_mem = 5MB
>
> maintenance_work_mem = 256MB

Ok.

> synchronous_commit = off

Ok. Could be important if your IO is slow as yours is.

> checkpoint_segments = 6

You could try raising this to 20, but I doubt it will help you that
much. OTOH it won't hurt.

> checkpoint_warning = 30s
>
> effective_cache_size = 1GB

Ok.

> Most of the SQL happening is selects – very little inserts, updates and
> deletes comparatively.

Are you sure? Your write rate is a bit big for there to be very little
insert/update/delete activity.

Scott Otis wrote:
> I agree that they don't make sense - part of the reason I am looking for
> help :)
>
> I am using iostat to get those numbers ( which I specify to average over
> 5 min then collect to display in Cacti ).
>
> 2 processes are taking up a good deal of CPU - the postgres stats
> collector and autovacuum ones. Both of those are using a lot of 1 core
> each.
>
> I am not familiar with a dd test - what is that?
>
> Thanks,
>
> Scott
>
> On Sep 3, 2009, at 8:03 AM, "Andy Colson" <andy(at)squeakycode(dot)net> wrote:
>
>> Scott Otis wrote:
>>> Would love to get some advice on how to change my conf settings /
>>> setup to get better I/O performance.
>>> Total I/O (these number are pretty constant throughout the day):
>>> Reads: ~ 100 / sec for about 2.6 Mb/sec
>>> Writes: ~ 400 /sec for about 46.1Mb/sec
>>> Most of the SQL happening is selects – very little inserts, updates
>>> and deletes comparatively.
>>
>> Maybe I'm wrong, but those two don't seem to jive. You say its mostly
>> selects, but you show higher writes per second.
>>
>> Does freebsd have a vmstat or iostat? How did you get the numbers
>> above? How's the cpu's look? (are they pegged?)
>>
>> The io stats above seem low (reading 2 meg a second is a tiny
>> fraction of what your system should be capable of). Have you tried a
>> dd test?
>>
>> -Andy

Please keep the list included so others may help.

the dd test:

http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm

I think Ivan is right, the 2 meg a second is probably because most of the reads are from cache. But he and I looked at the writes differently. If we ignore the 400/sec, and just read 46 meg a second (assuming you meant megabyte and not megabit) then, that's pretty slow (for sequential writing) -- which the dd test will measure your sequential read and write speed.

Ivan asked a good question:
By the way, why do you think your setup is slow? Is your application slow and you think your database is the reason?

-Andy

Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.

Would providing more information about the size and complexities of the databases help?

I measure I/O stats with iostat - here is the command I use:

iostat -d -x mfid0 -t 290 2

I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for sequential vs random - any help there?

When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.

When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.

Is that normal? It seems to me that the stats collector is doing all the I/O (which would mean the stats collector is doing 46.1 megabytes /sec).

Also, the I/O stats don't change hardly at all (except at night during backups which makes sense). They don't go up or down with user activity on the server - which makes me wonder a little bit. I have a feeling that if I just turned off Apache that the I/O stats wouldn't change. Which leads me to believe that the I/O is not query related - its stats collecting and autovacuuming related. Is that expected?

It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be using that much CPU) - therefore something in my configuration must be messed up or could be changed somehow. But maybe I'm wrong - please let me know.

I don't think my setup is necessarily slow. I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right. I am also looking out into the future and seeing how much load I can put on this server before getting another one. If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server.

Again thanks for all the help.

Scott Otis
CIO / Lead Developer
Intand
www.intand.com

Scott Otis wrote:
> Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.
>
> Would providing more information about the size and complexities of the databases help?
>
> I measure I/O stats with iostat - here is the command I use:
>
> iostat -d -x mfid0 -t 290 2
>
> I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for sequential vs random - any help there?
>
> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.
>
> When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.
>
> Is that normal? It seems to me that the stats collector is doing all the I/O (which would mean the stats collector is doing 46.1 megabytes /sec).
>
> Also, the I/O stats don't change hardly at all (except at night during backups which makes sense). They don't go up or down with user activity on the server - which makes me wonder a little bit. I have a feeling that if I just turned off Apache that the I/O stats wouldn't change. Which leads me to believe that the I/O is not query related - its stats collecting and autovacuuming related. Is that expected?
>
> It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be using that much CPU) - therefore something in my configuration must be messed up or could be changed somehow. But maybe I'm wrong - please let me know.
>
> I don't think my setup is necessarily slow. I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right. I am also looking out into the future and seeing how much load I can put on this server before getting another one. If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server.
>
> Again thanks for all the help.
>
> Scott Otis
> CIO / Lead Developer
> Intand
> www.intand.com
>

> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.
> When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.

Yeah, that sounds excessive. But my database gets 20 transactions a DAY, so, I have no experience with a busy box.

You say its mostly selects, but do you have any triggers or anything that might update a table? Do you do inserts or updates to track traffic?

What does:

select * from pg_stat_activity

look like? (I think vacuum will show up in there, right?) I'm curious if we can find the table autovacuum is working on, maybe that'll help pin it down.

-Andy

On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
> Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.
>
> Would providing more information about the size and complexities of the databases help?
>
> I measure I/O stats with iostat - here is the command I use:
>
> iostat -d -x mfid0 -t 290 2
>
> I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for sequential vs random - any help there?
>
> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.
>
> When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.
>
> Is that normal?  It seems to me that the stats collector is doing all the I/O (which would mean the stats collector is doing 46.1 megabytes /sec).
>
> Also, the I/O stats don't change hardly at all (except at night during backups which makes sense).  They don't go up or down with user activity on the server - which makes me wonder a little bit.  I have a feeling that if I just turned off Apache that the I/O stats wouldn't change.  Which leads me to believe that the I/O is not query related - its stats collecting and autovacuuming related.  Is that expected?
>
> It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be using that much CPU)  - therefore something in my configuration must be messed up or could be changed somehow.  But maybe I'm wrong - please let me know.
>
> I don't think my setup is necessarily slow.  I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right.  I am also looking out into the future and seeing how much load I can put on this server before getting another one.  If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server.
>
> Again thanks for all the help.

Can you post to the list all the uncommented lines from your
postgresql.conf file and attach the results of "select * from
pg_stat_all_tables" as an attachment?

...Robert

Robert Haas wrote:
> On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
>> Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.
>>
>> Would providing more information about the size and complexities of the databases help?
>>
>> I measure I/O stats with iostat - here is the command I use:
>>
>> iostat -d -x mfid0 -t 290 2
>>
>> I tried looking at the man page for iostat but couldn't find anywhere how to determine what the stats are for sequential vs random - any help there?
>>
>> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.
>>
>> When using 'top' the WCPU for the postgres stats collector and the autovacuum process are constantly at 20% - 21%.
>>
>> Is that normal? It seems to me that the stats collector is doing all the I/O (which would mean the stats collector is doing 46.1 megabytes /sec).
>>
>> Also, the I/O stats don't change hardly at all (except at night during backups which makes sense). They don't go up or down with user activity on the server - which makes me wonder a little bit. I have a feeling that if I just turned off Apache that the I/O stats wouldn't change. Which leads me to believe that the I/O is not query related - its stats collecting and autovacuuming related. Is that expected?
>>
>> It seems to me that the stats collector shouldn't be using that much I/O and CPU (and the autovacuum shouldn't be using that much CPU) - therefore something in my configuration must be messed up or could be changed somehow. But maybe I'm wrong - please let me know.
>>
>> I don't think my setup is necessarily slow. I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right. I am also looking out into the future and seeing how much load I can put on this server before getting another one. If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server.
>>
>> Again thanks for all the help.
>
> Can you post to the list all the uncommented lines from your
> postgresql.conf file and attach the results of "select * from
> pg_stat_all_tables" as an attachment?
>
> ...Robert
>

The first message he posted had this, and other info... Which is funny, because I almost asked the exact same question :-)

FreeBSD 6.4
Apache 2.2
PostgreSQL 8.3.6
PHP 5.2.9

~1500 databases w/ ~60 tables each

Conf settings:

listen_addresses = '*'
max_connections = 600
ssl = on
password_encryption = on
shared_buffers = 1GB
work_mem = 5MB
maintenance_work_mem = 256MB
max_fsm_pages = 2800000
max_fsm_relations = 160000
synchronous_commit = off
checkpoint_segments = 6
checkpoint_warning = 30s
effective_cache_size = 1GB


pg_stat_bgwriter:

checkpoints_timed: 16660
checkpoints_req: 1309
buffers_checkpoint: 656346
buffers_clean: 120922
maxwritten_clean: 1
buffers_backend: 167623
buffers_alloc: 472802349

On Thu, Sep 3, 2009 at 5:27 PM, Andy Colson<andy(at)squeakycode(dot)net> wrote:
> Robert Haas wrote:
>>
>> On Thu, Sep 3, 2009 at 4:16 PM, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
>>>
>>> Sorry about not responding to the whole list earlier - this is my first
>>> time posting to a mailing list.
>>>
>>> Would providing more information about the size and complexities of the
>>> databases help?
>>>
>>> I measure I/O stats with iostat - here is the command I use:
>>>
>>> iostat -d -x mfid0 -t 290 2
>>>
>>> I tried looking at the man page for iostat but couldn't find anywhere how
>>> to determine what the stats are for sequential vs random - any help there?
>>>
>>> When using 'top -m io' the postgres stats collector process is constantly
>>> at 99% - 100%.
>>>
>>> When using 'top' the WCPU for the postgres stats collector and the
>>> autovacuum process are constantly at 20% - 21%.
>>>
>>> Is that normal?  It seems to me that the stats collector is doing all the
>>> I/O (which would mean the stats collector is doing 46.1 megabytes /sec).
>>>
>>> Also, the I/O stats don't change hardly at all (except at night during
>>> backups which makes sense).  They don't go up or down with user activity on
>>> the server - which makes me wonder a little bit.  I have a feeling that if I
>>> just turned off Apache that the I/O stats wouldn't change.  Which leads me
>>> to believe that the I/O is not query related - its stats collecting and
>>> autovacuuming related.  Is that expected?
>>>
>>> It seems to me that the stats collector shouldn't be using that much I/O
>>> and CPU (and the autovacuum shouldn't be using that much CPU)  - therefore
>>> something in my configuration must be messed up or could be changed somehow.
>>>  But maybe I'm wrong - please let me know.
>>>
>>> I don't think my setup is necessarily slow.  I just want to make it as
>>> efficient as possible and wanted to get some feedback to see if am setting
>>> things up right.  I am also looking out into the future and seeing how much
>>> load I can put on this server before getting another one.  If I can reduce
>>> the I/O and CPU that the stats collector and autovacuum are using without
>>> losing any functionality then I can put more load on the server.
>>>
>>> Again thanks for all the help.
>>
>> Can you post to the list all the uncommented lines from your
>> postgresql.conf file and attach the results of "select * from
>> pg_stat_all_tables" as an attachment?
>>
>> ...Robert
>>
>
> The first message he posted had this, and other info... Which is funny,
> because I almost asked the exact same question :-)
>
>
> FreeBSD 6.4
> Apache 2.2
> PostgreSQL 8.3.6
> PHP 5.2.9
>
>
> ~1500 databases w/ ~60 tables each
>
>
> Conf settings:
>
> listen_addresses = '*'
> max_connections = 600
> ssl = on
> password_encryption = on
> shared_buffers = 1GB
> work_mem = 5MB
> maintenance_work_mem = 256MB
> max_fsm_pages = 2800000
> max_fsm_relations = 160000
> synchronous_commit = off
> checkpoint_segments = 6
> checkpoint_warning = 30s
> effective_cache_size = 1GB
>
>
> pg_stat_bgwriter:
>
> checkpoints_timed: 16660
> checkpoints_req: 1309
> buffers_checkpoint: 656346
> buffers_clean: 120922
> maxwritten_clean: 1
> buffers_backend: 167623
> buffers_alloc: 472802349

You're right - I missed that. But I still want to see pg_stat_all_tables.

I wonder if it would be worth attaching strace to the stats collector
and trying to get some idea what it's doing (if FreeBSD has
strace...).

....Robert

2009/9/3 Scott Otis <scott(dot)otis(at)intand(dot)com>:
> Sorry about not responding to the whole list earlier - this is my first time posting to a mailing list.
>
> Would providing more information about the size and complexities of the databases help?
>
> I measure I/O stats with iostat - here is the command I use:
>
> iostat -d -x mfid0 -t 290 2

Simply do "iostat mfid0 1" and post 10 lines of its output.

> When using 'top -m io' the postgres stats collector process is constantly at 99% - 100%.

In itself it doesn't mean much. The number of IOs is important.

> I don't think my setup is necessarily slow.  I just want to make it as efficient as possible and wanted to get some feedback to see if am setting things up right.  I am also looking out into the future and seeing how much load I can put on this server before getting another one.  If I can reduce the I/O and CPU that the stats collector and autovacuum are using without losing any functionality then I can put more load on the server.

In general it's tricky to optimize for unknown targets - if your
performance is OK right now, you should leave it alone.

On the other hand, your diagnosis of stats collector doing 46 MB/s
points to something very abnormal. You should probably post your
entire postgresql.conf.

--
f+rEnSIBITAhITAhLR1nM9F4cIs5KJrhbcsVtUIt7K1MhWJy1A==

> Simply do "iostat mfid0 1" and post 10 lines of its output.

tty mfid0 cpu
tin tout KB/t tps MB/s us ni sy in id
0 152 108.54 335 35.51 43 0 30 1 27
0 525 85.73 759 63.55 14 0 12 0 74
0 86 67.72 520 34.39 13 0 12 0 75
0 86 86.89 746 63.26 12 0 12 0 76
0 86 70.09 594 40.65 13 0 11 0 76
0 86 78.50 756 57.99 13 0 10 0 77
0 351 81.46 774 61.61 12 0 11 0 77
0 86 63.87 621 38.72 9 0 8 0 83
0 86 80.87 821 64.86 8 0 8 0 83
0 86 58.78 637 36.55 11 0 11 0 77

Scott

> Can you post to the list all the uncommented lines from your
postgresql.conf file and attach the results of "select * from
pg_stat_all_tables" as an attachment?

I attached a CSV of "select * from pg_stat_all_tables" from one of our
more heavily used databases. Note: I turned off stats collection and
autvacuuming a couple days ago to see what it would do and then
restarted postgres - I turned those back on this morning to that is why
there aren't more autovacuumed and autoanalyzed tables.

Sorry if this is a little verbose - I didn't want to leave anything out.

Uncommented lines from Postgresql.conf:

listen_addresses = '*'
max_connections = 600
ssl = on
password_encryption = on
shared_buffers = 1GB
work_mem = 5MB
maintenance_work_mem = 256MB
max_fsm_pages = 2800000
max_fsm_relations = 160000
synchronous_commit = off
checkpoint_segments = 6
checkpoint_warning = 30s
effective_cache_size = 1GB
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgsql'
log_filename = '%m%d%y_%H%M%S-pgsql.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_min_messages = warning
log_error_verbosity = default
log_min_error_statement = warning
silent_mode = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_temp_files = 0
track_activities = on
track_counts = on
update_process_title = off
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'

On Thu, Sep 3, 2009 at 7:11 PM, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
>> Can you post to the list all the uncommented lines from your
> postgresql.conf file and attach the results of "select * from
> pg_stat_all_tables" as an attachment?
>
> I attached a CSV of "select * from pg_stat_all_tables" from one of our
> more heavily used databases.  Note: I turned off stats collection and
> autvacuuming a couple days ago to see what it would do and then
> restarted postgres - I turned those back on this morning to that is why
> there aren't more autovacuumed and autoanalyzed tables.

Do you by any chance have a bazillion databases in this cluster? Can
you do these?

select sum(1) from pg_database;
select pg_relation_size('pg_database');
select sum(pg_column_size(d.*)) from pg_database;

...Robert

Claus Guttesen kometen(at)gmail(dot)com:

> > Would love to get some advice on how to change my conf settings / setup to
> > get better I/O performance.
> >
> > Server Specs:
> >
> > 2x Intel Xeon Quad Core (@2 Ghz - Clovertown,L5335)
> > 4GB RAM
> > 4x Seagate 73GB SAS HDD 10k RPM - in RAID ( stripped and mirrored )
> >
> > FreeBSD 6.4
> > Apache 2.2
> > PostgreSQL 8.3.6
> > PHP 5.2.9
> >
> > ~1500 databases w/ ~60 tables each
> >
> > max_connections = 600
> > shared_buffers = 1GB

> On a dual-core HP DL380 with 16 GB ram I have set shared_buffers at
> 512 MB for 900 max_connections. Far the largest table have approx. 120
> mill. records. You could try to lower shared_buffers.

> > max_fsm_pages = 2800000
> > max_fsm_relations = 160000

> What does the last couple of lines from a 'vacuum analyze verbose'
> say? I have max_fsm_pages = 4000000 and max_fsm_relations = 1500.

> You can also try to lower random_page_cost to a lower value like 1.2
> but I doubt this will help in your case.

last couple lines from 'vacuumdb -a -v -z':

INFO: free space map contains 114754 pages in 42148 relations
DETAIL: A total of 734736 page slots are in use (including overhead).
734736 page slots are required to track all free space.
Current limits are: 2800000 page slots, 160000 relations, using 26810 kB.

Scott

Robert Haas <robertmhaas(at)gmail(dot)com>:

> Do you by any chance have a bazillion databases in this cluster? Can
> you do these?

> select sum(1) from pg_database;

1555

> select pg_relation_size('pg_database');

221184

> select sum(pg_column_size(d.*)) from pg_database;

That gave me:

ERROR: missing FROM-clause entry for table "d"
LINE 1: select sum(pg_column_size(d.*)) from pg_database;

So I did this:

select sum(pg_column_size(d.*)) from pg_database as d;

and got:

192910

Also did this:

select sum(pg_database_size(datname)) from pg_database;

and got:

13329800428 (12.4GB)

Scott

>> > max_fsm_pages = 2800000
>> > max_fsm_relations = 160000
>
>> What does the last couple of lines from a 'vacuum analyze verbose'
>> say? I have max_fsm_pages = 4000000 and max_fsm_relations = 1500.
>
>> You can also try to lower random_page_cost to a lower value like 1.2
>> but I doubt this will help in your case.
>
> last couple lines from 'vacuumdb -a -v -z':
>
> INFO:  free space map contains 114754 pages in 42148 relations
> DETAIL:  A total of 734736 page slots are in use (including overhead).

----------------vvvvv-----------
> 734736 page slots are required to track all free space.
----------------^^^^^-----------

> Current limits are:  2800000 page slots, 160000 relations, using 26810 kB.

You can lower your max_fsm_pages setting to a number above 'xyz page
slots required ...' to 1000000 and fsm-relations to like 50000.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentler gamester is the soonest winner.

Shakespeare

On Fri, Sep 4, 2009 at 08:54, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com>:
>
>> Do you by any chance have a bazillion databases in this cluster?  Can
>> you do these?
>
>> select sum(1) from pg_database;
>
> 1555

Note that there are two features in 8.4 specifically designed to deal
with the situation where you have lots of databases and/or lots of
tables (depending on how many tables you have in each database, this
would definitely qualify). They both deal with the "pgstats temp file
too large generating i/o issue".

First, it will only write the file when it's actually necessary - 8.3
and earlier will always write it.

Second, you will have the ability to move the location of the file to
a different filesystem - specifically intended so that you can move it
off to a ramdrive.

Could be worth investigating an upgrade for this issue alone. The fact
that you don't have to struggle with tuning the FSM in 8.4 is another
thing that makes life a *lot* easier in this kind of installations.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

So is there anything I can do in 8.3 to help this? I have tried setting ' track_activities', 'track_counts' and 'autovacuum' to 'off' (which has reduced CPU and I/O a bit) - but the stats collector process is still using up a good deal of CPU and I/O - is there any way to turn stats collecting completely off?

Scott Otis
CIO / Lead Developer
Intand
www.intand.com

-----Original Message-----
From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Friday, September 04, 2009 1:19 AM
To: Scott Otis
Cc: Robert Haas; Ivan Voras; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3

On Fri, Sep 4, 2009 at 08:54, Scott Otis<scott(dot)otis(at)intand(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com>:
>
>> Do you by any chance have a bazillion databases in this cluster?  Can
>> you do these?
>
>> select sum(1) from pg_database;
>
> 1555

Note that there are two features in 8.4 specifically designed to deal with the situation where you have lots of databases and/or lots of tables (depending on how many tables you have in each database, this would definitely qualify). They both deal with the "pgstats temp file too large generating i/o issue".

First, it will only write the file when it's actually necessary - 8.3 and earlier will always write it.

Second, you will have the ability to move the location of the file to a different filesystem - specifically intended so that you can move it off to a ramdrive.

Could be worth investigating an upgrade for this issue alone. The fact that you don't have to struggle with tuning the FSM in 8.4 is another thing that makes life a *lot* easier in this kind of installations.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

"Scott Otis" <scott(dot)otis(at)intand(dot)com> wrote:

> So is there anything I can do in 8.3 to help this? I have tried
> setting 'track_activities', 'track_counts' and 'autovacuum' to 'off'
> (which has reduced CPU and I/O a bit)

You're going to regret that very soon, unless you are *very* sure you
have adequate manual vacuums scheduled.

http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html

-Kevin