Re: Oracle vs SQL Server vs MySQL vs Postgres

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

--
http://www.freelists.org/webpage/oracle-l