The Ops Benefit of the Cloud

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, see drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, order so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, treat and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

In MySQL 5.6, sickness it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, pills using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1025
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: index_subquery
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: func
rows: 1
Extra: Using index

MariaDB 5.5 boasts subquery optimization, and rightfully so. It looks like MariaDB materializes the subquery:

MariaDB [sakila]> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1043
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table:
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 2
ref: func
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: film_actor
type: index
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 4889
Extra: Using index

So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery. There are 3 rows here, a new <subquery2> table is used to help optimize the results.

In MySQL 5.6, the subquery is actually optimized away. The optimizer actually changes it. You can see this in the EXPLAIN plan by looking at the select_type. In both the MySQL 5.1 and MariaDB 5.5 examples, the select_type is PRIMARY, which is used for the outer query in a subquery (or the first SELECT in a UNION, but that does not apply here). In MySQL 5.6, the select_type is SIMPLE for both rows. Note that MySQL 5.6 also does not have to add a third table as MariaDB does:

mysql> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

In the presentation, the Oracle team says that for DBT3 Query #18, “execution time reduces from days to seconds”. With optimizations like this, I believe it!

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

In MySQL 5.6, sickness it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, pills using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1025
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: index_subquery
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: func
rows: 1
Extra: Using index

MariaDB 5.5 boasts subquery optimization, and rightfully so. It looks like MariaDB materializes the subquery:

MariaDB [sakila]> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1043
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table:
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 2
ref: func
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: film_actor
type: index
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 4889
Extra: Using index

So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery. There are 3 rows here, a new <subquery2> table is used to help optimize the results.

In MySQL 5.6, the subquery is actually optimized away. The optimizer actually changes it. You can see this in the EXPLAIN plan by looking at the select_type. In both the MySQL 5.1 and MariaDB 5.5 examples, the select_type is PRIMARY, which is used for the outer query in a subquery (or the first SELECT in a UNION, but that does not apply here). In MySQL 5.6, the select_type is SIMPLE for both rows. Note that MySQL 5.6 also does not have to add a third table as MariaDB does:

mysql> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

In the presentation, the Oracle team says that for DBT3 Query #18, “execution time reduces from days to seconds”. With optimizations like this, I believe it!

Today and tomorrow I am at CodeConnexx – An Open Source Technology and Life Conference. There are some great talks…the first talk this morning is Secrets and Success in the Style of GLEE – a bunch of songs and how they relate to being successful. By Jennifer Marsman of Microsoft.

Taylor Swift, cialis 40mg if you have an idea, psychotherapist do not be shy about it. In an interview, “don’t stop talking” – meaning show them your passion – but don’t force it, of course. Ask lots of questions, do not make assumptions. And if you get stuck in a problem, you can reason your way through it by talking out log.

Bonnie Raitt – “Let’s Give Them Something To Talk About”. Communicate! Let your manager know what’s going on and what you are doing. “Give them stories to tell about you” – and good ones too! Trip reports if you go on a trip, summary status e-mails, one-on-one meetings, etc.

Aretha Franklin – “Respect”. At the end of the day, diversity of opinion, education levels, backgrounds, is key to having successful business ideas. You can learn something from everyone. You can go around at a party, meet people and figure out what they are better than you at.

Frank Sinatra – “Luck Be a Lady”. At some other conference, Jennifer saw a formula for success: Success = hard work + intelligence + luck. She did not like that, because luck is a bunch of randomness, and if we work hard and are smart, we should be successful, right? But it’s completely true that luck is part of the equation. For example, in an interview. You can control working hard and you can control learning, and you will be well-positioned for when opportunity knocks.

Bette Middler – “Wind Beneath My Wings”. Role models – people that you worship from afar, perhaps stalk on Twitter, but probably won’t have a relationship with. Mentors are folks that are actively helping you grow, which you have a relationship with. You can choose a mentor to help you work on a skill or set of skills, and you can choose different people based on what they are good at. Someone who is good at MySQL might not be good at blogging or work/life balance. Jennifer challenges all of us to be role models to other people by speaking and blogging, because those folks are seen as industry experts, so you will become a role model.

