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

Displaying posts with tag: innodb (reset)

C bitfields considered harmful
+0 Vote Up -0Vote Down

In C (and C++) you can specify that a variable should take a specific number of bits of storage by doing “uint32_t foo:4;” rather than just “uint32_t foo”. In this example, the former uses 4 bits while the latter uses 32bits. This can be useful to pack many bit fields together.

Or, that’s what they’d like you to think.

In reality, the C spec allows the compiler to do just about anything it wants with these bitfields – which usually means it’s something you didn’t expect.

For a start, in a struct -e.g. “struct foo { uint32_t foo:4; uint32_t blah; uint32_t blergh:20; }” the …

  [Read more...]
Resizing the InnoDB Buffer Pool Online
Employee_Team +2 Vote Up -0Vote Down

As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7.5.

You can now use the "SET GLOBAL …

  [Read more...]
How to deal with MySQL deadlocks
+0 Vote Up -0Vote Down

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. Though an occasional deadlock is not something to worry about, frequent occurrences call for attention.

Before MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. But with Percona Toolkit’s …

  [Read more...]
When your query is blocked, but there is no blocking query - Part 3
+0 Vote Up -0Vote Down

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the …



  [Read more...]
When your query is blocked, but there is no blocking query
+1 Vote Up -0Vote Down

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state.

Example:

mysql [information_schema] > select trx_id,trx_state 
    -> from information_schema.innodb_trx;
+--------+-----------+
| trx_id | trx_state |
+--------+-----------+
| 7974 | LOCK WAIT |
| 7962 | RUNNING |
+--------+-----------+
2 rows in set (0.00 sec)


Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my …












  [Read more...]
MySQL 5.6 Full Text Search Throwdown: Webinar Q&A
+0 Vote Up -0Vote Down

Yesterday (Oct. 22) I gave a presentation titled “MySQL 5.6 Full Text Search Throwdown.” If you missed it, you can still register to view the recording and my slides.

Thanks to everyone who attended, and especially to folks who asked the great questions. I answered as many as we had time for during the session, …

  [Read more...]
MariaDB 10.1.1: Monitoring progress and temporal memory usage of Online DDL in InnoDB
+0 Vote Up -0Vote Down

Introduction

Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.

  1. InnoDB::ha_prepare_inplace_alter_table(..)
  2. InnoDB::ha_inplace_alter_table(..)
  3. InnoDB::ha_commit_inplace_alter_table(..)
  4. mysql_rename_table(..)

InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement …

  [Read more...]
Innodb transaction history often hides dangerous ‘debt’
+1 Vote Up -0Vote Down

In many write-intensive workloads Innodb/XtraDB storage engines you may see hidden and dangerous “debt” being accumulated – unpurged transaction “history” which if not kept in check over time will cause serve performance regression or will take all free space and cause an outage. Let’s talk about where it comes from and what can you do to avoid running into the trouble.

Technical Background: InnoDB is an MVCC engine which means it keeps multiple versions of the rows in the database, and when rows are deleted or updated they are not immediately removed from the database but kept for some time – …

  [Read more...]
InnoDB: Supporting Page Sizes of 32k and 64k
Employee_Team +1 Vote Up -0Vote Down

In the new InnoDB lab release we support page sizes of 32k and 64k. This gives users even more choices on the page size, allowing you to further customize InnoDB for your particular workload.

There are some things worthy of note related to this new feature:

  1. The extent size changes when the innodb_page_size is set 32k or 64k.
    The extent size is 2M for 32k page sizes, and 4M for 64k page sizes (the extent size is 1M for 4k, 8k, and 16k page …
  [Read more...]
Recover orphaned InnoDB partition tablespaces in MySQL
+0 Vote Up -0Vote Down

A few months back, Michael wrote about reconnecting orphaned *.ibd files using MySQL 5.6. I will show you the same procedure, this time for partitioned tables. An InnoDB partition is also a self-contained tablespace in itself so you can use the same method described in the previous post.

To begin with, I have an example table with a few orphaned partitions and we will reconnect each partition one by one to the original table.

mysql [localhost] {msandbox} (recovery) > SHOW CREATE TABLE t1 G …
  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 840 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.