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 34 Next 4 Older Entries

Displaying posts with tag: InnoDB plugin (reset)

Comprehensive How-To for Enabling the Standard InnoDB Plugin in MariaDB and MySQL
+4 Vote Up -0Vote Down

I’m always switching back-and-forth between the 2 different InnoDB flavors in MariaDB – XtraDB+ and the standard InnoDB plugin, so I thought I’d simply post all of the various combinations in a single place. (And then I cover enabling the InnoDB Plugin in MySQL, since it’s an option in 5.1.) [Addition: Thanks to Andrew and Sergei for the tips on shortening plugin-load=. The changes are reflected below.]

Note: Below is for Windows. For Linux, simply change “.dll” to “.so” where appropriate.

MariaDB 10.0:

Do not add anything, as the standard InnoDB plugin is the current default (as of 10.0.3, although I do anticipate this changing in the near future, and I’ll update the post accordingly when that happens).

MariaDB 5.5:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB
  [Read more...]
How to Build MariaDB 10.0 on Windows from Source Code
+2 Vote Up -0Vote Down

I just wanted to share my steps for building MariaDB 10.0 on Windows (10.0.1 in this case):

cd maria-10.0.1
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

That’s it! The zip file is created right there in bld/.

Hope this helps.

 

InnoDB Plugin Version Numbering in MySQL and MariaDB
+2 Vote Up -0Vote Down

As some of you may or may not know, I’ve maintained a list of all InnoDB Plugin versions as they’ve historically contained a different version (entirely different numbering scheme) than the MySQL distribution they were included with.

This list was most helpful for troubleshooting various InnoDB issues when the plugin may (or may not) have been involved, and/or for benchmarking, etc. And it’s fair to say it was more useful when the InnoDB plugin was not the mainstream, which it is now.

However, with the latest releases, in MySQL and MariaDB, the “InnoDB Version” simply matches the “MySQL Version”. These “latest releases” include: MySQL 5.6.10, MySQL 5.5.30, MySQL 5.1.68, and MariaDB 5.5.30

Of course this isn’t the most newsworthy story, but having maintained this

  [Read more...]
MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
+4 Vote Up -0Vote Down

MySQL 5.6 vs MySQL 5.5 & the Star Schema Benchmark

So far most of the benchmarks posted about MySQL 5.6 use the sysbench OLTP workload.  I wanted to test a set of queries which, unlike sysbench, utilize joins.  I also wanted an easily reproducible set of data which is more rich than the simple sysbench table.  The Star Schema Benchmark (SSB) seems ideal for this.

I wasn’t going to focus on the performance of individual queries in this post, but instead intended to focus only on the overall response time for answering all of the queries in the benchmark. I got some strange results, however, which

  [Read more...]
1.5x … 9x faster queries with PHP and MySQL 5.6, really?
Employee +0 Vote Up -0Vote Down

I am telling no secret saying MySQL 5.6 GA can be expected to be released soon, very soon. Time to test one of the improvements: MySQL 5.6 speaks SQL and Memcache protocol. In your PHP MySQL apps, try using the Memcache protocol to query MySQL. A key-value SELECT ... FROM ... WHERE pk = <key> can become 1.5x-2x faster, an INSERT INTO table(pk, ...) VALUES (<key> ...) can become 4.5x-9x faster, says the InnoDB team! Read on: background, benchmark, usage, PECL/mysqlnd_memcache,

  [Read more...]
How to Enable the Original InnoDB Plugin in MariaDB 5.5
+7 Vote Up -0Vote Down

As I mentioned here, there is a slight change for enabling the [original] InnoDB Plugin in MariaDB 5.5 (as compared to how you would enable it in 5.1).

Remember, in MariaDB 5.5, if you do not “enable” (i.e., add anything to the config file to do so) the InnoDB Plugin in MariaDB 5.5, you’ll end up with XtraDB+ for your InnoDb plugin. However, if you do “enable” the InnoDB plugin, then you end up with the original InnoDB plugin provided by Oracle/InnoDB.

The change is that the plugin file (.dll for Windows, .so file for Linux) which was previously named “ha_innodb_plugin.dll” is now just “ha_innodb.dll”.

Thus, if you previously enabled the plugin with (would have been in a 5.1 instance):

[mysqld]
ignore_builtin_innodb
  [Read more...]
How to Build MariaDB 5.5 on Windows from Source Code
+7 Vote Up -0Vote Down

I built MariaDB 5.5.24 on Windows yesterday from source, so I just wanted to share my steps.

Here is the short version:

bzr branch lp:maria/5.5 maria-5.5
cd maria-5.5
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

Done! Nice, neat zip file is created right there.

