Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 333 Next 30 Older Entries

Displaying posts with tag: Insight for DBAs (reset)

Monitoring MySQL flow control in Percona XtraDB Cluster 5.6
+0 Vote Up -0Vote Down

Monitoring flow control in a Galera cluster is very important. If you do not, you will not understand why writes may sometimes be stalled. Percona XtraDB Cluster 5.6 provides 2 status variables for such monitoring: wsrep_flow_control_paused and wsrep_flow_control_paused_ns. Which one should you use?

What is flow control?

Flow control does not exist with regular MySQL replication, but only with Galera replication. It is simply the mechanism nodes are using when they are not able to keep up with the write load: to keep replication synchronous, the node that is starting to lag instructs the other nodes that writes should be paused for some time so it does not get too far behind.

If you are not familiar with this notion, you

  [Read more...]
Reference architecture for a write-intensive MySQL deployment
+0 Vote Up -0Vote Down

We designed Percona Cloud Tools (both hardware and software setup) to handle a very high-intensive MySQL write workload. For example, we already observe inserts of 1bln+ datapoints per day. So I wanted to share what kind of hardware we use to achieve this result.

Let me describe what we use, and later I will explain why.

Server:

  • Chassis: Supermicro SC825TQ-R740LPB 2U Rackmount Chassis
  • Motherboard: Supermicro X9DRI-F dual socket
  • CPU: Dual Intel Xeon Ivy Bridge E5-2643v2 (6x 3.5Ghz cores, 12x HT cores, 25M L3)
  • Memory: 256GB (16x 16GB 256-bit quad-channel) ECC registered DDR3-1600
  • Raid: LSI MegaRAID 9260-4i 4-port 6G/s hardware RAID controller, 512M buffer
  • MainStorage: PCIe SSD HGST FlashMAX II 4.8TB
  • Secondary
  [Read more...]
A schema change inconsistency with Galera Cluster for MySQL
+0 Vote Up -0Vote Down

I recently worked on a case where one node of a Galera cluster had its schema desynchronized with the other nodes. And that was although Total Order Isolation method was in effect to perform the schema changes. Let’s see what happened.

Background

For those of you who are not familiar with how Galera can perform schema changes, here is a short recap:

  • Two methods are available depending on the value of the wsrep_OSU_method setting. Both have benefits and drawbacks, it is not the main topic of this post.
  • With TOI (Total Order Isolation), a DDL statement is performed at the same point in the replication flow on all nodes, giving strong guarantees that the schema is always identical on all nodes.
  • With RSU (Rolling Schema Upgrade), a DDL statement is not replicated to the other nodes.
  [Read more...]
Systemtap solves phantom MySQLd SIGTERM / SIGKILL issue
+0 Vote Up -0Vote Down

The Percona Managed Services team recently faced a somewhat peculiar client issue. We’d receive pages about their MySQL service being unreachable. However, studying the logs showed nothing out of the ordinary…. for the most part it appeared to be a normal shutdown and there was nothing in anyone’s command history nor a cron task to speak of that was suspicious.

This is one of those obscure and peculiar (read: unique) issues that triggered an old memory; I’d seen this behavior before and I had just the tool to catch the culprit in the act.

Systemtap made diagnostics of this issue possible and I can’t state enough how much of a powerful and often under-utilized tool set systemtap really is.

cat >

  [Read more...]
Managing shards of MySQL databases with MySQL Fabric
+1 Vote Up -1Vote Down

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.

Introduction

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...]
TokuDB gotchas: slow INFORMATION_SCHEMA TABLES
+0 Vote Up -0Vote Down

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...]
Check for MySQL slave lag with Percona Toolkit plugin for Tungsten Replicator
+0 Vote Up -0Vote Down

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...]
TIMESTAMP Columns, Amazon RDS 5.6, and You
+1 Vote Up -0Vote Down

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, 
);

They

  [Read more...]
Looking out for max values in integer-based columns in MySQL
+0 Vote Up -0Vote Down

Yay! My first blog post! As long as at least 1 person finds it useful, I’ve done my job. Recently, one of my long-term clients was noticing that while their INSERTs were succeeding, a particular column counter was not incrementing. A quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. We fixed this by using pt-online-schema-change to change the column to int(10) unsigned, thus allowing values up to 4294967295.

My client was now concerned about all his other integer-based columns and wanted me to check them all. So I wrote a

  [Read more...]
Failover with the MySQL Utilities: Part 2 – mysqlfailover
+1 Vote Up -0Vote Down

