Menu Close

Category: performance

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!

MySQL Fabric – Part 1 – Installing

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances. It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a highly available system. To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial: fabric1 (192.168.0.200) – fabric mysql1 (192.168.0.201) – mysql master mysql2 (192.168.0.202) – mysql slave mysql3 (192.168.0.203) – mysql slave Note: I’m running CentOS 6.5 on all servers. 1. Add mysql repo on all 4 machines, please read Installing latest version of MySQL via yum for more info: rpm -i http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm yum update 2. Install mysql mysql-server mysql-utilities: yum install mysql mysql-server . . .

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!

Why Does MySQL Replication Delays?

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen: MySQL replication: most important config parameters for performance on slave server? mysql replication delay very serious See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post: “How Does MySQL Replication Works?” How you can see, there is a big difference related to the entry point of data on master and slave, while the master has multiple simultaneously threads inserting/updating/deleting data, on the slave is just a single one responsible for deal with all these transactions Let’s imagine that one transaction (an UPDATE for example) took 50 seconds to be . . .

Liked? Help and Share!

Using the Mysql FullText Index Search

Today let’s talk about a resource very useful on MySQL, the FullText Index and Search This resource is very powerful, today on versions 5.5 is just available to MyISAM engine, but, like we can see on MySQL FullText documentation, it will be available also to InnoDB on MySQL 5.6 Usually when we want to search for a word or expression, we use LIKE ‘%word%’, in case we are looking for more than one word we use LIKE ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with FullText Index the syntax is easy, MATHC() … AGAINST (), where MATCH we specified the name(s) of column(s) which we are looking for, yes, we . . .

Liked? Help and Share!

MySQL query optimize with query cache

  EDIT: Please note that query cache is been a know scalability issue for MySQL in high concurrency environments.  Query cache has been removed from MySQL on version 8.0 . Use with care!   Today let’s talk about a very cool tool of MySQL, the query cache. The query cache saved the SELECT results, that’s already executed their raw data have not changed thus making the response time of query much more optimized, because it will fetch from memory rather than disk. It can work in 3 different ways, in our my.ini within the group [mysqld]: query_cache_type = 0 Off query_cache_type = 1 On to all the query’s query_cache_type = 2 On demand But how best to use the query cache? Considering that every time you change data in a table, it . . .

Liked? Help and Share!