Buglist

[Martijn van Oosterhout]

Re: Buglist

On Wed, Aug 20, 2003 at 05:41:18PM -0400, Tom Lane wrote:[color=blue]
> Andrew Sullivan <andrew@liberty rms.info> writes:[color=green]
> > I don't actually think having the process done in real time will
> > help, though -- it seems to me what would be more useful is an even
> > lazier vacuum: something that could be told "clean up as cycles are
> > available, but make sure you stay out of the way." Of course, that's
> > easy to say glibly, and mighty hard to do, I expect.[/color]
>
> I'd love to be able to do that, but I can't think of a good way.
>
> Just nice'ing the VACUUM process is likely to be counterproducti ve
> because of locking issues (priority inversion). Though if anyone cares
> to try it on a heavily-loaded system, I'd be interested to hear the
> results...[/color]

How about the really simple solution: explicit yields. Along the lines of:

for each page
vacuum page
sleep 5ms

As long as you sleep without holding any locks, this means it would (very
slowly) interate over the table. If the auto-vacuum can target tables it
would be even more effective. A slightly more sophisticated version would
be:

VACUUM MAX 10MB/s (or some such syntax)

Then you just keep a count of the number of processed pages and the amount
of time and if you're getting ahead of yourself, sleep a while.

Given lazy vacuum doesn't hold locks for long periods, it could be an idea
to continuously spend 1% of your disk bandwidth on a background vacuum. As
for vacuum full, I don't know if you could do the same thing.

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/RFtNY5Twig3Ge+Y RAklHAKDB+zB/FNDsARg2DBySaN4 VM0Y5PwCgvzKc
Q9qwEvlTjJkOGQT ejP3SSOk=
=fXSP
-----END PGP SIGNATURE-----

[Shridhar Daithankar]

Re: Buglist

On 19 Aug 2003 at 15:26, Bo Lorentsen wrote:
[color=blue]
> On Tue, 2003-08-19 at 14:31, Shridhar Daithankar wrote:[color=green]
> > Well, you could look at release notes. That contains lot of information. Of
> > course archives of pgsql-bugs and pgsql-patches are the ultimate unless you
> > plan to delve into CVS history and sources..[/color]
> Ok, I just liked to find something like bugzilla, or an explanation to
> how bugs are garantied to be visible. My boos like to compare this to
> the Mysql model found on : http://bugs.mysql.com/bugstats
>[color=green]
> > Check this.. http://www.mysql.com/doc/en/ANSI_diff_Transactions.html[/color]
> Hmm, it sound like they have transactions on OmniDB tables, but these
> are really slow, and therefor they put much energy into advetising for
> the MyISAM files (non transactional). Or, am I missing something.[/color]

Pretty much true but hard to prove by evidence. I would say drop that line f
argument. Mysql has transactions. Period.

OK, if you talk about transactions across two table types, innodb not being
default, might be not being free, then it is valid.

First rule of argument. Always talk facts..
[color=blue][color=green]
> > To me, it seems that their definition of transaction is limited to preventing
> > two guys writing to same row simaltenously, which is of course a limited view
> > of things.[/color]
> This sounds like there MyISAM tables, or ???[/color]

I haven't used mysql to be that expert.. sorry..
[color=blue]
>[color=green]
> > Few major differences I can see right here. Correct me on mysql side.
> >
> > - WAL log
> > - Transactabl DDLs[/color]
> Yes and lets add :
> - Views
> - subselects
> - plperl, plsql, plpython, plXXX[/color]

Extensible operator and type definition
Table files splitting on 1GB boundary
Rules
Inheritance
True foreign keys
Data integrity ( You should watch some mysql excertps produced on advocacy)

Here is one for your reference...
-------------[color=blue]
> * PROPER USAGE OF NULL
>
> mysql> select * from ai_test where id is null;
> +----+-------+
> | id | txt |
> +----+-------+
> | 1 | hello |
> +----+-------+
> 1 row in set (0.00 sec)
>
> ;-). I digress. Off the top of my head, in no particular order:[/color]

You're not trying hard enough:

mysql> create table test3 (a date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test3 values (-1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3 values ('1996-02-31');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3 values ('1996-67-31');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test3;
+------------+
| a |
+------------+
| 0000-00-00 |
| 1996-02-31 |
| 0000-00-00 |
+------------+
3 rows in set (0.00 sec)
-------------

I wouldn't bet my shoe on such database..
[color=blue]
>[color=green]
> > - Nested transactions coming soon to PG
> > - PITR coming soon to PG[/color]
> Not good for argumenting with my boss about future :-)[/color]

May be right. But a decision maker needs to know roadmap as well. As I said in
another mail, there exists real world code for all of this and it is going to
happen. It's not a vapourware. If you can press it, I think talking about
future is a good idea..
[color=blue][color=green]
> > I would love to see entire checklist but don't have any time to devote to
> > mysql.[/color]
> I do understand, and its no pleasure either :-)[/color]

