While working on a few memcached related plugins for Drizzle, I noticed that it would be nice to have the ability to query memcached statistics from an INFORMATION_SCHEMA table. Today I put together a plugin that adds 2 memcached related I_S tables to drizzle. First, lets see the tables the plugin adds to drizzle along with the columns in each table:
drizzle> select table_name
    -> from information_schema.tables
    -> where table_name like '%MEMCACHED%';
+--------------------+
| table_name         |
+--------------------+
| MEMCACHED_STATS    | 
| MEMCACHED_ANALYSIS | 
+--------------------+
2 rows in set (0 sec)

drizzle> desc information_schema.memcached_stats;
+-----------------------+-------------+------+-----+---------+-------+
| Field                 | Type        | Null | Key | Default | Extra |
+-----------------------+-------------+------+-----+---------+-------+
| NAME                  | varchar(32) | NO   |     |         |       | 
| PORT_NUMBER           | bigint      | NO   |     | 0       |       | 
| PROCESS_ID            | bigint      | NO   |     | 0       |       | 
| UPTIME                | bigint      | NO   |     | 0       |       | 
| TIME                  | bigint      | NO   |     | 0       |       | 
| VERSION               | varchar(8)  | NO   |     |         |       | 
| POINTER_SIZE          | bigint      | NO   |     | 0       |       | 
| RUSAGE_USER           | bigint      | NO   |     | 0       |       | 
| RUSAGE_SYSTEM         | bigint      | NO   |     | 0       |       | 
| CURRENT_ITEMS         | bigint      | NO   |     | 0       |       | 
| TOTAL_ITEMS           | bigint      | NO   |     | 0       |       | 
| BYTES                 | bigint      | NO   |     | 0       |       | 
| CURRENT_CONNECTIONS   | bigint      | NO   |     | 0       |       | 
| TOTAL_CONNECTIONS     | bigint      | NO   |     | 0       |       | 
| CONNECTION_STRUCTURES | bigint      | NO   |     | 0       |       | 
| GETS                  | bigint      | NO   |     | 0       |       | 
| SETS                  | bigint      | NO   |     | 0       |       | 
| HITS                  | bigint      | NO   |     | 0       |       | 
| MISSES                | bigint      | NO   |     | 0       |       | 
| EVICTIONS             | bigint      | NO   |     | 0       |       | 
| BYTES_READ            | bigint      | NO   |     | 0       |       | 
| BYTES_WRITTEN         | bigint      | NO   |     | 0       |       | 
| LIMIT_MAXBYTES        | bigint      | NO   |     | 0       |       | 
| THREADS               | bigint      | NO   |     | 0       |       | 
+-----------------------+-------------+------+-----+---------+-------+
24 rows in set (0 sec)

drizzle> desc information_schema.memcached_analysis;
+--------------------------------+-------------+------+-----+---------+-------+
| Field                          | Type        | Null | Key | Default | Extra |
+--------------------------------+-------------+------+-----+---------+-------+
| SERVERS_ANALYZED               | bigint      | NO   |     | 0       |       | 
| AVERAGE_ITEM_SIZE              | bigint      | NO   |     | 0       |       | 
| NODE_WITH_MOST_MEM_CONSUMPTION | varchar(32) | NO   |     |         |       | 
| USED_BYTES                     | bigint      | NO   |     | 0       |       | 
| NODE_WITH_LEAST_FREE_SPACE     | varchar(32) | NO   |     |         |       | 
| FREE_BYTES                     | bigint      | NO   |     | 0       |       | 
| NODE_WITH_LONGEST_UPTIME       | varchar(32) | NO   |     |         |       | 
| LONGEST_UPTIME                 | bigint      | NO   |     | 0       |       | 
| POOL_WIDE_HIT_RATIO            | bigint      | NO   |     | 0       |       | 
+--------------------------------+-------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

drizzle> 
You might wonder how you specify the memcached servers to obtain statistics on. Well, I created a system variable for that purpose:
drizzle> show variables like '%memcached%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| memcached_stats_servers |       | 
+-------------------------+-------+
1 row in set (0 sec)

