This is the fourth post in our MySQL Fabric series. In case you’re joining us now, we started with an introductory post, and then discussed High Availability (HA) using MySQL Fabric here (Part 1) and here (Part 2). Today we will talk about how MySQL Fabric can help you scale out MySQL databases with sharding.
At the time of writing, MySQL Fabric includes support for range- and hash-based sharding. As with HA, the functionality is split between client, through a MySQL Fabric-aware connector; and server, through the[Read more...]
MySQL Enterprise Monitor 2.3.18 is now available for download on the My Oracle Support (MOS) web site. This is a maintenance release that updates various third party components. You can find more information on the contents of this release in the change log.
You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.
Important: MySQL Enterprise Monitor (MEM) 3.0 offers many significant improvements over MEM 2.3. If you have not already analyzed upgrading any remaining 2.3 installations to MEM 3.0, we highly recommend you do so.[Read more...]
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.
6031 Connection Drive
Suite 900 Room 9068
Irving, TX 75038
Start :Monday, July 14, 2014 6:00 PM
It has been over a week or so and I have realized that I need to continue delivering blog posts in the series "Docker: Containers for the Masses" by covering a module for Ansible that I developed, docker_facts
This blog post is the latest in the series:[Read more...]
ClusterControl uses the Apache HTTP Server to serve its web interface, but it is also possible to use nginx. nginx + PHP fastcgi is well-known for its capabilities to run on a small memory footprint compared to standard Apache + PHP DSO.
In this post, we will show you how to run ClusterControl on nginx web server by swapping out the default Apache web server installed during the initial deployment. This blog post does not mean that we officially support nginx, it just an alternative way that a portion of our users have been interested in. For instance, Phil Bayfield[Read more...]
Human mistakes are inevitable. Wrong “DROP DATABASE” or “DROP TABLE” may destroy critical data on the MySQL server. Backups would help however they’re not always available. This situation is frightening but not hopeless. In many cases it is possible to recover almost all the data that was in the database or table.
Let’s look how we can do it. The recovery plan depends on whether InnoDB kept all data in a single ibdata1 or each table had its own tablespace . In this post we will consider the case innodb_file_per_table=OFF. This option assumes that all tables are stored in a common file, usually located at /var/lib/mysql/ibdata1.
For our scenario we will use[Read more...]
With the majority of the clients where I perform server audits I find an issue where both the host server and MySQL are not configured with anything other than the default values for the open files limit. This can cause a system stalling event where users are not able to access the server. What do we do to keep this from happening? I'm glad you asked! But first a little background...
The open_files_limit configuration value is used to specify the number of open files a user is allowed to have at any one time. This is done both in the operating system, and in the MySQL configuraition. The reason that it matters to MySQL is that every table consists of multiple files. When the process user, typically mysql, is accessing tables for a query then really the user is accessing files. Since there can be hundreds of users at any one times the number of open files can quickly add up. With a default of[Read more...]
While writing our TCP stream reassembly and MySQL protocol reverse-engineering algorithms, a few finer points of the MySQL protocol and internals came up. None of this is new information, but you may not have stumbled upon it before.
Prepared statements are generated by the
COM_STMT_PREPARE protocol command, with the statement text as an argument. The server prepares the statement, and assuming all goes well, returns a statement ID. This is a number that the client needs to remember for future executions of the prepared statement. The ID increments with each new prepared statement.
The statement is scoped to the connection that created it. It's not visible or valid for any other connection. Statement IDs are private to the connection too, so server-wide you will not have[Read more...]
We are using Percona Server + TokuDB engine extensively in Percona Cloud Tools and getting real usage operational experience with this engine. So I want to share some findings we came across, in hope it may help someone in their work with TokuDB.
So, one problem I faced is that
SELECT * FROM INFORMATION_SCHEMA.TABLES is quite slow when I have thousands tables in TokuDB. How slow? For example…
select * from information_schema.tables limit 1000; ... 1000 rows in set (18 min 31.93 sec)
This is very similar to what InnoDB faced a couple years back. InnoDB solved it by adding[Read more...]
A while back, I made some changes to the plugin interface for pt-online-schema-change which allows custom replication checks to be written. As I was adding this functionality, I also added the --plugin option to pt-table-checksum. This was released in Percona Toolkit 2.2.8.
With these additions, I spent some time writing a plugin that allows Percona Toolkit tools to use Tungsten Replicator to check for slave lag, you can find the code at[Read more...]
FromDual has the pleasure to announce the release of the new version 0.9.3 of its popular Database Performance Monitor for MySQL, Galera Cluster, MariaDB and Percona Server mpm.
This release contains various minor bug fixes and improvements.
You can download
Anemometer is a MySQL slow query monitoring tool. It's used to analyze/visualize slow query log, collected from MySQL instance to identify the problematic queries. Also, makes it easier to figure out what to optimize and how to track performance over time.
Configure, webserver with php, get aneommeter code from github and place into the document root of the webserver.
$ sudo git clone git://github.com/box/Anemometer.git
If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section on Fine-Tuning MySQL Full-Text Search to see what configuration changes may be required. As I mentioned in yesterday's post when comparing query results on my database with FULLTEXT indexes in MyISAM versus InnoDB I got different results. Specifically, the InnoDB tables were returning fewer results for certain queries with short FULLTEXT search terms. Here's an example of a query that returned fewer results on InnoDB:
where match(name,description,keywords) against('+v1*' IN BOOLEAN MODE);
With any technology you use, you have to ask yourself, "What is this tech good at doing?" For me, MySQL has always been excelent at running lots of small queries that use primary, unique, or well defined covering indexes. I guess most databases are good at that. Perhaps that is the bare minimum for any database. MySQL seems to excel at doing this however. We had a query that looked like this:
select category_id, count(*) from some_table[Read more...]
article_id in (1,2,3,4,5,6,7,8,9) and
This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:
CREATE TABLE mysql56 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', );
However, under Amazon RDS, the same table looked like this:
CREATE TABLE rds56 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, ts1 TIMESTAMP NULL DEFAULT NULL, ts2 TIMESTAMP NULL DEFAULT NULL, );
We're now pleased to announce additional keynotes delivered by MySQL power users. They will follow the "State of The Dolphin" keynote address by Oracle's Chief Corporate Architect Edward Screven and VP of MySQL Engineering Tomas Ulin, and include:
High Speed Event Logging at Booking.com
Nicolai Plum, Senior Systems Architect, will provide an
As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I read Matt Lord's blog post about the document relevancy rankings used for InnoDB full-text searches I knew to expect some differences when sorting by relevancy, so I want to focus on getting the same set of rows back, mostly ignoring the order in which the rows are returned.
Percona toolkit has a tool called pt-upgrade that works well for this purpose. I used 2 test servers with a copy of my production database. On one of the servers I left the[Read more...]
While there are many graphing tools out there and we’ve used Munin for a while now.
The MySQL plugin for Munin had fallen out of date and the show engine innodb status output changed in 5.5 making some bits of the plugin simply not work any more. Also the show global status has some extra variables so there was a need to create new graphs.
All of these are now in the 2.1.8+ development releases of Munin.
Here are samples of the new/updated graphs.
Check out the recording below!
Join in #DBHangOps this Thursday, July, 10, 2014 at 11:00am pacific (18:00 GMT), to participate in the discussion about:
See all of you on Thursday!Show notes
If you are automating your infrastructure using Puppet, then this blog is for you. We are glad to announce the availability of a Puppet module for ClusterControl. For those using Chef, we already published Chef cookbooks for Galera Cluster and ClusterControl some time back.
The ClusterControl module initial release is available on Puppet Forge, installing the[Read more...]