Author Archives: OpenSorceDBA

Workbench 6.2, CakePHP’s new ORM, and OKC Meetup on Wednesday

In the beginning was the MySQL command line and it was good. Verbose yes, error prone yes, and even tedious. But it was good. The UPDATE USER set SELECT_PRIV=’Y’, DROP_PRIV_’Y”, UPDATE_PRIV=’Y’,……,LAST_BLOODY_PRIV=’Y”; type errors have caught of us old timers. But it was still good.

Then came some GUI tools that were okay. Not great but they had their uses.

And then came MySQL Workbench. And it was very good.

Now there is MySQL Workbench 6.2 and it is excellent. It came out in Beta last week and has built on the proven success of its predecessors. All the usual stuff is there – Server Status, Users & Privileges, Startup/Shutdown, logs, Dashboard, Performance Reports and more. But there area whole bunch of subtle little tweaks that make it easier to use.

For instance, the VISUAL EXPLAIN allows toggling between the visual display and the tabular display. Plus you can now get query stats like the number of rows processed, joins per table, sorting information, and even how many temporary tables were created. And it is easier to toggle between the explain panel and the results grid.

Now that I am back from Europe, I will write a little more on this in the following days.

CakePHP’s new ORMCakePHP 2014 attendees in Madrid

I attended Cakefest in Madrid to hear about the latest in CakePHP. Version 3.0 is almost ready to launch and has many new features that will help speed its adoption. Chief among them is a new ORM’s query builder that is well designed, intuitive, and easy to use. Under the covers is PDO prepared statements to help protect against SQL injection.

Most Object Relational Modelers remind me of a juggler balanced on a large ball while simultaneously swinging a Hulla-hoop around his middle while trying to order coffee in a crowded Starbucks. Too much action for something that can be done much simpler. Object Oriented programmers want everything as an object but the rigors most ORMs demand if much more complicated than writing good SQL in the first place.

The new CakePHP ORM is based on a data mapper pattern and easily interfaces with MySQL, Postgres and SQLserver. The query builder is designed to take advantage of MySQL 5.6 features like improved sub queries. They hope soon to offer Oracle database support. The design is light, easy to read, and can take advantage of sub queries. My preference is still for well written SQL but this query builder is not bad. I am sure it will catch the attention of those who fight other ORMs in their projects.

Next years is CakePHP’s fifteenth anniversary and their spirited crew is doing great things. If you have not investigated them in a while, you really need to spend some time with their new 3.0.

Oklahoma MySQL Meetup

And I will be heading up I35 to talk to the OKC MySQL Meetup tomorrow. Hope to see you if you are in the area!

1 Comment

Filed under DBA Tools, MySQL Workbench, Open Source, User Group

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

Chicago MySQL Meetup August 4th

High Availability With MySQL – Jay Janssen of Percona

Monday, August 4, 2014
6:00 PM

GrubHub
111 W. Washington St.
Suite 2100
Chicago, IL

Come join Jay Janssen, Principal Consultant at Percona as he speaks about High Availability with MySQL. Jay has been with Percona since 2011. Before that, spent 7 years working for Yahoo in a variety of fields including High Availability architectures, MySQL training, tool building, global server load balancing, multi-datacenter environments, operationalization, and monitoring.

Pizza and beverages will be provided.

1 Comment

Filed under User Group

Early Bird Pricing for MySQL Central @ Oracle Open World Extended to August 1st

Register before August 1st for early bird pricing!

Millions of organizations around the world trust MySQL to power their business-critical web, cloud, and embedded applications. Want to learn best practices to develop next-generation applications with MySQL? Joins us at MySQL Central @ OpenWorld.

Highlights
Learn new skills
Share and network with the global MySQL community
Hear about new MySQL features directly from Oracle
Get insight on product roadmaps
Have fun

1 Comment

Filed under Oracle Open World 2014

Inaugural Meet-up Oklahoma City MySQL Meetup

The inaugural Meet-up Oklahoma City MySQL Meetup is Wednesday, July 23, 2014!
As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices”, followed by a Q/A session.

Please RSVP if you plan to attend so we can get a good headcount for food and beverages that will be provided and as always, please spread the word to friends or colleagues in the industry.

It should be a great night and great first event for the MySQL user group in OKC!

1 Comment

Filed under User Group

North Texas MySQL Users Group Meeting RSVP

Please RSVP for next Monday’s North Texas MySQL Users Group Meeting so we will know how much pizza to order.

MYSQL Tuning Trick for Queries and Server Tuning
Monday July 14th 6PM!

Event is free to the public. Newbies welcome!

Presented By: Dave Stokes, MySQL Community Manager North America, Oracle Corporation

Plus meet Oracle Ace, Oracle MySQL Ace, and IOUG Big Shot George Trujillo who has recently moved to DFW.

Oracle Corporation
6031 Connection Drive
Suite 900 Room 9068
Irving, TX 75038

Start :Monday, July 14, 2014 6:00 PM

1 Comment

Filed under User Group

MySQL APT Repository

THe MySQL APT Repository provides an easy and convenient way to get the latest MySQL software. My test server was need of a refresh so I put on a fresh install of Ubuntu 14.04 and downloaded mysql-apt-config_0.2.1-1ubuntu14.04_all.deb.

sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb
[sudo] password for dstokes:
Selecting previously unselected package mysql-apt-config.
(snip)

You will get a choice to install MySQL 5.6 or the latest 5.7 DMR.

sudo apt-get update Pulls the latest information from the repository for the various packages.

sudo apt-get install mysql-server Installs the server and will start it running. And then a quick sudo apt-get install mysql-workbench to get me where i needed to be.

There is a detailed information at A Quick Guide to Using the MySQL APT Repository

2 Comments

Filed under Administration, Basics

Log Changes with MySQL 5.7

Most MySQL-ers quickly learn to move logs out of the data directory. Hopefully the logs are being written to a different disk, on a different controller than where the data is being kept. The horror of finding you database server dead to the world because the single partition used for everything was filled up by the error log should be a thing of the past. MySQL 5.7 will give DBAs better control of log files,

As of 5.7.2, we have gained the ability to control the verbosity of error messages with log_error_verbosity. This system variable controls verbosity in writing error, warning, and note messages to the error log. A value of 1 provides errors only, 2 adds warnings, and 3 adds notes. The default value is 3. And with that with level 3, aborted connections and access-denied errors for new connection attempts are written to the error log.

The good ol’ log_warnings is being deprecated in favor of the added flexibility from log_error_verbosity.

And with MySQL 5.7.2, the log_timestamps system variable lets you set the timestamp time zone of messages written to the error log, the general query log, and slow query log files. The choices are UTC (the default) and SYSTEM (local system time zone). Previously messages use the local system time zone.

Please note that as of MySQL 5.7.2, the ID included in error log messages is that of the thread within mysqld responsible for writing the message. This indicates which part of the server produced the message, and is consistent with general query log and slow query log messages, which include the connection thread ID. Earlier releases use the ID of the mysqld process in error log message.

1 Comment

Filed under DBA Tools, Security

North Texas MySQL Users Group Meting July 14th 6PM

The North Texas MySQL Uxer Group will meet July 14th in the Oracle office in Irving at 6PM. Part of the topic will be MySQL query & server tuning with MySQL Workbench and part will be a surprise from Oracle ACE, Oracle MySQL ACE, and IOUG big shot George Trujillo who was recently relocated to the Dallas / Fort Worth Area.

And yes, pizza will be served ’cause pizza makes user groups run.

6031 Connection Drive
Suite 900
Irving, TX 75039

1 Comment

Filed under community team, User Group