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
[PERFORM] Planner question - bit data types
Does the planner know how to use indices to optimize these queries? For reference, I was having SEVERE performance problems with the following comparison in an SQL statement where mask was an integer: select ... from where .. and (permission mask = permission) This resulted in the planner deciding to run a nested loop and extraordinarily poor performance. I can probably recode the application to use a field of type bit(32) and either cast to an integer or have the code do the conversion internally (its just a shift eh?) The question is whether the above statement will be reasonably planned if mask is a bit type. -- Karl Denninger attachment: karl.vcf -- 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] Planner question - bit data types
Karl, For reference, I was having SEVERE performance problems with the following comparison in an SQL statement where mask was an integer: select ... from where .. and (permission mask = permission) AFAIK, the only way to use an index on these queries is through expression indexes. That's why a lot of folks use INTARRAY instead; it comes with a GIN index type. It would probably be possible to create a new index type using GiST or GIN which indexed bitstrings automatically, but I don't know that anyone has done it yet. Changing your integer to a bitstring will not, to my knowledge, improve this. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
