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 754 Next 30 Older Entries

Displaying posts with tag: innodb (reset)

When (and how) to move an InnoDB table outside the shared tablespace
+0 Vote Up -0Vote Down

In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his server who was looking for a way to make the ibdata1 file shrink. As you may know, that file (or, as explained there, the set of ibdata files composing the shared tablespace) stores all InnoDB tables created when innodb_file_per_table is disabled, but also other InnoDB structures, such as undo logs and data dictionary.

For example, when you run a transaction involving

  [Read more...]
A closer look at the MySQL ibdata1 disk space issue and big tables
+2 Vote Up -0Vote Down

A recurring and very common customer issue seen here at the Percona Support team involves how to make the ibdata1 file “shrink” within MySQL. I can only imagine there’s a degree of regret by some of the InnoDB architects on their design decisions regarding disk-space management by the shared tablespace* because this has been a big frustration for many MySQL users over the years.

There’s a very old bug (“InnoDB ibdata1 never shrinks after data is removed,” Sept. 8 2003) documenting user dissatisfaction. Shortly before that issue celebrated its 10th anniversary, James Day, MySQL senior principal support engineer at Oracle, posted a comment

  [Read more...]
Take image from corrupted hard drive
+0 Vote Up -0Vote Down

There are at least two cases when it makes sense to take an image from a corrupted hard drive as soon as possible: disk hardware errors and corrupted filesystem. Faulty hard drives can give just one chance to read a block, so there is no time for experiments. The similar picture with corrupted filesystems. Obviously something went wrong, it’s hard to predict how the operating system will behave next second and whether it will cause even more damage.

Save disk image to local storage

Probably the best and fastest way is to plug the faulty disk into a healthy server and save the disk image locally:

# dd if=/dev/sdb of=/path/on/sda/faulty_disk.img  conv=noerrror

Where /dev/sdb is the faulty disk and faulty_disk.img is the image on

  [Read more...]
Performance evaluation of MariaDB 10.1 and MySQL 5.7.4-labs-tplc
+0 Vote Up -0Vote Down

Introduction

Evaluating the performance of database systems is a very demanding task. There are a lot of hard choices to be made, e.g.:

  • What operating system and operating system version is to be used
  • What configuration setup is to be used
  • What benchmarks are to be used and how long are the warm-up and measure times
  • What test setups are to be used
  • What version of the database management system is used
  • What storage engine is used

While performance evaluation is mostly machine time, there is still a lot of hard work for the human monitoring the tests. In this blog post we have made following choices:

  • We’re using an Intel Xeon E5-2690 @ 2.9GHz CPU containing 32-cores and Linux 3.4.12 with 132G main memory. The database is stored on a Fusion-IO ioDrive2 Duo 2.41TB
  [Read more...]
Recover Corrupt MySQL Database
+2 Vote Up -0Vote Down

The unDROP for InnoDB tool can used to recover corrupt MySQL database. In this post we will show how to repair MySQL database if its files became corrupted and even innodb_force_recovery=6 doesn’t help.

The corruption of InnoDB tablespace may be caused by many reasons. A dying hard drive can write garbage, thus page checksum will be wrong. InnoDB then reports to the error log:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 4.

MySQL is well know for poor start-up script. A simple upgrade procedure may end up with two mysqld processes writing

  [Read more...]
Percona Server 5.1.73-14.12 is now available
+1 Vote Up -0Vote Down

Percona Server version 5.1.73-14.12

Percona is glad to announce the release of Percona Server 5.1.73-14.12 on July 31st, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.1.73, including all the bug fixes in it, Percona Server 5.1.73-14.12 is now the current stable release in

  [Read more...]
Update on MySQL on POWER8
+2 Vote Up -0Vote Down

About 1.5 months ago I blogged on MySQL 5.6 on POWER andtalked about what I had to poke at to make modern MySQL versions run and run well on shiny POWER8 systems.

One of those bugs, MySQL bug 47213 (InnoDB mutex/rw_lock should be conscious of memory ordering other than Intel) was recently marked as CLOSED by the Oracle MySQL team and the upcoming 5.6.20 and 5.7.5 releases should have the fix!

This is excellent news for those wanting to run MySQL on SMP systems that don’t have an Intel-like memory model (e.g. POWER and MIPS64).

This was the most major and invasive patch in the patchset for MySQL on POWER. It’s

  [Read more...]
