Using pt-online-schema-change

As I’ve been more and more experienced as a MySQL DBA it seems as though I’m going from brick wall to brick wall. Fortunately I came across pt-online-schema-change after running into some issues with running large table alterations on a production environment.

The way pt-online-schema-change implements the non-blocking schema change is pretty clever. The utility creates a duplicate schema to the current table, then runs the alter command on the new schema, once the new schema is created triggers are added to the original table for update, delete, and inserts. The triggers add the newly altered information to the altered schema.

The utility chunks the current data and inserts the chunks into the newly altered table. Finally the tables are swapped, the triggers are dropped, and the newly demoted table is deleted. From what I noticed pt-online-schema-change pre-v2.1 sets SQL_BIN_LOG to 0 by default unless you specified –bin-log. This WILL break replication if the table schema is changed on the master but is not replicated to the slave; the break could happen much, much later too. It really depends on your environment and when you first specify the new alteration in a query. I totally didn’t have this happen to me…

How to use

 The basics of how to use pt-online-schema-change are extremely quick pick up. It is pretty much like any other MySQL utility in that you specify your host, username, password, as well as the database and table. However it can be a bit annoying to remember the format for specifying the database and tablename. But I digress; let’s say that you want to change the table cookie.peanutbutter from MyISAM to InnoDB.

 

 

Essentially you can specify any table alteration (omitting the ALTER TABLE statement prefix) to pt-online-schema-change and it should work. In general I’ve found it is preferable to run online schema changes against any table using a auto-incrementing primary key. However you can always take a more manual approach to the chunking by using –chunk-index, –chunk-index-columns, and –chunk-size.

Flatricide Pulgamitude

So for an Information Retrieval class there is a contest to get the highest ranked website on Google for the term “Flatricide Pulgamitude”. I have to say this is probably one of the most pointless things that I could be interested in. Yet I do enjoy winning an awful lot. Might as well toss my hat into this race and see what comes of it.

MySQL 5.6 Database Administrator Exam Study Guide

As many of you may (or may not…) know Oracle is currently running the beta tests for the MySQL 5.6 Database Administrator exam and of course I had to sign up for it. I mean it’s not like I don’t already have enough on my plate, but as a masochist I felt it was only proper that I throw this on top too. During the process of studying for the exam I made myself a quick reference guide to what I believe is the appropriate MySQL 5.6 manual sections that correspond to the exam topics. I think it goes without saying I have no knowledge of what will be on the test so this is just a guess as to the material, but I figured I might as well throw it up anyway so that ya’ll don’t have to do make one. Also if I’m not lazy as fuck (and legally barred from doing so) I will probably throw up a brain dump of the type of questions I saw.

MySQL Architecture

  1. Use MySQL client programs to interface with the MySQL Server interactively and in batch
  2. Describe how MySQL uses disk and memory resources
  3. List and describe key characteristics of standard MySQL storage engines including InnoDB, NDB, MyISAM, MEMORY, FEDERATED

MySQL Server Installation, Configuration and Maintenance

  1. Select, deploy, start and stop MySQL using appropriate binary packages for Windows and Linux platforms
  2. Configure MySQL Server deployments using options files, command-line options and server variables
  3. Identify, configure, locate and describe appropriate use cases for MySQL the error, binary, general query and slow query log

MySQL Security

  1. Describe appropriate steps to secure a MySQL deployment at the operating system, filesystem and network levels
  2. Create and maintain user accounts with appropriate privileges and configuration
  3. Deploy and configure MySQL Enterprise Audit

Diagnostic Data and Metadata Sources in MySQL

  1. Configure and leverage PERFORMANCE_SCHEMA tables to identify and diagnose performance problems
  2. Obtain MySQL metadata from INFORMATION_SCHEMA tables

Optimizing MySQL Performance

  1. Demonstrate ability to diagnose and optimize poorly-performing queries
  2. Tune MySQL Server configuration for best performance
  3. Create and utilize table partitioning
  4. Apply best practices in optimizing schema objects
  5. Demonstrate understanding of locking concepts as applied to MySQL Server and storage engines

Backups and Recovery in MySQL

  1. Create and restore logical backups using mysqldump
  2. Create and restore binary backups
  3. Use backups for data recovery

High Availability Techniques for MySQL

  1. Describe, configure, deploy and troubleshoot MySQL replication
  2. Describe and contrast characteristics of common MySQL high availability solutions (MySQL Cluster, DRBD, Windows Failover Clustering, Solaris Cluster, OVM Template for MySQL Enterprise)

Install cacti 0.8.8a on CentOS 6.3

