MySQL Performance Tuning

Many databases (and particularly many relational databases) rely on Structured Query Language (SQL), for handling data storage, data manipulation, and data retrieval. If developers want to create, update or delete data then they have always been able to do so easily with SQL statements. That said, we live in an age where the sheer amount of data being shunted around has grown and is still growing at alarming and overwhelming rates, and to compound this, workloads are always changing too, so while SQL statements are useful, there is an ongoing and pressing need for MySQL performance tuning. The swift and efficient movement and processing of data is crucial if we hope to deliver an excellent end-user experience while keeping costs as low as possible.

So, developers wanting to seek out and eliminate hold-ups and inadequate operations must turn to MySQL performance tuning tools which can help them with execution plans and remove any guesswork. MySQL performance may be important, but it isn’t necessarily an easy thing to do. In fact, there are a few aspects of the process which make it a difficult undertaking for developers. MySQL optimization requires sufficient technical prowess, i.e. enough knowledge and skill to comprehend and create a variety of execution plans, and that can be quite off-putting.

As if the fact that it’s tricky wasn’t enough, MySQL optimization takes time and energy. When you’re faced with a whole array of SQL statements to wade through, you face a problem with a built-in degree of uncertainty. Each example needs to be carefully considered during MySQL performance tuning. First, you need to decide which ones to amend and which to leave alone, then you need to work out what approach to take to MySQL tuning with each one that you do select because they’ll all need different approaches depending on what their function is. That’s why we are going to discuss several tips and techniques that will help you approach MySQL performance without getting distracted snowed under by the sheer weight of them.

The Benefits of MySQL Optimization

MySQL performance tuning is essential for keeping costs down. If you can use the right-sized server for the job, then you won’t be paying for more than you need, and if you can understand whether moving data storage or adding extra server capacity will lead to MySQL performance improvements then that helps efficiency too. MySQL tuning can be challenging, but it’s worth the time that it takes because an optimized database has greater responsiveness, delivers better performance, and offers better functionality.

MySQL Query Optimization Guidelines

Here are some useful tips for MySQL tuning. They are a great addition to your collection of best practices.

Make sure that the predicates in WHERE, JOIN, ORDER BY and GROUP BY clauses are all indexed. WebSphere Commerce points out that SQL performance can be improved significantly by predicate indexing because not doing so can result in table scans that culminate in locking and other difficulties. Which is why we highly recommend that you index all predicate columns for better MySQL optimization.

Keep functions out of predicates

The database won’t use an index if there’s a predefined function.

For instance:

  1. SELECT * FROM TABLEONE WHERE UPPER(COLONE)=’ABC’

The UPPER() function means that the database won’t look to the index on COLONE. If that function can’t be avoided in SQL, you’ll need to make a new function-based index or create custom columns in the database in order to experience improved MySQL performance.

Remove non-essential columns with the SELECT clause

Rather than use ‘SELECT *’, always specify columns for the SELECT clause, because unneeded columns add extra load on the database, hindering its performance and causing knock-on effects to the whole system.

Try not to use a wildcard (%) at the start of a predicate

The predicate LIKE ‘%abc’ will cause a full table scan, i.e.:

  1. SELECT * FROM TABLEONE WHERE COLONE LIKE ‘%ABC’

This kind of wildcard use can slow down performance significantly.

Use INNER JOIN, instead of OUTER JOIN where you can

Only use outer join where you absolutely need to. If you use it when you don’t need to then you’ll be putting the brakes on database performance due to slower execution of SQL statements and negative effects on MySQL optimization.

Use UNION AND DISTINCT only where needed

If you use the UNION and DISTINCT operators when other options are available you’ll be needlessly adding excessive sorting which slows down SQL performance. Try using UNION ALL instead for better MySQL performance.

You need to use ORDER BY in SQL for better sorting results

ORDER BY sorts the result-set into pre-determined statement columns. Although this is advantageous for database admins who want data to be sorted, it’s detrimental to MySQL performance. The reason for this is that in order to produce the final result set the query needs to sort the data first, which requires quite a convoluted and resource intensive SQL operation.

Don’t Use MySQL as a Queue

Queues can sneak up on you and slow down your database performance. For example, any time you set up a status for a specific item so that a ‘relevant process’ can gain access to it, you will be creating a queue without knowing it. This just adds pointless extra loading time to use the resource.

Queues are a problem because they cause your workload to be treated in an inefficient serial fashion rather than more efficient parallel and because they frequently lead to a table that contains work in progress along with data from jobs that have already been completed. This slows down the app and also hinders MySQL performance tuning.

The Four Fundamental Resources

A properly functioning database requires four fundamental resources. A CPU, hard drive, memory, and a network. Problems with any one of them will negatively affect the database, so it’s important to choose the right hardware and make sure that it’s all functioning properly. In practical terms, this means that if you’re going to invest in a powerful CPU then don’t try to cut corners with less memory or slower storage. A set up is only as good as its slowest component, and if all of them aren’t at parity then the result will be MySQL performance bottlenecks. Investing in more memory is probably the most cost-effective way of improving performance as it is inherently faster than disk-based storage. If all operations can be held in memory without resorting to disk usage, then processes will speed up considerably.

