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 32115 Next 30 Older Entries
A little fun with InnoDB multi-versioning
+0 Vote Up -0Vote Down

Consider the following commands, executed in the MySQL CLI on a new connection with no special preparation (and pay special attention to the execution time):

mysql> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from t;
Empty set (5.20 sec)

mysql> select count(*) from t;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (5.22 sec)

mysql> select * from t where a = 10;
Empty set (0.00 sec)

mysql> select * from t where a < 10;
Empty set (5.35 sec)

mysql> select * from t where a > 10;
Empty set (5.41 sec)

mysql> select
  [Read more...]
TokuMX, MongoDB and InnoDB, IO-bound update-only with fast storage
+0 Vote Up -0Vote Down
I repeated the update-only IO-bound tests using pure-flash servers to compare TokuMX, MongoDB and InnoDB. The test setup was the same as on the pure-disk servers except for the hardware. In this case the servers have fast flash storage, 144G of RAM and 24 CPU cores with HT enabled. As a reminder, the InnoDB change buffer and TokuMX fractal tree don't help on this workload because there are no secondary indexes to maintain. Note that all collections/tables are in one database for this workload thus showing the worst-case for the MongoDB per-database RW-lock. The result summary:
  • InnoDB is much faster than MongoDB and TokuMX. This test requires a high rate of dirty page writeback and thanks to a lot of work from the InnoDB team at MySQL with help from Percona and

  [Read more...]
Types of writes
+0 Vote Up -0Vote Down

What does it mean to make writes fast? It helps to distinguish between the different types of writes. The slowest is a write that must be implemented as read-modify-write. This might require a disk read and can also create contention from preventing concurrent changes to the row for the duration of the read, modify and write. The row might not be unlocked until the change is made durable on storage (commit, fsync, etc) which lets you estimate the peak rate at which a single row can be changed on a traditional DBMS. And this latency between changes can get even worse when there is sync replication or multiple client-server round trips per transaction. The UPDATE statement in SQL is usually implemented as read-modify-write. Some DBMS engines require locking to be done above the DBMS because they don't support locking across operations where read and write are separate operations  [Read more...]

The basics of the InnoDB undo logging and history system
+0 Vote Up -0Vote Down

InnoDB implements multi-version concurrency control (MVCC), meaning that different users will see different versions of the data they are interacting with (sometimes called snapshots, which is a bit of a misleading term). This is done in order to allow users to see a consistent view of the system without expensive and performance-constraining locking which would limit concurrency. (This is where the “concurrency control” part of the term comes from; one alternative is locking everything the user may need.) Undo logging and InnoDB’s “history” system are the mechanisms that underly its implementation of MVCC, but the way this works is generally very poorly understood.

  [Read more...]
Looking for Slave Consistency: Say Yes to –read-only and No to SUPER and –slave-skip-errors
+0 Vote Up -0Vote Down

The biggest concern with a slave is to ensure your data is consistent with the master! End of story!

