Mysql – How to set MySQL’s internal time zone support

database-performanceMySQLrdstimezone

Today MySQL at Facebook posted the following about MySQL performance:

Today we found a performance problem in a completely unexpected place – if TIMESTAMP data type is being used and MySQL is configured to use system time zone, it will hit a code in Linux C library which has a global mutex. To add insult to an injury, MySQL's internal time zone support (using mysql.time_zone* tables) is ~30% faster at single thread workload.
With 30 client threads MySQL's internal timezone conversion is 30x faster.
The day started with something that looked like a MySQL bug, but apparently it is a MySQL victory. — domas

How can I determine whether my MySQL instance is using the slower system time zone or whether it's using the faster mysql.time_zone* tables? i.e. which MySQL config parameters should I check?

(we actually use Amazon RDS rather than hosting our own database however I don't think that matters other than instead of checking my.cnf, I'll need to use the RDS parameter group to check or change config variables. )

Best Answer

From the linked article

Once you load up the files (via http://dev.mysql.com/.../time-zone-support.html), just specify global time_zone configuration parameter in my.cnf, or change it with SET GLOBAL ... - still don't forget to change it in my.cnf. Do note, you may want to do that on all the slaves as well, as replication is a bit annoyed by different timezones.

You can determine the global.time_zone setting with

mysql> select @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
1 row in set (0.00 sec)
Related Topic