HI
Raiting:
6

Highload on a cheap hosting: a hash table in MySQL


High-loaded project (website) is not necessarily a popular social net, video hosting or MMORPG. The easiest way deeply to improve the website’s requirements for the hardware is transferring storage of sessions in the database. In this article we will discuss how to store data in the database and at the same time does not cut on the performance. Using a small amount of RAM we can quite well save the time of CPU. We're talking about situation when memcached is not available and other special resources of caching.

Magic MEMORY tables

DBMS MySQL database system realizes the type of tables that are stored in memory permanently and always are available for a short time. This is MEMORY, there is a synonym HEAP. The second name is older; therefore, it is preferable to use the first one.
Comparing with MyISAM or InnoDB, this format is a very limited, but it handles well the storage of operational data. Traditionally, we will give its pluses and minuses, we start with the pluses:

1. Any queries run as fast as possible - the data is already in memory
2. Tables are created quickly and easily could be deleted
3. Ability to limit the volume of each table
4. Blocking are supported

The third and fourth paragraphs distinguish MEMORY tables, for example, Memcache - where one server presents one hash table, and the possibility of an arbitrary blocking, also it is the feature of full-fledged DBMS. Naturally, the advantages end here.
There are a couple of fairly serious disadvantages:
1. Field types TEXT and BLOB are not available

Data storage

In our situation, the best field type is VARCHAR. From MySQL 5.0.3 version the length of field of this type could make 65,535 bytes, it is more than enough to store the same sessions. Common operations to store for this type are Set, Get, Check, and Delete. We realize Set method using a query REPLACE, Check - using SELECT COUNT (*), the rest is clear for us.
So, let us 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 we move on to PHP.

Object interface of a hash table

The interface is very primitive, thanks to the simple structure. The only nuance is serialization of all incoming values (value) - in fact, we need to store the arrays and objects. Therefore, the option that is close to the ideal will be as:

<?php
class HashTable
{
// Link to connect to MySQL
protected $connect;
// Name of table
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 using:


<?php
// Connection
$Link = mysql_connect ('localhost');
mysql_select_db ('test', $link);
mysql_set_charset ('utf8', $link);

$Storage = new HashTable($link, 'hashtable');
// Write
$Storage-> set('name', 'Vasya');
// Test
var_dump ($storage->check ('name'));
// Read
var_dump ($storage-> get ('name'));
// Delete
$Storage-> delete('name');
// Test
var_dump($storage-> check ('name'));
?>

Conclusion

It is worth to say that the solution is only for storing the small volume of information. If you download a lot of data into the MEMORY table, they can get into the swap, and even worse, the server will lose the resources to run queries on tables that are stored on a drive. As a result, the operational data of query may pass through the swap, which greatly will affect the performance of DBMS in general. Furthermore, if the limit is reached of the volume table, the old records are not deleted automatically and the server simply returns an error. On the other hand, it is easy to fit to a few megabytes; there will be detailed visitor statistics for the last hour or location of users on the website.
Pirat 7 september 2011, 12:29
Vote for this post
Bring it to the Main Page
 

Comments

Leave a Reply

B
I
U
S
Help
Avaible tags
  • <b>...</b>highlighting important text on the page in bold
  • <i>..</i>highlighting important text on the page in italic
  • <u>...</u>allocated with tag <u> text shownas underlined
  • <s>...</s>allocated with tag <s> text shown as strikethrough
  • <sup>...</sup>, <sub>...</sub>text in the tag <sup> appears as a superscript, <sub> - subscript
  • <blockquote>...</blockquote>For  highlight citation, use the tag <blockquote>
  • <code lang="lang">...</code>highlighting the program code (supported by bash, cpp, cs, css, xml, html, java, javascript, lisp, lua, php, perl, python, ruby, sql, scala, text)
  • <a href="http://...">...</a>link, specify the desired Internet address in the href attribute
  • <img src="http://..." alt="text" />specify the full path of image in the src attribute