Brittany Spears – “Oops I Did It Again”. The importance of making mistakes – making mistakes is good. Take big risks, because when a mistake does happen, you can learn from them. When a panel of successful tech women were asked what they would do differently, they said they would have taking more big risks.

Bill Withers – “Lean on Me”. Delegate if you need to. Ask people for help. Out of time, health and money, you can have any 2 of the 3. Young folks usually have time and health but not money. In your 30′s, you might have health and money but not time. And when you get older, you have time and money, but not health. Optimize for what you do not have. If you do not have time, then make it so you have more time – e.g. buying pre-made salad or getting a cleaning lady is a money/time tradeoff.

Journey – “Don’t Stop Believing”. Believe in yourself. Imposter Syndrome at Wikipedia. If you do not know, say you do not know something.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

In MySQL 5.6, sickness it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, pills using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1025
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: index_subquery
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: func
rows: 1
Extra: Using index

MariaDB 5.5 boasts subquery optimization, and rightfully so. It looks like MariaDB materializes the subquery:

MariaDB [sakila]> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1043
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table:
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 2
ref: func
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: film_actor
type: index
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 4889
Extra: Using index

So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery. There are 3 rows here, a new <subquery2> table is used to help optimize the results.

In MySQL 5.6, the subquery is actually optimized away. The optimizer actually changes it. You can see this in the EXPLAIN plan by looking at the select_type. In both the MySQL 5.1 and MariaDB 5.5 examples, the select_type is PRIMARY, which is used for the outer query in a subquery (or the first SELECT in a UNION, but that does not apply here). In MySQL 5.6, the select_type is SIMPLE for both rows. Note that MySQL 5.6 also does not have to add a third table as MariaDB does:

mysql> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

In the presentation, the Oracle team says that for DBT3 Query #18, “execution time reduces from days to seconds”. With optimizations like this, I believe it!

Today and tomorrow I am at CodeConnexx – An Open Source Technology and Life Conference. There are some great talks…the first talk this morning is Secrets and Success in the Style of GLEE – a bunch of songs and how they relate to being successful. By Jennifer Marsman of Microsoft.

Taylor Swift, cialis 40mg if you have an idea, psychotherapist do not be shy about it. In an interview, “don’t stop talking” – meaning show them your passion – but don’t force it, of course. Ask lots of questions, do not make assumptions. And if you get stuck in a problem, you can reason your way through it by talking out log.

Bonnie Raitt – “Let’s Give Them Something To Talk About”. Communicate! Let your manager know what’s going on and what you are doing. “Give them stories to tell about you” – and good ones too! Trip reports if you go on a trip, summary status e-mails, one-on-one meetings, etc.

Aretha Franklin – “Respect”. At the end of the day, diversity of opinion, education levels, backgrounds, is key to having successful business ideas. You can learn something from everyone. You can go around at a party, meet people and figure out what they are better than you at.

Frank Sinatra – “Luck Be a Lady”. At some other conference, Jennifer saw a formula for success: Success = hard work + intelligence + luck. She did not like that, because luck is a bunch of randomness, and if we work hard and are smart, we should be successful, right? But it’s completely true that luck is part of the equation. For example, in an interview. You can control working hard and you can control learning, and you will be well-positioned for when opportunity knocks.

Bette Middler – “Wind Beneath My Wings”. Role models – people that you worship from afar, perhaps stalk on Twitter, but probably won’t have a relationship with. Mentors are folks that are actively helping you grow, which you have a relationship with. You can choose a mentor to help you work on a skill or set of skills, and you can choose different people based on what they are good at. Someone who is good at MySQL might not be good at blogging or work/life balance. Jennifer challenges all of us to be role models to other people by speaking and blogging, because those folks are seen as industry experts, so you will become a role model.

Brittany Spears – “Oops I Did It Again”. The importance of making mistakes – making mistakes is good. Take big risks, because when a mistake does happen, you can learn from them. When a panel of successful tech women were asked what they would do differently, they said they would have taking more big risks.