drizzle>
Now, lets set the system variable to a small memcached instance I have running on my laptop:
drizzle> set global memcached_stats_servers = 'localhost:11211';
Query OK, 0 rows affected (0 sec)

drizzle> show variables like '%memcached%';
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| memcached_stats_servers | localhost:11211 | 
+-------------------------+-----------------+
1 row in set (0 sec)

drizzle>
And lets do a simple query on the MEMCACHED_STATS table:
drizzle> select name, port_number, version, gets, sets, hits, misses
    -> from information_schema.memcached_stats;
+----------------------------------+-------------+----------+------+------+------+--------+
| name                             | port_number | version  | gets | sets | hits | misses |
+----------------------------------+-------------+----------+------+------+------+--------+
| localhost                        |       11211 | 1.2.6    |  975 |  407 |  950 |     25 | 
+----------------------------------+-------------+----------+------+------+------+--------+
1 row in set (0 sec)

drizzle>
The MEMCACHED_ANALYSIS table is not interesting unless there is more than 1 memcached server specified in the system variable. Thus, we need to update that system variable first:
drizzle> set global memcached_stats_servers = 'localhost:11211, localhost:11212';
Query OK, 0 rows affected (0 sec)

drizzle>
Now, lets do the same query on MEMCACHED_STATS again:
drizzle> select name, port_number, version, gets, sets, hits, misses from information_schema.memcached_stats;
+----------------------------------+-------------+----------+------+------+------+--------+
| name                             | port_number | version  | gets | sets | hits | misses |
+----------------------------------+-------------+----------+------+------+------+--------+
| localhost                        |       11211 | 1.2.6    |  975 |  407 |  950 |     25 | 
| localhost                        |       11212 | 1.2.6    |    0 |    0 |    0 |      0 | 
+----------------------------------+-------------+----------+------+------+------+--------+
2 rows in set (0 sec)

drizzle>
So you can see that for each server you specify in the system variable, a row will be output in the table. I'm going to make some activity happen in the second memcached instance I just started on my machine. Another branch I created over the last few days is a port of Brian's memcached engine to drizzle. So I'm going to create a table using the memcached engine and then insert some data into that table:
drizzle> create table test_data (
    -> a int primary key,
    -> b int,
    -> c varchar(64))
    -> engine=memcached;
Query OK, 0 rows affected (0.01 sec)

drizzle> insert into test_data
    -> values (1, 2, "this will be stored in memcached");
Query OK, 1 row affected (0.01 sec)

drizzle> select b, c 
    -> from test_data
    -> where a = 1;
+------+----------------------------------+
| b    | c                                |
+------+----------------------------------+
|    2 | this will be stored in memcached | 
+------+----------------------------------+
1 row in set (0 sec)

drizzle> select b, c  from test_data where a = 2;
Empty set (0 sec)

drizzle>
Now, lets query the statistics again:
drizzle> select name, port_number, version, gets, sets, hits, misses from information_schema.memcached_stats;
+----------------------------------+-------------+----------+------+------+------+--------+
| name                             | port_number | version  | gets | sets | hits | misses |
+----------------------------------+-------------+----------+------+------+------+--------+
| localhost                        |       11211 | 1.2.6    |  975 |  407 |  950 |     25 | 
| localhost                        |       11212 | 1.2.6    |    2 |    1 |    1 |      1 | 
+----------------------------------+-------------+----------+------+------+------+--------+
2 rows in set (0.01 sec)

drizzle>
And we can see they have been updated as expected. Now, lets look at the MEMCACHED_ANALYSIS table. I'm just going to query the first 2 columns of this table:
drizzle> select servers_analyzed, average_item_size
    -> from information_schema.memcached_analysis;
+------------------+-------------------+
| servers_analyzed | average_item_size |
+------------------+-------------------+
|                2 |                86 | 
+------------------+-------------------+
1 row in set (0 sec)

drizzle>
There will always just be one row in the output from this table. It essentially mimics the functionality of the memstat client utility in libmemcached. I'm not too sure what what to do with this patch at the moment. If people are interested, I can propose it for merging into Drizzle so that it will be available as a plugin.

blog comments powered by Disqus

Published

29 September 2009

Category

drizzle