Pagination Queries

Applications that paginate tend to slow the server. By giving you a results page with a link to the next one these apps usually approach grouping and sorting in ways that don’t use indexes, using a LIMIT and offset function that places an extra burden on the server and then discards rows.

Adjusting the user interface itself will assist with optimization.  Instead of listing all pages in the results and linking to each page, you can just include a link to the next page. This also stops users wasting time on incorrect pages.

In terms of queries, rather than using LIMIT with offset, you can select one more row than you require, and when someone clicks the ‘next page’ link, you can set that last row as the start of the next set of results. For example, if the user looked at a page with rows 201 to 220, select row 221 as well; for the next page to be rendered, you would query the server for rows greater than or equal to 221, limit 21.

MySQL Optimization—Subqueries

In terms of subqueries, it’s better to use a join where you can, at least in current versions of MySQL.

The optimizer team is doing a lot of work on subqueries, so it may be that subsequent iterations of MySQL may come with extra subquery optimizations. It’s best to keep an eye on which MySQL optimizations end up in the each version, and what their effects will be. What I’m saying here is that my advice to err towards a join may not hold forever. Servers are getting more and more intelligent, and the instances where you will need to explain to them how to do something instead of what results to return are reducing.

Use Memcached for MySQL Caching

Memcached is a system that enables distributed memory caching, improving the speediness of websites that use big dynamic databases. It manages to do this by keeping database objects in Dynamic Memory to cut server load any time an outside data source asks for a read. A Memcached layer reduces the number of times a database issues a request.

Memcached stores each value (v) with a key (k), and then retrieves them without the need to parse the database queries for a much more streamlined process.

Conclusion

MySQL tuning  ( as well as tuning of MariaDB ) may be time-consuming and thought-provoking but it’s one of the hurdles that you need to take in your stride if you want to ensure that your users receive the best possible experience. Poor database performance could benefit from investing in the best hardware and making sure that it’s balanced, but even with the best CPUs, and the fastest memory and SSDs on the market, there is still an additional performance improvement to be had from taking the time to implement proper MySQL optimization. It can be a laborious task for developers, but the performance and enhancements and efficiency savings are well worth it. Keep these tips close to hand and refer to them often. They’re not all-encompassing, but they are a handy starting point for your journey into MySQL tuning.

MySQL vs MariaDB – Can One Replace The Other?

MySQL vs MariaDB

With all MariaDB’s power under the hood, “MySQL vs MariaDB” was suddenly a legitimate topic of conversation. The two databases are so similar that MariaDB negates the need to upgrade mysql, because it can usually replace MySQL in a straight swap. Here’s an in-depth comparison of the two, their relation to Plesk and how to set it up.

So, what’s MySQL?

You may have thought that you might like to upgrade MySQL, so this might be one of the first questions that comes to mind. The short answer is, it’s an open source relational database management system that has its roots in SQL, or Structured Query Language. The long answer is that MySQL began life in 2008.

A Swedish company with the catchy name of MySQL AB came up with it, before Sun bought it in 2010. It’s backed up by Oracle and runs on the usual suspects: Linux, UNIX and Windows. Although it’s versatile enough to be used in all kinds of settings, it’s come to be best known to use with web apps and online publishing.

MySQL is a part of the LAMP web development platform, or ‘stack,’ which is the common term used for a bunch of software components used together for a particular purpose. LAMP is an acronym that looks like this:

  • Linux
  • Apache Web server
  • MySQL, the relational database management system
  • PHP, the object-oriented scripting language (Perl or Python are also sometimes used in place of PHP).

We can only guess that if Apache web server had been named Inuit, then LAMP would have been LIMP, and we wonder whether sales would have taken a nosedive as a result? But jokes aside, anyone can use MySQL thanks to its GNU General Public License (GPL). However, Oracle insists that enterprise users pay for the privilege of using their offering in commercial settings.

What is MariaDB?

How comparable is performance when we’re talking about MariaDB vs MySQL? Well first you need to know what MariaDB is. It’s a close relative MySQL, a software fork of MySQL that its developers came up with in 2009. After Oracle Corp picked up MySQL they wanted to create a stand-in.

MariaDB has SQL in its DNA and allows for ACID-style data processing with assured atomicity, durability, isolation, and consistency for transactions. It has lots of features (which developers at MariaDB Corp have worked hard to ensure are similar). They include support for JSON APIs, replication of parallel data and a variety of storage engines, like MariaDB, Aria, ColumnStore, InnoDB, MyRocks, Spider, TokuDB, and Cassandra.

But there are some areas that will be incompatible between versions. For instance, from version 10.1 on, MariaDB stores JSON data using a different format to MySQL 5.7. To get around this, users who want to copy columns of JSON objects from MySQL to MariaDB can either convert them to the format that the latter uses or run statement-based replication jobs with SQL.

