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 |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
1 row in set (0.00 sec)

If you have installed MySQL via your distro repository, the path will be as follow:

$basedir/share/mysql/innodb_memcached_config.sql

In case you have used MySQL binaries, the path will be as follow:

$basedir/share/innodb_memcached_config.sql

Now we will run this .sql file. By default, the script creates a test table on test database, but on our tests we will use the memcached db:

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql>

Now let’s create our table to store memcached data:

mysql> CREATE DATABASE IF NOT EXISTS memcached;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE memcached.dados LIKE test.demo_test;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE innodb_memcache.containers SET db_schema = 'memcached', db_table = 'dados' WHERE name = 'aaa' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DROP DATABASE test;
Query OK, 1 row affected (0.07 sec)

Next step is to install memcached plugin on MySQL. To do it we will use INSTALL PLUGIN command:

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.01 sec)

To verify if the plugin was successful installed, we can run the bellow command:

mysql> \! netstat -tunap | grep LIST | grep mysql
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      2032/mysqld         
tcp        0      0 :::11211                    :::*                        LISTEN      2032/mysqld         
tcp        0      0 :::3306                     :::*                        LISTEN      2032/mysqld         
mysql> 

Configuring and Using:

Now we will test memcached via some programming language, PHP in this case:

 [root@localhost memcache]# cat test1.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('key1', 'Testing memcached');
echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>
[root@localhost memcache]# php test1.php 
Value of key1 is:Testing memcached
[root@localhost memcache]# 

Now, let’s see what is stored on MySQL?

mysql> SELECT * FROM memcached.dados;
+------+-------------------+------+------+------+
| c1   | c2                | c3   | c4   | c5   |
+------+-------------------+------+------+------+
| key1 | Testing memcached |    0 |   19 |    0 |
+------+-------------------+------+------+------+
1 row in set (0.00 sec)

mysql> 

What happens if we change an entry manually on MySQL?

mysql> UPDATE memcached.dados SET c2 = 'Entry modified  directly on MySQL';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


[root@localhost memcache]# cat test2.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test2.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 


What about if we want to store the entries on a different MySQL table?
We just need to create a new table, add a new container and use the delimiter which is configured on config_options table from innodb_memcache database:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'table_map_delimiter';
+---------------------+-------+
| name                | value |
+---------------------+-------+
| table_map_delimiter | .     |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE memcached.dados2 LIKE memcached.dados;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('bbb', 'memcached', 'dados2', 'c1', 'c2', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.01 sec)

We have created a new table named dados2 and added a new container to refer to that table, which we named as bbb, now we just need to use that as prefix on memcached:

 [root@localhost memcache]# cat test3.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('@@bbb.key1', 'Should be stored on dados2 table');
echo 'Value of bbb.key1 is:' . $m->get('@@bbb.key1') . "\n";
?>
[root@localhost memcache]# php test3.php 
Value of bbb.key1 is:Should be stored on dados2 table
[root@localhost memcache]# 


mysql> SELECT * FROM memcached.dados2;
+------+----------------------------------+------+------+------+
| c1   | c2                               | c3   | c4   | c5   |
+------+----------------------------------+------+------+------+
| key1 | Should be stored on dados2 table |    0 |   22 |    0 |
+------+----------------------------------+------+------+------+
1 row in set (0.00 sec)

We also can map the table to store values into separate fields on the table.
Verify that on the config_options table we already have a separator configured:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'separator';
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+
1 row in set (0.00 sec)

This character we will use to store values into multiple columns. Let’s create a table and add it to a new container(we will specify the columns we want the values separated by comma ‘,’):

mysql> CREATE TABLE products (id varchar(128), name varchar(255), value varchar(15), c3 int, c4 bigint, c5 int, PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('products', 'memcached', 'products', 'id', 'name,value', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.00 sec)

Now, lets create an array of products and add them to memcached:

[root@localhost memcache]# cat test4.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$products = array(
array('1', 'TV', '1999,00'),
array('2', 'Hack', '399,00'),
array('3', 'Table', '599,00'),
array('4', 'Chair', '99,00')
);

foreach($products as $product)
{
	$key = '@@products.' . $product[0];
	$value = $product[1] . '|' . $product[2];
	$m->set($key, $value);
}