Of all of the tools I’ve been evaluating recently for server performance monitoring cacti has to be one of my favorites (mind you I loathe actual cacti, far too many spines have embedded themselves in my flesh…). Cacti is a RRDTool based graphing solution for server performance monitoring. This level of insight crucial for sysadmins once you get above a few servers or when you have that nagging performance problem that “occasionally” happens. It’s always nice to see what’s happening, but it’s even better to see the historical data so that you can use it to help give insight future needs. As well it is extremely flexible when it comes to custom metric monitoring. One of these days I’ll post up an install and configuration guide for Percona’s Cacti scripts but then again it did take me nearly a year to finish this post…

Installing Cacti 0.8.8a

Just as a general note before we begin the install process, yes I’m aware you could have used iptables instead of system-config-firewall and in fact it would be probably be better to use iptables. But everyone needs to remember how lazy I am when it comes to systems that should not be directly web-accessible (even though in about 2 weeks you’ll hear that people want access to it from the outside). Also I assume that you are working from a base installation of CentOS and know at least generally what you’re doing (for instance, if you have MySQL installed, you shouldn’t go about the process of installing mysql-server, starting it and doing the secure install).

Then go to Customize > WWW (HTTP) > Close > OK > Yes

Now update the config file to have the correct username and password to MySQL. As well you will need to uncomment $url_path and update it. The following are the lines you’re looking for:

Now we need to update cron.

Add the following to your crontab:

Now time to update the apache settings to allow cacti.

Add the following to the file.

Finally all you need to do is restart httpd.

Navigate browser to http://[ip of the server you installed it on]/~cacti
Then click Next > New Install > Next > Finish

Default user login: admin // admin

Also please, please… PLEASE change the default admin password.

Installing Percona Toolkit on CentOS 6.3 x86_64

As with the post on innotop, I will only be discussing the installation of Percona Toolkit and will do a more complete set of examples with each utility within the next few weeks. I suppose I should spend a bit of time discussing what exactly is Percona Toolkit. Well the kit consists of many advanced tools for doing common tasks such as verifying data consistency in replicated enviornments, query log aggregation, summerizing MySQL server settings, locating duplicate indices, table alterations without locking the entire table, and many more. A full list of each tool and what it does can be found on the Percona Toolkit v2.1 Documentation.

Installation

Installing innotop on CentOS 6.3 x86_64