Externally Stored Fields in InnoDB
Employee_Team +1 Vote Up -0Vote Down

This article discusses the storage (inline and external) of field data in the InnoDB storage engine. All fields of variable length types like VARCHAR, VARBINARY, BLOB and TEXT can be stored inline within the clustered index record, or stored externally in separate BLOB pages outside of the index record (but within the same tablespace). All of these fields can be classified as large objects. These large objects are either binary large objects or character large objects. The binary large objects do not have an associated character set, whereas the character large objects do.

Within the InnoDB storage engine there is no difference between

  [Read more...]
Improving Innochecksum
Employee_Team +1 Vote Up -0Vote Down

In WL#6045 we improved the innochecksum tool so that it opens the tablespace file and validates the checksum for each page. It compares the calculated checksum to the stored checksum in the page and reports mismatches, if any. It can also now rewrite page checksums in the tablespace. It is an offline tool, however, so make sure that the MySQL server is not using the tablespace files when running the innochecksum tool.

When InnoDB encounters a page checksum mismatch, it will cause an ASSERT and perform a forced shutdown. So rather than wait for InnoDB to encounter this in production, it is better to use the innochecksum tool to validate and correct any checksum issues ahead of time. You could perform these checks on your MySQL

  [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...]
InnoDB, The Choice for High Concurrency Database Systems
Employee_Team +1 Vote Up -0Vote Down

InnoDB has proven to be a reliable data storage engine for modern, high concurrency database systems. It is fully ACID compliant, and supports a wide range of isolation modes, from READ-UNCOMMITEED to SERIALIZABLE.

InnoDB multiversion concurrency control (MVCC) enables records and tables to be updated without the overhead associated with row-level locking mechanisms. The MVCC implementation in InnoDB largely eliminates the need to lock tables or rows during updates, and enables good performance for high concurrency workloads.

To learn more about this subject and related performance tuning topics tuning, take the MySQL Performance Tuning training course. This

  [Read more...]
ibdconnect and secondary keys
+0 Vote Up -0Vote Down

ibdconnect is a tool to connect an ibd file to a foreign ibdata file. it’s important to understand how it works as secondary keys may cause interesting behavior.

Here is a table with a unique secondary key:

CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I update InnoDB dictionary with ibdconnect it all goes well:

# ./ibdconnect -f  t1.idb -o  /var/lib/mysql/ibdata1 -d test -t t1

Updating test/t1 (table_id 13) with id 0xA3060000
SYS_TABLES is updated successfully

SYS_INDEXES is updated










  [Read more...]
Fun with Bugs #33 - bugs fixed in MySQL 5.6.19
+0 Vote Up -0Vote Down
It seems Oracle released MySQL 5.6.19 yesterday. So, it's time to check what community bug reports are fixed there.

Let's start with InnoDB. We have the following bugs fixed:
  • Bug #72079, "Upgrade from 5.6.10 to 5.6.16 crashes and leaves unusable DB". Honestly I had not noted this bug, even though I upgrade several instances on Windows to each and every release since 5.6.8. Probably because I had no need to use FTS indexes till recently...
  • Bug #71014, about two extra memset calls that are now removed. I hope the patch bug report



  [Read more...]
Thanks, Oracle for fixing MySQL Bug 69477
+2 Vote Up -0Vote Down

Just under a year ago, on June 14, 2013, I reported MySQL Bug 69477 (and MariaDB Bug MDEV-4662) titled “InnoDB: Use of large externally-stored fields makes crash recovery lose data”. The gist of this bug is that if you use large BLOB fields (> ~10% of your InnoDB redo logs size), you can compromise crash recovery and suffer data loss after a crash.

The MySQL bug was marked as “Verified” in less than an hour (wow!), but since then there was not a single word about it. Suddenly a couple of days ago, I got an email with “[Com,Ver->Csd]” in the subject. My immediate thought was “Damn it, closed?! How can they close it?! It’s a bug!” — however, upon opening the email I noticed it was not

  [Read more...]
How important is it to use 2-byte and 3-byte integers?
Employee +2 Vote Up -0Vote Down

One interesting feature of MySQL, is that it supports a very large number of integer data types. From the MySQL manual:

