Tuning Your DBMS Automatically with Machine Learning | Hacker News Reader

Tuning Your DBMS Automatically with Machine Learning

aws.amazon.com

127 pointsblopeur24 comments

If you are on MySQL RDS, the first thing you need to do is change the INNODB_LOG_FILE_SIZE to something reasonable, like 2 gigs. I have preemptively stopped at least 5 vertical scaling requests with this one change.

The default of 128mb is plain stupid. I get why Amazon chose it, because it directly eats 2x the value of your backing store - something that can be hard to explain to customers with 4gb disks attached and not really running any appreciable load through it.

But when you have 100+ gig disks allocated on a 2xlarge instance, the small value makes no sense whatsoever.

At my previous employer, a small (back then, 10x larger today) outourcing company, I saved a customer's project by increasing innodb_buffer_pool_size from 8MB to 2GB. As a context, it was back in 2010 when MySQL had MyISAM as the default storage engine and I think 99% of the tutorials on tuning MySQL's performance were focused on MyISAM, while the customer had most of its tables as innodb. As a bonus, the value for innodb_buffer_pool_size was expressed in bytes, showing a large, 7 digits, number. Before my arrival there all the senior devs tried to improve the performance of that db. The customer bought a server with 8GB of RAM and dedicated it solely to the db, while the db was about 4GB in size. It was useless. When traffic to the web app increased, the db daemon started paginating and the server load increased to a point of 0 responsiveness. There were simple queries on indexed fields in tables with less than 1k records which took 8 seconds to execute.

It took me about 2 weeks of internet scavenging to find the culprit, which was just the innodb_buffer_pool_size size. That was my first commercial project I worked on for the employer and it got me the reputation of THE db performance tunning expert in the company. I was with them for 5 years and that was a quick&easy way to position myself as a valuable team member.

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 because my single SSD on a rather shag Lenovo PC (a cast off from another customer!) had better i/o and latency than whatever they were being given by said cloudy provider.

I suggest you start with the basics: CPU, RAM, disc I/O and latency, network I/O and latency. Optimise those first and then work up the stack (and down, then back up etc.)

If you start with "assume a spherical EC instance" you may not be considering the whole problem -> solution -> realisation thing.

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

You should have stayed on: They get 80% lower 99%-quantile latency, and I don't see anything that would make their process dependent on bad hardware. Ignoring a chance for such improvements by just twiddling a few config parameters doesn't sound like a winning strategy.

(Not that I agree with your diatribe against cloud providers. They all have options with excellent performance, such as locally-connected SSDs)

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.

It would be interesting to see this work expanded to include some of those factors; Namely indexing and archiving. Bad data models seem like a whole other beast, but indexing and archiving should be possible to generate good configuration suggestions for if you had a full history of DB usage trends along with what you were hoping to optimize for.

I assume you are referring to this project [1].

1. https://github.com/major/MySQLTuner-perl

Yep. I use this regularly. I really like it.

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.

Any techniques, recommendations, and/or open source tools to simplify the archiving step you mentioned?

That's one of those things where money can go a long way quickly - fat i/o (including network if you're off-loading, sharding, peering, vdbs etc.), good cpu, efficient log sizes. Sticking to the basics is probably the best - if you're running high transaction and data change rates then the collective throughput "off" of your transaction server(s) is every bit as important as the throughput "on" the server(s).

They use both that script, as well as a performance expert for comparison. Their method is about as good as the DBA, and both beat the simple script by a large margin.

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 consultant to look at it. I charged them $2k for an afternoon of hacking onsite and 2 days of followup, and they were ecstatic to pay it, saying that not doing that for 2 more weeks would have been more expensive. They're now paying the minimum charge for that and 5 other databases.

Most of the cloudy database offerings are ridiculously expensive for small, infrequently accessed databases (0.1 to 0.01 qps, let's be honest there's a boatload of these in every company), compared to either hosting it yourself or getting a small shared hosting or dedicated provider to do it.

I often wonder if a lot of the cloud scaling isn't being pushed by these companies for the same reason. Using a distributed publish-subscribe queue will seriously slow down anything that doesn't require, say ~5-10 machines, to execute.

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 :-)

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.

Reminds me of this: https://blog.acolyer.org/2017/01/17/self-driving-database-ma...

Yep! Peloton is by the same people.

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

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

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

So cool! Its not hard to image this same technique used on clouds to tweek infastructure for workloads.

Similar concepts can be applied to optimize applications performance e.g. .net web apps or any similar stack. I got something to research during coming week

I just see that Postgres have better performance than MySQL with default configuration.

Reply on news.ycombinator.com