← Back to team overview

drizzle-discuss team mailing list archive

the status of filesystem storage engine in drizzle

 

http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html

The file system storage engine is in shape now.

There are lots of storage engines in Drizzle already.  What’s this new
storage engine used for?  To give you a rough idea, you can use the below
SQL statement to read the file ‘/proc/loadavg’ directly.

drizzle> CREATE TABLE LoadAverage (
load1 DECIMAL(3, 2),
load5 DECIMAL(3, 2),
load15 DECIMAL(3, 2),
active_processes VARCHAR(12),
lastpid INTEGER) ENGINE=FILESYSTEM, FILE="/proc/loadavg";

drizzle> select * from LoadAverage;
+-------+-------+--------+------------------+---------+
| load1 | load5 | load15 | active_processes | lastpid |
+-------+-------+--------+------------------+---------+
|  0.00 |  0.00 |   0.00 | 1/97             |    6061 |
+-------+-------+--------+------------------+---------+

The file system storage engine eases our manipulation on disk file,
especially those file under /proc directory.  This engine tries to be small
and useful, like the calculator at your hand.

There are several options available for this storage engine for now.  Extra
options provides extra functionalities.

   1. *FILE*.  This option specifies which file composes the corresponding
   table.  This is the most important and useful option, it’s a MUST option for
   this storage engine.
   2. *ROW_SEPARATOR*.  This option specifies which characters should be
   taken as end-of-line.  The default is “\n”.
   3. *COL_SEPARATOR*.  This option specifies which characters should be
   taken as the delimiter for columns.  The default is ” \t” (space and tab),
   it’s reasonable to make it as default.
   4. *SEPARATOR_MODE*.  It has three values, STRICT, GENERAL, WEAK.  This
   one is a little tricky to understand. It shows the rule how to treat
   continuous separators. For example, if we have the line
   “111<SPACE><SPACE><SPACE>222″, the columns in WEAK mode would be only two
   columns “111″ and “222″; the columns in other modes would be four columns
   “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file;
   while STRICT mode will ruthlessly add an entire empty tuple into this table.
   5. *FORMAT*. This is a newly added feature.  It’s written to process the
   file “/proc/meminfo”. This file has the similar format as key and value each
   line. What about we transpose these columns and rows and make all these keys
   as the column in a table? That should be cool. Yes, we can do this. Specify
   “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’,
   here you go:

   CREATE TABLE t1 (a int)
ENGINE=FILESYSTEM,FILE="/proc/meminfo",FORMAT="KEY_VALUE",COL_SEPARATOR=":
";
   SELECT * FROM t1;
   Active  AnonPages       Bounce  Buffers Cached  CommitLimit
Committed_AS    DirectMap2M     DirectMap4k     Dirty   HugePages_Free
 HugePages_Rsvd  HugePages_Surp  HugePages_Total Hugepagesize
InactiveMapped  MemFree MemTotal        NFS_Unstable    PageTables
 SReclaimable    SUnreclaim      Slab   SwapCached       SwapFree
  SwapTotal       VmallocChunk    VmallocTotal    VmallocUsed
Writeback       WritebackTmp
   1526364 198768  0       221004  2829356 2600136 495728  4186112
7040    1916    0       0       0      02048    1722772 51064   502844
 4059680 0       27124   198000  12076   210076  0       570296
570296 34359655499      34359738367     82808   0       0
   SHOW CREATE TABLE t1;
   Table   Create Table
   t1      CREATE TABLE `t1` (
   `Active` varchar(8) DEFAULT NULL,
   `AnonPages` varchar(7) DEFAULT NULL,
   ...some lines omitted...
   `Writeback` varchar(2) DEFAULT NULL,
   `WritebackTmp` varchar(2) DEFAULT NULL
   ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE'
COL_SEPARATOR=': '


There are still some TODOs on my list:

   1. enclosing and quotation is not there.
   2. more test cases to cover some corner cases.
   3. …

I would like my project to be useful, and I encourage all DBAs,
administrators to give a try and see whether it fits your daily use.  Any
feedback is welcome! [image: :-)]


--Zimin

Follow ups