We’ve heard lots in recent years about Big Data and the alternative models of data management and processing, like Hadoop and NoSQL. But truth be told, relational databases are still the workhorses of most of today’s small and medium sized businesses. Relational DBs date back over 40 years and SQL skills are fairly common, and they’re known to be highly secure.
MySQL is the world’s second most popular relational database management system (RDMS) and is the most popular open-source version of the database. It’s easily accessible and is often known to be part of the LAMP web development stack, standing for the ‘M’ in the acronym of popular tools, along with Linux, Apache, and PHP/Perl/Python. The fact that MySQL is free, easy to setup and scales fast are some of the main reasons why it’s the best match for many SMBs.
But as with any relational database, MySQL can be a beast when it doesn’t work right, causing your applications to slow down or stop performing and putting your business in jeopardy. That’s why you want to pay close attention to MySQL performance and do everything possible to keep things moving along. In the following we’ve accumulated 7 quick MySQL performance tips that will help keep your environment running smoothly and effectively, and your business applications running at their peak.
1. Understand the 4 fundamentals
In order to function correctly a database needs four essential components – CPU, memory, disk, and network. If any of these aren’t working properly, or are erratic, then the database server will also perform poorly. The key here is to balance these four areas properly. For example, don’t have your servers running with fast CPU and disk space, but then become short on memory allocation. Keeping an eye on these four focus areas will address the majority of your performance issues and help keep your applications running optimally and at scale.
2. Partition your tables
As your database scales up from a few thousand rows to hundreds of thousands of rows, performance lags behind and the queries take longer to process. That’s why it’s important to use partitions. The principle of partitions is about breaking the database information into smaller, more manageable chunks to provide quicker processing times. For instance, say you decide to separate all rows where the column zipcode has a value between 70000 and 79999. You would use the RANGE command to partition this segment of data. In other words, PARTITION BY RANGE (zipcode).
According to one expert, indexing is the most misunderstood part of database management, but if properly implement the key to highest returns on performance. He goes on to point out 3 primary points to remember when it comes to indexing. We summarize these elements here:
* It’s much more efficient for a database to find groups of rows rather than churning through individual rows, which leads to random disk operations. Keep things simple by setting your operations to find groups of adjacent rows rather than single rows.
* Indexing should allow the database to read rows in their desired order rather than sorting, which ultimately proves to be costly.
* Use “covering indexes” when possible, which contains all and possibly more than the columns you need for your query. By using SELECT * FROM tablename WHERE criteria, this kind of index will usually will use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.
4. Pay attention to your storage engine
MySQL supports two main storage engine types called InnoDB or MyISAM. There’s a lot of discussion about the differences and which engine is best for which set of circumstances. The best advice on the matter seems to be that for beginners and novices MyISAM is the best bet, while InnoDB tends to be a little more complicated and is more difficult for some to use and navigate.
5. Don’t overly-focus on configurations
One site has this to say about the matter of MySQL configuration settings, which is worth quoting here:
The defaults that ship with MySQL are one-size-fits-none and badly outdated, but you don’t need to configure everything. It’s better to get the fundamentals right and change other settings only if needed. In most cases, you can get 95 percent of the server’s peak performance by setting about 10 options correctly. The few situations where this doesn’t apply are going to be edge cases unique to your circumstances.
6. Draw upon the expertise of your peers
Fortunately, it helps that MySQL has been around for 20 years there’s a ton of industry experience. Don’t go it alone, but rather leverage the knowledge of your peers to solve questions and to optimize your environment. Aside from the standard forums, blogs, and Q&A sites, there are a good number of outlets for gaining insights and knowledge on topics whenever you’re in need of serious expertise. Conferences, trade shows, and local user group events can be a great way to get quick and informative answers to the most pressing issues within your MySQL environment.
7. Leverage Monitis MySQL Monitoring
If you’re looking for best-in-class web monitoring and performance tracking then you need to head over to Monitis. With its industry-leading global service, Monitis lets businesses monitor their network anytime and from anywhere, including website uptime monitoring, full page load and transaction monitoring, and web load testing. The benefits and takeaways here are peace of mind and less stress. Last November the company introduced a new MySQL Monitoring feature to help administrators more effectively monitor their MySQL environment and make sure applications run at the speed and at the quality that customers require today. By checking active queries, identifying which ones are being sent or received, and locating potential hangups and problems from the slowest-running queries, Monitis MySQL Monitoring can display all of your information in easy and intuitive to read graphs. By keeping your applications running smoothly and more effectively, Monitis alleviates the stress and helps you focus on running your business.
via Planet MySQL
7 quick MySQL performance tips for the small business