Menu Close

Category: InnoDB

Forcing deadlock rollback victim transaction

If you use a storage engine that supports transactions, you probably have faced or heard of deadlock’s. From MySQL Documentation: “Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.” At work, we had an important job that sometimes were failing due to dead lock. I wanted to enhance it, so it will do what the documentation says ( Retry the transaction ). In order to do that, I wanted to have a scenario where I was able to reproduce the deadlock and the victim transaction was the one from the job I was fixing. Create a deadlock is simple, you just need to have 2 sessions that each one holds a lock that the other is waiting for. For . . .

Liked? Help and Share!

MySQL & NoSQL – Memcached Plugin

Many of you have already heard about NoSQL databases and one of the the most used tool is Memcached, where you add a cache layer between the application and database. Since MySQL version 5.6, a new plugin is available to do the integration between MySQL and Memcached. On this article, we will learn how to install it on linux, and some basic configurations of it. Pre-requirements: Install libevent Installation: To install memcached support we will need to create a few tables responsible for MySQL and memcached integration. MySQL already includes the file which creates those tables (innodb_memcached_config.sql), you can find this file in a sub folder of your basedir. To discover where is your basedir, run the bellow command: mysql> SHOW VARIABLES LIKE ‘basedir’; +—————+——-+ | Variable_name | Value | . . .

Liked? Help and Share!

Warm-up InnoDB Buffer Pool

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 . . .

Liked? Help and Share!