Z-Car

MySQL – ORDER BY does not sort data properly…

sql girl in halter top

Imagine a developer working late at night trying to complete some simple queries for a report due the next day.  No matter what she does, the resulting query will just not sort properly. Is this some undiscovered MySQL bug?  No wonder this software is free, MySQL is broke and can’t even perform a basic sort.  It looks simple, sort the data in a table:

mysql> SELECT id, technology from Enigma;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

To sort the records based on the technology, do an ORDER BY on column Technology:

mysql> SELECT id, technology from Enigma ORDER BY Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

It seems that the server does not understand what I have asked, or I have not mastered the entire alphabet …
Let’s add the clause CSA (just in case!)

mysql> SELECT id, technology from Enigma ORDER BY ASC Technology;
+——+————-+
| Id | technology |
+——+————-+
| 1 | PHP |
| 2 | LINUX |
| 3 | MySQL |
+——+————-+

That does not seem to be any better!  MySQL does not sort the data as I wish, am I going crazy? Should I change my RDBMS?
Don’t worry, a careful look at the table structure allows us to see things clearly.

mysql> SHOW CREATE TABLE enigma;

Table: enigma
Create Table: CREATE TABLE `Enigma` (
`Id` int (11) DEFAULT NULL,
`Technology` enum (‘PHP’, ‘Linux’, ‘MySQL’) DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = latin1

The column technology is of a type ENUM.  The Enum field is stored as an integer, and that is the index that MySQL uses for sorting.  In other words, 1 is PHP, 3 is MySQL, and 2 is Linux, so sorting does works properly, just not as expected when the Enum data field is storing character data.  So how do you get the desired result?  Simply force MySQL to use the value string and not index when sorting.   This can be done by using either the Concat() or Cast() functions, as follows:

function concat ()
mysql> SELECT id, technology from Enigma ORDER BY concat (technology);
+——+————-+
| Id | technology |
+——+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+————-+
function cast ()
mysql> SELECT id, technology from Enigma ORDER BY cast (technology as char);
+——+————-+
| id | Technology |
+—– -+————-+
| 2 | LINUX |
| 3 | MySQL |
| 1 | PHP |
+——+———– – +


MySQL – Innodb Slow Shutdown – Dirty Buffer Pages

girl working on computer

If you have a MySQL server that reserves a large amount of memory for the Innodb buffer pool, you may have seen that it can often take a very long time to shutdown.  During the shutdown process, the server will not be able to serve any requests.  From my experience, I have seen that it can take 1-5 minutes per GB of buffer to perform a shutdown.  So, for example, if you have a server with 50G of memory allocated to the Innodb Buffer Pool, and 50% of the buffer is dirty (unwritten to disk), it can take anywhere from 25-125 minutes to shutdown.  Multiple hour shutdowns on large, busy Innodb MySQL servers is not unheard of.

Luckily, there is a fairly easy way to speed up shutdown while still allowing the server to stay up and handle requests.  The secret is to lower the maximum percentage of dirty pages allowed.  This is controlled by the global variable innodb_max_dirty_pages_pct. On my servers I usually have this set at 75%. This variable can be changed at run-time, which allows us to modify this variable to speed up system shutdown.

First, enter the command :

set global innodb_max_dirty_pages_pct=0;

You can then enter the following to verify the new value:

show global variables where variable_name rlike ‘dirty’;

To monitor the number of dirty pages that have not been written to disk, enter the following:

show global status where variable_name rlike ‘dirty’;

You will see this number begin to drop, although it is unlikely to ever reach 0, once it drops to a low level and reaches a plateau, you can then shutdown the server. You will find that the server will shutdown very quickly, allowing you to minimize downtime.


Top My.CNF Optimizations for Innodb

worried-girl-computer

These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb.   If you are suffering from poor performance, try changing the following settings.  Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks.  The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL.  If you need help optimizing your queries, let us know.

innodb_buffer_pool_size =8G
Set this to ~80% of free server memory.  For example, if you have a dedicated MySQL server with 10G, set to 8G

innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure

innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement.  However, this can actually have a negative affect, so make sure you test appropriately.

MySQL – DateTime vs. TimeStamp – When to use?

pretty victoria has a secret

This question often comes up, and while there is often a heated debate, these are my thoughts on the subject.

I typically use a timestamp when I need to record a fixed point in time. For example when a record was inserted into the database or when some useraction took place that resulted in a row being updated.   The Timestamp data type has various features that allow it to automatically perform this function.   The default value for the Timestamp data type results in it being set to the current_date when a new row is added.  If you as the On Update syntax, this value will also be updated whenever the row is updated.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when the field can be updated based on specific events or actions, besides a simple row Update.  In addition, a TimeStamp field can only store dates since 1970, so if you need to store dates in the past, such as a birthday, you must use DateTime.

A couple other things to keep in mind, TimeStamp fields support the TimeZone setting on your server.  For example, if I have a database in Europe, and take a dump of that database to syncronize/populate a database in America, then the timestamp would update to reflect the real time of the event in the new time zone, while datetime would still reflect the time of the event in the European timezone.  By default, the current time zone for each connection is the server’s time, however the time zone can be set on a per-connection basis.

Lastly, TIMESTAMP stores its value in 4 bytes, while DATETIME uses 8 bytes.  This is the main reason why TimeStamp has a lower limit of 1970.