Bill Withers – “Lean on Me”. Delegate if you need to. Ask people for help. Out of time, health and money, you can have any 2 of the 3. Young folks usually have time and health but not money. In your 30′s, you might have health and money but not time. And when you get older, you have time and money, but not health. Optimize for what you do not have. If you do not have time, then make it so you have more time – e.g. buying pre-made salad or getting a cleaning lady is a money/time tradeoff.

Journey – “Don’t Stop Believing”. Believe in yourself. Imposter Syndrome at Wikipedia. If you do not know, say you do not know something.

As many folks know, information pills !

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

In MySQL 5.6, sickness it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, pills using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1025
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: index_subquery
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: func
rows: 1
Extra: Using index

MariaDB 5.5 boasts subquery optimization, and rightfully so. It looks like MariaDB materializes the subquery:

MariaDB [sakila]> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1043
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table:
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 2
ref: func
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: film_actor
type: index
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 4889
Extra: Using index

So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery. There are 3 rows here, a new <subquery2> table is used to help optimize the results.

In MySQL 5.6, the subquery is actually optimized away. The optimizer actually changes it. You can see this in the EXPLAIN plan by looking at the select_type. In both the MySQL 5.1 and MariaDB 5.5 examples, the select_type is PRIMARY, which is used for the outer query in a subquery (or the first SELECT in a UNION, but that does not apply here). In MySQL 5.6, the select_type is SIMPLE for both rows. Note that MySQL 5.6 also does not have to add a third table as MariaDB does:

mysql> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

In the presentation, the Oracle team says that for DBT3 Query #18, “execution time reduces from days to seconds”. With optimizations like this, I believe it!

Today and tomorrow I am at CodeConnexx – An Open Source Technology and Life Conference. There are some great talks…the first talk this morning is Secrets and Success in the Style of GLEE – a bunch of songs and how they relate to being successful. By Jennifer Marsman of Microsoft.

Taylor Swift, cialis 40mg if you have an idea, psychotherapist do not be shy about it. In an interview, “don’t stop talking” – meaning show them your passion – but don’t force it, of course. Ask lots of questions, do not make assumptions. And if you get stuck in a problem, you can reason your way through it by talking out log.

Bonnie Raitt – “Let’s Give Them Something To Talk About”. Communicate! Let your manager know what’s going on and what you are doing. “Give them stories to tell about you” – and good ones too! Trip reports if you go on a trip, summary status e-mails, one-on-one meetings, etc.

Aretha Franklin – “Respect”. At the end of the day, diversity of opinion, education levels, backgrounds, is key to having successful business ideas. You can learn something from everyone. You can go around at a party, meet people and figure out what they are better than you at.

Frank Sinatra – “Luck Be a Lady”. At some other conference, Jennifer saw a formula for success: Success = hard work + intelligence + luck. She did not like that, because luck is a bunch of randomness, and if we work hard and are smart, we should be successful, right? But it’s completely true that luck is part of the equation. For example, in an interview. You can control working hard and you can control learning, and you will be well-positioned for when opportunity knocks.

Bette Middler – “Wind Beneath My Wings”. Role models – people that you worship from afar, perhaps stalk on Twitter, but probably won’t have a relationship with. Mentors are folks that are actively helping you grow, which you have a relationship with. You can choose a mentor to help you work on a skill or set of skills, and you can choose different people based on what they are good at. Someone who is good at MySQL might not be good at blogging or work/life balance. Jennifer challenges all of us to be role models to other people by speaking and blogging, because those folks are seen as industry experts, so you will become a role model.

Brittany Spears – “Oops I Did It Again”. The importance of making mistakes – making mistakes is good. Take big risks, because when a mistake does happen, you can learn from them. When a panel of successful tech women were asked what they would do differently, they said they would have taking more big risks.

Bill Withers – “Lean on Me”. Delegate if you need to. Ask people for help. Out of time, health and money, you can have any 2 of the 3. Young folks usually have time and health but not money. In your 30′s, you might have health and money but not time. And when you get older, you have time and money, but not health. Optimize for what you do not have. If you do not have time, then make it so you have more time – e.g. buying pre-made salad or getting a cleaning lady is a money/time tradeoff.