?>
[root@localhost memcache]# php test4.php 



mysql> SELECT * FROM memcached.products;
+----+----------+---------+------+------+------+
| id | nome     | valor   | c3   | c4   | c5   |
+----+----------+---------+------+------+------+
| 1  | TV       | 1999,00 |    0 |   23 |    0 |
| 2  | Hack     | 399,00  |    0 |   24 |    0 |
| 3  | Table    | 599,00  |    0 |   25 |    0 |
| 4  | Chair    | 99,00   |    0 |   26 |    0 |
+----+----------+---------+------+------+------+
4 rows in set (0.00 sec)

Server/Service restart:

Let’s see what happens if we have to restart MySQL service(Same apply to server reboot)?
Will the data remain available on memcached after it?

[root@localhost memcache]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost memcache]# cat test5.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test5.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 

That is it! Entries will remain available even after reboot’s/restart’s.

SELinux:

On environments where we have SELinux enabled, it can block memcached integration because MySQL is not allowed to listen on memcached port, where is a tip on how to allow it(I’m using CentOS as linux distro):

Search for entries containing mysqld and dinied keywords on /var/log/audit/audit.log , if you find any, type the bellow commands to create a new SELinux module to allow it:

type=AVC msg=audit(1421080542.433:31): avc:  denied  { name_bind } for  pid=1360 comm="mysqld" src=11211 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:memcache_port_t:s0 tclass=tcp_socket
type=SYSCALL msg=audit(1421080542.433:31): arch=c000003e syscall=49 success=no exit=-13 a0=2d a1=7f5fcc0409a0 a2=10 a3=7f5fe0ee275c items=0 ppid=1123 pid=1360 auid=0 uid=27 gid=27 euid=27 suid=27 fsuid=27 egid=27 sgid=27 fsgid=27 tty=pts0 ses=2 comm="mysqld" exe="/usr/sbin/mysqld" subj=unconfined_u:system_r:mysqld_t:s0 key=(null)


audit2why < /var/log/audit/audit.log

cd /root/
mkdir selinux-custom
cd selinux-custom
audit2allow -a -M mysql-memcache
semodule -i mysql-memcache.pp

Opcoes do memcached: In case you want to modify any memcached specific option, you can add the daemon_memcached_option to MySQL config file, for example, to change memcached port:

#no arquivo de configuração (normalmente my.cnf)
daemon_memcached_option="-p11222"

That is it, I hope you’ve learned how to install and configure MySQL and memcached plugin integration.

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 mysql-utilities
chkconfig mysqld on
/etc/init.d/mysqld start

3. On mysql1,mysql2,mysql3 add the follow to my.cnf:

[mysqld]
...
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

4. On mysql1 add a replication user for each mysql ip:


GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd';
GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.201 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.202 IDENTIFIED BY 'reppwd'; "
[root@mysql1 ~]# mysql -u root -e "GRANT REPLICATION SLAVE ON *.* TO replication@192.168.0.203 IDENTIFIED BY 'reppwd'; "

5. On mysql1 add the privileges to fabric user from fabric node ip:


GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';

[root@mysql1 ~]# mysql -u root -e "GRANT ALL ON *.* TO 'replication'@'192.168.0.200' IDENTIFIED BY 'reppwd';"

6. On mysql2 and mysql3, setup replication:

CHANGE MASTER TO MASTER_HOST='192.168.0.201', MASTER_USER='replication', MASTER_PASSWORD='reppwd', MASTER_AUTO_POSITION=1;
START SLAVE;

7. On fabric1, add the fabric MySQL user:


GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';

[root@fabric1 ~]# mysql -u root -e "GRANT ALL ON fabric.* TO 'fabric'@'localhost' IDENTIFIED BY 'fabricpwd';"

8. On fabric1,, configure user and password on [storage] and [servers] group on /etc/mysql/fabric.cfg :

[storage]
...
password = fabricpwd
...
[servers]
password = reppwd
user = replication

9. On fabric1, start the fabric db, it will ask to create a password, this password will be used on all next mysqlfabric commands:


mysqlfabric manage setup

