Opinions, Expertise, Passion.

I will be speaking on July 29th in New York at an Entrepreneurs Forum on A Free Panel on Cloud Computing. With a number of experts including Hank Williams of KloudShare, Mike Nolet of AppNexus, and Hans Zaunere of New York PHP fame is should be a great event.

The focus of my presentation will be on “Extending existing applications to leverage the cloud” where I will be discussing both the advantages of the cloud, and the complexities and issues that you will encounter such as data management, data consistency, loss of control, security and latency for example.

Using traditional MySQL based applications I’ll be providing an approach that can lead to your application gaining greater power of cloud computing.

---

About the Author

Ronald Bradford provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.

I have seen on many engagements the column data type is defined as INT(1).

People have the misconception that this numeric integer data type is of the length of one digit, or one byte. (One digit is 0-9 an one byte is 0-255)

This is incorrect.

Integer

For integer numeric data types in MySQL, that is TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT the (n) has no bearing on the size of data stored within the specific data type. The (n) is simply for display formatting.

In the MySQL Manual 10.2. Numeric Types you read This optional display width is used to display integer values having a width less than the width specified for the column by left-padding them with spaces. 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.

The following example shows the (n) in this case 3 has no effect on the size of data stored.

DROP TABLE IF EXISTS numeric_int;
CREATE TABLE numeric_int(i INT(3) NOT NULL);
INSERT INTO numeric_int VALUES (1),(22),(333),(444),(55555);
SELECT * FROM numeric_int\G
i: 1
i: 22
i: 333
i: 444
i: 55555

Floating Point

When it comes to floating point precision of FLOAT and DOUBLE, the syntax of (m,n) has a different inteperation. The manual states A precision from 0 to 23 results in a four-byte single-precision FLOAT column. A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column.
I will discuss this some more in a different post with some interesting findings.

And MySQL allows a non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

So in the case of FLOAT,DOUBLE the (n) does both affect storage and presentation where it rounds the number as confirmed by the following test. Look a the last 2 rows for the rounding confirmation.

DROP TABLE IF EXISTS numeric_float;
CREATE TABLE numeric_float(f1 FLOAT(10,5)  NOT NULL);
INSERT INTO numeric_float values (1),(2.0),(3.12345),(4.123451),(5.123456);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
SELECT * FROM numeric_float\G
f1: 1.00000
f1: 2.00000
f1: 3.12345
f1: 4.12345
f1: 5.12346
5 rows in set (0.01 sec)

Fixed Precision

The DECIMAL data type (NUMBER is a synonym) stores numbers to a fixed number of precision. From the manual again When declaring a DECIMAL or NUMERIC column, the precision and scale can be (and usually is) specified; for example: salary DECIMAL(5,2)
In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point. If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

So in our test:

DROP TABLE IF EXISTS numeric_decimal;
CREATE TABLE numeric_decimal(f1 DECIMAL(10,5)  NOT NULL);
INSERT INTO numeric_decimal values (1),(2.0),(3.12345),(4.123451),(5.123456);
Query OK, 5 rows affected, 2 warnings (0.00 sec)
SELECT * FROM numeric_decimal\G
f1: 1.00000
f1: 2.00000
f1: 3.12345
f1: 4.12345
f1: 5.12346

What is also interesting is that with a FLOAT, the rounding of a number greater then (n), produces no warnings, yet when using DECIMAL you will see warnings. These are:

INSERT INTO numeric_decimal values (1),(2.0),(3.12345),(4.123451),(5.123456);
Query OK, 5 rows affected, 2 warnings (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 2

mysql> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'f1' at row 4 |
| Note  | 1265 | Data truncated for column 'f1' at row 5 |
+-------+------+-----------------------------------------+
2 rows in set (0.00 sec)

What is also interesting is that the manual states the following When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the allowable number of digits.)

The number is generally truncated, buy differs per OS. In the case on Mac O/S and Linux it is rounded. The two test environments in this case where:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.23-rc                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | i686                         |
| version_compile_os      | apple-darwin9.0.0b5          |
+-------------------------+------------------------------+
5 rows in set (0.01 sec)

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.24-rc                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | i686                         |
| version_compile_os      | redhat-linux-gnu             |
+-------------------------+------------------------------+
5 rows in set (0.41 sec)

Conclusion

