5 ms·

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 performa

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.

stdgy 9y ago
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.
danieltillett 9y ago
I assume you are referring to this project [1]. 1. https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl
- icelancer 9y ago
  Yep. I use this regularly. I really like it.
- tyingq 9y ago
  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.
j_s 9y ago
Any techniques, recommendations, and/or open source tools to simplify the archiving step you mentioned?
- seorphates 9y ago
  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).
matt4077 9y ago
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.
gaius 9y ago
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!
- candiodari 9y ago
  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.
  - gaius 9y ago
    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 :-)

stdgy 9y ago

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.

danieltillett 9y ago

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

icelancer 9y ago
Yep. I use this regularly. I really like it.
tyingq 9y ago
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.

j_s 9y ago

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

seorphates 9y ago
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).

matt4077 9y ago

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.

gaius 9y ago

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!

candiodari 9y ago
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.
- gaius 9y ago
  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 :-)