Just to be clear, my unenthusiaism to study mysql has nothing to do with my
time shortage. If I need to study mysql to understand it better and project
postgresql, I would happily do that. But I seriously don't have time..

Bye
Shridhar

--
Feel free to contact me (flames about my english and the useless of thisdriver
will be redirected to /dev/null, oh no, it's full...).(Micha el Beck, describing
the PC-speaker sound device)


---------------------------(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

[Andrew Sullivan]

Re: Buglist

On Thu, Aug 21, 2003 at 03:40:29PM +1000, Martijn van Oosterhout wrote:[color=blue]
> Given lazy vacuum doesn't hold locks for long periods, it could be
> an idea to continuously spend 1% of your disk bandwidth on a
> background vacuum. As for vacuum full, I don't know if you could do
> the same thing.[/color]

Assuming that one can keep up with the dust bunnies this way, though,
one wouldn't need to do vacuum full. This would definitely be a way
cool feature, if implementable.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Sullivan]

Re: Buglist

On Wed, Aug 20, 2003 at 11:41:41PM +0200, Karsten Hilbert wrote:[color=blue]
> You mean, like, "nice 19" or so ?[/color]

ISTR someone reporting problems with locking on the performance list
from doing exactly that. The problem is that the vacuum back end
might take a lock and then not get any processor time -- in which
case everybody else gets their processor slice but can't do anything,
because they have to wait until the niced vacuum process gets back in
line.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110


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

[Shridhar Daithankar]

Re: Buglist

