Category Archives: MySQL

Fabric First Steps, er, Threads

I will be presenting MySQL Fabric to the Triangle MySQL User Group in Raleigh on February 11th and then I will be speaking the next day at Percona University. Part of my first talk will have a live demo. Most presenters dread live demos as anything that can go wrong usually does go wrong and in very spectacular fashion to ensure humiliation, disgrace, and well deserved scorn.

To add to the pressure is a new laptop that is so far reluctant to perform well during the first two presentations of this year. Hopefully third presentation is a charm.

So how do you start with Fabric? First, download the MySQL Utilities. I am using the 1.5.2 version (1.6 Alpha is also available) and installed it on my Ubuntu 14.04 with dpkg.

For the impatient, there is a Fabric Quick Start for those who do not want to follow my plodding.

I setup a Fabric user with MySQL Workbench un-creatively named ‘Fabric’.

The heart of Fabric is the mysqlfabric command.
$ mysqlfabric help
Usage: mysqlfabric [--param, --config] [arg, ...].

MySQL Fabric 1.5.2 - MySQL server farm management framework

Options:
--version show program's version number and exit
-h, --help show this help message and exit
--param=CONFIG_PARAMS
Override a configuration parameter.
--config=FILE Read configuration from FILE.

Basic commands:
help Show help for command
help commands List all commands
help groups List all groups
$

Now I need to investigate setting up some servers to manage. With a new laptop I need to see how it handles Virtual Box, Vagrant or maybe Giuseppe’s MySQL Sandbox for this purpose. Part of the dread of a live demo is the failure of the building blocks and i want this as simple as possible. But that will be in the next post.

Leave a comment

Filed under MySQL, MySQL Fabric

Dallas PHP Users Group Meeting Rescheduled to Jan 21st

Dave Stokes: SQL for PHP Programmers

Wednesday, Jan 21, 2015, 7:00 PM

SoftLayer Offices
4849 Alpha Rd Dallas, TX

48 PHP Developers Attending

This month we welcome back Dave Stokes to share some excellent SQL knowledge. He’ll be presenting a regular session length of his tutorial talk “SQL for PHP Developers”:————-PHP Programmers know the latest and greatest on their preferred language, are excellent at Javascript but very few have had any training in SQL.  This presentation co…

Check out this Meetup →

Wednesday, January 21, 2015
7:00 PM

SoftLayer Offices
4849 Alpha Rd, Dallas, TX (map)

Come in the doors by the flagpoles, check in with security then it’s upstairs to the left
This month we welcome back MySQL Community Manager Dave Stokes from Oracle to share some excellent SQL knowledge. He’ll be presenting a regular session length of his tutorial talk “SQL for PHP Developers”:

————-

PHP Programmers know the latest and greatest on their preferred language, are excellent at Javascript but very few have had any training in SQL. This presentation covers the differences in a declarative language (SQL) and an Object-Orientated/Procedural language (PHP), how relational theory is supposed to work, and how to let the database do the heavy lifting to make your life easier.
————-
Come out on Tuesday the 20th at 7pm at the SoftLayer offices on Alpha to hang out with fellow developers and hear about this great topic!

Where: SoftLayer Offices on Alpha Rd

Who: Dave Stokes

When: Wednesday, January 21st @ 7pm

Leave a comment

Filed under MySQL, User Group

Color Your MySQL Console

I started programming in the era of punch cards and learned to love monochrome screens quickly. The standard MySQL client console is a echo of those days. But we can get color.

One of the really interesting parts of my job is looking at the new submissions to Planet.MySQL.Com and seeing what interesting things people are doing with MySQL. Today I found Nicola Strappzaaon’s Swapbytes.com in the approval queue and his top article at the time was Give a little color to the MySQL Console. Actually the tile read Darle un poco de color a la consola de MySQL but Google translate overcame my insufficient skills.

Basically you install grcat ‘universal coulouriser’, install a configuration file, and tweak the .my.cnf file to use grcat as the pager. Nicola’s blog goes into more detail but here is the gist.


apt-get install grc
https://raw.githubusercontent.com/nicola51980/myterm/master/bash/dotfiles/grcat wget -O ~ / .grcat

And the .my.cnf file

[client]
pager = grcat ~/.grcat | less -RSFXin

Colorized MySQL Console

Color Added MySQL Console is quick and easy to do do.

I had change the .grcat configuration file as there was a complaint about a bad color ‘whit’ but replacing the first ‘white’ in the file with red solved that quickly. So change white to red under the section #data in ( ) and ‘ ‘.

So hats off to Nicola Strappzaaon and all the other new bloggers on Planet! And I encourage everyone to check out the different language blogs as you never know what gems you will find there.

Leave a comment

Filed under Basics, MySQL

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.
plugin-load=validate_password.so
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.

2 Comments

Filed under Administration, MySQL, MySQL Security

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 https://github.com/MarkLeith/mysql-sys

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.

1 Comment

Filed under MySQL, Uncategorized

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 http://www.meetup.com/Virtual-Tech-Self-Study/messages/boards/thread/38423162 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.

Leave a comment

Filed under Basics, MySQL

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?

4 Comments

Filed under Basics, MySQL

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.

Leave a comment

Filed under Administration, MySQL

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.

1 Comment

Filed under Administration, Basics, MySQL

Recruiters Looking for MySQL DBAs and MySQL Developers

Good MySQL talent is hard to find. Each day I get several ‘I am looking for a MySQL DBA’ or ‘MySQL Developer needed’. Long story short, post your opening on http://forums.mysql.com as it catches the most eyes of MySQL Professionals.

1 Comment

Filed under MySQL