Here is the longer version with outputs for those interested:

C:\mariadb-5.5>bzr branch lp:maria/5.5 maria-5.5
Connected (version 2.0, client Twisted)
Authentication (publickey) successful!
Secsh channel 1 opened.
Branched 3418 revision(s).

C:\mariadb-5.5>cd maria-5.5

C:\mariadb-5.5\maria-5.5>mkdir bld

C:\mariadb-5.5\maria-5.5>cd bld

C:\mariadb-5.5\maria-5.5\bld>cmake .. -G "Visual Studio 10"
-- Check for working C compiler using: Visual Studio 10
-- Check for working C compiler using: Visual Studio 10 -- works
-- Detecting C compiler
  [Read more...]
Optimizing neighbor flush behavior
Employee +4 Vote Up -0Vote Down

The performance of flush_list flushing of InnoDB decides the basic performance for modifying workloads. So, it is important to optimize the flush behavior. In this post we’ll consider how to optimize the neighbor-flushing behavior.

Factor 1: Characteristics of storage

Depending on the characteristics of your storage’s throughput for write IO, you can term your storage as either “write amount bound” or “write times bound”. The minimum unit of the InnoDB datafile is page size (16KB or less). And InnoDB attempts to combines them in a single IO up to 1 extent (1MB) maximum, if they are contiguous.

<one HDD>:  Almost “write times bound”. Because head-seek time is the most effective factor for access time of HDD. And

  [Read more...]
New flushing algorithm in InnoDB
Employee +4 Vote Up -0Vote Down

In MySQL labs release April 2012 we have reworked the flushing heuristics in InnoDB. In this post I’ll give an overview of what we have changed and the various configuration variables we have introduced to fine tune the flushing algorithm. If you are interested in finding out how our new flushing algorithm fares in benchmarks you can get these details in Dimitri’s well-explained blog here.

Flushing means writing dirty pages to disk. I have explained in some detail about adaptive_flushing and types of flushing in my previous notes. Please go through these

  [Read more...]
InnoDB persistent stats got a friendly UI
Employee +1 Vote Up -0Vote Down

After introducing InnoDB persistent statistics in MySQL 5.6, in this April Labs release we have dressed it up in a nice UI and refactored the internals a bit to make the code more elegant and straight-forward.

The persistent stats are now controlled globally and can also be overridden at table level, should any table require a different behavior.

Global

The server global flag –innodb-stats-persistent (boolean) now controls whether all InnoDB tables use persistent statistics or not. Keep in mind that if a table is using persistent stats then its statistics will not be updated automatically and you are responsible for running ANALYZE TABLE periodically, whenever you think the table contents has changed too much. Thus the default for

  [Read more...]
Tracking Server Variables, Documentation, Manuals, Changelogs for MySQL, InnoDB, MariaDB, and XtraDB
+1 Vote Up -0Vote Down

I find myself constantly looking up server variables (and manuals and changelogs) for MySQL, MariaDB, and XtraDB, which versions they are in, and so forth. So I finally created a couple pages which contain the links to all of these various bits of information across the various flavors of MySQL.

I’ve been using them every day, so I thought some others might want to bookmark these as well.

I’ve created the following:

o Changelogs
o Documentation
o Server Variables
o InnoDB Plugin Versions

The Changelogs page contains links for MySQL 3.23 up through 5.6, MariaDB 5.1 – 5.3, XtraDB 5.1 – 5.5, Xtrabackup 1.3 – 1.6,




  [Read more...]
Improving InnoDB memory usage continued
Employee +3 Vote Up -0Vote Down

Continues from Improving InnoDB memory usage.

Here are some numbers from the fixups described in the above article:

The workload consists of 10 partitioned tables, each one containing 1000 partitions. This means 10’000 InnoDB tables. We truncate the tables, then restart mysqld and run:

1. INSERT a single row into each of the 10 tables
2. SELECT * from each table
3. FLUSH TABLES (this causes the tables to be closed and reopened on the next run)
4. wait for 10 seconds

we repeat the above steps 10 times. Here is the total memory consumption by mysqld with 1GB InnoDB buffer pool during the workload:




  [Read more...]
Improving InnoDB memory usage
+4 Vote Up -0Vote Down

Last month we did a few improvements in InnoDB memory usage. We solved a challenging issue about how InnoDB uses memory in certain places of the code.

