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

Displaying posts with tag: innodb (reset)

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+1 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table - 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

read more

InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN
+0 Vote Up -0Vote Down

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len:
  [Read more...]
Presenting MySQL 5.7 Performance & Benchmarks at Percona Live
Employee_Team +2 Vote Up -0Vote Down

I’ll speak about MySQL 5.7 Performance & Benchmarks during the incoming Percona Live and will be happy to share with you all our latest finding, improvements, benchmark results, open issues and many other stuff keeping our brains in constant activity ) As you know, there is no a “silver bullet” solution for MySQL Performance tuning.. – only by a good understanding of what is going inside of MySQL and InnoDB you may configure your MySQL server in the most optimal way for your workloads. And this topic is endless, and progressing every month with new solutions and features – all parties are working hard here, and I’m very

  [Read more...]
innodb_flush_logs_on_trx_commit and Galera Cluster
+0 Vote Up -0Vote Down

We deploy Galera Cluster (in MariaDB) for some clients, and innodb_flush_logs_on_trx_commit is one of the settings we’ve been playing with. The options according to the manual:

  • =0 don’t write or flush at commit, write and flush once per second
  • =1 write and flush at trx commit
  • =2 write log, but only flush once per second

The flush (fsync) refers to the mechanism the filesystem uses to try and guarantee that written data is actually on the physical medium/device and not just in a buffer (of course cached RAID controllers, SANs and other devices use some different logic there, but it’s definitely written beyond the OS space).

In a non-cluster setup, you’d always want it to be =1 in order to be ACID compliant and that’s also InnoDB’s default. So far so good. For cluster setups,

  [Read more...]
Presenting MySQL/InnoDB at Percona Live 2014
Employee_Team +1 Vote Up -0Vote Down

I will be presenting at Percona Live 2014 and I’m excited to share and discuss the latest and greatest features and improvements that we have made to MySQL/InnoDB in 5.7. Great performance improvements, there are some new exciting compression features that we are working on,  GIS support,  temporary table performance etc.. There is a long list. Also, we are always interested to hear about user issues and priorities so that we can address them and/or work them into our plan. Your feedback is very important for us, if you want to influence the direction of InnoDB development then you need to talk to me .

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

The MariaDB project is pleased to announce the immediate availability of MariaDB 10.0.9. This is a Release Candidate release.

Among other changes, XtraDB is now the default InnoDB implementation, Oracle’s InnoDB is included as a plugin and can be dynamically loaded if desired. Packages for Ubuntu 14.04 “trusty” and Debian “Sid” have also been added to the MariaDB Ubuntu and Debian repositories.

See the Release Notes and Changelog for detailed information on this release and the

  [Read more...]
Engineer duo from Google, LinkedIn join again for InnoDB talks
+2 Vote Up -0Vote Down

Google senior systems engineer Jeremy Cole is once again teaming with LinkedIn senior software engineer Davi Arnaut for two InnoDB-focused sessions at the upcoming Percona Live MySQL Conference and Expo 2014 this April 1-4 in Santa Clara, California.

The duo will present “InnoDB: A journey to the core II” on April 2 and “InnoDB: A  hands-on exploration of on-disk storage with

  [Read more...]
Optimizing InnoDB Transactions
Employee +2 Vote Up -0Vote Down

(This is a cross-post from the Engine Yard blog. The original article appears here.)

Here is a question I've actually been asked a few times:

"I am writing a batch processing script which modifies data as part of an ongoing process that is scheduled by cron. I have the ability to group a number of modifications together into a transaction, but I'm not sure what the correct number is?"

First off, I think that this question is interesting not just in the context of batch processing, but it equally applies to all parts of the application. If you are designing a high throughput system for MySQL, there are actually some potential pain points that you can design your way around.

Potential Pain Points

Here are the situations where the

  [Read more...]
