Buglist

[Jan Wieck]

Re: [HACKERS] Buglist

Tom Lane wrote:
[color=blue]
> Jan Wieck <JanWieck@Yahoo .com> writes:[color=green]
>> Shridhar Daithankar wrote:[color=darkred]
>>> Umm.. What does FSM does then? I was under impression that FSM stores page
>>> pointers and vacuum work on FSM information only. In that case, it wouldn't
>>> have to waste time to find out which pages to clean.[/color][/color]
>[color=green]
>> It's the other way around! VACUUM scan's the tables to find and reclaim
>> free space and remembers that free space in the FSM.[/color]
>
> Right. One big question mark in my mind about these "partial vacuum"
> proposals is whether they'd still allow adequate FSM information to be
> maintained. If VACUUM isn't looking at most of the pages, there's no
> very good way to acquire info about where there's free space.[/color]

That's why I think it needs one more pg_stat column to count the number
of vacuumed tuples. If one does

tuples_updated + tuples_deleted - tuples_vacuumed

he'll get approximately the number of tuples a regular vacuum might be
able to reclaim. If that number is really small, no need for autovacuum
to cause any big trouble by scanning the relation.

Another way to give autovacuum some hints would be to return some number
as commandtuples from vacuum. like the number of tuples actually
vacuumed. That together with the new number of reltuples in pg_class
will tell autovacuum how frequent a relation really needs scanning.


Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== JanWieck@Yahoo. com #


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Matthew T. O'Connor]

Re: [HACKERS] Buglist

On Fri, 2003-08-22 at 10:45, Tom Lane wrote:[color=blue]
> Jan Wieck <JanWieck@Yahoo .com> writes:
> Right. One big question mark in my mind about these "partial vacuum"
> proposals is whether they'd still allow adequate FSM information to be
> maintained. If VACUUM isn't looking at most of the pages, there's no
> very good way to acquire info about where there's free space.[/color]

Well, pg_autovacuum really needs to be looking at the FSM anyway. It
could look at the FSM, and choose to to do a vacuum normal when there
the amount of FSM data becomes inadequate. Of course I'm not sure how
you would differentiate a busy table with "inadequate " FSM data and an
inactive table that doesn't even register in the FSM. Perhaps you would
still need to consult the stats system.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Tom Lane]

Re: [HACKERS] Buglist

Jan Wieck <JanWieck@Yahoo .com> writes:[color=blue]
> Okay, my proposal would be to have a VACUUM mode where it tells the
> buffer manager to only return a page if it is already in memory, and
> some "not cached" if it would have to read it from disk, and simply skip
> the page in that case.[/color]

Since no such call is available at the OS level, this would only work
well with very large shared_buffers settings (ie, you try to rely on
PG shared buffers to the exclusion of kernel disk cache). AFAIK the
general consensus is that that's not a good way to run Postgres.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Franco Bruno Borghesi]

Re: Buglist

Initial beta release of plPHP http://www.postgresql.org/news/143.html

