SYS-CON MEDIA Authors: Yeshim Deniz, Elizabeth White, Pat Romanski, Liz McMillan, William Schmarzo

Related Topics: Open Source Cloud, Java IoT, Microsoft Cloud, Linux Containers, Machine Learning , Apache

Open Source Cloud: Article

Ten Tips for MySQL Users

The things every MySQL user should know

MySQL, just like other relational databases, is complicated. It can jam at any time jeopardizing your applications or business without notice. Most MySQL malfunctions are due to common mistakes. It is essential to avoid these mistakes, often hidden by configuration trap or workload, to ensure that MySQL server runs effectively. Here are 10 tips to ensure excellent performance of the MySQL server.

Profile your workload
Profiling the server's workload is the best way to know how the server spends time. This exposes the most exclusive questions for tuning. Time is very essential here as one considers how quickly the server completes a request when a query is issued it. Similar queries are grouped by these tools together in a row in order to distinguish slow and fast queries.

Understand the fundamental resources
A database server requires four important resources to function: CPU, disk, network and memory all of which if erratic, overloaded or weak may cause the database to malfunction. It is important to understand these resources in troubleshooting problems and choosing hardware. Ensure all components are functioning well and reasonably balanced against each other when choosing hardware for MySQL. Server performance can be cheaply enhanced by adding memory on disk-bound workloads. On the other hand, MySQL performs best with fast CPUs since each query cannot be paralleled across CPUs. When troubleshooting, performance and utilization of resources must be checked carefully to determine whether it is overloaded or functioning poorly. This also goes for using database conversion tools.

MySQL should not be set as a queue
Queues may invade your application without your knowledge. For instance, when a status is set such that a particular worker process claims it before acting on it, then you are certainly creating a queue. Queues cause problems by serializing workload and preventing tasks from being executed in parallel.

Filter results
MySQL can be optimized to do cheap work first then the hard work, on a smaller set of resulting data. For instance, when searching for something within a radius of a given geographical point, the great-circle (Haversine) formula is the tool used for calculating distance on the surface of a sphere. This formula, however, needs a lot of CPU-sensitive trigonometric calculations thus posing a problem as the CPU utilization rises due to slow operations. It is hence necessary to pare down records to a smaller subset of the total and trimming the resulting set to a precise circle before using this formula

Understanding the scalability death traps
Unlike common beliefs, scalability is not vague as there are exact mathematical equations expressing scalability. The equations show why systems do not scale as they should. The universal scalability law explains scaling in terms of serialization and crosstalk. Parallel processes that must stop for a serialized process to take place are limited in scalability. Similarly, when parallel processes chat with each other consistently to coordinate their work, they hinder each other. Applications scale better when crosstalk and serialization are avoided, which translate to locks on rows for MySQL.

Too much focus should not be placed on configuration
DBAs spend time tweaking configurations resulting in a small improvement that can be damaging. Not all MySQL defaults should be configured. Ninety-five percent of the server's best performance can be obtained by correct setting of 10 options. Server tuning tools are recommended for giving guidelines.

Look out for pagination queries that cause the server to malfunction
This causes the server to generate a lot of work and discard rows. Optimization can be done to set the final row as the starting point for another set of results.

Save statistics promptly, alert reluctantly
It is important to capture and save metrics as they are essential in effecting changes in the system. Meanwhile, alert sparingly and only on conditions indicating an actionable problem.

Learn the three rules of indexing
Properly designed indexes serve three functions in a database: Allow the server identify groups of adjacent rows, avoid sorting by reading in a preferred order and satisfy all queries from the index alone.

Leverage the expertise of your peers
Doing what you feel is logical will work most of the times but not all the times. The time it doesn't work may cost you time and money.

More Stories By Anne Lee

Anne Lee is a freelance technology journalist, a wife and a mother of two.

