Thread: [Fwd: MySQL Benchmark page - Problem with vacuum() in PostgreSQL]
Hi all, The "MySQL Benchmark" page on the mysql.com website has benchmarks for various databases : http://www.mysql.com/information/benchmarks.html In the PostgreSQL entry they aren't able to report an "accurate" result for all things, as they haven't been able to get vacuum to work 100% correctly. If someone can take a look at the description of the problem which Monty is having (he's the lead MySQL developer) we'll be able to have accurate benchmark for PostgreSQL on their benchmark page. So, anyone want to lend a hand? :-) Regards and best wishes, Justin Clift -------- Original Message -------- Subject: MySQL Benchmark page - Problem with vacuum() in PostgreSQL Date: Sat, 28 Jul 2001 14:01:22 +0200 From: Anna Ewerlid <Anna.Ewerlid@signal.uu.se> To: justin@postgresql.org Hi! >>>>> "Justin" == Justin Clift <justin@postgresql.org> writes: Justin> Hi, Justin> I'm looking at your MySQL benchmark page : Justin> http://www.mysql.com/information/benchmarks.html Justin> It says you guys weren't able to get vacuum() working reliably with Justin> PostgreSQL 7.1.1, and you'll generate a fast version of the benchmarks Justin> when you can. Justin> What exactly was the problem? I have tried to explain the problem in document that we have published under the benchmarks results. The problem was that when we run the benchmark with the --fast option, which basicly does a vacuum() between after each batch of updates, postmaster started to fill up disk with log files during one of the vacuum() runs and didn't stop until the disk was full. When this happened I killed postmaster and tried to restart it, but postmaster just died with a core dump after that :( I repeated the above a couple of times and was able to repeat the problem with disk full, but didn't manage to crash postmaster again. I would really appreciate if you could help us locate and fix the problem is PostgreSQL, because I would really like to see that all open source databases gets good benchmarks results to be able to deliver the message that open source is a good alternative to commercial databases. If you have any time over, please take a look at the benchmarks and see if there is anything that could be improved to get better results for PostgreSQL, especially when running with the --fast option! If you think we have missed any important 'single user' benchmark, feel free to add it to the benchmark suite and email Anna and me a patch; We really want to be able to show which operations are fast and which are slow for the different databases, so that the different database users know to which constructs they have to avoid! We just hired Anna to start working on a multi-user benchmark suite that will be a complement to the current single-user benchmark suite. If you have any comments about the benchmarks, please share them with us! I have worked hard to make them fair against all databases; I have actually put more work into getting PostgreSQL to give good numbers than for any other database! The problem is that I don't know PostgreSQL intimately which makes it a bit harder to get really benchmark results for it! Regards, Monty
Justin Clift <justin@postgresql.org> forwards: > The problem was that when we run the benchmark with the --fast option, > which basicly does a vacuum() between after each batch of updates, > postmaster started to fill up disk with log files during one of the > vacuum() runs and didn't stop until the disk was full. See http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html regards, tom lane
Hi! >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> Justin Clift <justin@postgresql.org> forwards: >> The problem was that when we run the benchmark with the --fast option, >> which basicly does a vacuum() between after each batch of updates, >> postmaster started to fill up disk with log files during one of the >> vacuum() runs and didn't stop until the disk was full. Tom> See Tom> http://www.ca.postgresql.org/mhonarc/pgsql-patches/2001-06/msg00061.html Tom> regards, tom lane Thanks! I will look at this as soon as I am back home from my vacation and then do a new benchmark run and update the benchmark page. Regards, Monty PS: If anyone has any ideas of things that we have missed in our current benchmark pages, please email them to Anna so that she can add these to the next generation of the benchmark suite. Some things that I know we have missed in the single user benchmark are: - Sub select (all different forms of sub select, with a comparison to normal selects for those select that can be changed to normal selects) - Foreign keys (which should contain a comparison with multi-table-delete) - Transactions - Rollback With comparison I mean that there should be at least one test that makes it easy for the user to see which construct is better for this database.
Michael Widenius wrote:
> [...]
>
> Some things that I know we have missed in the single user
> benchmark are:
> - Sub select (all different forms of sub select, with a comparison
> to normal selects for those select that can be
> changed to normal selects)
> - Foreign keys (which should contain a comparison with multi-table-delete)
> - Transactions
> - Rollback
>
> With comparison I mean that there should be at least one test that
> makes it easy for the user to see which construct is better for
> this database.
Can we clearify that point a little? Does it mean to define a
foreign key constraint in databases that support it and just
check for the error, but do all the appropriate locking and
existence checks for all operations (UPDATE/DELETE PK,
INSERT/UPDATE FK) on the client side for databases that don't
support it?
Well, especially because of the "appropriate locking", it'd
make much more sense to do it all in concurrent multiuser ...
:-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Hi! >>>>> "Jan" == Jan Wieck <JanWieck@yahoo.com> writes: Jan> Michael Widenius wrote: >> [...] >> >> Some things that I know we have missed in the single user >> benchmark are: >> - Sub select (all different forms of sub select, with a comparison >> to normal selects for those select that can be >> changed to normal selects) >> - Foreign keys (which should contain a comparison with multi-table-delete) >> - Transactions >> - Rollback >> >> With comparison I mean that there should be at least one test that >> makes it easy for the user to see which construct is better for >> this database. Jan> Can we clearify that point a little? Does it mean to define a Jan> foreign key constraint in databases that support it and just Jan> check for the error, but do all the appropriate locking and Jan> existence checks for all operations (UPDATE/DELETE PK, Jan> INSERT/UPDATE FK) on the client side for databases that don't Jan> support it? Jan> Well, especially because of the "appropriate locking", it'd Jan> make much more sense to do it all in concurrent multiuser ... Jan> :-) The plan is to (in the long run) have a test that shows ALL speed affects of foreign keys. This includes at least: - Checking the constraint - Time for forced rollback when a constraint fails - Time of cascaded deletes This above should of course be done both single and multi user. Regards, Monty