Codership’s Galera Cluster Best Practices Webinar for MySQL DBAs and DevOps Part One is now available to watch here
“Best Practices for MySQL Scalability.”
If you have not already done so, I encourage you to register for my “Best Practices for MySQL Scalability” Webinar which will take place on May 1st at 10 a.m. PST. This will be an overview presentation, led by me and providing a high-level look at the components of MySQL scalability: application architecture, MySQL version and configuration, choosing hardware and operating systems. For each area we’ll investigate the most important best practices. Talk to you on Wednesday, and remember to prepare your questions in advance to get the most value out of the Webinar!
More info: MySQL scalability depends on getting many things right including …[Read more]
One of the main responsibilities of any database administrator is to keep a close eye on how database performance is impacting size and storage. Decisions will have to be made on whether or not to make changes within the database structure or application itself, or to make the changes on the storage and resource side [...] Read More
A default installation of MySQL is easy to perform, but if you really want your databases to sing, you should tune them like you would tune a piano. In MySQL tuning pertains to either the application or the database system. In this post, we cover some common tuning techniques and best practices to increase your [...] Read More
1. Use Auto-scaling
Auto-scaling is a unique feature of cloud computing and Amazon's EC2 offering. Setup a load balancer and a couple of webservers for your application as you normally would. Design your webserver based on a template AMI that you'll reuse over and over. Then setup auto-scaling and set thresholds based on the traffic you forecast. When a threshold is passed, AWS will spinup a new instance of your webserver, and roll it into the load balancer pool automatically. Once traffic falls below the scale back threshold, Amazon will take a server out of the pool for you.
Be sure to monitor this …[Read more]
1. Tune those queries
By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. This is why we often see clients with growing pains, and scalability challenges as their site becomes more popular. This also makes sense. It wouldn't necessarily be a good use of time to tune a query for some page off in a remote corner of your site, that didn't receive real-world traffic. So some amount of reactive tuning is common and appropriate.
Enable the slow query log and watch it. Use …[Read more]
Lately in the MySQL community, we only hear about scalability or performance improvements of storage engines, but nothing about query engine itself. For example, one classic example being InnoDB; if[...]
When we wanted to quickly process intermediate data by using temporary or heap tables; then its normal tendency to create a table like its source table; so people will simply opt for CREATE TEMPORARY TABLE temp_table LIKE source_table.
This is an easy and convenient way. But the problem is if you have a simple stored procedure or code module that gets executed frequently, then you might indirectly experience a slow-down in the performance if your source_table is large enough and highly contended. Things will be really worst if concurrent threads starts using the source_table or in combination of source_table and CREATE LIKE.. statements and can lead to disaster scalability issues. The main reason for the post is; I noticed from SHOW PROCESSLIST from random servers where X threads getting into WAITING for TABLE lock state for simple …[Read more]