21–30 of 91 posts
The question im interested in is why MySQL did not use the index for the lookup.
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.
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards. 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…
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.
If you initiate the shell commands from inside the database, does it count as less hacky? :) https://github.com/petere/plsh
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards. 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…
The rewrite was easy and performance went through the roof.
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards. 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…
On PostgreSQL I've also had really positive experience with the cstore_fdw from Citus. It's an extension you have to compile yourself but you end up with the ability to create compressed columnar tables. 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).
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards. 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…
The distinct on the date format is the problem, probably should use a separate query on each table to fill in the date output as a new column first, and then join afterwards. 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…
I highly recommend Clickhouse for this. It is blazing fast and can do over 100GB/s on a single modern machine if you have enough RAM. And it is very easy to install and configure.
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.
Earlier quoted context omitted.
I highly recommend Clickhouse for this. It is blazing fast and can do over 100GB/s on a single modern machine if you have enough RAM. And it is very easy to install and configure.
> And it is very easy to install and configure 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.