integer types supported by MySQL." border="1">
Type Storage Minimum Value Maximum Value (Bytes) (Signed/Unsigned) (Signed/Unsigned) TINYINT 1 -128 127 0 255 SMALLINT 2 -32768 32767 0 65535 MEDIUMINT 3 -8388608 8388607 0 16777215 INT 4 -2147483648 2147483647 0 4294967295 BIGINT 8 -9223372036854775808
  [Read more...]
A technical WebScaleSQL review and comparison with Percona Server
+1 Vote Up -0Vote Down

The recent WebScaleSQL announcement has made quite a splash in the MySQL community over the last few weeks, and with a good reason. The collaboration between the major MySQL-at-scale users to develop a single code branch that addresses the needs of, well, web scale, is going to benefit the whole community. But I feel that the majority of community opinions and comments to date have been based on the announcement itself and the organizational matters only. What we have been missing is an actual look at the code. What actual new features and bug-fixes are there? Let’s take a

  [Read more...]
MySQL-5.7 improves DML oriented workloads
Employee_Team +3 Vote Up -0Vote Down

In MySQL 5.7, we have improved the scalability of DML oriented workloads in InnoDB. This is the result of a number of changes, which I will outline below.

(1) Fix index->lock contention

This RW lock protects all indexes, both the cluster and the secondary indexes.

Before 5.7, every modifications to non-leaf pages (every modifications for the tree structure) required to exclude the other threads’ access to the whole index by X-lock, and every concurrent accessing the index tree were blocked. This was the major reason of the index->lock contention in concurrent DML workloads.

