What is the simplest way to optimize MySQL Database Performance?

Like all relational databases, MySQL can prove to be a complicated beast that can halt at a time, leaving your applications in the lurch and your business on the line. The truth is, most MySQL performance issues are underlying common mistakes. It is important to eliminate these errors, which are often obscured by some subtlety in your workload or configuration trap, in order to ensure that your MySQL server hums along at top speed, providing stable and consistent performance. 

Fortunately, many MySQL performance optimization issues turn out to have similar solutions, making it a manageable task to troubleshoot and tun MySQL.

Here are 10 tips from MySQL expert for great performance:

Profile the workload - Profiling the workload of the server is the best way to understand how your server spends its time. You can expose the costliest queries for further tuning by profiling your workload. Time is the most important metric here because you care very little about anything except how quickly it completes when you issue a query against the server.

The best way to profile your workload is by using a tool like the query analyzer for MySQL Enterprise Monitor or the Percona Toolkit pt-query-digest. These tools capture server queries and return a task table sorted by decreasing response time order, instantly bubbling up to the top of the most expensive and time-consuming tasks so you can see where to focus your efforts.

Understand the fundamental resources - Four basic resources are needed for a database server to function: CPU, memory, disk, and network. If any of these are weak, erratic, or overloaded, it is highly likely that the database server will perform poorly. Understanding the basic resources is important in two specific areas: hardware selection and troubleshooting.

Ensure good-performing components all around when selecting MySQL hardware. Just as important, they are fairly well balanced against each other. Organizations often choose servers that have fast CPUs and disks but are hungry for memory. In some cases, adding memory is a cheap way to increase performance by magnitude orders, particularly on disk-bound workloads. This may seem counterintuitive, but disks are overused in many cases because there is not enough memory to hold the working data set of the server.

Avoid using MySQL as a queue - Queues and access patterns like queues can sneak into your application without your knowledge. For example, if you set an item's status so that it can be claimed by a particular worker process before acting on it, then you create a queue unwittingly. It is a common example to mark emails as unsent, send them, then mark them as sent.

Queues cause problems for two main reasons: they serialize your workload, preventing parallel tasks, and often result in a table that includes processing work as well as historical data from long processed jobs. Both add application latency and load MySQL.

Filter the results by the cheapest first - A great way to optimize MySQL is first to do cheap, imprecise work, then hard, accurate work on the smaller, resulting data set. Suppose you're looking for something within a given geographic point radius, for example. The first tool in the toolbox of many programmers is the "Haversine" formula for computing distance along a sphere's surface. The problem with this technique is that the formula requires a lot of very CPU-intensive trigonometric operations. Great-circle calculations tend to run slowly and skyrocket the machine's use of the CPU.

Before applying the formula of the great circle, set your records to a small subset of the total and adjust the resulting set to an accurate circle. A square containing the circle is an easy way to do this (precisely or inaccurately). Thus, with all those expensive trig functions, the world outside the square is never hit.

Know the scalability death traps - Scalability isn't as vague as you might think. There are, in fact, precise mathematical scalability definitions expressed as equations. These equations emphasize why systems are not as scaleful as they should be. Take the Universal Scalability Law, a definition useful to express and quantify the scalability characteristics of a system. It explains problems of scaling in terms of two basic costs: serialization and crosstalk.

Their scalability is inherently limited in parallel processes that must be stopped for something to take place serialized. Likewise, they limit each other if the parallel processes have to chat with each other all the time to coordinate their work. Evite serialization and crosstalk, and it will scale your application much better. What translates into MySQL's inside? It varies, but there are some examples that would avoid exclusive row locks. That is why Queues, point 3 above, tend to scale poorly.

Indexing is probably the most misunderstood topic in databases as there are so many ways to get confused about how indexes work and how they are used by the server. To really understand what is going on, it takes a lot of effort.

All You Need to Know About MySQL Partitions

