473,836 Members | 2,205 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 5028
On Wed, Aug 20, 2003 at 12:40:03PM -0400, Vivek Khera wrote:
>> "BW" == Bruno Wolff, <Bruno> writes:

BW> Also, since at least 7.3, normal vacuums aren't normally going to
BW> affect the performance of your database server that much.

I disagree. Triggering a vacuum on a db that is nearly saturating the
disk bandwidth has a significant impact.


Vivek is right about this. If your system is already very busy, then
a vacuum on a largish table is painful.

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.

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 #41
Andrew Sullivan wrote:
On Wed, Aug 20, 2003 at 12:40:03PM -0400, Vivek Khera wrote:
>>>>> "BW" == Bruno Wolff, <Bruno> writes:

BW> Also, since at least 7.3, normal vacuums aren't normally going to
BW> affect the performance of your database server that much.

I disagree. Triggering a vacuum on a db that is nearly saturating the
disk bandwidth has a significant impact.


Vivek is right about this. If your system is already very busy, then
a vacuum on a largish table is painful.

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.


What about a little hint to the buffer management that if it has to
evict another buffer to physically read this one (meaning the buffer
pool was full already) then it will not put this buffer at the top of
the LRU chain but rather at it's end? This way a vacuum on a large table
will not cause a complete cache eviction.

Might be a useful hint for sequential scans too.
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 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #42
> Just nice'ing the VACUUM process is likely to be counterproducti ve
because of locking issues (priority inversion).

OK, getting out the brown paper bag |-(

Is there any concept of db engine idleness obtainable from
states of PG internal variables that might be leveraged ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

---------------------------(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 #43
Jan Wieck <Ja******@Yahoo .com> writes:
What about a little hint to the buffer management that if it has to
evict another buffer to physically read this one (meaning the buffer
pool was full already) then it will not put this buffer at the top of
the LRU chain but rather at it's end? This way a vacuum on a large table
will not cause a complete cache eviction.


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
OS offers a facility for requesting this, it'd be worth experimenting
with.

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 #44
On Wed, Aug 20, 2003 at 05:21:35PM -0400, Jan Wieck wrote:
What about a little hint to the buffer management that if it has to
evict another buffer to physically read this one (meaning the buffer
pool was full already) then it will not put this buffer at the top of
the LRU chain but rather at it's end? This way a vacuum on a large table
will not cause a complete cache eviction.

Might be a useful hint for sequential scans too.


Somebody was playing with using LRU-2 or ARC or some other algorithm for
page replacement instead of the current LRU... wouldn't it help with
this too?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La vida es para el que se aventura"

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

http://archives.postgresql.org

Nov 11 '05 #45
On Wed, Aug 20, 2003 at 05:41:18PM -0400, Tom Lane wrote:
Andrew Sullivan <an****@liberty rms.info> writes:
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.
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...


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 <kl*****@svana. org> http://svana.org/kleptog/ "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


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

Nov 11 '05 #46
On 19 Aug 2003 at 15:26, Bo Lorentsen wrote:
On Tue, 2003-08-19 at 14:31, Shridhar Daithankar wrote:
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.. 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
Check this.. http://www.mysql.com/doc/en/ANSI_diff_Transactions.html

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.


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

This sounds like there MyISAM tables, or ???


I haven't used mysql to be that expert.. sorry..
Few major differences I can see right here. Correct me on mysql side.

- WAL log
- Transactabl DDLs Yes and lets add :
- Views
- subselects
- plperl, plsql, plpython, plXXX


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...
------------- * 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:
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..
- Nested transactions coming soon to PG
- PITR coming soon to PG

Not good for argumenting with my boss about future :-)


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..
I would love to see entire checklist but don't have any time to devote to
mysql.

I do understand, and its no pleasure either :-)


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

Nov 11 '05 #47
On Thu, Aug 21, 2003 at 03:40:29PM +1000, Martijn van Oosterhout wrote:
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.


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
<an****@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

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

Nov 11 '05 #49
On 21 Aug 2003 at 10:59, Andrew Sullivan wrote:
On Wed, Aug 20, 2003 at 05:58:32PM -0400, Tom Lane wrote:
Jan Wieck <Ja******@Yahoo .com> writes:
the LRU chain but rather at it's end? This way a vacuum on a large table
will not cause a complete cache eviction.


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


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.


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

Nov 11 '05 #50

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.