Highload on cheap hosting: a hash table in MySQL

High-load project (website) is not necessarily a popular social network, video hosting or MMORPG. The simplest way to dramatically increase the requirements of the site to the gland is to transfer the storage of sessions to the database. In this article, we will look at how to store data in a database, and without sacrificing performance. By sacrificing a small amount of RAM, you can significantly save CPU time. We talk about styution when memcached and other special caching tools are not available.

Magic MEMORY tables

MySQL DBMS implements the type of tables that are permanently stored in RAM, and therefore are always available in the shortest amount of time. This is MEMORY, there is still a synonym for HEAP. The second name is older, so it is preferable to use the first.
Compared to MyISAM or InnoDB, this format is very limited, but it does an excellent job of storing real-time data, but traditionally I will bring its pros and cons, I will start with the pros:

1. Any queries are executed as quickly as possible - the data is already in memory
2. Tables are quickly created and quickly destroyed.
3. Ability to limit the size of each table
4. Locks are supported

The third and fourth points distinguish MEMORY-tables from, for example, Memcache - where one server represents one hash table, and the possibility of arbitrary locking is also a distinctive feature of full-fledged DBMS. Naturally, this advantage ends.
There are a couple of quite serious drawbacks:

1. TEXT and BLOB field types are not available.

Data storage

In our situation, the optimal field type is VARCHAR. Since MySQL 5.0.3, the length of the field of this type can be 65,535 bytes - this is more than enough to store the same sessions. The usual operations for this type of storage are Set, Get, Check, Delete operations. We implement the Set method using the REPLACE query, Check using SELECT COUNT (*), everything is clear with the rest.
So, create a table:

CREATE TABLE `hashtable` (
`key` VARCHAR(32),
`value` VARCHAR(65536),
PRIMARY KEY (`key`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE utf8_bin;

Ok, now for PHP.

Object interface hash table

Thanks to its simple structure, the interface is extremely primitive. The only nuance is the serialization of all incoming values ​​(value) - after all, we need to store both arrays and objects. Therefore, an approximate version of the ideal turned out to be:

 <?php class HashTable { //     MySQL protected $connect; //   protected $table; /** * * @param resource MySQL $connect * @param string $table */ public function __construct($connect, $table) { $this->connect = $connect; $this->table = $table; } /** * * @param string $key * @param string $val * @return boolean */ public function set($key, $val) { $key = md5($key); $val = serialize($val); $val = mysql_real_escape_string($val, $this->connect); $query = 'REPLACE INTO `'.$this->table.'` (`key`, `value`) '; $query .= 'VALUES ("'.$key.'", "'.$val.'")'; return mysql_query($query, $this->connect) ? true : false; } /** * * @param string $key * @return void */ public function get($key) { $key = md5($key); $query = 'SELECT `value` FROM `'.$this->table.'` WHERE `key`="'.$key.'"'; $result = mysql_query($query, $this->connect); if ($result) { $row = mysql_fetch_row($result); return unserialize($row[0]); } else { return false; } } /** * * @param string $key * @return boolean */ public function check($key) { $key = md5($key); $query = 'SELECT COUNT(*) FROM `'.$this->table.'` WHERE `key`="'.$key.'"'; $result = mysql_query($query, $this->connect); $row = mysql_fecth_row($result); return (bool)$row[0]; } /** * * @param string $key * @return boolean */ public function delete($key) { $key = md5($key); $query = 'DELETE FROM `'.$this->table.'` WHERE `key`="'.$key.'"'; return mysql_query($query, $this->connect) ? true : false; } } 

Example of use:

 <?php //  $link = mysql_connect('localhost'); mysql_select_db('test', $link); mysql_set_charset('utf8', $link); $storage = new HashTable($link, 'hashtable'); //  $storage->set('name', 'Vasya'); //  var_dump($storage->check('name')); //  var_dump($storage->get('name')); //  $storage->delete('name'); //  var_dump($storage->check('name')); 

Finally

It should be noted that this solution is only for storing small amounts of information. If you load a lot of data into the MEMORY table, they can fall into a swap, and even worse, deprive the server of resources for querying the tables stored on disk. As a result, the operational data of the request can also pass through a swap, which will greatly affect the performance of the DBMS as a whole. In addition, if the table volume limit is reached, old records are not automatically deleted and the server simply returns an error. On the other hand, a few megabytes easily fit, detailed statistics of visits for the last hour or the position of users on the site.