Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 773 10 Older Entries

Displaying posts with tag: innodb (reset)

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 successfully
ibdconnect modifies content of ibdata1, so checksums are wrong now.










  [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" target="_blank) outfitted with the InnoDB storage engine with two NoSQL alternatives — basic MongoDB

  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 773 10 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.