3 of the biggest things I see when dealing with out-of-sync slaves:

  • Many users do not use the --read-only option on their slaves.
  • Some of those who do often have numerous users with SUPER who can still perform writes.
  • Many users simply use --slave-skip-errors=… to avoid common errors.
  • Of course, if you have a slave, definitely use the --read-only option.

    However, SUPER users can still write on slaves with --read-only, so blindly granting SUPER to all users just to save a little time when creating users won’t help. I’d suggest to use SUPER as sparingly as possible (not to mention it’s good for security also).

    And the use of --slave-skip-errors=… is generally just a quick fix to avoid

      [Read more...]
    A first look at InnoDB spatial indexes in the MySQL 5.7 april lab release ...
    +0 Vote Up -0Vote Down

    The MySQL 5.7 April Labs release comes with a preview of spatial indexes for InnoDB, something that I've heard rumours about for quite a while but so far couldn't find any kind of actual confirmation for. Spatial indexes for InnoDB would more or less get rid of the last MyISAM-only feature (after the addition of fulltext indexing to InnoDB in MySQL 5.6)

    So it's about time to have a closer look at what has been brewing at "the cathedrals crypt" ...

    read more

    More details on disk IO-bound, update only for MongoDB, TokuMX and InnoDB
    +0 Vote Up -0Vote Down
    This has a few more details on the results for update-only sysbench using a disk IO-bound workload. I describe the impact from changing innodb_flush_neighbors. The parameter can be set to write back some dirty pages early when other pages in the same extent must be written back. The goal is to reduce the number of disk seeks consumed by page writeback and this can help on disk based servers.

    There might be a small impact from changing innodb_flush_neighbors on this workload from both the TPS results and the amount of data written to disk per update.  In a previous

      [Read more...]
    Making the MTR rpl suite GTID_MODE Agnostic
    Employee_Team +1 Vote Up -0Vote Down

    In MySQL 5.6 we introduced GTID_MODE as a new server option. A global transaction identifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs. For additional information, please refer to the MySQL manual.

    Prior to 5.6.17 and 5.7.4, we had GTID specific replication (referred to as “rpl” within the MTR suite) regression tests, and we had to separately run

      [Read more...]
    ‘Open Source Appreciation Day’ draws OpenStack, MySQL and CentOS faithful
    +1 Vote Up -0Vote Down

    210 people registered for the inaugural “Open Source Appreciation Day” March 31 in Santa Clara, Calif. The event will be held each year at Percona Live henceforth.

    To kick off the Percona Live MySQL Conference & Expo 2014, Percona held the first “Open Source Appreciation Day” on Monday, March 31st. Over 210 people registered and the day’s two free events focused on

      [Read more...]
    MongoDB, TokuMX and InnoDB for disk IO-bound, update-only by PK
    +0 Vote Up -0Vote Down
    I used sysbench to measure TPS for a workload that does 1 update by primary key per transaction. The database was much larger than RAM and the server has a SAS disk array that can do at least 2000 IOPs with a lot of concurrency. The update is to a non-indexed column so there is no secondary index maintenance which also means there is no benefit from a fractal tree in TokuMX or the change buffer in InnoDB. I also modified the benchmark client to avoid creating a secondary index. Despite that TokuMX gets almost 2X more TPS than InnoDB and InnoDB gets 3X to 5X more TPS than MongoDB.
    • TokuMX is faster because it doesn't use (or waste) random IOPs on writes so more IO capacity is

      [Read more...]
    MySQL 5.7.4 Overview and Highlights
    +2 Vote Up -0Vote Down

    MySQL 5.7.4 was recently released (it is the latest MySQL 5.7, and is the “m14″ or “Milestone 14″ release), and is available for download here and here.

    The 5.7.4 changelog begins with the following, so I felt it appropriate to include it here as well.

    In Memoriam:

    “This release is dedicated to the memory of two young engineers of the MySQL Engineering family, Astha and Akhila, whom we lost while they were in their early twenties. This is a small remembrance and a way to recognize your contribution to the 5.7 release. You will be missed.”

      [Read more...]
    How TokuMX Secondaries Work in Replication
    +0 Vote Up -0Vote Down

    As I’ve mentioned in previous posts, TokuMX replication differs quite a bit from MongoDB’s replication. The differences are large enough such that we’ve completely redone some of MongoDB’s existing algorithms. One such area is how secondaries apply oplog data from a primary. In this post, I’ll explain how.

    In designing how secondaries apply oplog data, we did not look closely at how MongoDB does it. In fact, I’ve currently forgotten all I’ve learned about MongoDB’s implementation, so I am not in a position to compare the two. I think I recall that MongoDB’s oplog idempotency was a key to their

      [Read more...]
    percona-millipede – Sub-second replication monitor
    +0 Vote Up -0Vote Down

    I recently helped a client implement a custom replication delay monitor and wanted to share the experience and discuss some of the iterations and decisions that were made. percona-millipede was developed in conjunction with Vimeo with the following high-level goal in mind: implement a millisecond level replication delay monitor and graph the results.  Please visit http://making.vimeo.com for more information and thanks to Vimeo for sharing this tool!

    Here is the rough list of iterations we worked through in developing this tool/process:

  • Standard pt-heartbeat update/monitor
  • Asynchronous, threaded update/monitor tool
  • Synchronized (via zeroMQ), threaded version of the tool
  • pt-heartbeat

    Initially, we had been running

      [Read more...]
    OurSQL Episode 181: MariaDB Goodies
    +0 Vote Up -0Vote Down

    This week we discuss more features of MariaDB 10. Ear Candy is how to avoid downtime when switching to GTIDs and At the Movies is Managing Hundreds of MySQL Servers Efficiently.

    News
    MySQL Community Awards 2014: the Winners

    MariaDB 10
    MariaDB 10.0.1 features
    Other MariaDB podcasts:
    OurSQL Episode 89, where we talk about an overview of MariaDB

    read more

    MySQL Enterprise Monitor 3.0.9 has been released
    Employee_Team +0 Vote Up -0Vote Down

    We are pleased to announce that MySQL Enterprise Monitor 3.0.9 is now available for download on the My Oracle Support (MOS) web site.

    The Service Manager, Agent, and bundled MySQL Server binaries included in 3.0.9 are all updated to use OpenSSL 1.0.1g. Please see http://www.oracle.com/technetwork/topics/security/opensslheartbleedcve-2014-0160-2188454.html for further information. You can also find additional details about Enterprise Monitor 3.0.9 in the change log.

    You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab,

      [Read more...]
    Working with NULL Database Columns in Go
    +0 Vote Up -0Vote Down

    When I started to learn the Go programming language and use it with MySQL, my first encounter with nullable columns left me a little disoriented, but afterwards I came to understand how Go (as usual) provides what I was looking for in a nicer way than I had in mind.

    First, a little bit of background. Go generally simplifies a lot of code by avoiding nil (the Go keyword for an undefined value) most of the time. Most variables you'll create simply cannot be nil at all, and have a sensible "zero value" -- strings default to the empty string, numbers default to 0, booleans to false, and so on. Complex types such as structs have similarly useful zero-values. I really appreciate this. It cleans up a ton of boilerplate code to initialize variables.

    I'm going to jump right into the

      [Read more...]
    Working with NULL Database Columns in Go
    +0 Vote Up -0Vote Down

    When I started to learn the Go programming language and use it with MySQL, my first encounter with nullable columns left me a little disoriented, but afterwards I came to understand how Go (as usual) provides what I was looking for in a nicer way than I had in mind.

    First, a little bit of background. Go generally simplifies a lot of code by avoiding nil (the Go keyword for an undefined value) most of the time. Most variables you'll create simply cannot be nil at all, and have a sensible "zero value" -- strings default to the empty string, numbers default to 0, booleans to false, and so on. Complex types such as structs have similarly useful zero-values. I really appreciate this. It cleans up a ton of boilerplate code to initialize variables.

    I'm going to jump right into the

      [Read more...]
    SQL or NoSQL? … Both with MariaDB 10
    +0 Vote Up -0Vote Down
    Mon, 2014-04-14 21:52gerrynarvaja


    Dynamic columns came to my attention a few days back. Since then I read a little bit more in the documentation (see Dynamic columns in the Knowledge Base) and played with it a little. The reason I became curious was that it brings the 'schema less' feature of the NoSQL world into the MySQL world. It was implemented in MariaDB v5.3, and MariaDB 10.0 introduces several enhancements. We will cover the topic in some detail in episode 181 of the MySQL Community podcast in case you're interested. But I thought I'd showcase the feature through an hypothetical upgrade.

    Differences between
      [Read more...]

    MySQL/MariaDB autoconf macros
    +0 Vote Up -0Vote Down

    I've had my own set of autotool macros for building against different MySQL client and server APIs for quite a while. Originally created as part of my code generator projects (CodeGen_MySQL_UDF and CodeGen_MySQL_Plugin) I ended up having diverging copies in a few other projects, too (my and my autotools branch of ).

    So I've finally taken the time to merge the differnet copies into a single standalone project on GitHUB.

    read more

    All dbForge plugins in Visual Studio now!
    +0 Vote Up -0Vote Down

    We are glad to announce the release of three plugins for Visual Studio. Now, improved versions of plugins may be integrated into Visual Studio simultaneously. There is no need to leave Visual Studio or uninstall any of Devart plugins to switch between them. SQL Server, MySQL, and Oracle databases are at hand now.

    All dbForge plugins in Visual Studio now!
    +0 Vote Up -0Vote Down

    We are glad to announce the release of three plugins for Visual Studio. Now, improved versions of plugins may be integrated into Visual Studio simultaneously. There is no need to leave Visual Studio or uninstall any of Devart plugins to switch between them. SQL Server, MySQL, and Oracle databases are at hand now.

    MySQL 5.7 user table: password_last_changed & password_lifetime
    Employee +1 Vote Up -0Vote Down

    MySQL 5.7.4 has added two fields to the mysql.user table — password_last_changed, a timestamp and password_lifetime, a small but unsigned integer. Several blogs ago I started to cobble together a password expiration tracking script before these two columns were added. But I could see three ways of tracking expired passwords but none of them were palatable. Todd Farmer was working on a similar idea.

    So when you run mysql_upgrade after upgrading to 5.7.4, you will find these two new columns. The password_last_changed will be set to the time you ran the upgrade and password_lifetime will be set to null.

    You can set global password lifetime policy in the options file.
    [mysqld]


      [Read more...]
    Geographically distributed multi-master MySQL clusters
    +0 Vote Up -0Vote Down
    In this webinar, we discuss the multi-master capabilities of Continuent Tungsten to help you build and manage systems that spread data across multiple sites.  We cover important topics such as setting up large scale topologies, handling failures, and how to handle data privacy issues like removing personally identifiable information or handling privacy law restrictions on data movement. We
    Installing Apache2 With PHP5 And MySQL Support On Fedora 20 (LAMP)
    +0 Vote Up -0Vote Down

    Installing Apache2 With PHP5 And MySQL Support On Fedora 20 (LAMP)

    LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Fedora 20 server with PHP5 support (mod_php) and MySQL support.

    MySQL Connect Call for Papers ENDS April 15th
    Employee +1 Vote Up -0Vote Down

    The MySQL Connect Call for Papers ends April 15th. So submit ASAP!!

    Below are examples of some of the broad areas of interest our Conference Review Committee may be seeking:

    • Customer Success Stories or Case Studies
    • Best Practices (based on experiences and insights acquired)
    • Tips and Tricks / How To Sessions (based on expertise in specific areas)
    • Deep Dives
    • Partner / Community Solutions
    • What’s New
    • Introductory, 101-type sessions
    • Upgrades

    Write an abstract that is easy to read and describes the value of the presentation. Explicitly mention what is being discussed during the session rather than making a marketing or strategy pitch. For example, mention

      [Read more...]
    Advisory on Heartbleed (CVE-2014-0160) for Percona’s customers and users
    +0 Vote Up -0Vote Down

    Over the last few days, the Percona team has spent a lot of time evaluating the impact of the Heartbleed bug (CVE-2014-0160) for our customers and for the users of our software. We published a formal disclosure a few days ago. However, I thought a quick summary and some additional information would be good to provide for our MySQL Performance Blog readers.

    First, I want to point out that “Heartbleed” is an issue in a commonly used third-party library which typically comes with your operating system, so there is a lot of software which is impacted. An openly exposed service, which is typically a website or some form of API, can potentially cause the biggest impact for anyone. Even though we talk a lot about MySQL Server (and its variants), it will not be the

      [Read more...]
    MySQL 5.7 and GIS, an Example
    Employee_Team +1 Vote Up -0Vote Down

    Summary
    This post will offer a very simple demonstration of how you can use MySQL 5.7 for Spatial features within your applications. In order to demonstrate this, I’ll walk through how we can determine the 10 closest Thai restaurants to a particular location. For this example, we’ll be using the apartment that I lived in when I first started working at MySQL, back in 2003.

    For more details on all of the new GIS related work that we’ve done in MySQL 5.7, please read through these blog posts from the developers:


      [Read more...]
    Developing with MySQL and NoSQL
    Employee_Team +1 Vote Up -0Vote Down

    MySQL adopts a very different approach to 'NoSQL' than other databases. With the memcached plugin, MySQL provides the speed and high availability benefits of a standard 'NoSQL' database solution, while mitigating many of the drawbacks to this approach.

    A traditional memcached application bypasses the SQL layer entirely, and stores all its data in memory. This makes data access extremely fast, but there is a risk that the data will disappear in the event of a system problem. 

    The MySQL memcached plugin for InnoDB also bypasses the SQL and optimization layers, resulting in excellent performance. It goes further, writing key-value data directly to  InnoDB tables. The result is fast data access while retaining the advantages provided by the existing relational database infrastructure, such as the ability to run complex queries with SQL, maintain data

      [Read more...]
    #DBHangOps 04/16/14 -- PLMCE Learnings and Defending your DBs!
    +0 Vote Up -0Vote Down
    #DBHangOps 04/16/14 -- PLMCE Learnings and Defending your DBs!

    Come join the GHangout at http://goo.gl/rxttYG or watch the livestream below:

    Hello everybody!

    Join in #DBHangOps this Wednesday, April, 16, 2014 at 12:00pm pacific (19:00 GMT), to participate in the discussion about:

    • Learnings from Percona Live MySQL Conference and Expo
    • Defending your databases!

    Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Wednesday!

    See all of you on Wednesday!

    Examining Query Samples with VividCortex
    +0 Vote Up -0Vote Down

    Customers love our Top Queries feature, which lets them rank queries by a metric such as overall execution time or count. This is a great way to examine entire families of similar queries together. We group queries by digesting out the literals, normalizing whitespace, and so forth.

    Here is a view of queries on some of our primary database servers, over the last 4 days. What do you notice? I notice a strange pattern on query 5 and 6.

    Is that query getting slower each day till it resets? Or is its response time consistent, and its execution count varies? We can click on the query to highlight it. When we do, the right-hand information pane fills with details about it.

    Now we can see

      [Read more...]
    Showing entries 1 to 30 of 32115 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.