So just to conclude, (n) for Integer types is for display formatting only, (m,n) for floating point will round the number at n places, while in fixed point (m,n) n will round or truncate the number.

---

About the Author

Ronald Bradford provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.

References

• 10.2. Numeric Types
• Top 20 Design Tips for Data Architects - Apr 2008

Recently I was asked to provide guidelines for testing fail over of a MySQL configuration that was provided by a hosting provider.

The first observation was the client didn’t have any technical details from the hosting provider of what the moving parts were, and also didn’t have any confirmation other then I think a verbal confirmation that it had been testing.

The first rule in using hosting, never assume. Too many times I’ve seen details from a client stating for example H/W configuration, only to audit and find out otherwise. RAID is a big one, and is generally far more complex to determine. Even for companies with internal systems I’ve seen the most simple question go unanswered. Q: How do you know your RAID is fully operational? A: Somebody will tell us? It’s really amazing to investigate on site with the client to find that RAID system is running in a degraded mode due to a disk failure and nobody knew.

It took some more digging to realize the configuration in question was with Red Hat Cluster Suite. A word of warning for any clients that use this, DO NOT USE MyISAM. I’ll leave it to the readers to ask me why.

Here is a short list I provided as the minimum requirements I’d test just to ensure the configuration was operational.

Verifying a working Red Hat Cluster Suite MySQL Environment

The MySQL Environment

The database environment consists of two MySQL database servers, configured in an active/passive mode using a shared disk storage via SAN.
For the purposes of the following procedures the active server will be known as the ‘primary’ server, and the passive server will be the ’secondary server’.
The two physical servers for the purposes of these tests will be defined as ‘alpha’ and ‘beta’, with specific H/W that does not change during these tests.

Normal Operations

Expected Configuration under normal operations.

Primary Server

• server is pingable
• server accepts SSH Connection
• MySQL service is started
• has /data appropriately mounted
• has assigned VIP address
• MySQL configuration file and settings are correct

Secondary Server

• server is pingable
• server accepts SSH Connection
• MySQL service IS NOT started
• DOES NOT have /data mounted
• DOES NOT has assigned VIP address
• MySQL configuration file is not available

1. Reboot servers ‘alpha’ and ‘beta’.

Test Status:

• alpha server is the designated primary server
• alpha and beta servers are operational

Action:
1.1 Restart alpha server (init 6)
1.2 Restart beta server (init 6)

Checklist:
1.3 Alpha server matches primary server configuration
1.4 Beta server matches secondary server configuration

2. Controlled fail over from ‘alpha’ to ‘beta’

Test Status:

• alpha server is the designated primary server
• alpha and beta servers are operational

Action:
2.1 Alpha server - Instigate Cluster failover (clusvcadm -r mysql-svc)

Checklist:
2.2 Beta server matches primary server configuration
2.3 Alpha server matches secondary server configuration

3. Controlled failover from ‘beta’ to ‘alpha’

Test Status:

• beta server is the designated primary server
• alpha and beta servers are operational

Action:
3.1 beta server - Instigate Cluster failover (clusvcadm -r mysql-svc)

Checklist:
3.2 Alpha server matches primary server configuration
3.3 Beta server matches secondary server configuration

Exception Operations

4. Loss of connectivity to primary server

Test Status:

• alpha server is the designated primary server
• beta server is online

Action:
4.1 Stop networking services on ‘alpha’ (ifdown bond0)

Checklist:
4.2 Monitoring detects and reports connectively loss
4.3 Automated failover occurs
4.4 Beta server matches primary server configuration
4.5 Alpha server matches secondary server configuration

5. Restore connectivity to secondary server

Test Status:

• beta server is the designated primary server
• alpha server is online, but not accessible via private IP

Action:
4.1 Start networking services on ‘alpha’ (ifup bond0)

Checklist:
5.2 Monitoring detects and reports connectively restored
5.3 No failback occurs
5.4 Beta server matches primary server configuration
5.5 Alpha server matches secondary server configuration

6. Loss of connectivity to secondary server

Test Status:

• beta server is the designated primary server
• alpha server is online

Action:
6.1 Stop networking services on ‘alpha’ (ifdown bond0)

Checklist:
6.2 Monitoring detects and reports connectively lost
6.3 No failback occurs
6.4 Beta server matches primary server configuration
6.5 Alpha server matches secondary server configuration

7. Restore connectivity to secondary server

Test Status:

