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:

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

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

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

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

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

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

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.

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.

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.

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.

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.

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.

One of the tools that is included with Maatkit is called ‘mk-query-digest’.  If you have Mysql slow logging enabled, this perl script can me used to examine these entries, and provide a very useful and user friendly report that will help you understand the queries that are exceeding your current slow log threshold. You can then take the results and determine whether or not you have opportunity to do some schema and/or query optimization.

In order to get the ball rolling all you need to do is execute the following command:

‘perl /path/to/mk-query-digest /path/to/slow.log’

Once you do you will be presented with a report similar to the following:

# 200ms user time, 10ms system time, 9.59M rss, 11.17M vsz
# Overall: 199 total, 8 unique, 0.92 QPS, 3.44x concurrency ______________
#                    total     min     max     avg     95%  stddev  median
# Exec time           745s      1s     15s      4s      9s      3s      3s
# Lock time            4ms       0   159us    21us    66us    18us    16us
# Rows sent         16.35M       0 381.59k  84.16k 298.06k  81.83k  76.03k
# Rows exam         16.35M       0 381.59k  84.16k 298.06k  81.83k  76.03k
# Time range        2010-03-04 18:32:44 to 2010-03-04 18:36:21
# bytes             55.53k       6   7.67k  285.75  313.99  519.23  271.23

Continue Reading ->

Maatkit is a group of perl scripts that provide advanced methods of administration for Mysql.

Here are a few of the more useful things that Maatkit allows you do to:

1. Monitor MySQL replication delay
2. Make a MySQL slave server lag behind its master.
3. Ensuring slaves have the same data as masters
4. Find duplicate indexes and foreign keys on MySQL tables.
5. Execute SQL statements and print statistics.
6. Execute queries on multiple servers and check for differences.

Here is the link to the Maatkit website which provides more details on each of the scripts.  I will be providing some real world examples of Maatkit usage in another post.

Percona has recently released some of their performance patches for Mysql 5.1.  Previous to these patches were only available for the 5.0.x codebase.

You can read more about it here. The link also provides from interesting benchmarks as well, pitting these patches vs the standard storage engine setup.

To be clear, these patches work against the built in version of Innodb with comes standard with the Mysql server.

You can xtradb if you wish to use some of the Percona patches that pertain to the Innodb plugin that is offered by Innobase