Tuning Your DBMS Automatically with Machine Learning • Hacker News Board

Re: Tuning Your DBMS Automatically with Machine Learning

#21

Post by gaius » Sun, Jun 04, 2017, 9:54 AM UTC

candiodari wrote: post #20

gaius wrote: post #19

Earlier quoted context omitted.

The most common thing I come across is devs saying the DB is slow but the load on the DB server is 0.1... Always profile before you tune!

I think AWS, and all other cloudy provider's attitude is more along the lines of : "Now you can trade not having a DBA, and letting your programmers not get criticized by him for paying us $$$($$$$$$$$$$$$$$). DBA's bad ! Bad ! BAD I tell you". I fixed a customer's database a few months back. It was a cloud sql instance. They paid cloudy provider $40k for hosting a ~130 MB database (in ~ 8 months) before getting a co…

I am generally a fan of cloud, but it's important to understand what it replaces:

- Purchasing the kit and waiting for delivery. Probably a few weeks BUT that's a few weeks of no extra work on your part, just waiting. You still need to fill in the PO, get it signed off by your CTO, whatever

- Racking and powering. Except on the biggest jobs, 1-2 days for a DC team of physical labour.

- Installing an OS and software. These days, a few hours, PXE boot then let CFEngine or whatever do it

For everything else, you still need someone to either do it or take responsibility for it being done and verified and documented and reviewed to see if it's still the right solution and so on and so on. As I always say "if you don't have a DBA then YOU are the DBA".

Incidentally "you don't need a DBA anymore" was also the pitch of MongoDB et al... Careful devs, one day it will be your turn :-)

Re: Tuning Your DBMS Automatically with Machine Learning

#22

Post by ris » Sun, Jun 04, 2017, 11:45 AM UTC

candiodari wrote: post #20

gaius wrote: post #19

Earlier quoted context omitted.

The most common thing I come across is devs saying the DB is slow but the load on the DB server is 0.1... Always profile before you tune!

I think AWS, and all other cloudy provider's attitude is more along the lines of : "Now you can trade not having a DBA, and letting your programmers not get criticized by him for paying us $$$($$$$$$$$$$$$$$). DBA's bad ! Bad ! BAD I tell you". I fixed a customer's database a few months back. It was a cloud sql instance. They paid cloudy provider $40k for hosting a ~130 MB database (in ~ 8 months) before getting a co…

This is one of the reasons I get quite nervous when people start talking about these private-implementation, "cloud native" database services (Aurora, Redshift, Spanner...). As wonderful as they sound (and possibly are), not only are you binding yourself to a single vendor by adopting these, but there is nothing to keep the pricing of these services connected to reality.

Whereas as long as you e.g. "keep it postgres", you're able to experiment (at fairly low effort) with different database setups and find what is more efficient or cost effective for you. If Cloud Provider X's pricing model starts to deviate from what people are able to achieve with some custom database setup, people will start flocking to that - and, in fact, Cloud provider X would probably find themselves competing with an easy to use "managed database" service that does just that on their own Cloud Marketplace.

Not that this isn't an extremely interesting project as long as it's possible to wrestle the concept free from Amazon's infrastructure.

Re: Tuning Your DBMS Automatically with Machine Learning

#23

Post by throwanem » Sun, Jun 04, 2017, 12:28 PM UTC

hashhar wrote: post #15

gerdesj wrote: post #5

On GitHub: "No description, website, or topics provided." In the article: "and collects its Amazon EC2 instance type and current configuration" ... and I switched off. I recently diagnosed a MySQL latency snag on a well known cloudy platform for a customer. I run rather a lot of comparative bonnie++, MySQL bench and Lord knows what else. I was able to convince the customer that my office PC ran MariaDB better simply…

But then there's the issue of cost. Tuning performance on the software side only costs time and expertise but not money. And management doesn't really want to spend money unless you tell them they are going under within a week.

Sounds like GP is a contractor. In that world, time and expertise is money - contracting rates aren't perceived as a sunk cost the way salaries are - and an SSD won't cost more than a few hours' billable time would. (Less than one hour, for high-end firms.)

Re: Tuning Your DBMS Automatically with Machine Learning

#24

Post by tyingq » Sun, Jun 04, 2017, 1:30 PM UTC

danieltillett wrote: post #4

tkyjonathan wrote: post #2

Technically there is a perl tool to make recommendations for mysql settings - although not with AI. As a DBA, I would probably use this, but 80% of the performance improvements come from indexing, fixing bad data models and archiving - especially with RDS where the options for performance optimization are limited.

I assume you are referring to this project [1]. 1. https://github.com/major/MySQLTuner-perl

I love that project, but it seems like it could use a refresh. For example, it currently still complains if the query cache is turned off...but Mysql does that on purpose, and will remove it entirely soon. Last time I used it, it also seemed like it was still straddling suggestions between MyISAM and InnoDB. It seems safe enough at this point to have the tool strongly suggest converting MyISAM tables and focusing solely on newer storage backends.

Re: Tuning Your DBMS Automatically with Machine Learning

#25

Post by morgo » Sun, Jun 04, 2017, 1:50 PM UTC

Product Manager for the MySQL Server here.

The default configuration for MySQL is for a server with 512M RAM: https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

.. so some improvement should be expected. The paper shows what has been tuned in each configuration (pasting below).

It would be diligent to point out that the DBA configuration actually changes the semantics so you may lose data (disabling doublewrite, flush-log-at-trx-commit=0).

(a) OtterTune Configuration (MySQL)

=============================

innodb_buffer_pool_size 8.8 G

innodb_thread_sleep_delay 0

innodb_flush_method O_DIRECT

innodb_log_file_size 1.3 G

innodb_thread_concurrency 0 # this is the default

innodb_max_dirty_pages_pct_lwm 0 # this is the default

innodb_read_ahead_threshold 56 # this is the default

innodb_adaptive_max_sleep_delay 150000

innodb_buffer_pool_instances 8 # this is the default

thread_cache_size 9 # this is the default

(b) DBA Configuration (MySQL)

=========================

innodb_buffer_pool_dump_at_shutdown 1

innodb_buffer_pool_load_at_startup 1

innodb_buffer_pool_size 12 G

innodb_doublewrite 0

innodb_flush_log_at_trx_commit 0

innodb_flush_method O_DIRECT

innodb_log_file_size 1 G

skip_performance_schema

(c) Tuning Script Configuration (MySQL)

==============================

innodb_buffer_pool_instances 4

innodb_buffer_pool_size 4 G

query_cache_limit 2 G

query_cache_size 2 G

query_cache_type 1

(d) Amazon RDS Configuration (MySQL)

===============================

innodb_buffer_pool_size 10.9 G

innodb_flush_method O_DIRECT

innodb_log_file_size 128 M

key_buffer_size 16 M

max_binlog_size 128 M

read_buffer_size 256 k

read_rnd_buffer_size 512 M

table_open_cache_instances 16

thread_cache_size 20