473,854 Members | 1,830 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Buglist

Hi ...

I'm trying to convince my boss to use posgresql (I need RI, transactions
and views), but he keeps comparing the project to mysql. Until now, I
found the answers to he's questions on the www.postgresql.org page, but
now I'm lost :-)

Where do I find a list of bugs both found and solved, or will I need to
ask on the pgsql-bugs list to know the answer ?

Also have anyone tryed to compare the new transaction model in MySQL 4.x
to PostgreSQL ?

I'm looking forward to recive even more constructive arguements :-)

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

Nov 11 '05
66 5038
On Thu, Aug 21, 2003 at 08:38:14PM +0530, Shridhar Daithankar wrote:
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?


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
<an****@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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #51
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:
On Wed, Aug 20, 2003 at 11:41:41PM +0200, Karsten Hilbert wrote:
You mean, like, "nice 19" or so ?


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
<an****@liberty rms.info> M2P 2A8
+1 416 646 3304 x110
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

Nov 11 '05 #52
On Thu, Aug 21, 2003 at 12:05:28PM -0400, Edmund Dengler wrote:
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.


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
<an****@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 ma*******@postg resql.org)

Nov 11 '05 #53
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:
On Thu, Aug 21, 2003 at 12:05:28PM -0400, Edmund Dengler wrote:
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.


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
<an****@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 ma*******@postg resql.org)

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

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

Nov 11 '05 #54
On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
<sh************ *****@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 ...

Servus
Manfred

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

Nov 11 '05 #55
On Thu, 21 Aug 2003 17:56:02 -0400, Tom Lane <tg*@sss.pgh.pa .us>
wrote:
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).


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 ma*******@postg resql.org

Nov 11 '05 #56
Manfred Koizar wrote:
On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
<sh************ *****@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 ...


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. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 11 '05 #57
On 21 Aug 2003 at 18:46, Jan Wieck wrote:
Manfred Koizar wrote:
On Thu, 21 Aug 2003 21:10:34 +0530, "Shridhar Daithankar"
<sh************ *****@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 ...


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.


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 ma*******@postg resql.org)

Nov 11 '05 #58
On Fri, 22 Aug 2003 12:15:33 +0530, "Shridhar Daithankar"
<sh************ *****@persisten t.co.in> wrote:
Which leads us to a zero gravity vacuum, that does the lazy vacuum for
pages currently available in the buffer cache only. [...]


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


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

Nov 11 '05 #59
Jan Wieck <Ja******@Yahoo .com> writes:
Shridhar Daithankar wrote:
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.
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.


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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #60

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.