Category Archives: Mysql

All things Mysql.

Remove unused servers from PMM

We recently decommissioned some unused Mysql servers. Part of this process involved removing the nodes from Percona Monitoring and Management (PMM). I found that although this process is very simple overall, it is not every intuitive. The process is as follows:

1) Find node name:
curl -s ‘http://pmm-hostname.domain.com/v1/internal/ui/nodes?dc=dc1′ | python -mjson.tool

2) Remove node from consul:
curl -s -X PUT -d ‘{“Datacenter”:”dc1″,”Node”:”node_name“}’ ‘http://pmm-hostname.domain.com/v1/catalog/deregister?dc=dc1′

3) Remove node from prometheus:
curl -X DELETE ‘http://pmm-hostname.domain.com/prometheus/api/v1/series?match\[\]=\{instance=”node_name“\}’

Maatkit is now Percona Toolkit for MySQL

I have written about Maatkit in the past, and more specifically how to use ‘mk-query-digest’.   Development on Maatkit has stopped at this point and you should look to use the Percona Toolkit for Mysql going forward.

We use ‘pt-query-digest’ on a regular basis on our servers in order to profile running Mysql instances during periods of high load and at times when general query profiling is required.  It appears as though there have been some changes to way in which the script works during the transition from ‘mk-query-digest’ to ‘pt-query-profiler’

In order to use it at this point you should use the following example syntax:

# pt-query-digest --user user_name --password pass_word --processlist localhost --interval 0.01 --run-time 10m

You will also notice that there is a new command line paramater ‘--run-time’ that is used to determine how long you would like the profiler to run before producing a report, in this case I would like to run the profiler for 10 minutes.

The output is also slightly different in that the summery report that was normally printed out at the end of the report has been moved toward the beginning of the report as well.

InnoDB Quick Reference Guide

We are currently in the process of upgrading our mysql 5.1 instances to mysql 5.6 , because of this, we are once again very focused on overall mysql performance, and more specifically on Innodb performance going forward.

Matt Reid recently published a book entitled ‘InnoDB Quick Reference Guide.’

I believe that this book will come in very handy to us over the next few weeks and months, as we once again look to delve into mysql behavior and Innodb internals.

I have downloaded a copy of this e-book and I will be providing a more in-depth review shortly.

UPDATE:

Chapter 1: Getting Started with Innodb
This chapter talks about the features of the Innodb storage engine, as well as it’s requiremnets, suported platforms, etc. The chapter does a good job of providing a clear overview of Innodb and it’s overall features and use cases.

Chapter 2: Basic Configuration Parameters
This chapter talks about various configuration varables and how they realate to and effect Innodb. The chapter helps provide you with a better understanding of some of the more basic Innodb configuration options and how the effect Innodb.

Chapter 3: Advanced Configuration Parameters
This chapter provides a much more in-dpeth look at some of the more advanced configuraion variables used to control the behavior of Innodb and how they relate it’s overall performance. The chapter does a great job of covering all the necessary Innodb related parameters that really effect how Innodb performs under real world workloads.

Chapter 4: Load Testing InnoDB for Performance
This chapter focuses on the numerous open-source tools that can be used to test the performance of both the application (Mysql) and the OS (filesystem, etc). All the major tools are covered here and the chapter does a good job of covering each of the tools and their use cases.

Chapter 5: Maintenance and Monitoring
This section discuss some typical maintence tasks that are associated with running Innodb. Other information includes some common methods for finding and pulling runtime information and performance information from the storage engine.

Chapter 6: Troubleshooting InnoDB
This chapter provides some good insite into several of the more common issues that you could face if you have an Innodb deployment, from crash recovery to issues regarding backup and recovery.

Chapter 7: References and links
A small section that you can use to find further detail about Innodb and Mysql.

Overall:
This book does a good job of covering the main features, paramaters, and use cases for the Mysql InnodDB storage engine.

Connecting to Mssql database servers using PHP on Linux

I recently had the pleasure(!) of trying to get PHP on Debian working correctly with a Microsoft SQL server so that the data could be migrated from a Mssql instance into a Mysql one.

Previous to this attempt, the developers were using a Windows machine as a ‘broker’ between the two database. This setup was much too slow for importing and exporting large amounts of data,  so we decided to cut out the middle man (the Windows machine) and do all the processing on a single server.

First I needed to install a few prerequisite packages:

user@computer:$ apt-get install unixodbc-dev
user@computer:$ apt-get install libmysqlclient15-dev

Next we need to download and uncompress the FreeTDS source code:

user@computer:$ wget ftp://ftp.linuxforum.hu/mirrors/frugalware/pub/frugalware/frugalware-testing/source/lib-extra/freetds/freetds-0.82.tar.gz

Next we use configure and install FreeTDS with the following options:

user@computer:$ ./configure --enable-msdblib --prefix=/usr/local/freetds --with-tdsver=7.0 --with-unixodbc=/usr
user@computer:$ make
user@computer:$ make install

Next we need to download and uncompress the PHP source code:

user@computer:$ wget http://us.php.net/get/php-5.3.6.tar.bz2/from/www.php.net/mirror

Next we use configure and install PHP with the following options:

user@computer:$ ./configure --with-mssql=/usr/local/freetds --with-mysql --with-mysqli
user@computer:$ make
user@computer:$ make install

Lastly we will need to create and install the mssql module for PHP:

user@computer:$ cd ext/mssql
user@computer:$ phpize
user@computer:$ ./configure --with-mssql=/usr/local/freetds
user@computer:$ make
user@computer:$ make install

