473,854 Members | 1,802 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 5039
Tom Lane wrote:
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.


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. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #61
On Fri, 2003-08-22 at 10:45, Tom Lane wrote:
Jan Wieck <Ja******@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.


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

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


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

Nov 11 '05 #63
Initial beta release of plPHP http://www.postgresql.org/news/143.html

On Tue, 2003-08-19 at 10:46, David Siebert wrote:
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:
On 19 Aug 2003, Bo Lorentsen wrote:


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


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.


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


How about "Friends don't let friends use MySQL"?

Hopefully others with a stonger knowledge will provide this.
Rod




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


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

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

Nov 11 '05 #64
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.


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

Nov 11 '05 #65
Bruce Momjian wrote:
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.


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.


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. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(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 #66
After a long battle with technology,kh** *@kcilink.com (Vivek Khera), an earthling, wrote:
>> "TL" == Tom Lane <tg*@sss.pgh.pa .us> writes:


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


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.
Nov 11 '05 #67

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.