Journey – “Don’t Stop Believing”. Believe in yourself. Imposter Syndrome at Wikipedia. If you do not know, say you do not know something.

As many folks know, information pills !

This week was a great catch-up week. There is only one conference I am doing for the rest of the year, medicine CodeConnexx, sovaldi sale though submissions for conferences in the first quarter of 2013 are happening, too. We have some great candidates we are interviewing for our open Database Administrator position.

The database team has gotten a lot of great stuff done this week (I know, I say that all the time, but it’s true!):

  • Audited and got rid of legacy MyISAM tables in our Addons database.
  • Decommissioned an old MySQL cluster that has not been in use for a while. It is the database that used to back the predecessor to Mozillians.
  • Moved half our backups in one data center to another machine in the same data center, as they were on a netapp that’s having problems with being overloaded. Our Storage Team is talking to NetApp, but for now we alleviated some of the problems by moving the backups to another head. We also opened the process to get hardware allocated so our backups aren’t using NFS.
  • We took the move as an opportunity to puppetize the backup servers. Now all the backup scripts and backup instances are puppetized, with just a few more challenging items remaining: config files and startup scripts for each backup instance.
  • We have enabled a Nagios check for pt-config-diff so that we will be alerted (by e-mail only) when a running configuration on MySQL is different from the configuration file.
  • Fixed automatic slow query log copying from Addons to a machine our web developers use.
  • The entire IT team is working on documenting our Nagios checks – specifically, what to do for each Nagios check so our oncall folks can handle more problems before we have to be called in. We have documented 6 checks so far.
  • Fixed a fascinating problem in which ulimits we put in place were not being read by Percona, when we upgraded from MySQL 5.1 to Percona 5.1. (I have to blog about this, actually, with all the details)
  • We upgraded the kernel for 3 different Addons database servers due to a crashing bug.
  • Finished work on one of our multi-use staging clusters – upgrading and converting to innodb_file_per_table.
  • Reduced the innodb_buffer_pool_size on one of our multi-use staging clusters, so that swapping and its corresponding cpu load would be reduced.
  • Loaded in missing data due to a failed cron job on our Crash Stats cluster.
  • Deleted some spam comments in Bugzilla.
  • Created two new read/write accounts for the development database cluster for Mozillians.
  • Moved our soon-to-be deprecated cacti database off an SSH jump host, which means the jump host no longer has a MySQL installation on it.
  • Ran a query to figure out how many Bugzilla e-mail addresses have + or – in them as a percentage of total emails.

Next week is Halloween! Are you ready?

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

In MySQL 5.6, sickness it looks like IN() subqueries are optimized even better than they are in MariaDB 5.5. Here’s a typical IN() subquery, pills using the sakila sample database (query taken from slide 6 of the presentation about new MySQL 5.6 optimizer statistics):

SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)

Before there were any subquery optimizations, say if you are using MySQL 5.1, you would have to rewrite this query as a JOIN, to avoid the dreaded DEPENDENT SUBQUERY that shows up in the EXPLAIN:

