How I slashed a SQL query's runtime with two Unix commands
spinellis.gr
spinellis.gr
Also wrong tool for the job comes to mind. MariaDB/MySQL are just not great at joins and query planning, and at this scale, especially with all the work involved to export and use unix tools, why not use any of the columnar data warehouses that can handle this much faster?
Export it to bigquery with CSVs and the query will probably finish in 30 seconds.
Trimmed some heavy queries that I was working with from about 4 hours to about 3 minutes. Also compressed the data from 220gb to about 20gb.
Unfortunately that extension has a bunch of limitations and issues that keep it from being production-ready. PostgreSQL could really use a proper columnstore table implementation, and there's a pluggable storage API on the roadmap but it hasn't gotten much traction yet (and is focused on an in-memory engine first).
http://tech.marksblogg.com/benchmarks.html
More interesting to me is the reverse: using FDW from the analytical DB to Postgres, e.g., https://aws.amazon.com/blogs/big-data/join-amazon-redshift-a...
One of the most interesting things is you can shard postgres extremely easily now by using FDWs which can do native pushdown of optimizations.
Also, Citus itself has been improving in that time, and I'm curious to see how that performs.
For the specific dataset I needed to take advantage of some of the additional datatypes that PG has available, which is how I found it.
The rewrite was easy and performance went through the roof.
On a single node yea, unfortunately not beyond that. Really wish there was bigger focus on operational features, and a bigger community for a rather fantastic product.
CH is an amazing piece of tech, but getting configuration just right on many nodes is quite complex, and all of the experts writing about how to do so are writing in Russian.
The documentation seems to be improving rapidly though.
Fast as hell and surprisingly performance in low memory conditions.
In this case it would be much faster to have an intermediate table or column with that statistic that could then be properly indexed.
https://www.definitions.net/definition/sargable
https://stackoverflow.com/questions/799584/what-makes-a-sql-...
The post references a StackExchange question [1] which has a simple analysis, and four helpful suggestions (similar to yours), as to which the author states:
"I tried the first suggestion, but the results weren't promising. As experimenting with each suggestion could easily take at least half a day, I proceeded with a way I knew would work efficiently and reliably"
A consequence of many skipping on traditional learning processes and how developers are now also forced into castings and portfolios.
In ye olden times, there would be an grumpy DBA in this story terminating the query and yelling at the user. Many, many people are consuming databases without understanding how to approach SQL optimization, because nobody is forcing them to!
Although the parent posed a caricature with "yelling", this is an even more extreme one.
The "forcing" function merely requires a bit of "hardass" but not the willful ignorance you associate with it. At the risk of going true-scotsman, I believe that competent DBAs capable of the forcing function would already understand the need without trying.
As such, I don't think there needs to be any kind of "medium" between two extremes of ignorance, but, rather, back to the GP's point, a reduction of ignorance in "what we have now".
Of course, I don't believe hardass/yelling is the best way to achieve that, either, instead favoring (original) Devops culture, but that also doesn't work if Ops (including DBA) skills aren't recognized as valuable any more (and "Devops" just means "replace Ops with Devs").
I learned a lot about query optimization in my 3 years as an intern. I also caused a few issues along the way, including a number of PR reports to IBM. Some of which were benign, like incorrect documentation. Others were more problematic, such as semantically identical queries returning different results (e.g. coalesce vs case when null in a column spec) to knocking out the entire development LPAR on the mainframe with a simple select statement (hit a bug in the query optimizer and it crashed not just the DB, but the whole LPAR).
Still, those 3 years were invaluable to me. They developed both an intuition on how to write optimal queries, but also the knowledge of how to utilize the tools to identify problems, measure and fix them. It has served me well as a developer in the 15 years since I left my internship. I don't get into fights with DBAs since they recognize I know as much or more than them about optimizing queries on datasets I work with.
And at the risk of sounding like a clown, I'll go ahead and ask: ETL seems to be such a basic and generic thing to the point that it's useless as a concept. What did those Phds achieve in those thousand hours that the author missed?
Yes, what the OP is doing can be called extract/transform/load, but that's an extremely general set of operations. Do you have some specific tool in mind that would make solving their problem a breeze? Is there a specific process that one should follow for ETL which was ignored?
Problem here is they are using a system designed for the typical webapp use-case (OLTP). It's optimized to find a particular row or rows and then maybe update them with low latency.
ETL tools are more optimized towards this sort of bulk data processing (OLAP) where we want to scan all the rows for a particular column and run some sort of analysis.
A simple optimization some databases make for this is to orient the data so that each whole column is stored together rather than storing each row together. This makes it much quicker to run queries over an entire column or two in a table.
Generally modern ETL is optimized towards working with LARGE amounts of data so there are many tools that are optimized for this.
One was with an internal database we used for tracking employee time and generating invoices and reports. I forget the exact details but this technique took it down from an hour to a second, or similar.
Another time was more recently in an interview, using the mysql employee sample database. I had a naive query that did most of what was needed, but after quite a bit more work I was able to make a materialized view that caused the query to go from using 64+GB of RAM and dying from OOM after 4 hours, to running in 45 minutes in 2GB of RAM.
It usually takes me hours and hours to put together though. Because I do it so infrequently.
Yes, the left join will scan the whole table. Left join means to return all the rows from the left hand side table. It won't use the index.
Inner join with index is way faster than left join.
The optimizer should be handling this for you, though for very large datasets, constructing a new table from a query essentially avoids the locking issues you might otherwise run into.
RAM is cheap and jumping from 16GB to 64GB (or even 128GB) might have cost as much as the analysis time.
The only clue to that a memory upgrade might have been a quicker fix was that the merged file was 133G. Seems to me that an upgrade to 128GB of RAM might have led to a dramatically shorter query execution time.
I'll probably get downvoted for this, but you should should examine opportunities to optimize your SQL/other storage patterns before you assume anything else if your indicators are your DB is slow.
I'm all for optimizing for minimal resource usage, but if plugging $200 worth of RAM into the machine allowed the original query to run in an acceptable timeframe, that may well easily offset the hours of analysis and transformation that resulted in the shorter runtime. Unless, of course, we're more concerned with engaging in a performative defense of our arbitrarily self-bestowed credentials as "engineers".
That's an interestingly narrow viewpoint with which I, narrowly, agree.
I suspect that the narrow viewpoint is just a form of the aphorism "if all you have is a hammer, everything looks like a nail."
> if you should even be called an engineer (if you should even be called an engineer, neigh, a developer)
However, in the broader context of engineering as problem solving (or developing a product), I disagree.
Not all (computer) problems are best solved with software.
As a sysadmin (who can code but doesn't love to), I routinely struggle with managers who don't have an intuitive sense of even a first approximation of what modern hardware is capable of, because their entire background is programming.
The fact that (latency) "numbers every programmer should know" exists (and has for quite some time) is fairly telling. What's more telling is that there are no dollar values attached to any of them.
But you've succeeded in your job in being a practical person trying to get something done.
I would love to know where you work, where "let me spend some time on optimizing this query" could be justified over "lets put a few more sticks of ram". Everywhere that I've worked, developer time is much more valuable and much more costly than a few ram sticks (assuming it's just a server or two, and not N instances).
https://www.reddit.com/r/programming/comments/94r6w6/how_i_s...
I'd write this query:
select pc.project_id, date_format(c.created_at, '%x%v1'), count(*)
from commits c
join project_commits pc
on c.id = pc.commit_id
group by pc.project_id, date_format(c.created_at, '%x%v1')
where tableB.someCol != null
or something. Otherwise, the join is useless (or he should use an inner join)
If you're testing, take a sample of the database and test on that.
First try the simple form:
explain
select
project_commits.project_id,
date_format(commits.created_at, '%x%v1') as week_commit
from commits, project_commits
where project_commits.commit_id = commits.id;
I talked to our resident SQL expert (retired from IBM, now doing a job as the lab administrative assistant). She suggested sorting the data before inserting it.
That sped up the query.
it still bother me that data order matters so much. But, I can't reproduce the problem in MySQL any more anyway.
Depending on when you originally did the query, maybe you were using 5.5 or below, which would explain why you now can’t reprodce it?
I have something like in the near past, but with much heavy calculations. Denormalizing using triggers turn query that take minutes in less 1 second.
Ensure project_commits.project_id is in an index before running the test
With 5bn+ rows and a memory constraint, the type of index begins to make a difference - e.g. in Postgres I would have tried using a bloom index.
I think that is configurable however.
In contrast, if you have sorted/indexed both tables on the join key, then the database is able to do a merge join. This is effectively what the command line implementation did, and is much faster, assuming you can get hold of sorted data quickly to begin with. If the tables are unsorted, then the extra cost of sorting them first needs to be added.
Most databases will evaluate several methods of running the query. Something like Postgres EXPLAIN will provide details of the method being used.
Nested loops aren't that different, performance-wise, than sorted merge joins. Sorting takes O(n log n); whereas the nested loop does n lookups, each taking O(log n), for a similar O(n log n). Memory allowing, a hash join has more potential for speedup.
There should be a locality win from a sorted merge - depending on the correlation between scan order and foreign key order, the index lookups in the nested loop may be all over the place. Usually this doesn't matter much because you don't normally do 5+ billion row joins.
Given that commits.id is a primary key, IIRC, it must also be NOT NULL; thus the LEFT JOIN is uselessly equivalent to an inner join; which most database engines seem to prefer expressed as a WHERE clause (since it's more trivial to re-order those operations).
Were it an 'inner join' equivalent where clause or the smaller table specified first, at least the full-table scan should have been on the smaller table.
Your database storage engine of choice might differ or have other options (E.G. in PostgreSQL an index on the result of comparing the two source keys COULD be created and a carefully structured query written to use /that/... I think, I haven't tested it but it'd at least be worth the experiment.)
MySQL (mariadb presumably?) can't. https://stackoverflow.com/questions/8509026/is-cross-table-i...