On 21 Aug 2003 at 10:59, Andrew Sullivan wrote:
[color=blue]
> On Wed, Aug 20, 2003 at 05:58:32PM -0400, Tom Lane wrote:[color=green]
> > Jan Wieck <JanWieck@Yahoo .com> writes:[color=darkred]
> > > the LRU chain but rather at it's end? This way a vacuum on a large table
> > > will not cause a complete cache eviction.[/color]
> >
> > I think what we really need is a way to schedule VACUUM's I/O at a lower
> > priority than normal I/Os. Wouldn't be very portable :-( ... but if the[/color]
>
> Hey, they both sounds like nifty ideas to me! The portability sure
> worries me, though, on that I/O trick. Still, Oracle (f'rinstance)
> made all kinds of optimisations for Sun (and conversely) partly
> because, I expect, that's where a lot of their users were, and the
> performance or reliability gains were significant. Whether that is
> worth doing for PostgreSQL, when there are probably lots of other
> targets to aim at, is an open question.[/color]

Well, if you guys remember my posts on performance recently, the said project
will probably drift to mysql as performance requirement on solaris platform
seems pretty steep to postgresql.

Personally I think inserting 5M rows in 11 column table should not take more
than an hour. ( That's the performance criteria). But apparently postgresql is
not making it no matter what..

Just an FYI, I think we need to do something for solaris. If a hourse does not
drink despite being taken to water, throw him in water.. After it's the
database users who are stuck. Not Sun..

Bye
Shridhar

--
Fun Facts, #14: In table tennis, whoever gets 21 points first wins. That's how
it once was in baseball -- whoever got 21 runs first won.


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

http://archives.postgresql.org

[Andrew Sullivan]

Re: Buglist

On Thu, Aug 21, 2003 at 08:38:14PM +0530, Shridhar Daithankar wrote:[color=blue]
> If a database is clean i.e. no dead tuple, an autovacuum daemon with 1 min
> interval can achieve pretty much same result, isn't it?[/color]

But we're talking about the case of large, busy databases that have
already choked their disks. We have the same problem here in our
test machines. We start running load tests, and with vacuums nicely
scheduled and everything we start topping out on the performance
pretty quickly, because of I/O bottlenecks on the database. We know
the difference in I/O bandwidth between our test env. and the
production env., so we can put in a fudge factor for this; but that's
it.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110


---------------------------(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

[Edmund Dengler]

Re: Buglist

Well, if they are locked waiting on vacuum, then vacuum should upgrade
it's priority to the highest waiting process (priority inheritance).
This way, vacuum will be running at a priority level equivalent to who is
waiting on it.

Regards,
Ed

On Thu, 21 Aug 2003, Andrew Sullivan wrote:
[color=blue]
> On Wed, Aug 20, 2003 at 11:41:41PM +0200, Karsten Hilbert wrote:[color=green]
> > You mean, like, "nice 19" or so ?[/color]
>
> ISTR someone reporting problems with locking on the performance list
> from doing exactly that. The problem is that the vacuum back end
> might take a lock and then not get any processor time -- in which
> case everybody else gets their processor slice but can't do anything,
> because they have to wait until the niced vacuum process gets back in
> line.
>
> A
>
> --
> ----
> Andrew Sullivan 204-4141 Yonge Street
> Liberty RMS Toronto, Ontario Canada
> <andrew@liberty rms.info> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>[/color]


---------------------------(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

[Andrew Sullivan]

Re: Buglist

On Thu, Aug 21, 2003 at 12:05:28PM -0400, Edmund Dengler wrote:[color=blue]
> Well, if they are locked waiting on vacuum, then vacuum should upgrade
> it's priority to the highest waiting process (priority inheritance).
> This way, vacuum will be running at a priority level equivalent to who is
> waiting on it.[/color]

Right, but all that intelligence is something that isn't in there
now. And anyway, the real issue is I/O, not processor.

A

--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@liberty rms.info> M2P 2A8
+1 416 646 3304 x110


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Edmund Dengler]

Re: Buglist

What I am pointing out is that this is all the same issue, and that
solutions to the "we can't do priorities because of locking issues" have
existed for many years. I/O is the same as processors, it is a resource
that needs managing. So the intelligence can be made to exist, it just
needs to be made.

Now onto other questions: can vacuuming be done without locks? Can it be
done in parts (ie, lock only a bit)? Can the I/O be better managed? Is
this a general model that would work well?

I have plenty of queries that I would love to run on a "as the system
allows" basis, or on a "keep a bit of spare cycles or I/O for the
important stuff", but which I cannot specify. So a vote from me for any
mechanism that allows priorities to be specified. If this is a desired
feature, then comes the hard part of what is feasible, what can be done in
a reasonable amount of time, and of doing it.

Regards!
Ed

On Thu, 21 Aug 2003, Andrew Sullivan wrote:
[color=blue]
> On Thu, Aug 21, 2003 at 12:05:28PM -0400, Edmund Dengler wrote:[color=green]
> > Well, if they are locked waiting on vacuum, then vacuum should upgrade
> > it's priority to the highest waiting process (priority inheritance).
> > This way, vacuum will be running at a priority level equivalent to who is
> > waiting on it.[/color]
>
> Right, but all that intelligence is something that isn't in there
> now. And anyway, the real issue is I/O, not processor.
>
> A
>
> --
> ----
> Andrew Sullivan 204-4141 Yonge Street
> Liberty RMS Toronto, Ontario Canada
> <andrew@liberty rms.info> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Manfred Koizar]

Re: Buglist

On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
<shridhar_daith ankar@persisten t.co.in> wrote:[color=blue]
>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 ...

Servus
Manfred

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

[Manfred Koizar]

Re: Decent VACUUM (was: Buglist)

On Thu, 21 Aug 2003 17:56:02 -0400, Tom Lane <tgl@sss.pgh.pa .us>
wrote:[color=blue]
>Conceivably it could be a win, though,
>if you could do frequent "vacuum decent"s and only a full-scan vacuum
>once in awhile (once a day maybe).[/color]

That's what I had in mind; similar to the current situation where you
can avoid expensive VACUUM FULL by doing lazy VACUUM frequently
enough.

Servus
Manfred

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

[Jan Wieck]

Re: Buglist

Manfred Koizar wrote:[color=blue]
> On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
> <shridhar_daith ankar@persisten t.co.in> wrote:[color=green]
>>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.


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 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Shridhar Daithankar]

Re: Buglist

On 21 Aug 2003 at 18:46, 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]

Since autovacuum issues vacuum analyze only, is it acceptable to say that this
is taken care of already?

Bye
Shridhar

--
"One size fits all": Doesn't fit anyone.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Manfred Koizar]

Re: Buglist

On Fri, 22 Aug 2003 12:15:33 +0530, "Shridhar Daithankar"
<shridhar_daith ankar@persisten t.co.in> wrote:[color=blue][color=green]
>> Which leads us to a zero gravity vacuum, that does the lazy vacuum for
>> pages currently available in the buffer cache only. [...][/color]
>
>Since autovacuum issues vacuum analyze only, is it acceptable to say that this
>is taken care of already?[/color]

Even a plain VACUUM (without FULL) scans the whole relation to find
the (possibly few) pages that need to be changed. We are trying to
find a way to avoid those needless reads of clean pages, because (a)
they are IOs competing with other disk operations and (b) they push
useful pages out of OS cache and (c) of PG shared buffers. The latter
might become a non-issue with LRU-k, 2Q or ARC. But (a) and (b)
remain.

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Re: [HACKERS] Buglist

Jan Wieck <JanWieck@Yahoo .com> writes:[color=blue]
> Shridhar Daithankar wrote:[color=green]
>> 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=blue]
> 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.

regards, tom lane

---------------------------(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