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

Displaying posts with tag: innodb (reset)

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; …
  [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; …

  [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 …


  [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 …

  [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 …

  [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 …

  [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 …

  [Read more...]
InnoDB Spatial Indexes in 5.7.4 LAB release
Employee_Team +3 Vote Up -0Vote Down

With MySQL 5.7.4 LAB release, InnoDB now supports “Spatial Index” on geometry data.

Before this new feature, InnoDB stores geometry data as BLOB data, and only prefix index can be created on the spatial data. It is very inefficient when comes to spatial search, especially when it comes to complex geometry data. In most cases, table scan are the only way to get the result. This all changed with InnoDB spatial index, which is implemented as R-tree, any spatial search becomes far more efficient.

InnoDB spatial index can be used with all existing syntax that has been developed for MyISAM spatial index. In addition, InnoDB spatial index …

  [Read more...]
on io scheduling again
+1 Vote Up -0Vote Down

Most of database engines have to deal with underlying layers – operating systems, device drivers, firmware and physical devices, albeit different camps choose different methods.
In MySQL world people believe that InnoDB should be handling all the memory management and physical storage operations – maximized buffer pool space, adaptive/fuzzy flushing, crash recovery getting faster, etc. That can result in lots of efficiency wins, as managing everything with data problem in mind allows to tune for efficiency and performance.

Other storage systems (though I hear it from engineers on different types of problems too) like PostgreSQL or MongoDB consider OS to be much smarter and …


  [Read more...]
InnoDB Crash Recovery Improvements in MySQL 5.7
Employee_Team +2 Vote Up -0Vote Down

Background

InnoDB is a transactional storage engine. Two parts of the acronym ACID (atomicity and durability) are guaranteed by write-ahead logging (WAL) implemented by the InnoDB redo log.

A statement within a user transaction can consist of multiple operations, such as inserting a record into an index B-tree. Each low-level operation is encapsulated in a mini-transaction that groups page-level locking and redo logging. For example, if an insert would cause a page to be split, a mini-transaction will lock and modify multiple B-tree pages, splitting the …

  [Read more...]
10 Newer Entries Showing entries 91 to 100 of 822 10 Older Entries

Planet MySQL © 1995, 2015, 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.