How SQL Database Engines Work, by the Creator of SQLite (2008) [video] • Hacker News Board

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#121

Post by A_Person » Mon, Jun 25, 2018, 3:51 PM UTC

kimdotcom wrote: post #115

A_Person wrote: post #93

Gosh, I must say there seems to be some misunderstanding of RDBMS concepts in some posts in this thread! I was writing database systems professionally, back in the days before the RDBMS concept was even a thing. So here's my (enormously long and convoluted) 2 cents worth. Please be sure to pack a sandwich and get hydrated before you continue. Say you were dealing with doctors and patients, and needed to store that in…

So, were you writing DB software before Codd's research at IBM was available?

Further to my other reply, I've just checked the intertubes, and found that Codd's paper "A Relational Model of Data for Large Shared Data Banks" was published in 1970, and Dijkstra's "Go To Statement Considered Harmful" in 1968. So I think my fading memories of all this are accurate (for once!).

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#122

Post by logicallee » Mon, Jun 25, 2018, 3:53 PM UTC

acqq wrote: post #90

logicallee wrote: post #79

Earlier quoted context omitted.

Perhaps I was naive about the state of the art. After the now-dead reply I received, I searched, and found a couple of papers like this https://arxiv.org/abs/1801.04590 - "Frame-Recurrent Video Super-Resolution" but if you look at p. 8, I think many of the algorithms still wouldn't end up with readable text. This paper is from this year, so it is an area of active research. I wrote a quick mail to the authors to see…

It's surely interesting that you try to figure out the "state of art" but as far as I see the amount of information is too low to recover more readability, and that was my estimate even before your experiments. The way I saw it, the algorithm would have to model the "compression interference patterns" to do that, and even if something like that existed the amount of information seems to be far too low. If it's really…

yeah, I after I wrote those paper authors I also wrote the slide author - who is the only one who emailed me back, so I got the slides.

Still, I think more information is recoverable. For example I could tell Video Enhancer wasn't using that many frames (from the encoding progress and framerates), maybe just a few adjacent frames.

Yet you could see a very clear improvement:

https://imgur.com/a/0rhy7q7

(As though a sharpen mask was applied, but it isn't.)

I did see if I could get an improvement if I forced it to use more frames by running through a few times, each time doubling video speed so I end up with 1 frame from the whole segment - but it didn't end up better. Anyway, now I have the original.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#123

Post by A_Person » Mon, Jun 25, 2018, 4:02 PM UTC

mmjaa wrote: post #120

A_Person wrote: post #119

Earlier quoted context omitted.

Ok. Oracle had/has a presumeably similar thing (PL/SQL). I misread his comment to mean, postgres SQL.

Yes indeed, I meant PROGRESS 4GL, which was sort of around before all the "RDBMS" hoopla congealed all this wonderful info into a single art. Its just that your last paragraph really sounded like a quote, near-verbatim, from the very guides published by PROGRESS just for the purposes of educating people on how to make their data relatable .. but of course that is because these are natural laws for databases.

I remember when 4GLs were the hot new thing. Let's get into those! Oops, nope, forget that, now it's Data Dictionaries, let's get into those instead! Rinse & repeat ...

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#124

Post by vram22 » Mon, Jun 25, 2018, 5:12 PM UTC

A_Person wrote: post #93

Gosh, I must say there seems to be some misunderstanding of RDBMS concepts in some posts in this thread! I was writing database systems professionally, back in the days before the RDBMS concept was even a thing. So here's my (enormously long and convoluted) 2 cents worth. Please be sure to pack a sandwich and get hydrated before you continue. Say you were dealing with doctors and patients, and needed to store that in…

I'm guessing A_Person is making some up of what he/she said (to be entertaining) (I don't mean the DB facts - which are right, of course, and the questions at the end), but it was an amusing post anyway :)

Well done.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#125

Post by mmt » Mon, Jun 25, 2018, 6:11 PM UTC

DreamSpinner wrote: post #84

mmt wrote: post #63

Earlier quoted context omitted.

> Once you get above about 256 GB of RAM I think it might be as high as 1TB these days, though with what's going on with DDR4 prices, the situation is strange at the moment. Of course, I don't disput your point that a 100+TB database isn't all that large, especially with indexes. I suspect that it's this false dichotomy of "fit in RAM" and "big data" has resulted in many needless forays into distributed computing.

I like to break up data problems like this: * Trivially Small * Fits In RAM * Fits on one server (CPU / Storage) * Fits on one Big Hardware (Mainframe or other Specialized equipment) * Requires Distributed Storage And/Or Processing

I think it's important to know that other boundaries exist, at least conceptually, but, ultimately, it's the practical considerations that are important. For example, for what you listed, what would you say are the storage cutoffs for each tier today?

> data problems

I find there is also, occasionally, a lack of awareness of when a problem is data-heavy, encouraged by abstraction layers like ORMs.

This can lead to casual or naive (neither meant derogatorily) distributed computing, where the app hoovers up the data from database to be processed. This can be great for anything CPU-intensive but terrible for the I/O-intensive.

> Fits on one Big Hardware (Mainframe or other Specialized equipment)

I don't think this really exists today, unless you're including an otherwise commodity high-end (e.g. 8-socket) server that carries up to a 4x price premium in "specialized equipment".

I'm aware that mainframes still exist, but, for a variety of reasons [1], I'd consider them as being in a world of their own, rather than a step on this continuum.