Innotop (http://code.google.com/p/innotop/) has to be one of the best InnoDB monitoring I’ve become accustomed to while wearing a DBA hat. It allows you to get everything from what queries are currently running to the number of queries per second to monitoring deadlocks to even seeing the InnoDB I/O information. One of these days I plan on going fairly in-depth with the intricacies of monitoring with innotop, but as for today all you get is an installation guide with a quick breakdown of various errors and fixes during the install process.

Installation

Error Solutions

Migrating Unicode Data From MSSQL to MySQL

A few months back I ran into an issue with migrating Unicode characters from MSSQL to MySQL (okay… so maybe it was more than a year ago and I never really got around to writing anything, stop judging). Through much research and frustration, I found that MSSQL and MySQL do not support any of the same Unicode character sets. Thus it is nearly impossible to migrate any character that is not covered under the extended ASCII standard. Also go figure I couldn’t find any documentation on anyone running into this particular issue.

MSSQL has a fairly limited amount of character sets that it can export; you can export using the -C or -w flag while using bcp. By specifying the -C you will be able to export any of the character sets found in the Code Page Architecture (http://msdn.microsoft.com/en-us/library/ms186356.aspx). When you specify the -w flag MSSQL will export all data as UTF-16LE (http://en.wikipedia.org/wiki/UTF-16/UCS-2). Unfortunately MySQL’s native support for UTF-16 is limited to UTF-16BE. This inconsistency in the supported UTF-16 encoding by the two database engines renders a direct migration impossible for unicode characters.

After searching high and low for a boxed solution I came to realization that either I suck at googling (a definite possibility) or nothing existed. So like any true professional, I decided to roll my own half-assed solution using bcp, libiconv (http://gnuwin32.sourceforge.net/packages/libiconv.htm), and MySQL’s LOAD DATA INFILE.

Walk Through

First you will need to convert your table schema in MSSQL to a MySQL table. This should be pretty straight forward, but you will need to have a completely identical tables in both systems before you start the moving process.

Then we start out by dumping the information from MSSQL. During this dumping process I ended up having to use marker characters for special positions. I would love to say this is because there was some real reason behind it, but nope, I was just too inept to get it working any other way.

Then we will run the iconv conversion from MSSQL’s Unicode format to MySQL’s Unicode format.

After that we will load the information into MySQL using the markers we created during the dumping process in MSSQL.

Finally here is a completed script for the conversion process and some extra commands to clean up our temp data.

Utilizing Memcache with PHP

Today I will be demonstrating how to use the PHP memcache extension, as well as a possible use case for memcache. If you have not already installed memcached and the PHP memcache extension check out http://www.wolflabs.org/2008/12/07/memcached-php-memcache-extension-installation/.

Example Source

Here is the code that I will be using for this example. Please note that this script should be run from the cli on the same server memcached is currently running on.

Using the Memcache PHP Extension

In the example code above I have outlined how to connect, set data, and destroy data in memcached. Lets go step by step through the code, the first thing we need to do is create a new memcache class, if you get an error saying that the class does not exist that means that the PHP memcache extension is not installed. As well we will connect to the memcahe server, in my case it is my localhost on the default memcahe port.

Next we will try to see if our test data already exists in memcache in the ‘testData’ key, think of memcache as an associative array.

Now the data that is loaded for the ‘testData’ key should be in our variable. We are going to check to see if there was data in our ‘testData’ key. If the data exists then we will unset it, although if the data did not exist we will set it with the current UNIX time stamp. Also please note that we are using the MEMCACHE_COMPRESSED flag, the reason for this is that we can store more information in memory this way, but it does cause more strain on the server’s CPU since every time we put or get data it needs to be compressed or uncompressed. If you are unable to set/get any data from memcache make sure your php install was compiled with zlib support, memcache compression uses zlib, if it is not available the set or get will fail silently and you won’t get any data.

A Real World Example

A real world example for the use of memcache is user data for an application. Lets say that you have a really complicated ACL (access control list) that you use to set down fine grain permissions, these type of scripts can be very database intensive if you use normalization like I do, lets say that you are running 4 queries per user to figure out their permissions, that may not seem like a lot but you need to take into account all of the other read/writes that are happening in the database for every other user. You will strain the database with these queries that really do not need to be run on each page load. What I do for applications I work on is create a cache of the user’s permissions as well as their overall user data, and use a key that will be unique to each user, for example ‘USRD_’. In a perfect world this would work all the time and you would never have to reload user data once it is compiled, but this isn’t true since administrators could change user permissions on the fly. So my solution is to simply destroy the cached user data of all logged in users, or for a single user if permissions were changed for a single person, as well any time that a user changes their data, like email or name, the cache is destroyed and recreated on the next page load. Using caching techniques like this you should be able to lower the hit on the database server by a lot.

Something else that I would suggest that you should do is take a look at your slow query logs. If you see a particular query coming up a lot or generally taking a ton of system resources, ask yourself if it could be cached, remember memcache servers are cheap, database servers are not.

Installing PHP from source on CentOS x86_64 (w/ apache)

Installing PHP from source is much easier than most people think. In this tutorial I will describe how to install a bare PHP build with mysql/mysqli support in addition to configuring apache to interpret PHP scripts.

Compiling PHP Source

Alright, well in order to compile the php source code you must first have gcc install (# yum install gcc). Also if you want to be able to use PHP in apache then you need to have httpd and httpd-devel packages installed. Here is how I did my install. (Please note that I used PHP 5.2.6 for my install, but this will work with just about any php version, just be sure to untar and cd into the proper directory for your version of php.)

You’re also going to want to place a php.ini into /etc/php.ini and make the /etc/php.d directory if you have not done so already.

Installing PHP into apache

To install PHP into apache all you need to do is place the following configuration file in /etc/httpd/conf.d/php.conf.

Finalizing our install is fairly simple, just restart apache by typing the following command and you should be good to run PHP applications for the web.

Memcached + PHP Memcache Extension Installation

Reciently at work as well as in my own personal developement I have found a lot of emphasis on caching technologies. Be this for keeping heavy database queries to a minimum or because you wish to keep heavily served files out of the disk I/O. In this post (my first post!!) I will demonstrate how to install and configure memcached on a linux (CentOS to be specific) box. As well how to install the memcache extension into PHP.

Installing Memcached

Make sure before you start tying to install memcache that you have gcc (# yum install gcc) and libevent-devel (# yum install libevent-devel) installed.

Now if you are lazy like me and don’t want to have to remember what the memcached flags are every time you start it I would recommend using an init.d script. I recommend using the one located at http://www.dev411.com/wiki/Memcached_startup_files_for_Red_Hat_(RHEL). To install the init.d script on your server you need to place the start-memcached perl script in /usr/local/bin/start-memcached. Make sure you have given this file chmod 755 and is chown root:root. Then you must place the memcached-init into /etc/init.d/memcached, make sure this script has the same permissions as the previous file. Finally create the configuration file /etc/memcached.conf, you can place any options that you want ran by the memcached binary in this file. I use the following in my file.

Starting Memcached

Now you should be able to start and stop memcached quickly by using the following commands.

If you did not install the init.d script then you can start memcached with the following command.

Installing the Memcache PHP extension

Installing the memcache php module from source is fairly strait forward, it is just like installing any other php module. Please note that you must have zlib-devel and php-devel packages installed.

Now we need to tell php to include our memcache extension when it starts up. We do this by putting the following configuration data in /etc/php.d/memcache.ini

You will be able to verify that the memcache php extension has been installed by typing # php -m and looking for memcache in the php extension list.