Latest Stories
Founded in 2002 and headquartered in Chicago, Nexum® takes a comprehensive approach to security. Nexum approaches business with one simple statement: “Do what’s right for the customer and success will follow.” Nexum helps you mitigate risks, protect your data, increase business continuity and meet your unique business objectives by: Detecting and preventing network threats, intrusions and disruptions Equipping you with the information, tools, training and resources you need to effectively m...
The Transparent Cloud-computing Consortium (T-Cloud) is a neutral organization for researching new computing models and business opportunities in IoT era. In his session, Ikuo Nakagawa, Co-Founder and Board Member at Transparent Cloud Computing Consortium, will introduce the big change toward the "connected-economy" in the digital age. He'll introduce and describe some leading-edge business cases from his original points of view, and discuss models & strategies in the connected-economy. Nowad...
Doug was appointed CEO of Big Switch in 2013 to lead the company on its mission to provide modern cloud and data center networking solutions capable of disrupting the stronghold by legacy vendors. Under his guidance, Big Switch has experienced 30+% average QoQ growth for the last 16 quarters; more than quadrupled headcount; successfully shifted to a software-only and subscription-based recurring revenue model; solidified key partnerships with Accton/Edgecore, Dell EMC, HPE, Nutanix, RedHat and V...
Having been in the web hosting industry since 2002, dhosting has gained a great deal of experience while working on a wide range of projects. This experience has enabled the company to develop our amazing new product, which they are now excited to present! Among dHosting's greatest achievements, they can include the development of their own hosting panel, the building of their fully redundant server system, and the creation of dhHosting's unique product, Dynamic Edge.
Digital transformation is about embracing digital technologies into a company's culture to better connect with its customers, automate processes, create better tools, enter new markets, etc. Such a transformation requires continuous orchestration across teams and an environment based on open collaboration and daily experiments. In his session at 21st Cloud Expo, Alex Casalboni, Technical (Cloud) Evangelist at Cloud Academy, explored and discussed the most urgent unsolved challenges to achieve fu...
PCCW Global is a leading telecommunications provider, offering the latest voice and data solutions to multi-national enterprises and communication service providers. Our truly global coverage combined with local, on the ground knowledge has helped us build best in class connections across the globe; and especially in some of the remotest, hard-to-reach areas in exciting growth markets across Asia, Africa, Latin America and the Middle East.
NanoVMs is the only production ready unikernel infrastructure solution on the market today. Unikernels prevent server intrusions by isolating applications to one virtual machine with no users, no shells and no way to run other programs on them. Unikernels run faster and are lighter than even docker containers.
Public clouds dominate IT conversations but the next phase of cloud evolutions are "multi" hybrid cloud environments. The winners in the cloud services industry will be those organizations that understand how to leverage these technologies as complete service solutions for specific customer verticals. In turn, both business and IT actors throughout the enterprise will need to increase their engagement with multi-cloud deployments today while planning a technology strategy that will constitute a ...
CloudEXPO | DevOpsSUMMIT | DXWorldEXPO Silicon Valley 2019 will cover all of these tools, with the most comprehensive program and with 222 rockstar speakers throughout our industry presenting 22 Keynotes and General Sessions, 250 Breakout Sessions along 10 Tracks, as well as our signature Power Panels. Our Expo Floor will bring together the leading global 200 companies throughout the world of Cloud Computing, DevOps, IoT, Smart Cities, FinTech, Digital Transformation, and all they entail. As ...
Darktrace is the world's leading AI company for cyber security. Created by mathematicians from the University of Cambridge, Darktrace's Enterprise Immune System is the first non-consumer application of machine learning to work at scale, across all network types, from physical, virtualized, and cloud, through to IoT and industrial control systems. Installed as a self-configuring cyber defense platform, Darktrace continuously learns what is ‘normal' for all devices and users, updating its understa...
CloudEXPO | DevOpsSUMMIT | DXWorldEXPO Silicon Valley 2019 will cover all of these tools, with the most comprehensive program and with 222 rockstar speakers throughout our industry presenting 22 Keynotes and General Sessions, 250 Breakout Sessions along 10 Tracks, as well as our signature Power Panels. Our Expo Floor will bring together the leading global 200 companies throughout the world of Cloud Computing, DevOps, IoT, Smart Cities, FinTech, Digital Transformation, and all they entail.
SUSE is a German-based, multinational, open-source software company that develops and sells Linux products to business customers. Founded in 1992, it was the first company to market Linux for the enterprise. Founded in 1992, SUSE is the world’s first provider of an Enterprise Linux distribution. Today, thousands of businesses worldwide rely on SUSE for their mission-critical computing and IT management needs.
Digital Transformation (DX) is a major focus with the introduction of DXWorldEXPO within the program. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throughout enterprises of all sizes. We are offering early bird savings...
In his keynote at 19th Cloud Expo, Sheng Liang, co-founder and CEO of Rancher Labs, discussed the technological advances and new business opportunities created by the rapid adoption of containers. With the success of Amazon Web Services (AWS) and various open source technologies used to build private clouds, cloud computing has become an essential component of IT strategy. However, users continue to face challenges in implementing clouds, as older technologies evolve and newer ones like Docker c...
Only Adobe gives everyone - from emerging artists to global brands - everything they need to design and deliver exceptional digital experiences. Adobe Systems Incorporated develops, markets, and supports computer software products and technologies. The Company's products allow users to express and use information across all print and electronic media. The Company's Digital Media segment provides tools and solutions that enable individuals, small and medium businesses and enterprises to cre...