Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How I slashed a SQL query's runtime with two Unix commands

spinellis.gr

Post reply on HN
Print view View original

71–80 of 91 posts

Re: How I slashed a SQL query's runtime with two Unix commands

#71

Post by dekhn »

miahi wrote: post #61
dekhn wrote: post #59

I once had a huge performance problem for a simple self-join that I expected would run quickly. 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.

I guess you were missing an index, as data order should only matter when doing full scans.

this was a full self-join (SELECT * FROM blah a, JOIN blah b WHERE a.id = b.id). I ran strace on it, for each a item, it was doing a seek to each b row and reading a whole page. By pre-sorting, I increased locality.

Re: How I slashed a SQL query's runtime with two Unix commands

#72

Post by ben509 »

If you find you're exporting and running via `uniq` or the like, `create temporary table as` is probably what you want. If you think about it, it's essentially what you're doing, without the additional filesystem involvement.

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.

Re: How I slashed a SQL query's runtime with two Unix commands

#73

Post by hermitdev »

nextweek2 wrote: post #18

I'd also like to see a breakdown of the time taken to complete the project. 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.

If you need to add hardware to solve a software optimization problem such as this, you've failed your job as a software engineer (if you should even be called an engineer, neigh, a developer).

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.

Re: How I slashed a SQL query's runtime with two Unix commands

#74

Post by linsomniac »

I've run into situations like this where I was able to use a window function or a materialized view, to get huge speed gains. It basically was restructuring the tables into a new view, much like the shell script did in this case, and that view then could be optimized by the query planner much more effectively.

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.

Re: How I slashed a SQL query's runtime with two Unix commands

#75

Post by mjevans »

sampleinajar wrote: post #63
CyanLite2 wrote: post #46

TLDR: author doesn’t know how to use database indexes properly.

Possibly. The author at least knew enough to observe "Both join fields are indexed. However, MariaDB implements the join with a full scan of project_commits and an index lookup on commits."

That may have actually been a result of the ordering and poor query design.

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

Re: How I slashed a SQL query's runtime with two Unix commands

#76

Post by camel_Snake »

blub wrote: post #69
languagehacker wrote: post #30

Am I missing something or is this blog post just someone discovering that ETL exists

As someone that doesn't know much about ETL, I don't really see why it's worth mentioning in this context. 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?

Note: I'm just a backend dev, but I've been looking into data engineering a bit recently so I'm not terribly experienced. Happy to be corrected

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.

Re: How I slashed a SQL query's runtime with two Unix commands

#77

Post by oliverped »

The query is simply very badly written. Who makes a left join of a 5b rows table to a smaller table and at the same time expects a distinct from the “left” table??? If the distinct is important to solve the problem without expecting the join to return “true” then why have a join at first place???

Re: How I slashed a SQL query's runtime with two Unix commands

#78

Post by ww520 »

Isn't the Unix join essentially doing an inner join? Why is the original SQL doing a left join while the text processing with Unix join is doing an inner join?

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.

Re: How I slashed a SQL query's runtime with two Unix commands

#79

Post by ldh »

hermitdev wrote: post #73
nextweek2 wrote: post #18

I'd also like to see a breakdown of the time taken to complete the project. 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.

If you need to add hardware to solve a software optimization problem such as this, you've failed your job as a software engineer (if you should even be called an engineer, neigh, a developer). 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.

The use case is pretty straightforward; TFA says that the referenced columns are both indexed and the runtime is a function of how MariaDB executes that simple query. Do you have anything concrete to offer in terms of optimizing "storage patterns" in this case that the OP has missed?

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".

Re: How I slashed a SQL query's runtime with two Unix commands

#80

Post by pastage »

thecopy wrote: post #22
nextweek2 wrote: post #18

I'd also like to see a breakdown of the time taken to complete the project. 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.

Yes, having tables with the size of 5B rows on a machine with 16GB RAM is just stupid, going to the disk will most probably always happen.

5B rows is not that much we blew past 32 bit uids in 2001, 16GB was really expensive then.
Post reply on HN