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.<p>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.<p>But when you have 100+ gig disks allocated on a 2xlarge instance, the small value makes no sense whatsoever.
On GitHub: "No description, website, or topics provided."<p>In the article: "and collects its Amazon EC2 instance type and current configuration"<p>... and I switched off.<p>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.<p>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.)<p>If you start with "assume a spherical EC instance" you may not be considering the whole problem -> solution -> realisation thing.
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.
Reminds me of this:
<a href="https://blog.acolyer.org/2017/01/17/self-driving-database-management-systems/" rel="nofollow">https://blog.acolyer.org/2017/01/17/self-driving-database-ma...</a>
Product Manager for the MySQL Server here.<p>The default configuration for MySQL is for a server with 512M RAM: <a href="https://dev.mysql.com/doc/refman/5.7/en/memory-use.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.7/en/memory-use.html</a><p>.. so some improvement should be expected. The paper shows what has been tuned in each configuration (pasting below).<p>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).<p>(a) OtterTune Configuration (MySQL)<p>=============================<p>innodb_buffer_pool_size 8.8 G<p>innodb_thread_sleep_delay 0<p>innodb_flush_method O_DIRECT<p>innodb_log_file_size 1.3 G<p>innodb_thread_concurrency 0 # this is the default<p>innodb_max_dirty_pages_pct_lwm 0 # this is the default<p>innodb_read_ahead_threshold 56 # this is the default<p>innodb_adaptive_max_sleep_delay 150000<p>innodb_buffer_pool_instances 8 # this is the default<p>thread_cache_size 9 # this is the default<p>(b) DBA Configuration (MySQL)<p>=========================<p>innodb_buffer_pool_dump_at_shutdown 1<p>innodb_buffer_pool_load_at_startup 1<p>innodb_buffer_pool_size 12 G<p>innodb_doublewrite 0<p>innodb_flush_log_at_trx_commit 0<p>innodb_flush_method O_DIRECT<p>innodb_log_file_size 1 G<p>skip_performance_schema<p>(c) Tuning Script Configuration (MySQL)<p>==============================<p>innodb_buffer_pool_instances 4<p>innodb_buffer_pool_size 4 G<p>query_cache_limit 2 G<p>query_cache_size 2 G<p>query_cache_type 1<p>(d) Amazon RDS Configuration (MySQL)<p>===============================<p>innodb_buffer_pool_size 10.9 G<p>innodb_flush_method O_DIRECT<p>innodb_log_file_size 128 M<p>key_buffer_size 16 M<p>max_binlog_size 128 M<p>read_buffer_size 256 k<p>read_rnd_buffer_size 512 M<p>table_open_cache_instances 16<p>thread_cache_size 20
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