On Tue, 2003-08-19 at 10:46, David Siebert wrote:
[color=blue]
> I learned MySQL then went on to Postgres. I chose postgres for my in
> house project just because of the row locking and transactions. Looking
> back I could have used MySQL. I have yet to use stored procedures or
> many of the high level functions of Postgres however transactions make
> things so much cleaner. I do not think MySQL is a bad system. It works
> well for many people in many situations. I think that MySQL and SAP
> getting together could be very exciting. When it comes to SQL databases
> I would say we have a wealth good choices. This if I use PHP I have to
> use MySQL is a load of tripe. PHP can work just fine with Postgres. I
> hate to even suggest this but has anyone thought of adding PHP to the
> languages that you can use to write stored procedures in Postgres?
>
>
> Roderick A. Anderson wrote:
> [color=green]
> >On 19 Aug 2003, Bo Lorentsen wrote:
> >
> >
> >[color=darkred]
> >>Also have anyone tryed to compare the new transaction model in MySQL 4.x
> >>to PostgreSQL ?
> >>
> >>[/color]
> >
> >Bo, I've recently started having to deal with MySQL. (Web sites
> >wanting/using php _need/have-to-have_ MySQL. Their words not mine.) And
> >from going from a "I dislike MySQL" to "I'm really hating MySQL" has been
> >getting easier and easier.
> > My dealings with MySQL are for the 3.xx version but I semi-followed a
> >thread on this several months ago so feel fully qualified to to throw in
> >my views. :-) My take on others research was that MySQL transaction
> >model is a bubble gum and bailing wire add on not an integral part of
> >MySQL. It _was_ tacked onto the top of the database so if either it or
> >MySQL failed you were likely to loose data.
> >
> >
> >[color=darkred]
> >>I'm looking forward to recive even more constructive arguements :-)
> >>
> >>[/color]
> >
> >How about "Friends don't let friends use MySQL"?
> >
> >Hopefully others with a stonger knowledge will provide this.
> >
> >
> >Rod
> >
> >[/color]
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that your
> message can get through to the mailing list cleanly
> [/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Sma821dVnhLsBV0 RAgMPAJ9+Bz118F PgUQeudaxcfUqkR LzMQgCfTR4/
ba6G/3dmHwLp+NBEfUQR quU=
=qj7z
-----END PGP SIGNATURE-----

[Bruce Momjian]

Re: Buglist

Jan Wieck wrote:[color=blue]
> Manfred Koizar wrote:[color=green]
> > On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
> > <shridhar_daith ankar@persisten t.co.in> wrote:[color=darkred]
> >>Point I am trying to make is to tune FSM and autovacuum frequency
> >>such that you catch all the dead tuples in RAM[/color]
> >
> > You might be able to catch the pages with dead tuples in RAM, but
> > currently there's no way to keep VACUUM from reading in all the clean
> > pages, which can be far more ...[/color]
>
> Which leads us to a zero gravity vacuum, that does the lazy vacuum for
> pages currently available in the buffer cache only. And another pg_stat
> column telling the number of tuples vacuumed so that an autovac has a
> chance to avoid IO consuming vacuum runs for relations where 99% of the
> dead tuples have been caught in memory.[/color]

What would be really interesting is to look for dead tuples when you
write/discard a buffer page and add them to the FSM --- that is probably
the latest time you still have access to the page and has the highest
probability of being recyclable.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Jan Wieck]

Re: Buglist

Bruce Momjian wrote:
[color=blue]
> Jan Wieck wrote:[color=green]
>> Manfred Koizar wrote:[color=darkred]
>> > On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
>> > <shridhar_daith ankar@persisten t.co.in> wrote:
>> >>Point I am trying to make is to tune FSM and autovacuum frequency
>> >>such that you catch all the dead tuples in RAM
>> >
>> > You might be able to catch the pages with dead tuples in RAM, but
>> > currently there's no way to keep VACUUM from reading in all the clean
>> > pages, which can be far more ...[/color]
>>
>> Which leads us to a zero gravity vacuum, that does the lazy vacuum for
>> pages currently available in the buffer cache only. And another pg_stat
>> column telling the number of tuples vacuumed so that an autovac has a
>> chance to avoid IO consuming vacuum runs for relations where 99% of the
>> dead tuples have been caught in memory.[/color]
>
> What would be really interesting is to look for dead tuples when you
> write/discard a buffer page and add them to the FSM --- that is probably
> the latest time you still have access to the page and has the highest
> probability of being recyclable.
>[/color]

True, but it's again in the time critical path of a foreground
application because it's done by a backend who has to read another page
on behalf of a waiting client right now. Also, there is only a small
probability that all the pages required to do the index purge for the
tuples reclaimed are in memory too. Plus there is still no direct
connection between a heap tuples ctid and the physical location of it's
index tuples, so purging an index requires a full scan of it, which is
best done in bulk operations.


Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== JanWieck@Yahoo. com #


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Christopher Browne]

Re: Buglist

After a long battle with technology,kher a@kcilink.com (Vivek Khera), an earthling, wrote:[color=blue][color=green][color=darkred]
>>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa .us> writes:[/color][/color]
>
> TL> Just nice'ing the VACUUM process is likely to be counterproducti ve
> TL> because of locking issues (priority inversion). Though if anyone cares
> TL> to try it on a heavily-loaded system, I'd be interested to hear the
> TL> results...
>
> tried it once. didn't make much difference except that vacuum took
> longer than normal. i didn't see any deadlocks.
>
> i actually figured out what my main problem was. vacuum every 6 hours
> on my two busiest tables was taking longer than 6 hours when we were
> very busy...[/color]

I "wedged" a database server once that way; it was busy, busy, busy
with a multiplicity of processes trying to simultaneously vacuum the
same table.

The "new generation" resolution to that is pg_autovacuum; if you're
running a pre-7.3 version, a good idea is basically to have a vacuum
script that checks a "lock file" and exits if it sees that another
process is already busy vacuuming.
--
output = reverse("gro.mc a" "@" "enworbbc")

http://www.ntlug.org/~cbbrowne/postgresql.html

"I am aware of the benefits of a micro kernel approach. However, the
fact remains that Linux is here, and GNU isn't --- and people have
been working on Hurd for a lot longer than Linus has been working on
Linux." -- Ted T'so, 1992.