Python checks whether Memcached caches MySQL results

This section describes the use cases of memcached in a production environment. memcached stores the query results of the relational database mysql, such as the download ranking of the website.

This section describes the use cases of memcached in a production environment. memcached stores the query results of the relational database mysql, such as the download ranking of the website.

This section describes the use cases of memcached in a production environment. memcached stores the query results of the relational database mysql, such as the download ranking of the website, this will increase the disk I/O overhead, and the ranking does not need to be updated in real time. Therefore, we store this result in memcached. memcached stores data serialized in the memory, we can set the timeout time and periodically update the new results from the relational database query to memcached.

I use python to write a small demo to demonstrate this scenario. First, python needs to load the corresponding modules to connect memcached and mysql. The purpose of the program is: first query the two persons with the highest salary from memcached and print their names and salaries. If no results are returned, it indicates that memcached does not exist, then we will query the results from the relational database mysql, update the results in memcached, and request again, then the results will be directly returned from memcached.

Install python-memcached and MySQL-python packages

Yum install-y python-memcached MySQL-python

#! /Usr/bin/python

Import sys

Import MySQLdb

Import memcache

Memc = memcache. Client (['127. 0.0.1: 100'], debug = 1 );

Key = memc. get ("top2salary ")

If key! = None:

Print "Load data from Memcache: % s, % s" % (key [0], key [1])

Else:

Print "Updating memcached data from MySQL ."

Conn = MySQLdb. connect (host = "127.0.0.1 ",

User = "root ",

Passwd = "123456 ",

Db = "web_user ")

Cursor = conn. cursor ()

Cursor.exe cute ('select * from emp order by salary desc limit 2 ')

Rows = cursor. fetchall ()

Memc. set ('top2salary ', rows, 10)

Mysql emp Table query results:

Mysql> select * from emp;

+ -------------- + -------- +

| Name | salary |

+ -------------- + -------- +

| Casillas | 10500 |

| Fernández | 350 |

| Varane | 1, 21000 |

| Pepe | 16000 |

| Ramos | 1, 35000 |

| Nacho | 4400 |

| Coentro | 12500 |

| Marcelo | 22000 |

| Carvajal | 10500 |

| Arbeloa | 6200 |

| Khedira | 19500 |

| Casemiro | 5300 |

| Xabi Alonso | 10500 |

| Modric | 35000 |

| Maid | 16000 |

| Isco| 31000 |

| Balance | 70500 |

| Di María | 26500 |

| Ronaldo | 88000 |

| Jes é | 13000 |

| Benzema | 30000 |

| Morata | 1, 10500 |

+ -------------- + -------- +

22 rows in set (0.00 sec)

The first execution of memcached is empty, and the query is immediately performed in myql, cached in memcached, and returned from memcached again, in the code, I set the timeout time to 10 seconds. After 10 seconds, the cache will be cleared and queried from mysql again. If new mysql Data changes during this time period, such as inserting a higher-paying person, the latest results will be returned. Generally, mysql is cached in memcached as results that do not change frequently.

[Root @ localhost ~] #./Memc. py

Updating memcached data from MySQL.

[Root @ localhost ~] #./Memc. py

Load data from Memcache: ('ronaldo ', '123'), ('bale', '123 ')

[Root @ localhost ~] #./Memc. py

Load data from Memcache: ('ronaldo ', '123'), ('bale', '123 ')

[Root @ localhost ~] #./Memc. py

Load data from Memcache: ('ronaldo ', '123'), ('bale', '123 ')

Advantage if you load a web page and query mysql every time and execute SQL statements such as order by to increase the load on the database I/O, it also slows down the page opening speed.

Python details: click here
Python: click here

Recommended reading:

Python development technology details. (Zhou Wei, Zong Jie). [hd PDF scan version + book guide video + code]

Obtain Linux information using a Python script