The symptom of the issue was that under a certain workloads the memory used by InnoDB kept growing infinitely, until OOM killer kicked in. It looked like a memory leak, but Valgrind wasn’t reporting any leaks and the issue was not reproducible on FreeBSD – it only happened on Linux (see Bug#57480). Especially the latest fact lead us to think that there is something in the InnoDB memory usage pattern that reveals a nasty side of the otherwise good-natured Linux’s memory manager.

It turned out to be an interesting

  [Read more...]
InnoDB Plugin Version History
+4 Vote Up -0Vote Down

I’m often wondering what version of the InnoDB Plugin is included with which version of MySQL (or MariaDB). The MySQL changelogs used to denote which version of the InnoDB plugin was included with that particular release of MySQL, but sadly this is no longer the case.

Therefore I’ve compiled a comprehensive list which contains all of this info, and then some (and note all InnoDB Plugin changelog links are provided at the bottom).

Hope you find it helpful

MySQL 5.6:

MySQL     Plugin     Status Date 5.6.4 1.2.4 Milestone 7 12/20/2011 5.6.3 1.2.3 Milestone 6 10/03/2011 5.6.2 1.2.2 Milestone 5       04/11/2011

MySQL 5.5:

MySQL      [Read more...]
Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At
+5 Vote Up -0Vote Down

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1
  [Read more...]
Shortened warm-up times with a preloaded InnoDB buffer pool
Employee +7 Vote Up -0Vote Down

Are you running an InnoDB installation with a many-gigabytes buffer pool(s)? Does it take too long before it goes back to speed after a restart? If yes, then the following will be interesting to you.

In the latest MySQL 5.6 Labs release we have implemented an InnoDB buffer pool(s) dump and load to solve this problem.

The contents of the InnoDB buffer pool(s) can be saved on disk before MySQL is shut down and then read in after a restart so that the warm up time is drastically shortened – the buffer pool(s) go to the state they were before the server restart! The time needed for that is roughly the time needed to read data from disk that is about the size of the buffer pool(s).

Lets dive straight into the commands to perform various dump/load operations:

The buffer pool(s) dump can be done at any time when MySQL is

  [Read more...]
Reasons to use MySQL 5.5 Presentation
+1 Vote Up -1Vote Down

I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

Reasons to use MySQL 5.5
+0 Vote Up -0Vote Down

Download PDF Presentation
Download Audio

There are a number of significant new features in MySQL 5.5 including semi-synchronous replication, SIGNAL and RESIGNAL, the PERFORMANCE_SCHEMA, additional STATUS variables, new partitioning options, different default storage engine, better UTF8 support and removal of deprecated functions just to list key considerations.

However some of the performance improvements are worth the



  [Read more...]
IOPS, innodb_io_capacity, and the InnoDB Plugin
+4 Vote Up -0Vote Down
In the InnoDB plugin, a new variable was added named innodb_io_capacity, which controls the maximum number of I/O operations per second that InnoDB will perform (which includes the flushing rate of dirty pages as well as the insert buffer (ibuf) batch size).

First off, let me just say this is a welcome addition (an addition provided by the Google Team, fwiw).

However, before this was configurable, the internal hard-coded value for this was 100. But when this became configurable, the default was increased to 200.

For many systems, this is not an issue (i.e., the overall system can perform 200 IOPS).

However, there are still many disks (which is often the







  [Read more...]
Tips and Tricks for Faster DDL
+0 Vote Up -0Vote Down

Data Dictionary Language (DDL) operations have traditionally been slow in MySQL. Any change to the table definition would be implemented by creating a copy of the table and index layout with the requested changes, copying the table contents row by row, and finally renaming tables and dropping the original table.

The InnoDB Plugin for MySQL 5.1 implements a more efficient interface for creating and dropping indexes. Indexes can be created or dropped without rebuilding the entire table.

Speeding up Bulk INSERT, UPDATE and DELETE Operations

Normally, InnoDB would update all indexes of a table when rows are inserted or deleted. If you update an indexed column, InnoDB would have to delete the old value and insert the new value in the corresponding index. If you update a primary key column, the row would be deleted and inserted

  [Read more...]
Leveraging the InnoDB Plugin
+3 Vote Up -0Vote Down

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

  • Backup all InnoDB tables via mysqldump
  • Drop InnoDB tables
  • Verify InnoDB not used
  • Stop MySQL
  • Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
  • Remove ibdata? files
  • Start MySQL
  • Create Tables
  • Reload Data
  • Verify InnoDB Operation
    • The primary

  [Read more...]
Five reasons to upgrade to MySQL 5.5
+3 Vote Up -0Vote Down

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary

  [Read more...]
Ease of Switching to the InnoDB Plugin and the Numerous Benefits
+10 Vote Up -0Vote Down

In my last post, I discussed how to troubleshoot InnoDB locks using the new InnoDB Plugin’s new Information Schema tables.

However, that got me to thinking about how many MySQL 5.1 users who have still not switched to use the plugin as opposed to the built-in version of InnoDB.

There are many advantages to using the plugin as opposed to the built-in version (aside from just the new I_S tables, and more importantly, numerous performance enhancements), and it’s breeze to set up, so I wanted to provide a quick start guide to using the new InnoDB plugin.

Note that

  [Read more...]
Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables
+4 Vote Up -0Vote Down

Tracking down InnoDB lock information using the new Information Schema tables provided with the InnoDB plugin has never been easier.

Long story short, the other day I was trying to identify what transaction was holding the lock for a particular UPDATE. The UPDATE would not complete and kept timing out with “Lock wait timeout exceeded; try restarting transaction”.

Of course I checked the output of SHOW ENGINE INNODB STATUS. From that output, I could tell it was the replication thread holding the lock, *since* it was the only other transaction running. But I could not verify this with output, it just had to be the case.

Next I enabled the InnoDB lock monitor, and examined that output too. Here, it showed more detail on the locks being held, however, it only shows the first 10 locks held by the

  [Read more...]
Time for an update
+0 Vote Up -0Vote Down

Sorry all I have been very busy transitioning into my new position for the last couple of months.

I want to just give a small note – it was very good to meet a bunch of you at the conference back in April!

However, on to more serious business – I currently work for Electronic Arts, and at this point we are actually looking for Sr. MySQL DBA’s. So if you or someone you know is interested, feel free to email me directly at nwesterlund – at – ea.com . We will of course treat everything confidentially.

On a different note – the InnoDB plugin rocks in performance compared to the built-in version! I have seen test results (from my own test) ranging from 15% to 70% performance improvement! Good job there!

Couldn’t load plugin named ‘innodb’
+0 Vote Up -0Vote Down

As part of reviewing storage engines for my work on the upcoming Expert PHP and MySQL book, I finally had an excuse to try out the InnoDB Plugin for MySQL which is now conveniently included with MySQL 5.1 since 5.1.38.

Following the MySQL 5.1 Reference Manual instructions at 13.6. The InnoDB Storage Engine I included the bare minimum as documented to my my.cnf.

[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=ha_innodb_plugin.so ;innodb_cmp=ha_innodb_plugin.so ;innodb_cmp_reset=ha_innodb_plugin.so ;innodb_cmpmem=ha_innodb_plugin.so ;innodb_cmpmem_reset=ha_innodb_plugin.so

However to my misfortune the following error occured.

091212 17:45:14
  [Read more...]
innodb_io_capacity
+0 Vote Up -0Vote Down

Doing some performance testing on some modern hardware comparing Innodb plugin 1.0.4 with stock Innodb. I'm running a sysbench transactions test (reads and writes) with 200M rows in my table (table size is around 46G, RAM is 16G, buffer pool is set to 12G).

I was puzzled to see the innodb plugin to be decent, but not really as great as I expected, I was doing about ~6100 RW operations a second (individual statements within transactions). Then I compared it to the stock innodb and shockingly I got ~7K ops. I thought about what I tuned that was different in the plugin and came up with the innodb_io_capacity.

-->

read more

Only God can make random selections
Employee +0 Vote Up -0Vote Down

Recently, it was reported (see MySQL bug #43660) that “SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table”. The problem appeared to happen only on 64-bit systems, but not 32-bit systems. The bug turns out to be a case of mistaken identity. The real criminal here wasn’t the SHOW INDEXES or the ANALYZE command, but something else entirely. It wasn’t specific to 64-bit platforms, either. Read on for the interesting story about this mystery and its solution …

InnoDB estimates statistics for the query optimizer by picking random pages from an index. Upon detailed analysis, we found that the algorithm that picks random pages for estimation always picked the same page, thus producing the same result every time. This made it appear that the index cardinality was not

  [Read more...]
Software is Hard Sometimes …
Employee +0 Vote Up -0Vote Down

Some months ago, Google released a patch for InnoDB that boosts performance on multi-core servers. We decided to incorporate the change into the InnoDB Plugin to make everybody happy: users of InnoDB don’t have to apply the patch, and Google no longer has to maintain the patch for new versions of InnoDB. And it makes us at Innobase happy because it improves our product (as you can in this post about InnoDB Plugin release 1.0.3).

However, there are always technical and business issues to address. Given the low-level changes in the patch, was it technically sound? Was the patch stable and as rock solid as is the rest of InnoDB? Although it was written for the built-in InnoDB in MySQL 5.0.37, we needed to

  [Read more...]
Plug In for Performance and Scalability
+0 Vote Up -0Vote Down

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:

  • Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
  • More efficient memory allocation: ability to use more scalable platform memory allocator
  • Improved out-of-the-box scalability: unlimited concurrent thread execution by default
  • Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements

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