MySQL 5.7 on a new Laptop

MySQL 5.7.5-m15 shoud be an easy install on a new laptop but I got bit! My faitful ol’ laptop has been put to pasture and I am busy getting a new laptop loaded with all the items I need for my job. I set up my Ubuntu 14.04 software and started adding all the usual suspects. That means Ubuntu providing MySQL 5.5 but I wanted 5.7. So I added the package to access the MySQL apt-get repository (see A Quick Guide to Using the MySQL APT Repository. It is proper procedure to go 5.5 to 5.6 to get to 5.7 and I went to 5.6. Login as root after the install and all is fine.

I reconfigure apt-get to grab 5.7 and all installs correctly, or so I thought. I can’t login! Dang. Check the log and see Access denied for user 'root'@'localhost' (using password: YES. And I knew the password I was not being fat fingered.

To make a long story short, I added the following two lines to my /etc/mysql/my.cnf file under the [mysqld] section and was able to login.
validate_password_policy = LOW

MySQl 5.7 has a slew of new secutiry features and I guessed that my old password did not fit a new default policy. But looking at the installed plugins showed validate_password was not loaded. I also use rather simple passwords on instances that I expierment on and can get by with less than optimal passwords so I set the policy to low.

Below are the relvant variables.
mysql> show variables like 'validate%';
| Variable_name | Value |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
6 rows in set (0.00 sec)

mysql> show variables like '%password%';
| Variable_name | Value |
| default_password_lifetime | 360 |
| disconnect_on_expired_password | ON |
| old_passwords | 0 |
| report_password | |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
10 rows in set (0.00 sec)

So now I have my new laptop running MySQl 5.7.5 and there are only fifty dozen otehr packages to add.

SYS Schema First Steps

Oracle DBAs have has the luxury of their V$ variables for a long time while we MySQL DBAs pretended we were not envious. With MySQL 5.6 and 5.7 we were gifted with the PERFORMANCE_SCHEMA tables. But there is such a wealth of information in those tables that it is intimidating to plunge in to them.  Thankfully Mark Leith has given us the SYS Schema. The SYS Schema is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

The first step is to get a copy of the SYS SCHEMA files.
git clone

Next install the SYS Schema (here for MySQL 5.7)
mysql -u root -p < ./sys_57.sql

Now run MySQL and look at this new schema.
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
| Tables_in_sys |
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_allocated |
| memory_global_total |
| processlist |
| ps_check_lost_instrumentation |
| schema_index_statistics |
| schema_object_overview |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_allocated |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_index_statistics |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
92 rows in set (0.00 sec)

Note that the views preceded by x$ are designed to be used within tools.

So the first table in the list above is is host_summaryand a quick query reveals the following.
mysql> select * from host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 1328
statement_latency: 10.73 s
statement_avg_latency: 8.08 ms
table_scans: 88
file_ios: 17268
file_io_latency: 6.34 h
current_connections: 3
total_connections: 10
unique_hosts: 2
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.03 sec)

Now that I have pried open the lid, the next post will show how to dig deeper into the SYS Schema.

MySQL Marinate for the Holiday Season

Just a friendly reminder that you can pick up MySQL Marinate whenever you want! You can use the master list at for reference – just ignore the dates and work at your own pace!

We found that very few people were taking the dates to heart, so we stopped trying to organize around them. The message boards are still valid, so feel free to ask if you have any questions – we are here to help!

The above was a quick note from Sheeri Cabral about the wonderful MySQL Marinate program that arrived in my email. This is a great way for novices to learn MySQL and for old war horses to find some new insights.

Does MySQL need a mentoring program?

Does MySQL need a mentoring program? I get calls, emails, and other requests for trained MySQL DBAs and Developers. Human Resources, managers, team leads, and entrepreneurs have the need but can not find the bodies. It is easy enough to download MySQL, get it running, and use it. But the next steps seem problematic for many. There are programs like MySQL Marinate and Girl Develop It to provide some hands on help for beginners. Autodidacts can find tons of MySQL Books and on line information. But how do we take the beginners and get them to intermediate or beyond?

How do we support these new comers, give them a hand if needed, a shoulder to cry on, or just provide someone who has been there before to bounce ideas around when needed? How do we pull them into social networks to warn them of pitfalls, pass on information about new technologies, or just be there as a friendly voice when the air movement device is being impacted by non optimal material? How do we pass on best practices, professional guidance, and the norms of our community? There is only so much forums, IRC, and Stack Overflow can handle. Local users groups are good if you have a local user group.

A good place to start is to see what other Open Source projects are doing. PHP Mentorting is a formal, personal, long term, peer to peer mentorship organization focused on creating networks of skilled developers from all walks of life. Read their info and let me know if you think the MySQL Community needs something similar.

Being a mentor has benefits too. There is an old saying that you really do not know a subject until you can pass on your knowledge to someone else. It also helps bring along someone who could replace you if you decided to climb the corporate ladder. Plus you never know what you fledgling might teach you.

So do we need a MySQL mentoring program?

Containing your MySQL Instances

Virtual servers for MySQL are popular but are they the answer? Should we be containing our instances instead. An Updated Performance Comparison of
Virtual Machines and Linux Containers
is an interesting study of using containers over VMs by four brilliant folk from IBM Research in Austin. There are several benefits to containers that are detailed in the study.

So what is wrong with VMs? IBM has been using them since the 1970s. Well, VMs have a static number of virtual CPUs (vCPU) and a fixed amount of RAM that bound performance. And each vCPU can only use up to one real CPUs of clock cycles. Since each VM is a Linux process, resource management like the scheduler come into play.

Containers? They are built on the kernel namespaces. A container acts like its own little Linux box but shares the overhead with other containers and the host. Stuff ‘inside’ the container can not see outside. Containers can be set up to use resources between them. Plus they can be constrained to only use a defined amount of resources such as CPU, memory and I/O.

A good part of the study looks at using MySQL on native hardware, KVM, and three Docker configurations. It is very interesting that throughput with Docker was close to native hardware and much less overhead than the KVM. SysBench shows that KVm hs much higer overhead 40%+ in ll cases. It appears that the container loses 1.5% CPU utilization.

So maybe we need to stop spinning up VMs and start deploying containers. More on this later.

Changes in MySQL 5.6.20

The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.

  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
  • A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
  • The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL

5.6.20 has a slew of bug fixes, functionality changes, and notes.

So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use blobs, consider this:

Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #16963396, Bug #19030353, Bug #69477)

That is golden information for those of us who used a lot of blobs and great info for configuring servers.