Fun with Bugs #30 - quick review of my reports in February, 2014
+0 Vote Up -0Vote Down
I've got only one comment to my previous post about deadlock, and it was more like a hint based on a different use case, not a real explanation. So far there is nobody who wants to get free beer... Maybe this is even good, as I do not go to the conference and BOF I've submitted will be supervised by my colleague Przemysław Malkowski. But you still have entire month till the conference to get a chance for a beer from him (we'll arrange this somehow).

In the meantime I'd like to review bug reports for MySQL server (few) and fine manual (many) that I've submitted in February, 2014. 22 in total, one

  [Read more...]
The MySQL ARCHIVE storage engine – Alternatives
+0 Vote Up -0Vote Down

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data

  [Read more...]
Magic deadlock: what locks are really set by InnoDB?
+0 Vote Up -0Vote Down
Megabytes of text had been written already on InnoDB locking and deadlocks. Still, even very simple cases of deadlocks while working with a table having only one row sometimes make people wonder what happened and why.

Today I want to check if this topic is explained well in the manual and existing blog posts and understood properly. So, it's an exercise for my dear readers and those who like to report bugs as much as I do.

Let's consider a very simple example. In session #1 with default transaction isolation level execute the following:
CREATE TABLE `tt` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tt values(1,1); -- insert a row there
select * from tt; -- check that











  [Read more...]
Abdel-Mawla Gharieb: Online DDL vs pt-online-schema-change
+2 Vote Up -0Vote Down

One of the most expensive database operations is performing Data Definition Language (DDL, e.g. CREATE, DROP, ALTER, etc.) statements, specially, the ALTER statements because MySQL blocks the entire table for both reads and writes while modifying the table.

For the huge tables, this might take hours to get the table changed which affects the application, so that, a good planning is required for such operations in order to avoid doing these changes during the peak times. For those people who have 24/7 services or limited maintenance window, DDL on huge tables is a really nightmare.

Percona developed a very good tool called pt-online-schema-change (version 2.2.6 at the time of writing this article) to perform such operations

  [Read more...]
Introducing Trite: A tool for automating import of InnoDB tablespaces
+0 Vote Up -0Vote Down

Mysqldump is a fantastic tool for backing up and restoring small and medium sized MySQL tables and databases quickly. However, when databases surge into the multi-terabyte range restoring from logical backups is inefficient. It can take a significant amount of time to insert a hundred million plus rows to a single table, even with very fast I/O. Programs like MySQL Enterprise Backup and Percona XtraBackup allow non-blocking binary copies of your InnoDB tables to be taken while it is online and processing requests. XtraBackup also has an export feature that allows InnoDB file per tablespaces to be detached from the shared table space and imported
  [Read more...]
Migration of MTR suites to use InnoDB (continued …)
Employee_Team +2 Vote Up -0Vote Down

This is a continuation of my post on migration-of-mtr-suites-to-use-innodb . To set the context here is a quick recap.

MySQL 5.5 had the following changes with respect to the default engine

  • Default engine in the server changed from MyISAM to InnoDB
  • MTR modified to start server with the old default MyISAM. (This was required because historically most test results were recorded with MyISAM and many of them would fail if tests were run with the new server default,  InnoDB)
  • Tests retained as is in 5.5 and planned to migrate them to run with default engine in a future release

In MySQL 5.7 release we have started the migration project. Right in the beginning we realized that the switch of default engine had an unexpected side effect. MTR tests that

  [Read more...]
Ghosts of MySQL Past: Part 2
+0 Vote Up -0Vote Down

This continues on from my post yesterday and also contains content from my linux.conf.au 2014 talk (view video here).

Way back in May in the year 2000, a feature was added to MySQL that would keep many people employed for many years – replication. In 3.23.15 you could replicate from one MySQL instance to another. This is commonly cited as the results of two weeks of work by one developer. The idea is simple: create a log of all the SQL queries that modify the database and then replay them on a slave. Remember, this is

  [Read more...]
The Effects of Database Heap Storage Choices in MongoDB
+0 Vote Up -0Vote Down

William Zola over at MongoDB gave a great talk called “The (Only) Three Reasons for Slow MongoDB Performance”. It reminded me of an interesting characteristic of updates in MongoDB. Because MongoDB’s main data store is a flat file and secondary indexes store offsets into the flat file (as I explain here), if the location of a document changes, corresponding entries in secondary indexes must also change. So, an update to an unindexed field that causes the document to move also causes modifications to every secondary index, which, as William points out, can be expensive. If a document has indexed an array, this

  [Read more...]
Fun with Bugs #29 - important bug fixes in MySQL 5.6.16
+3 Vote Up -0Vote Down
As you should already know, Oracle had released MySQL 5.6.16 officially on January 31, 2014. You can find all the details in official release notes. In this post I decided to concentrate on important fixes for bugs reported by community in 4 most important categories: InnoDB, replication, partitioning and optimizer.

Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself

  [Read more...]
What is FTS_BEING_DELETED.ibd
+0 Vote Up -0Vote Down

I currently have on a MySQL 5.6 database using innodb_file_per_table the following individual tablespace file.

schema/FTS_00000000000001bb_BEING_DELETED.ibd

The schema is all InnoDB tables, and there ARE NO Full Text Indexes. I cannot comment on if a developer has tried to create one previously.
I am none the wiser in explaining the ongoing use of these files, or if it can be/should be deleted.

On closer inspection there are infact a number of FTS files.

$ ls -al FTS*
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_BEING_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:20 FTS_00000000000001bb_BEING_DELETED.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:26 FTS_00000000000001bb_CONFIG.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29 16:21 FTS_00000000000001bb_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql 98304 Jan 29

  [Read more...]
Speaking at this year’s MySQL conference
+0 Vote Up -0Vote Down

For this year’s Percona Live: MySQL Conference and Expo 2014 (PLMCE), Davi Arnaut and I submitted a couple of co-speaking sessions proposals, both of which were accepted. Please come and see us and learn some more about InnoDB at the following sessions:

InnoDB: A journey to the core II

In this new installment of the series, we explore the most crucial structures for crash recovery and consistency, the redo and undo logs. This presentation dissects the structure and organization of InnoDB’s redo and undo logs, providing insight into how data changes occur at the InnoDB layer and how it guarantees that it can recover to a consistent state in

  [Read more...]
Transaction life cycle improvements in 5.7.3
Employee_Team +3 Vote Up -0Vote Down
This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another "virtual" list, the auto-commit non-locking  read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to  [Read more...]
Warm-up InnoDB Buffer Pool
+4 Vote Up -0Vote Down
Send to Kindle

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona

  [Read more...]
Transaction life cycle improvements in 5.7.3
Employee_Team +1 Vote Up -0Vote Down

This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another “virtual” list, the auto-commit non-locking read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write

  [Read more...]
InnoDB supports plugin parser in fulltext index
Employee_Team +4 Vote Up -0Vote Down

InnoDB Fulltext Search now supports plugin parser in MySQL 5.7.3 release. It is a compatible feature as for MyISAM Fulltext Search. So the syntax and usage remain to be largely the same.

A parser plugin can operate in either of two roles:

a) The plugin can replace the built-in parser. In this role, the plugin reads the input to be parsed, splits it up into words, and passes the words to the server (either for indexing or for word accumulation).