MySQL partitioning is about changing the way the database engine physically stores data–ideally optimizing it. You can distribute portions of table data (a.k.a. partitions) throughout the file system based on a set of user-defined rules (a.k.a. "partitioning function"). In this way, if only a fraction of table data is accessed by the queries and the partitioning function is set properly, there will be less scanning and it will be faster to scan queries.

It is important to note that when dealing with large data sets, partitioning makes the most sense. MySQL Partitioning will not make a difference if you have less than a million rows or just thousands of records.

Horizontal and Vertical Partitioning

Horizontal partitioning means that different physical partitions are assigned to all rows that match the partitioning function. Vertical partitioning allows the splitting into different physical partitions of different table columns. MySQL currently supports horizontal, but not vertical, partitioning. The documentation of the engine clearly states that vertical partitions will not be supported in the near future: "There are no plans to introduce vertical partitioning into MySQL at this time."

Benefits of partitioning

Storage: More data can be stored in one table than can be stored on a single partition of the disk or file system.

Deletion: It's almost instantaneous to drop a useless partition, but a classic DELETE query running in a very big table might take minutes.

Partition Pruning: This is the ability to exclude from a search non-matching partitions and their data; it makes it faster to query. MySQL 5.7 also supports explicit selection of partitions in queries, which significantly increases the speed of search. This also applies to DELETE, INSERT, REPLACE and UPDATE statements as well as LOAD DATA and LOAD XML statements. (Obviously, this only works if you know in advance which partitions you want to use.)

How to check if the engine supports partitioning

Community binaries by default include support for partitioning. By running the SHOW PLUGINS statement, you can check whether it is supported in your current instance. Both outputs note that the plugin you are searching for is called "partition" and is set to ACTIVE. If you do not see the partition listed by either of these methods, your version of MySQL will probably have been installed without support for partitioning. In this case, the-DWITH PARTITION STORAGE ENGINE option must be used to perform another MySQL compilation. The documentation on "Installing MySQL from Source" will show you how to do this.

Basics of partitioning

Optimized date-time functions: For partition use, the functions TO DAYS(), YEAR(), and TO SECONDS() are optimized. You can also use date and time functions returning integer values or NULL values like WEEKDAY(), DAYOFYEAR(), or MONTH().

Identification of partitions: Partitions are always numbered sequentially, starting when created automatically from 0. Using partition numbers, rows are inserted to identify where each row is going. For example, if you partition a table into four, the partition numbers0, 1, 2, and 3 will be used by MySQL to identify each partition.

Naming conventions: Partition names should follow the same tables and database naming conventions for MySQL. It is worth noting that names of partitions are not sensitive to cases.

Directory: Partitions can be assigned during creation to a specific directory. You can do this with the CREATE TABLE statement's DATA DIRECTORY and INDEX DIRECTORY options.

Engine: The same table partitions must use the same storage engine. All partitions will be MyISAM if you partition a MyISAM table; the same applies to InnoDB.

Index Partitioning: Partitioning applies to all data and indexes in a table; it is not possible to partition data only and not indexes, or vice versa. You can't partition just a portion of the table as well.

Foreign keys: Partitioned tables of InnoDB do not support foreign keys. The implications of this for data integrity are very important. In the partitioned table, you can't add a foreign key (pointing to another table). Conversely, you won't be able to partition it if the table has a foreign key. A non-partitioned table cannot also have a foreign key column pointing to a column of a partitioned table.

Partition Columns: The rule of thumb here is that in the partitioned table, all columns used in the partitioning expression must be part of every single key. This seemingly simple statement imposes some important limitations that we will discuss next.

Eric Vanier is a database expert who, with his team of mysql consulting professionals, has helped numerous Fortune 500 companies maintain their database. The present scenario of database management is very cluttered and requires both skill and expertise to champion. Thanks to him, companies are now dependent on a smooth-running and have experienced mysql performance improvement.