Alex Stetsenko

The fact that Twitter and Digg are replacing MySQL by Cassandra have become very popular.

However for me it was also interesting to find out how Twitter engineers had conducted their research and what other solutions had considered.

For example, there are questions to evaluate potential tool:

• How will we add new machines?
• Are their any single points of failure?
• Do the writes scale as well?
• How much administration will the system require?
• If its open source, is there a healthy community?
• How much time and effort would we have to expend to deploy and integrate it?
• Does it use technology which we know we can work with?… and so on.

And they had to check various databases: HBase, Voldemort, MongoDB, MemcacheDB, Redis, Cassandra, HyperTable.

Another interesting fact is that Twitter guys actually test on production.
For instance, to roll out the new data store they:

1. Write code that can write to Cassandra in parallel to MySQL, but keep it disabled
2. Slowly turn up the writes to Cassandra (it can be done by user groups like “turn this feature on for employees only” or by percentages “turn this feature on for 1.2% of users”)
3. Find a bug 🙂
4. Turn the feature off
5. Fix the bug and deploy
6. GOTO #2

Eventually 100% doubling of writes are being done.

Read more details in the interview where Twitter Storage Team Lead Ryan King talks about the reasons for the switch and how it is planned to migrate tweets from MySQL to Cassandra.

Mikiya Okuno has released a simple “MySQL Cheat Sheet” recently. It is designed for those who newly starts using MySQL or uses it occasionally. It fits one page of A4 size perfectly.

You can download it from here: http://www.mysqlpracticewiki.com/files/cheat-sheet-en.pdf.

The license is CC-BY-SA, i.e. redistribution and modifications are allowed under CC license.

In addition to SHOW TABLE STATUS there are at least two more ways to find out a table storage engine.

The first way is simply to query the INFORMATION_SCHEMA.TABLES.

SELECT `table_name`, `engine`
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = DATABASE();

In case there is not permission to access INFORMATION_SCHEMA the second way requires only the SELECT privilege for the table.

SHOW CREATE TABLE TestTable;

A while ago I was involved in the technical part of an interview with a PHP developer. Obviously some questions were about MySQL. For instance – how to add a new column to existing table. The candidate tried to convince us that there was no way to do that except creating another table with new column and copy data from old table there. Sure thing that we advised him to read about ALTER command.

However it turned out that suggested ‘method’ could have sense. According to Chris at Everything MySQL blog a full dump and reload to a table with new column may be much more faster than alter table! For instance there is shown a test with 5Gb table and alter table is 47% slower. Of course such ‘alternative alter’ should be used only against slave as well as some other restrictions may apply.

Below is the suggested process.

1. If you have a slave, AND YOU SHOULD AT THIS POINT, run stop slave
2. Run the SELECT INTO OUTFILE local, if you have the space, or over nfs (not very fast)
3. DROP or RENAME THE TABLE you are trying to ALTER
— RENAME only if you have the space and you don’t trust THE PROCESS!
4. CREATE the same table with the ALTERATIONS, if you dropped, or a new table with the ALTERATIONS
5. IMPORT the file from step 2
6. Either RENAME the two tables, if you renamed, or start slave
7. Fail over the writes to the newly altered slave
8. Repeat steps 1 – 7 for the old master

SQL Antipatterns is Bill Karwin’s presentation which not only covers the most common mistakes in database designing but also suggests several ways to solve them.

There might be many ways to force leading zeros to be displayed when string length is less than expected.

One of them is to use string function LPAD(). For example, the following query:

SELECT LPAD('1101', 8, '0');

returns ‘00001101’.

However, if string (first parameter) is longer than length (second parameter), the return value is shortened. For example:

SELECT LPAD('1101', 2, '0');

returns ’11’.

Sometimes junior database developers are being confused by field types like INT(2). Does it mean 2 bytes or 2 digits only?

However it is just optional field width which in conjunction with the optional extension attribute ZEROFILL allows to replace the default padding of spaces with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004.

Therefore the display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

But according to MySQL reference manual there is a possibility of problems when MySQL generates temporary tables for some complicated joins and larger values than the display width are stored in an integer column, because in these cases MySQL assumes that the data fits into the original column width.

Time zone support is a typical part of many web applications. But not every application takes into account daylight savings time when +1 is added to hour difference during summer time only.

When PHP allows to identify DST simply by calling something like:

print date('I', strtotime('2009-03-11 00:00:00'));

it may not be obvious that:

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'EST');

and

SELECT CONVERT_TZ('2009-03-11 00:00:00', 'GMT', 'US/Eastern');

return different results:

• ‘2009-03-11 05:00:00’ in case of ‘EST’ and
• ‘2009-03-11 04:00:00’ in case of ‘US/Eastern’.

This article explains how to make work CONVERT_TZ() function (which returns NULL by default) and stay current with time zone changes.

P.S.: There are some time zones with minute adjustment like UTC+5:45 or UTC+9:30 as well as with difference more than 12 hours from GMT like UTC+13 or UTC+14.

Now MySQL allows executing tasks that run according to a schedule:

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

Here is a sample SQL to create an event:

CREATE EVENT e_hourly
    ON SCHEDULE 
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

Read more in MySQL reference manual: 19.4. Using the Event Scheduler as well as how this feature was originally designed by Andrey Hristov when he was working on his master’s thesis.

I’ve already written a post about MySQL GUI Tool for Windows.

In case you need to manage or support MySQL database on Mac OS X, Sequel Pro may be a good option. However it does not support stored routines manipulation. But hopefully it will be fixed in the next release.