b) The plugin can act in conjunction with the built-in parser by serving as a front end for it. In this role, the plugin extracts text from the input and passes the text to the parser, which splits up the text into words using its normal parsing rules.

If you want to write your own full text plugin, please refer to http://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plugins.html.

If you have a

  [Read more...]
One more InnoDB gap lock to avoid
+1 Vote Up -0Vote Down

While troubleshooting deadlocks for a customer, I came around an interesting situation involving InnoDB gap locks. For a non-INSERT write operation where the WHERE clause does not match any row, I expected there should’ve been no locks to be held by the transaction, but I was wrong. Let’s take a look at this table and and example UPDATE.

mysql> SHOW CREATE TABLE preferences \G
*************************** 1. row ***************************
       Table: preferences
Create Table: CREATE TABLE `preferences` (
  `numericId` int(10) unsigned NOT NULL,
  `receiveNotifications` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`numericId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*)
  [Read more...]
MySQL 5.7.3: Deep dive into 1mil QPS with InnoDB & Memcached
Employee_Team +2 Vote Up -0Vote Down

As you probably already know, in MySQL 5.7.3 release, InnoDB Memcached reached a record of over 1 million QPS on a read only load. The overview of the benchmark and testing results can be seen in an earlier blog by Dimitri. In this blog, I will spend sometime on the detail changes we have made to achieve this number.

First thanks to Facebook's Yoshinori with his bug#70172 that brought our attention to this single commit read only load test. We have been focussing on operation with large batch size. This bug prompted us to do a series of optimization on single commit read only queries and these optimizations eliminate almost all major bottlenecks from the InnoDB Memcached plugin itself.

  [Read more...]
innodb_stats_on_metadata and slow queries on INFORMATION_SCHEMA
+2 Vote Up -0Vote Down

INFORMATION_SCHEMA is usually the place to go when you want to get facts about a system (how many tables do we have? what are the 10 largest tables? What is data size and index size for table t?, etc). However it is also quite common that such queries are very slow and create lots of I/O load. Here is a tip to avoid theses hassles: set innodb_stats_on_metadata to OFF.

This is a topic we already talked about, but given the number of systems suffering from INFORMATION_SCHEMA slowness, I think it is good to bring innodb_stats_on_metadata back on the table.

The problem

Let’s look at a system I’ve seen recently: MySQL 5.5, working set fitting

  [Read more...]
how MySQL engineering broke the backups
+0 Vote Up -0Vote Down

MySQL has exceptional track of record by introducing minor fixes that cause major breakages. Though usually I could blame naiveté of engineers, who did not really ever have to deal with production implications, but lately I can start sensing various business implications against open-source offerings.

As an original author of mydumper I really cannot get out of my mind that 5.5 and 5.6 metadata locking changes are there to screw with anyone who is building a backup solution using stable snapshot views of MySQL (for example, mysqldump –single-transaction, the golden standard of backing things up in MySQL world).

As seen in a bug #71017 (palindrome!) filed by my esteemed colleague Eric, newly

  [Read more...]
MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin
Employee_Team +5 Vote Up -0Vote Down

Or I could place in the title – “Yes, we done it!”

After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished

While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:

  [Read more...]
InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity
+0 Vote Up -0Vote Down

In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s responsible for performing flushing operations. Page_cleaner performs flushing of the dirty pages from the buffer pool based on two factors:
- access pattern  -  the least recently used pages will be flushed by LRU flusher from LRU_list when buffer pool has no free pages anymore;
- age – the oldest modified non-flushed pages are part of flush_list structure and will be flushed by flush_list flusher based on several heuristics.

There is a good overview of the page_cleaner and also here you may find some details about



  [Read more...]
Recalculating InnoDB Persistent Statistics - a Story of the Bug Report
+0 Vote Up -0Vote Down
One of the first posts in this blog was about reporting MySQL bugs "properly", in a way that maximizes chances for it to be processed really soon. I had written the following there:
"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"
Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?

It should NOT be the case. Let's


  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 748 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.