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 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 15797 Next 30 Older Entries

Displaying posts with tag: mysql (reset)

MariaDB 5.5.37 now available
+0 Vote Up -0Vote Down

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.37. This is a Stable (GA) release.

See the Release Notes and Changelog for detailed information on this release and the What is MariaDB 5.5? page in the MariaDB Knowledge Base for general information about the MariaDB 5.5 series.

Download MariaDB 5.5.37

Release Notes Changelog What is MariaDB

  [Read more...]
Abdel-Mawla Gharieb: Setting the right GCache size in Galera Cluster
+0 Vote Up -0Vote Down

One of our customers had a question related to the right value of Galera Cache size (gcache.size) in Galera Cluster for MySQL which I would like to share with you.

The question was: My maintenance window takes 4 hours for my 5TB DB. How can I avoid an SST ?!

Basically, having too small GCache size will lead to SST (Snapshot State Transfer) instead of IST (Incremental State Transfer), thus we can avoid the SST by setting the GCache to the appropriate value.

To check the current value of the GCache size:

mysql> SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_options'\G Variable_name: wsrep_provider_options Value: base_host = 192.168.1.12; . . . gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; . . .

The value


  [Read more...]
How to find bugs in MySQL
+2 Vote Up -0Vote Down

Finding bugs in MySQL is not only fun, it’s also something I have been doing the last four years of my life.

Whether you want to become the next Shane Bester (who is generally considered the most skilled MySQL bug hunter worldwide), or just want to prove you can outsmart some of the world’s best programmers, finding bugs in MySQL is a skill not reserved anymore to top QA engineers armed with a loads of scripts, expensive flash storage and top-range server hardware. Off course, for professionals that’s still the way to go, but now anyone with an average laptop and a standard HDD can have a lot of fun trying to find that

  [Read more...]