[root@fabric1 ~]# mysqlfabric manage setup
[INFO] 1406131468.176740 - MainThread - Initializing persister: user (fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc: 
Repeat Password:
Password set.

10. On fabric1, start fabric:


mysqlfabric manage start &

11. On fabric1,, add a group:

mysqlfabric group create GLOBAL1

[root@fabric1 ~]# mysqlfabric group create GLOBAL1
Password for admin: 
Procedure :
{ uuid        = 5e4a6bdb-60f0-4e34-87ba-4c56b7616b35,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

12. On fabric1,, add mysql1, mysql2 and mysql3 to GLOBAL1 group:


mysqlfabric group add GLOBAL1 192.168.0.201
mysqlfabric group add GLOBAL1 192.168.0.202
mysqlfabric group add GLOBAL1 192.168.0.203

[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.201
Password for admin: 
Procedure :
{ uuid        = 39efb9c4-6195-4c41-aa02-0bfdc228bfe2,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.202
Password for admin: 
Procedure :
{ uuid        = c8babfb9-d836-44c0-b4fd-015cd1df8298,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group add GLOBAL1 192.168.0.203
Password for admin: 
Procedure :
{ uuid        = c86bba70-69ac-4923-9c54-1a8aaab6d97e,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}

13. On fabric1, get the uuid of your master:

mysqlfabric group lookup_servers GLOBAL1

[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}

14. On fabric1, add your master as master on GLOBAL1 group:

mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'

[root@fabric1 ~]# mysqlfabric group promote GLOBAL1 --slave_id='2e157d1e-1281-11e4-80dc-080027aa0242'
Password for admin: 
[WARNING] 1406131951.712366 - Executor-2 - Error () trying to process transactions in the relay log for candidate (('Command (START SLAVE SQL_THREAD, ()) failed: 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO', 1200)).
[INFO] 1406131951.824763 - Executor-2 - Master has changed from None to 2e157d1e-1281-11e4-80dc-080027aa0242.
Procedure :
{ uuid        = 733ae69d-fb12-447b-b86b-041703491315,
  finished    = True,
  success     = True,
  return      = True,
  activities  = 
}
[root@fabric1 ~]# mysqlfabric group lookup_servers GLOBAL1
Password for admin: 
Command :
{ success     = True
  return      = [{'status': 'PRIMARY', 'server_uuid': '2e157d1e-1281-11e4-80dc-080027aa0242', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.0.201'}, {'status': 'SECONDARY', 'server_uuid': '41d85bee-1281-11e4-80dc-080027e87bc6', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.202'}, {'status': 'SECONDARY', 'server_uuid': '472734d8-1281-11e4-80dc-0800274a710c', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.0.203'}]
  activities  = 
}
[root@fabric1 ~]# 

That is it, we now have our MySQL Fabric environment working and ready.
Watch out for my next few posts to learn more about MySQL Fabric.

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 versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;

Now, the next time we stop MySQL, a file named ib_buffer_pool will be created under the MySQL datadir (you can change the file name by using the innodb_buffer_pool_filename config).

Next step is to tell MySQL to read the this file and load the actual page data into the buffer pool, we will do it by adding a new entry named innodb_buffer_pool_load_at_startup on my.cnf (and also add innodb_buffer_pool_dump_at_shutdown to it, this way it will automatically dump it every time we shutdown the server):

[mysqld]
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Now we can restart our mysql server, as mention before, it will create a file with a content similar to the above:

[root@marcelodb data]# tail ib_buffer_pool
0,69618
0,69619
0,69620
0,69621
0,69622
0,69623
0,69624
0,69625
0,69626
0,69627

When we restart our server, we can see 2 new messages being written to MySQL logs:

2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54

This means that MySQL have wrote the file with the buffer pool pages.

2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01

This means that MySQL have finished to reload the pages content back to the InnoDB buffer pool.

You can controller the dump / reload progress through the Innodb_buffer_pool_dump_status and Innodb_buffer_pool_load_status:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------+
| Variable_name                  | Value                  |
+--------------------------------+------------------------+
| Innodb_buffer_pool_load_status | Loaded 5121/6441 pages |
+--------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140108 16:55:05 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)

If you wish, you can tell MySQL to dump/reload the buffer pool right now using innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.

If you use InnoDB as storage engine of any of you tables, that is a very cool practice to put in place to minimize the side effects of a server/service restart.

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:

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 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 applied on the master, during this time, others threads executed queries on the master, what is gonna happen when the slave receive and execute this transaction? yes, you are right, DELAY, all other pending transaction and the ones which got inserted while the slave is applying this UPDATE will need to wait until it finish to be applied.

If you use MySQL from version 5.6.3 and have your data partitioned per database, you can minimize this “bottleneck” using slave-parallel-workers, with this option you can config the number of SQL_THREAD'S to execute simultaneously transaction since they are in different databases.

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 can look for more then one column, we just need all this columns specified on our index and AGAINST is where we specify the word(s) which we are looking for, we can also, specified a search mode, but I will talk about it later

Let’s create our table and start it

CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`body` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM;
INSERT INTO `articles` VALUES (1,'MySQL Tutorial','DBMS stands for DataBase ...'),(2,'How To Use MySQL Well','After you went through a ...'),(3,'Optimizing MySQL','In this tutorial we will show ...'),(4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),(5,'MySQL vs. YourSQL','In the following database comparison ...'),(6,'MySQL Security','When configured properly, MySQL ...');

Let’s do our first query, looking for articles which approach ‘database’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.01 sec)

Now, let’s look for ‘database tutorial’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('database tutorial');

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
3 rows in set (0.01 sec)

Now, let’s search for articles which approach ‘MySQL’, just a detail, all of our articles contain the word ‘MySQL’

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL');

Empty set (0.00 sec)

Why? Let’s talk about Search Modes, by default, MySQL uses Natural Language mode, which tell us if the searched word match if 50% or more rows, the entire query doesn’t match.
Other good function with MySQL allow us to use, is change our search mode, let’s use Boolean mode:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('MySQL' IN BOOLEAN MODE);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

6 rows in set (0.00 sec)

Ok, but what is the difference between they? why use boolean mode? like says on name, is true or false, let’s do a query looking for ‘database’ but I don’t want to show rows which contain the word ‘tutorial’, is this difficult? no, let’s see:

mysql> select * from articles WHERE MATCH(title,body) AGAINST ('+database -tutorial' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
1 row in set (0.01 sec)

Is this for today, on next article, we will learn more about the boolean mode, changing the order of relevance

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 invalidates the cache of the same, as you indicated is a mapping of the frequency of tables updating and use the cache for those that do not have a great update/insert frequency, so that the cache will give a performance gain, and do not be overloading the server having to invalidate many queries.
For example, the table of system log, every user action it will insert new data in this table, so it is not a good candidate to be cached, since the table of the site news, receive updates two times a days, there’re a good candidate to be cached,

I’ll show in practice that we can achieve results with the query cache:

I’ve it set to ON DEMAND – query_cache_type = 2
I’ve a table with 100 million records, the option ON DEMAND are needed we specify that we want to select in SQL_CACHE:

    Running the query at first time:

mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
. . .
20 rows in set (2 min 15.20 sec)

At this time, warm up the cache with our consultation, he delayed the default time , but put it in cache, see the result of the same query:
mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
...
20 rows in set (0.00 sec)

The same query took 0 seconds to bring the results, it was cached.
Hoje vamos falar de uma ferramenta bem bacana do MySQL, o query cache.
But what is the size of the query cache?
Like everything in MySQL, it is configurable by the option query_cache_size values around 16m are recommended, depending on the capacity of your server.

If you do the analysis and see that most of your tables should be cached, you can use the reverse process, ie, set the variable query_cache_type = 1 this will cause all queries are cached and putting in selects that should not cache with SQL_NO_CACHE SELECT * FROM ...
But remember, use any of the following functions shall invalidate the query cache:
BENCHMARK() CONNECTION_ID() CONVERT_TZ()
CURDATE() CURRENT_DATE() CURRENT_TIME()
CURRENT_TIMESTAMP() CURTIME() DATABASE()
ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK()
LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT()
NOW() RAND() RELEASE_LOCK()
SLEEP() SYSDATE() UNIX_TIMESTAMP(
USER() UUID() UUID_SHORT()

Remember that to use the query cache, select * from ... is different from SELECT * FROM ... – Case sensitive
marcelo altmann MySQL