[1] e.g. inherently distributed architecture, not obvious if storage scale actually greater than high-end commodity, interop issues

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#126

Post by mmt » Mon, Jun 25, 2018, 6:29 PM UTC

PuercoPop wrote: post #72

greenyoda wrote: post #17

Earlier quoted context omitted.

> I really wonder who, today, have data that cannot fit in RAM, apart from big actors. Keeping all your data in RAM has significant problems, even if it all fits. For example, would you want to lose all your customers' orders and billing information if your code crashed? In addition to the relational database model, SQL databases offer ACID transactions, which are useful if you want to have consistent and reliable da…

> For example, would you want to lose all your customers' orders and billing information if your code crashed? There are things like WAL and snapshots. Having your dataset in RAM and querying directly doesn't exclude persisting it to disk. Read Stonebraker's "The End of an Architectural Era"[0]. Basically the OP is right in that SQL DBs were designed assuming that RAM was scarce and that asumption is no longer valid.…

> Read Stonebraker's "The End of an Architectural Era"

I tried, but it lost me in section 2.3:

>It seems plausible that the next decade will bring domination by shared-nothing computer systems, often called grid computing or blade computing.

No, it doesn't seem plausible at all. This has been, by some accounts, the future of computing, since at least the 80s.

https://en.wikipedia.org/wiki/Transputer

But shared-nothing is just too darned hard to program for.

Also, main memory is still scarce. We're just barely up to the 1TB of just some of the (small, by today's standards) databases the paper mentions. Ironically, it seemed to emphasis traditional business database needs over what might happen with the tech industry itself, which has turned out to be the main driving force behind database usage (and data creation).

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#127

Post by mmt » Mon, Jun 25, 2018, 7:02 PM UTC

zzzcpan wrote: post #95

gaius wrote: post #77

Earlier quoted context omitted.

SQL just wasn't designed for performance. That is strictly true in the literal sense that SQL is just a textual representation of relational algebra and calculus, and noone says a mathematical notation is "designed for performance" or otherwise. But in a more practical, useful sense, it's the language most designed for performance, since the query planner has so much leeway to perform optimisation. It can do more dra…

If you cannot predict performance of a query you cannot write a query with guaranteed performance characteristics and cannot guarantee performance of your application. So, no, SQL and RDBMSs in general are neither designed for performance nor are any good at it. Which is one of the reasons we have the whole world outside of them.

> Which is one of the reasons we have the whole world outside of them.

The other reason seems to be a failure to learn (or a belief in "this time it's different") the lessons from the 70s and 80s that informed many of the fundamental design decisions of RDBMSes.

The "NoSQL" world has had a remarkable number of incidents with ACID failures. Unsurprisingly, a fix involves sacrificing performance.

This isn't to say that the trade-off is never worth it. In fact, RDBMSes can and do offer such trade-offs as options. It's just not the default.

It may be accurate that RDBMSes are designed and "shipped" with default configurations that are ACID-first [1] (to coin a term), whereas the "world outside" is performance-first [2].

However, it's nowhere near accurate, and maybe even disingenuous, to suggest that SQL or the relational model somehow prevents high performance. The reality of the actual tools contradicts your claim, as the sibling comment pointed out.

[1] with the exception of early MySQL which defaulted to MyISAM as a storage engine

[2] as the joke goes, so is writing to /dev/null and reading from /dev/zero

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#128

Post by philjohn » Mon, Jun 25, 2018, 7:19 PM UTC

kimdotcom wrote: post #117

slow_donkey wrote: post #22

Earlier quoted context omitted.

To be fair, using redis or elasticsearch as a main datastore is doable. Although I'm not sure they're much better choices in terms of understanding how they work. You could summon Antirez I guess

Doesn't redis by default have recovery via the filesystem enabled?

It does - BUT depending on how often you have it syncing changes to disk you can lose data.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#129

Post by wilburlikesmith » Mon, Jun 25, 2018, 11:31 PM UTC

ryanworl wrote: post #60

dang wrote: post #54

Earlier quoted context omitted.

Does it cover the same material though? I don't want to deprive people of the original survey.

I don’t think it is worth changing the link. They are not the exact same material and anyone interested in SQLite should just watch both.

And wow, there I thought programmers know it all... Best practices, instances and implementations... Suppose we must get a Google / Tesla AI bot to create and write us an effecient and suitable database (plus structure and auto AI joins :p) haha, what do I know...

Hey: @ryanworl wasn't aimed at your comment, you're right, they should watch and learn it all..

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#130

Post by barrkel » Mon, Jun 25, 2018, 11:42 PM UTC

blattimwind wrote: post #104

barrkel wrote: post #100

Earlier quoted context omitted.

Given a database and a query, yes, you can understand the execution profile. What you can't do is start out with a schema and a query and understand the execution profile. Query planners take statistics into account when determining which indexes to use or to fall back to scanning, and can normally only examine a fraction of possible join orders. So you can usually only fully predict the performance profile of simple…

I might add, those processes have little to do with SQL. When you have a lot of data, and a lot of queries, then you're going to have to monitor and optimize your databases. I might also add that a basic understanding of data modelling and what an index can and can't do is sufficient to avoid many, many performance pitfalls (this again has mostly nothing to do with SQL per se). Any undergrad course on databases teach…

The variance provided by an enormous pile of state and an algorithm which uses that state means it's a bit more unpredictable than most other systems, where you're used to seeing lines, curves and almost always monotonic series.