Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Robert Haas robertmh...@gmail.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
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
max_fsm_pages = 280 max_fsm_relations = 16 What does the last couple of lines from a 'vacuum analyze verbose' say? I have max_fsm_pages = 400 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). v--- 734736 page slots are required to track all free space. ^--- Current limits are: 280 page slots, 16 relations, using 26810 kB. You can lower your max_fsm_pages setting to a number above 'xyz page slots required ...' to 100 and fsm-relations to like 5. -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Claus Guttesen kome...@gmail.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 = 280 max_fsm_relations = 16 What does the last couple of lines from a 'vacuum analyze verbose' say? I have max_fsm_pages = 400 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: 280 page slots, 16 relations, using 26810 kB. Scott
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
On Fri, Sep 4, 2009 at 08:54, Scott Otisscott.o...@intand.com wrote: Robert Haas robertmh...@gmail.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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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:mag...@hagander.net] Sent: Friday, September 04, 2009 1:19 AM To: Scott Otis Cc: Robert Haas; Ivan Voras; pgsql-performance@postgresql.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 Otisscott.o...@intand.com wrote: Robert Haas robertmh...@gmail.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/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Scott Otis scott.o...@intand.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 a...@squeakycode.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
On Thu, Sep 3, 2009 at 4:16 PM, Scott Otisscott.o...@intand.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
Robert Haas wrote: On Thu, Sep 3, 2009 at 4:16 PM, Scott Otisscott.o...@intand.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 = 280 max_fsm_relations = 16 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
On Thu, Sep 3, 2009 at 5:27 PM, Andy Colsona...@squeakycode.net wrote: Robert Haas wrote: On Thu, Sep 3, 2009 at 4:16 PM, Scott Otisscott.o...@intand.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 = 280 max_fsm_relations = 16 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
2009/9/3 Scott Otis scott.o...@intand.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== -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
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 = 280 max_fsm_relations = 16 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' pg_stat_all_tables2.csv Description: pg_stat_all_tables2.csv -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Seeking performance advice and explanation for high I/O on 8.3
On Thu, Sep 3, 2009 at 7:11 PM, Scott Otisscott.o...@intand.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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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 = 280 max_fsm_relations = 16 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