mysql> explain SELECT title FROM film WHERE film_id IN (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 1025
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: index_subquery
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: func
rows: 1
Extra: Using index

MariaDB 5.5 boasts subquery optimization, and rightfully so. It looks like MariaDB materializes the subquery:

MariaDB [sakila]> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1043
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table:
type: eq_ref
possible_keys: distinct_key
key: distinct_key
key_len: 2
ref: func
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: film_actor
type: index
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 4889
Extra: Using index

So MariaDB recognizes the subquery and optimizes it. But it is still optimized as a subquery. There are 3 rows here, a new <subquery2> table is used to help optimize the results.

In MySQL 5.6, the subquery is actually optimized away. The optimizer actually changes it. You can see this in the EXPLAIN plan by looking at the select_type. In both the MySQL 5.1 and MariaDB 5.5 examples, the select_type is PRIMARY, which is used for the outer query in a subquery (or the first SELECT in a UNION, but that does not apply here). In MySQL 5.6, the select_type is SIMPLE for both rows. Note that MySQL 5.6 also does not have to add a third table as MariaDB does:

mysql> explain SELECT title FROM film WHERE film_id IN
-> (SELECT film_id FROM film_actor)G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: PRIMARY
key: idx_title
key_len: 767
ref: NULL
rows: 1000
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 1
Extra: Using index; FirstMatch(film)
2 rows in set (0.00 sec)

In the presentation, the Oracle team says that for DBT3 Query #18, “execution time reduces from days to seconds”. With optimizations like this, I believe it!

Today and tomorrow I am at CodeConnexx – An Open Source Technology and Life Conference. There are some great talks…the first talk this morning is Secrets and Success in the Style of GLEE – a bunch of songs and how they relate to being successful. By Jennifer Marsman of Microsoft.

Taylor Swift, cialis 40mg if you have an idea, psychotherapist do not be shy about it. In an interview, “don’t stop talking” – meaning show them your passion – but don’t force it, of course. Ask lots of questions, do not make assumptions. And if you get stuck in a problem, you can reason your way through it by talking out log.

Bonnie Raitt – “Let’s Give Them Something To Talk About”. Communicate! Let your manager know what’s going on and what you are doing. “Give them stories to tell about you” – and good ones too! Trip reports if you go on a trip, summary status e-mails, one-on-one meetings, etc.

Aretha Franklin – “Respect”. At the end of the day, diversity of opinion, education levels, backgrounds, is key to having successful business ideas. You can learn something from everyone. You can go around at a party, meet people and figure out what they are better than you at.

Frank Sinatra – “Luck Be a Lady”. At some other conference, Jennifer saw a formula for success: Success = hard work + intelligence + luck. She did not like that, because luck is a bunch of randomness, and if we work hard and are smart, we should be successful, right? But it’s completely true that luck is part of the equation. For example, in an interview. You can control working hard and you can control learning, and you will be well-positioned for when opportunity knocks.

Bette Middler – “Wind Beneath My Wings”. Role models – people that you worship from afar, perhaps stalk on Twitter, but probably won’t have a relationship with. Mentors are folks that are actively helping you grow, which you have a relationship with. You can choose a mentor to help you work on a skill or set of skills, and you can choose different people based on what they are good at. Someone who is good at MySQL might not be good at blogging or work/life balance. Jennifer challenges all of us to be role models to other people by speaking and blogging, because those folks are seen as industry experts, so you will become a role model.

Brittany Spears – “Oops I Did It Again”. The importance of making mistakes – making mistakes is good. Take big risks, because when a mistake does happen, you can learn from them. When a panel of successful tech women were asked what they would do differently, they said they would have taking more big risks.

Bill Withers – “Lean on Me”. Delegate if you need to. Ask people for help. Out of time, health and money, you can have any 2 of the 3. Young folks usually have time and health but not money. In your 30′s, you might have health and money but not time. And when you get older, you have time and money, but not health. Optimize for what you do not have. If you do not have time, then make it so you have more time – e.g. buying pre-made salad or getting a cleaning lady is a money/time tradeoff.

Journey – “Don’t Stop Believing”. Believe in yourself. Imposter Syndrome at Wikipedia. If you do not know, say you do not know something.

As many folks know, information pills !

This week was a great catch-up week. There is only one conference I am doing for the rest of the year, medicine CodeConnexx, sovaldi sale though submissions for conferences in the first quarter of 2013 are happening, too. We have some great candidates we are interviewing for our open Database Administrator position.

The database team has gotten a lot of great stuff done this week (I know, I say that all the time, but it’s true!):

  • Audited and got rid of legacy MyISAM tables in our Addons database.
  • Decommissioned an old MySQL cluster that has not been in use for a while. It is the database that used to back the predecessor to Mozillians.
  • Moved half our backups in one data center to another machine in the same data center, as they were on a netapp that’s having problems with being overloaded. Our Storage Team is talking to NetApp, but for now we alleviated some of the problems by moving the backups to another head. We also opened the process to get hardware allocated so our backups aren’t using NFS.
  • We took the move as an opportunity to puppetize the backup servers. Now all the backup scripts and backup instances are puppetized, with just a few more challenging items remaining: config files and startup scripts for each backup instance.
  • We have enabled a Nagios check for pt-config-diff so that we will be alerted (by e-mail only) when a running configuration on MySQL is different from the configuration file.
  • Fixed automatic slow query log copying from Addons to a machine our web developers use.
  • The entire IT team is working on documenting our Nagios checks – specifically, what to do for each Nagios check so our oncall folks can handle more problems before we have to be called in. We have documented 6 checks so far.
  • Fixed a fascinating problem in which ulimits we put in place were not being read by Percona, when we upgraded from MySQL 5.1 to Percona 5.1. (I have to blog about this, actually, with all the details)
  • We upgraded the kernel for 3 different Addons database servers due to a crashing bug.
  • Finished work on one of our multi-use staging clusters – upgrading and converting to innodb_file_per_table.
  • Reduced the innodb_buffer_pool_size on one of our multi-use staging clusters, so that swapping and its corresponding cpu load would be reduced.
  • Loaded in missing data due to a failed cron job on our Crash Stats cluster.
  • Deleted some spam comments in Bugzilla.
  • Created two new read/write accounts for the development database cluster for Mozillians.
  • Moved our soon-to-be deprecated cacti database off an SSH jump host, which means the jump host no longer has a MySQL installation on it.
  • Ran a query to figure out how many Bugzilla e-mail addresses have + or – in them as a percentage of total emails.

Next week is Halloween! Are you ready?

Last week, generic more dev.” But Baron is coming from the point of a developer, and from the point of an ops person, there is not necessarily “less ops”.

Some commenters made points along the lines of, “you can just rent rack space in some datacenter for that.” And I agree. There are some ops benefits that Amazon adds, such as easier monitoring and backups, but for the most part, there is not *less* work from an operations standpoint when you are in a cloud environment – my time doing remote DBA work at Pythian and PalominoDB certainly taught me that!

There are still operating systems to install, maintain and upgrade. There are still compatibility issues and having to upgrade and maintain software and configurations. There are software-as-a-service (SaaS) technologies like Amazon’s RDS (which provides MySQL as a service) and the benefit there is not having to worry about configuration or upgrades. There are Amazon machine images (AMIs) that folks share, so that the operating system initial installation requires little knowledge.

The cloud is really useful if you need a machine up and running very quickly. I totally understand that developers want to use the cloud instead of waiting for a machine to arrive, and even in IT it is useful to have another machine for a while. For example, if you wanted to test MySQL 5.6 but do not have a spare machine, you can spin up an instance of a machine in the cloud.

From a production or staging perspective though, there is still a LOT of work to be done to architect a system. The ops benefit of the cloud is NOT “less ops”. The ops benefit of the cloud is actually thanks to how Amazon built its cloud – it was built as a cloud computing platform. The Amazon Cloud was built to provide extra CPU cycles (“elastic cloud computing”). In the days before persistent data stores with “elastic block systems” (EBS), many developers and system administrators lost time when an instance would reboot and all their work was gone – not just the development work, but their setup work – the operating system users, any software packages they had installed, etc. Running any important system in the cloud, these days, means having some kind of installation and configuration management in place, so that if an instance reboots or if another instance is needed, the rebooted/new instance can be brought back to a working state as quickly as possible.

Some folks get their environment set up how they want it, and take a snapshot that can be used as an Amazon machine instance (AMI). This works great, until you need to update any software or make any changes in configuration, whether it’s operating system configuration (like adding a user), or software configuration (like a my.cnf file).

The benefit of the cloud from an ops side is that it forces us to do what we should be doing anyway – running installation and configuration management. At Mozilla, we have a plan to move some services to “the cloud”, but we are already using kickstart for installation management and Puppet for configuration management, so we are already set with those benefits.

Comments are closed.