Обсуждение: Dumb question - how to tell if autovacuum is doing its job in 8.2.x
Dumb question - how to tell if autovacuum is doing its job in 8.2.x
От
Sic Transit Gloria Mundi
Дата:
Hi,
I couldnt find this on google, the archives, or the manual. But with the changes to what the autovacuum daemon logs, how can we verify it's doing its thing? Is there a way to query the last time a table was vacuumed? But I don't see that in the system catalog.
Thanks!
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
Sic Transit Gloria Mundi <sitrglmu@yahoo.com> writes: > I couldnt find this on google, the archives, or the manual. But with the changes to what the autovacuum daemon logs, howcan we verify it's doing its thing? Is there a way to query the last time a table was vacuumed? But I don't see thatin the system catalog. Try the pg_stat views. regards, tom lane
pg_stat_all_table view should help you:
select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/9/07, Sic Transit Gloria Mundi < sitrglmu@yahoo.com> wrote:
Hi,
I couldnt find this on google, the archives, or the manual. But with the changes to what the autovacuum daemon logs, how can we verify it's doing its thing? Is there a way to query the last time a table was vacuumed? But I don't see that in the system catalog.
Thanks!
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
Shoaib Mir wrote: > pg_stat_all_table view should help you: > > select last_autovacuum, last_autoanalyze from pg_stat_all_tables; select last_autovacuum, last_autoanalyze from pg_stat_all_tables; last_autovacuum | last_autoanalyze -----------------+------------------ | ...snip lots of identically blank lines... | | (939 rows) Does that mean it's working or not configured right? Thanks, Walter
Hello List,
I want to append column of two different tables in a single column of a view .
data type of tow column of two diffrent tables will be same.
WHAT I WANT TO DO IS:
Table 1 | |
ID | DESC |
1 | A |
2 | B |
3 | C |
Table 2 | |
ID | DESC |
1 | D |
2 | E |
3 | F |
View(Table1|| Table 2) | |
ID_view | Desc |
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
6 | F |
Is there any way???
Thanks in advance
Ashish...
Ashish Karalkar wrote: > Hello List, > > I want to append column of two different tables in a single column of a view . > > data type of tow column of two diffrent tables will be same. > > WHAT I WANT TO DO IS: > > Table 1 > ID DESC > 1 A > 2 B > 3 C > > Table 2 > ID DESC > 1 D > 2 E > 3 F > > View(Table1|| Table 2) > ID_view Desc > > 1 A > 2 B > 3 C > 4 D > 5 E > 6 F > > Is there any way??? Looks like a UNION ALL to me. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Ashish Karalkar wrote: > Hello List, > > I want to append column of two different tables in a single column of a view . > > data type of tow column of two diffrent tables will be same. > > WHAT I WANT TO DO IS: > > Table 1 > ID DESC > 1 A > 2 B > 3 C > > > > > > Table 2 > ID DESC > 1 D > 2 E > 3 F > > > > View(Table1|| Table 2) > ID_view Desc > > 1 A > 2 B > 3 C > 4 D > 5 E > 6 F > > > > Is there any way??? A union - SELECT id,desc FROM table1 UNION SELECT id,desc FROM table2; This will give you ID_view Desc 1 A 2 B 3 C 1 D 2 E 3 F If you actually want the id_view column to show 1 through 6 then you will want to generate a sequence that is shown for that column instead of the original id column. Or generate the id_view in the client, such as use the row position in the returned set. -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
I guess UNION ALL should work good here instead of a UNION for the exact same kind of output he needs:
SELECT id,desc FROM table1
UNION ALL
SELECT id,desc FROM table2;
---+---
1 | A
2 | B
3 | C
1 | D
2 | E
3 | F
As UNION gave me a little different output, like this:
---+--
1 | A
1 | D
2 | B
2 | E
3 | C
3 | F
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
SELECT id,desc FROM table1
UNION ALL
SELECT id,desc FROM table2;
---+---
1 | A
2 | B
3 | C
1 | D
2 | E
3 | F
As UNION gave me a little different output, like this:
---+--
1 | A
1 | D
2 | B
2 | E
3 | C
3 | F
--
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)
On 2/13/07, Shane Ambler <pgsql@007marketing.com> wrote:
Ashish Karalkar wrote:
> Hello List,
>
> I want to append column of two different tables in a single column of a view .
>
> data type of tow column of two diffrent tables will be same.
>
> WHAT I WANT TO DO IS:
>
> Table 1
> ID DESC
> 1 A
> 2 B
> 3 C
>
>
>
>
>
> Table 2
> ID DESC
> 1 D
> 2 E
> 3 F
>
>
>
> View(Table1|| Table 2)
> ID_view Desc
>
> 1 A
> 2 B
> 3 C
> 4 D
> 5 E
> 6 F
>
>
>
> Is there any way???
A union -
SELECT id,desc FROM table1
UNION
SELECT id,desc FROM table2;
This will give you
ID_view Desc
1 A
2 B
3 C
1 D
2 E
3 F
If you actually want the id_view column to show 1 through 6 then you
will want to generate a sequence that is shown for that column instead
of the original id column. Or generate the id_view in the client, such
as use the row position in the returned set.
--
Shane Ambler
pgSQL@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
Shoaib Mir wrote: > I guess UNION ALL should work good here instead of a UNION for the exact > same kind of output he needs: That would be UNION ordering the results to remove duplicate rows which UNION ALL doesn't do. Technically the results from any query can come back in any order unless an ORDER BY is included. > SELECT id,desc FROM table1 > UNION ALL > SELECT id,desc FROM table2; > > ---+--- > 1 | A > 2 | B > 3 | C > 1 | D > 2 | E > 3 | F > > > As UNION gave me a little different output, like this: > > ---+-- > 1 | A > 1 | D > 2 | B > 2 | E > 3 | C > 3 | F > > -- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > On 2/13/07, Shane Ambler <pgsql@007marketing.com> wrote: >> >> Ashish Karalkar wrote: >> > Hello List, >> > >> > I want to append column of two different tables in a single column >> of a >> view . >> > >> > data type of tow column of two diffrent tables will be same. >> > >> > WHAT I WANT TO DO IS: >> > >> > Table 1 >> > ID DESC >> > 1 A >> > 2 B >> > 3 C >> > >> > >> > >> > >> > >> > Table 2 >> > ID DESC >> > 1 D >> > 2 E >> > 3 F >> > >> > >> > >> > View(Table1|| Table 2) >> > ID_view Desc >> > >> > 1 A >> > 2 B >> > 3 C >> > 4 D >> > 5 E >> > 6 F >> > >> > >> > >> > Is there any way??? >> >> A union - >> >> SELECT id,desc FROM table1 >> UNION >> SELECT id,desc FROM table2; >> >> This will give you >> >> ID_view Desc >> >> 1 A >> 2 B >> 3 C >> 1 D >> 2 E >> 3 F >> >> >> If you actually want the id_view column to show 1 through 6 then you >> will want to generate a sequence that is shown for that column instead >> of the original id column. Or generate the id_view in the client, such >> as use the row position in the returned set. >> >> >> >> >> -- >> >> Shane Ambler >> pgSQL@007Marketing.com >> >> Get Sheeky @ http://Sheeky.Biz >> > -- Shane Ambler pgSQL@007Marketing.com Get Sheeky @ http://Sheeky.Biz
So hmm a UNION with an ORDERY BY should be good for this scenario...
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/13/07, Shane Ambler <pgsql@007marketing.com> wrote:
Shoaib Mir wrote:
> I guess UNION ALL should work good here instead of a UNION for the exact
> same kind of output he needs:
That would be UNION ordering the results to remove duplicate rows which
UNION ALL doesn't do. Technically the results from any query can come
back in any order unless an ORDER BY is included.
> SELECT id,desc FROM table1
> UNION ALL
> SELECT id,desc FROM table2;
>
> ---+---
> 1 | A
> 2 | B
> 3 | C
> 1 | D
> 2 | E
> 3 | F
>
>
> As UNION gave me a little different output, like this:
>
> ---+--
> 1 | A
> 1 | D
> 2 | B
> 2 | E
> 3 | C
> 3 | F
>
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
> On 2/13/07, Shane Ambler <pgsql@007marketing.com > wrote:
>>
>> Ashish Karalkar wrote:
>> > Hello List,
>> >
>> > I want to append column of two different tables in a single column
>> of a
>> view .
>> >
>> > data type of tow column of two diffrent tables will be same.
>> >
>> > WHAT I WANT TO DO IS:
>> >
>> > Table 1
>> > ID DESC
>> > 1 A
>> > 2 B
>> > 3 C
>> >
>> >
>> >
>> >
>> >
>> > Table 2
>> > ID DESC
>> > 1 D
>> > 2 E
>> > 3 F
>> >
>> >
>> >
>> > View(Table1|| Table 2)
>> > ID_view Desc
>> >
>> > 1 A
>> > 2 B
>> > 3 C
>> > 4 D
>> > 5 E
>> > 6 F
>> >
>> >
>> >
>> > Is there any way???
>>
>> A union -
>>
>> SELECT id,desc FROM table1
>> UNION
>> SELECT id,desc FROM table2;
>>
>> This will give you
>>
>> ID_view Desc
>>
>> 1 A
>> 2 B
>> 3 C
>> 1 D
>> 2 E
>> 3 F
>>
>>
>> If you actually want the id_view column to show 1 through 6 then you
>> will want to generate a sequence that is shown for that column instead
>> of the original id column. Or generate the id_view in the client, such
>> as use the row position in the returned set.
>>
>>
>>
>>
>> --
>>
>> Shane Ambler
>> pgSQL@007Marketing.com
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>
--
Shane Ambler
pgSQL@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
Shoaib Mir wrote: > So hmm a UNION with an ORDERY BY should be good for this scenario... Only if the order matters to the OP, but he can always perform an ORDER BY on the queries on his view. I don't really see the point. The main difference between UNION and UINION ALL is that the latter allows for duplicates, which removes the need to unduplicate the results of the UNION (which requires ordering and is therefore relatively expensive). In short; if duplicates don't matter (or are desirtable even) UNION ALL is faster. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
On 2/13/07, Walter Vaughan <wvaughan@steelerubber.com> wrote:
select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
last_autovacuum | last_autoanalyze
-----------------+------------------
|
...snip lots of identically blank lines...
|
|
(939 rows)
Does that mean it's working or not configured right?
It means auto vacuum/analyze did not trigger on any of the
tables. You may want to try:
SELECT name, setting from pg_settings where name like '%autovacuum%';
to get the settings of autovacuum and check if autovacuum is turned on or not.
Thanks,
Pavan
--
EnterpriseDB http://www.enterprisedb.com
Make sure you have stats collector enabled, if auto vacuum is doing the analyze and vacuum it should be recording that info in this view. For details on this you can have a look at --> http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html
Just for a test try doing a VACUUM or ANALYZE manually and see if that gets updated in the last_vacuum of pg_stats_all_tables.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Just for a test try doing a VACUUM or ANALYZE manually and see if that gets updated in the last_vacuum of pg_stats_all_tables.
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/13/07, Walter Vaughan < wvaughan@steelerubber.com> wrote:
Shoaib Mir wrote:
> pg_stat_all_table view should help you:
>
> select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
select last_autovacuum, last_autoanalyze from pg_stat_all_tables;
last_autovacuum | last_autoanalyze
-----------------+------------------
|
...snip lots of identically blank lines...
|
|
(939 rows)
Does that mean it's working or not configured right?
Thanks,
Walter
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly