Using gearman to implement redis cache mysql

Environment: centos6.5mysql5.6gearman Introduction: Gearman is a distributed task distribution framework. The design is concise and widely supported. A typical Gearman application includes the following parts: GearmanJobServer: core program of Gearman, which runs in the background as a daemon:

Environment: centos6.5 mysql5.6 gearman Introduction: Gearman is a distributed task distribution framework. The design is concise and widely supported. A typical Gearman application includes the following parts: Gearman Job Server: core program of Gearman, which runs in the background as a daemon Client:

Environment:

Centos6.5

Mysql5.6

Gearman introduction:

Gearman is a distributed task distribution framework. The design is concise and widely supported. A typical Gearman application includes the following parts:

• 
  

  • Gearman Job Server: core program of Gearman, which runs in the background as a daemon

  • Gearman Client: it can be understood as the recipient of the task. For example, if I want to execute a mail sending task in the background, I can call a Gearman Client in the program and input the mail information, then, the execution result is immediately displayed to the user, and the task itself runs slowly in the background.

  • Gearman Worker: The real executor of a task. Generally, you need to write the specific logic and run it in the daemon mode. After receiving the task content transmitted by Gearman Client, Gearman Worker will process it in order.

Design Concept:

First, use the mysql UDF (through the combination of lib_mysqludf_json and gearman-mysql-udf) to send data to Gearman when mysql DATA changes, mysql is equivalent to Gearman's clinet. Then run the php program compiled by myself as the worker and upload the data in Gearman to Redis. At this time, Redis is equivalent to the consumer of Gearman.

1. Install gearman

The yum source in the experiment is based on the yum source of the network that comes with centos6.5, and adds the epel source (additional Packages of Extra Packages for Enterprise Linux and Enterprise Linux) it is a software warehouse project maintained by the Fedora team and provides RHEL/CentOS with software packages they do not provide by default. Using this source saves a lot of trouble and saves the trouble of compiling source code. It should be noted that whether it is using the network yum source of centos or the epel extension source, you need your IP address to access the Internet.

Install gearman extensions and nc tools for gearman, php, and php

yum install -y php-pecl-gearman libgearman  libgearman-devel gearmand nc

Start the gearman Service

/etc/init.d/gearmand start

Verify whether gearman is successfully started. If Port 4730 is returned, the service is started normally.

[root@hadoop1 ~]# netstat -alnutp |grep gearman

2. simulate how Gearman works:

Run the following command to view the Gearman queue:

watch -n 1 "(echo status; sleep 0.1) | nc 127.0.0.1 4730"

The result is as follows:

writeLog        0       0       0

Meaning of the four columns: 1-Task Name; 2-Number of waiting queue tasks; 3-Number of running tasks; 4-Number of running worker processes;

Compile a php code to simulate Gearman's Client: client. php

 AddServer (); $ client-> doBackground ('writelog', 'Log content'); echo 'file operations in the background'; echo "\ n ";

Execute client. php

php client.php

At this time, check the Gearman queue again and find a task in the waiting queue.

writeLog        1       0       0

Write a php code to simulate Gearman's Worker: worker. php

This worker is used to write the 'Log content' string sent from the client to the Gearman. Log file in the current directory.

 addServer();    $worker->addFunction('writeLog', 'writeLog');    while($worker->work());    function writeLog($job)    {        $log = $job->workload();        file_put_contents(__DIR__ . '/gearman.log', $log . "\n", FILE_APPEND | LOCK_EX);    }

Start worker. php In nohup mode in the background

nohup php worker.php &

Check the Gearman queue again and find that the waiting task is changed to 0, the worker process is changed to 1, and the gearman. log has content.

writeLog        0       0       1

[root@hadoop1 ~]# cat gearman.log Log content

3. Install the mysql-server, mysql, and php-mysql drivers, this is to use a program later to compare the efficiency of reading data from Redis and Mysql respectively ). In this experiment, mysql5.6 has been installed on my machine, so I used mysql5.6 for experiment. Of course, you can also use yum to install mysql. The installed mysql version may not be 5.6, but it does not matter.

Install mysql software

yum install -y mysql-server mysql php-mysql

Start mysql

/etc/init.d/mysql start

4. Install lib_mysqludf_json

wget  https://github.com/mysqludf/lib_mysqludf_json/archive/master.zipmv master master.zipunzip master.zipcd lib_mysqludf_json-masterrm -rf lib_mysqludf_json.so gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

At this time, the lib_mysqludf_json.so file is re-compiled, and lib_mysqludf_json.so needs to be copied to the mysql plug-in directory to view the mysql plug-in directory:

[root@hadoop1 ~]# mysql -u root -pupbjsxt --execute="show variables like '%plugin%';"    +---------------+--------------------------+| Variable_name | Value                    |+---------------+--------------------------+| plugin_dir    | /usr/lib64/mysql/plugin/ |+---------------+--------------------------+

[root@hadoop1 lib_mysqludf_json-master]# cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/

5. Install gearman-mysql-udf

wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz   tar xf gearman-mysql-udf-0.6.tar.gz -C ./cd gearman-mysql-udf-0.6./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql/plugin/make && make install

This plug-in is directly installed in the mysql plug-in directory.

6. Connect to mysql, create the corresponding function and trigger, and set the gearman server Information

[Root @ hadoop1 ~] # Mysql-u root-p ****** mysql> create function json_object returns string soname 'lib _ mysqludf_json.so '; mysql> create function gman_do_background returns string soname 'libgearman _ mysql_udf.so '; mysql> create function gman_servers_set returns string soname 'libgearman _ mysql_udf.so '# CREATE a trigger for the test table in the skip hop mysql> use skip hop; mysql> describe test; + ------- + ---------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ------- + ---------- + ------ + ----- + --------- + ------- + | id | int (11) | YES | NULL | name | char (20) | YES | NULL | + ------- + ---------- + ------ + ----- + --------- + ------- + 2 rows in set (0.00 sec) mysql> source trigger. SQL # Set gearman server Information mysql> SELECT gman_servers_set ('2017. 0.0.1: 4730 ');

The content of the trigger. SQL script file is as follows:

DELIMITER $$CREATE TRIGGER datatoredis AFTER UPDATE ON test  FOR EACH ROW BEGIN    SET @ret=gman_do_background('syncToRedis', json_object(NEW.id as `id`, NEW.name as `name`));   END$$DELIMITER ;

7. Update a piece of data in mysql and view the gearman queue.

mysql> update test set name='redis' where id=10;Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0

writeLog        0       0       1syncToRedis     1       0       0

It can be seen that the mysql trigger has successfully passed data into gearman.

8. Install redis and php-pecl-redis (the php driver to connect to redis)

yum install php-pecl-redis redis -y

Start redis

/etc/init.d/redis   start

Log on to the redis client and view the data in the current memory.

[root@hadoop1 ~]# redis-cli redis 127.0.0.1:6379> keys *(empty list or set)

There is no data in redis.

9. Write a worker program to transfer data from gearman to redis: redis_worker.php

 addServer();    $worker->addFunction('syncToRedis', 'syncToRedis');    $redis = new Redis();    $redis->connect('127.0.0.1');    while($worker->work());    function syncToRedis($job)    {        global $redis;        $workString = $job->workload();        $work = json_decode($workString);        if(!isset($work->id)){                return false;        }        $redis->set($work->id, $workString);    }

10. Run redis_worker.php in the background in nohup mode.

nohup php redis_worker.php &

Then, view the gearman queue.

writeLog        0       0       1syncToRedis     0       0       1

It is found that the number of tasks waiting before the syncToRedis task is changed to 0, and the number of running worker processes is changed to 1.

Check whether data is cached in redis

[root@hadoop1 ~]# redis-cli redis 127.0.0.1:6379> keys *1) "10"redis 127.0.0.1:6379> get 10"{\"id\":10,\"name\":\"redis\"}"

Redis has successfully cached data updated in mysql.

11. Performance Comparison Between Reading data from redis and reading data from mysql

Write php code to read data from redis: php_redis.php

 Connect ('2017. 0.0.1 '); echo $ redis-> get ('10'); echo "\ n"; $ redis-> close (); $ etime = microtime (true ); // obtain the execution end time of the program $ total = $ etime-$ stime; // calculate the difference echo "$ total ". 'second'; echo "\ n";?>

Write php code to read data from mysql: php_mysql.php

 ". $ Row ['name']; echo "\ n";} mysql_close ($ con); $ etime = microtime (true ); // obtain the execution end time of the program $ total = $ etime-$ stime; // calculate the difference echo "$ total ". 'second'; echo "\ n"?>

Run two php programs respectively.

[Root @ hadoop1 ~] # Php php_redis.php {"id": 10, "name": "redis"} 0.00059199333190918 seconds [root @ hadoop1 ~] # Php php_mysql.php 10 --> redis0.0043718814849854 seconds [root @ hadoop1 ~] # Bc <0.0043718814849854/0.000591993331909187

By querying a record, we can find that the length of time for obtaining data from mysql is seven times the length of time for obtaining data from redis. It can be seen that the performance is improved by almost an order of magnitude.

Note:

The test proves that mysql will lose the information of the previously set gearman server after restart. The solution is as follows:

Create an init_file. SQL file in the datadir directory of mysql, with the content set for gearman server

echo  "SELECT gman_servers_set('127.0.0.1:4730');" > /var/lib/mysql/init_file.sql

Add the following content to [mysqld] of the mysql configuration file:

init-file=/var/lib/mysql/init_file.sql

Restart mysql, update a record, and try again!

Reference: http://avnpc.com/pages/mysql-replication-to-redis-by-gearman

PS: I have completed my first blog. I hope you can give me more advice and have a good life!