[PERFORM] Occupation bloc in pages of table
Hello, I see in the documentation that we can obtain the number of pages for a table with the view named pg_class. I would want if it is possible for each pages of a table to have the occupation of blocs in percentage in order to see if the page is good full or not. I don’t find anything in the doc and the archive. Best regards, Sorry for my english ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] SAN performance mystery
Hi, Tim, Tim Allen wrote: One thing that has been apparent is that autovacuum has not been able to keep the database sufficiently tamed. A pg_dump/pg_restore cycle reduced the total database size from 81G to 36G. Two first shots: - Increase your free_space_map settings, until (auto)vacuum does not warn about a too small FSM setting any more - Tune autovacuum to run more often, possibly with a higher delay setting to lower the load. If you still have the original database around, Performing the restore took about 23 hours. Try to put the WAL on another spindle, and increase the WAL size / checkpoint segments. When most of the restore time was spent in index creation, increase the sort mem / maintainance work mem settings. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SAN performance mystery
Hi, Tim, Seems I sent my message to fast, cut in middle of a sencence: Markus Schaber wrote: A pg_dump/pg_restore cycle reduced the total database size from 81G to 36G. If you still have the original database around, ... can you check wether VACUUM FULL and REINDEX achieve the same effect? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
Hi, Csaba, Csaba Nagy wrote: Well, your application might be completely well behaved and still your DBA (or your favorite DB access tool for that matter) can leave open transactions in an interactive session. It never hurts to check if you actually have idle in transaction sessions. It happened a few times to us, some of those were bad coding on ad-hoc tools written by us, others were badly behaved DB access tools opening a transaction immediately after connect and after each successful command, effectively leaving an open transaction when leaving it open while having lunch... Some older JDBC driver versions had the bug that they always had an open transaction, thus an application server having some pooled connections lingering around could block vacuum forever. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat
Jim C. Nasby wrote: On Wed, Jun 21, 2006 at 01:21:05PM -0300, jody brownell wrote: Jun 21 13:04:04 vanquish postgres[3311]: [19-1] DEBUG: target: removed 5645231 row versions in 106508 pages Jun 21 13:04:04 vanquish postgres[3311]: [19-2] DETAIL: CPU 3.37s/1.23u sec elapsed 40.63 sec. Jun 21 13:04:04 vanquish postgres[3311]: [20-1] DEBUG: target: found 5645231 removable, 1296817 nonremovable row versions in 114701 pages Jun 21 13:04:04 vanquish postgres[3311]: [20-2] DETAIL: 0 dead row versions cannot be removed yet. So the table contained 5.6M dead rows and 1.3M live rows. I think you should forget about having autovacuum keep this table in-check and add manual vacuum commands to your code. Autovac is intended to deal with 99% of use cases; this is pretty clearly in the 1% it can't handle. Maybe your free space map is configured to small, can you watch out for log messages telling to increase it? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Occupation bloc in pages of table
luchot [EMAIL PROTECTED] writes: I would want if it is possible for each pages of a table to have the occupation of blocs in percentage in order to see if the page is good full or not. There is not any magic way of getting that information, but you could modify contrib/pgstattuple to produce such a report. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Buffers to Nest Loop Join
Hi for all,Please, Normaly when some SGBD exec the algoritm Nest-Loop Join, there are diferences about the space(buffer) for outer table and inner table. So, I want know where Postgres define the number for this spaces (buffers)? And can I change it?This is very important to me.Thanks, I hope that somebody can help me. By Daniel Abra sua conta no Yahoo! Mail - 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz.
[PERFORM] Temporary table
Hello, I´m have some problems with a temporary table, i need create a table, insert some values, make a select and at end of transaction the table must droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; In this case, the table cademp doesn´t exist at the first insert, in the same transaction. Tks, Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary table
Franklin Haut [EMAIL PROTECTED] writes: How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; You need a BEGIN/COMMIT around that, or else rethink using ON COMMIT DROP. As is, the temp table goes away instantly when the CREATE commits. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary table
Franklin Haut wrote: Hello, I´m have some problems with a temporary table, i need create a table, insert some values, make a select and at end of transaction the table must droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; In this case, the table cademp doesn´t exist at the first insert, in the same transaction. It is NOT the same transaction. By default, each STATEMENT is it's own transaction. Stick a BEGIN; before the create table, and a commit; after the select. Larry Rosenman Tks, Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RES: [PERFORM] Temporary table
Ok, it works. Thanks Franklin -Mensagem original- De: Larry Rosenman [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 23 de junho de 2006 19:08 Para: 'Franklin Haut'; pgsql-performance@postgresql.org Assunto: RE: [PERFORM] Temporary table Franklin Haut wrote: Hello, I´m have some problems with a temporary table, i need create a table, insert some values, make a select and at end of transaction the table must droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; In this case, the table cademp doesn´t exist at the first insert, in the same transaction. It is NOT the same transaction. By default, each STATEMENT is it's own transaction. Stick a BEGIN; before the create table, and a commit; after the select. Larry Rosenman Tks, Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Optimizer internals
On Thu, Jun 15, 2006 at 15:38:32 -0400, John Vincent [EMAIL PROTECTED] wrote: Any suggestions? FYI the original question wasn't meant as a poke at comparing PG to MySQL to DB2. I'm not making an yvalue judgements either way. I'm just trying to understand how we can use it the best way possible. Actually we just thought about something. With PG, we can create an index that is a SUM of the column where indexing, no? We're going to test this in a few hours. Would that be able to be satisfied by an index scan? No, that won't work. While you can make indexes on functions of a row, you can't make indexes on aggregate functions. You might find making a materialized view of the information you want can help with performance. The issues with sum are pretty much the same ones as with count. You can find a couple different ways of doing materialized views for count in the archives. There is a simple way of doing it that doesn't work well with lots of concurrent updates and a more complicated method that does work well with lots of concurrent updates. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