MariaDB Corp. offers training, migration services, remote management, and various tailored products to subscribers. MariaDB Foundation, which was set up in 2012, looks after the source code to make sure that the software remains open source.

MySQL vs MariaDB comparison

MySQL MariaDB
Database Structure MySQL is an open-source relational database management system (RDBMS). As with every other relational database, MySQL users work with staple constituents such as tables, constraints, triggers, roles, stored procedures, and views. Each row, or record is identified with a primary key, and foreign keys are used to make sure that related tables can refer to each other accurately. MariaDB is a fork of MySQL, so the database structure and indexes of MariaDB are the same as they are with MySQL. The upshot of this is that your applications will still work if you switch, because the way that the data is organised doesn’t need to change.
Indexes MySQL also allows hash indexes and InnoDB engine utilises inverted lists for FULLTEXT indexes. Most MySQL and MariaDB indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are held in B-trees. The indexes on spatial data types use R-trees so they are an exception.
Databases Deployment MySQL is written in C and C++ and binaries are available for these systems: Microsoft Windows, OS X, Linux, AIX, BSDi, FreeBSD, HP-UX, IRIX, NetBSD, Novell Netware, and lots more. MariaDB is written in C, C++, Bash, and Perl and has binaries for these systems: Microsoft Windows, Linux, OS X, FreeBSD, OpenBSD, Solaris, and others.

Since MariaDB works as a straight swap for MySQL, you should have no trouble uninstalling MySQL, installing MariaDB in its place, then connecting (so long as the file versions are the same). Please be aware that you should run mysql_upgrade to conclude the upgrade process.

Replication / Clustering Replication with MySQL is asynchronous. One server acts as a master to the others’ slaves. You can replicate all databases, selected databases or even selected tables within a database.

MySQL Cluster is a technology providing shared-nothing (there isn’t one point of failure) clustering and auto-sharding (partitioning) for the MySQL database management system.

Internally, MySQL Cluster uses synchronous replication through a two-phase commit mechanism to ensure that data is written to many nodes. This contrasts with what is normally termed “MySQL Replication”, which is asynchronous.

MariaDB offers master-master and master-slave replication as well. MariaDB uses the Galera Cluster for multi-master. From MariaDB 10.1 onwards, Galera is included with MariaDB. To enable clustering you only need to activate the configuration parameters.
Support MySQL developers and support engineers give round-the-clock coverage for patches, updates and fixing bugs. This is part of Oracle’s lifetime support arrangement.

Oracle offers MySQL Premier Support, Extended Support, and Sustaining Support depending upon your needs.

MariaDB ‘s engineers give round the clock support under the enterprise subscription for mission-critical production systems. Support staff are well versed in both MariaDB and MySQL.
Connectors MySQL provides a variety of database connectors which include C, C++, Delphi, Perl, Java, Lua, .NET, Node.js, Python, PHP, Lisp, Go, R, D, and Erlang. MariaDB provides various database connectors which include ADO.NET, C, C++, D, Java, JavaScript, ODBC, Perl, PHP, Python, Ruby, and Visual Studio plug-in.

MySQL and MariaDB under Plesk

Plesk keeps its own databases (which can sit locally on the same server as Plesk or remotely) for things like webmail service, its customers’ websites and their apps. It offers integration for MySQL, MariaDB and PostgreSQL database management engines and provides each of them with their own management tools.

Plesk has its own MySQL database on the local server that’s the key component of keeping it running. The good news is that if you have any version after and including 11.5 you can use MariaDB as a replacement. MariaDB vs MySQL performance shouldn’t concern you as MariaDB performs equally well.

Replacing MySQL with MariaDB in Plesk

  1. Backup the databases sitting on your MySQL server with one of these commands:
    • for backing up for backing up all databases:

# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --all-databases | gzip > /root/mysql.all.dump.sql.gz

  • for just backing up the data which Plesk needs:

# mysqldump -uadmin -p`< /etc/psa/.psa.shadow ` --databases mysql psa apsc | gzip > /root/mysql.mysql-psa-apsc.dump.sql.gz

  1. Configure the MariaDB repository on your server. Make sure to choose a version of MariaDB which is equivalent to the version of MySQL which you are using. Plesk supports Maria DB versions 5.5, 10.0, and 10.1.

Note: after you’ve installed MariaDB, don’t disable this repository on your server. Plesk needs it to be enabled for updates.

  1. If the Watchdog Plesk extension is installed on your server then stop it, and also stop any other monitoring services that might start the mysql service after you’ve stopped it manually.
  2. Install MariaDB by following one of the methods mentioned here
  3. Turn on Watchdog and any other services that you stopped in step three.
  4. Tell Plesk about the changes in the MySQL component:

plesk sbin packagemng --set-dirty-flag

  1. (Optional) If you get any MySQL errors following this update, restore the backup you created in step 1:

zcat /root/mysql.all.dump.sql.gz | mysql -uadmin -p`< /etc/psa/.psa.shadow