4 ms·

Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to d

by danjoc 9y ago

Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.

quizotic 9y ago
+1 for the idea of tuning query optimization based on ML. I don't know of any DBMSs that take this advice, and that's remarkable in this day and age.
- vthriller 9y ago
  Oracle already promised to bring something similar back in september: https://www.oracle.com/database/autonomous-database/index.html https://www.oracle.com/database/autonomous-database/index.ht...
- jve 9y ago
  SQL Server 2017 has introduced 2 Automatic database tuning features: Automatic plan correction and Automatic index management (Latter for Azure SQL Only) https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning https://docs.microsoft.com/en-us/sql/relational-databases/au...
- rev112 9y ago
  This looks quite relevant: https://aws.amazon.com/blogs/ai/tuning-your-dbms-automatically-with-machine-learning/ https://aws.amazon.com/blogs/ai/tuning-your-dbms-automatical... ("Tuning Your DBMS Automatically with Machine Learning") The authors propose a way to do some automatic tuning for MySQL and Postgres. Link to the paper itself: http://db.cs.cmu.edu/papers/2017/tuning-sigmod2017.pdf http://db.cs.cmu.edu/papers/2017/tuning-sigmod2017.pdf
- power 9y ago
  Postgres has a query optimizer that uses Genetic Algorithms: https://www.postgresql.org/docs/current/static/geqo.html https://www.postgresql.org/docs/current/static/geqo.html Clarification: this is for planning how to execute a query, not for tuning the db settings
  - agnivade 9y ago
    Wow, did not know that. TIL!
dmichulke 9y ago
Your suggestion sounds very much like that of a Silicon Valley start-up guy ("Machine Learning to the rescue!") but I believe here a deterministic detection of the underlying drive, possibly tied to a warning on start-up, would be easier to code, less bug-prone, and faster. OTOH, if you speak about the general problem of optimizing configuration, then I distinctly recall having read something about automatic server configuration, IMO on AWS, but quite possibly only as a feature request.
- matt4077 9y ago
  There was an article and Software posted here about automatic tuning of pg with machine learning. Sorry for not searching—I’m on the road. IIRC it was an academic paper and the process was somewhat byzantine when I tried to recreate it. But the results looked good.
- viraptor 9y ago
  I suspect it was the paper about Ottertune: http://ottertune.cs.cmu.edu http://ottertune.cs.cmu.edu (that was innodb, not pg though)
davidcuddeback 9y ago
PostgreSQL does have built-in support for a genetic algorithm based query optimizer. I haven't tried it yet, so can't comment on how well it works. Docs are here: https://www.postgresql.org/docs/9.6/static/geqo.html https://www.postgresql.org/docs/9.6/static/geqo.html.
- pgaddict 9y ago
  The idea is that up until some number of relations (8 by default, IIRC) the join tree is searched exhaustively, then it switches to the genetic algorithm. So it's kinda automatic.

by danjoc 9y ago

Thanks for the tip. This probably affects more people than there are people who realize it. It would be really interesting if PG would use machine learning to discover this sort of tuning on its own.

quizotic 9y ago

+1 for the idea of tuning query optimization based on ML. I don't know of any DBMSs that take this advice, and that's remarkable in this day and age.

vthriller 9y ago
Oracle already promised to bring something similar back in september: https://www.oracle.com/database/autonomous-database/index.html https://www.oracle.com/database/autonomous-database/index.ht...
jve 9y ago
SQL Server 2017 has introduced 2 Automatic database tuning features: Automatic plan correction and Automatic index management (Latter for Azure SQL Only) https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning https://docs.microsoft.com/en-us/sql/relational-databases/au...
rev112 9y ago
This looks quite relevant: https://aws.amazon.com/blogs/ai/tuning-your-dbms-automatically-with-machine-learning/ https://aws.amazon.com/blogs/ai/tuning-your-dbms-automatical... ("Tuning Your DBMS Automatically with Machine Learning") The authors propose a way to do some automatic tuning for MySQL and Postgres. Link to the paper itself: http://db.cs.cmu.edu/papers/2017/tuning-sigmod2017.pdf http://db.cs.cmu.edu/papers/2017/tuning-sigmod2017.pdf
power 9y ago
Postgres has a query optimizer that uses Genetic Algorithms: https://www.postgresql.org/docs/current/static/geqo.html https://www.postgresql.org/docs/current/static/geqo.html Clarification: this is for planning how to execute a query, not for tuning the db settings
- agnivade 9y ago
  Wow, did not know that. TIL!

dmichulke 9y ago

Your suggestion sounds very much like that of a Silicon Valley start-up guy ("Machine Learning to the rescue!") but I believe here a deterministic detection of the underlying drive, possibly tied to a warning on start-up, would be easier to code, less bug-prone, and faster. OTOH, if you speak about the general problem of optimizing configuration, then I distinctly recall having read something about automatic server configuration, IMO on AWS, but quite possibly only as a feature request.

matt4077 9y ago
There was an article and Software posted here about automatic tuning of pg with machine learning. Sorry for not searching—I’m on the road. IIRC it was an academic paper and the process was somewhat byzantine when I tried to recreate it. But the results looked good.
viraptor 9y ago
I suspect it was the paper about Ottertune: http://ottertune.cs.cmu.edu http://ottertune.cs.cmu.edu (that was innodb, not pg though)

davidcuddeback 9y ago

PostgreSQL does have built-in support for a genetic algorithm based query optimizer. I haven't tried it yet, so can't comment on how well it works. Docs are here: https://www.postgresql.org/docs/9.6/static/geqo.html https://www.postgresql.org/docs/9.6/static/geqo.html.

pgaddict 9y ago
The idea is that up until some number of relations (8 by default, IIRC) the join tree is searched exhaustively, then it switches to the genetic algorithm. So it's kinda automatic.