Menu Close

Category: optimize

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!

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!