Re: [PERFORM] vacuum full max_fsm_pages question
On Tuesday 21 September 2004 00:01, Patrick Hatcher wrote:
Hello.
Couple of questions:
- Q1: Today I decided to do a vacuum full verbose analyze on a large table
that has been giving me slow performance. And then I did it again. I
noticed that after each run the values in my indexes and estimate row
version changed. What really got me wondering is the fact my indexes
report more rows than are in the table and then the estimated rows is less
than the actual amount.
The table is a read-only table that is updated 1/wk. After updating it is
vacuumed full. I've also tried reindexing but the numbers still change. Is
this normal? Below is a partial output for 4 consecutive vacuum full
analyzes. No data was added nor was there anyone in the table.
This looks normal to me for a pre 7.4 database, if I am right your running on
7.2? Basically your indexes are overgrown, so each time you run vacuum you
are shrinking the number of pages involved, which will change the row counts,
and correspondingly change the count on the table as the sampled pages
change.
- Q2: I have about a dozen 5M plus row tables. I currently have my
max_fsm_pages set to 300,000. As you can see in vacuum full output I
supplied, one table is already over this amount. Is there a limit on the
size of max_fsm_pages?
The limit is based on your memory... each page = 6 bytes. But according to
the output below you are not over 30 pages yet on that table (though you
might be on some other tables.)
CONF settings:
# - Memory -
shared_buffers = 2000 # min 16, at least max_connections*2, 8KB
each sort_mem = 12288# min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 30 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 500 # min 100, ~50 bytes each
Vacuum full information
#after second vacuum full
INFO: index emaildat_fkey now contains 8053743 row versions in 25764
pages DETAIL: 1895 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.38s/0.42u sec elapsed 11.11 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65882 pages, 3000 rows sampled, 392410 estimated
total rows
#after third vacuum full
INFO: index emaildat_fkey now contains 8052738 row versions in 25769
pages DETAIL: 890 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.08s/0.32u sec elapsed 4.36 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65874 pages, 3000 rows sampled, 392363 estimated
total rows
#after REINDEX and vacuum full
INFO: index emaildat_fkey now contains 8052369 row versions in 25771
pages DETAIL: 521 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.37s/0.35u sec elapsed 4.79 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65869 pages, 3000 rows sampled, 392333 estimated
total rows
#After vacuum full(s)
mdc_oz=# select count(*) from cdm.cdm_email_data;
count
-
5433358
(1 row)
I do think the count(*) seems a bit off based on the vacuum output above. I'm
guessing you either have blocking transactions in the way or your not giving
us a complete copy/paste of the session involved.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[PERFORM] Hyper threading?
Hi all, I searched list archives, but did not found anything about HT of Pentium 4/Xeon processors. I wonder if hyperthreading can boost or decrease performance. AFAIK for other commercial servers (msssql, oracle) official documents state something like faster, but not always, so probably slower, unless faster. User opinions are generaly more clear: better swhitch off HT. Do you have any experiance or test results regarding hyperthreading? Or what additional conditions can make HT useful or pointless? TIA, Mariusz ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] vacuum full max_fsm_pages question
Sorry. I wrote PG 7.4.2 and then I erased it to write something else and
then forgot to add it back.
And thanks for the Page info. I was getting frustrated and looked in the
wrong place.
So it's probably best to drop and readd the indexes then?
- Original Message -
From: Robert Treat [EMAIL PROTECTED]
To: Patrick Hatcher [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, September 20, 2004 11:12 PM
Subject: Re: [PERFORM] vacuum full max_fsm_pages question
On Tuesday 21 September 2004 00:01, Patrick Hatcher wrote:
Hello.
Couple of questions:
- Q1: Today I decided to do a vacuum full verbose analyze on a large
table
that has been giving me slow performance. And then I did it again. I
noticed that after each run the values in my indexes and estimate row
version changed. What really got me wondering is the fact my indexes
report more rows than are in the table and then the estimated rows is
less
than the actual amount.
The table is a read-only table that is updated 1/wk. After updating it
is
vacuumed full. I've also tried reindexing but the numbers still change.
Is
this normal? Below is a partial output for 4 consecutive vacuum full
analyzes. No data was added nor was there anyone in the table.
This looks normal to me for a pre 7.4 database, if I am right your running
on
7.2? Basically your indexes are overgrown, so each time you run vacuum you
are shrinking the number of pages involved, which will change the row
counts,
and correspondingly change the count on the table as the sampled pages
change.
- Q2: I have about a dozen 5M plus row tables. I currently have my
max_fsm_pages set to 300,000. As you can see in vacuum full output I
supplied, one table is already over this amount. Is there a limit on
the
size of max_fsm_pages?
The limit is based on your memory... each page = 6 bytes. But according
to
the output below you are not over 30 pages yet on that table (though
you
might be on some other tables.)
CONF settings:
# - Memory -
shared_buffers = 2000 # min 16, at least max_connections*2,
8KB
each sort_mem = 12288# min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 30 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 500 # min 100, ~50 bytes each
Vacuum full information
#after second vacuum full
INFO: index emaildat_fkey now contains 8053743 row versions in 25764
pages DETAIL: 1895 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.38s/0.42u sec elapsed 11.11 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65882 pages, 3000 rows sampled, 392410
estimated
total rows
#after third vacuum full
INFO: index emaildat_fkey now contains 8052738 row versions in 25769
pages DETAIL: 890 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 2.08s/0.32u sec elapsed 4.36 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65874 pages, 3000 rows sampled, 392363
estimated
total rows
#after REINDEX and vacuum full
INFO: index emaildat_fkey now contains 8052369 row versions in 25771
pages DETAIL: 521 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.37s/0.35u sec elapsed 4.79 sec.
INFO: analyzing cdm.cdm_email_data
INFO: cdm_email_data: 65869 pages, 3000 rows sampled, 392333
estimated
total rows
#After vacuum full(s)
mdc_oz=# select count(*) from cdm.cdm_email_data;
count
-
5433358
(1 row)
I do think the count(*) seems a bit off based on the vacuum output above.
I'm
guessing you either have blocking transactions in the way or your not
giving
us a complete copy/paste of the session involved.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] vacuum full max_fsm_pages question
Patrick, Sorry. I wrote PG 7.4.2 and then I erased it to write something else and then forgot to add it back. Odd. You shouldn't be having to re-vacuum on 7.4. And thanks for the Page info. I was getting frustrated and looked in the wrong place. So it's probably best to drop and readd the indexes then? Well, I have to wonder if you've not run afoul of the known 7.4.2 bug regarding indexes. This system hasn't had an improper database shutdown or power-out in the last few weeks, has it? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] 7.4 vs 7.3 ( hash join issue )
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm having performance degradation with a view upgrading from 7.3 to 7.4, the view is a not so complex, one of his field is the result from a function. If I remove the function ( or I use a void function ) the 7.4 out perform the 7.3: On 7.4 I get: x=# explain analyze select * from v_ivan_2; ~ QUERY PLAN - ~ Hash Left Join (cost=7028.36..16780.89 rows=65613 width=288) (actual time=2059.923..9340.043 rows=79815 loops=1) ~ Hash Cond: (outer.id_baa_loc = inner.id_baa_loc) ~ - Hash Left Join (cost=6350.62..15134.25 rows=65613 width=258) (actual time=1816.013..7245.085 rows=65609 loops=1) ~ Hash Cond: (outer.id_localita = inner.id_localita) ~ - Hash Left Join (cost=6252.93..14786.74 rows=65613 width=247) (actual time=1777.072..6533.316 rows=65609 loops=1) ~ Hash Cond: (outer.id_frazione = inner.id_frazione) ~ - Hash Left Join (cost=6226.61..14362.74 rows=65613 width=235) (actual time=1768.273..5837.104 rows=65609 loops=1) ~ Hash Cond: (outer.id_baa = inner.id_baa) ~ - Hash Left Join (cost=5092.24..12342.65 rows=65594 width=197) (actual time=1354.059..4562.398 rows=65592 loops=1) ~ Hash Cond: (outer.id_pratica = inner.id_pratica) ~ - Hash Left Join (cost=3597.52..10010.84 rows=65594 width=173) (actual time=785.775..3278.372 rows=65592 loops=1) ~ Hash Cond: (outer.id_pratica = inner.id_pratica) ~ - Hash Join (cost=1044.77..6605.97 rows=65594 width=149) (actual time=274.316..2070.788 rows=65592 loops=1) ~ Hash Cond: (outer.id_stato_pratica = inner.id_stato_pratica) ~ - Hash Join (cost=1043.72..5850.59 rows=65593 width=141) (actual time=273.478..1421.274 rows=65592 loops=1) ~ Hash Cond: (outer.id_pratica = inner.id_pratica) ~ - Seq Scan on t_pratica p (cost=0.00..3854.27 rows=65927 width=137) (actual time=7.275..533.281 rows=65927 loops=1) ~ - Hash (cost=1010.92..1010.92 rows=65592 width=8) (actual time=265.615..265.615 rows=0 loops=1) ~ - Seq Scan on t_baa_pratica bp (cost=0.00..1010.92 rows=65592 width=8) (actual time=0.209..164.761 rows=65592 loops=1) ~ - Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.254..0.254 rows=0 loops=1) ~ - Seq Scan on lookup_stato_pratica s (cost=0.00..1.05 rows=5 width=22) (actual time=0.190..0.210 rows=5 loops=1) ~ - Hash (cost=2519.82..2519.82 rows=65865 width=28) (actual time=511.104..511.104 rows=0 loops=1) ~ - Seq Scan on t_persona (cost=0.00..2519.82 rows=65865 width=28) (actual time=0.068..381.586 rows=65864 loops=1) ~ Filter: (is_rich = true) ~ - Hash (cost=1462.53..1462.53 rows=64356 width=28) (actual time=567.919..567.919 rows=0 loops=1) ~ - Index Scan using idx_t_persona_is_inte on t_persona (cost=0.00..1462.53 rows=64356 width=28) (actual time=12.953..432.697 rows=64356 loops=1) ~ Index Cond: (is_inte = true) ~ - Hash (cost=1113.65..1113.65 rows=41444 width=46) (actual time=413.782..413.782 rows=0 loops=1) ~ - Hash Join (cost=4.33..1113.65 rows=41444 width=46) (actual time=2.687..333.746 rows=41444 loops=1) ~ Hash Cond: (outer.id_comune = inner.id_comune) ~ - Seq Scan on t_baa_loc bl (cost=0.00..653.44 rows=41444 width=20) (actual time=0.422..94.803 rows=41444 loops=1) ~ - Hash (cost=4.22..4.22 rows=222 width=34) (actual time=1.735..1.735 rows=0 loops=1) ~ - Seq Scan on t_comune co (cost=0.00..4.22 rows=222 width=34) (actual time=0.521..1.277 rows=222 loops=1) ~ - Hash (cost=25.59..25.59 rows=1459 width=20) (actual time=8.343..8.343 rows=0 loops=1) ~ - Seq Scan on t_frazione f (cost=0.00..25.59 rows=1459 width=20) (actual time=0.554..5.603 rows=1459 loops=1) ~ - Hash (cost=94.94..94.94 rows=5494 width=19) (actual time=38.504..38.504 rows=0 loops=1) ~ - Seq Scan on t_localita l (cost=0.00..94.94