A little fun with InnoDB multi-versioning
+1 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
+1 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
+2 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...]
    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...]
    ‘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...]
    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.

    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...]
    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...]
    Why aren't you using X, version 2
    +1 Vote Up -0Vote Down
    Sometimes I get asked why am I not using product X where X is anything but MySQL. The products that are suggested change over time and the value of X very much depends on the person asking the question. An ex-manager from my days at Oracle told me that Oracle would be better and developers from the SQL Server team told me the same. For those keeping score there was a social network that ran SQL Server and they were kind of enough to explain why.

    Too often this is an assertion rather than a question and it would be more clear to say "I think you should be using X". A better question would be "Why are you using MySQL". This is the burden we carry for running MySQL at scale, but I am not in search of

      [Read more...]
    MySQL Enterprise Backup 3.10: Teasing compression.
    Employee +0 Vote Up -0Vote Down

    Ok, so I wanted to look into the new compression options of MEB 3.10.

    And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

    An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

    So, these tests are solely about backup. I’ll do restore when I get some *more* time.

     

    First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

    Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

    mysqlbackup --user=root --password=oracle
      [Read more...]
    MySQL Enterprise Backup 3.10: Teasing compression.
    Employee +0 Vote Up -0Vote Down

    Ok, so I wanted to look into the new compression options of MEB 3.10.

    And I would like to share my tests with you. Remember, they’re just this, tests, so please feel free to copy n paste and obtain your own results and conclusions, and should I say it, baselines, in order to compare future behaviour, on your own system.

    An Oracle Linux 6.3 virtual machine with 3Gb RAM, 2 virtual threads, on a 1x quad core, windows laptop. Not pretty, but hey.

    So, these tests are solely about backup. I’ll do restore when I get some *more* time.

     

    First up, lets compare like with like, i.e. MEB version 3.9 & 3.10:

    Let’s make this interesting, hence, want to use as much resources available as possible, read, write, process threads and number of buffers.

    mysqlbackup --user=root --password=oracle
      [Read more...]
    New SQLyog and MONyog coming – please upgrade soon!
    +1 Vote Up -0Vote Down

    We are about to release upgrades to both SQLyog and MONyog with an important fix: linked libraries possibly vulnerable to the ‘Heartbleed’ OpenSSL bug have been upgraded to non-affected versions (the new MONyog release will have a few more fixes as well).

    Since this security issue became known a few days ago, media and Internet have swollen with information about vulnerable systems. There is probably both a lot of facts and fiction circulating.

    A good summary appeared in the Percona blog. It mostly focuses on server-side vulnerabilities. However

      [Read more...]
    Dotdeb repository problems with MariaDB 5.5 (solution)
    +0 Vote Up -0Vote Down

    Dotdeb is a repository currently targeting Debian and Ubuntu, providing a nice set of packages for LAMP servers.

    Recently, MySQL 5.6 was added to the dotdeb repository. On the surface, this is a very harmless addition. MariaDB is a replacement for MySQL and it should be possible for applications designed for MySQL to easily switch to MariaDB. Therefore MariaDB also includes the libraries that applications using MySQL depend upon, such as libmysqlclient18 and mysql-common. The dpkg package manager looks at the MySQL 5.6 packages in dotdeb and assumes that 5.6 is a higher version than 5.5, which results in it removing or replacing libraries during normal apt-get installation and upgrade procedures.

    The problems appear in the following scenarios:

      [Read more...]
    Korean MySQL Power User Group
    +1 Vote Up -0Vote Down

    If you are a MySQL power user in Korea, its well worth joining the Korean MySQL Power User Group. This is a group led by senior DBAs at many Korean companies. From what I gather, there is experience there using MySQL, MariaDB, Percona Server and Galera Cluster (many on various 5.5, some on 5.6, and quite a few testing 10.0). No one is using WebScaleSQL (yet?). The discussion group is rather active, and I’ve got a profile there (I get questions translated for me).

    This is just a natural evolution of the DBA Dinners that were held once every quarter. Organised by OSS Korea, and

      [Read more...]
    MongoDB, TokuMX and InnoDB for concurrent inserts
    +0 Vote Up -0Vote Down
    I used the insert benchmark with concurrent insert threads to understand performance limits in MongoDB, TokuMX and InnoDB. The database started empty and eventually was much larger than RAM. The benchmark requires many random writes for secondary index maintenance for an update-in-place b-tree used by MongoDB and InnoDB. The test server has fast flash storage. The work per transaction for this test is inserting 1000 documents/rows where each document/row is small (100 bytes) and has 3 secondary indexes to maintain. The test used 10 client connections to run these transactions concurrently and each client uses a separate collection/table. The performance summaries listed below are based on the context for this test -- fast storage, insert heavy with secondary index maintenance. My conclusion from running many insert benchmark tests is that I don't want to load big databases with  [Read more...]
    MySQL 5.6.17 Overview and Highlights
    +1 Vote Up -0Vote Down

    MySQL 5.6.17 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here:

    For this release, I counted 7 “Functionality Added” and/or “Incompatible Change” fixes:

  • Incompatible Change: The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument.
  • Incompatible Change: The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now are deprecated and setting the sql_mode value to include any of them generates a warning. In MySQL 5.7, these modes do nothing. Instead, their effects are included in the effects of
  •   [Read more...]
    PHP mysqlnd memory optimizations: from 49MB to 2MB
    Employee +0 Vote Up -0Vote Down

    Inspired by Antony, Andrey has implemented a memory optimization for the PHP mysqlnd library. Depending on your usage pattern and the actual query, memory used for result sets is less and free’d earlier to be reused by the PHP engine. In other cases, the optimization will consume about the same or even more memory. The additional choice is currently available with mysqli only.

    From the network line into your script

    Many wheels start spinning when mysqli_query() is called. All the PHP MySQL APIs/extensions (mysqli, PDO_MySQL, mysql) use a client library that handles the networking details and provides a C API to the C extensions. Any recent PHP will default to use the mysqlnd library. The library speaks

      [Read more...]
    Amazon EC2 Linux AMIs
    +1 Vote Up -0Vote Down

    If you use Amazon Elastic Compute Cloud (EC2), you are always given choices of AMIs (by default; there are plenty of other AMIs available for your base-os): Amazon Linux AMI, Red Hat Enterprise Linux, SUSE Enterprise Server and Ubuntu. In terms of cost, the Amazon Linux AMI is the cheapest, followed by SUSE then RHEL. 

    I use EC2 a lot for testing, and recently had to pay a “RHEL tax” as I needed to run a RHEL environment. For most uses I’m sure you can be satisfied by the Amazon Linux AMI. The last numbers suggest Amazon Linux is #2 in terms of usage on EC2.

    Anyway, recently Amazon Linux AMI came out with the 2014.03 release (see release

      [Read more...]
    The MySQL Optimizer Cost Model Project
    Employee_Team +3 Vote Up -0Vote Down

    You may not be aware of this but the foundation that the MySQL optimizer builds on when choosing a query plan – the cost model – is for the most part very old. At least in tech terms.

    Much of it was written in another millennium, at a time when “Forest Gump” and “Titanic” won Oscars and “Baywatch” was the big thing on TV. Although the revision history doesn’t go that far back, it wouldn’t come as a surprise if it predates that annoying “Macarena” song and even “The Sign” (Ace Of Base) – don’t follow those links unless you’re feeling very brave…

    Thankfully, a lot has happened since Ace of

      [Read more...]
    Percona Live MySQL Conference Highlights
    +0 Vote Up -0Vote Down

    The Percona Live MySQL Conference and Expo 2014 was March 31st through April 4th in Santa Clara, California. I heard numerous positive comments from attendees and saw even more on social media. Our conference team lead by Kortney Runyan pulled together a smooth, enjoyable event which made it easy for attendees to focus on learning and networking. Some of the Percona Live MySQL Conference highlights from this year follow.

    Percona Live MySQL Conference Highlights

    A few stats for the conference this year versus last year:

    • Total registrations were up nearly 15%
    • Attendees represented 40 countries, up from 36 in 2013
    • 34 companies sponsored the conference this year, up from 33 last year
    • This year’s conference covered 5 days including
      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 15797 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.