I just see that Postgres have better performance than MySQL with default configuration.
Tuning Your DBMS Automatically with Machine Learning
11–20 of 25 posts
Re: Tuning Your DBMS Automatically with Machine Learning
#12If 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.
Re: Tuning Your DBMS Automatically with Machine Learning
#13Similar 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
Re: Tuning Your DBMS Automatically with Machine Learning
#14If 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 an…
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.
Re: Tuning Your DBMS Automatically with Machine Learning
#15On 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.
Re: Tuning Your DBMS Automatically with Machine Learning
#16On 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…
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)
Re: Tuning Your DBMS Automatically with Machine Learning
#17Technically 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.
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.
Re: Tuning Your DBMS Automatically with Machine Learning
#18Technically 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
Yep. I use this regularly. I really like it.
Re: Tuning Your DBMS Automatically with Machine Learning
#19Technically 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.
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!
Re: Tuning Your DBMS Automatically with Machine Learning
#20Technically 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.
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.