Yeah, I was thinking about writing it but I only know the high level and
not the details.
Reading Jonathan Lewis' Oracle 8i intro blew me away. It was such clear
powerful description of Oracle.
I'd love to see that for the 4 main databases.
When you do a select what happens? WHen you do a transaction ? what happens
and in what order? Of course I knew at one time and remember the basics but
not the details.
like a transaction, you get an ITL , you write some stuff to the rollback,
everything including rollback gets written to the redo etc etc delayed
block clean out etc etc
What happens on Postgres? I don't know. You stuff your new data in tothe
block, leave the olds stuff, the vacuum process comes along and clean out
all the bloat as long as there is no one anywhere in the database including
completely unrelated databases and unrelated schemas with a transaction
open etc. etc
What happens on SQL server? what get gets written to rollback ? there is
only rollback written when there is row level lockiing?
What happens on MySQL? what is history length list? How does MySQL track
and rebuild versions of blocks? Does it just leave the old data in the
blocks until some later date? Who cleans it out and how?
On Wed, Mar 1, 2023 at 1:44 PM John Thomas <jt2354@xxxxxxxxx> wrote:
Kyle, sounds like you have a first draft :-) We'll look forward to the
published version!
Oh, and MySQL's equivalent of a shared pool mechanism was pretty worthless
some years ago.
Regards,
John Thomas
On Wed, 1 Mar 2023 at 20:55, kyle Hailey <kylelf@xxxxxxxxx> wrote:
Anyone know of good doc on the comparisons across the major RDMS ,
like the high level performance OMGs?
Examples
- SQL Server by default is missing row level locking (OMG! of course
you can turn it on at a cost)
- Postgres , any open transaction, be it "create table foo(id int);
select * from foo; " tanks performance for everyone on the DB doing
transactions, everyone.
- Postgres - you wrap your transaction IDs and your database goes
down a day or two
- MySQL has similar issue with "create table foo(id int); select *
from foo; " where the history length list grows.
- MySQL wait events are worthless. the main wait, like 80% of all
waits is a CPU, IO and LOCK all rolled into one, yes indeed, worthless OMG
Kyle