In the previous post of this series we saw how you could use mysqlrpladmin to perform manual failover/switchover when GTID replication is enabled in MySQL 5.6. Now we will review mysqlfailover (version 1.4.3), another tool from the MySQL Utilities that can be used for automatic failover.

Summary

  • mysqlfailover can perform automatic failover if MySQL 5.6′s GTID-replication is enabled.
  • All slaves must use --master-info-repository=TABLE.
  • The monitoring node
  [Read more...]
Using MySQL triggers and views in Amazon RDS
+1 Vote Up -0Vote Down

I recently had an opportunity to migrate a customer from a physical server into Amazon’s RDS environment. In this particular case the customers’ platform makes extensive use of MySQL triggers and views.  I came across two significant issues that prevented me from following Amazon’s documentation, which basically states “use mysqldump” but doesn’t call out a specific method of dealing with MySQL triggers and views.

Amazon Relational Database Service (Amazon RDS) is a great platform if you’re looking for complete hands-off management of your MySQL environment, but comes at a cost in the area of flexibility, i.e. you don’t have SUPER privilege and this brings up additional challenges.

  • You need to ensure you set log_bin_trust_function_creators=1 ( by default this is off, 0).
  • You need to
  •   [Read more...]
    How to avoid even more of the common (but deadly) MySQL development mistakes
    +1 Vote Up -0Vote Down

    On July 16 I’ll be presenting my next webinar focusing on common mistakes committed by MySQL users.

    How to Avoid Even More of the Common (but Deadly) MySQL Development Mistakes

    “Why can’t I just save my data to a file?”

    Using an SQL database seems so complex to get right, and for good reason. The variety of data-driven applications is practically limitless, and as project requirements change, we find ourselves taking shortcuts and adopting bad habits. But there are proven methods to understanding how to develop and manage data in a scalable and reliable way. This talk shows you some of these methods, including:

    • How to optimize a

      [Read more...]
    Failover with the MySQL Utilities – Part 1: mysqlrpladmin
    +2 Vote Up -0Vote Down

    MySQL Utilities are a set of tools provided by Oracle to perform many kinds of administrative tasks. When GTID-replication is enabled, 2 tools can be used for slave promotion: mysqlrpladmin and mysqlfailover. We will review mysqlrpladmin (version 1.4.3) in this post.

    Summary

    • mysqlrpladmin can perform manual failover/switchover when GTID-replication is enabled.
    • You need to have your servers configured with --master-info-repository = TABLE or to add the --rpl-user option for the
      [Read more...]
    Why %util number from iostat is meaningless for MySQL capacity planning
    +1 Vote Up -0Vote Down

    Earlier this month I wrote about vmstat iowait cpu numbers and some of the comments I got were advertising the use of util% as reported by the iostat tool instead. I find this number even more useless for MySQL performance tuning and capacity planning.

    Now let me start by saying this is a really tricky and deceptive number. Many DBAs who report instances of their systems having a very busy IO subsystem said the util% in vmstat was above 99% and therefore they believe this number is a good indicator of an overloaded IO subsystem.

    Indeed – when your IO subsystem is busy, up to its full capacity, the utilization should be very close to 100%. However, it is perfectly possible for the IO subsystem and MySQL with it to have

      [Read more...]
    Getting to know TokuDB for MySQL
    +0 Vote Up -0Vote Down

    During last April’s Percona Live MySQL Conference and Expo, TokuDB celebrated it’s first full-year as an open source storage engine. I still remember reading the official announcement and the expectations it created one year ago. The premises were very interesting as it had the potential of helping MySQL manage “big data” in a way InnoDB just couldn’t. It also provided additional interesting features like “hot schema changes,” all the while making our dear flash storages last longer.

    While I’ve kept an eye on the evolution of TokuDB this past year, I reckon I haven’t given

      [Read more...]
    How to setup Docker for Percona ClusterControl and add existing Percona XtraDB Cluster
    +0 Vote Up -0Vote Down

    In my previous post I showed you how to setup Percona XtraDB Cluster 5.6 on Docker. This time I will show you how to setup Percona ClusterControl and add the existing Percona XtraDB Cluster 5.6 that we’ve managed to setup from the previous post.

    Let us note the following details about our existing containers:

    • 172.17.0.2 dockerpxc1
    • 172.17.0.3 dockerpxc2
    • 172.17.0.4 dockerpxc3
    • 172.17.0.5 dockerccui-test
      [Read more...]
    “How to monitor MySQL performance” with Percona Cloud Tools: June 25 webinar
    +0 Vote Up -0Vote Down

    We recently released a new version of Percona Cloud Tools with MySQL monitoring capabilities. Join me June 25 and learn the details about all of the great new features inside Percona Cloud Tools – which is now free in beta. The webinar is titled “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools” and begins at 10 a.m. Pacific time.

    In addition to MySQL metrics, Percona Cloud Tools also monitors OS performance-related stats. The new Percona-agent gathers metrics with fine granularity (up to once per second), so you are able to see any of these metrics updated real-time.

    During the webinar I’ll explain how the new Percona-agent works and how

      [Read more...]
    mydumper [less] locking
    +1 Vote Up -0Vote Down
    In this post I would like to review how my dumper for MySQL works from the point of view of locks. Since 0.6 serie we have different options, so I will try to explain how they work

    As you may know mydumper is multithreaded and this adds a lot of complexity compared with other logical backup tools as it also needs to coordinate all threads with the same snapshot to be consistent. So let review how mydumper does this with the default settings.

    By default mydumper uses 4 threads to dump data and 1 main thread

    Main Thread
    • FLUSH TABLES WITH READ LOCK
    Dump Thread X
    • START TRANSACTION WITH CONSISTENT SNAPSHOT;
    • dump non-InnoDB tables
    Main Thread
    • UNLOCK TABLES
    Dump Thread X
    • dump InnoDB tables
    As you can see in this case we need FTWRL for two things, coordinate transaction’s snapshots and dump  [Read more...]
    Measure the impact of MySQL configuration changes with Percona Cloud Tools
    +1 Vote Up -0Vote Down

    When you make a change to your MySQL configuration in production it would be great to know the impact (a “before and after” type of picture). Some changes are obvious. For many variables proper values can be determined beforehand, i.e. innodb_buffer_pool_size or innodb_log_file_size. However, there is 1 configuration variable which is much less obvious for many people working with MySQL: query_cache.

    The idea of query cache is great, however, there are a lot of

      [Read more...]
    Using MySQL 5.6 Performance Schema in multi-tenant environments
    +0 Vote Up -0Vote Down

    Hosting a shared MySQL instance for your internal or external clients (“multi-tenant”) was always a challenge. Multi-tenants approach or a “schema-per-customer” approach is pretty common nowadays to host multiple clients on the same MySQL sever. One of issues of this approach, however, is the lack of visibility: it is hard to tell how many resources (queries, disk, cpu, etc) each user will use.

    Percona Server contains userstats Google patch, which will allow you to get the resource utilization per user. The new MySQL 5.6 performance_schema has even more instrumentation which can give you a better visibility on per-user or

      [Read more...]
    Do not trust vmstat IOwait numbers
    +0 Vote Up -0Vote Down

    I’ve been running a benchmark today on my old test box with conventional hard drives (no raid with BBU) and noticed something unusual in the CPU utilization statistics being reported.

    The benchmark was run like this:

    sysbench --num-threads=64 --max-requests=0 --max-time=600000 --report-interval=10 --test=oltp --db-driver=mysql --oltp-dist-type=special  --oltp-table-size=1000000   --mysql-user=root --mysql-password=password  run

    Which means: create 64 threads and hammer the database with queries as quickly as possible. As the test was run on the localhost I would expect the benchmark to completely saturate the system – being either using CPU or being blocked on IO nature of this benchmark so it does not spend a lot on database locks, especially as this system has just 2 cores.

    Looking at VMSTAT however I noticed

      [Read more...]
    High Availability with MySQL Fabric: Part II
    +1 Vote Up -0Vote Down

    This is the third post in our MySQL Fabric series. If you missed the previous two, we started with an overall introduction, and then a discussion of MySQL Fabric’s high-availability (HA) features. MySQL Fabric was RC when we started this series, but it went GA recently. You can read the press release here, and see this blog post from Oracle’s Mats Kindahl for more details. In our previous post, we showed a simple HA setup managed with MySQL Fabric, including some basic failure

      [Read more...]
    How MySQL ‘queries’ and ‘questions’ are measured
    +2 Vote Up -0Vote Down

    MySQL has status variables “questions” and “queries” which are rather close but also a bit different, making it confusing for many people. The manual describing it might not be very easy to understand:

    Queries
    The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
     Questions
    The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET
      [Read more...]
    From zero to full visibility of MySQL in 3 minutes with Percona Cloud Tools
    +1 Vote Up -0Vote Down

    First, I would like to invite you to my webinar, “Monitoring All (Yes, All!) MySQL Metrics with Percona Cloud Tools,” on Wednesday, June 25 at 10 a.m. Pacific Daylight Time, where I will talk on the
    new features in Percona Cloud Tools, including monitoring capabilities.

    In this post I’d like to show the cool and interesting things we’ve implemented in Percona Cloud Tools, including the recently released agent that Daniel also talks about here in this


      [Read more...]
    Location for InnoDB tablespace in MySQL 5.6.6
    +2 Vote Up -0Vote Down

    There is one new feature in MySQL 5.6 that didn’t get the attention it deserved (at least from me ) : “DATA DIRECTORY” for InnoDB tables.

    This is implemented since MySQL 5.6.6 and can be used only at the creation of the table. It’s not possible to change the DATA DIRECTORY with an ALTER for a normal table (but it’s in some case with partitioned ones as you will see below). If you do so, the option will be just ignored:

    mysql> CREATE TABLE `sales_figures` (
        ->   `region_id` int(11) DEFAULT NULL,
        ->   `sales_date` date DEFAULT NULL,
        ->   `amount` int(11) DEFAULT NULL
        -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
        -> DATA DIRECTORY = '/tb1/';
    Query OK,
      [Read more...]
    Installing Percona XtraDB Cluster 5.6 with the Docker open-source engine
    +2 Vote Up -0Vote Down

    In my previous post, I blogged about using Percona Server with Docker and have shown you how fast and easy it was to create a virtual environment with just a few commands.

    This time I will be showing you how to setup a three-node Percona XtraDB Cluster (PXC) 5.6 on the Docker open-source engine. Just to review Docker… “is an open-source engine that automates the deployment of any application as a lightweight, portable, self-sufficient container that will run virtually anywhere.”

    In this case we will make use of a Dockerfile, think of this more like the Vagrantfile, it is a build script with a set of commands automating

      [Read more...]
    Introducing the 3-Minute MySQL Monitor
    +1 Vote Up -0Vote Down

    There are many cool, new things happening with Percona Cloud Tools.  To avoid “tl;dr” I will highlight only one new feature after a brief, general announcement.  The new feature is a 3-minute MySQL monitor.  I’ll blog later about other features.

    The general announcement is: Last week we quietly released a brand-new agent called percona-agent, and we added MySQL and system monitoring to Percona Cloud Tools.  We also wrote a brand-new API from the ground up.  We call it all “PCT v2″.  For you it means a better experience and more features, all still free while we’re in beta.

    One new feature in Percona Cloud Tools v2 is MySQL monitoring in 3 minutes, i.e. a 3-minute MySQL monitor.  Let’s

      [Read more...]
    How to improve InnoDB performance by 55% for write-bound loads
    +2 Vote Up -0Vote Down

    During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write buffer is, it is a disk buffer were pages are written before being written to the actual data file. Upon restart, pages in the double write buffer are rewritten to their data files if complete. This is to avoid data file corruption with half written pages. I knew it has an impact on performance, on ZFS since it is transactional I always disable it, but I never realized how important the performance

      [Read more...]
    Database auditing alternatives for MySQL
    +1 Vote Up -0Vote Down

    Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes.

    Audits are needed for security. You can track data access and be alerted to suspicious activity. Audits are required for data integrity. They are the only way to validate that changes made to data are correct and legal.

    There are several regulations that require database audits:

    • Sarbanes-Oxley (SOX) Act of 2002 is a US federal law that regulates how financial data must be handled and protected.
    • Payment Card Industry Data Security Standard, otherwise known as PCI-DSS is an international standard developed to protect cardholder’s data.
    • Health Insurance Portability and Accountability Act (HIPAA) enacted by the U.S. Congress
      [Read more...]
    Errant transactions: Major hurdle for GTID-based failover in MySQL 5.6
    +1 Vote Up -0Vote Down

    I have previously written about the new replication protocol that comes with GTIDs in MySQL 5.6. Because of this new replication protocol, you can inadvertently create errant transactions that may turn any failover to a nightmare. Let’s see the problems and the potential solutions.

    In short

    • Errant transactions may cause all kinds of data corruption/replication errors when failing over.
    • Detection of errant transactions can be done with the GTID_SUBSET() and
      [Read more...]
    Showing entries 1 to 30 of 333 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.