In MySQL 5.7 concurrent access is now permitted to the non-leaf pages (internal nodes of the B+Tree) as long as they are not related to the concurrent tree structure modifications (WL#6326). This change reduces

  [Read more...]
InnoDB with reduced page sizes wastes up to 6% of disk space
+2 Vote Up -0Vote Down

In InnoDB bugs found during research on InnoDB data storage I mentioned MySQL Bug #67963 which was then titled “InnoDB wastes 62 out of every 16384 pages”. I said:

InnoDB needs to occasionally allocate some internal bookkeeping pages; two for every 256 MiB of data. In order to do so, it allocates an extent (64 pages), allocates the two pages it needed, and then adds the remainder of the extent (62 free pages) to a list of extents to be used for single page allocations called FREE_FRAG. Almost nothing allocates pages from that list, so these pages go to waste.

This is fairly subtle, wasting only 0.37% of disk space in any large InnoDB table, but nonetheless

  [Read more...]
Rankings with InnoDB Full-Text Search
Employee_Team +2 Vote Up -0Vote Down

Summary
Since MySQL 5.6 went GA—where among many other new features, we introduced full-text indexes for the InnoDB storage engine—questions have occasionally come up about InnoDB full-text search relevancy rankings when doing BOOLEAN MODE searches. Typically these questions revolved around a core issue: why do the results differ from that of MyISAM? In short, the InnoDB document search and relevancy rankings were modeled after the open source Sphinx full-text search engine, and not after the MyISAM storage engine (which sometimes simply produces incorrect results). The algorithms used for the InnoDB implementation

  [Read more...]
MariaDB 10 – XtraDB & InnoDB versions
+1 Vote Up -0Vote Down

I’ve had this question several times when presenting and once via an internal email thread so I figure I might as well write about it: What is the default transactional engine in MariaDB 10.0? The answer is simple – it is XtraDB.

However this answer has some history: initial releases of MariaDB 10 actually shipped with InnoDB from MySQL 5.6. Only in 10.0.9 RC did the default switch back to being XtraDB. As MariaDB users previously know, XtraDB was the default InnoDB in 5.1, 5.2, 5.3, and 5.5 too. As always, you can switch easily between InnoDB/XtraDB – read more in: Using InnoDB instead of XtraDB

How do you tell what version of InnoDB or XtraDB you are running? Simply, run: SHOW GLOBAL VARIABLES LIKE 'innodb_version';

MariaDB 10.0 (read

  [Read more...]
Thoughts on Small Datum – Part 2
+0 Vote Up -0Vote Down

If you did not read my first blog post about Mark Callaghan’s (@markcallaghan) benchmarks as documented in his blog, Small Datum, you may want to skim through it now for a little context.

——————-

On March 11th, Mark, a former Google and now Facebook database guru, published an insertion rate benchmark comparing MySQL (http://www.mysql.com) outfitted with the InnoDB storage engine with two NoSQL alternatives — basic MongoDB and TokuMX (the Tokutek high-performance

  [Read more...]
The mystery of MySQL 5.6 excessive buffer pool flushing
+1 Vote Up -0Vote Down

I'm experimenting with upgrading to MySQL 5.6 and am experiencing an unexplained increase in disk I/O utilization. After discussing this with several people I'm publishing in the hope that someone has an enlightenment on this.

We have a few dozens servers in a normal replication topology. On this particular replication topology we've already evaluated that STATEMENT based replication is faster than ROW based replication, and so we use SBR. We have two different workloads on our slaves, applied by two different HAProxy groups, on three different data centres. Hardware-wise, servers of two groups use either Virident SSD cards or normal SAS spindle disks.

Our servers are I/O bound. A common query used by both workloads looks up data that does not necessarily have a hotspot, and is

  [Read more...]
Thoughts on Small Datum – Part 1
+0 Vote Up -0Vote Down

A little background…

When I ventured into sales and marketing (I’m an engineer by education) I learned I would often have to interpret and simply summarize the business value that is sometimes hidden in benchmarks. Simply put, the people who approve the purchase of products like TokuDB® and TokuMX™ appreciate the executive summary.

Therefore, I plan to publish a multipart series here on TokuView where I will share my simple summaries and thoughts on business value for the benchmarks Mark Callaghan (@markcallaghan), a former Google and now Facebook database guru, is publishing on his blog, Small Datum.

I’m going to start with his first benchmark post and work my way forward to

  [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...]
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...]
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...]
WordPress and UTF-8
Employee +2 Vote Up -0Vote Down

For many years, MySQL had only supported a small part of UTF-8, a section commonly referred to as plane 0, the “Basic Multilingual Plane”, or the BMP. The UTF-8 spec is divided into “planes“, and plane 0 contains the most commonly used characters. For a long time, this was reasonably sufficient for MySQL’s purposes, and WordPress made do with this limitation.

It has always been possible to store all UTF-8 characters in the latin1 character set, though latin1 has shortcomings. While it recognises the connection between upper and lower case characters in Latin alphabets (such as English, French and German), it doesn’t recognise the same connection for other alphabets. For example, it doesn’t know that ‘Ω’ and ‘ω’ are the upper and lower-case

  [Read more...]
InnoDB Transparent PageIO Compression
Employee_Team +3 Vote Up -0Vote Down

We have released some code in a labs release that does compression at the InnoDB IO layer. Let me answer the most frequently asked question. It will work on any OS/File system that supports sparse files and has “punch hole” support. It is not specific to FusionIO. However, I’ve been told by the FusionIO developers that you will get two benefits from FusionIO + NVMFS, no fragmenation issues and more space savings because of a smaller file system block size. Why the block size matters I will attempt to explain next.

The high level idea is rather simple. Given a 16K page we compress it using your favorite compression algorithm and write out the only the compressed data. After writing out the data we “punch a hole” to release the unused part of the original 16K block back to the file system. Let me illustrate with an example:

[DDDDDDDDDDDDDDDD]

  [Read more...]
Significant performance boost with new MariaDB page compression on FusionIO
+0 Vote Up -0Vote Down

The MariaDB project is pleased to announce a special preview release of MariaDB 10.0.9 with significant performance gains on FusionIO devices. This is is a beta-quality preview release.

Download MariaDB 10.0.9-FusionIO preview

Background

The latest work between MariaDB and FusionIO has focused on dramatically improving performance of MariaDB on the high-end SSD drives produced by Fusion-IO and at the same time delivering much better endurance for the drives themselves. Furthermore, FusionIO flash memory solutions increase transactional database performance. MariaDB includes specialized improvements for FusionIO devices, leveraging a feature of the NVMFS filesystem on these popular, high performance solid state disks. Using this feature, MariaDB 10 can

  [Read more...]
Fun with Bugs #32 - some bugs I've reported in March
+0 Vote Up -0Vote Down
Comparing to the previous month I was not really productive bug reporter in March 2014 (partially because I spent few days at a nice FLOSS UK conference where I tried to give a session on PERFORMANCE_SCHEMA). Just 12 reports, of them 5 documentation requests are already closed. There are some interesting reports among other 7 to write about though.

But let me start with good (or not entirely good) news about my older report, Bug #71858 (easy way to crash MySQL with single SELECT

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