Now you should be able to connect to any Microsoft SQL (and Mysql) server from PHP using the functions found here.

Replication improvements in Mysql 5.5

As promised Rob Young over at Oracle’s Mysql blog has provided us with one more Mysql 5.5 writeup. This time the focus is on some of the new features that you can expect from Mysql 5.5.

Here are the topics covered by Rob’s post:

  • Semi-synchronous Replication
  • Replication Heartbeat
  • Automatic Relay Log Recovery
  • Replication Per Server Filtering
  • Replication Slave Side Data Type Conversions

These are exciting changes, that many people have been looking forward to for quite some time. Including these features in 5.5 will help make sure that replication is even more reliable and more manageable in the future.

Calculating overall database size in Mysql

Recently I had a server that was running low on free disk space, after a bit of digging around I found out that the Mysql database on that particular machine was taking up the bulk of the usable disk space.

Given the fact that this was a shared Mysql instance, I needed to determine which databases were consuming the most amount of space. In order to calculate the total amount of space being used we need to take both the size of the data and all the indexes into account.

I used the following SELECT query, which will return the size of all databases(data + indexes) in MB.

SELECT table_schema "Database Name", sum( data_length + index_length) / 1024 / 1024
"Database Size(MB)" FROM information_schema.TABLES GROUP BY table_schema ;

Running the query above will result in output similar to this:

+--------------------+-------------------+
| Database Name      | Database Size(MB) |
+--------------------+-------------------+
| movies             |     3772.06922913 |
| tmp                |      101.08132978 |
| bikes              |       57.04234117 |
| information_schema |        0.00781250 |
| mysql              |        0.60790825 |
+--------------------+-------------------+

In this case we can clearly see that the ‘movies’ database is consuming the most space. At this point we may want to dig a little deeper and look at the size of each table within the ‘movies’ database, to see where in particular the space is being used.

In order to get some more detail we can use the following SELECT query:

SELECT table_name, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size(MB)" FROM information_schema.TABLES WHERE table_schema = "movies";

Running the query above will result in output similar to this:

+-----------------------------+------------+-------------+--------------+----------+
| table_name                  | table_rows | data_length | index_length | Size(MB) |
+-----------------------------+------------+-------------+--------------+----------+
| Id                          |          1 |       16384 |            0 |     0.02 |
| Teaser                      |          1 |       16384 |            0 |     0.02 |
| TeaserLog                   |      21767 |  3177586576 |       392192 |  3030.76 |
| TeaserChild                 |     912602 |    48873472 |     33112064 |    78.19 |
| Director1                   |     460722 |    57229312 |     13156352 |    67.13 |
| Director2                   |    2044044 |    87801856 |            0 |    83.73 |
| City                        |     286134 |    17367040 |     17858560 |    33.59 |
| City_alt_spelling           |       1086 |       65536 |        65536 |     0.13 |
| City_backup                 |     148811 |    13123584 |            0 |    12.52 |
| City_misspelling_log        |     166589 |     9977856 |            0 |     9.52 |
| City_save                   |     148618 |    13123584 |            0 |    12.52 |
+-----------------------------+------------+-------------+--------------+----------+
11 rows in set (0.14 sec)

Based on the output from this SQL query we are able to see that the ‘TeaserLog’ table is using up the majority of space within the ‘movies’ database.

Performance and scalability improvements in Mysql 5.5

Oracle’s Mysql Blog has a very good post that provides an overview of some of the improvements that you can expect in the upcoming Mysql 5.5 release.

This writeup focuses mainly on the changes as they relate to performance and scalability, however the author (Rob Young) expresses his plans to discuss other aspects as well, sometime in the near future.

Here are just a few of the topics covered by Rob:

  • Improved Default Thread Concurrency
  • Improved Recovery Performance
  • Multiple Buffer Pool Instances
  • Native Asynchronous I/O for Linux
  • Improved Metadata Locking Within Transactions
  • Better performance on Windows based installs

At some point I hope to continue my testing and benchmarking of several different versions of Mysql such as MariaDB, Percona and Mysql 5.5. However for production databases we will be sticking with the Mysql 5.1.x code branch for the foreseeable feature.

O’Reilly MySQL Conference & Expo 2010 Keynote Vidoes

For those of us who were not able to attend this years Mysql Conference in Santa Clara, CA, the keynote videos have been posted online for your viewing pleasure!

UPDATE:

Here is a link to another location (Youtube), that has some more of the videos from the Mysql Conference, including the one given on Oracle to Mysql migration given by NPR’s own Joanne Garlow.

Raid Controller Caching Options

Here is a quick link to a blog post that talks about RAID caching for various database workloads.  The post also lists some of the popular RAID cards that are being put into use today, as well as  some interesting features that the author feels are missing from these current lineup of available RAID cards.

NPR developer talk at O’Reilly MySQL Conference and Expo 2010

Joanne Garlow one of the Senior developers here at NPR , will be giving a talk this Tuesday at the 2010 O’Reilly MySQL Conference and Expo in Santa Clara, CA.  The talk is entitled ‘Migration from Oracle to Mysql : An NPR Case Study’ and will focus on some of the ‘lessons learned’ during our recent Oracle to Mysql migration, it will also cover some of the tools that we found useful during the migration as well as some of the issues we encountered concerning character encoding and concurrency.

If you are thinking about converting your backend databases from Oracle to Mysql and you are attending this conference, you should attend this talk, as I am sure it will be very informative and quite helpful to you going forward.