Select Page

Percona Server Goodies

Patrick Galbraith | | December 31, 2010

I’m posting to describe some of the features and improvements that I have been utilizing with the Percona Server with XtraDB MySQL distribution, as well as Percona’s XtraBackup. If you don’t already know, Percona Server XtraDB is a backwards-compatible MySQL distribution with XtraDB, an enhanced version of InnoDB. It runs faster, has better performance and is easier to monitor and tune. It also comes with some particular features that I have been taking advantage of lately.

Some of our customers have a huge amount of data, so you end up with a database server and architecture that is quite often pushed to the limits. When you reach these limits, you quickly hear from the customer and experience all those issues that a DBA would rather not have to encounter — highly loaded servers, huge slave lags, application response being slow for users, and the list goes on. You, as a DBA, constantly seek optimizations– to a schema, tweaked server settings, better architecture for scaling out, so if something is available that has performance improvements out of the box, you will gladly utilize it!

I’m going to mention my experiences using Percona Server with XtraDB as well as several features that I have been glad to take advantage of over the past several months.

Obtaining Percona Server with XtraDB

Go to http://www.percona.com/software/percona-server/downloads. You can obtain a binary package or source, build if need be, and install according to instructions. My experience on Ubuntu was that I had to un-install the existing stock MySQL 5.1 (making sure to back up my data directory). Also, with Ubuntu, there are the Debian startup hooks in /etc/mysql/. I turn these off because I find them unnecessary. The easiest way to ensure they don’t run for me was to edit /etc/init.d/mysql and add an exit 0 to the top of this script. Alternatively, I have started to remove Debian-specific lines from /etc/init.d/mysql from:

MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

to:

MYADMIN="/usr/bin/mysqladmin"

And remove the lines:

# Now start mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start)
 [ -n "$output" ] && log_action_msg "$output"

Then you can simply remove all the debian-specific files from /etc/mysql.

Now, this only applies for Debian-based Linux distributions like Ubuntu and of course Debian itself. There are instructions pertinent to other Linux distributions on Percona’s site.

Also note that If you are upgrading from MySQL 5.0, you will also need to run mysql_upgrade.

Once you have this done, you can restart MySQL.

A few of my favorite features

The following are a few of the features that Percona provides that I have been using in particular lately that I have found useful

Dump and restore buffer pool – innodb_lru_dump_restore

My first favorite feature is being able to dump and restore the buffer pool! This feature is super easy to take advantage of and whether you should use it or not is a no-brainer! How many times have you had to restart MySQL, knowing all to well that your hard-earned buffer pool will be lost? Well, this is no more! The way this feature works is that despite it’s name, it doesn’t actually dump and restore the contents of the buffer pool. Better yet, it stores the identifiers to the pages of the buffer pool, hence not taking up space on disk equivalent to the size of your buffer pool. So, if you have a buffer pool of 100GB (as I set up for a client’s new server last night with great anticipation) it doesn’t mean that it will dump 100GB of data.

To use it, simply add the following to your my.cnf:

innodb_lru_dump_restore=1

You can also set this variable dynamically on a running server:

set global innodb_lru_dump_restore=1;

And make sure to add it to your my.cnf.

And to manually load and restore the buffer pool, you can issue the following:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_DUMP*/;
+------------------------------+
| result_message               |
+------------------------------+
| XTRA_LRU_DUMP was succeeded. |
+------------------------------+

And to restore:

mysql> select * from information_schema.XTRADB_ADMIN_COMMAND /*!XTRA_LRU_RESTORE*/;
+---------------------------------+
| result_message                  |
+---------------------------------+
| XTRA_LRU_RESTORE was succeeded. |
+---------------------------------+

Fast InnoDB Checksum

The process of the InnoDB checksum is important in maintaining data integrity but it does carry with it a performance penalty. Percona XtraDB has added a new feature, innodb_fast_checksum. Simply add the following to your my.cnf

innodb_fast_checksum = 1

This will require a restart of you MySQL server.

When I switched to using this for one of our clients, I noticed a definite improvement in performance, particularly a slave that was lagging was better able to catch up.

Higher concurrent transactions limit (4072 vs. 1024)

This feature has made it possible to have a higher number of concurrent transactions from the standard InnoDB limit of 1024. This feature does come with some caveats, explained on Percona’s site, particularly to do with using ibbackup. (What I’m not sure is if this is an issue with Xtrabackup). Anyhow, this new limit gives you more concurrent transactions due to it making more undo slots available, if you run into the problem of running out of undo slots.

To use it, you simply use:

innodb_extra_undoslots=ON

In your my.cnf. As the Percona site shows, you will also want to analyze your error log upon restarting with this option turned off for either a warning or error to indicate whether these expanded slots were used.

HandlerSocket

Percona comes stock with HandlerSocket, a plugin created by Akira Higuchi. This is a plugin that makes it possible for you to use MySQL as a NoSQL datastore. It essentially bypasses the SQL parsing and optimizer layer — where most of the processing is done — and goes right to the storage engine layer for an extremely fast data store within the framework of MySQL – including all the features with MySQL such as replication, logging, various backup strategies. And you can still access this data through the SQL layer, which gives you both SQL and NoSQL, depending on your application’s needs. Furthermore, if you are using a storage engine such as InnoDB, you get the innate benefit of caching with the buffer pool.

I haven’t even begun to take full advantage of this feature, but am in the process of evaluating it. To use this feature with Percona Server, run the following:

mysql> install plugin handlersocket soname 'handlersocket.so';

To verify:

mysql> select plugin_name, plugin_status, plugin_library from information_schema.plugins where plugin_name like 'handler%';
+---------------+---------------+------------------+
| plugin_name   | plugin_status | plugin_library   |
+---------------+---------------+------------------+
| handlersocket | ACTIVE        | handlersocket.so |
+---------------+---------------+------------------+

Add the following to your my.cnf:

# port for read requests
loose_handlersocket_port = 9998

# port for write requests
loose_handlersocket_port_wr = 9999

# worker threads to handle read requests
loose_handlersocket_threads = 16

# worker threads to handle write requests
loose_handlersocket_threads_wr = 1

# allow handler socket to access as many concurrent connections as possible
open_files_limit = 65535

For more information on this plugin, see http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html#comment-form

XtraBackup

The next tool I really like isn’t part of the Percona Server is XtraBackup. XtraBackup is similar to InnoDB Hot Backup, Ibbackup (InnoBase/Oracle) except it is free and open source. There are a number of features that XtraBackup has that Ibbackup does not have, listed on Percona’s site. You can obtain XtraBackup from Percona’s site, see http://www.percona.com/software/percona-xtrabackup/

I like to use it with the innobackupex

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS