[falcolas]

> What we really need is to demystify DB performance, which for the most part is fairly simple.

Without getting into the rest of your argument, I'd like to quickly address this.

No, it really isn't.

In your typical MySQL database, your performance for a simple "select * from x where y" is going to go through a lot of complicated machinery (most of which can be tuned for performance), a few points of which I will enumerate below.

    1) Acquire a query cache lock & see if this query is there
    2) Run the query through the optimizer
      2a) Perform multiple shallow dives into a table to look at the cardinality of the filtered columns
      2b) Identify the best indexes based on the shallow dives
      2c) Create a query plan
    3) Push the query plan down into InnoDB
    4) Load the index into memory, if its not already there
    5) Load the potential rows into memory, if they are not already there
      5a) If there's not enough memory, load a few into memory, and be ready to push those rows out of memory in favor of more rows when needed
      5b) Load rows that are still in the insert tree but not yet part of the regular buffer pool or pages on disk
    6) Loop through the candidate rows for matches to the filter
    7) Return the data to MySQL
    8) Acquire the query cache lock & update it
    9) Return the data to the client

Any and all of these can (and often should) be tuned. There are 600+ page books and very old (and oft updated) blogs dedicated to this topic... it's not something you can teach a developer in a couple of days.

As an example, I attended an introductory course to being a MySQL DBA; it lasted 5 days of 8-5 teaching & running examples. And it only scratched the surface of what I do on a daily basis.

[mattmanser]

This sort of nonsense is exactly the sort of mysticism I'm talking about.

My, for example, do you think every query needs to be tweaked for this one:

Push the query plan down into InnoDB

So why did you include it on the list?

5a + 5b, Is MySQL so bad at memory management that you tweak it for every query?

So why did you include it.

Virtually everything on that list are implementation details that the vast majority of applications and developers don't need to even think about.

[falcolas]

> Virtually everything on that list are implementation details that the vast majority of applications and developers don't need to even think about.

Particularly if you hire a DBA.

But that's not what you meant... and you're right. If your business doesn't generate enough query traffic and data to stress your database, then no, you don't need more than the indexing and smart query development which your average developer can learn.

The moment your business hits that wall, however, you'll be scrambling to get someone who can dig into those "implementation details" and wring every ounce of performance out of your database.

> [...] do you think every query needs to be tweaked for this one: Push the query plan down into InnoDB

Nope. However, for that one table that is better served by using Tokudb, or MyISAM, or the Archive engine it does matter (yes, there are actually use cases for using MyISAM tables instead of InnoDB).

> 5a + 5b, Is MySQL so bad at memory management that you tweak it for every query?

Again, for every query? No. Also, 5a and 5b have nothing to do with memory management, and everything to do with the size of the dataset you have to pull into memory to identify and return the results. Knowing how this affects the buffer pool LRUs, disk usage patterns, and how to optimize the interaction between the two can be vital.

[mattmanser]

Look, you're doing the equivalent of looking at a car and saying that every single one needs to be hand tuned.

Just because you're a mechanic.

I said there is a bar when you will need a DBA. It is much, much, much higher than you're making out.

And the things you're talking about, most of the people mucking around with them probably shouldn't be. They've probably made it worse. Any the tiny few who actually need to? They're a tiny few.