• beta server is the designated primary server
• alpha server is online, but not accessible via private IP

Action:
7.1 Start networking services on ‘alpha’ (ifup bond0)

Checklist:
7.2 Monitoring detects and reports connectively restored
7.3 No failback occurs
7.4 Beta server matches primary server configuration
7.5 Alpha server matches secondary server configuration

8. Power down secondary server

Test Status:

• beta server is the designated primary server
• alpha server is online

Action:
8.1 Power down alpha (init 0) NOTE: Need remote boot capabilities

Checklist:
8.2 Monitoring detects and reports connectively lost
8.3 Beta server matches primary server configuration
8.4 Additional paging for extended down time for ‘degraded support for failover’

9. Loss of connectivity to primary server

Test Status:

• beta server is the designated primary server
• alpha server is offline

Action:
9.1 Power down beta (init 0) NOTE: Need remote boot capabilities

Checklist:
9.2 Monitoring detects and reports connectively lost
9.3 Site database connectively completely unavailable
9.4 Additional paging for loss of HA solution

10. power restored to secondary server
Test Status:

• alpha server is offline
• beta server is offline

Action:
10.1 Power on alpha

Checklist:
10.2 Monitoring detects and reports server up
10.3 Alpha server assumes primary role (previously it was beta)
10.4 Alpha server matching primary server configuration
10.5 Addition paging for degraded HA

11. power restored to secondary server

Test Status:

• alpha server is primary server
• beta server is offline

Action:
11.1 Power on beta

Checklist:
11.2 Monitoring detects and reports server up
11.3 Alpha server matching primary server configuration
11.4 Beta server matching secondary server configuration

Database Operations

12. MySQL services on primary server go offline

Test Status:

• alpha server is the designated primary server
• beta server is online

Action:
12.1 Stop mysql services on ‘alpha’ (/etc/init.d/mysqld stop)

Checklist:
12.2 Monitoring detects and reports database loss (while connectivity is still available)
12.3 Automated failover occurs
12.4 Beta server matches primary server configuration
12.5 Alpha server matches secondary server configuration

13. MySQL services on secondary server go offline

Test Status:

• beta server is the designated primary server
• alpha server is online

Action:
13.1 stop mysql services on ‘beta’ (/etc/init.d/mysqld stop)

Checklist:
13.2 Monitoring detects and reports database loss (while connectivity is still available)
13.3 Automated failover occurs
13.4 Alpha server matches primary server configuration
13.5 beta server matches secondary server configuration

14. Load Testing during failure

Test Status:

• alpha server is the designated primary server
• beta server is online

Action:
14.1 Agressive load testing against database server
14.2 MySQL killed without prejudice (killall -9 mysqld_safe mysql)

Checklist:
14.3 Monitoring detects and reports mysql service loss
14.4 Automated failover occurs
14.5 Beta server matches primary server configuration
14.6 Alpha server matches secondary server configuration
14.7 Beta mysql logs shows a forced MySQL Recovery in logs

15. Forced Recovery

Test Status:

• alpha server is the designated primary server
• beta server is online

Action:
15.1 Manual full database backup is done (in case recovery does not work). Hosting Provider not told of this.
15.2 Dummy new table/schema is created (used as verification point)
15.3 Database on alpha primary server is dropped
15.4 Hosting Provider is notified stating a full database recovery including Point In time to just before drop (no time given, only command that was run)

Checklist:
15.5 Site is marked as unavailable
15.6 Hosting Provider restore data from backup and recover to point in time
15.7 Confirmation that new table/schema is restored, and full schema is available
15.8 Site is made available
15.9 Record of time for full disaster is recorded

Conclusion

This is not an exhaustive test, in fact it is just a documented approach for consideration to show a client what the minimum testing should be. As no dry run actually occurred, there may be inaccuracies and additions necessary to this document when first executed. I would need access to an appropriate configuration in order to perform a level of testing to complete this document.

---

About the Author

Ronald Bradford provides Consulting and Advisory Services in Data Architecture, Performance and Scalability for MySQL Solutions. An IT industry professional for two decades with extensive database experience in MySQL, Oracle and Ingres his expertise covers data architecture, software development, migration, performance analysis and production system implementations. His knowledge from 10 years of consulting across many industry sectors, technologies and countries has provided unique insight into being able to provide solutions to problems. For more information Contact Ronald.