Assume a scenario where the DBMS wants to write something to the disk. If we leave it to the OS, it might schedule it for a later time. However, it would be best in the interest of the DBMS that it be written immediately. And this would be a contention.
Hi Andy! I probably won't get a response but I am gonna shoot my question anyways 😅 If the ctid value returns the physical location of the tuple on disk (page_id, slot_id) then how come multiple tables have common ctid values? I fetched the ctid values of two different tables and noticed that both results had a lot of common (page_id,slot_id)
AFAIK, that's physically impossible (in Postgres). You can test it easily with this query (assuming "id" is the primary key): SELECT * FROM xxx AS t1, xxx AS t2 WHERE t1.id != t2.id AND t1.ctid = t2.ctid;
What's the point of database page size? Let's say my hardware page is 4KB, is there any advantage by having my database page size more than 4KB? because if we have larger than 4KB there is no change in sequential access performance, because my disk page size is 4KB anyways,
Ideally database page size should be a multiple of hardware page size (say 8KB), then it's almost guaranteed to stored in adjacent with each other. Since sequential scan is fast and random access it slow, it can improve the performance. Besides, a larger database pages can reduce the overhead associated with page metadata or something like that. However, it can also lead to a suboptimal cache utilisation if the database page size is much larger than the hardware page size. It may also lead to fragmentation problem. So the ultimate answer is it depends? :|
Very nice and interesting! I have 15+ years in software development and data base usage and many of these things are new to me.
I should admit I've watched a lot of cats and puppies on TH-cam. But this is the best content here.
coolest lecture I ever listened to
The lectures are undoubtedly brilliant. But, I really really love the intro and the outro too. What a production! So cool.
Andy is badass. That's the comment.
insightful lecture
28:30 Why would there be contention over data structures which are internal to the OS?
Assume a scenario where the DBMS wants to write something to the disk. If we leave it to the OS, it might schedule it for a later time. However, it would be best in the interest of the DBMS that it be written immediately. And this would be a contention.
Which architecture mentioned by the instructor at timestamp 6:21? Not so clear
von neumann architecture
could not find a way to get record id of page and slot id of tuple in mysql.
The cartoon he talks about 34:12
th-cam.com/video/1BRGU_AS25c/w-d-xo.html
Hi Andy! I probably won't get a response but I am gonna shoot my question anyways 😅
If the ctid value returns the physical location of the tuple on disk (page_id, slot_id) then how come multiple tables have common ctid values? I fetched the ctid values of two different tables and noticed that both results had a lot of common (page_id,slot_id)
AFAIK, that's physically impossible (in Postgres). You can test it easily with this query (assuming "id" is the primary key):
SELECT * FROM xxx AS t1, xxx AS t2 WHERE t1.id != t2.id AND t1.ctid = t2.ctid;
What's the point of database page size? Let's say my hardware page is 4KB, is there any advantage by having my database page size more than 4KB? because if we have larger than 4KB there is no change in sequential access performance, because my disk page size is 4KB anyways,
Ideally database page size should be a multiple of hardware page size (say 8KB), then it's almost guaranteed to stored in adjacent with each other. Since sequential scan is fast and random access it slow, it can improve the performance. Besides, a larger database pages can reduce the overhead associated with page metadata or something like that.
However, it can also lead to a suboptimal cache utilisation if the database page size is much larger than the hardware page size. It may also lead to fragmentation problem.
So the ultimate answer is it depends? :|
@andypavlo any insights from you?
@@ynchenExactly, it’s basically to align to disk page size (multiple of it). Memory pages are also 4KB by default.
A what fly infestation? @1:00:08
Legend!
thanks a lot.
didnt know databses could be so... gangsta
Test
It works.